Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
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 '12 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 '13 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? –  NoICE Nov 27 '13 at 19:31

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
Hello Frank, thank you for your answer. I have not heard of repmrg and I will definitely give it a try. –  Craig Efrein Jul 7 '12 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. –  Craig Efrein Jul 19 '12 at 15:25

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..

share|improve this answer
    
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. –  Craig Efrein Jan 2 '12 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 –  Craig Efrein Jan 2 '12 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. –  Aaron Brown Jul 6 '12 at 19:16
    
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. –  NoICE Nov 27 '13 at 19:28

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.

share|improve this answer
1  
pgPool is a single point of failure, you loose everything when is goes down. –  Frank Heikens Jul 7 '12 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. –  Craig Efrein Jul 7 '12 at 17:47
    
Why not to use pgpool II with HAproxy? With a heartbeat and floating ip listening? –  mikiemorales Feb 12 at 17:13
    
Just for historic reference, pgpool-ii also doesn't currently run on Windows. –  tommed Aug 26 at 8:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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