How does one setup two identical servers for automatic failover in PostgreSQL 9.1.
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:
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"'
host replication all 10.0.66.1/32 trust host replication all 10.0.66.2/32 trust
postgresql.conf and pg_hba.conf are identical to what is configured on the master server.
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)
- su - postgres
- touch trigger.txt in /opt/pgsql91/data/
- recovery.conf becomes recovery.done
- psql -c ";SELECT pg_start_backup('backup', true)";
- rsync -a -v -e ssh /opt/pgsql91/data/ 10.0.66.1:/opt/pgsql91/data/ --exclude postmaster.pid
- psql -c ";SELECT pg_stop_backup()";
On the new slave (10.0.66.1)
- create the recovery.conf : cp recovery.done to recovery.conf
- vi recovery.conf change ip address : primary_conninfo = 'host=10.0.66.2'
- start postgresql
So my questions are now :
- Is this the correct way to switch roles?
- Has anyone automated this process, if so what did you do?
- 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?