01.02. Creation STANDBY database on pgsql-db02

                <<< Back   Next >>>

1. Shutdown PRIMARY database on pgsql-db01

[root]# systemctl stop postgresql-14.service

2. Add parameter on pgsql-db01 ($PGDATA/postgresql.conf)

#---------------------------------------------------

# For connection

#---------------------------------------------------

listen_addresses = '*'

#---------------------------------------------------

# For replication

#---------------------------------------------------

wal_level = 'replica'

archive_mode = 'on'

archive_command = 'test ! -f /srv/postgres/archivedir/%f && cp %p /srv/postgres/archivedir/%f && chmod g+r /srv/postgres/archivedir/%f'

#---------------------------------------------------

# ERROR REPORTING AND LOGGING

#---------------------------------------------------

log_destination = 'stderr'

log_directory = 'pg_log'

log_filename = 'postgresql-%a.log'

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 0

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h ' 

log_timezone = 'W-SU'

3. ADD ACL rule on pgsql-db01 ($PGDATA/pg_hba.conf)

# Allow replication connections from localhost, by a user with the replication privilege

host    replication     postgres        192.168.1.82/32         trust

host    replication     postgres        192.168.1.81/32         trust

4. Restart PRIMARY database on pgsql-db01

[root]# systemctl start postgresql-14.service

5. Creation STANDBY on pgsql-db02

[postgres]$ pg_basebackup -h 192.168.1.81 -U postgres -D /srv/postgres/data/14

6. Creation file standby.signal STANDBY on pgsql-db02

[postgres]$ touch $PGDATA/standby.signal

7. Add on $PGDATA/ (postgresql.auto.conf)

restore_command = 'cp /srv/postgres/archivedir/%f %p'

primary_conninfo = 'host=192.168.1.81 port=5432 user=postgres'

hot_standby = 'on'

8. Startup standby database on pgsql-db02

[root]# systemctl start postgresql-14.service

9. Check role database on pgsql-db02

psql > select pg_is_in_recovery();

                <<< Back   Next >>>