Re: Hot_standby WAL archiving question - Mailing list pgsql-admin
| From | Keith |
|---|---|
| Subject | Re: Hot_standby WAL archiving question |
| Date | |
| Msg-id | CAHw75vvwuxPKjKyZpZH3Oia19t1fYG_yC_5gS6g12Wv7Fu8UyA@mail.gmail.com Whole thread Raw |
| In response to | Re: Hot_standby WAL archiving question (Peter Brunnengräber <pbrunnen@bccglobal.com>) |
| List | pgsql-admin |
On Mon, May 2, 2016 at 12:14 PM, Peter Brunnengräber <pbrunnen@bccglobal.com> wrote:
Hi Keith,
Thank you for the reply.
Ok, but so how can I safely clean up the pg_archive folder on my master?
Thank you!
-With kind regards,
Peter Brunnengräber----- Original Message -----
From: "Keith" <keith@keithf4.com>
To: "Peter Brunnengräber" <pbrunnen@bccglobal.com>
Cc: "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Monday, May 2, 2016 12:07:12 PM
Subject: Re: [ADMIN] Hot_standby WAL archiving question
On Mon, May 2, 2016 at 11:53 AM, Peter Brunnengräber < pbrunnen@bccglobal.com > wrote:
Hello all,
So I have been working on setting up an active/standby clustered postgresql 9.2 using corosync. The corosync documentation has me enable WAL archiving "archive_command = 'cp %p /db/data/postgresql/9.2/pg_archive/%f'"
I assume so that the secondary can catch up in async (log shipping) mode before going into sync (streaming) mode. But I am noticing as I push data through the DB, that my pg_archive keeps growing and I am worried about running out of disk space.
Per the postgresql documentation, I set "wal_keep_segments" but I believe this only effects the segments in the pg_xlog and not what the archive_command is processing.
So, since I am not keeping the archived WAL statements for long term use, is it safe to set "archive_cleanup_command = 'pg_archivecleanup /db/data/postgresql/9.2/pg_archive %r'" on the master? Also will the archive cleanup command even have effect? I only read about it being on slaves to clean-up what has already been processed.
Thank you!
-With kind regards,
Peter Brunnengräber
--
Sent via pgsql-admin mailing list ( pgsql-admin@postgresql.org )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
The archive_cleanup_command is something you set on the slave in the recovery.conf, not on the master. It will run whatever command you place there to remove WAL files that have been confirmed to have been replayed by the slave and are no longer needed by the slave itself.
Keith
If those WAL files in pg_archive on the master are not being used by a slave system, and you don't want to keep them around for point-in-time recovery (PITR) purposes, then I don't see a need to even do this at all. You're right that wal_keep_segments is for the pg_xlogs folder and those WAL files are what a slave uses to replay the master. Secondary WAL shipping using the archive_command can still be useful even with streaming replication in case the slave falls further behind than the master keeps WALs in pg_xlogs. But if you can get to 9.4+, replication slots can solve this issue much better.
I'm not sure what corosync is or why it would set something like this if the pg_archive folder is not available for the slave to see and manage. WAL archives outside the pg_xlogs folder are useless to a master unless you're also doing file system backups and need them for PITR. Maybe that's why? If so, you'll have to set up some sort of secondary process to clean up the WAL files. The pg_archivecleanup command can be used for this as well as a standalone program, but you have to feed it a specific WAL file to use as the oldest one to keep. Or you can figure out how long it takes your master to cleanup its pg_xlogs files then you can do a find/delete cronjob.
If you're doing filesystem backups (pg_basebackup or something like it), and you've got the space, I'd recommend keeping those WAL files around for at least a day or 2. Or if you can keep all the WAL files generated between backups, then you can perform PITR to any point in time between those backups, which can be very useful.
I'm not sure what corosync is or why it would set something like this if the pg_archive folder is not available for the slave to see and manage. WAL archives outside the pg_xlogs folder are useless to a master unless you're also doing file system backups and need them for PITR. Maybe that's why? If so, you'll have to set up some sort of secondary process to clean up the WAL files. The pg_archivecleanup command can be used for this as well as a standalone program, but you have to feed it a specific WAL file to use as the oldest one to keep. Or you can figure out how long it takes your master to cleanup its pg_xlogs files then you can do a find/delete cronjob.
If you're doing filesystem backups (pg_basebackup or something like it), and you've got the space, I'd recommend keeping those WAL files around for at least a day or 2. Or if you can keep all the WAL files generated between backups, then you can perform PITR to any point in time between those backups, which can be very useful.
Keith
pgsql-admin by date: