Hi All.
I have a slave that was using streaming replication, and all was well,
but its far away (remote data center), and its using too much bandwidth
at peak times.
I'm switching it to wal shipping at night. I don't really need it
constantly up to date, nightly is good enough.
In my recovery.conf, I disabled the primary_conninfo, and have:
restore_command = 'cp /pub/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /pub/archive/ %r'
I restart PG, and all seems well. It reached a consisten state and
allows read-only queries. Cool. Except its spamming the log with lines
like:
Sep 29 15:28:13 webserv postgres[17735]: [125-1] ,,2014-09-29
15:28:13.329 CDT,: LOG: restored log file "00000002000000B900000015"
from archive
Sep 29 15:28:18 webserv postgres[17735]: [126-1] ,,2014-09-29
15:28:18.358 CDT,: LOG: restored log file "00000002000000B900000015"
from archive
Sep 29 15:28:23 webserv postgres[17735]: [127-1] ,,2014-09-29
15:28:23.387 CDT,: LOG: restored log file "00000002000000B900000015"
from archive
The answer to that, I thought was pg_standby. So I changed:
restore_command = 'pg_standby -d /pub/archive %f %p 2>>/tmp/standby.log'
I restart PG, but now it never reaches consistency, wont allow queries,
and /tmp/standbylog show's its trying to start with an old file:
Keep archive history: no cleanup required
running restore: OK
Trigger file: <not set>
Waiting for WAL file: 00000002000000B900000004
WAL file path: /pub/archive/00000002000000B900000004
Restoring to: pg_xlog/RECOVERYXLOG
Sleep interval: 5 seconds
Max wait interval: 0 forever
Command for restore: cp "/pub/archive/00000002000000B900000004"
"pg_xlog/RECOVERYXLOG"
Keep archive history: no cleanup required
WAL file not present yet.
00000002000000B900000004 doesn't exist, I've already moved past it.
(Also, note, the first log I posted above accessing
00000002000000B900000015 is much later after me messing around some more)
I understand why cp is spamming the log, I guess, but I don't understand
why I cant just replace it with pg_standby.
Any hints on how to set this up?
Thanks for your time.
-Andy