<div dir="ltr"><br><div class="gmail_quote"><div dir="ltr"><div>Postgres version: 12.2</div><div>pgpool-II version: 4.1.4 (karasukihoshi)</div><div>OS: RHEL 7</div><div><br></div><div>I have two instances of Postgres and one instance of pgpool-II running. I am following the tutorial here to set up HA for postgres. <a href="https://www.pgpool.net/docs/41/en/html/example-cluster.html" target="_blank">https://www.pgpool.net/docs/41/en/html/example-cluster.html</a></div><div><br></div><div>I have created the required users (pgpool, repl), enabled passwordless SSH, configured pgpool to use streaming replication and brought up the primary postgres server. I am able to connect to pgpool via port 9999 and show pool_nodes shows both postgres instances. <br></div><div><br></div><div>The primary's status is shown as up and the standby is shown as down as expected. Following the next steps to setup a standby server using online recovery mechanism fails with "command failed at 1st stage".</div><div><br></div><div>Please find the Postgres log from the primary instance below.</div><div>One thing I observed is that the PRIMARY_NODE_HOST value is empty and due to which pg_basebackup and psql commands are failing because -h has no value.</div><div><br></div><div>Why would the value for PRIMARY_NODE_HOST go missing while all other variables have values?<br></div><div><br></div><div>+ PRIMARY_NODE_PGDATA=/var/lib/pgsql/12/data<br>+ DEST_NODE_HOST=<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT110_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span><br>+ DEST_NODE_PGDATA=/var/lib/pgsql/12/data<br>+ PRIMARY_NODE_PORT=5432<br>+ DEST_NODE_ID=1<br>+ DEST_NODE_PORT=15432<br>+ PRIMARY_NODE_HOST=<br>+ PGHOME=/var/lib/pgsql/12<br>+ PGBIN=/usr/pgsql-12<br>+ ARCHIVEDIR=/var/lib/pgsql/12/archivedir<br>+ REPLUSER=repl<br>+ REPL_SLOT_NAME=172_16_2_149<br>+ POSTGRESQL_STARTUP_USER=postgres<br>+ SSH_KEY_FILE=id_rsa_pgpool<br>+ SSH_OPTIONS='-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/id_rsa_pgpool'<br>+ echo recovery_1st_stage: start: pg_basebackup for Standby node 1<br>recovery_1st_stage: start: pg_basebackup for Standby node 1<br>+ ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i '~/.ssh/id_rsa_pgpool' postgres@<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT111_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span> ls /tmp<br>Warning: Permanently added '<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT112_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span>' (ECDSA) to the list of known hosts.<br>+ '[' 0 -ne 0 ']'<br>++ /usr/pgsql-12/bin/initdb -V<br>++ awk '{print $3}'<br>++ sed 's/\([0-9]*\)[a-zA-Z].*/\1/'<br>++ sed 's/\..*//'<br>+ PGVERSION=12<br>+ '[' 12 -ge 12 ']'<br>+ RECOVERYCONF=/var/lib/pgsql/12/data/myrecovery.conf<br>+ /usr/pgsql-12/bin/psql -h -p 5432 postgres -c 'SELECT pg_create_physical_replication_slot('\''172_16_2_149'\'');'<br>+ '[' 2 -ne 0 ']'<br>+
echo ERROR: recovery_1st_stage: create replication slot
'"172_16_2_149"' failed. You may need to create replication slot
manually.<br>ERROR: recovery_1st_stage: create replication slot "172_16_2_149" failed. You may need to create replication slot manually.<br>++ echo /var/lib/pgsql/12/data/myrecovery.conf<br>++ sed -e 's/\//\\\//g'<br>++ echo /var/lib/pgsql/12/data/myrecovery.conf<br>++ sed -e 's/\//\\\//g'<br>+ ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i '~/.ssh/id_rsa_pgpool' postgres@<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT113_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span> '<br><br> set -o errexit<br><br> [ -d "/var/lib/pgsql/12/data" ] && rm -rf /var/lib/pgsql/12/data<br> [ -d "/var/lib/pgsql/12/archivedir" ] && rm -rf /var/lib/pgsql/12/archivedir/*<br><br> /usr/pgsql-12/bin/pg_basebackup -h -U repl -p 5432 -D /var/lib/pgsql/12/data -X stream<br><br> cat > /var/lib/pgsql/12/data/myrecovery.conf << EOT<br>primary_conninfo = '\''host= port=5432 user=repl application_name=<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT114_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span> passfile='\'''\''/var/lib/pgsql/.pgpass'\'''\'''\''<br>recovery_target_timeline = '\''latest'\''<br>restore_command
= '\''scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null
-i ~/.ssh/id_rsa_pgpool :/var/lib/pgsql/12/archivedir/%f %p'\''<br>primary_slot_name = '\''172_16_2_149'\''<br>EOT<br><br> if [ 12 -ge 12 ]; then<br>
sed -i -e "\$ainclude_if_exists =
'\''\/var\/lib\/pgsql\/12\/data\/myrecovery.conf'\''" -e
"/^include_if_exists =
'\''\/var\/lib\/pgsql\/12\/data\/myrecovery.conf'\''/d"
/var/lib/pgsql/12/data/postgresql.conf<br> touch /var/lib/pgsql/12/data/standby.signal<br> else<br> echo "standby_mode = '\''on'\''" >> /var/lib/pgsql/12/data/myrecovery.conf<br> fi<br>
sed -i -e "s/#*port = .*/port = 15432/" -e
"s@#*archive_command = .*@archive_command = '\''cp \"%p\"
\"/var/lib/pgsql/12/archivedir/%f\"'\''@" /var/lib/pgsql/12/data/postgresql.conf<br>'<br>Warning: Permanently added '<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT115_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span>' (ECDSA) to the list of known hosts.<br>pg_basebackup: error: too many command-line arguments (first is "repl")<br>Try "pg_basebackup --help" for more information.<br>+ '[' 1 -ne 0 ']'<br>+ /usr/pgsql-12/bin/psql -h -p 5432 postgres -c 'SELECT pg_drop_replication_slot('\''172_16_2_149'\'');'<br>+ '[' 2 -ne 0 ']'<br>+ echo ERROR: recovery_1st_stage: drop replication slot '"172_16_2_149"' failed. You may need to drop replication slot manually.<br>ERROR: recovery_1st_stage: drop replication slot "172_16_2_149" failed. You may need to drop replication slot manually.<br>+ echo ERROR: recovery_1st_stage: end: pg_basebackup failed. online recovery failed<br>ERROR: recovery_1st_stage: end: pg_basebackup failed. online recovery failed<br>+ exit 1<br><span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT116_com_zimbra_phone"><a href="callto:2023-03-29%2014" target="_blank">2023-03-29 14</a></span>:11:26.654 EDT [7708] ERROR: pgpool_recovery failed<br><span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT117_com_zimbra_phone"><a href="callto:2023-03-29%2014" target="_blank">2023-03-29 14</a></span>:11:26.654 EDT [7708] STATEMENT: SELECT pgpool_recovery('recovery_1st_stage', '<span role="link" id="m_-7549961554436300625gmail-OBJ_PREFIX_DWT118_com_zimbra_phone"><a href="callto:172.16.2.149" target="_blank">172.16.2.149</a></span>', '/var/lib/pgsql/12/data', '5432', 1, '15432')</div><div><div><div><br></div></div></div></div>
</div><br clear="all"><br><span class="gmail_signature_prefix">-- </span><br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><font size="1"><span style="color:rgb(0,0,0);background-color:rgb(153,102,51)"><span style="background-color:rgb(255,255,255)"><div><font size="2"><span style="color:rgb(0,0,0);background-color:rgb(153,102,51)"><span style="background-color:rgb(255,255,255)">Thanks & Regards,</span></span></font></div><div><font size="2"><span style="color:rgb(0,0,0);background-color:rgb(153,102,51)"><span style="background-color:rgb(255,255,255)">Prasanna Venkadesh.</span></span></font></div><div><font size="1"><span style="color:rgb(0,0,0);background-color:rgb(153,102,51)"><span style="background-color:rgb(255,255,255)"><br></span></span></font></div></span></span></font></div></div></div>