This is the 24th day of my participation in the August More Text Challenge

The environment

Operating system:

CentOS Linux release 7.6.1810 (Core)

Database Version:

PostgreSQL 12.4

IP:

192.168.100.170 Primary library 192.168.100.202 Secondary library

The implementation steps

The master library creates an account to synchronize data

postgres=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
Copy the code

Add access control to the pg_hba.conf file of the primary library

Host replication replica 192.168.100.202/32 TrustCopy the code

Add primary/secondary synchronization parameters to the postgresql.conf file of the primary library

wal_level = hot_standby 
max_wal_senders = 8 
wal_keep_segments = 64 
wal_sender_timeout = 60s
max_connections = 100 
Copy the code

The main library to restart

[postgres@yuan data]$ pg_ctl restart -D $PGDATA -l $PGLOG
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
Copy the code

The slave library verifies that the master library is accessible

Return to enter the password to indicate access

[postgres@dj ~]$PSQL -h 192.168.100.170 -u postgres Password for user postgres:Copy the code

Stop from the library

[postgres@dj ~]$ pg_ctl stop -D $PGDATA -l $PGLOG
waiting for server to shut down.... done
server stopped
Copy the code

Clear the slave data file

[postgres@dj data]$ rm -rf  /app/pgsql/data/*
[postgres@dj data]$ ll
total 0
Copy the code

Pull primary library data file

[postgres@dj data]$pg_basebackup -h 192.168.100.170 -d /app/ PGSQL /data -p 5432 -u replica-FP-XS-PV-r --checkpoint=fast Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/D000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_17064" 50729/50729 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/D000100 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed [postgres@dj data]$ ll total 128 -rw-------. 1 postgres postgres 224 Jul 12 03:43 backup_label drwx------. 7 postgres postgres 4096 Jul 12 03:43 base drwx------. 2 postgres postgres 4096 Jul 12 03:43 global drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_commit_ts drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_dynshmem -rw-------. 1 postgres postgres 4886 Jul 12 03:43 pg_hba.conf -rw-------. 1 postgres postgres 1636 Jul 12 03:43 pg_ident.conf drwx------. 4 postgres postgres 4096 Jul 12 03:43 pg_logical drwx------. 4 postgres postgres 4096 Jul 12 03:43 pg_multixact drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_notify drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_replslot drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_serial drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_snapshots drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_stat drwx------. 2 postgres  postgres 4096 Jul 12 03:43 pg_stat_tmp drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_subtrans drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_tblspc drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_twophase -rw-------. 1 postgres postgres 3 Jul 12 03:43 PG_VERSION drwx------. 3 postgres postgres 4096 Jul 12 03:43 pg_wal drwx------. 5 postgres postgres 4096 Jul 12 03:43 pg_walminer drwx------. 2 postgres postgres 4096 Jul 12 03:43 pg_xact -rw-------. 1 postgres postgres 267 Jul 12 03:43 postgresql.auto.conf -rw-------. 1 postgres postgres 27115 Jul 12 03:43 postgresql.conf -rw-------. 1 postgres postgres 30 Jul 12 03:43 postmaster.opts.bak -rw-------. 1 postgres postgres 0 Jul 12 03:43 standby.signalCopy the code

Modify primary/secondary synchronization parameters in the postgresql.conf file of the secondary library

Delete the synchronization parameters added by the main library and add the following parameters:

Primary_conninfo = 'host=192.168.100.170 port=5432 user= Replica Password =replica' recovery_target_timeline = latest hot_standby = on max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s hot_standby_feedback = on Max_connections = 200 # greater than the primary max_worker_processes = 20Copy the code

Starting from the library

[postgres@dj data]$ pg_ctl start -D $PGDATA -l $PGLOG
waiting for server to start.... done
server started
Copy the code

Primary/secondary synchronization authentication

Postgres =# select client_addr,usename,backend_start, application_NAME,sync_state,sync_priority FROM pg_stat_replication; client_addr | usename | backend_start | application_name | sync_state | sync_priority -----------------+---------+-------------------------------+------------------+------------+--------------- 192.168.100.202 | up | 2021-08-24 18:03:32. 089937 + 08 | walreceiver | async | 0 - delete database created test observation from the create database library is synchronization test; drop database test;Copy the code