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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/GWI_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)