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?


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


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 -D /opt/pgsql/backup

The PostgreSQL service was shutdown at 3 pm.

2 Answers 2


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.

  • 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. Jul 2, 2012 at 4:55

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.

  • thank you for your answer, but all of the various constraints and relationships between the tables would prevent me from restoring just one. Jul 6, 2012 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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