Infrastructure at your Service

Mouhamadou Diaw

EDB Failover Manager 3.3 With Virtual Ip Address

In a previous blog , I talked about installing and configuring Edb Failover Manager with PostgreSQL. The configuration of a Virtual Ip Address was not discussed in this previous article.In this blog I am going to show how we can implement a virtual IP with EFM.
We suppose that
-The standby is already built
-EFM is configured and running (see here for the configuration)
Below our configuration ( Iam using PostgreSQL 11.1 with Centos 7)

[[email protected] efm-3.3]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A
        Master      192.168.22.101       UP     UP
        Standby     192.168.22.102       UP     UP

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.101

Standby priority host list:
        192.168.22.102

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.101       0/301BE38
        Standby     192.168.22.102       0/301BE38

        Standby database(s) in sync with master. It is safe to promote.
[[email protected] efm-3.3]#

To resume we have three servers
-dbi-pg-tools : the witness with IP 192.168.22.100
-dbi-pg-essentials : the master with IP 192.168.22.101
-dbi-pg-essentials_2 : the standby with IP 192.168.22.102
Let’s say that we want to configure a virtual IP 192.168.22.105.
The first step is to update the efm.properties on both nodes with following lines

virtualIp=192.168.22.105
virtualIp.interface=enp0s9   -- name of my interface
virtualIp.prefix=24
virtualIp.single=true

Then restart efm on both nodes.

[[email protected] network-scripts]#  service efm-3.3 start

Running again the status command should show the VIP address. Restarting efm should be enough but if ever the virtual IP is not shown we can use following command to add the virtual IP.

[[email protected] network-scripts]# /usr/edb/efm-3.3/bin/efm_address add4 enp0s9 192.168.22.105/24

Then the status should be

[[email protected] ~]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A      192.168.22.105
        Master      192.168.22.101       UP     UP       192.168.22.105*
        Standby     192.168.22.102       UP     UP       192.168.22.105

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.101

Standby priority host list:
        192.168.22.102

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.101       0/301BEE0
        Standby     192.168.22.102       0/301BEE0

        Standby database(s) in sync with master. It is safe to promote.
[[email protected] ~]#

We can manually ping the virtual IP from both servers. There should not exist any loss packets

[[email protected] ~]# ping -q -c3 -w5 192.168.22.105
PING 192.168.22.105 (192.168.22.105) 56(84) bytes of data.

--- 192.168.22.105 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.278/0.366/0.528/0.116 ms
[[email protected] ~]#

And from the master we can see that the VIP address is assigned to enp0s9

[[email protected] network-scripts]# ifconfig enp0s9                       
enp0s9: flags=4163  mtu 1500
        inet 192.168.22.105  netmask 255.255.255.0  broadcast 0.0.0.0
        ether 08:00:27:53:1c:ed  txqueuelen 1000  (Ethernet)
        RX packets 3394  bytes 397433 (388.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3096  bytes 571448 (558.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[[email protected] network-scripts]#

So we can use the virtual address to connect to the primary database

[[email protected] ~]$ psql -h 192.168.22.105
Password for user postgres:
psql (11.1)
Type "help" for help.

postgres=# \c sample
You are now connected to database "sample" as user "postgres".
sample=# insert into mytab values(1);
INSERT 0 1
sample=#

And now let’s do a switchover

[[email protected] ~]# /usr/edb/efm-3.3/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
[[email protected] ~]#

A few moment after the switchover, we can see that the vip is now assigned to the new master

[[email protected]_2 efm-3.3]# ifconfig enp0s9
enp0s9: flags=4163  mtu 1500
        inet 192.168.22.105  netmask 255.255.255.0  broadcast 0.0.0.0
        ether 08:00:27:4d:4d:4d  txqueuelen 1000  (Ethernet)
        RX packets 1235  bytes 149947 (146.4 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 837  bytes 525953 (513.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

That we can verify by running the status command

[[email protected] ~]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A      192.168.22.105
        Standby     192.168.22.101       UP     UP       192.168.22.105
        Master      192.168.22.102       UP     UP       192.168.22.105*

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.102

Standby priority host list:
        192.168.22.101

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.102       0/301E260
        Standby     192.168.22.101       0/301E260

        Standby database(s) in sync with master. It is safe to promote.
[[email protected] ~]#

Conclusion :

In this blog we have seen how we can configure a virtual IP with EFM. Hope that will help

One Comment

  • Kishore kumar Davala says:

    VIP would help to share load balance between Master and Slave. It means that Whether Slave would support READS? Application which is configured DB using Virtual IP would share READS between Master and Slave?. Can we set any additional parameters in /etc/edb/efm-3.0/efm.properties file to achieve load balance in terms reads (50% Reads from Master[192.168.22.101] – 50% Reads from Slave[192.168.22.102])

    Thanks in advance,
    Kishore

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant