EDB-2 node

https://www.dbi-services.com/blog/setup-a-two-node-postgres-ha-cluster-using-edb-advanced-server-and-efm/

Some time ago I had a customer looking for a two node PostgreSQL Master/Replica solution. As we need Oracle compatibility in a later step, we decided to go with the EnterpriseDB tools. This article should give you an introduction on how to setup the environment.

Prerequisites

There are just some few things, that you need to prepare.
You need (at least) three servers with:

• EPEL repository available
• Subscription for EDB
• EDB repository available

To make everything working with our DMK some folders and links are needed:

1 2 3 4 5 6 7

mkdir -p /u01/app/postgres/product/as11 mkdir -p /u01as11 mkdir -p /usr/edb mkdir -p /u02/pgdata/11/PG1 ln -s /u02/pgdata/11/PG1/ /u01as11/data ln -s /u01/app/postgres/product/as11/ /usr/edb/as11 yum install -y unzip xorg-x11-xauth screen

EDB Advanced Server

Installation

Let’s start with the installation of the EDB Advanced Server  This is really straight forward:

1 2 3 4 5

$ yum install edb-as11-server $ chown enterprisedb:enterprisedb /u02/pgdata/11/epg1/ $ chown -R enterprisedb:enterprisedb /u01/app/ $ rm -rf /u01as11/backups/ $ passwd enterprisedb

Now you can install and configure our DMK. Make sure to adjust var::PGUSER::=::nooption::”enterprisedb” in the dmk.conf.

As soon as the installation is done, you can initialize a new primary cluster.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112

enterprisedb@edb1:/var/lib/edb/ [PG1] cat /etc/pgtab PG1:/u01/app/postgres/product/as11/:/u02/pgdata/11/PG1/:5444:Y enterprisedb@edb1:/var/lib/edb/ [PG1] dmk enterprisedb@edb1:/var/lib/edb/ [pg950] PG1   ********* dbi services Ltd. ****************   STATUS           : CLOSED   ******************************************** enterprisedb@ad1:/var/lib/edb/ [PG1] sudo mkdir -p /u02/pgdata/PG1 enterprisedb@ad1:/var/lib/edb/ [PG1] sudo chown enterprisedb:enterprisedb /u02/pgdata/PG1 enterprisedb@ad1:/var/lib/edb/ [PG1] /u01/app/postgres/product/as11/bin/initdb --pgdata=/u02/pgdata/PG1/ --pwprompt --data-checksums --auth=md5 The files belonging to this database system will be owned by user "enterprisedb". This user must also own the server process.   The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english".   Data page checksums are enabled.   Enter new superuser password: Enter it again:   fixing permissions on existing directory /u02/pgdata/PG1 ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default timezone ... Europe/Berlin selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok creating edb sys ... ok loading edb contrib modules ... edb_redwood_bytea.sql ok edb_redwood_date.sql ok dbms_alert_public.sql ok dbms_alert.plb ok dbms_job_public.sql ok dbms_job.plb ok dbms_lob_public.sql ok dbms_lob.plb ok dbms_output_public.sql ok dbms_output.plb ok dbms_pipe_public.sql ok dbms_pipe.plb ok dbms_rls_public.sql ok dbms_rls.plb ok dbms_sql_public.sql ok dbms_sql.plb ok dbms_utility_public.sql ok dbms_utility.plb ok dbms_aqadm_public.sql ok dbms_aqadm.plb ok dbms_aq_public.sql ok dbms_aq.plb ok dbms_profiler_public.sql ok dbms_profiler.plb ok dbms_random_public.sql ok dbms_random.plb ok dbms_redact_public.sql ok dbms_redact.plb ok dbms_lock_public.sql ok dbms_lock.plb ok dbms_scheduler_public.sql ok dbms_scheduler.plb ok dbms_crypto_public.sql ok dbms_crypto.plb ok dbms_mview_public.sql ok dbms_mview.plb ok dbms_session_public.sql ok dbms_session.plb ok edb_bulkload.sql ok edb_gen.sql ok edb_objects.sql ok edb_redwood_casts.sql ok edb_redwood_strings.sql ok edb_redwood_views.sql ok utl_encode_public.sql ok utl_encode.plb ok utl_http_public.sql ok utl_http.plb ok utl_file.plb ok utl_tcp_public.sql ok utl_tcp.plb ok utl_smtp_public.sql ok utl_smtp.plb ok utl_mail_public.sql ok utl_mail.plb ok utl_url_public.sql ok utl_url.plb ok utl_raw_public.sql ok utl_raw.plb ok commoncriteria.sql ok waitstates.sql ok installing extension edb_dblink_libpq ... ok installing extension edb_dblink_oci ... ok installing extension pldbgapi ... ok snap_tables.sql ok snap_functions.sql ok dblink_ora.sql ok sys_stats.sql ok finalizing initial databases ... ok syncing data to disk ... ok   Success. You can now start the database server using:       /u01/app/postgres/product/as11/bin/pg_ctl -D /u02/pgdata/PG1/ -l logfile start   enterprisedb@ad1:/var/lib/edb/ [PG1]

