1

I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time. I have set up replication, now as we don't want to change the database connection string in the application during failover, I need to set up VIP for the same so that I provide only one IP in the connection string.

I have used this article for setting and testing VIP. I am able to setup VIP on a machine and able to connect PostgreSQL with this VIP. But when I tried to assign the same VIP to the slave server, i got an error that cannot assign requested address bind.

I need help to setup VIP and make it work with streaming replication so that applications always point to the master server i.e. point to new master after failover.

lets suppose a case , if VIP is assigned to master and that VM goes down, how will I unassign VIP from old master and assign to a new master.

2 Answers 2

0

The VIP should only be assigned to one host: in a replication system, it must be assigned to the current primary/master server. Only failover or switchover should move the VIP to the standby/slave server.

2
  • if vip is assigned to master and that VM goes down, how will I unassign VIP from old master and assign to a new master
    – YogeshR
    Commented Feb 21, 2020 at 8:14
  • if master VM goes down, VIP should also go down and to assign it to new master you only need to create the VIP on new master. Usually you don't do this manually but use some clustering software to do this automatically like for example clusterlabs.github.io/PAF
    – pifor
    Commented Feb 21, 2020 at 8:44
0

What your link describes is not a true1 virtual IP address (VIP) implementation; it simply shows how to assign an additional IP address to an interface. Since it's not a true VIP, it can only be assigned to one host at a time -- the one that's currently primary.

Having said that, your chosen approach can work. Reassignment of the IP address is just one of the things that should happen during failover. Typically one uses some sort of automation, e.g. repmgr, to detect failures and execute the required steps. One then would include the IP address assignment among those steps.

For example, if you choose to set up repmgr, you can put the IP address assignment commands into a shell script and instruct repmgr to run it when needed, by adding to its configuration file an event_notification_command property that would cause the script to be invoked in response to the standby_switchover and standby_promote events.


1 - A true VIP is not assigned to any interface; instead, a routing process accepts traffic destined to the VIP and redirects it to an appropriate real IP address. In such case you don't need to do anything extra for failover: the router will detect that one of the target IP addresses is not responding and send traffic to the alternative.


Disclosure: I work for the company that maintains repmgr.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.