<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Hi Contributors,</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
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.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
I am trying to do vice versa, Old primary not able to bring back to original state automatically through pgpool.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
I have followed the following steps.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<ul style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<li>Current settings Step-1</li><li>postgres=# show pool_nodes;</li><li> node_id | hostname | port | status | lb_weight | role | select_cnt</li><li>---------+----------------+------+--------+-----------+--------+------------</li><li> 0 | 100.114.116.67 | 5432 | 2 | 1.000000 | master | 0</li><li> 1 | 100.114.116.68 | 5433 | 2 | 0.000000 | slave | 0</li><li>(2 rows)</li></ul>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u>Step-2</u></b></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Shutting down the master server 67</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div>sudo service postgresql-9.5 stop</div>
<div>Stopping PostgreSQL 9.5:</div>
<div>waiting for server to shut down.... done</div>
<div>server stopped</div>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<u>Step-3</u></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
l_use_sync_map: we cannot use sync map because STREAM: 0 query in progress: 0 doing extended query: 0</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: reading backend data packet kind</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DETAIL: backend:1 of 2 kind = 'Z'</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: read_kind_from_backend max_count:1.000000 num_executed_nodes:1</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: processing backend response</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DETAIL: received kind 'Z'(5a) from backend</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: processing backend response</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DETAIL: Ready For Query received</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: session context: clearing ignore till sync. DONE</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
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</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
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</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
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</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: reading message length</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DETAIL: slot: 1 length: 5</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
2016-08-17 14:25:04: pid 9795: DEBUG: processing ReadyForQuery</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<u>Step-4</u></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<u><br>
</u></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
section to the server was lost. Attempting reset: Succeeded.</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
postgres=# show pool_nodes;</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
node_id | hostname | port | status | lb_weight | role | select_cnt</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
---------+----------------+------+--------+-----------+--------+------------</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
0 | 100.114.116.67 | 5432 | 3 | 1.000000 | slave | 0</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
1 | 100.114.116.68 | 5433 | 2 | 0.000000 | master | 0</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
(2 rows)</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
postgres=#</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u>Step-5</u></b></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Start the old master 67 again</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div>sudo service postgresql-9.5 start</div>
<div>Starting PostgreSQL 9.5:</div>
<div><br>
</div>
<div>WARNING --> PERL_INSTALL_PATH is not set in /opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file</div>
<div>WARNING --> PYTHON_INSTALL_PATH is not set in /opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file</div>
<div>WARNING --> TCL_INSTALL_PATH is not set in /opt/PostgreSQL/9.5/etc/sysconfig/plLanguages.config file</div>
<div><br>
</div>
<div>waiting for server to start.... done</div>
<div>server started</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u>Step-6</u></b></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Shutting down the current master 68</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div><br>
</div>
<div>postgres=# \q</div>
<div>bash-4.1$ /usr/local/pgsql/bin/pg_ctl -D /pgsql/data/9.5/data stop</div>
<div>waiting for server to shut down......... done</div>
<div>server stopped</div>
<div>bash-4.1$</div>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u>Step-7</u></b></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u><br>
</u></b></div>
<div><font face="Calibri,sans-serif">P</font><span style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">gpool is down after master shutdown</span></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div><br>
</div>
<div>bash-4.1$ psql -p 9999</div>
<div>psql: ERROR: pgpool is not accepting any new connections</div>
<div>DETAIL: all backend nodes are down, pgpool requires at least one valid node</div>
<div>HINT: repair the backend nodes and restart pgpool</div>
<div>bash-4.1$</div>
<div><br>
</div>
<div><br>
</div>
<div>
<div>lading startup packet</div>
<div>2016-08-17 14:29:15: pid 9965: DETAIL: application_name: psql</div>
<div>2016-08-17 14:29:15: pid 9965: DEBUG: reading startup packet</div>
<div>2016-08-17 14:29:15: pid 9965: DETAIL: Protocol Major: 3 Minor: 0 database: postgres user: postgres</div>
<div>2016-08-17 14:29:15: pid 9965: DEBUG: forwarding error message to frontend</div>
<div>2016-08-17 14:29:15: pid 9965: FATAL: pgpool is not accepting any new connections</div>
<div>2016-08-17 14:29:15: pid 9965: DETAIL: all backend nodes are down, pgpool requires at least one valid node</div>
<div>2016-08-17 14:29:15: pid 9965: HINT: repair the backend nodes and restart pgpool</div>
<div>2016-08-17 14:29:15: pid 9595: DEBUG: reaper handler</div>
<div>2016-08-17 14:29:15: pid 9595: LOG: child process with pid: 9965 exits with status 256</div>
<div>2016-08-17 14:29:15: pid 9595: LOG: fork a new child process with pid: 9988</div>
<div>2016-08-17 14:29:15: pid 9595: DEBUG: reaper handler: exiting normally</div>
<div>2016-08-17 14:29:15: pid 9988: DEBUG: initializing backend status</div>
</div>
<div><br>
</div>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<b><u>Step-8</u></b></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Restarting the pgpool </div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div>and restart pgpool</div>
<div>bash-4.1$ exit</div>
<div>exit</div>
<div>[root@ushdbld00034 bin]# ./pgpool -f /usr/local/etc/pgpool.conf stop</div>
<div>2016-08-17 14:30:27: pid 10015: LOG: stop request sent to pgpool. waiting for termination...</div>
<div>.done.</div>
<div>[1]+ Done ./pgpool -f /usr/local/etc/pgpool.conf -n -d > /tmp/PPOOL.log 2>&1</div>
<div>[root@ushdbld00034 bin]# ./pgpool -f /usr/local/etc/pgpool.conf -n -d > /tmp/PPOOL.log 2>&1 &</div>
<div>[1] 10017</div>
<div><br>
</div>
<div><br>
</div>
<div><b><u>Step-9</u></b></div>
<div><br>
</div>
<div>Old master is new master now</div>
<div><br>
</div>
<div>postgres=# show pool_nodes;</div>
<div> node_id | hostname | port | status | lb_weight | role | select_cnt</div>
<div>---------+----------------+------+--------+-----------+--------+------------</div>
<div> 0 | 100.114.116.67 | 5432 | 2 | 1.000000 | master | 0</div>
<div> 1 | 100.114.116.68 | 5433 | 3 | 0.000000 | slave | 0</div>
<div>(2 rows)</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>I am not able to bring back the old master through pgpool , Do I need to execute the failover scripts through pgpoo.conf?</div>
<div><br>
</div>
<div>Will pgpool would not automatically bring back the old master after shutdown the new master?</div>
<div>I have recovery.conf on both the servers. </div>
<div><br>
</div>
<div>Please let me know.</div>
<div><br>
</div>
<div>Your help Is much appreciated.</div>
<div><br>
</div>
<div>Thanks !</div>
<div>sai</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
</div>
</body>
</html>