[pgpool-general: 8956] Re: pgpool-II 4.3 relationship with postgresql-12 and archive_cleanup_command off the primary PostgreSQL system

Thu Nov 2 04:10:39 JST 2023

Thanks for the update, I  just wanted to confirm I was not missing something obvious that should be address automatically either with PGPOOL or PostgreSQL itself.  

Henri 

-----Original Message-----
From: pgpool-general [mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Wolf Schwurack
Sent: November 1, 2023 1:51 PM
To: Henri Pilon <henri_pilon at bell.net>; 'Bo Peng' <pengbo at sraoss.co.jp>
Cc: pgpool-general at pgpool.net
Subject: [pgpool-general: 8955] Re: pgpool-II 4.3 relationship with postgresql-12 and archive_cleanup_command off the primary PostgreSQL system

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>

_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general