OK. I think I'm on to something here, I first reset back to my file level backup. I created a recovery.conf file in the root of the data directory like this: --------------------- restore_command = 'cp /mnt/archive/%f %p' recovery_target_time = '2017-01-24 02:08:00.023064+11' recovery_target_inclusive = 'true' pause_at_recovery_target = 'false' --------------------- Note, the archive directory had no files in it, I left the WAL files in the pg_xlog directory.
Then I started up the database again: postgres@atlassian:~/9.3/main$ /usr/lib/postgresql/9.3/bin/pg_ctl -D /etc/postgresql/9.3/main start server starting postgres@atlassian:~/9.3/main$ 2017-01-30 10:07:28 AEDT LOG: database system was interrupted while in recovery at 2017-01-27 20:13:26 AEDT 2017-01-30 10:07:28 AEDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2017-01-30 10:07:28 AEDT LOG: starting point-in-time recovery to 2017-01-24 02:08:00.023064+11 2017-01-30 10:07:28 AEDT LOG: database system was not properly shut down; automatic recovery in progress 2017-01-30 10:07:28 AEDT WARNING: WAL was generated with wal_level=minimal, data may be missing 2017-01-30 10:07:28 AEDT HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. 2017-01-30 10:07:28 AEDT LOG: redo starts at 5/528B4558 2017-01-30 10:07:40 AEDT LOG: consistent recovery state reached at 5/A3FFFA30 cp: cannot stat ‘/mnt/archive/0000000100000005000000A3’: No such file or directory cp: cannot stat ‘/mnt/archive/0000000100000005000000A4’: No such file or directory 2017-01-30 10:07:40 AEDT LOG: redo done at 5/A3FFF9E8 2017-01-30 10:07:40 AEDT LOG: last completed transaction was at log time 2017-01-24 02:08:00.023064+11 cp: cannot stat ‘/mnt/archive/0000000100000005000000A3’: No such file or directory cp: cannot stat ‘/mnt/archive/00000002.history’: No such file or directory 2017-01-30 10:07:40 AEDT LOG: selected new timeline ID: 2 cp: cannot stat ‘/mnt/archive/00000001.history’: No such file or directory 2017-01-30 10:07:40 AEDT LOG: archive recovery complete 2017-01-30 10:08:55 AEDT FATAL: the database system is starting up 2017-01-30 10:08:57 AEDT FATAL: the database system is starting up This time it looks like it has actually finished the startup and recovery. However I think I might have something wrong about the process. Any thoughts on the above log? *Brian Mills* CTO *Mob: *0410660003 *Melbourne* 03 9012 3460 or 03 8376 6327 *|* *Sydney* 02 8064 3600 *|* *Brisbane* 07 3173 1570 Level 1 *|* 600 Chapel Street *|* South Yarra*|* VIC *|* 3141 *|* Australia <https://www.facebook.com/TryBooking/> <https://twitter.com/trybooking> <https://www.linkedin.com/company/trybooking-com> On 30 January 2017 at 04:49, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/28/2017 11:23 PM, Brian Mills wrote: > >> I presume this is a binary log file for the database. >> >> Am I able to recover to a point in time using this log file? >> >> What I would do in SQL Server would be recover to a point in time, say a >> bit before the last completed transaction time the log mentions, then >> take a backup. Is that possible in postgres? >> > > Had another thought. If I remember correctly you are using this as an > exercise in Postgres recovery. If that is indeed the case you might try: > > 1) Stop the Postgres instance you have running now. > > 2) Move the WAL file that Postgres is currently stalled on, > 0000000100000005000000A3, out of pg_xlog. > > 3) Restart the Postgres instance. > > My guess it it will not bring it back to the exact point you want, but > close. You can get a general idea by running(before and after removing the > WAL), as the postgres user: > > pg_controldata -D /etc/postgresql/9.3/main > > >> The log mentions this: >> 2017-01-27 20:36:18 AEDT LOG: last completed transaction was at log >> time 2017-01-24 02:08:00.023064+11 >> >> (which is moments before, or possibly as the disk filled up doing a db >> backup dump) >> >> *Brian Mills* >> CTO >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