Than adjust wal_keep_segments and afterwards the edb-as service can be enabled and started.

1 2 3

$ echo "wal_keep_segments=100" >> $PGDATA/postgresql.auto.conf $ sudo systemctl enable edb-as-11.service $ sudo systemctl start edb-as-11

To be sure everything works as expected, reboot the server (if possible).

All above steps should also be done on your additional nodes, but without the systemctl start.

Configuration

First, on Node 1 (Master) you need to create the replication role.

1 2	postgres=# create role replication with REPLICATioN PASSWORD 'replication' login; CREATE ROLE

Second, you need to add replication to pg_hba.conf.

1 2 3 4 5

local   replication    all             127.0.0.1/32            trust host    replication    all             192.168.22.53/32        trust host    replication    all             192.168.22.51/32        trust host    replication    all             192.168.22.52/32        trust host    replication    all             ::1/128                 trust

And last but not least, your should exchange the ssh-key of all nodes:

1 2 3 4

enterprisedb@edb1:/u01 [PG1] ssh-keygen enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb1 enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb2 enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb3

Create the replica

As already mentioned, you need almost all steps done on Node 2 as well, but without starting the service.
Make sure to have all hosts in pg_hba.conf of Master.

To create the replication create a pg_basebackup into Node 2:

1 2	enterprisedb@edb2:/u01 [PG1] pg_basebackup -h 192.168.22.51 -U replication -p 5432 -D $PGDATA -Fp -Xs -P -R 49414/49414 kB (100%), 1/1 tablespace

Once finish, check if the recovery.conf is available and add the following lines:

1 2	enterprisedb@edb2:/u01 [PG1] echo "recovery_target_timeline = 'latest'" >> /u02/pgdata/11/PG1/recovery.conf enterprisedb@edb2:/u01 [PG1] echo "trigger_file='/u02/pgdata/11/PG1/trigger_failover'" >> /u02/pgdata/11/PG1/recovery.conf

To test, if the recovery is working, start the cluster and check the recovery status.

1 2 3 4 5 6 7 8 9 10

enterprisedb@edb2:/u01 [PG1] pgstart enterprisedb@edb2:/u01 [PG1] psql -U enterprisedb -c "select pg_is_in_recovery()" postgres  pg_is_in_recovery -------------------  t (1 row)   enterprisedb@edb2:/u01 [PG1] sudo systemctl enable edb-as-11.service enterprisedb@edb2:/u01 [PG1] pgstop enterprisedb@edb2:/u01 [PG1] systemctl start edb-as-11

EDB Postgres Failover Manager (EFM)

