[pgpool-general: 4863] Postgres Replication, watchdog, and post failover cleanup
Ryan Fraser
ryan at redshelf.com
Wed Aug 3 23:51:49 JST 2016
Hi all,
I've been working on a database project that involves using Postgres 9.1,
PgPool 3.5.3, and SaltStack in Amazon EC2, and I apologize ahead of time with
the long winded email.
As it currently stands, I have salt states that automate the setup of new new
servers depending on their roles they are given at provision (postgres-master,
postgres-slave). When the states run across all the running machines it sets
up a working asynchronous streaming replication cluster. Wal-e is also being
used for base backups and wal archving to S3.
For this example I'll be using a master with 5 slaves, the salt states also
setup all the tools necessary as well as pgpool, and a config with a unique
watchdog priority based on it's role. It's to the point that I check to make
sure streaming replication is working correctly before I start pgpool up.
1. ubuntu at ip-172-31-2-26:~$ sudo salt '*' cmd.run "cat /etc/pgpool2/3.5.3/pgpool.conf| grep wd_priority"
2. ip-172-31-2-7.us-west-1.compute.internal:
3. wd_priority = 1
4. wd_priority = 5
5. wd_priority = 3
6. ip-172-31-2-6.us-west-1.compute.internal:
7. wd_priority = 1
8. wd_priority = 5
9. wd_priority = 2
10. ip-172-31-2-8.us-west-1.compute.internal:
11. wd_priority = 1
12. wd_priority = 1
13. wd_priority = 4
14. ip-172-31-2-9.us-west-1.compute.internal:
15. wd_priority = 1
16. wd_priority = 3
17. wd_priority = 5
18. ip-172-31-2-4.us-west-1.compute.internal:
19. wd_priority = 1
20. wd_priority = 6
21. wd_priority = 6
22. ip-172-31-2-5.us-west-1.compute.internal:
23. wd_priority = 1
24. wd_priority = 4
25. wd_priority = 1
it took me awhile to get wd_priority to spit out a unique number based on it's
role, that's why there's multiple entries. But let's note here that the
postgres master in this case is 172.31.2.4, since my logic is set to give the
master the highest number for watch dog priority, with 172.31.2.5 being next
in line to take over the VIP in the event that the machine or pgpool fails.
Now I check to make sure that that 172.31.2.4 has actually been designated as
master
1. ubuntu at ip-172-31-2-26:~$ sudo salt -G 'roles:postgres-master' test.ping
2. ip-172-31-2-4.us-west-1.compute.internal:
3. True
Looks like salt thinks so, now let's make sure replication is setup correctly
1. ubuntu at ip-172-31-2-4:~$ sudo -u postgres psql
2. psql (9.1.22)
3. Type "help" for help.
4.
5. postgres=# select * from pg_stat_replication;
6. procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
7. \---------+----------+-------------+------------------+-------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
8. 28655 | 16384 | replication | walreceiver | 172.31.2.5 | | 44840 | 2016-08-02 22:21:34.352537+00 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 0 | async
9. 28901 | 16384 | replication | walreceiver | 172.31.2.6 | | 56999 | 2016-08-02 22:26:35.648484+00 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 0 | async
10. 28902 | 16384 | replication | walreceiver | 172.31.2.8 | | 55492 | 2016-08-02 22:26:35.751934+00 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 0 | async
11. 28903 | 16384 | replication | walreceiver | 172.31.2.7 | | 48535 | 2016-08-02 22:26:35.786888+00 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 0 | async
12. 28904 | 16384 | replication | walreceiver | 172.31.2.9 | | 51240 | 2016-08-02 22:26:35.818891+00 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 0 | async
13. (5 rows)
(Sorry for not using extended format here) we can see that the master has the
slaves replicating correclty, now I'm going to start up the pgpool2 service
across my machines.
1. ubuntu at ip-172-31-2-26:~$ sudo salt '*' cmd.run "service pgpool2 start"
2. ip-172-31-2-7.us-west-1.compute.internal:
3. * Starting pgpool-II pgpool
4. FD - Starting pgpool-II by executing:
5. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
6. ...done.
7. ip-172-31-2-8.us-west-1.compute.internal:
8. * Starting pgpool-II pgpool
9. FD - Starting pgpool-II by executing:
10. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
11. ...done.
12. ip-172-31-2-9.us-west-1.compute.internal:
13. * Starting pgpool-II pgpool
14. FD - Starting pgpool-II by executing:
15. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
16. ...done.
17. ip-172-31-2-5.us-west-1.compute.internal:
18. * Starting pgpool-II pgpool
19. FD - Starting pgpool-II by executing:
20. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
21. ...done.
22. ip-172-31-2-6.us-west-1.compute.internal:
23. * Starting pgpool-II pgpool
24. FD - Starting pgpool-II by executing:
25. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
26. ...done.
27. ip-172-31-2-4.us-west-1.compute.internal:
28. * Starting pgpool-II pgpool
29. FD - Starting pgpool-II by executing:
30. /usr/sbin/pgpool -n -f /etc/pgpool2/3.5.3/pgpool.conf -F /etc/pgpool2/3.5.3/pcp.conf -a /etc/pgpool2/3.5.3/pool_hba.conf >>/var/log/pgpool/pgpool.log 2>&1 &
31. ...done.
In salt i've set my designated Virtual IP for watchdog to be 172.31.2.30, all
the appropriate IAM roles and security groups are applied to the machines, so
let's see if I can connect to it from my salt server and see if it can see the
other pool nodes. (note here i have postgresql running on port 5433 on each
machine and pgpool is running as 5432)
1. ubuntu at ip-172-31-2-26:~$ psql -U postgres -h 172.31.2.30
2. psql (9.1.21, server 9.1.22)
3. Type "help" for help.
4.
5. postgres=# show pool_nodes;
6. node_id | hostname | port | status | lb_weight | role | select_cnt
7. \---------+------------------------------------------+------+--------+-----------+---------+------------
8. 0 | ip-172-31-2-6.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
9. 1 | ip-172-31-2-5.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
10. 2 | ip-172-31-2-9.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
11. 3 | ip-172-31-2-4.us-west-1.compute.internal | 5433 | 2 | 0.166667 | primary | 0
12. 4 | ip-172-31-2-8.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
13. 5 | ip-172-31-2-7.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
14. (6 rows)
So we can connect to our pgpool cluster, it can see the other nodes in the
cluster, and we know streaming replication is working correctly. So far so
good, now let's try to cause a failover event. In this case, I will go into
the ec2 control panel and power down the master. If everything works out okay,
The VIP should be moved to the next in line with the lowest watch dog
priority, and pgpool should elect a new master and promote it as such. Now
that the server is powered down let's check pool_nodes again.
1. postgres=# show pool_nodes;
2. node_id | hostname | port | status | lb_weight | role | select_cnt
3. \---------+------------------------------------------+------+--------+-----------+---------+------------
4. 0 | ip-172-31-2-6.us-west-1.compute.internal | 5433 | 2 | 0.166667 | primary | 0
5. 1 | ip-172-31-2-5.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
6. 2 | ip-172-31-2-9.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
7. 3 | ip-172-31-2-4.us-west-1.compute.internal | 5433 | 3 | 0.166667 | standby | 0
8. 4 | ip-172-31-2-8.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
9. 5 | ip-172-31-2-7.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
10. (6 rows)
Now we can see that our old master, 172.31.2.4 is now unavailable, and a new
master, 172.31.2.6 has been elected as a new primary. Here's where I'm first
really confused as to what to do next. It shows the status of all the other
slaves as a status of 2, or available, however the other slaves are currently
not replicating from the new master as shown below, why is this?
1. ubuntu at ip-172-31-2-6:~$ sudo -u postgres psql
2. psql (9.1.22)
3. Type "help" for help.
4.
5. postgres=# select * from pg_stat_replication;
6. procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
7. \---------+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+------------
8. (0 rows)
Now let's say I want to have the slaves replicating from the new master, in
this case let's start replication on 172.31.2.5, I have a script that setups
recovery.conf based on a few flags that are passed to it
1. ubuntu at ip-172-31-2-5:~$ sudo -u postgres /etc/postgresql/9.1/main/replscripts/initiate_replication.sh -f -H ip-172-31-2-6.us-west-1.compute.internal -P 5433 -p replication
2. initiate_replication - Start
3. DEBUG: The script will be executed with the following arguments:
4. DEBUG: \--trigger-file=/etc/postgresql/9.1/main/im_the_master
5. DEBUG: --standby_file=/etc/postgresql/9.1/main/im_slave
6. DEBUG: \--primary-host=ip-172-31-2-6.us-west-1.compute.internal
7. DEBUG: \--primary-port=5433
8. DEBUG: \--slot-name=ip_172_31_2_5
9. DEBUG: \--user=replication
10. DEBUG: \--password=replication
11. DEBUG: \--force
12. INFO: Checking if trigger file exists...
13. INFO: Checking if standby file exists...
14. INFO: Standby file not found. Creating new one...
15. INFO: Ensuring replication user and password in password file (.pgpass)...
16. * Stopping PostgreSQL 9.1 database server [ OK ]
17. INFO: Deleting old data...
18. INFO: Getting the initial backup...
19. wal_e.main INFO MSG: starting WAL-E
20. DETAIL: The subcommand is "backup-fetch".
21. STRUCTURED: time=2016-08-02T23:00:15.870621-00 pid=4027
22. wal_e.worker.s3.s3_worker INFO MSG: beginning partition download
23. DETAIL: The partition being downloaded is part_00000000.tar.lzo.
24. HINT: The absolute S3 key is wal-e-stage/basebackups_005/base_000000010000000000000002_00000032/tar_partitions/part_00000000.tar.lzo.
25. STRUCTURED: time=2016-08-02T23:00:22.453123-00 pid=4027
26. INFO: Creating recovery.conf file...
27. INFO: Removing old postgresql.conf file...
28. INFO: Copying new postgresql.conf file...
29. INFO: Starting postgresql service...
30. * Starting PostgreSQL 9.1 database server [ OK ]
31. initiate_replication - Done!
Now let's verify on the new master that it has a new slave started.
1. Now let's check the master
2. ubuntu at ip-172-31-2-6:~$ sudo -u postgres psql
3. psql (9.1.22)
4. Type "help" for help.
5.
6. postgres=# select * from pg_stat_replication;
7. procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
8. \---------+----------+-------------+------------------+-------------+-----------------+-------------+------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
9. 3368 | 16384 | replication | walreceiver | 172.31.2.5 | | 49348 | 2016-08-02 23:00:30.00704+00 | streaming | 0/C000000 | 0/C000000 | 0/C000000 | 0/C000000 | 0 | async
10. (1 row)
(Again sorry for not using extended display here) We can see that our master
has a slave readded back to it, so what is the output from pgpool now?
1. ubuntu at ip-172-31-2-26:~$ psql -U postgres -h 172.31.2.30
2. psql (9.1.21, server 9.1.22)
3. Type "help" for help.
4.
5. postgres=# show pool_nodes;
6. node_id | hostname | port | status | lb_weight | role | select_cnt
7. \---------+------------------------------------------+------+--------+-----------+---------+------------
8. 0 | ip-172-31-2-6.us-west-1.compute.internal | 5433 | 2 | 0.166667 | primary | 0
9. 1 | ip-172-31-2-5.us-west-1.compute.internal | 5433 | 3 | 0.166667 | standby | 0
10. 2 | ip-172-31-2-9.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
11. 3 | ip-172-31-2-4.us-west-1.compute.internal | 5433 | 3 | 0.166667 | standby | 0
12. 4 | ip-172-31-2-8.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
13. 5 | ip-172-31-2-7.us-west-1.compute.internal | 5433 | 2 | 0.166667 | standby | 0
14. (6 rows)
Looks like the master is still available, however the slave we have pointed to
the new master now has a status of 3, while the other defunct slaves are still
showing a status of 2. Is there any particular reason for this? Is there some
sort of task I'm not performing that needs to be done? So really it's only two
questions Why do defunct slaves show an available status, and how to I repair
slaves to point to the promoted master and make them available in pgpool?
Thank you all,
Ryan Fraser
DevOps Engineer
+\(1\).png)
312.878.8586
[ryan at redshelf.com](mailto:ryan at redshelf.com)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160803/60a26237/attachment-0001.html>
More information about the pgpool-general
mailing list