The failure of the master PostgreSQL cluster: how to be?
Welcome. Today I would like to talk about such a bad situation as a failure of the master in the case of the native replication for PostgreSQL 9.x. So, suppose you have a cluster of two or more PostgreSQL database servers in a master suddenly a meteorite fell. It is logical to assume that you will have to do a master of one of the replicas. This can be done in two ways.
the manual for configuring replication it is said that in recovery.conf among other things you can(and should) specify the parameter trigger_file. It's simple — once you create the replica of the file specified in this parameter, PostgreSQL will abort the restore(in our case, replication) and will open a new timeline.
This means that after you create the trigger file change position binary counter log, and not consistently(say — on 000000010000000000000043 000000010000000000000044), indicating a new era — (000000020000000000000043).
The good news is that this method does not require restart — everything happens on the fly. Downtime will not be(time to change configs on the clients is not taken into account), all connections are just going to kill PostgreSQL process and walreceiver will give the nod to the entry.
The bad news is that this method is good if you only have two servers — master and one replica in case the cluster consists of 3 or more machines — to make this node the new master without perevalivka other replicas will not work — while trying to bind another replica to the new master, PostgreSQL consistently says the following:
the
All attempts to shove replicas history file(which stores the point of transition to the new timeline — this file is created every time at the end of the recovery process) with success also have not crowned. In General, the members of the official MailList' adhere to this point of view — in this approach, the other replicas will have peronality(in the case of 9.0 — using pg_start_backup/pg_stop_backup and rsync, and in the case 9.1 — using pg_basebackup utility).
Somehow, the description of the second method in the manual could not be found(perhaps it is there and I was not careful enough — I will not argue). I think you will agree that it is simple, logical and, apparently, reliable(at least — I have not managed anything to break in the process of multiple experiments):
1. From the entire cluster, you need to find the most fresh replica. You can do it from the console running on each host, something of the form:
If a meteorite on your master hasn't fallen yet, but you expect it from minute to minute the master node it is better to extinguish in advance, so that the position of the binary log has not changed during your manipulations.
2. For the selected replica, changing postgresql.conf so that the node could be master(see below for parameters taken from the manual for configuring replication in your case the values can of course differ):
3. Pg_hba rules.conf:
the
4. Deleted on the new wizard recovery.conf
5. Do restart PostgreSQL on the new master.
6. Run recovery.conf on the rest of the replicas(indicated by a new master) and perform restarts.
So here's a simple way you can convert the replica to the master without losing the position of the binary log. From obvious minuses — have to restarting the entire cluster(although if you have the ability to override the IP address with the old master to the new — restart of replicas is not required).
If you for some reason want to make a new master replica, the position of the bin-log, which is not the most new — would first have to appoint a master of the new replica(it is desirable to cover it from accidental connections to write), synchronize it with the replica from which you want to eventually make a master and then do it all operations described above(that is, in fact — you assign master twice — first only one replica, and then for all others).
In General, in the experiments, the only problem the place that we managed to find a situation when some of the replicas behind so that the new master no longer needed XLOG files. How to deal with it I described in my previous post — can add only that if you send backup binary logs at bekapy server — this issue can hardly be called significant.
Article based on information from habrahabr.ru
1. The use of the trigger file.
the manual for configuring replication it is said that in recovery.conf among other things you can(and should) specify the parameter trigger_file. It's simple — once you create the replica of the file specified in this parameter, PostgreSQL will abort the restore(in our case, replication) and will open a new timeline.
This means that after you create the trigger file change position binary counter log, and not consistently(say — on 000000010000000000000043 000000010000000000000044), indicating a new era — (000000020000000000000043).
The good news is that this method does not require restart — everything happens on the fly. Downtime will not be(time to change configs on the clients is not taken into account), all connections are just going to kill PostgreSQL process and walreceiver will give the nod to the entry.
The bad news is that this method is good if you only have two servers — master and one replica in case the cluster consists of 3 or more machines — to make this node the new master without perevalivka other replicas will not work — while trying to bind another replica to the new master, PostgreSQL consistently says the following:
the
FATAL: timeline 2 of the primary does not match recovery target timeline 1All attempts to shove replicas history file(which stores the point of transition to the new timeline — this file is created every time at the end of the recovery process) with success also have not crowned. In General, the members of the official MailList' adhere to this point of view — in this approach, the other replicas will have peronality(in the case of 9.0 — using pg_start_backup/pg_stop_backup and rsync, and in the case 9.1 — using pg_basebackup utility).
2. Deletion recovery.conf
Somehow, the description of the second method in the manual could not be found(perhaps it is there and I was not careful enough — I will not argue). I think you will agree that it is simple, logical and, apparently, reliable(at least — I have not managed anything to break in the process of multiple experiments):
1. From the entire cluster, you need to find the most fresh replica. You can do it from the console running on each host, something of the form:
# ps aux|grep postg|grep rec
postgres 143 0.0 13.2 8692004 6533448 ? Ss Feb06 3:58 postgres: startup process recovering 00000001000001E500000054
postgres 2683 0.0 0.0 4044 8699452 ? Ss Feb09 0:33 postgres: wal receiver process streaming 1E5/542F9970
If a meteorite on your master hasn't fallen yet, but you expect it from minute to minute the master node it is better to extinguish in advance, so that the position of the binary log has not changed during your manipulations.
2. For the selected replica, changing postgresql.conf so that the node could be master(see below for parameters taken from the manual for configuring replication in your case the values can of course differ):
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /path_to/archive/%f'
3. Pg_hba rules.conf:
the
host replication postgres 192.168.100.3/32 trust4. Deleted on the new wizard recovery.conf
5. Do restart PostgreSQL on the new master.
6. Run recovery.conf on the rest of the replicas(indicated by a new master) and perform restarts.
So here's a simple way you can convert the replica to the master without losing the position of the binary log. From obvious minuses — have to restarting the entire cluster(although if you have the ability to override the IP address with the old master to the new — restart of replicas is not required).
If you for some reason want to make a new master replica, the position of the bin-log, which is not the most new — would first have to appoint a master of the new replica(it is desirable to cover it from accidental connections to write), synchronize it with the replica from which you want to eventually make a master and then do it all operations described above(that is, in fact — you assign master twice — first only one replica, and then for all others).
In General, in the experiments, the only problem the place that we managed to find a situation when some of the replicas behind so that the new master no longer needed XLOG files. How to deal with it I described in my previous post — can add only that if you send backup binary logs at bekapy server — this issue can hardly be called significant.
Комментарии
Отправить комментарий