[pgpool-general: 7388] Re: ERROR: executing recovery, execution of command failed at "1st stage"

Murat K srvrmurat at gmail.com
Sat Jan 9 20:47:12 JST 2021


Dear Bo,

This is my command on primary attempting to setup standby server;

-bash-4.2$ pcp_recovery_node -h 192.168.vip.vip -p 9898 -U pgpool -n 1
Password:

It gets stuck telling "Password:" at the command prompt.

Below is the postgre log on primary. There are many "Password:" lines.
Does pg_basebackup need an extra "-w" parameter?
Or what is wrong with it?

Thanks in advance,
murat

2021-01-09 13:56:39.707 +03 [2414] LOG:  received SIGHUP, reloading
configuration files
+ PRIMARY_NODE_PGDATA=/var/lib/pgsql/13/data
+ DEST_NODE_HOST=turhal
+ DEST_NODE_PGDATA=/var/lib/pgsql/13/data
+ PRIMARY_NODE_PORT=5432
+ DEST_NODE_ID=1
+ DEST_NODE_PORT=5432
++ hostname
+ PRIMARY_NODE_HOST=zile.tokat
+ PGHOME=/usr/pgsql-13
+ ARCHIVEDIR=/var/lib/pgsql/archivedir
+ REPLUSER=repl
+ echo recovery_1st_stage: start: pg_basebackup for Standby node 1
recovery_1st_stage: start: pg_basebackup for Standby node 1
+ ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null
postgres at turhal -i /var/lib/pgsql/.ssh/id_rsa_pgpool ls /tmp
Warning: Permanently added 'turhal,192.168.21.64' (ECDSA) to the list of
known hosts.
+ '[' 0 -ne 0 ']'
++ /usr/pgsql-13/bin/initdb -V
++ awk '{print $3}'
++ sed 's/\([0-9]*\)[a-zA-Z].*/\1/'
++ sed 's/\..*//'
+ PGVERSION=13
+ '[' 13 -ge 12 ']'
+ RECOVERYCONF=/var/lib/pgsql/13/data/myrecovery.conf
+ /usr/pgsql-13/bin/psql -p 5432
 pg_create_physical_replication_slot
-------------------------------------
 (turhal,)
(1 row)

++ echo /var/lib/pgsql/13/data/myrecovery.conf
++ sed -e 's/\//\\\//g'
++ echo /var/lib/pgsql/13/data/myrecovery.conf
++ sed -e 's/\//\\\//g'
+ ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null
postgres at turhal -i /var/lib/pgsql/.ssh/id_rsa_pgpool '

    set -o errexit

    rm -rf /var/lib/pgsql/13/data
    rm -rf /var/lib/pgsql/archivedir/*

    /usr/pgsql-13/bin/pg_basebackup -h zile.tokat -U repl -p 5432 -D
/var/lib/pgsql/13/data -X stream

    if [ 13 -ge 12 ]; then
        sed -i -e "\$ainclude_if_exists =
'\''\/var\/lib\/pgsql\/13\/data\/myrecovery.conf'\''"                -e
"/^include_if_exists =
'\''\/var\/lib\/pgsql\/13\/data\/myrecovery.conf'\''/d"
/var/lib/pgsql/13/data/postgresql.conf
    fi

    cat > /var/lib/pgsql/13/data/myrecovery.conf << EOT
primary_conninfo = '\''host=zile.tokat port=5432 user=repl
application_name=turhal passfile='\'''\''/var/lib/pgsql/.pgpass'\'''\'''\''
recovery_target_timeline = '\''latest'\''
restore_command = '\''scp zile.tokat:/var/lib/pgsql/archivedir/%f %p'\''
primary_slot_name = '\''turhal'\''
EOT

    if [ 13 -ge 12 ]; then
        touch /var/lib/pgsql/13/data/standby.signal
    else
        echo "standby_mode = '\''on'\''" >>
/var/lib/pgsql/13/data/myrecovery.conf
    fi

    sed -i "s/#*port = .*/port = 5432/"
/var/lib/pgsql/13/data/postgresql.conf
'
Warning: Permanently added 'turhal,192.168.21.64' (ECDSA) to the list of
known hosts.
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:

On Thu, Jan 7, 2021 at 5:35 PM Bo Peng <pengbo at sraoss.co.jp> wrote:

