Switch Your PostgreSQL Primary for a Read Replica Without Downtime
In my ongoing research to identify solutions and similarities between MySQL - PostgreSQL, I recently faced a simple issue. I needed to perform a slave shift from one IP to another, and I did not want to have to restart the slave that is serving the reads. In MySQL, I can repoint the replication online with the command Change Master TO, so I was looking for a similar solution in Postgres. In my case, I could also afford some stale reads, so a few seconds delay would have been okay, but I couldn't take down the server.
After brief research, I noticed that there is not a solution that allows you to do that without restarting the PostgreSQL server instance.
I was a bit disappointed because I was just trying to move the whole traffic from one subnet to another, so not really changing the Master, but just the pointer.
At this point, I raised my question to my colleagues who are experts in PG. Initially, they confirmed to me that there is no real dynamic solution/command for that. However, while discussing this, one of them (Jobin Augustine) suggested a not "officially supported" way, which might work.
In brief, given that the WAL Receiver uses its own process, killing it would trigger an internal refresh operation, and that could result in having the replication restart from the new desired configuration.
This was an intriguing suggestion, but I wondered if it might have some negative side effects. In any case, I decided to try it and see what would happen.
This article describes the process I followed to test the approach. To be clear: this is not an "Official" solution and is not recommended as best practice.
From now on in this article, I will drop the standard MySQL terms and instead use Primary for Master and Replica for Slave.
Scenarios
I carried out two main tests:
- No load in writing
- Writing happening
For each of these, I took these steps:
a) move Replica to same Primary (different ip)
b) move Replica to different Primary/Replica, creating a chain, so from:
+--------+
| Primary|
+----+---+
|
+--------+ | +--------+
|Replica1+<----+--->+Replica2|
+--------+ +--------+To:
+-------+
|Primary|
+---+---+
|
v
+---+----+
|Replica2|
+---+----+
|
v
+---+----+
|Replica1|
+--------+The other thing was to try to be as non-invasive as possible. Given that, I used KILL SIGQUIT (3) instead of the more brutal SIGKILL.
SIGQUIT "The SIGQUIT signal is sent to a process by its controlling terminal when the user requests that the process quit and perform a core dump."
To note that, I did try this with SIGTERM (15), which is the nicest approach, but it didn't, in fact, force the process to perform the shift as desired.
In general, in all the following tests, what I execute is:
ps aux|grep 'wal receiver'
kill -3 <pid>These are the current IPs for node:
Node1 (Primary):
NIC1 = 192.168.1.81
NIC2 = 192.168.4.81
NIC3 = 10.0.0.81
Node2 (replica1):
NIC1 = 192.168.1.82
NIC2 = 192.168.4.82
NIC3 = 10.0.0.82
Node1 (replica2):
NIC1 = 192.168.1.83
NIC2 = 192.168.4.83
NIC3 = 10.0.0.83
The starting position is:
select pid,usesysid,usename,application_name,client_addr,client_port,backend_start,state,sent_lsn,write_lsn,flush_lsn,sync_state from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
22495 | 24601 | replica | node2 | 192.168.4.82 | 49518 | 2019-02-06 11:07:46.507511-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | asyncAnd now let's roll the ball and see what happens.
Experiment 1: Moving to Same Primary No Load
I will move Node2 to point to 192.168.1.81
In my recovery.confprimary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
change to:
primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.1.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
[root@pg1h3p82 data]# ps aux|grep 'wal receiver'
postgres 8343 0.0 0.0 667164 2180 ? Ss Feb06 16:27 postgres: wal receiver process streaming 10/FD6C60E8
Checking the replication status:
[root@pg1h3p82 data]# ps aux|grep 'wal receiver'
postgres 8343 0.0 0.0 667164 2180 ? Ss Feb06 16:27 postgres: wal receiver process streaming 10/FD6C60E8
Tue 19 Feb 2019 12:10:22 PM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
23748 | 24601 | replica | node2 | 192.168.4.82 | 49522 | 2019-02-19 12:09:31.054915-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
(2 rows)
Tue 19 Feb 2019 12:10:23 PM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
(1 row)
Tue 19 Feb 2019 12:10:26 PM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
23756 | 24601 | replica | node2 | 192.168.1.82 | 37866 | 2019-02-19 12:10:26.904766-05 | catchup | 10/FD460000 | 10/FD3A0000 | 10/FD6C60E8 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
(2 rows)
Tue 19 Feb 2019 12:10:28 PM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
23756 | 24601 | replica | node2 | 192.168.1.82 | 37866 | 2019-02-19 12:10:26.904766-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
(2 rows)It takes six seconds to kill the process, shift to a new IP, and perform the catch-up.
Experiment 2: Moving to Different Primary (As a Chain of Replicas) No Load
I will move Node2 to point to 192.168.4.83
In my recovery.con primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.1.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
change to:
primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.83 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
[root@pg1h3p82 data]# ps aux|grep 'wal receiver'
postgres 25859 0.0 0.0 667164 3484 ? Ss Feb19 1:53 postgres: wal receiver process
On Node1:
Thu 21 Feb 2019 04:23:26 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
31241 | 24601 | replica | node2 | 192.168.1.82 | 38232 | 2019-02-21 04:17:24.535662-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | async
(2 rows)
Thu 21 Feb 2019 04:23:27 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 10/FD6C60E8 | 10/FD6C60E8 | 10/FD6C60E8 | asyncOn Node3:
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-----+----------+---------+------------------+-------------+-------------+---------------+-------+----------+-----------+-----------+------------
(0 rows)
Thu 21 Feb 2019 04:23:30 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
1435 | 24601 | replica | node2 | 192.168.4.82 | 58116 | 2019-02-21 04:23:29.846798-05 | streaIn this case, shifting to a new primary took four seconds.
Now, all this is great, but I was working with NO load, so what would happen if we have read/write taking place?
Experiment 3: Moving to Same Primary WITH Load
I will move Node2 to point to 192.168.4.81
In my recovery.conf primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.1.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
change to: primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
[root@pg1h3p82 data]# ps aux|grep 'wal receiver'
postgres 20765 0.2 0.0 667196 3712 ? Ss 06:23 0:00 postgres: wal receiver process streaming 11/E33F760
Thu 21 Feb 2019 06:23:03 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+------------+------------+------------+------------
31649 | 24601 | replica | node2 | 192.168.1.82 | 38236 | 2019-02-21 06:21:23.539493-05 | streaming | 11/8FEC000 | 11/8FEC000 | 11/8FEC000 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/8FEC000 | 11/8FEC000 | 11/8FEC000 | async
Thu 21 Feb 2019 06:23:04 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+------------+------------+------------+------------
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/904DCC0 | 11/904C000 | 11/904C000 | async
Thu 21 Feb 2019 06:23:08 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+------------+------------+------------+------------
31778 | 24601 | replica | node2 | 192.168.4.82 | 49896 | 2019-02-21 06:23:08.978179-05 | catchup | 11/9020000 | | | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/9178000 | 11/9178000 | 11/9178000 | async
Thu 21 Feb 2019 06:23:09 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+------------+------------+------------+------------
31778 | 24601 | replica | node2 | 192.168.4.82 | 49896 | 2019-02-21 06:23:08.978179-05 | streaming | 11/91F7860 | 11/91F7860 | 11/91F7860 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/91F7860 | 11/91F7860 | 11/91F7860 | asyncIn this case, shifting to a new primary takes six seconds.
Experiment 4 : Moving to Different Primary (As a Chain of Replicas) No Load
I move Node2 to point to 192.168.4.83In my recovery.conf
primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
change to: primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.83 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
[root@pg1h3p82 data]# ps aux|grep 'wal receiver'
postgres 21158 6.3 0.0 667196 3704 ? Ds 06:30 0:09 postgres: wal receiver process streaming 11/4F000000
Node1:
Thu 21 Feb 2019 06:30:56 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
31778 | 24601 | replica | node2 | 192.168.4.82 | 49896 | 2019-02-21 06:23:08.978179-05 | streaming | 11/177F8000 | 11/177F8000 | 11/177F8000 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/177F8000 | 11/177F8000 | 11/177F8000 | async
(2 rows)
Thu 21 Feb 2019 06:30:57 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/17DAA000 | 11/17DAA000 | 11/17DAA000 | async
(1 row)Node3:
Thu 21 Feb 2019 06:31:01 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-----+----------+---------+------------------+-------------+-------------+---------------+-------+----------+-----------+-----------+------------
(0 rows)
Thu 21 Feb 2019 06:31:02 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
------+----------+---------+------------------+--------------+-------------+-------------------------------+---------+-------------+-------------+-------------+------------
1568 | 24601 | replica | node2 | 192.168.4.82 | 58122 | 2019-02-21 06:31:01.937957-05 | catchup | 11/17960000 | 11/17800000 | 11/177F8CC0 | async
(1 row)
Thu 21 Feb 2019 06:31:03 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
1568 | 24601 | replica | node2 | 192.168.4.82 | 58122 | 2019-02-21 06:31:01.937957-05 | streaming | 11/1A1D3D08 | 11/1A1D3D08 | 11/1A1D3D08 | async
(1 row)In this case, shifting to a new primary took seven seconds.
Finally, I did another test. I was wondering, can I move the server Node2 back under the main Primary Node1 while writes are happening?
Well, here's what happened:
In my recovery.confprimary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.83 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
change to:primary_conninfo = 'application_name=node2 user=replica password=replica connect_timeout=10 host=192.168.4.81 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
After I kill the process as I did in the previous examples, Node2 rejoined the Primary Node1, but...
Thu 21 Feb 2019 06:33:58 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
1901 | 24601 | replica | node2 | 192.168.4.82 | 49900 | 2019-02-21 06:33:57.81308-05 | catchup | 11/52E40000 | 11/52C00000 | 11/52BDFFE8 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/5D3F9EC8 | 11/5D3F9EC8 | 11/5D...Node2 was not really able to catch up quickly, or at least not able to do that until the load was on the primary and high. As soon as I reduced the application pressure:
Thu 21 Feb 2019 06:35:29 AM EST (every 1s)
pid | usesysid | usename | application_name | client_addr | client_port | backend_start | state | sent_lsn | write_lsn | flush_lsn | sync_state
-------+----------+---------+------------------+--------------+-------------+-------------------------------+-----------+-------------+-------------+-------------+------------
1901 | 24601 | replica | node2 | 192.168.4.82 | 49900 | 2019-02-21 06:33:57.81308-05 | streaming | 11/70AE8000 | 11/70000000 | 11/70000000 | async
22449 | 24601 | replica | node3 | 192.168.4.83 | 43648 | 2019-02-06 10:56:32.612439-05 | streaming | 11/70AE8000 | 11/70AE8000 | 11/70AE8000 | asyncNode2 was able to catch up and align itself.
Conclusions
In all tests, the Replica was able to rejoin the Primary or the new primary with obvious different times.
From the tests I carried out so far, it seems that modifying the replication source and then killing the "WAL receiver" thread is a procedure that allows us to shift the replication source without the need for a service restart.
This is even more efficient compared to the MySQL solution, given the time taken for the recovery and flexibility.
What I am still wondering is if this might cause some data inconsistency issues or not. I asked some of the PG experts inside the company, and it seems that the process should be relatively safe, but I would appreciate any feedback/comments in case you know this may not be a safe operation.
Good PostgreSQL to everybody!
