Question :
I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this:
.-----. .--------.
| | W | DB |
| APP |-----+----------->| MASTER |----.
| | | | | |
`-----` | `--------` | STREAMING REPLICATION
| .--------. |
| R | DB | |
+----------->| SLAVE1 |<---+
| | | |
| `--------` |
| .--------. |
| R | DB | |
`----------->| SLAVE2 |<---`
| |
`--------`
after failover:
.-----. .--------.
| | | DB |
| APP |-----+---- | MASTER |
| | | | FAIL |
`-----` | `--------`
| .--------.
| W/R | DB |
+----------->| SLAVE1 |---.
| | | |
| `--------` |
| .--------. | STREAMING REPLICATION
| R | DB | |
`----------->| SLAVE2 |<--`
| |
`--------`
Follow the post for HA PostgreSQL Cluster by Streaming Replication + pgpool-II I am created 4 virtual machines (vm) with vagrant. One Master (M), two Slaves (S1 and S2) and other for pgpool (APP). When I do some queries in APP all work fine. When I stop the master, S1 is promoted to M and the S2 now have a new master. But this get me a problem. I don’t know what this error mean and I am lost thinking how to fix it:
2014-08-18 17:04:56 UTC FATAL: timeline 2 of the primary does not match recovery target timeline 1
I am using:
- postgres 9.1
- pgpool 3.1.1-1
UPDATE
This error is emitted by S2 when S1 is promoted to Master.
UPDATE 2
Nodes IP Address:
M - 192.168.1.10
S1 - 192.168.1.11
S2 - 192.168.1.12
APP- 192.168.1.13
This is my recovery.conf file configuration for S1 and S2.
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=replicator'
trigger_file = '/tmp/postgresql.trigger.5432'
UPDATE 3
Here is my backends into the /etc/pgpool2/pgpool.conf file:
...
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.1.12'
backend_port2 = 5432
backend_weight2 = 0
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'
And it is failover.sh file:
#!/bin/sh
FALLING_NODE=$1
OLD_MASTER=$2
NEW_MASTER=$3
SLAVE1='slave1'
SLAVE2='slave2'
if test $FALLING_NODE -eq 0
then
ssh -T $SLAVE1 touch /tmp/postgresql.trigger.5432
ssh -T $SLAVE1 "while test ! -f /var/lib/postgresql/9.1/main/recovery.done; $
ssh -T $SLAVE2 "sed -i 's/192.168.1.10/192.168.1.11/' /var/lib/postgresql/9.$
ssh -T $SLAVE2 /etc/init.d/postgresql restart
/usr/sbin/pcp_attach_node 10 localhost 9898 pgpool pgpool 2
fi
Answer :
The issue is covered in this detailed blog.
In short, the master and the standby servers must all have archive_mode = on
and an archive_command
, until 9.3 which removes this requirement.