Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s post, I will show you Creating A Hot Standby In PostgreSQL.
PostgreSQL Community 13 Installation:
1 | https://dbtut.com/index.php/2022/03/13/postgresql-community-13-installation-on-oracle-linux-7/ |
To create Stanby, we need two servers with Postgresql Community 13 installed.
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.
Master Server IP:192.168.1.12
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/pgsql/13/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/pgsql/13/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 postgresql-13.service |
5. Replication user is created.
1 2 | bash-4.2$ psql postgres postgres# CREATE ROLE replication WITH REPLICATION PASSWORD 'pass' LOGIN |
6. To access the database from outside, the listen address should be set as follows and the following changes should be made in pg_hba.conf.
1 2 3 4 5 | bash-4.2$ vi /var/lib/pgsql/13/data/postgresql.conf listen_addresses = '*' -bash-4.2$ vi /var/lib/pgsql/13/data/pg_hba.conf # IPv4 local connections: host all all 0.0.0.0/0 md5 |
7. Add the following line to the pg_hba.conf file. (We set the IP of the standby server.)
1 2 | -bash-4.2$ vi /var/lib/pgsql/13/data/pg_hba.conf host replication replication 192.168.1.14/32 md5 |
8. After the change is made, we restart the service.
1 | $ systemctl restart postgresql-13.service |
Actions to be taken on Standby Server:
1.We stop the service.
1 | $ systemctl stop postgresql-13.service |
2.We back up the second data directory.
1 | -bash-4.2$ mv /var/lib/pgsql/13/data/ /var/lib/pgsql/13/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.12 -U postgres -D /var/lib/pgsql/13/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/pgsql/13/data/standby.signal |
5. We start the service.
1 | $ systemctl start postgresql-13.service |
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.
