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

Today, I will talk to you about the principle of streaming replication in PostgreSQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Unlike MYSQL, MYSQL replication is based on logical replication, while PG has two functions: stream replication and logical replication. When it comes to streaming replication, if you let me understand that it is a function like ORACLE Dataguard, of course, ORALCE also has streaming replication and advanced replication and other functions, so I won't mention it here.

There are many technologies copied here, such as shared disk failover, write-Ahead log shipping, logincal replication, and so on. What we are going to learn today is

Write-ahead log shipping, this function.

Then what good is it for us to use this function?

1 it is a built-in function

2 the way to connect is through wal ahead log

3 there are no special hardware requirements

4 basically has no effect on the operation of the host

5 you can set the synchronization and asynchronism of data

6 the Standy library is a read-only activity

After reading the above points, it is found that replication by stream is actually better than logical replication, that is, 1 there is no transaction conflict, 2 there will be no transaction size, and 3 there will be no worry about data loss during the switching process.

These are all the issues that I may be worried about in the process of copying MYSQL.

Stream replication is a feature introduced by PostgreSQL in 9.0, which is mainly composed of three processes

1 walsender

2 walreceiver

3 startup

How do these three work? let's draw a picture to analyze it.

1 Standby library attempts to connect to the main library through walreciever

2 the Primary main library will connect tcp-IP through wal sender, and shake hands to confirm the connection

3 the standby of the slave database will send his last LSN number to the master database, and the LSN number of the slave library must be smaller than that of the master database (if it is reversed, it means that the data of the master database lags behind the slave database, or other problems. There is no way to copy it.)

4 the main library will provide WAL Segments and start the work of replicating data transmission.

Pass through

Select application_name,state from pg_stat_replication; the following statement to see if the current library is in the state of sender.

Speaking of which, there is a problem with every kind of database replication, that is, if the STANDBY library restarts or STANDBY fails to keep up with the speed at which the PRIMARY library sends logs, the result is replication failure. And PostgreSQL has two ways to try to save something like this.

1 improving wal_keep_segments reminds me of MONGODB increasing the storage size of OPLOG so that MONGODB replication does not cause similar problems.

(2) use the way and function of replication slot to send WAL data flexibly.

In fact, the vernacular is either I do something on the sending side, or I do something on the receiving side.

At the same time, we can use sent_lsn, write_lsn,flush_lsn, replay_lsn, to determine the status of our replication.

In the replication of 3 PG, it can be a multi-slave structure.

Having said that, we need to explore the parameters in configuration flow replication.

First, you need to turn on POSTGRESQL replication.

1 wal_level = replica

Determine how much information is written to the WAL. If you want to adjust this parameter, you need to set it before enabling the service.

If set to minimal level, the logs of commands like create table as create index, copy and so on will not be recorded.

2 synchronous_commit related options

On, remote_apply, remote_write, local

The default safety value is ON, and if you choose remote_apply or remote_write, you will wait for the host of standby to give back before the main library can continue.

3 max_wal_sender sets the maximum number of connections for primary standby. Default is 10.

4 wal_keep_segments here specifies the maximum number of wal logs that can be kept, and the number of logs in a WAL is 16MB.

5 the default time for wal_sender_timeout to detect no response in standby is 60 seconds.

6 synchronous_standby_name = 'standby_name one by one'

One of the functions in the PG setting is to set up a standby to stream copy with the host of PG, and when the standby stops working, the host stops working. In the above parameter options, you need to fill in the specific standby machine needs, such a relationship with the main library.

At the same time, you need to set it in the standby library

It needs to be set in the recovery.conf of the slave library

7 the following settings are made under standby

Hot_standby = on statements that allow queries to be made in the state of recovery

Otherwise, after configuring the relevant parameters, you need to use pg_basebackup to pull the data from the master database to the slave database. After configuring the parameters, start the slave database, then the PG stream replication similar to ORACLE dataguard will take effect.

After reading the above, do you have any further understanding of the principle of stream replication in PostgreSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.