PostgreSQL feature highlight: the rapid transformation of the old master in the stand-by with pg_rewind
Anyone who had to deal with the failover procedure when working with streaming replication in PostgreSQL, was probably puzzled by the question: “How can I not copy anything again, but get the old master to work as a stand-by?” So unfortunately, the built-in functionality in PostgreSQL, alas, no. Yes, it is impossible so easy to take and connect the old master to the new and to make it work. To do this, repeat the process of setting up streaming replication again, i.e. copy the entire cluster and run postgres in hot-standby mode.
Fortunately, work in this direction is underway, and the results are not bad enough. The project is called pg_rewind .
Immediately I warn you this is not yet production-ready and the post has the character of how-to + technology preview.
Known limitations:
A little more theory and let's move on to practice ... what is the rewinding process:
So now the practice, below I will describe the process of how to "rewind" the old master and connect to the new master without full reinitialization.
The plan is simple:
All operations are performed in CentOS 6.5. In general, the process is quite simple, I had the most difficulty at the pg_rewind assembly stage, so I will describe the process completely.
So let's go! First you need to install several packages that will be required as dependencies when building postgresql. After installation, we clone the repositories and install PostgreSQL.
Here it is worth noting that by default PostgreSQL is installed in / usr / local and all utilities are in / usr / local / pgsql / bin, therefore this path must be added to the PATH environment variable. This can be done in different ways, I did the following, more or less decent (and maybe not very) way for CentOS:
Now go to the directory with the pg_rewind code and build the utility. The resulting utility is simply copied to the postgres utilities.
Now we set up stream replication. As I wrote at the beginning, for pg_rewind to work successfully, one of two conditions must be met:
1) Clusters must be initialized with checksum support (initdb -k).
or
2) postgres services must be started with the wal_log_hints = on option in postgresql.conf
I chose the second option.
The configuration process has already been described many times , so I will not dwell on this here.
So when replication is up and running, we are failover. This operation is performed by creating a trigger file specified in recovery.conf in the trigger_file option. After creating the trigger file, the stand-by will switch to master mode and start living its life. Therefore, the old master now needs to be turned off.
And now pg_rewind comes into play. The parameters are the directory where the cluster of the old master ( -D ) is located and the options for connecting to the new master server ( --source-server ). Attention, the operation needs to be performed under the user who owns the database cluster (by default, postgres).
So rewind done (do not be afraid of the word error, this is a regular test). Now you need to pay attention to the postrgesql.conf and recovery.done files, remember that they are copied from the new wizard and may contain server-specific settings (for example, ip-addresses or port numbers in listen_addresses, port, etc.) The second file, recovery. done should be renamed to recovery.conf and configured to connect to the new master.
After the configuration is corrected we launch postgres and look at the server log
Pay attention to the database system is ready to accept read only connections , this is the most important line here indicating that everything went smoothly and postgres started as expected and ready to accept connections. You can check streaming replication with standard tools via pg_stat_replication or create test tables on the wizard and check their availability on the stand-by.
That's all. At the end I will add ... First, in the process between the proclamation of a new master and rewind, the new master is available for recording, so when rewinding you do not need to isolate the master from connections from the application side. Second, the project is still young, is in active development, and in my opinion is not ready for production. So try, test ...
Those who are not ready to do this all or simply lazy, but I want to see, there is a small video for 8 minutes, the switching action starts at 4:20 (who is unfamiliar with postgres will not understand anything).
')
Thank you all for your attention.
Fortunately, work in this direction is underway, and the results are not bad enough. The project is called pg_rewind .
Immediately I warn you this is not yet production-ready and the post has the character of how-to + technology preview.
Known limitations:
- Currently, pg_rewind only supports version 9.4 and part 9.3. Why partly because the cluster must be necessarily initialized with support for checksums ( initdb -k ). With version 9.4 simpler, it needs either support for checksums, or the presence of the parameter wal_log_hints set to on.
- Another significant limitation, the master must be turned off normally. That is, if the master was turned off abnormally, then the rewind will not be performed. But, developers are working in this direction.
- While tablespaces are not supported and work with WAL archives is not implemented (the missing archives should be copied manually).
A little more theory and let's move on to practice ... what is the rewinding process:
- Scanning the directory of the old node since the WAL branch and recording the blocks that were changed after the branch.
- Copying the changed blocks from the new master to the old one;
- Copying the remaining files (clog, configuration files, etc.);
- Editing recovery.conf and launching the old wizard with new settings;
- WAL playback from checkpoint after failover.
So now the practice, below I will describe the process of how to "rewind" the old master and connect to the new master without full reinitialization.
The plan is simple:
- build and install PostgreSQL 9.4 (currently only devel);
- build pg_rewind;
- configure streaming replication;
- failover;
- rewind the old master and connect to the new master.
All operations are performed in CentOS 6.5. In general, the process is quite simple, I had the most difficulty at the pg_rewind assembly stage, so I will describe the process completely.
So let's go! First you need to install several packages that will be required as dependencies when building postgresql. After installation, we clone the repositories and install PostgreSQL.
# yum install git gcc zlib-devel flex bison readline-devel pam-devel openssl-devel libxslt-devel # git clone --branch master http://git.postgresql.org/git/postgresql.git # git clone https://github.com/vmware/pg_rewind # cd postgresql # ./configure && make && make install Here it is worth noting that by default PostgreSQL is installed in / usr / local and all utilities are in / usr / local / pgsql / bin, therefore this path must be added to the PATH environment variable. This can be done in different ways, I did the following, more or less decent (and maybe not very) way for CentOS:
# vi /etc/profile.d/pgsql-9.4.sh PGSQL94_BINDIR=/usr/local/pgsql/bin pathmunge $PGSQL94_BINDIR export PGSQL94_BINDIR # source /etc/profile Now go to the directory with the pg_rewind code and build the utility. The resulting utility is simply copied to the postgres utilities.
# cd ../pg_rewind # make USE_PGXS=1 top_srcdir=../postgresql/ # cp pg_rewind /usr/local/pgsql/bin/ Now we set up stream replication. As I wrote at the beginning, for pg_rewind to work successfully, one of two conditions must be met:
1) Clusters must be initialized with checksum support (initdb -k).
or
2) postgres services must be started with the wal_log_hints = on option in postgresql.conf
I chose the second option.
The configuration process has already been described many times , so I will not dwell on this here.
So when replication is up and running, we are failover. This operation is performed by creating a trigger file specified in recovery.conf in the trigger_file option. After creating the trigger file, the stand-by will switch to master mode and start living its life. Therefore, the old master now needs to be turned off.
And now pg_rewind comes into play. The parameters are the directory where the cluster of the old master ( -D ) is located and the options for connecting to the new master server ( --source-server ). Attention, the operation needs to be performed under the user who owns the database cluster (by default, postgres).
$ pg_rewind -D /var/lib/pgsql/9.4/data/ --source-server="host=192.168.122.12 port=5432 user=postgres" The servers diverged at WAL position 0/48D9580 on timeline 1. Rewinding from Last common checkpoint at 0/3000860 on timeline 1 error reading xlog record: record with zero length at 0/48D9620 Done! So rewind done (do not be afraid of the word error, this is a regular test). Now you need to pay attention to the postrgesql.conf and recovery.done files, remember that they are copied from the new wizard and may contain server-specific settings (for example, ip-addresses or port numbers in listen_addresses, port, etc.) The second file, recovery. done should be renamed to recovery.conf and configured to connect to the new master.
$ mv /var/lib/pgsql/9.4/data/recovery.done /var/lib/pgsql/9.4/data/recovery.conf $ vi /var/lib/pgsql/9.4/data/recovery.conf standby_mode = 'on' primary_conninfo = 'user=postgres host=192.168.122.12 port=5432' trigger_file = '/var/lib/pgsql/9.4/data/failover' After the configuration is corrected we launch postgres and look at the server log
$ cat /var/lib/pgsql/9.4/data/pg_log/postgresql-Mon.log LOG: entering standby mode LOG: redo starts at 0/48D95B0 LOG: record with zero length at 0/61D9EE8 LOG: consistent recovery state reached at 0/61D9EE8 LOG: database system is ready to accept read only connections LOG: started streaming WAL from primary at 0/6000000 on timeline 2 Pay attention to the database system is ready to accept read only connections , this is the most important line here indicating that everything went smoothly and postgres started as expected and ready to accept connections. You can check streaming replication with standard tools via pg_stat_replication or create test tables on the wizard and check their availability on the stand-by.
That's all. At the end I will add ... First, in the process between the proclamation of a new master and rewind, the new master is available for recording, so when rewinding you do not need to isolate the master from connections from the application side. Second, the project is still young, is in active development, and in my opinion is not ready for production. So try, test ...
Those who are not ready to do this all or simply lazy, but I want to see, there is a small video for 8 minutes, the switching action starts at 4:20 (who is unfamiliar with postgres will not understand anything).
')
Thank you all for your attention.
Source: https://habr.com/ru/post/216067/
All Articles