Re: Hot_standby WAL archiving question

On Mon, May 2, 2016 at 12:14 PM, Peter Brunnengräber <pbrunnen(at)bccglobal(dot)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(at)keithf4(dot)com>
> To: "Peter Brunnengräber" <pbrunnen(at)bccglobal(dot)com>
> Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)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(at)bccglobal(dot)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(at)postgresql(dot)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.

Keith