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

This article will explain in detail how PostgreSQL uses repmgr to achieve high availability. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1 big factory support

2 configuration is simple and reliable, without many dependent packages installed, there are still problems that make you want to commit suicide, or the urine point where it is difficult to solve the problem because of the lack of related technical text.

This high availability scheme has been in use in production for some time, and no problems have been found before, but some problems have also been found in the use of this period of time, so we are prepared to talk about this high availability scheme in detail, and avoid some provocative jokes that PG is not reliable and highly available.

This highly available method is the product of repmgr, a 2-quadrant company. (free), the version of PG in the following text is 11.2, and REPMGR is version 4.4. Due to more functions, so can not be finished at one time, can only be written in stages, today's text to do is two POSTGRESQL, complete manual switching.

First of all, you need to install 2 postgresql. It is assumed that you have already installed it. (of course, the installation is compiled and installed. If it is not compiled and installed, I do not guarantee that there will be no other problems. There was a previous article about compilation and installation. Of course, you can also go to Dege's github to find professional words about POSTGRESQL installation, which is not shallow).

1 the installed POSTGRESQL can first have the conditions for replication

2 two postgresql should be configured the same, including configuration files, extension and so on

Here in order to facilitate the following understanding of the two machines

192.168.198.21 main library

192.168.198.22 prepare the library

In fact, we can configure a machine, we are in a machine (mainframe)

Operate the following commands

Create database repmgr

Create user repmgr with password 'repmgr'; (feel free to use your password)

Alter user repmgr superuser login

Alter database repmgr owner to repmgr

Compile the unpacking of repmgr

After confirming that repmgr has been compiled, you need to perform ssh secret-free work on both machines.

The privacy advice here is based on the fact that you manipulate your postgresql account, not root

(note: secret-free work such as MYSQL's DBA would not be difficult to understand, because that's how MHA does it.)

In addition, you need to configure the communication account for the high availability of repmgr, and you also need to perform secret-free work. You need to set .pgpass in the directory location of the account where you operate postgresql, as shown in the following figure.

In fact, if you have configured mha, it is easy to understand such things)

In addition, you also need to configure pg_hba.conf. The configuration method is shown in the following figure (you may have questions here if you understand it, but you do need to set it up here, the documentation on the official website).

Https://repmgr.org/docs/4.4/quickstart-authentication.html

After all this, we need to configure the repmgr.conf file (in fact, this is similar to the way MHA is configured, so if you are a MYSQL DBA, the cost of learning PG's high-availability mode will be very low.)

The logo in the node_id=1 cluster. Note that there can be no duplicates in this cluster. Generally, numbers are used.

Node_name='192.168.198.21' needs to give the node a registered name. Here you can use IP, machine name and so on.

Conninfo='host=192.168.198.21 dbname=repmgr user=repmgr connect_timeout=2'

Local connection information, connection information when repmgr operates locally

Data_directory='/pgdata/data' specifies the data directory of the current host

The account of the copy operation performed by replication_user='repmgr'

The way replication_type='physical' is copied

Directory of executable files for repmgr_bindir='/usr/local/postgres/bin' repmgr

Pg_bindir='/usr/local/postgres' configure the executable file of PG

Another machine needs to be changed in node_id, node_name, conninfo, etc.

So far, let's summarize the status of the two machines at present.

The host, has already registered repmgr, the server is open, and can accept the secret-free way of remote connection of repmgr. The standby library shuts down, and the data directory of the standby database is empty (because the operation of the main database pulling data is to be started).

Next we are going to use the following command to do-- dry-run (is mysql's DBA a surprise and the same as the pt tool)

Repmgr-h 192.168.198.21-U repmgr-d repmgr-f / etc/repmgr.conf standby clone-- dry-run

As you can see-- there is no problem with dry-run. Directly execute the command to clone.

Repmgr-h 192.168.198.21-U repmgr-d repmgr-f / etc/repmgr.conf standby clone

Then, after the clone is successful, it is actually pg_basebackup. After that, you need to change the listen address in the postgresql,conf file in the standby machine to the local address.

(in fact, these jobs also do the work of primary standby, which has nothing to do with high availability itself. Knowledge repmgr helps you do this.)

Start the server, normal, and start replication

If there is a problem here, the possible reason

1 there is a problem with the pg_hba.conf setting

2 postgresql.conf Slave did not change the postgresql,conf listening address

(please add basic knowledge of POSTGRESQL)

The next step is to verify the replication (skip this step if you are confident)

From the library, we check whether the current replication is OK. The following figure shows OK.

And then execute from the library

Repmgr-f / etc/repmgr.conf standby register

After successful registration

At present, most of the high availability (manual switching) work has been completed by 80%.

Repmgr-f / etc/repmgr.conf cluster show

Through the above figure and command, you can see the master-slave status of the two machines.

Write below, someone may complain about me, others are automatic, you manual, you are out of your mind.

1 POSTGRESQL repmgr master-slave switch, can be automatic, but this issue can not be finished

2 if the use of mysql is relatively smooth, you can immediately reflect a problem here, MHA I switch I did not use MHA to detect, I also use other ways to detect, and then use the MHA command switch mode for highly available switching.

At this point, it's clear that if you have MYSQL's high-availability MHA solution, you already have an idea, and the reason you need to read on is how to switch manually. And then you can let yourself go.

If you want to say that there is no reliable way to use POSTGRESQL, don't hit it in the face.

Let's start switching manually.

Repmgr-f / etc/repmgr.conf standby switchover-U repmgr-- verbose

All right, after the switch command starts, the master-slave switch starts, as shown in the following figure

After switching, we check the status on the host

The host has become a slave library, and the slave library has been upgraded to a master library

Looking at it from the library, it is clear to you that the main library is 22, from the library to 21.

This is also different from MHA, if your failed master library still has room to save, you can still let it become from the library, continue to serve, of course, there is a time limit, default to try for one minute, otherwise give up.

This is the end of this article on "how PostgreSQL uses repmgr to achieve high availability". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.