4

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
  • When exactly is that error emitted, and by what? Is that message emitted by S2 after you promote S1 to M? What order do you perform promotion and recovery setup in and what steps exactly? – Craig Ringer Aug 19 '14 at 13:18
  • I forgot to say that the log is emitted by S2 when S1 is promoted to Master. – Robert Aug 19 '14 at 13:24
  • Do you switch S2 to replicate from S1 before or after promoting it to master? Do you shut down the old master or do something else with it? – Craig Ringer Aug 19 '14 at 14:19
  • The workflow is: I stopped the Master, then S1 become in the new master and S2 point to it. – Robert Aug 19 '14 at 14:45
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.