Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will tell you Creating A Hot Standby In EDB PostgreSQL.
EDB PostgreSQL Installation link:
1 | https://dbtut.com/index.php/2022/03/22/edb-postgres-13-installation-on-oracle-linux-7-8/ |
We need two servers with EDB Postgresql installed to create Stanby. If you have not installed, you can install from the link above.
There are operations that need to be done on both the master and standby server.
We will start with the operations that need to be done on the master server.
1 2 | Master Server IP: 192.168.1.130 Standby Server IP: 192.168.1.14 |
Actions to be taken on the Master Server:
1. We check if the master database is in archive mode.
1 2 3 4 5 | postgres=# show archive_mode; archive_mode -------------- off (1 row) |
2. Since the database is not in archive mode, we put the database in archive mode.
We ensure that our archives are sent to the standby database with the archive_command parameter.
With vi, we change the contents of the postgresql.conf file as follows.
1 2 3 | bash-4.2$ vi /var/lib/edb/as13/data/postgresql.conf archive_mode = on |
3. Since we will create stanby later, we also set the following parameters in the postgresql.conf file.
1 2 3 4 | bash-4.2$ vi /var/lib/edb/as13/data/postgresql.conf wal_level = replica max_wal_senders = 3 max_replication_slots = 4 |
4. We must restart the postgresql service for the changes we made in the postgresql.conf file to take effect.
1 | $ systemctl restart edb-as-13 |
5. We create the Replication user.
1 2 | bash-4.2$ psql postgres postgres# CREATE ROLE replication WITH REPLICATION PASSWORD 'pass' LOGIN |
6. To access the database from outside, we must set the listen address as below and the following changes should be made in pg_hba.conf.
1 2 3 4 5 | bash-4.2$ vi /var/lib/edb/as13/data/postgresql.conf listen_addresses = '*' -bash-4.2$ vi /var/lib/edb/as13/data/pg_hba.conf # IPv4 local connections: host all all 0.0.0.0/0 md5 |
7. Add the following line to the pga_hba.conf file. (The IP of the standby server is set.)
1 2 | -bash-4.2$ vi /var/lib/edb/as13/data/pg_hba.conf host replication replication 192.168.1.14/32 md5 |
8. After the change is made, the service is restarted.
1 | $ systemctl restart edb-as-13 |
Actions to be taken on Standby Server:
1. We stop the service.
1 | $ systemctl stop edb-as-13 |
2. We back up the data directory.
1 | -bash-4.2$ mv /var/lib/edb/as13/data/ /var/lib/edb/as13/data_old |
3. We connect to the master server, take a backup and restore it.
1 | bash-4.2$ pg_basebackup -h 192.168.1.130 -U postgres -D /var/lib/edb/as13/data-U replication -v -P --wal-method=stream --write-recovery-conf |
4. We create the standby.signal file.
1 | bash-4.2$ touch /var/lib/edb/as13/data/standby.signal |
5. We start the service.
1 | $ systemctl start edb-as-13 |
When we complete the above steps, we will have the standby database. If you want to test it, you can query a table on the master server on the standby server, or you can check the standby with the query below.
1 | select now(),pg_last_xact_replay_timestamp(); |
I hope it will be a useful document for you.
