[pgpool-general: 8954] Re: pgpool-II 4.3 relationship with postgresql-12 and archive_cleanup_command off the primary PostgreSQL system
Henri Pilon
henri_pilon at bell.net
Thu Nov 2 02:21:28 JST 2023
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]
Sent: November 1, 2023 5:22 AM
To: henri_pilon henri_pilon <henri_pilon at bell.net>
Cc: 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> 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>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/
More information about the pgpool-general
mailing list