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

I. brief introduction

Postgres introduced a master-slave stream replication mechanism after 9.0. the so-called stream replication is that the slave server synchronizes the corresponding data from the master server through the tcp stream. In this way, there is still a backup in the slave server when the master server data is lost.

Compared to file-based log shipping, stream replication allows you to keep updates from the server. The slave server connects to the master server, and the resulting stream WAL is recorded to the slave server without waiting for the master server to finish writing the WAL file.

PostgreSQL stream replication is asynchronous by default. There is a small delay between the commit of the transaction on the master server and the visible change on the slave server, which is much smaller than the file-based log delivery, which is usually completed in 1 second. If the primary server suddenly crashes, a small amount of data may be lost.

Synchronous replication must wait for both the master server and the slave server to write the WAL before committing the transaction. This increases the response time of the transaction to some extent.

Note: this experiment is based on docker.

II. Postgresql installs docker pull postgresql:9.4docker imagesREPOSITORY TAG IMAGE ID CREATED SIZEdocker.io/postgres 9.4 36726735dc3c 2 weeks ago 206MBdocker run-it-- name postgresql postgres:9.4 bashsu postgrescd / usr/lib/postgresql/9.4/bin

The / var/lib/postgresql/data directory exists to initialize the database

. / initdb-D / var/lib/postgresql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

Fixing permissions on existing directory / var/lib/postgresql/data... Ok

Creating subdirectories... Ok

Selecting default max_connections... 100

Selecting default shared_buffers... 128MB

Selecting default timezone... Etc/UTC

Selecting dynamic shared memory implementation... Posix

Creating configuration files... Ok

Creating template1 database in / var/lib/postgresql/data/base/1... Ok

Initializing pg_authid... Ok

Initializing dependencies... Ok

Creating system views... Ok

Loading system objects' descriptions... Ok

Creating collations... Ok

Creating conversions... Ok

Creating dictionaries... Ok

Setting privileges on built-in objects... Ok

Creating information schema... Ok

Loading PL/pgSQL server-side language... Ok

Vacuuming database template1... Ok

Copying template1 to template0... Ok

Copying template1 to postgres... Ok

Syncing data to disk... Ok

WARNING: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option-A, or--auth-local and-- auth-host, the next time you run initdb.Success. You can now start the database server using:. / postgres-D / var/lib/postgresql/dataor. / pg_ctl-D / var/lib/postgresql/data-l logfile start

At this point, the pg database is installed.

Open the database. If you don't need logs, you don't need to add logfile.

. / pg_ctl start-D / var/lib/postgresql/data

Also start a standby database

Docker run-it-- name postgresql2 postgres:9.4 bash

The steps are the same as above.

Third, the operation of the main server

The primary server is 172.18.0.4

First create a new directory to archive logs, I actually do not have archived logs here, as needed.

Mkdir / opt/pgsql/pg_archive

1. First, you need to create a database user for master-slave synchronization. Create the user replica and grant login and replication permissions.

Postgres# CREATE ROLE replica login replication encrypted password 'replica'

two。 Modify pg_hba.conf to allow replica users to synchronize.

Add two lines to the pg_hba.conf:

Host all all 172.18.0.5 host all all 32 trust # allows 0.5 to connect to the primary server

Host replication replica 172.18.0.5 host replication replica 32 md5 # allows 0.5 to be copied using the replica user

In this way, replica is set up as the user to make stream copy requests from 172.18.0.4.

* Note: the second field must be replication.

4. Modify postgresql.conf

Listen_addresses ='*'# listening on all IP

Archive_mode = on # allow archiving

Archive_command ='cp% p / opt/pgsql/pg_archive/%f' # use this command to archive logfile segment as needed.

Wal_level = hot_standby

Max_wal_senders = 32 # this setting allows up to several stream replication connections, almost a few from, just a few, a little larger setting is better.

Wal_keep_segments = 256set the maximum number of xlog retained by stream replication, with a segment of 16MB. Set a large value as far as possible to prevent the main database from generating logs too quickly and overwriting them before sending them to hot_standy.

Wal_sender_timeout = 60s # set the timeout for streaming replication hosts to send data

Max_connections = 100 # this setting should be noted that the max_connections of the slave library must be greater than that of the master

Restart the server after configuring two files.

Pg_ctl stop-D / var/lib/postgresql/data

Pg_ctl start-D / var/lib/postgresql/data

3. Test whether 0.5 can connect to 0.4 database. Run the following command on 0.5:

Psql-h 172.18.0.4-U postgres

See if you can access the database. If possible, it is normal.

Fourth, the operation from the server

1. Copy data from master node to slave node

Su-postgres

