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

I. Overview

PostgreSQl has introduced log shipping similar to Oracle's active dataguard and MySql relay logs since version 9.0. With the help of this function, we can realize the master-slave replication of PostgreSql.

The basic principle is that usually one master database provides read and write, and then synchronizes the data to another slave library. The slave library continuously apply the data received from the master database. The slave library does not provide write services, but only external read services. The server that provides full read and write function in postgresql is called primary database or master database, and the slave server that can provide read service while receiving synchronous data from the master library is called hot standby server.

PostgreSQL maintains a WAL log file in the pg_ xlog subdirectory under the data directory, which is used to record every change of the database file. This log file mechanism provides a solution for database hot backup, that is, when the database is backed up using the file system, the corresponding WAL log is also backed up, even if the data blocks are inconsistent. You can also replay the WAL log to push the contents of the backup to a consistent state. This is called point-in-time backup (Point-in-Time Recovery), or PITR for short. There are two ways to send WAL logs to another server, which are:

1. WAL Log Archive (base-file)

two。 Stream replication (streaming replication)

The first is to copy the WAL log file to the standby database after writing a WAL log, which in short is to achieve remote backup through the cp command, so that the standby database usually lags behind the main database by one WAL log file. The second kind of stream replication is a new method of transferring WAL logs provided after postgresql9.x. Its advantage is that as soon as the master library generates logs, it will be immediately passed to the standby library, with a lower synchronization delay than the first, so we will certainly choose the way of stream replication.

One more thing to note before the actual operation is that the most critical step in the construction of standby is to generate a basic backup of master in standby. Postgresql9.1 provides a very convenient tool-pg_basebackup. The detailed description and parameter description of it can be found on the official website (pg_basebackup tool). The following details will be given during the construction process.

II. Introduction of basic environment

System platform: CentOS release 6.6 (Final)

Postgresql:postgresql-9.6.6

SELINUX=disabled

Iptables shuts down

Main library (master) IP:192.168.221.161

IP:192.168.221.160 from the standby

For basic environment construction, please refer to the previous article (installation and configuration of Postgresql9.6.6 under Centos6.6), that is, the basic installation and configuration of PostgreSql.

III. Configuration of the main library

1. Add a synchronized user name and password to the main library

[postgres@MidApp] $psql psql (9.6.6) Type "help" for help. Postgres=# CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123456 creating ROLEpostgres=#

two。 Modify / home/postgres/pgsql/data/pg_hba.conf, add on the last line

[root@MidApp tmp] # tail-6 / home/postgres/pgsql/data/pg_hba.conf # Allow replication connections from localhost, by a user with the# replication privilege.#local replication postgres trust#host replication postgres 127.0.0.1 by a user with the# replication privilege.#local replication postgres trust#host replication postgres 32 trust#host replication postgres:: 1 trustHost replication repluser 128 trustHost replication repluser 192.168.221.160lap 32 md5

This line configuration means that user repluser is allowed to initiate a stream replication connection to this database in the form of md5 encryption from the host 192.168.221.160.

3. Configure the following parameters under the main configuration file

Listen_address ='*'(default localhost) wal_level = hot_standby (default is minimal) max_wal_senders=5 (default is 0) wal_keep_segments=64 (default is 0) synchronous_standby_names = 'standby01'

The first parameter listens to all IP;, the second parameter starts hot standby;, and the third parameter indicates how many concurrent standby databases the main library can have, which is set to 5. The fourth parameter represents a WAL log file size, which defaults to 16m.

The fifth parameter specifies the Standby name of the synchronous replication (there is a place to be defined from the recovery.conf of the library, but this parameter can not be set)

4. Restart the main library for the configuration to take effect.

If there is an error in startup, you can go to the log to check.

IV. Slave library configuration

First of all, it is necessary to ensure that the environment before the synchronization between the master library and the slave library is consistent, so that it is convenient to do synchronization. Because I had configured the PG database on the slave machine before, I took a lot of detours at first. Finally, clear out PG's home directory and do it all over again.

1. Generate a basic backup from the slave library through the pg_basebackup command line tool. The command is as follows. 100% indicates that the backup is successful.

