Home / server / Questions / 19987
Accepted
Sean Preston
Asked: 2009-06-05 11:19:31 +0800 CST

Recover PostgreSQL database from filesystem backup

  • 772

I have a PostgreSQL 8.3 data directory backup. I need to copy a database from this backup into a new PostgreSQL instance. Due to problems with the old server I cannot do a pg_dump of the database.

I have figured out which directory is for the database in question. In MySQL for instance all you would normally do is shutdown the server, copy the directory across and restart it and the database would be available. Now this does not work with PostgreSQL and I suspect it is because the database is not listed in global/pg_database file. This file seems to be auto-generated when PostgreSQL starts so how do I tell the system that the database is there?

disaster-recovery postgresql

1 Answers

  1. Best Answer

    Please be aware that in PostgreSQL, your cluster data directory is a self-contained unit that cannot reliably be restored in parts, as per the documentation here.

    Your best hope is to use the full data directory of the old server and to start a server on it, then restore from a dump of the database you get from there:

    1. Get a postgresql.conf that fit the configuration on the new host going (orient yourself by the files in the old - which you change here - and new data directories)
    2. Find out the user name of the old installation's PostgreSQL superuser - most likely postgres.
    3. Create this user name (UNIX user) on the new host, unless it's already there.
    4. Give (chown) the old data directory to that user, unless the uid matches the one on the old server.
    5. Configure pg_hba.conf in the old data directory to allow connections from local users to the same postgres user name (this is normally the default, but check).
    6. Run the old database cluster as the UNIX user with the same name as the old data directory's superuser, e.g. for user postgres: 
      sudo -u postgres postgres -D old_data_directory


      or with su:
      

su postgres
postgres -D old_data_directory
    7. dump the old database, again with the usual user name, postgres: 
      sudo -u postgres pg_dump -p /path/to/socket/for/old/directory database_name > dump_file.sql


      becoming postgres with su analogous to above
    8. adapt the dump_file.sql to the new server's user names etc; create the empty database with appropriate ownership there
    9. scan the database into the new server:
      sudo -u postgres psql -p /path/to/socket/for/new/directory new_database_name


      once again, getting postgres privileges using su analogous to above

    That should get you set, or at least show a good path to get there.

    • 16