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:
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:
$ 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.
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.
$ 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.
postgres=# create role replication with REPLICATioN PASSWORD 'replication' login; CREATE ROLE
Second, you need to add replication to pg_hba.conf.
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:
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:
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:
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.
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.
$ 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.
$ 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).
$ 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.
$ 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.
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.
$ 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:
is.witness=true
Afterwards start the efm-3.7 service on node 3 as well.
$ sudo systemctl start efm-3.7.service
In the end, you can check if everything is running as expected using EFM.
$ 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.