My setup
I've just set up streaming replication in postgres for the first time (9.3.5), and while the streaming is working as I expect, I'm struggling to get my standby to run the archive_command so I can store all of the log files.
Master postgres.conf:
wal_level = hot_standby
checkpoint_segments = 8
max_wal_senders = 4
wal_keep_segments = 8
Standby postgres.conf:
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
archive_command = 'test ! -f /backup/postgres_archive/constant/%f && cp %p /backup/postgres_archive/constant/%f'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Standby recovery.conf:
standby_mode = 'on'
primary_conninfo = 'host=my-host.example.com port=5432 user=replicator password=my_password sslmode=require'
restore_command = 'cp /backup/postgres_archive/constant/%f %p'
trigger_file = '/tmp/postgresql.trigger'
The permissions on the folder I'm trying to write into are correct and archive_command works fine when I run it manually as the user postgres is running as. To be sure I tried changing the archive command to a simple touch (again, tested fine as the postgres user), but it didn't make any difference.
Things that are working
My DB is still in its relative infancy so there's not much load on it at all. To this end I'm just simulating it by writing random data into a test table. After I commit on the master I can see the changes appearing straight away in the standby so I'm happy with that.
One thing I don't quite understand is that the WAL files are the standby and the master are slightly different, but it almost looks as though one or other has provisioned a WAL that it hasn't started writing to yet (which isn't on the other). Is that normal?
If I do select pg_switch_xlog()
on the master and then write some more both the master and standby seem to switch and start writing to the next/same WAL file. So that mirrors my understanding of what's happening.
Help
I have a few questions about all this. I have read through every page of the postgres manuals regarding this but I couldn't find anything to help in my particular case.
I tried finding some way of getting postgres to show me more information about what it might be doing / not doing in the log files but it didn't come up with anything useful. In debugging this, what should I change in the config to get as much useful information into the logs as possible?
In terms of when the log archiving would run, I guess because the master is sort of controlling which WAL file is active it's effectively the trigger for when the log shipping should run on the standby. Is that correct?
The streaming replication all seems to work as I expect but trying to get log shipping running on the standby doesn't appear to even try. What am I doing wrong?
archive_command
on the standby, rather than the master?restore_command
to catch up if the master has removed WAL the standby still requires. So in general it doesn't make any sense to have the replica archive WAL received by streaming.wal_keep_segments
is set, in which case it keeps WAL for bit longer then deletes it. The purpose of WAL archiving is (a) to allow point in time recovery from a base backup; and (b) to allow a replica to read and replay the archived WAL, either as its only way of following the master, or in case it gets too far behind to stream data from the master. In general the master should be archiving WAL and the replica, even if streaming, should be set up to replay it if needed.