PostgresSQL master-slave setup steps

PostgresSQL master-slave setup steps

Due to work requirements, I recently started to contact various databases and try high-availability solutions for various database products. I am sharing the master-slave configuration of postgresql today. It is actually quite simple. Follow the steps in this article to ensure the establishment of PG master-slave.

1. installation environment

192.168.0.136 master database
192.168.0.160 slave Database
PORT: 5432
USR: ipvs

2. The master database has been running for a period of time. Check the version of the master database to ensure that the version of the master database is the same.

# Psql -- version
Psql (PostgreSQL) 9.4.11
 
# Rpm-qa | grep postgres
Postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
Postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
Postgresql94-9.4.11-1PGDG.rhel6.x86_64

3. Install software of the same version on the slave Database

Check the installation status. The same software version as primary has been installed.

# Rpm-qa | grep postgres
Postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
Postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
Postgresql94-9.4.11-1PGDG.rhel6.x86_64

4. query the database location of the master database

# Su-postgres
$ Echo $ PGDATA
/Var/lib/pgsql/9.4/data
$ Cd/var/lib/pgsql/9.4/data
$ Ls
Base pg_clog pg_mirror.conf pg_log pg_multixact
Pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION
Postgresql. auto. conf postmaster. opts global pg_dynshmem pg_ident.conf
Pg_logical pg_notify pg_serial pg_stat pg_subtrans
Pg_twophase pg_xlog postgresql. conf postmaster. pid

Confirm the configuration file location

S = # show config_file;
Config_file
-----------------------------------------
/Var/lib/pgsql/9.4/data/postgresql. conf

View the data file directory

Postgres = # show data_directory;
Data_directory
-------------------------
/Var/lib/pgsql/9.4/data

5. To modify the parameter file of the master database, first query the existing parameter content of pg_assist.conf.

$ Cat pg_hba.conf | grep-v '^ #'
Local all peer
Host all 0.0.0.0/0 trust
Host all: 1/128 ident

6. Add

$ More pg_mirror.conf
Host replication replica 192.168.0.160 md5

In this way, you can set the replica user to perform stream replication requests from the network segment corresponding to 192.168.0.160.

7. Set the password, logon, and backup permissions for ipvs in the master database.

$ Psql
Postgres # create role replica login replication encrypted password 'replica123'

8. Modify postgresql. conf. Pay attention to the following settings:

Wal_level = hot_standby # This is the host that sets the master as wal.
Max_wal_senders = 10 # You can set up to several stream replication connections. If there are several slave nodes, set several
Wal_keep_segments = 256 # set the maximum number of xlogs retained by stream Replication
Wal_sender_timeout = 60 s # Set the timeout time for sending data from the stream replication host
Max_connections = 100 # note that the slave database's max_connections must be greater than the master database's
Archive_mode = on
Archive_command = 'cp % p/var/lib/pgsql/9.4/archive/% F'

9. Create the corresponding archive log storage path

Mkdir-p/var/lib/pgsql/9.4/archive/

10. Restart the master database to make the configuration take effect.

# Service postgresql-9.4 start
Starting postgresql-9.6 service: [OK]

11. Remotely connect to the primary database from the slave database to verify that the replica user can access the database normally.

Psql-h IP-address-p 5432 dbname usename
Psql-h 192.168.0.136-p 5432 ipvs replica

12. Perform a basic backup in the master database (the following Hot-standby files mainly use the data directory ):

Postgres = # SELECT pg_start_backup ('bak20170905 ');
$ Tar czvf/var/lib/pgsql/9.4/backups/backup_data.tar.gz.20170905/var/lib/pgsql/9.4/data
S = # SELECT pg_stop_backup ();

13. Copy the backup file sftp to the slave database, decompress the file, and replace the original data directory.

Cd/var/lib/FIG/9.4/
Mv data data_bk
Mv backup_data.tar.gz.20170905 backup_data.tar.gz
Tar-xzvf backup_data.tar.gz

14. delete some identification information and archive log files.

Rm-rf data/pg_xlog/
Mkdir-p data/pg_xlog/archive_status
Rm data/postmaster. pid

15. Find and copy the recovery. conf. sample file to the data directory.

Find/-name recovery. conf. sample
/Root/postgresql/postgresql-9.2.20/src/backend/access/transam/recovery. conf. sample
/Usr/pgsql-9.4/share/recovery. conf. sample
Copy
Cp/usr/pgsql-9.4/share/recovery. conf. sample/var/lib/pgsql/9.4/data/recovery. conf

