[pgpool-general: 4931] Re: PGPOOL failover issues
Vaidyanathaswamy, Sai (NBCUniversal)
Sai.Vaidyanathaswamy at nbcuni.com
Fri Aug 19 08:13:43 JST 2016
Thank you for the prompt response,
I am able to manually attach the old primary again as master by the
following command.
. /pcp_attach_node 0 100.114.116.67 5432 postgres
I have placed the following scripts and also in pgpool.conf (
follow_master_command = Œ/scripts/follow_master.sh %d')
# Executes this comm
Script;
!/bin/sh
PGPOOLIP=100.114.116.71
PGUSER=postgres
PGPASS=testing24
/usr/local/bin/pcp_recovery_node 0 $PGPOOLIP 9999 $PGUSER >
/tmp/failover.log 2>&1
$PGPASS $1
/usr/local/bin/pcp_attach_node 0 $PGPOOLIP 9999 $PGUSER >
/tmp/failover.log 2>&1
$PGPASS $1
I am not seeing the pgpool not taking over immediately after the slave
master goes down, Is there any way automatically pgpool takes over without
any delay
Pgpool.conf is not executing the follow_master_command.
Your help is much appreciated.
Thanks !
sai
On 8/17/16, 5:16 PM, "Tatsuo Ishii" <ishii at sraoss.co.jp> wrote:
>[To: pgpool-general-request at pgpool.net was removed. Please do not send
>postings to pgpool-general-request at pgpool.net]
>
>> Hi Contributors,
>>
>> I am testing the failover for PGPOOL, I have scenario where I am
>>testing the failover by shutting down the master, Slave is promoted to
>>master.
>> I am trying to do vice versa, Old primary not able to bring back to
>>original state automatically through pgpool.
>>
>> I have followed the following steps.
>>
>>
>> * Current settings Step-1
>> * postgres=# show pool_nodes;
>> * node_id | hostname | port | status | lb_weight | role |
>>select_cnt
>> *
>>---------+----------------+------+--------+-----------+--------+---------
>>---
>> * 0 | 100.114.116.67 | 5432 | 2 | 1.000000 | master | 0
>> * 1 | 100.114.116.68 | 5433 | 2 | 0.000000 | slave | 0
>> * (2 rows)
>>
>> Step-2
>>
>> Shutting down the master server 67
>>
>> sudo service postgresql-9.5 stop
>> Stopping PostgreSQL 9.5:
>> waiting for server to shut down.... done
>> server stopped
>>
>>
>> Step-3
>>
>> l_use_sync_map: we cannot use sync map because STREAM: 0 query in
>>progress: 0 doing extended query: 0
>> 2016-08-17 14:25:04: pid 9795: DEBUG: reading backend data packet kind
>> 2016-08-17 14:25:04: pid 9795: DETAIL: backend:1 of 2 kind = 'Z'
>> 2016-08-17 14:25:04: pid 9795: DEBUG: read_kind_from_backend
>>max_count:1.000000 num_executed_nodes:1
>> 2016-08-17 14:25:04: pid 9795: DEBUG: processing backend response
>> 2016-08-17 14:25:04: pid 9795: DETAIL: received kind 'Z'(5a) from
>>backend
>> 2016-08-17 14:25:04: pid 9795: DEBUG: processing backend response
>> 2016-08-17 14:25:04: pid 9795: DETAIL: Ready For Query received
>> 2016-08-17 14:25:04: pid 9795: DEBUG: session context: clearing ignore
>>till sync. DONE
>> 2016-08-17 14:25:04: pid 9795: DEBUG: pool_use_sync_map: we cannot use
>>sync map because STREAM: 0 query in progress: 0 doing extended query: 0
>> 2016-08-17 14:25:04: pid 9795: DEBUG: pool_use_sync_map: we cannot use
>>sync map because STREAM: 0 query in progress: 0 doing extended query: 0
>> 2016-08-17 14:25:04: pid 9795: DEBUG: pool_use_sync_map: we cannot use
>>sync map because STREAM: 0 query in progress: 0 doing extended query: 0
>> 2016-08-17 14:25:04: pid 9795: DEBUG: reading message length
>> 2016-08-17 14:25:04: pid 9795: DETAIL: slot: 1 length: 5
>> 2016-08-17 14:25:04: pid 9795: DEBUG: processing ReadyForQuery
>>
>>
>> Step-4
>>
>> section to the server was lost. Attempting reset: Succeeded.
>> postgres=# show pool_nodes;
>> node_id | hostname | port | status | lb_weight | role |
>>select_cnt
>>
>>---------+----------------+------+--------+-----------+--------+---------
>>---
>> 0 | 100.114.116.67 | 5432 | 3 | 1.000000 | slave | 0
>> 1 | 100.114.116.68 | 5433 | 2 | 0.000000 | master | 0
>> (2 rows)
>>
>> postgres=#
>>
>> Step-5
>>
>> Start the old master 67 again
>>
>> sudo service postgresql-9.5 start
>> Starting PostgreSQL 9.5:
>>
>> WARNING --> PERL_INSTALL_PATH is not set in
>>/opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file
>> WARNING --> PYTHON_INSTALL_PATH is not set in
>>/opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file
>> WARNING --> TCL_INSTALL_PATH is not set in
>>/opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file
>>
>> waiting for server to start.... done
>> server started
>>
>>
>>
>>
>>
>> Step-6
>>
>>
>>
>> Shutting down the current master 68
>>
>>
>> postgres=# \q
>> bash-4.1$ /usr/local/pgsql/bin/pg_ctl -D /pgsql/data/9.5/data stop
>> waiting for server to shut down......... done
>> server stopped
>> bash-4.1$
>>
>> Step-7
>>
>> Pgpool is down after master shutdown
>>
>> bash-4.1$ psql -p 9999
>> psql: ERROR: pgpool is not accepting any new connections
>> DETAIL: all backend nodes are down, pgpool requires at least one valid
>>node
>> HINT: repair the backend nodes and restart pgpool
>> bash-4.1$
>>
>>
>> lading startup packet
>> 2016-08-17 14:29:15: pid 9965: DETAIL: application_name: psql
>> 2016-08-17 14:29:15: pid 9965: DEBUG: reading startup packet
>> 2016-08-17 14:29:15: pid 9965: DETAIL: Protocol Major: 3 Minor: 0
>>database: postgres user: postgres
>> 2016-08-17 14:29:15: pid 9965: DEBUG: forwarding error message to
>>frontend
>> 2016-08-17 14:29:15: pid 9965: FATAL: pgpool is not accepting any new
>>connections
>> 2016-08-17 14:29:15: pid 9965: DETAIL: all backend nodes are down,
>>pgpool requires at least one valid node
>> 2016-08-17 14:29:15: pid 9965: HINT: repair the backend nodes and
>>restart pgpool
>> 2016-08-17 14:29:15: pid 9595: DEBUG: reaper handler
>> 2016-08-17 14:29:15: pid 9595: LOG: child process with pid: 9965 exits
>>with status 256
>> 2016-08-17 14:29:15: pid 9595: LOG: fork a new child process with pid:
>>9988
>> 2016-08-17 14:29:15: pid 9595: DEBUG: reaper handler: exiting normally
>> 2016-08-17 14:29:15: pid 9988: DEBUG: initializing backend status
>>
>>
>> Step-8
>>
>> Restarting the pgpool
>>
>> and restart pgpool
>> bash-4.1$ exit
>> exit
>> [root at ushdbld00034 bin]# ./pgpool -f /usr/local/etc/pgpool.conf stop
>> 2016-08-17 14:30:27: pid 10015: LOG: stop request sent to pgpool.
>>waiting for termination...
>> .done.
>> [1]+ Done ./pgpool -f /usr/local/etc/pgpool.conf -n
>>-d > /tmp/PPOOL.log 2>&1
>> [root at ushdbld00034 bin]# ./pgpool -f /usr/local/etc/pgpool.conf -n -d
>>> /tmp/PPOOL.log 2>&1 &
>> [1] 10017
>>
>>
>> Step-9
>>
>> Old master is new master now
>>
>> postgres=# show pool_nodes;
>> node_id | hostname | port | status | lb_weight | role |
>>select_cnt
>>
>>---------+----------------+------+--------+-----------+--------+---------
>>---
>> 0 | 100.114.116.67 | 5432 | 2 | 1.000000 | master | 0
>> 1 | 100.114.116.68 | 5433 | 3 | 0.000000 | slave | 0
>> (2 rows)
>>
>>
>>
>> I am not able to bring back the old master through pgpool , Do I need
>>to execute the failover scripts through pgpoo.conf?
>
>Yes. Or you could start PostgreSQL on 100.114.116.68 then execute
>pcp_attach_node to bring back it to online.
>
>> Will pgpool would not automatically bring back the old master after
>>shutdown the new master?
>> I have recovery.conf on both the servers.
>>
>> Please let me know.
>>
>> Your help Is much appreciated.
>>
>> Thanks !
>> sai
>
>Best regards,
>--
>Tatsuo Ishii
>SRA OSS, Inc. Japan
>English: http://www.sraoss.co.jp/index_en.php
>Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list