3 improved formatting
source | link

Automating failover in PostgresqlPostgreSQL 9.1

How does one setup two identical servers for automatic failover in postgresqlPostgreSQL 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.

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 syncronizesynchronize 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 that the new master server wouldn'twon't commit any transactions because it wasis waiting for the slave to respond. There is no slave because however because the other server, the old master is down. Is this rightcorrect or do I need to temporarily disable synchronous replication while the new slave is down?

Automating failover in Postgresql 9.1

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 syncronize 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 that the new master server wouldn't commit any transactions because it was waiting for the slave to respond. There is no slave because however because the other server, the old master is down. Is this right or do I need to temporarily disable synchronous replication while the new slave is down?

Automating failover in PostgreSQL 9.1

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?
2 added 997 characters in body
source | link

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 = 816    
wal_keep_segments = 8 
archive_mode = on    
archive_command = 'cp "%p" /opt/pgsql91/archive/"%f"'  

pg_hba.conf

 host  replication   all   10.0.66.21/32      trust

Standby Server

postgresql.conf

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'

pg_hba.conf

host  replication   all   10.0.66.12/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'

So my questions are: Thanks to hzRoot, I now understand how to switch the server from standby to master.

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

On the new master (10.0.66.2)

  1. What is the standard method for failing over from the master to the slave in a minimum amount of time ?

    su - postgres
  2. Is there a way just using postgresql 9.1 to automate this process, or what kind of shell commands/scripts would I need to automate this process? example: scp, rsync.

    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()";

I can not use a third party software, everything has to be native to postgresql 9.1On 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

Thank you in advanceSo 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 that the new master server wouldn't commit any transactions because it was waiting for the slave to respond. There is no slave because however because the other server, the old master is down. Is this right or do I need to temporarily disable synchronous replication while the new slave is down?

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

OS

Centos 5 Postgresql 9.1 compiled from source

My Current Setup :

Master server configuration:

postgresql.conf

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 

pg_hba.conf

 host  replication   all   10.0.66.2/32      trust

Standby Server

postgresql.conf

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'

pg_hba.conf

host  replication   all   10.0.66.1/32      trust

recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=10.0.66.1'

So my questions are:

  1. What is the standard method for failing over from the master to the slave in a minimum amount of time ?

  2. Is there a way just using postgresql 9.1 to automate this process, or what kind of shell commands/scripts would I need to automate this process? example: scp, rsync.

I can not use a third party software, everything has to be native to postgresql 9.1

Thank you in advance

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 syncronize 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 that the new master server wouldn't commit any transactions because it was waiting for the slave to respond. There is no slave because however because the other server, the old master is down. Is this right or do I need to temporarily disable synchronous replication while the new slave is down?
1
source | link

Automating failover in Postgresql 9.1

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

OS

Centos 5 Postgresql 9.1 compiled from source

My Current Setup :

Master server configuration:

postgresql.conf

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 

pg_hba.conf

 host  replication   all   10.0.66.2/32      trust

Standby Server

postgresql.conf

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'

pg_hba.conf

host  replication   all   10.0.66.1/32      trust

recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=10.0.66.1'

So my questions are:

  1. What is the standard method for failing over from the master to the slave in a minimum amount of time ?

  2. Is there a way just using postgresql 9.1 to automate this process, or what kind of shell commands/scripts would I need to automate this process? example: scp, rsync.

I can not use a third party software, everything has to be native to postgresql 9.1

Thank you in advance