PostgreSQL PITR restore

Question

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.

Answer 1

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.

• user4659: 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.

Answer 2

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.

• user4659: thank you for your answer, but all of the various constraints and relationships between the tables would prevent me from restoring just one.