In today’s article, I will tell you Creating A Hot Standby In EDB PostgreSQL.<\/p>\n
EDB PostgreSQL Installation link:<\/p>\n
https:\/\/dbtut.com\/index.php\/2022\/03\/22\/edb-postgres-13-installation-on-oracle-linux-7-8\/<\/pre>\nWe need two servers with EDB Postgresql installed to create Stanby. If you have not installed, you can install from the link above.<\/p>\n
There are operations that need to be done on both the master and standby server.<\/p>\n
We will start with the operations that need to be done on the master server.<\/p>\n
Master Server IP: 192.168.1.130\r\nStandby Server IP: 192.168.1.14<\/pre>\nActions to be taken on the Master Server:<\/h4>\n
1. We check if the master database is in archive mode.<\/p>\n
postgres=# show archive_mode;\r\n archive_mode \r\n--------------\r\n off\r\n(1 row)<\/pre>\n2. Since the database is not in archive mode, we put the database in archive mode.<\/p>\n
We ensure that our archives are sent to the standby database with the archive_command parameter.<\/p>\n
With vi, we change the contents of the postgresql.conf file as follows.<\/p>\n
bash-4.2$ vi \/var\/lib\/edb\/as13\/data\/postgresql.conf\r\narchive_mode = on\r\narchive_command = 'rsync -a %p enterprisedb@192.168.1.14:\/var\/lib\/edb\/as13\/ARCHIVELOG\/%f'<\/pre>\n3. Since we will create stanby later, we also set the following parameters in the postgresql.conf file.<\/p>\n
bash-4.2$ vi \/var\/lib\/edb\/as13\/data\/postgresql.conf\r\nwal_level = replica\r\nmax_wal_senders = 3\r\nmax_replication_slots = 4<\/pre>\n4. We must restart the postgresql service for the changes we made in the postgresql.conf file to take effect.<\/p>\n
$ systemctl restart edb-as-13<\/pre>\n5. We create the Replication user.<\/p>\n
bash-4.2$ psql postgres\r\npostgres# CREATE ROLE replication WITH REPLICATION PASSWORD 'pass' LOGIN<\/pre>\n6. 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.<\/p>\n
bash-4.2$ vi \/var\/lib\/edb\/as13\/data\/postgresql.conf\r\nlisten_addresses = '*'\r\n-bash-4.2$ vi \/var\/lib\/edb\/as13\/data\/pg_hba.conf\r\n# IPv4 local connections:\r\nhost all all 0.0.0.0\/0 md5<\/pre>\n7. Add the following line to the pga_hba.conf file. (The IP of the standby server is set.)<\/p>\n
-bash-4.2$ vi \/var\/lib\/edb\/as13\/data\/pg_hba.conf\r\nhost replication replication 192.168.1.14\/32 md5<\/pre>\n8. After the change is made, the service is restarted.<\/p>\n
$ systemctl restart edb-as-13<\/pre>\nActions to be taken on Standby Server:<\/h4>\n
1. We stop the service.<\/p>\n
$ systemctl stop edb-as-13<\/pre>\n2. We back up the data directory.<\/p>\n
-bash-4.2$ mv \/var\/lib\/edb\/as13\/data\/ \/var\/lib\/edb\/as13\/data_old<\/pre>\n3. We connect to the master server, take a backup and restore it.<\/p>\n
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<\/pre>\n4. We create the standby.signal file.<\/p>\n
bash-4.2$ touch \/var\/lib\/edb\/as13\/data\/standby.signal<\/pre>\n5. We start the service.<\/p>\n
$ systemctl start edb-as-13<\/pre>\nWhen 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.<\/p>\n
select now(),pg_last_xact_replay_timestamp();<\/pre>\nI hope it will be a useful document for you.<\/p>\n
<\/p>\n
<\/div>\n