Shulou(Shulou.com)06/01 Report--

Due to the needs of work, recently began to contact a variety of databases, and try a variety of database products with high availability solutions.

What we share today is the master-slave configuration of postgresSQL, which is actually quite simple. Follow the steps in this article to ensure that the master-slave configuration of PG can be realized.

1. Installation environment

192.168.0.136 master library 192.168.0.160 slave library PORT: 5432USR: postgres

two。 The master database has been running for some time. Check the version of the master database to make sure that the version of the master database and slave database is the same.

# psql-- versionpsql (PostgreSQL) 9.4.1 percent rpm-qa | grep postgrespostgresql94-libs-9.4.11-1PGDG.rhel6.x86_64postgresql94-server-9.4.11-1PGDG.rhel6.x86postgresql94-9.4.11-1PGDG.rhel6.x86_64

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

Check the installation and have installed the same software version as primary

# rpm-qa | grep postgrespostgresql94-libs-9.4.11-1PGDG.rhel6.x86_64postgresql94-server-9.4.11-1PGDG.rhel6.x86postgresql94-9.4.11-1PGDG.rhel6.x86_64

4. Query the database location of the main database

# su-postgres$ echo $PGDATA/var/lib/pgsql/9.4/data$ cd / var/lib/pgsql/9.4/data$ lsbase pg_clog pg_hba.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 profile location

Postgres=# 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 main library, first query the existing parameters of pg_hba.conf.

$cat pg_hba.conf | grep-v'^ # 'local all all peerhost all all 0.0.0.0 ident 0 trusthost all all:: 1Comp128 ident

6. Add to the pg_hba.conf of the main library

$more pg_hba.confhost replication replica 192.168.0.160 md5

In this way, replica is set up as a user to make stream replication requests from the corresponding network segment of 192.168.0.160.

7. Set password, login and backup permissions for postgres in the main library.

$psqlpostgres# CREATE ROLE replica login replication encrypted password 'replica123'

8. Modify postgresql.conf and pay attention to the following settings:

Wal_level = hot_standby # this is to set the host max_wal_senders for wal = 10 # this setting allows for up to several stream replication connections, almost several from the Set several wal_keep_segments = 256# set the maximum number of xlog retained by stream replication wal_sender_timeout = 60s # set the timeout max_connections for sending data by the stream replication host max_connections = 100. note that the max_connections of the slave library must be greater than the archive_mode of the master database = onarchive_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 main library to make the configuration take effect

# service postgresql-9.4 startStarting postgresql-9.6 service: [OK]

11. Remotely connect to the master database from the slave library to verify that replica users can access it properly.

Psql-h IP-address-p 5432 dbname usename psql-h 192.168.0.136-p 5432 postgres replica

twelve。 Then make a basic backup in the main database (the following Hot-standby mainly uses the files under 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/datapostgres=# SELECT pg_stop_backup ()

13. Sftp the backup file to the slave library, extract it, and replace the original data directory

Cd / var/lib/pgsql/9.4/mv data data_bkmv backup_data.tar.gz.20170905 backup_data.tar.gztar-xzvf backup_data.tar.gz

14. Delete some identity information, archive log files, etc.

Rm-rf data/pg_xlog/mkdir-p data/pg_xlog/archive_statusrm 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. Then edit recovery.conf:

Standby_mode = onrestore_command ='cp / var/lib/pgsql/9.4/archive/%f% p'# parameter, I also need to confirm the specific meaning of primary_conninfo = 'host=192.168.0.136 port=5432 user=replica password=replica123' # primary 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.confcp / 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

Executed on the master node, the verification has been successfully built, indicating that 5.160 is a slave server, receiving a stream, and an asynchronous flow replication.

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

21. For 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,sync_priority,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-+- -+- +-+-replica | walreceiver | 192.168.0.160 | 39375 | 2017-09-05 1749 streaming 22.5123931408 | | streaming | 5thumb 1049488 | 5pm 1049488 | 5mp 1049488 | 5mp 1049488 | 0 | async

twenty-two。 In addition, you can run ps aux | grep postgres on the master node and slave node respectively to view the process:

On the main server, you can see a wal sender process, an archiver process, and so on.

# ps aux | grep postgrespostgres 1104 0.0 0.1 324652 15120? S 14:26 0:00 / usr/pgsql-9.4/bin/postmaster-D / var/lib/pgsql/9.4/data postgres 1111 0.0 324652 5844? Ss 14:26 0:00 postgres: wal writer process postgres 1113 0.0 0.0 179796 1544? Ss 14:26 0:00 postgres: archiver process last was 000000010000000500000000.00000060.backuppostgres 8515 0.0 0.0 325448 3108? Ss 17:49 0:00 postgres: wal sender process replica 192.168.0.160 (39375) streaming 5/103A1D0

From the server, you can see that the wal receiver process and the recovering process are restoring archive log

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

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

# pwd/var/lib/pgsql/9.4/data/pg_xlog# ls000000010000000500000001 000000010000000500000002 archive_status

At this point, the PostgreSQL master-slave stream replication installation and deployment is complete.

Insert or delete data on the master server, and the corresponding changes can be seen on the slave server. You can only query from the server, not insert or delete data.

24. Step 12, 13, 14, another way to copy the master library to the slave library is the pg_basebackup command to copy the file.

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 the recovery.conf. This file can be obtained from the share folder of the pg installation directory, such as

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

Make sure that the folder permission is 700, this is critical, other permissions, can not start normally

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

Start standby using the following command

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.