To make our two Node setup High Available, we need to install the EDB Postgres Failover Manager  on three nodes. On the both installed with the Master / Replica and on a third one as a witness server.

Installation

Installation for EFM is straight forward as well, therefore your have to do the following steps on all three nodes. To use EFM toghether with our DMK, you need to create some links.

1 2 3 4 5 6

$ sudo yum install edb-efm37 $ sudo yum install java-1.8.0-openjdk $ sudo chown -R enterprisedb:enterprisedb /etc/edb/efm-3.7/ $ cat /etc/edb/efm-3.7/efm.nodes $ sudo ln -s /usr/edb/efm-3.7 /usr/edb/efm $ sudo ln -s /etc/edb/efm-3.7 /etc/edb/efm

Configuration

On the master you have to set a password for the enterprisedb user and encrypt the password using EFM.

1 2	$ psql -U enterprisedb -c "alter user enterprisedb with password '*******'" postgres $ /usr/edb/efm/bin/efm encrypt efm

The enrypted password generated by efm encrypt will be needed in the efm.properties files

As next step we need an efm.properties file on Node 1 and 2 (parameters to adjust below).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ cp /u01/app/postgres/local/dmk/templates/postgres/efm.properties.in /etc/edb/efm-3.7/efm.properties $ vi /etc/edb/efm-3.7/efm.properties   db.user=enterprisedb   db.password.encrypted=f17db6033ef1be48ec1955d38b4c9c46   db.port=5400   db.database=postgres   db.bin=/u01/app/postgres/product/as11/bin   db.recovery.dir=/u02/pgdata/11/EPAS   bind.address=192.168.22.51:7800   admin.port=7809   is.witness=false   virtualIp=192.168.22.55   virtualIp.interface=enp0s8   virtualIp.prefix=24   virtualIp.single=true $ sudo chown enterprisedb:enterprisedb /etc/edb/efm/efm.properties

We also need a efm.nodes file to have all nodes of the cluster.

1 2 3 4 5 6

$ cat /etc/edb/efm/efm.nodes # List of node address:port combinations separated by whitespace. # The list should include at least the membership coordinator's address. 192.168.22.51:7800 192.168.22.52:7800 192.168.22.53:7800 $ chown efm:efm efm.nodes $ chmod 660 /etc/edb/efm/efm.nodes

To conclude, enable and start the efm-3.7.service.

1 2	sudo systemctl enable efm-3.7.service sudo systemctl start efm-3.7.service

On node 3 we need to create a efm.properties file as well, but we need the efm.properties_witness file of dmk as draft.

1	$ cp /u01/app/postgres/local/dmk/templates/postgres/efm.properties_witness /etc/edb/efm-3.7/efm.properties

Adjust the parameters as shown in the step for node 1 and 2, but be careful to have:

1	is.witness=true

Afterwards start the efm-3.7 service on node 3 as well.

1	$ sudo systemctl start efm-3.7.service

In the end, you can check if everything is running as expected using EFM.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

$ efm cluster-status efm Cluster Status: efm           Agent Type  Address              Agent  DB       VIP         -----------------------------------------------------------------------         Standby     192.168.22.51        UP     UP       192.168.22.55         Master      192.168.22.52        UP     UP       192.168.22.55*         Witness     192.168.22.53        UP     N/A      192.168.22.55   Allowed node host list:         192.168.22.52 192.168.22.51 192.168.22.53   Membership coordinator: 192.168.22.52   Standby priority host list:         192.168.22.51   Promote Status:           DB Type     Address              WAL Received LSN   WAL Replayed LSN   Info         ---------------------------------------------------------------------------         Master      192.168.22.52                           0/110007B8         Standby     192.168.22.51        0/110007B8         0/110007B8           Standby database(s) in sync with master. It is safe to promote.

That’s it, now you have a Master/Replica system using EDB tools.

In a next step we will have a look at the setup of the cluster monitoring using EDB Enterprise Manager.