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.