Re: Initiating log shipping backups when the initial filesystem-level backup went out of sync

My experienc is that rsync is the way to go when a replicant gets stale -- you
can at least be certain you didn't miss anything and it will take much less
time than the initial rsync since it only takes things that are new.

Hth,
/r/b

On Monday, September 5, 2016, Wouter Verhelst <
wouter(dot)verhelst(dot)ext(at)huawei(dot)com> wrote:

> > Hi,
> >
> > A while back, I had to replace the hardware for a postgres server
> > containing about 600G of data. While at it, we did an upgrade of the
> > postgresql version used, and we had intended to also modify the way
> > backups were being done, by moving from a daily pg_dumpall in cron to
> > shipping WAL logs to an offsite location.
> >
> > Due to the many changes involved, I decided that it would be easier to
> > copy the data by running "ssh oldserver pg_dumpall | psql postgres",
> > but I did (approximately) the following:
> >
> > - Create the cluster
> > - Set "wal_level = hot_standby", "archive_mode = on", and
> > "archive_command = 'test ! -f /srv/pg_wal/%f && cp %p /srv/pg_wal/%f'
> > in postgresql.conf.
> > - Set up everything so that files written to /srv/pg_wal end up
> > (eventually) on the remote server.
> > - rsync the data directory to the off-site server
> > - Start the server
> > - Run the pg_dumpall thing
> > - Start the server on the off-site location, to test if it could open
> > everything, and stop it again
> > - Create a file "recovery.conf" in the wrong location (at first) and
> > then in the correct location (later on, after reading the documentation
> > more carefully), with a line saying "restore_command = 'cp
> > /srv/pg_wal/%f "%p"'" and one saying "standby_mode = on"
> > - Start the server
> >
> > At this point, the log file shows the following message:
> >
> > 2016-08-29 13:53:22 CEST [25504-1] LOG: database system was shut down
> > in recovery at (...)
> > 2016-08-29 13:53:22 CEST [25504-2] LOG: entering standby mode
> > 2016-08-29 13:53:22 CEST [25504-3] LOG: record with zero length at
> > 0/2000200
> > 2016-08-29 13:53:22 CEST [25504-4] LOG: invalid primary checkpoint
> > record
> > 2016-08-29 13:53:22 CEST [25504-5] LOG: record with zero length at
> > 0/2000198
> > 2016-08-29 13:53:22 CEST [25504-6] LOG: invalid secondary checkpoint
> > record
> > 2016-08-29 13:53:22 CEST [25504-7] PANIC: could not locate a valid
> > checkpoint record
> > 2016-08-29 13:53:22 CEST [25480-1] LOG: startup process (PID 25504)
> > was terminated by signal 6: Aborted
> > 2016-08-29 13:53:22 CEST [25480-2] LOG: aborting startup due to
> > startup process failure
> >
> > In the pg_xlog directory on the off-site server, there is a file
> > 000000010000000000000002 and one 000000010000000000000003. When I run
> > md5sum, I find that the first has the same checksum as the one with the
> > same name in the pg_wal directory containing the files from the
> > original server; the second does not, but it *does* have the same
> > checksum as the file 000000010000000000000001 in that directory (which
> > does not exist in the pg_xlog directory on the off-site server).
> >
> > I have retained all WAL files from 000000010000000000000001 all the way
> > to the current one.
> >
> > Is it still possible for me to load these WAL files into the server at
> > the remote site, without having to do a filesystem-level copy of all
> > the files in the postgresql database directory? If not, any hints on
> > what I did wrong?
> >
> > Thanks,
>
> Ping?
>
> Is this possible, or should I spend time to rsync files over?
>
> Thanks,
>
> --
> Wouter Verhelst
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org
> <javascript:;>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
--
Robert W. Burgholzer
'Making the simple complicated is commonplace; making the complicated
simple, awesomely simple, that's creativity.' - Charles Mingus
Athletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
Wine: http://reesvineyard.wordpress.com/