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.
wal_level = hot_standby checkpoint_segments = 8 max_wal_senders = 4 wal_keep_segments = 8
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_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.
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?