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

[Bo Peng]

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/