Thread: Question about postgres replication construction
Hi everyone,
I need some advice.
I need to set up a 3 node postgres 12 cluster.
The primary postgres server with a streaming replication hot standby which is managed with repmgr.
Besides these 2 servers I need to create another standby server which will be synced with wal files that are copied to the server.
This third node does not have any connection with the other two nodes and will only be updated through the wal files.
The third node will be created by taking a base backup of the primary node and restoring it to the PGDATA directory.
repmgr is not configured for node 3 because it has no connection with the other two nodes.
The primary connection parameter is uncommented in the postgresql.conf.
This construction works until I perform a switchover from node 1 to node 2 with repmgr, making node 2 the new primary and node 1 the new standby.
The standby on node 3 does not pick up the switch.
My question is: Does anyone have an idea in how to approach this? Is this a valid construction?
I know I'm missing something essential but at this moment I can't seem to see it.
Thanks in advance.
Regards
Marian
Hi everyone,
I need some advice.
I need to set up a 3 node postgres 12 cluster.
The primary postgres server with a streaming replication hot standby which is managed with repmgr.
Besides these 2 servers I need to create another standby server which will be synced with wal files that are copied to the server.
This third node does not have any connection with the other two nodes and will only be updated through the wal files.
The third node will be created by taking a base backup of the primary node and restoring it to the PGDATA directory.
repmgr is not configured for node 3 because it has no connection with the other two nodes.
The primary connection parameter is uncommented in the postgresql.conf.
This construction works until I perform a switchover from node 1 to node 2 with repmgr, making node 2 the new primary and node 1 the new standby.
The standby on node 3 does not pick up the switch.
My question is: Does anyone have an idea in how to approach this? Is this a valid construction?
I know I'm missing something essential but at this moment I can't seem to see it.
On Fri, 29 Oct 2021 at 14:07, MARIANE K <mskiesewetter@gmail.com> wrote:Hi everyone,
I need some advice.
I need to set up a 3 node postgres 12 cluster.
The primary postgres server with a streaming replication hot standby which is managed with repmgr.
Besides these 2 servers I need to create another standby server which will be synced with wal files that are copied to the server.
This third node does not have any connection with the other two nodes and will only be updated through the wal files.
The third node will be created by taking a base backup of the primary node and restoring it to the PGDATA directory.
repmgr is not configured for node 3 because it has no connection with the other two nodes.
The primary connection parameter is uncommented in the postgresql.conf.
This construction works until I perform a switchover from node 1 to node 2 with repmgr, making node 2 the new primary and node 1 the new standby.
The standby on node 3 does not pick up the switch.
My question is: Does anyone have an idea in how to approach this? Is this a valid construction?
I know I'm missing something essential but at this moment I can't seem to see it.
--Thanks,VijayMumbai, India
Hi Marian, On Fri, Oct 29, 2021 at 01:49:30PM +0200, MARIANE K wrote: > [...] > > By the way I've found my error. I forgot to change the archive_command on > node 2. After changing that it worked like a charm. > However i'm interested if anyone has other (better) ideas in how to tackle > this. I think it's going to be very hard to try suggesting something, since you never told us about the actual goal of your implementation. What do actually hope to achieve with the setup you described earlier in this thread? What properties and qualities should your system exhibit, and which shortcomings does it need and try to avoid? If you elaborate on that, someone might be able to come up with improvements, or at least comments on, your current architecture and implementation. -- with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +436502133337 xmpp: johannes@truschnigg.info