18

How does one setup two identical servers for automatic failover in PostgreSQL 9.1.

OS

Centos 5
PostgreSQL 9.1 compiled from source
The postgres user account exists on both machines and has a ssh passwordless key to connect to both machines.

My Current Setup :

Master server configuration:

postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 16    
wal_keep_segments = 8 
archive_mode = on    
archive_command = 'cp "%p" /opt/pgsql91/archive/"%f"'  

pg_hba.conf:

 host  replication   all   10.0.66.1/32      trust
 host  replication   all   10.0.66.2/32      trust

Standby Server

postgresql.conf and pg_hba.conf are identical to what is configured on the master server.

recovery.conf:

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'
 trigger_file = '/opt/pgsql91/data/trigger.txt'

Thanks to hzRoot, I now understand how to switch the server from standby to master.

Using the following commands, I can synchronize the new slave with the new master and then get replication backup and running.

On the new master (10.0.66.2)

  1. su - postgres
  2. touch trigger.txt in /opt/pgsql91/data/
  3. recovery.conf becomes recovery.done
  4. psql -c ";SELECT pg_start_backup('backup', true)";
  5. rsync -a -v -e ssh /opt/pgsql91/data/ 10.0.66.1:/opt/pgsql91/data/ --exclude postmaster.pid
  6. psql -c ";SELECT pg_stop_backup()";

On the new slave (10.0.66.1)

  1. create the recovery.conf : cp recovery.done to recovery.conf
  2. vi recovery.conf change ip address : primary_conninfo = 'host=10.0.66.2'
  3. start postgresql

So my questions are now :

  1. Is this the correct way to switch roles?
  2. Has anyone automated this process, if so what did you do?
  3. If synchronous replication is enabled, I noticed the new master server won't commit any transactions because it is waiting for the slave to respond. There is no slave however because the other server, the old master is down. Is this correct or do I need to temporarily disable synchronous replication while the new slave is down?
3
  • 1. yes correct 2. may be it is better not to automate that process. 3. so you need 2 slave and 1 master at least. because as you said sync. replication need at least 2 nodes to push commits sync. if it there is just one master node, you will not be able to commit..
    – sftsz
    Jan 24, 2012 at 6:33
  • steps 4, 5, and 6 are not necessary on the new master because, well, you're replicating to begin with. Second, what if the master died and was offline - you would not be able to connect to it. Steps 4,5, & 6 are typically done on a new slave node joining the replication pool.
    – Eric
    Mar 24, 2013 at 2:51
  • @Eric as I played with this, steps 4,5,6 are required to bring back the old master to working state. Making the standby new primary immediately makes new WAL entry, so it's now 1 entry ahead of the old master. Starting the old master in standy mode threw errors on me, so i did have to make steps 4,5,6 on the old master to sync it with the new master (by using pg_basebackup, which can stream the whole xlog from the new master - replaces steps 4,5,6 in postgres >= 9.1 I think). Am I correct or did I do something wrong and this shoudln't be necessary? Nov 27, 2013 at 19:31

3 Answers 3

8

Check out repmrg:

repmgr is a set of open source tools that helps DBAs and System administrators manage a cluster of PostgreSQL databases..

By taking advantage of the Hot Standby capability introduced in PostgreSQL 9, repmgr greatly simplifies the process of setting up and managing database with high availability and scalability requirements.

repmgr simplifies administration and daily management, enhances productivity and reduces the overall costs of a PostgreSQL cluster by:

  • monitoring the replication process; allowing DBAs to issue high
  • availability operations such as switch-overs and fail-overs.

It does two things:

  1. repmgr: command program that performs tasks on your cluster and then exits
  2. repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.

For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.

Version 2.0 is about to be released, including RPM's.

2
  • Hello Frank, thank you for your answer. I have not heard of repmrg and I will definitely give it a try. Jul 7, 2012 at 17:49
  • Hello again Frank, Thanks for the repmgr, it was exactly what I was looking for. I finally got to try it out today. Jul 19, 2012 at 15:25
4

in your recovery.conf file you should add a line that tells postgres to failover from master to slave. you should add

trigger_file = '/any/file/to/trigger'

when you create this file on given path. nodes will change. (file don't include anything it is just a trigger)

you can find additional information on streaming replication

on the other hand, may be it will possible to make it automatically created with some tricks but using monitoring tools and making fail over manual will be better..

4
  • Thank you for the response. It may be a couple of days before I can test it out but I will definitely get back to you. Jan 2, 2012 at 15:08
  • I'm going to give you +1 for the trigger_file answer which has helped me to greatly streamline the process. It isn't the entire answer which is how to fully automate the process. One other thing I've noticed is that while the master was down, transactions wouldn't complete because it was waiting for the master to acknowledge. This was resolved by using async replication Jan 2, 2012 at 18:04
  • That's pretty awesome. I have a lot of criticisms about the lack of flexibility in PostgreSQL's replication implementation, but this is a great, simple way of handling failover. Jul 6, 2012 at 19:16
  • 1
    It does however takeover the master role even when the master itself is still running (so you have two masters). This is not automated by postgres itself. Nov 27, 2013 at 19:28
0

Has anyone considered using pgpool-II for this?

http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html

I setting up replication for PostgreSQL. It seems the tricky part happens when the old master comes back.

From what I have read, pgpool seems like it can automate most of that. However I'm not sure if leverages the replication features already present in PostgreSQL 9.1.

4
  • 1
    pgPool is a single point of failure, you loose everything when is goes down. Jul 7, 2012 at 9:51
  • 1
    Thank you for your answer. I have tried PGPool II with mixed results on both CentOS and Debian and ultimately gave up. Jul 7, 2012 at 17:47
  • 1
    Why not to use pgpool II with HAproxy? With a heartbeat and floating ip listening? Feb 12, 2014 at 17:13
  • Just for historic reference, pgpool-ii also doesn't currently run on Windows.
    – tommed
    Aug 26, 2014 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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