> Hi,
>
> On Wed, 6 Jan 2021 09:17:09 +0300
> Murat K <srvrmurat at gmail.com> wrote:
>
> > template1=# exit
> > -bash-4.2$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
> > ERROR:  extension "pgpool_recovery" already exists
> > -bash-4.2$
> >
> >
> > [root at zile ~]#  pcp_recovery_node -h 192.168.vip.vip -p 5432 -U pgpool
> -n 1
> > Password:
> > ERROR: unable to read data from socket.
>
> Is the port number 5432 specified in pcp_recovery_node command the
> PostgreSQL port number?
> You need to specify the pcp port number which is specified in pcp_port
> paramater.
>
> If "pcp_port = 9898", you need to execute pcp_recovery_node:
>
> pcp_recovery_node -h 192.168.vip.vip -p 9898 -U pgpool -n 1
>
> > [root at zile ~]# tail -10 /var/lib/pgsql/13/data/log/postgresql-Wed.log
> > 2021-01-06 08:54:25.465 +03 [29639] FATAL:  the database system is
> shutting
> > down
> > 2021-01-06 08:54:25.470 +03 [1446] LOG:  database system is shut down
> > 2021-01-06 08:54:47.739 +03 [1094] LOG:  starting PostgreSQL 13.1 on
> > x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
> > 4.8.5-44), 64-bit
> > 2021-01-06 08:54:47.739 +03 [1094] LOG:  listening on IPv4 address
> > "0.0.0.0", port 5432
> > 2021-01-06 08:54:47.739 +03 [1094] LOG:  listening on IPv6 address "::",
> > port 5432
> > 2021-01-06 08:54:47.739 +03 [1094] LOG:  listening on Unix socket
> > "/var/run/postgresql/.s.PGSQL.5432"
> > 2021-01-06 08:54:47.740 +03 [1094] LOG:  listening on Unix socket
> > "/tmp/.s.PGSQL.5432"
> > 2021-01-06 08:54:47.786 +03 [1292] LOG:  database system was shut down at
> > 2021-01-06 08:54:25 +03
> > 2021-01-06 08:54:47.913 +03 [1094] LOG:  database system is ready to
> accept
> > connections
> > 2021-01-06 09:01:14.477 +03 [1809] LOG:  invalid length of startup packet
> >
> > [root at zile ~]# tail -10 /var/log/pgpool_log/pgpool-2021-01-06_085447.log
> > 2021-01-06 08:54:59: pid 1148: LOG:  new IPC connection received
> > 2021-01-06 08:54:59: pid 1088: LOG:  watchdog cluster now holds the
> quorum
> > 2021-01-06 08:54:59: pid 1088: DETAIL:  updating the state of quarantine
> > backend nodes
> > 2021-01-06 08:54:59: pid 1148: LOG:  new IPC connection received
> > 2021-01-06 08:55:03: pid 1399: LOG:  successfully acquired the delegate
> > IP:"192.168.vip.vip"
> > 2021-01-06 08:55:03: pid 1399: DETAIL:  'if_up_cmd' returned with success
> > 2021-01-06 08:55:03: pid 1148: LOG:  watchdog escalation process with
> pid:
> > 1399 exit with SUCCESS.
> > 2021-01-06 08:55:04: pid 1148: LOG:  new outbound connection to
> turhal:9000
> > 2021-01-06 08:55:04: pid 1148: LOG:  new outbound connection to
> niksar:9000
> > 2021-01-06 08:56:38: pid 1354: LOG:  watchdog: lifecheck started
> > [root at zile ~]#
> >
> > Thanks
> >
> > On Wed, Jan 6, 2021 at 4:15 AM Bo Peng <pengbo at sraoss.co.jp> wrote:
> >
> > > Hi,
> > >
> > > On Tue, 5 Jan 2021 18:02:26 +0300
> > > Murat K <srvrmurat at gmail.com> wrote:
> > >
> > > > Hi All,
> > > >
> > > > I am implementing Pgpool-II + Watchdog Setup Example
> > > > <https://www.pgpool.net/docs/42/en/html/example-cluster.html> . I
> > > managed
> > > > to install pgpool 4.2.1  and run but having problems with online
> recovery
> > > > (pcp_recovery_node) and switchover/failover scenarios.
> > > >
> > > > # pcp_recovery_node -h vip.vip.vip.vip -p 9898 -U pgpool -n 1
> > > > Password:
> > > > ERROR:  executing recovery, execution of command failed at "1st
> stage"
> > > > DETAIL:  command:"recovery_1st_stage"
> > > >
> > > > I reviewed failover, follow primary, recovery_1st_stage scripts but
> could
> > > > not make it work.
> > > > Can somebody shed some light?
> > >
> > > Did you execute "CREATE EXTENSION ..." following this section?
> > >
> > >
> > >
> https://www.pgpool.net/docs/42/en/html/example-cluster.html#EXAMPLE-CLUSTER-PGPOOL-CONFIG-ONLINE-RECOVERY
> > >
> > > If you could share the pgpool log, I may figure out the cause.
> > >
> > > > Regards,
> > > > murat
> > >
> > >
> > > --
> > > Bo Peng <pengbo at sraoss.co.jp>
> > > SRA OSS, Inc. Japan
> > > _______________________________________________
> > > pgpool-general mailing list
> > > pgpool-general at pgpool.net
> > > http://www.pgpool.net/mailman/listinfo/pgpool-general
> > >
>
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS, Inc. Japan
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20210109/ee5cf598/attachment.htm>


More information about the pgpool-general mailing list