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

In this article Xiaobian for you to introduce in detail "what is the method of PostgreSQL streaming replication", the content is detailed, the steps are clear, the details are handled properly, I hope this article "what is the method of PostgreSQL streaming replication" can help you solve your doubts, the following follows the editor's ideas slowly in depth, together to learn new knowledge.

Master-slave term master / master server

A server that can be written.

Also known as a read / write server.

Slave / standby server

A server where data is continuously synchronized with the primary server.

Also known as a backup server or replica.

A warm standby server is a server that cannot be connected until it is promoted to the primary server.

By contrast, hot standby servers can accept connections and provide read-only queries. In the next discussion, we will only focus on hot standby servers.

The data is written to the master server and propagated to the slave server. If there is a problem with the existing master server, one of the slave servers will take over and continue to write to ensure the availability of the system.

What is WAL for WAL transport-based replication?

WAL stands for Write-Ahead Logging.

It is a log file to which all changes to the database are written before the data file is applied / written.

WAL is used for recovery after a database crash to ensure data integrity.

WAL is used in database systems to achieve atomicity and persistence.

How is WAL used for replication?

Pre-written logging is used to maintain data synchronization between database servers. This is done in two ways:

File-based log delivery

WAL log files are transferred from the primary server to the standby server to keep the data synchronized.

Master can copy logs directly to the slave server storage or share storage with the slave server.

A WAL log file can contain up to 16MB data.

WAL files are sent only when this threshold is reached.

This causes replication delays and increases the chance of data loss if the primary server crashes and the logs are not archived.

Streaming WAL record

WAL record blocks are streamed by the database server to keep the data synchronized.

The standby server connects to the primary server to receive WAL blocks.

WAL records are streamed when they are generated.

Streaming of WAL records does not need to wait for the WAL file to be populated.

Compared to file-based log shipping, this allows the standby server to keep up to date.

By default, streaming replication is asynchronous, even though it supports synchronous replication.

These two methods have their own advantages and disadvantages. File-based delivery enables point-in-time recovery and continuous archiving, while streaming ensures that data on the standby server is immediately available. However, you can configure PostgreSQL to use both methods and enjoy the benefits. In this article, we focus on stream replication to achieve PostgreSQL high availability.

How to set up streaming replication

Setting up streaming replication in PostgreSQL is very simple. Assuming that PostgreSQL is already installed on all servers, you can start by following these steps:

Configuration on the primary node

Use the initdb utility to initialize the database on the primary node.

Create a role / user with replication permissions by running the following command. After running the command, you can verify it by running\ du to list them on psql. CREATE USER REPLICATION LOGIN ENCRYPTED PASSWORD''

Configure attributes related to streaming replication in the main PostgreSQL configuration (postgresql.conf) file:

# Possible values are replica | minimal | logicalwal_level = replica# required for pg_rewind capability when standby goes out of sync with masterwal_log_hints = on# sets the maximum number of concurrent connections from the standby servers.max_wal_senders = "The below parameter is used to tell the master to keep the minimum number of# segments of WAL logs so that they are not deleted before standby consumes them.# each segment is 16MBwal_keep_segments =" The below parameter enables read only connection on the node when it is in# standby role. This is ignored when the server is running as master.hot_standby = on

Add replication entries to the pg_hba.conf file to allow replication connections between servers:

# Allow replication connections from localhost,# by a user with the replication privilege.# TYPE DATABASE USER ADDRESS METHODhost replication repl_user IPaddress (CIDR) md5

Restart the PostgreSQL service on the primary node for the changes to take effect.

Configuration on standby nod

Use the pg_basebackup utility to create a basic backup of the primary node and use it as the starting point for the standby node.

# Explaining a few options used for pg_basebackup utility#-X option is used to include the required transaction log files (WAL files) in the# backup. When you specify stream, this will open a second connection to the server# and start streaming the transaction log at the same time as running the backup.#-c is the checkpoint option. Setting it to fast will force the checkpoint to be# created soon.#-W forces pg_basebackup to prompt for a password before connecting# to a database.pg_basebackup-D-h-X stream-c fast-U repl_user-W

If it does not exist, create a replication profile (automatically if the-R option is provided in pg_basebackup):

# Specifies whether to start the server as a standby. In streaming replication,# this parameter must be set to on.standby_mode = 'on'# Specifies a connection string which is used for the standby server to connect# with the primary/master.primary_conninfo =' host= port= user= password= application_name= "host_name"'# Specifies recovering to a particular timeline. The default is to recover along the# same timeline that was current when the base backup was taken.# Setting this to latest recovers to the latest timeline found# in the archive, which is useful in a standby server.recovery_target_timeline = 'latest'

Activate standby.

The standby configuration must be done on all standby servers. When the configuration is complete and the standby server is started, it connects to the primary server and starts streaming logs. This sets up replication and can verify SELECT * FROM pg_stat_replication; by running the SQL statement.

By default, streaming replication is asynchronous. If you want to synchronize it, you can configure it with the following parameters:

# num_sync is the number of synchronous standbys from which transactions# need to wait for replies.# standby_name is same as application_name value in recovery.conf# If all standby servers have to be considered for synchronous then set value'*'# If only specific standby servers needs to be considered, then specify them as# comma-separated list of standby_name.# The name of a standby server for this purpose is the application_name setting of the# standby, as set in the primary_conninfo of the# standby's WAL receiver.synchronous_standby_names = 'num_sync (standby_name [ .])'

Synchronous_commit must be set for synchronous replication, which is the default setting. PostgreSQL provides very flexible options for synchronous commit and can be configured at the user / database level. Valid values are as follows:

Off-even before the transaction record is actually flushed to the WAL log file on that node, the transaction commit is confirmed to the client.

Local-the transaction commit is confirmed to the client only after the transaction record is flushed to the WAL log file on that node.

Remote_write-confirms the transaction commit to the client only after the specified server synchronous_standby_names confirms that the transaction record has been written to the disk cache, but not necessarily after flushing to the WAL log file.

On-the transaction commit is confirmed to the client only after the specified server synchronous_standby_names confirms that the transaction record is flushed to the WAL log file.

Remote_apply-the transaction commit is confirmed to the client only after the specified server synchronous_standby_names confirms that the transaction record has been flushed to the WAL log file and applied to the database.

Setting synchronous_commit to off or local in synchronous replication mode makes it work asynchronously and can help you achieve better write performance. However, this increases the risk of data loss and read latency on the standby server. If set to remote_apply, it ensures that the data on the standby server is immediately available, but write performance may be degraded because it should be applied to all / mentioned standby servers.

If you plan to use continuous archiving and point-in-time recovery, you can enable archiving mode. Although streaming replication is not mandatory, enabling archiving mode has an additional benefit. If archive mode is not turned on, then we need to use the replication slot feature or make sure that the wal_keep_ segments value is set high enough based on the load.

After reading this, the article "what is the method of PostgreSQL streaming replication" has been introduced. If you want to master the knowledge of this article, you still need to practice and use it yourself to understand it. If you want to know more about related articles, you are welcome to follow the industry information channel.

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.