Restore data from tar file

Question

I have a tar archive with full backup of my CentOS filesystem with PostgreSQL 9.1 installed.

How can I restore one database from it?

If I can only restore a full database cluster, how can I do that? Just copy-paste all the files?

Answer 1

You will need a locally installed PostgreSQL of the same major as the data directory archive. For example, if the archive came from 9.1.5 you would need some 9.1.x on the local machine, it doesn't matter if it's 9.1.0 or 9.1.9, but it must be 9.1 not 9.2 or 9.0.

If you don't have 9.1, you can install it from packages or just download the source and compile it locally.

Then, if you just want to dump the data to reload into an existing local PostgreSQL install:

• Unpack the archive
• If necessary set your PATH so that PostgreSQL 9.1's bin directory is first on the PATH
• PGPORT=5435 pg_ctl -D /path/to/unpacked/archive start
• PGPORT=5435 pg_dumpall --globals-only
• Dump each database with pg_dump

If you instead want to restore the database so it runs instead of whatever's locally running, you must:

• Ensure the current install is of the same major version
• Stop the current PostgreSQL: service postgresql stop
• Rename the data directory. On CentOS it'll be in /var/lib/pgsql/9.1/data/, so something like sudo mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data.old. Do not delete it; if it turns out there is data in there you forgot you needed, this way you can still get it back.
• Move the unpacked archive to the old data directory location, e.g sudo mv /path/to/unpacked/archive/folder /var/lib/pgsql/9.1/data
• Set the permissions: sudo chown postgres:postgres /var/lib/pgsql/9.1/data; sudo chmod 700 /var/lib/pgsql/9.1/data
• Edit postgresql.conf to reflect any required changes for the local install
• Start PostgreSQL: service postgresql start

Some details like service name, path, etc will vary depending on whether you're running the original Red Hat packages of PostgreSQL, or whether you installed from http://yum.postgresql.org/ .

Answer 2

_{Answer originally left in comments by the OP and Igor Romanchenko:}

Copy the postgres data directory from the archive and launch postgres for the copied data directory.

I copied the postgres centos data directory to the ubuntu data directory

/var/lib/postgresql/9.1/main

(It is similar to data). After restarting, I had an error:

• Error: could not exec

  /usr/lib/postgresql/9.1/bin/pg_ctl
      /usr/lib/postgresql/9.1/bin/pg_ctl 
      start -D /var/lib/postgresql/9.1/main
      -l /var/log/postgresql/postgresql-9.1-main.log
      -s -o  
      -c config_file="/etc/postgresql/9.1/main/postgresql.conf"
  

After restoring the backup to the right place I still got this error.

I finally solved the problem by changing the owner of the /var/lib/PostgreSQL folder.