9

I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a table at 12 pm on databaseB and I want to restore databaseB to just before the time at which the table was deleted. The time is now 3 pm.

How can I just restore databaseB up to 12 pm without losing three hours worth of data in the other two databases?

postgresql.conf

wal_level = archive             
archive_mode = on               
archive_command = 'test ! -f /opt/pgsql/logs/%f && cp %p /opt/pgsql/logs/%f'
max_wal_senders = 100

recovery.conf

restore_command = 'cp /opt/pgsql/logs/%f %p'
recovery_target_time = '2012-06-29 11:59:59 CEST'

I ran my base backup at 9 am

pg_basebackup -h 127.0.0.1 -D /opt/pgsql/backup

The PostgreSQL service was shutdown at 3 pm.

Improve this question
6

I would do a point in time recovery to a different location, restoring to the desired time, and pg_dump the problem database. I would drop the one database on the normal location, create it again, and load the pg_dump output.

Make sure you make and save a file-system level copy of the cluster's data directory tree before you start anything like this.

Improve this answer
1
  • Thank you, +1 for the answer. Could you please add the steps necessary to recover to a different location and then the restore using pg_restore? With your answer, I'm thinking of using the actual base backup location to do the PITR. – Craig Efrein Jul 2 '12 at 4:55
0

To expand on kgrittn's answer, you can also do a pg_dump of only the table which was dropped and rebuild it in the existing databaseB, so you don't need to lose 6 hours of data in the other tables.

Improve this answer
1
  • thank you for your answer, but all of the various constraints and relationships between the tables would prevent me from restoring just one. – Craig Efrein Jul 6 '12 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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