Unix Postgres 9.5. using pg_basebackup and WAL files. Can't get a PITR recovery

Started by Pruett, Jennisalmost 10 years ago2 messagesgeneral
Jump to latest
#1Pruett, Jennis
Jennis.Pruett@leidos.com

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.

#2Jeff Janes
jeff.janes@gmail.com
In reply to: Pruett, Jennis (#1)
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general