16. Edit recovery. conf:

Standby_mode = on
Restore_command = 'cp/var/lib/pgsql/9.4/archive/% f % P' # This parameter also needs to be confirmed.
Primary_conninfo = 'host = 192.168.0.136 port = 5432 user = replica password = replica123 '# master server information and connected users. This information is the most important
Recovery_target_timeline = 'latest'

17. Copy the following configuration file

Cp/var/lib/pgsql/9.4/data. bk/postgresql. conf/var/lib/pgsql/9.4/data/postgresql. conf
Cp/var/lib/pgsql/9.4/data. bk/pg_hba.conf/var/lib/pgsql/9.4/data/pg_hba.conf

18. Then edit pstgresql. conf:

Hot_standby = on

19. Start Hot-Standby:

/Usr/pgsql-9.4/bin/postmaster-D/var/lib/pgsql/9.4/data -- port = 5432

20. Verify that the deployment is successful

Run the verification on the master node. The verification has been successfully set up, indicating that 5.160 is from the server, receiving the stream, and asynchronous stream replication.

Postgres = # select client_addr, sync_state from pg_stat_replication;
Client_addr | sync_state
------------- + ------------
192.168.0.160 | async

21. query more data synchronization information:

Postgres = # select usename, application_name, client_addr, client_port, backend_start, backend_xmin, state, sent_location, write_location, flush_location, replay_location, primary, sync_state from pg_stat_replication;
Usename | application_name | client_addr | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
--------- + Hour + ------------- + hour + -------------- + ----------- + --------------- + ---------------- + -------------- + hour + --------------- + ------------
Replica | walreceiver | 192.168.0.160 | 39375 | 17:49:22. 512393 + 08 | streaming | 5/1049488 | 5/1049488 | 5/1049488 | 5/1049488 | 0 | async

22. In addition, you can run ps aux | grep S on the master and slave nodes to view processes:

On the master server, you can see a wal sender process and an archiver process.

# Ps aux | grep postgres
Postgres 1104 0.0 0.1 324652 15120? S/usr/pgsql-9.4/bin/postmaster-D/var/lib/pgsql/9.4/data
Postgres 1111 0.0 0.0 324652 5844? Ss ipvs: wal writer process
Postgres 1113 0.0 0.0 179796 1544? Ss postgres: archiver process last was 00000000000000000000.00000060.backup
Postgres 8515 0.0 0.0 325448 3108? Ss ipvs: wal sender process replica 192.168.0.160 (39375) streaming 5/103A1D0

From the server, we can see that the wal explorer process and the recovering process are restoring the archive log.

$ Ps aux | grep postgres
Postgres 11508 0.0 0.1 324684 15128? S/usr/pgsql-9.4/bin/postmaster-D/var/lib/pgsql/9.4/data
Postgres 11510 0.0 0.0 324796 4336? Ss postgres: startup process recovering 000000010000000500000001
Postgres 11513 0.0 0.0 331892 3700? Ss postgres: wal receiver process streaming 5/103A1D0

23. You can see the archive log file received from the database in the following path.

# Pwd
/Var/lib/pgsql/9.4/data/pg_xlog
# Ls
000000010000000500000001 000000010000000500000002 archive_status

So far, the installation and deployment of PostgreSQL master-slave stream replication is complete.

Insert or delete data on the master server, and you can see the corresponding changes on the server. You can only query data on the server, but cannot insert or delete data.

Steps 24. 12th, 13, and 14 can be copied from the master database to the slave database by using the pg_basebackup command.

Pg_basebackup-F p -- progress-D/var/lib/pgsql/9.4/data2-h 192.168.0.136-p 5432-U replica -- password

Go to the/var/lib/pgsql/9.4/data2 directory and modify recovery. conf. This file can be obtained from the share folder of the pg installation directory, for example

Cp/usr/pgsql-9.6/share/recovery. conf. sample/var/lib/pgsql/9.6/data2/recovery. conf

Ensure that the folder permission is 700. This is critical. Other permissions cannot be started normally.

$ Chmod 0700/var/lib/pgsql/9.6/data2

Run the following command to start standby:

$/Usr/pgsql-9.6/bin/postmaster-D/var/lib/pgsql/9.6/data2

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151186.htm