[root@DB tmp] # pg_basebackup-h 192.168.221.161-F p-P-D / home/postgres/pgsql/data-p5432-U repluser-- passwordPassword: 22802 kB, 1 tablespaceNOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to comple

Parameter description:-h specifies the database IP of the connection

-F specifies the format of the output, supporting p (plain as-is output) or t (tar format output)

-P print backup progress in real time during backup

-D specifies the directory of the backup

-U specifies the user name of the connection

-p specify the port to connect to

-- password specifies the password of the user to connect

Description of other parameters:

-R automatically generates recovery.conf files after backup, and I know this parameter only after the fact.

-l specify the identity of a backup

You can use pg_basebackup-- help to view the specific parameters, or you can check the official website introduction https://www.postgresql.org/docs/current/static/app-pgbasebackup.html.

Special note: I do not add the-R parameter here, so I have to copy the recovery.conf manually.

Cp / home/postgres/pgsql/share/recovery.conf.sample / home/postgres/pgsql/data/recovery.conf

Add the following information:

Standby_mode = onprimary_conninfo = 'application_name=standby01 user=repluser password=123456 host=192.168.221.161 port=5432 sslmode=disable sslcompression=1'

two。 Modify the master configuration file / home/postgres/pgsql/data/postgresql.conf of the slave library

Hot_standby = on

Change hot_standby to enabled

3. Next, you can start the slave library.

[root@DB tmp] # / etc/init.d/postgresql startStarting PostgreSQL:-bash: / home/postgres/pgsql/data/serverlog: Permission deniedok

The first time to start the error report, this is because the above command to generate backup was executed by the root user, which caused the property of the PG home directory to become root, so I have to reset the permissions.

Chown-R postgres:postgres / home/postgres/pgsql/*

Start again normally, check the process is also OK

[root@DB tmp] # / etc/init.d/postgresql startStarting PostgreSQL: ok [root@DB tmp] # ps-ef | grep postgroot 52577 82731 0 15:12 pts/0 00:00:00 su-postgrespostgres 52578 52577 0 15:12 pts/0 00:00:00-bashpostgres 74295 10 20:01? 00:00:00 / home/postgres/pgsql/bin/postmaster-D / home/postgres/pgsql/datapostgres 74296 74295 0 20:01? 00 : 00:00 postgres: startup process recovering 000000010000000000000003 postgres 74297 74295 5 20:01? 00:00:00 postgres: wal receiver process streaming 0/3000140 postgres 74298 74295 0 20:01? 00:00:00 postgres: checkpointer process postgres 74299 74295 0 20:01? 00:00:00 postgres: writer process Postgres 74300 74295 0 20:01? 00:00:00 postgres: stats collector process

5. Verification of results

1. Query through select usename,application_name,client_addr,state from pg_stat_replication in the main library:

[postgres@MidApp] $psql psql (9.6.6) Type "help" for help. Postgres=# select usename,application_name,client_addr,state from pg_stat_replication; usename | application_name | client_addr | state-+-repluser | standby01 | 192.168.221.160 | streaming (1 row) postgres=#

You can see that repluser on 192.168.221.160 synchronizes the data of the main database through stream replication

two。 Create a table to verify

Create a table on the main library and insert data validation

Postgres=# create table test01 (id int primary key,note text); CREATE TABLEpostgres=#\ d List of relations Schema | Name | Type | Owner-+-public | test01 | table | postgres (1 row) postgres=# insert into test01 values (1 lead 111111'); INSERT 0 1postgres=# select * from test01; id | note-+-1 | 1111111 (1 row)

View on the from library:

[postgres@DB data] $psql psql (9.6.6) Type "help" for help. Postgres=#\ d List of relations Schema | Name | Type | Owner-+-public | test01 | table | postgres (1 row) postgres=# select * from test01; id | note-+-1 | 1111111 (1 row)

Try inserting data and take a look:

Postgres=# insert into test01 values (2 cannot execute INSERT in a read-only transaction 22222222'); ERROR:

As you can see, the data synchronized from the main library can be viewed from the library, but the data cannot be written.

VI. Summary

The above is the whole process of setting up the master-slave synchronization of Postgresql. I stepped on many holes all the way and recorded it, hoping to help others.

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.