Re: base backup from the standby without pg_basebackup

Hello,

We had an issue with backing up one of our master servers, since a
base backup increased the load on the master, resulting in slowdowns
in the application working with that database. While there are ways to
overcome this problem by using nice on the backend process doing the
backup or throttling the network connection, we've decided to use a
less complicated route of producing a base backup from an almost idle
standby server.

We don't use pg_basebackup, but our own tool, which does the rsync +
compression and also symlinks some directories from pg_data (such as
pg_log or pg_xlog) to the locations on different partitions. So, the
question is, how would one make a base backup from the [async] standby
without using pg_basebackup. Our steps were the following:

- make sure WAL files from the master are archived and reachable on
the new replica host (via NFS).
- run pg_start_backup('label') on the master
- initialise a new cluster, rsync  files from the replica, create a
recovery.conf and so on (made by our tool)
- run pg_stop_backup() on the master.

The new replica did start and were restoring WAL files for a while,
but eventually we came across the PANIC message:

2015-03-18 19:10:52.943 CET,,,17293,,55083494.438d,922,,2015-03-17
15:05:08 CET,1/0,0,PANIC,XX000,"WAL contains references to invalid
pages",,,,,"xlog redo visible: rel 1663/16414/24453; blk 26569",,,,""

We did check the disk on that system (and now rechecking the memory),
but so far the hardware itself looks ok, which makes me wonder if the
procedure above is flawed? What would be the proper way to produce a
base backup from the standby without using pg_basebackup?

Both master and replicas are running PostgreSQL 9.3.5.

Kind regards,
--
Alexey Klyukin


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