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
upgradeof the postgresql version used, and we had intended to also modify the way backups were being done, by moving
froma 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
readingthe 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
inthe pg_wal directory containing the files from the original server; the second does not, but it *does* have the same
checksumas the file 000000010000000000000001 in that directory (which does not exist in the pg_xlog directory on the
off-siteserver).
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-levelcopy of all the files in the postgresql database directory? If not, any hints on what I did wrong?
Thanks,
--
Wouter Verhelst
