Watchdog for replication in PostgreSQL 9
Welcome. I want to share one samopisny a crutch, maybe someone will be useful.
Simulated situation: there is a cluster of PostgreSQL database servers — master and n replicas. There comes a rainy day and one(or more) replicas falls. The reasons are unimportant — dead piece of iron, the cleaning lady broke a MOP wire or a UFO temporarily sohawal server. Bottom line, if the replica did, she never nahoditsya.
The reason is in the process of replication PostgreSQL. Upon receipt of the data, the magazine resets to the disk(by default — chunks 16Mb). In the config wizard, we use the option wal_keep_segments, which indicates how many recent pieces XLOG-we will store. Let, the option will be equal to 16. This means that in the pg_xlog directory at the same time, is 16-log files, and with the appearance of the 17th, an attempt is made to send the 1st to the archive(in other words — there is a constant rotation). And if your remark will be left behind, more than these 16 files — when trying to catch up with the master, in the log you will see message that required piece XLOG-cannot be obtained because the wizard he was gone to the archive. And all because the default archiving command typically looks like this:
As you can see, no checks, will not damage any of the backup file.
In fact — nothing in it is not deadly — if the correct archive file was not deleted, you just need to slip it on the replica in the right place and the file itself is "rolled". Bad point is that, depending on load, to copy, as a rule have not a single file, and dozens/hundreds/thousands. Besides, the process you need to follow since while you copy some files to the master archive new. In the worst case can be saved as a complete "perevalivka" replica.
In order not to face such joys, I wrote a small script in a mixture of Perl and Bash. I will say in advance that in no case do not claim to be the best programmer — the main condition was not the beauty of the code, and take appropriate action. So a script here, and I'll tell you what he's doing.
As we can see from this simple command in the processes list can be found:
1. The number of connected replicas with their ip addresses.
2. A piece of XLOG, which they are currently being processed.
And we in turn, as the people running this design, we know how much we have replicas and their addresses. Pay attention to the last part of the lines is the position of the XLOG-specific cues. If we remove this value from the resulting slash and cut off the last 6 characters then we would get 153ED. This 16 tericee number.
And even in the processes list we can find:
The last part of the string file that was archived. Pick up the last 13 characters, remove 6 zeros, consecutive. Get 153EB.
Convert the resulting numbers are in decimal(for clarity), discover and 87019 87021. Comparing these two numbers we find that the replica uses the log file which is older than the last archived 2 steps — hence, one fragment we can safely remove.
Based on these simple manipulations and running my script:
1. The administrator puts in /etc/postgresql/9.0/main/slaves.list is a list of replicas(one per line).
2. The script fits as archive_command in PostgreSQL configuration.
3. When trying to archive a piece of XLOG and the following happens:
3.1. Tests if the number of connected replicas, the number specified in the config file.
3.2. If all is well — look whether the hosts specified in the config(made in case when you are extending a cluster, we forget to record a new replica configuration).
3.3. Calculated minimum position XLOG-replica(look for the Laggards).
3.4. Compare the number from the file name to be archived and the number of the preceding paragraph.
If all the conditions pass, copy the file to the archive and return 0. If something went wrong — return unit — PostgreSQL considers the attempt failed and after some time will take next. The process can be monitored in /var/log/postgresql/9.0/watchdog.log
Achtung! Means that the replica list will point to the existing DNS records(done for personal convenience), or names that are specified locally in /etc/hosts. To disable this — you need to comment out line at the 47th 55ю.
Article based on information from habrahabr.ru
quick facts
Simulated situation: there is a cluster of PostgreSQL database servers — master and n replicas. There comes a rainy day and one(or more) replicas falls. The reasons are unimportant — dead piece of iron, the cleaning lady broke a MOP wire or a UFO temporarily sohawal server. Bottom line, if the replica did, she never nahoditsya.
The reason is in the process of replication PostgreSQL. Upon receipt of the data, the magazine resets to the disk(by default — chunks 16Mb). In the config wizard, we use the option wal_keep_segments, which indicates how many recent pieces XLOG-we will store. Let, the option will be equal to 16. This means that in the pg_xlog directory at the same time, is 16-log files, and with the appearance of the 17th, an attempt is made to send the 1st to the archive(in other words — there is a constant rotation). And if your remark will be left behind, more than these 16 files — when trying to catch up with the master, in the log you will see message that required piece XLOG-cannot be obtained because the wizard he was gone to the archive. And all because the default archiving command typically looks like this:
cp %p /var/lib/postgresql/9.0/main/archive/%fAs you can see, no checks, will not damage any of the backup file.
In fact — nothing in it is not deadly — if the correct archive file was not deleted, you just need to slip it on the replica in the right place and the file itself is "rolled". Bad point is that, depending on load, to copy, as a rule have not a single file, and dozens/hundreds/thousands. Besides, the process you need to follow since while you copy some files to the master archive new. In the worst case can be saved as a complete "perevalivka" replica.
In order not to face such joys, I wrote a small script in a mixture of Perl and Bash. I will say in advance that in no case do not claim to be the best programmer — the main condition was not the beauty of the code, and take appropriate action. So a script here, and I'll tell you what he's doing.
How it works
master01:~# ps aux|grep postg|grep sender
postgres 26132 0.0 0.0 6518660 3052 ? Ss 20:21 0:01 postgres: wal sender process postgres 192.168.12.1(36254) streaming 153/ED957E68
postgres 26133 0.0 0.0 3056 6518660 ? Ss 20:21 0:01 postgres: wal sender process postgres 192.168.12.2(51907) streaming 153/ED957E68
postgres 0.0 0.0 26135 6518660 3060 ? Ss 20:21 0:01 postgres: wal sender process postgres 192.168.12.3(39404) streaming 153/ED957E68
postgres 29142 0.0 0.0 6518724 3084 ? Ss 20:44 0:01 postgres: wal sender process postgres 192.168.12.4(51897) streaming 153/ED957E68
postgres 29320 0.0 0.0 6518724 3084 ? Ss 20:45 0:01 postgres: wal sender process postgres 192.168.12.5(49234) streaming 153/ED957E68
postgres 29453 0.0 0.0 6518724 3084 ? Ss 20:46 0:01 postgres: wal sender process postgres 192.168.12.6(35519) streaming 153/ED957E68
As we can see from this simple command in the processes list can be found:
1. The number of connected replicas with their ip addresses.
2. A piece of XLOG, which they are currently being processed.
And we in turn, as the people running this design, we know how much we have replicas and their addresses. Pay attention to the last part of the lines is the position of the XLOG-specific cues. If we remove this value from the resulting slash and cut off the last 6 characters then we would get 153ED. This 16 tericee number.
And even in the processes list we can find:
master01:~# ps aux|grep postg|grep arch
556 postgres 0.0 0.0 1668 66184 ? Ss Oct11 0:23 postgres: archiver process last was 0000000100000153000000EBThe last part of the string file that was archived. Pick up the last 13 characters, remove 6 zeros, consecutive. Get 153EB.
Convert the resulting numbers are in decimal(for clarity), discover and 87019 87021. Comparing these two numbers we find that the replica uses the log file which is older than the last archived 2 steps — hence, one fragment we can safely remove.
Based on these simple manipulations and running my script:
1. The administrator puts in /etc/postgresql/9.0/main/slaves.list is a list of replicas(one per line).
2. The script fits as archive_command in PostgreSQL configuration.
3. When trying to archive a piece of XLOG and the following happens:
3.1. Tests if the number of connected replicas, the number specified in the config file.
3.2. If all is well — look whether the hosts specified in the config(made in case when you are extending a cluster, we forget to record a new replica configuration).
3.3. Calculated minimum position XLOG-replica(look for the Laggards).
3.4. Compare the number from the file name to be archived and the number of the preceding paragraph.
If all the conditions pass, copy the file to the archive and return 0. If something went wrong — return unit — PostgreSQL considers the attempt failed and after some time will take next. The process can be monitored in /var/log/postgresql/9.0/watchdog.log
Achtung! Means that the replica list will point to the existing DNS records(done for personal convenience), or names that are specified locally in /etc/hosts. To disable this — you need to comment out line at the 47th 55ю.
Комментарии
Отправить комментарий