Rm-rf / opt/pgsql/data/* # empties all the data under the data directory first

Pg_basebackup-h 172.18.0.4-U replica-D / var/lib/postgresql/data-X stream-P # copy data from 0.4 to 0.5 (basic backup)

Mkdir / opt/pgsql/pg_archive

two。 Configure recovery.conf

Copy / usr/share/postgresql/9.4/recovery.conf.sample to / var/lib/postgresql/data/recovery.conf

Cp / usr/share/postgresql/9.4/recovery.conf.sample / var/lib/postgresql/data/recovery.conf

Modify recovery.conf

Standby_mode = on # indicates that the node is from the server

Primary_conninfo = 'host=172.18.0.4 port=5432 user=replica password=replica' # information about the primary server and the users connected

Recovery_target_timeline = 'latest'

3. Configure postgresql.conf

Wal_level = hot_standby

Max_connections = 1000 # generally, the maximum number of connections from the library is larger for applications that check more than write.

Hot_standby = on # indicates that this machine is not only used for data archiving, but also for data query

Max_standby_streaming_delay = 30s # maximum delay time for stream backup

Wal_receiver_status_interval = 10s # how often does the slave status be reported to the master? of course, the slave status will be reported to the master for each data replication. Here is only the longest interval.

Hot_standby_feedback = on # whether to give feedback to the master if there is an incorrect data replication

Restart the slave server after configuration

Pg_ctl stop-D / var/lib/postgresql/data

Pg_ctl start-D / var/lib/postgresql/data

5. Verify whether the deployment is successful

Execute in the primary node database:

Select client_addr,sync_state from pg_stat_replication

The results are as follows:

Postgres=# select client_addr,sync_state from pg_stat_replication; client_addr | sync_state-+-172.18.0.5 | async (1 row) postgres=#

Description 0.5 is from the server, in receiving the stream, and is replicated in the asynchronous flow.

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

On the primary server (0.4):

Ps aux | grep postgres

Root 210 0.0 0.0 48508 1548? S 06:34 0:00 su postgres

Postgres 211 0.0 0.1 19864 2256? S 06:34 0:00 bash

Postgres 250 0.0 0.9 273940 17632? S 06:41 0:00 / usr/lib/postgresql/9.4/bin/postgres-D / var/lib/postgresql/data

Postgres 252 0.0 0.2 274044 3800? Ss 06:41 0:00 postgres: checkpointer process

Postgres 253 0.0 0.1 274072 3216? Ss 06:41 0:00 postgres: writer process

Postgres 254 0.0 0.3 273940 6108? Ss 06:41 0:00 postgres: wal writer process

Postgres 255 0.0 0.1 274348 2656? Ss 06:41 0:00 postgres: autovacuum launcher process

Postgres 256 0.0 0.0 129220 1836? Ss 06:41 0:00 postgres: stats collector process

Postgres 276 0.0 0.1 274480 3164? Ss 06:57 0:00 postgres: wal sender process replica 172.18.0.5 (42834) streaming 0/3019C90

Postgres 391 0.0 0.0 38296 1752? R + 07:36 0:00 ps aux

Postgres 392 0.0 0.0 12772 692? S + 07:36 0:00 grep postgres

You can see that there is a wal sender process.

From the server (94):

Ps aux | grep postgres

Root 394 0.0 0.0 48508 1548? S 06:42 0:00 su postgres

Postgres 395 0.0 0.1 19884 2320? S 06:42 0:00 bash

Postgres 488 0.0 2.3 314268 45052? S 06:57 0:00 / usr/lib/postgresql/9.4/bin/postgres-D / var/lib/postgresql/data

Postgres 489 0.0 0.2 314452 4904? Ss 06:57 0:00 postgres: startup process recovering 000000010000000000000003

Postgres 490 0.0 0.1 314388 3524? Ss 06:57 0:00 postgres: checkpointer process

Postgres 491 0.0 0.1 314268 2956? Ss 06:57 0:00 postgres: writer process

Postgres 492 0.0 0.0 129220 1848? Ss 06:57 0:00 postgres: stats collector process

Postgres 493 0.0 0.2 319036 4384? Ss 06:57 0:01 postgres: wal receiver process streaming 0/3019C90

Postgres 508 0.0 0.0 38296 1756? R + 07:37 0:00 ps aux

Postgres 509 0.0 0.0 12772 700? S + 07:37 0:00 grep postgres

You can see that there is a wal receiver process.

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. It can only be queried from the server, not inserted or deleted.

Lord:

Postgres=#\ c test

You are now connected to database "test" as user "postgres".

Test=# create table company (

Test (# id int primary KEY NOT NULL

Test (# name TEXT NOT NULL

Test (# age INT NOT NULL

Test (# address CHAR (50)

Test (# salary REAL

Test (# join_date DATE

Test (#)

CREATE TABLE

Test=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32,' California', 2000)

INSERT 0 1

Test=#

Test=#

Test=# select * from company

Id | name | age | address | salary | join_date

1 | Paul | 32 | California | 20000 | 2001-07-13

(1 row)

From the top:

Postgres=#\ l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges-+- -postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | = c/postgres + | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | = c/postgres + | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.utf8 | en_US.utf8 | (4 rows) postgres=#\ c testYou are now connected to database "test" as user "postgres" .test = # select * from companytest-# | Id | name | age | address | salary | join_date-+-+-- -1 | Paul | 32 | California | 20000 | 2001-07-13 (1 row) s

Done!

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.