How To Install PostgreSQL Replication With Replication Slots

dbtut

5 years ago

First of all, we must install PostgreSQL on Primary Server and perform initdb operations. You can find out how to do this in the article “How To Install PostgreSQL On centos/redhat“.

On the Primary Server:

Switch to the postgres user with the su postgres command and connect to the primary server with the psql -h primaryserverip command.

Then run the following commands:

create role replication login replication;
select pg_create_physical_replication_slot('yoursecondaryservername');
ALTER SYSTEM SET synchronous_standby_names TO 'yoursecondaryservername';

create role replication login replication;

select pg_create_physical_replication_slot('yoursecondaryservername');

ALTER SYSTEM SET synchronous_standby_names TO 'yoursecondaryservername';

Edit the pg_hba.conf file by using the vi command in the directory where we installed PostgreSQL and update it as follows.

Note: If there is a record at the top in the pg_hba.conf file, it does not care for the values at the bottom. So make sure these changes are at the top.

host    all             all             primaryserverip/32      trust
host    replication     replication     secondaryserverip/32    trust

1 2	host all all primaryserverip/32 trust host replication replication secondaryserverip/32 trust

We then transfer our data to the secondary server using the base backup method. We need to run the following command on the secondary server.

On the Secondary Server with postgres user:

pg_basebackup --pgdata=/yourdatadirectory \
      --waldir=/yourwaldirectory \
      --write-recovery-conf --wal-method=stream \
      --checkpoint=fast \
      --progress --verbose\
      --slot=yoursecondaryservername \
      --host=yourprimaryserverip \
      --port=5432 \
      --username=replication

pg_basebackup --pgdata=/yourdatadirectory \

--waldir=/yourwaldirectory \

--write-recovery-conf --wal-method=stream \

--checkpoint=fast \

--progress --verbose\

--slot=yoursecondaryservername \

--host=yourprimaryserverip \

--port=5432 \

--username=replication

Configure recovery.conf

After running pg_basebackup, we update the recovery.conf under the data directory with vi command and update it as follows. Do not copy and paste this file as it is. You will only add the following two lines to the relevant sections.

Note: The recovery.conf file was removed along with postgresql 12. If recovery.conf is present in the data directory, you cannot start postgresql.

application_name=synchronous_standby_name_you_set_on_primary_server
trigger_file='/dbwhite/dbats/promotdbats.data'

1 2	application_name=synchronous_standby_name_you_set_on_primary_server trigger_file='/dbwhite/dbats/promotdbats.data'

The final version should be as follows:

standby_mode = 'on'
primary_conninfo = 'user=replication passfile=''/var/lib/pgsql/.pgpass'' host=yourprimaryserverip port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name='synchronous_standby_name_you_set_on_primary_server'
primary_slot_name = 'yoursecondaryservername'
trigger_file='/dbwhite/dbats/promotdbats.data'

standby_mode = 'on'

primary_conninfo = 'user=replication passfile=''/var/lib/pgsql/.pgpass'' host=yourprimaryserverip port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name='synchronous_standby_name_you_set_on_primary_server'

primary_slot_name = 'yoursecondaryservername'

trigger_file='/dbwhite/dbats/promotdbats.data'

Note: With PostgreSQL 12, standby_mode = ‘on’ no longer exists and PostgreSQL writes other information in the recovery.conf to postgresql.auto.conf. Also trigger_file’s name changed to promote_trigger_file.

Then go to postgresql.conf and change the ip. Because basebackup copy postgresql.conf from primary server. Therefore we must change IP information on postgresql.conf. İf its localhost do not change.

Change pg_hba.con on the Secondary Server

Then we change the pg_hba on the secondary server as follows. If we perform failover, these settings will be needed.

host    all             all             primaryserverip/32    trust
host    all             all             secondaryserverip/32  trust
host    replication     replication     primaryserverip/32    trust

host all all primaryserverip/32 trust

host all all secondaryserverip/32 trust

host replication replication primaryserverip/32 trust

Note: After this change, you must restart or reload the postgresql on the secondary server.

Then start postgresql service on secondary server.

systemctl start postgresql-11

1	systemctl start postgresql-11

Then go to primary server and connect postgresql and execute the below command to check replication start succesfully.

On Primary Server:

\x
select * from pg_stat_replication;

1 2	\x select * from pg_stat_replication;

As you can see, replication works asynchronously. You can synchronize replication by running the following command on the primary server.

On Primary Server:

ALTER SYSTEM SET synchronous_commit TO 'remote_write';

1	ALTER SYSTEM SET synchronous_commit TO 'remote_write';

Check PostgreSQL Replication Status

Then run the following command and check the replication status again. As you can see, replication has become synchronous.

select pg_reload_conf();

1	select pg_reload_conf();

You may want to do a failover test. You can read the article “How To Failover and Failback PostgreSQL Replication” to do a failover test.