1

We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.

We created few databases in master and it is getting replicated to slave servers. Our Postgresql version is 9.4 and data directory is "/var/lib/pgsql/9.4/data" in both master and slave.

While setting up streaming replication, we edit postgresql.conf in master

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.4/archive/%f'

Our recovery.conf in slave is

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=replication password=password'
trigger_file = '/var/lib/pgsql/9.4/trigger'
restore_command = 'cp /var/lib/pgsql/9.4/archive/%f "%p"'

I also added below command to clear the old archive in recovery.conf in slave

archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.4/archive %r'

but I started seeing below error in pg_log of slave.

< 2019-01-16 09:02:48.937 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2
pg_archivecleanup: archive location "/var/lib/pgsql/9.4/archive" does not exist
< 2019-01-16 09:07:45.927 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2

After doing some research I realized that problem is genuine as the slave is not having this directory "/var/lib/pgsql/9.4/archive" and its present in master server only.

Now, we removed "archive_cleanup_command" from recovery.conf (slave) but in our master server "/var/lib/pgsql/9.4/archive" is taking so much of space. We need to clear these old files inside "archive".

I had few doubts and wanted to take some suggestions to resolve the problem :-

  1. Can you suggest what can be done so that we don't run into every day "No space left in /var of the master" issue ? Can we run the pg_archivecleanup from master server and remove the old files like below
pg_archivecleanup -d archive 000000010000003700000010.00000020.backup
pg_archivecleanup:  keep WAL file "archive/000000010000003700000010" and later
pg_archivecleanup:  removing file "archive/00000001000000370000000F"
pg_archivecleanup:  removing file "archive/00000001000000370000000E"
  1. Should I setup streaming replication again with some mount point attached to both master and slave and save the archive of master to "mount point" and then add "archive_cleanup_command" in recovery.conf?
  • If you don't do the second, you can still rsync the WAL files, but having a common mount point is possibly easier. Also, read postgresql.org/docs/9.4/pgarchivecleanup.html carefully before running pg_archivecleanup manually. Finally, consider using a fresher version, 9.4 is currently the oldest still in support. – dezso Jan 16 at 16:41
0

Does the archive exist only to support the streaming replication, or do you want it for other reasons like PITR? (Since you attempted to configure pg_archivecleanup in the first place, I guess I can assume you don't want it for other reasons...)

Because your restore_command is currently broken, then you know that the WAL archive has not been needed thus far to support streaming replication--because if it had been needed it would not have worked. That is, your replica has never fallen far enough behind that the master has removed the WAL from pg_wal before the replica had fetched it. If you think that this will always continue to be the case, then you just turn achive_mode = off and delete your current archive. There is no point in maintaining an archive that is not and cannot be used.

Since the introduction of replication slots, pg_archivecleanup is more-or-less obsolete. If you want a backstop in case the replica doesn't naturally keep up, you can make a shared mount point and use pg_archivecleanup on that mount point if you want. But configuring a replication slot is a better way to accomplish the same thing.

  • Hello jjanes,We only wanted to setup streaming replication. I followed the video tutorial([link]youtube.com/watch?v=XFp8wHNtP3s&vl=en). As per your suggestion, I will turn off archive_mode=off (I guess this is only change needs to be done in postgresql.conf in master and restart postgresql-9.4 services. Can you suggest if service restart is required, do we need to do any changes in slave conf files also?). – KSingh Jan 17 at 14:04
  • Also, for recovery purpose, I will write a script which will be scheduled as cronjob which will take pg_dump and pg_dumpall backup of databases on weekly basis(pg_dump) and twice in a week(pg_dump). This will be used to recover database/restore database of old days. – KSingh Jan 17 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.