[pgpool-general: 8955] Re: pgpool-II 4.3 relationship with postgresql-12 and archive_cleanup_command off the primary PostgreSQL system
Wolf Schwurack
u0150482 at umail.utah.edu
Thu Nov 2 02:50:48 JST 2023
Hey Henri
I manage the archive directory with my backups. I use a backup script that after a backup is done it removes the old wal files in the archive directory.
Wolf
On 11/1/23, 11:22 AM, "pgpool-general on behalf of Henri Pilon" <pgpool-general-bounces at pgpool.net <mailto:pgpool-general-bounces at pgpool.net> on behalf of henri_pilon at bell.net <mailto:henri_pilon at bell.net>> wrote:
Hi Bo ,
I would agree with you views as the ../data/pg_wal folder is being manage from a disk / folder point of view by postgresql internally.
My question is more related to the PGPOOL-II setup and the additional folder requirement called archivedir reflected in the postgresql.conf (archive_command = 'test ! -f /var/lib/pgsql/archivedir/%f && cp %p /var/lib/pgsql/archivedir/%f' ) to copy the wal files outside of the /data/pg_wal folder to the archivedir that maybe use by the standby systems recover ( PGPOOL-II creation of the myrecovery.conf file) aspect.
How are other folks managing the "archivedir" folder in their production environment?
Thanks,
Henri
-----Original Message-----
From: Bo Peng [mailto:pengbo at sraoss.co.jp <mailto:pengbo at sraoss.co.jp>]
Sent: November 1, 2023 5:22 AM
To: henri_pilon henri_pilon <henri_pilon at bell.net <mailto:henri_pilon at bell.net>>
Cc: pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
Subject: Re: [pgpool-general: 8952] pgpool-II 4.3 relationship with postgresql-12 and archive_cleanup_command off the primary PostgreSQL system
Hi,
Sorry for the late response.
> I did several google search and unable to find nay relevant
> information around this subject in relation to pgpool-ii.
>
> What’s the proper way of managing the archivedir space off the primary
> system?
>
> What’s the best practice for this area and any guidance would be
> appreciated ?
I think this is a question regarding postgresql.
Pgpool-II does not have a function to delete old WALs.
You can delete the old WALs periodically, for example using cron.
On Mon, 23 Oct 2023 10:04:08 -0400 (EDT) henri_pilon henri_pilon <henri_pilon at bell.net <mailto:henri_pilon at bell.net>> wrote:
>
> My apologies if this question has been asked before , and looking for
> some guidance.
>
> Configuration - 3 nodes cluster (primary and 2 standby systems)
> with pgpool-II 4.3 and PostgreSQL 12
>
> postgresql.conf off each systems.
>
> checkpoint_timeout = 30min
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> wal_level = replica
> password_encryption = md5
> archive_mode = on
> archive_command = 'test ! -f /var/lib/pgsql/archivedir/%f && cp %p
> /var/lib/pgsql/archivedir/%f'
> hot_standby = on
> max_wal_senders = 10
> wal_keep_segments = 10
> port = 5432
> wal_log_hints = on
> restore_command = 'cp /var/lib/pgsql/archivedir/%f %p'
> archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/archivedir
> %r'
> include_if_exists = '/var/lib/pgsql/12/data/myrecovery.conf'
>
> I understand the pgpool-II myrecovery.conf overwriting the
> restore_command off each standby systems.
> physical slot replication is being utilize across the 2 standby
> systems, letting pgpool-II perform this configuration with the
> appropriate pcp command
>
> Our issue is that off the primary server the dedicate disk reserve for
> postgres keeps filling up, especially the /var/lib/pgsql/archivedir
> folder.
>
> My understanding the archive_cleanup_command action is only perform
> off the standby systems.
>
> The way we are currently are managing the issue is by periodically
> performinf a pg_basebackup off the primary system to a backup folder
> and then performinnf the archive_cleanup_command manually looking for
> the .backup file in the archivedir folder.
>
> #! /bin/sh
> export PATH=/usr/local/pgsql/bin:${PATH}
> ARCHDIR=/usr/local/pgsql/archive LASTWALPATH=`/bin/ls
> $ARCHDIR/*.backup | /bin/sort –r | /usr/bin/head -1` if [ $LASTWALPATH
> = '' ]; then echo 'NO label file found.'
> exit 1
> fi
> LASTWALFILE=`/bin/basename $LASTWALPATH` pg_archivecleanup $ARCHDIR
> $LASTWALFILE stat=$?
> echo 'Archivelog cleanup complete'
> exit $stat
>
> I did several google search and unable to find nay relevant
> information around this subject in relation to pgpool-ii.
>
> What’s the proper way of managing the archivedir space off the primary
> system?
>
> What’s the best practice for this area and any guidance would be
> appreciated ?
>
> Thanks,
>
> Henri
--
Bo Peng <pengbo at sraoss.co.jp <mailto:pengbo at sraoss.co.jp>>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/ <https://www.sraoss.co.jp/>
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general <http://www.pgpool.net/mailman/listinfo/pgpool-general>
More information about the pgpool-general
mailing list