[pgpool-general: 2599] Fwd: Re: pgpool-wd problems adding a third backend node

[Attila Heidrich]

I'm sorry, I forgot to reply the list!

---------- Forwarded message ----------
From: Attila Heidrich <attila.heidrich at gmail.com>
Date: 2014-03-03 14:37 GMT+01:00
Subject: Re: [pgpool-general: 2561] Re: pgpool-wd problems adding a third
backend node
To: Yugo Nagata <nagata at sraoss.co.jp>


Thanks a lot, the tutorial is really great and clean.

I use load_balace mode, not master/slave mode, but this should not change
very much of my problem.


I have framed the problem a bit:

When pgpool starts the pgpool_remote_start script, it never returns. After
the end of the scripts, pcp_recovery_node waits until the end of time.

Running the script by hand always work.

postgres at pool-1:~$ /usr/sbin/pcp_recovery_node -d 0 localhost 9898 postgres
DifficultPassword 2
DEBUG: send: tos="R", len=46
DEBUG: recv: tos="r", len=21, data=AuthenticationOK
DEBUG: send: tos="D", len=6

... and just waits forever...

Meanwhile the remote postgresql DB is up and running:

postgres at pool-3:~$ /usr/lib/postgresql/9.3/bin/pg_ctl -w -D
/var/lib/postgresql/9.3/main/ status
pg_ctl: server is running (PID: 8780)


The pgpool_remote_start script is:
postgres at pool-1:~$ /var/lib/postgresql/pgpool_remote_start pool-3
/var/lib/postgresql/9.3/main/
waiting for server to start....2014-03-03 14:33:50 CET LOG:  database
system was shut down at 2014-03-03 14:33:42 CET
2014-03-03 14:33:50 CET LOG:  database system is ready to accept connections
2014-03-03 14:33:50 CET LOG:  autovacuum launcher started
 done
server started
2014-03-03 14:33:51 CET LOG:  received SIGHUP, reloading configuration files
Connection to pool-3 closed.
postgres at pool-1:~$

Here is the script text:
----------------------
#!/bin/sh

/usr/bin/logger Called "$0" "$*"

DEST=$1
DESTDIR=$2
PGCTL="/usr/lib/postgresql/9.3/bin/pg_ctl"

COMMAND="ssh -t postgres@$DEST '$PGCTL -w -D $DESTDIR start'"
/usr/bin/logger -- Trying "$COMMAND"
eval $COMMAND
----------------------

I can do some kind of no-so-live recovery, If I manually follow the steps:

stop PG on node-x
call copy_basebackup
call pgpool_remote_start
call pcp attach_node

I can repeat it for any node, and works, the problem is, that I do not know
how to handle consistency during the node recovery.


Attila

p.s. I use GlusterFS to share some data, like script texts among the
servers, and keep WAL logs safe, it is very handy, I can say!


2014-02-24 13:05 GMT+01:00 Yugo Nagata <nagata at sraoss.co.jp>:

Hi,
>
> If you are using pgpool with master-slave mode and streaming-replication,
> the tutorial may be helpful to setup.
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave/en.html
>
> This includes scripts for online-recovery configuration.
>
> To add 3rd backend, what you need to do are:
> 1) Install PostgreSQL to the 3rd server. postgres should be stopped.
> 2) Add the backend information to all pgpools' pgpool.conf.
> 3) Reload all pgpools (You don't need to restart them.)
>  $ pgpool reload
> 4) Do online-recovery at one pgpool.
>  $ pcp_recovery 0 localhost 9898 user pass 2
>
> If this doesn't work, please infrom the actual operation which you did
> and all pgpool.conf and their log messages.
>
> On Tue, 11 Feb 2014 16:13:08 +0100
> Attila Heidrich <attila.heidrich at gmail.com> wrote:
>
> > Sorry for the mistypings, the correct inserted section is:
> > backend_hostname2 = 'pool-3'
> > backend_port2 = 5433
> > backend_weight2 = 1
> > backend_data_directory2 = '/var/lib/postgresql/9.3/main/'
> > backend_flag2 = 'ALLOW_TO_FAILOVER'
> >
> > After once this operation killed the replication, I can not easily ever
> > start it again with just two nodes either.
> >
> > and a different question: how the configurations of the pools being
> > clustered will be replicated? Is the only way doing it by hand?
> >
> > [[ pgpooladmin for example doesn't do anything with the configs on the
> > failover pgpools. It cannot even detect if pgpool is running, or not,
> but I
> > guess this is because the pid file is not created at all, which is a
> > problem too, but I don't really care pgpooladmin at the moment. ]]
> >
> >
> > Attila
> >
> >
> > 2014-02-11 15:21 GMT+01:00 Attila Heidrich <attila.heidrich at gmail.com>:
> >
> > > Dear All!
> > >
> > > I have read many docs, but still have totally silly problems, like the
> > > following:
> > >
> > > I run two Debian 7.3 boxes, with postgresql 9.3 and pgpool 3. on each.
> > > I use  pgpool's own wd mech.
> > >
> > > It seems to work fine in this scenario, but everything fails when I
> add a
> > > 3rd backend node.
> > >
> > > I just insert the following section into both pgpool.conf:
> > >
> > > backend_hostname2 = 'pool-3'
> > > backend_port1 = 5433
> > > backend_weight1 = 1
> > > backend_data_directory1 = '/var/lib/postgresql/9.3/main/'
> > > backend_flag1 = 'ALLOW_TO_FAILOVER'
> > >
> > > after the first and the second, which are the same, just with
> > > backend_hostname0 = 'pool-1', and backend_hostname1 = 'pool-2'
> > > respectively. pgpool replication works with the two nodes.
> > >
> > > Stop pgpools
> > > Stop database
> > > rsync the master database to the 3rd node (I would like to have online
> > > sync, but always got backend_error, so I have decided to start with
> offline
> > > sync)
> > > start all databases
> > > start pgpool#1
> > > start pgpool#2 (I have experienced that starting at once doesn't make
> any
> > > sense, it usually doesn't work such way, some, or all of the pgpool
> nodes
> > > should be restarted)
> > >
> > > And pgpool stops replicating, although the pools see each other, wd
> seems
> > > to work.  Any DB operation goes only the db on the active pool node.
> > >
> > > What do I do wrong??
> > >
> > > Regards,
> > >
> > > Attila
> > >
>
>
> --
> Yugo Nagata <nagata at sraoss.co.jp>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140304/035676f9/attachment.html>