[pgpool-general: 2573] Re: pgpool_recovery failed

[Tatsuo Ishii]

Pgpool-II 2.2.2 is too old and 2.x are not maintained anymore. Please
upgrade to the latest version.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi All,
> 
> I'm just a beginner with pgpool, and I'm not able to recover a node
> using pgpool recovery.
> 
> SO: Red Hat 6.4
> pgpool version: pgpool-II-2.2.2
> postgresSQL: 8.4.18
> 
> I'm using the user postgres also in pgpool.conf file as recovery_user
> just to simplify.
> 
> When I execute:
> postgres$ /opt/pgpool2/bin/pcp_recovery_node 20 postgres1 9898
> postgres postgres 1
> BackendError
> 
> taking a look at the logs:
> 
> pgpool log:
> 
> 2014-02-12 14:44:16 DEBUG: pid 31401: I am PCP 31401 accept fd 5
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: received PCP packet
> type of service 'M'
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: salt sent to the
> client
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: received PCP packet
> type of service 'R'
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: authentication OK
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: received PCP packet
> type of service 'O'
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: start online recovery
> 2014-02-12 14:44:16 LOG:   pid 31401: starting recovering node 1
> 2014-02-12 14:44:16 DEBUG: pid 31401: exec_checkpoint: start
> checkpoint
> 2014-02-12 14:44:16 DEBUG: pid 31401: exec_checkpoint: finish
> checkpoint
> 2014-02-12 14:44:16 LOG:   pid 31401: CHECKPOINT in the 1st stage done
> 2014-02-12 14:44:16 LOG: pid 31401: starting recovery command: "SELECT
> pgpool_recovery('copy_base_backup', '10.164.4.223',
> '/var/lib/pgsql/data')"
> 2014-02-12 14:44:16 DEBUG: pid 31401: exec_recovery: start recovery
> 2014-02-12 14:44:16 ERROR: pid 31401: exec_recovery: copy_base_backup
> command failed at 1st stage
> 2014-02-12 14:44:16 DEBUG: pid 31401: exec_recovery: finish recovery
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: received PCP packet
> type of service 'X'
> 2014-02-12 14:44:16 DEBUG: pid 31401: pcp_child: client
> disconnecting. close connection
> 
> 
> postgres log:
> 
> LOG:  connection received: host=10.164.4.222 port=57182
> LOG:  connection authorized: user=postgres database=template1
> LOG:  statement: CHECKPOINT
> LOG: statement: SELECT pgpool_recovery('copy_base_backup',
> '10.164.4.223', '/var/lib/pgsql/data')
> sh: /var/lib/pgsql/data/copy_base_backup: Permission denied
> ERROR:  pgpool_recovery failed
> STATEMENT: SELECT pgpool_recovery('copy_base_backup', '10.164.4.223',
> '/var/lib/pgsql/data')
> LOG: disconnection: session time: 0:00:00.113 user=postgres
> database=template1 host=10.164.4.222 port=57182
> 
> It seems a permission issue, however I have set the script
> copy_base_backup's permission to 777, just in case and if I run this
> script directly everything goes right:
> 
> postgres$ $ ./copy_base_backup . 10.164.4.223 /var/lib/pgsql/data/
> #! /bin/sh -xvvv
> 
> DATA=$1
> + DATA=.
> RECOVERY_TARGET=$2
> + RECOVERY_TARGET=10.164.4.223
> echo "$RECOVERY_TARGET"
> + echo 10.164.4.223
> 10.164.4.223
> RECOVERY_DATA=$3
> + RECOVERY_DATA=/var/lib/pgsql/data/
> echo "$RECOVERY_DATA"
> + echo /var/lib/pgsql/data/
> /var/lib/pgsql/data/
> 
> psql -c "select pg_start_backup('pgpool-recovery')" postgres
> + psql -c 'select pg_start_backup('\''pgpool-recovery'\'')' postgres
>  pg_start_backup
> -----------------
>  0/21000020
> (1 row)
> 
> echo "restore_command = 'scp
> $HOSTNAME:/var/lib/pgsql/data/pg_xlog_archive/%f %p'" >
> /var/lib/pgsql/data/recovery.conf
> + echo 'restore_command = '\''scp
> postgres1.corenetworks.es:/var/lib/pgsql/data/pg_xlog_archive/%f
> %p'\'''
> tar -C /var/lib -zcf pgsql.tar.gz pgsql
> + tar -C /var/lib -zcf pgsql.tar.gz pgsql
> tar: pgsql/data/pgsql.tar.gz: file changed as we read it
> 
> psql -c 'select pg_stop_backup()' postgres
> + psql -c 'select pg_stop_backup()' postgres
>  pg_stop_backup
> ----------------
>  0/21000088
> (1 row)
> 
> scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA
> + scp pgsql.tar.gz 10.164.4.223:/var/lib/pgsql/data/
> pgsql.tar.gz 100%   59MB  29.6MB/s   00:02
> 
> And I got copy of pgsql.tar.gz directory both in local and remote
> nodes, so is not a ssh problem.
> 
> Additionally when the SELECT sentence is executed:
> postgres$ psql -h postgres1 -p 9999 -U postgres -d template1
> 
> template1=# SELECT pgpool_recovery('copy_base_backup', '10.164.4.223',
> '/var/lib/pgsql/data');
> ERROR:  pgpool_recovery failed
> 
> 
> Any help would be really appreciated,
> kind regards,
> Cristina
> 
> 
> 
>