Thread: Unix Postgres 9.5. using pg_basebackup and WAL files. Can't get a PITR recovery
Unix Postgres 9.5. using pg_basebackup and WAL files. Can't get a PITR recovery
I can't cut and paste my issues, so I will explain as best I can.
I’m testing PITR on a single 9.5 db (no replication setups).
I have all the settings that I can find, wal_level, archive_command,
restore_command, directories defined, logs defined. My WAL files are stored off in
another area with gzip in the archive_command. The restore_command also uses gunzip. Those work fine.
(pg_xresetlog seems to work OK too)
I can restore from a pg_basebackup, but if I define PITR in recovery.conf along with the
pg_basebackup in place, I can't get to that point in time.
Does it matter if I use hot_standby or archive? hot_standby should include archive functionality and then some.
(I’ve since tried both, seems to not matter in this case)
I am running on a port other than 5432 for this particular db instance. I have more than 1 postgres version running.
One of my next experiments will be to run on the 5432 default.
The only PG var I define is $PGDATA. I use full paths for all commands.
I've run through the examples in the documentation several times, but it seems that the WAL files just
won't get picked up by the defined date/time in recovery.conf.
I’m using cron to create time-named tables in the database (every 10 min) to see what times I’m getting back in in my recoveries.
They all retrieve from the backup, but don’t get the WAL files up to a time.
This is all in experiment mode for now, so there is no urgency.
But I have almost exhausted all testing permutations and combinations.
Does anyone have an idea?
Thanks.
Re: Unix Postgres 9.5. using pg_basebackup and WAL files. Can't get a PITR recovery
On Fri, Jul 15, 2016 at 10:09 AM, Pruett, Jennis <Jennis.Pruett@leidos.com> wrote: > I can't cut and paste my issues, so I will explain as best I can. > > > > I’m testing PITR on a single 9.5 db (no replication setups). > > > > I have all the settings that I can find, wal_level, archive_command, > > restore_command, directories defined, logs defined. My WAL files are stored > off in > > another area with gzip in the archive_command. The restore_command also uses > gunzip. Those work fine. > > (pg_xresetlog seems to work OK too) > > > > I can restore from a pg_basebackup, but if I define PITR in recovery.conf > along with the > > pg_basebackup in place, I can't get to that point in time. What does the server log file say? You should see something like this: 3071 2016-07-20 08:02:00.698 PDT LOG: database system was interrupted; last known up at 2016-07-14 12:59:39 PDT 3071 2016-07-20 08:02:04.503 PDT LOG: starting point-in-time recovery to 2016-07-19 13:50:00-07 3071 2016-07-20 08:02:04.872 PDT LOG: restored log file "00000002.history" from archive What do you see instead? Cheers, Jeff