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

[Henri Pilon]

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/