[pgpool-general: 7872] Re: pgpool and postgresql 13

[Jon SCHEWE]

> 
> On Tue, 9 Nov 2021 18:01:08 +0000
> Jon SCHEWE <jon.schewe at raytheon.com> wrote:
> 
> > I'm new to pgpool and using it to setup a high availability postgresql cluster. I have 3 systems, each one running pgpool and postgresql. I'm using pgpool 4.1 and postgresql 13 with streaming replication. I have most everything working, except failover across the postgresql servers. I see that in postgresql versions 12 and higher the file recover.conf file is no longer read. The scripts for pgpool creates myrecovery.conf instead. A couple of questions about this config
> > 
> > 1. Should I be modifying my postgresql.conf file to include myrecovery.conf? I haven't found this referenced in the documentation.
> > 2. Should I be putting my primar_conninfo parameters in this file instead of postgresql.conf?
> 
> Are you using the scripts included in Pgpool-II 4.1 RPM?
> 
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_master.sh.sample;hb=refs/heads/V4_1_STABLE
> 
> If you are using the follow_master.sh.sample scripts included in Pgpool-II,
> the script will automatically modify postgresql.conf to include myrecovery.conf.
> 
> ======================
>             if [ ${PGVERSION} -ge 12 ]; then
>                 sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
>                        -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${NODE_PGDATA}/postgresql.conf
>                 touch ${NODE_PGDATA}/standby.signal
>             else
>                 echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
> ======================

Yes, I'm using the scripts from the RPM. However this script only runs on failure, so I need to have my system setup initially in a way that is compatible with these scripts.
So I added the following to the bottom of my postgresql.conf
include_if_exists = 'myrecovery.conf'

and then created myrecovery.conf with 
primary_conninfo = '...'
recovery_target_timeline = 'latest'
restore_command = '...'
primary_slot_name = '...'

I believe this will make my initial startup compatible with the failover scripts of pgpool. Am I correct? If this is correct, I'm thinking that this should be mentioned in the documentation as the streaming replication of postgresql won't work without it or may be incompatible with the pgpool scripts.

I also noticed that the hostname is used for the primary slot name and this isn't necessarily a valid identifier for a replication slot. I've added the following variable to follow_master.sh and used it where a replication slot name is needed.
FAILED_NODE_HOST_SLOT_NAME=$(echo ${FAILED_NODE_HOST} | awk -F. '{print $1}' | sed -e 's/-/_/')