Having continued my research, the problem I encountered is the exact same that's been recorded here:
https://www.marshut.net/kstxxk/pitr-failing-to-stop-before-drop-database.html I took the backup by following the procedures as laid forth in the continuous archiving document ( http://www.postgresql.org/docs/9.2/static/continuous-archiving.html) I configured the archiving to gzip the wal files to pgdata/archive Then I started the backup process by issuing "select pg_start_backup('mylabel')" next tarball'd the contents of pgdata, excluding the pgdata/archive dir and the pgdata/pg_xlog dir (although preserving the directory structure) after that I issued "select pg_stop_backup()" then I added the contents of pgdata/archive and pgdata/pg_xlog to the tarball above then I gzipped the tarball. The above is how I archived and backed up.. To restore the backup, I shut down the server, moved pgdata to pgdata_backup, untarballed the backup tarball, removed all the files in the new pgdata/pg_xlog dir, copied the files from pgdata_backup/archive and pgdata_backup/pg_xlog into the new pgdata dir, set up the recovery.conf file giving it a timestamp gathered from the pgdata_backup/pg_log/<> log files.. I copied ALL the pg_xlog files ... not simply the "unarchived ones". All of the unarchived ones should have been removed when I removed the contents of the pg_xlog dir after restoring the tarball.. I think I answered all the questions - please let me know if I missed some. Based on the url I pasted at the top, though, it appears I'm not the only one who's encountered this problem. On Tue, Dec 2, 2014 at 3:39 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 11/28/2014 02:29 PM, Joshua Boyd wrote: > >> I am testing out point in time recovery from a hot physical backup in a >> disaster recovery situation - I turned on archiving of files, created a >> hot physical backup, >> > > How did you take the backup? > > Archiving how and to where? > > then (after letting it run for a few days) issued a > >> "DROP DATABASE". The pg_log file shows the DROP DATABASE command was >> issued at '2014-11-28 10:20:00.010 PST'. I shut down the server, moved >> the pgdata directory to pgdata_backup ... restored the files in the hot >> physical backup I made, copied the wal archive files from pgdata_backup >> to the (new) pgdata archive, >> > > The above I do not understand. > You where archiving the WALs in your pgdata directory? > > Restored the backup how? > > cleared out the new pg_xlog dir and copied > >> the files from the old pg_xlog into the new.. Set up a recovery.conf >> > > All the files or only the unarchived ones? > > file as such: >> >> restore_command = 'gunzip -c /home/pg2dev/joshtest/pgdata/archive/%f.gz >> > %p' >> recovery_target_time = '2014-11-28 10:20:00.010 PST' >> recovery_target_inclusive = false >> >> then I started the server up. the pg_log shows the following: >> > > >> And then I look in pgdata/base .. and sure enough, that directory is >> missing. I examine my hot physical backup file and that directory >> exists within it. >> >> So .... even though the recovery SAYS "recovery stopping before commit >> of transaction 235078" ... it doesn't appear that it's 100% accurate. >> It didn't commit the transaction, clearly, because the database is still >> listed in the data dictionary ... however, the filesystem files are >> gone. Please - am I doing something wrong, or would this be considered >> a bug? >> >> -- >> Joshua Boyd >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Joshua Boyd
