current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

This question is an exact duplicate of:

I have a tar archive with full backup of my CentOS filesystem with pg9.1 installed. How can I restore one database from it?

share|improve this question

migrated from stackoverflow.com Mar 15 '14 at 1:14

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Paul White, RolandoMySQLDBA, Mark Storey-Smith, Kin, evilcube Apr 16 '14 at 10:44

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
1  
You can not restore one database. You can only restore full database cluster. –  Igor Romanchenko Mar 14 '14 at 11:58
    
How can I do that? Just copy-paste all files? –  asiniy Mar 14 '14 at 11:59
    
Copy postgres data directory from archive and launch postgres for the copied data directory. –  Igor Romanchenko Mar 14 '14 at 13:07
    
Igor, I copied postgres centos data directory to ubuntu data directory /var/lib/postgresql/9.1/main (it similar to data). After restarting, I have 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" : And, after restoring backup to right place I still get this error. WTF? –  asiniy Mar 16 '14 at 5:00
1  
Solved it, see here: dba.stackexchange.com/questions/61026/reset-postgresql –  asiniy Mar 16 '14 at 8:14

1 Answer 1

up vote 1 down vote

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/ .

share|improve this answer
    
Craig, thanks for helping. It seems I need the first way solution. But. How can I do pg_dump without password required? I forgot it. I tried to change my pg_hba.conf, but with no success. –  asiniy Mar 16 '14 at 8:47
    
It seems I solved this. Just edited pg_hba.conf at data folder! –  asiniy Mar 16 '14 at 9:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.