[pgpool-general: 2572] pgpool_recovery failed

[Cristina Valderas]

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




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20140212/606831db/attachment.htm>