Hi Thank you for comments.
> First of all thanks for doing this. It's definitely a neat option to
> have. Unfortunately I haven't yet worked with ProstgreSQL 9.0. Could
> you please elaborate little bit more on how it works? What happens if
> master dies? Who is responsible for creating/deleting recovery.conf
> file?
This patch does not setup recovery.conf for hot standby automatically,
so you need setup it manually before starting hot standby.
If recovery.conf exists, resource agent will become slave.
Die of master causes fail-over correctly.
> Also please be more careful with quoting variables in your if statements.
I checked and quoted.
I elaborate.
**************** Condition *********************************
1. Streaming replication works correctly.
2. Streaming replication works correctly if you swap
primary server for hot standby server.
FYA I wrote my configuration at the end of the post.
See "Example of configuration".
**************** Setup and start ***************************
1. You decide which server do you want to be master.
In this instance I assume that you choice node1.
2. node1# rm /var/lib/pgsql/9.0/data/recovery.conf
3. node1# /etc/init.d/heartbeat start
Wait for "ONLINE node1".
4. node1# crm configure load update pgsql.crm
5. Wait for master started as below
node1# crm_mon -1 -fA
------------------------------------------------------------
============
Last updated: Tue Feb 8 18:51:00 2011
Stack: Heartbeat
Current DC: node1 (0c140f90-7de3-438f-b1b5-3b9722bbde21) -
partition with quorum
Version: 1.0.10-da7075976b5ff0bee71074385f8fd02f296ec8a3
2 Nodes configured, unknown expected votes
3 Resources configured.
============
Online: [ node1 ]
vip (ocf::heartbeat:IPaddr2): Started node1
Master/Slave Set: msPostgresql
Masters: [ node1 ]
Stopped: [ postgresql:1 ]
Clone Set: clnPingd
Started: [ node1 ]
Stopped: [ pingd:1 ]
Node Attributes:
* Node node1:
+ master-postgresql:0 : 1000
+ pgsql-status : PRI
+ ping_set : 100
Migration summary:
* Node node1:
------------------------------------------------------------
If start operation fails, please check PostgreSQL state using "crm_mon -A".
"pgsql-status" line shows something.
6. Backup node1's data and restore to node2.
In my case I use rsync command as below.
node2# psql -h 192.168.2.107 -U postgres -c "SELECT pg_start_backup('label')"
node2# rsync -avr --delete --exclude=postmaster.pid
192.168.2.107:/var/lib/pgsql/9.0/data/ /var/lib/pgsql/9.0/data/
node2# psql -h 192.168.2.107 -U postgres -c "SELECT pg_stop_backup()"
7. Put recovery.conf for node2.
node2# cp recovery.conf /var/lib/pgsql/9.0/data/
node2# chown postgres:postgres /var/lib/pgsql/9.0/data/recovery.conf
8. node2# /etc/init.d/heartbeat start
9. Wait for slave started as below.
node2# crm_mon -1 -fA
------------------------------------------------------------
============
Last updated: Tue Feb 8 19:41:00 2011
Stack: Heartbeat
Current DC: node1 (0c140f90-7de3-438f-b1b5-3b9722bbde21) -
partition with quorum
Version: 1.0.10-da7075976b5ff0bee71074385f8fd02f296ec8a3
2 Nodes configured, unknown expected votes
3 Resources configured.
============
Online: [ node1 node2 ]
vip (ocf::heartbeat:IPaddr2): Started node1
Master/Slave Set: msPostgresql
Masters: [ node1 ]
Slaves: [ node2 ]
Clone Set: clnPingd
Started: [ node1 node2 ]
Node Attributes:
* Node node1:
+ master-postgresql:0 : 1000
+ pgsql-status : PRI
+ ping_set : 100
* Node node2:
+ master-postgresql:1 : 100
+ pgsql-status : HS
+ ping_set : 100
Migration summary:
* Node node1:
* Node node2:
------------------------------------------------------------
If master doesn't exist in other node or recovery.conf doesn't
exist in node2,
start operation fails.
********************** Other Operation ***************************************
1. If node1's master dies, node2 becomes a master.
node1# killall -9 postgres
node1# crm_mon -1 -fA
------------------------------------------------------------
============
Last updated: Tue Feb 8 20:12:34 2011
Stack: Heartbeat
Current DC: node1 (0c140f90-7de3-438f-b1b5-3b9722bbde21) -
partition with quorum
Version: 1.0.10-da7075976b5ff0bee71074385f8fd02f296ec8a3
2 Nodes configured, unknown expected votes
3 Resources configured.
============
Online: [ node1 node2 ]
vip (ocf::heartbeat:IPaddr2): Started node2
Master/Slave Set: msPostgresql
Masters: [ node2 ]
Stopped: [ postgresql:0 ]
Clone Set: clnPingd
Started: [ node1 node2 ]
Node Attributes:
* Node node1:
+ master-postgresql:0 : -INFINITY
+ pgsql-status : STOP:with errors
+ ping_set : 100
* Node node2:
+ master-postgresql:1 : 1000
+ pgsql-status : PRI
+ ping_set : 100
Migration summary:
* Node node1:
postgresql:0: migration-threshold=1 fail-count=1
* Node node2:
Failed actions:
postgresql:0_monitor_9000 (node=node1, call=14, rc=7,
status=complete): not running
------------------------------------------------------------
2. To recover node1, backup node2's data and restore to node1.
node1# psql -h 192.168.2.207 -U postgres -c "SELECT pg_start_backup('label')"
node1# rsync -avr --delete --exclude=postmaster.pid
192.168.2.207:/var/lib/pgsql/9.0/data/ /var/lib/pgsql/9.0/data/
node1# psql -h 192.168.2.207 -U postgres -c "SELECT pg_stop_backup()"
3. Put recovery.conf for node1.
node1# cp recovery.conf /var/lib/pgsql/9.0/data/
node1# chown postgres:postgres /var/lib/pgsql/9.0/data/recovery.conf
4. Remove flag file of repressing start.
node1# rm /var/lig/pgsql/PGSQL.5432.repress_start
5. node1# crm resource cleanup msPostgresql
6. Node1 becomes slave as below.
node1# crm_mon -1 -fA
------------------------------------------------------------
============
Last updated: Tue Feb 8 20:32:04 2011
Stack: Heartbeat
Current DC: node1 (0c140f90-7de3-438f-b1b5-3b9722bbde21) -
partition with quorum
Version: 1.0.10-da7075976b5ff0bee71074385f8fd02f296ec8a3
2 Nodes configured, unknown expected votes
3 Resources configured.
============
Online: [ node1 node2 ]
vip (ocf::heartbeat:IPaddr2): Started node2
Master/Slave Set: msPostgresql
Masters: [ node2 ]
Slaves: [ node1 ]
Clone Set: clnPingd
Started: [ node1 node2 ]
Node Attributes:
* Node node1:
+ master-postgresql:0 : 100
+ pgsql-status : HS
+ ping_set : 100
* Node node2:
+ master-postgresql:1 : 1000
+ pgsql-status : PRI
+ ping_set : 100
Migration summary:
* Node node1:
* Node node2:
------------------------------------------------------------
********************** Example of configuration ****************************
* postgresql.conf (common configuration for node1 and node2)
- Please mkdir /var/lib/pgsql/9.0/data/pg_archive preliminarily
- To cause fail-over correctly, set listen_addresses = '*'
if you use virtual IP together.
---------------------------------------------------------------------------
listen_addresses = '*'
port = 5432
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 16
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_archive/%f'
---------------------------------------------------------------------------
* pg_hba.conf (common configuration for node1 and node2)
- 192.168.2.0/24 is LAN for replication.
- Node1 has 192.168.2.107 and Node2 has 192.168.2.207.
---------------------------------------------------------------------------
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 192.168.2.0/24 trust
host replication all 192.168.2.0/24 trust
---------------------------------------------------------------------------
* recovery.conf for node1
---------------------------------------------------------------------------
standby_mode = 'on'
primary_conninfo = 'host=192.168.2.207 port=5432 user=postgres'
restore_command = 'cp /var/lib/pgsql/9.0/data/pg_archive/%f %p'
trigger_file = '/var/lib/pgsql/PGSQL.5432.trigger'
---------------------------------------------------------------------------
* recovery.conf for node2
---------------------------------------------------------------------------
standby_mode = 'on'
primary_conninfo = 'host=192.168.2.107 port=5432 user=postgres'
restore_command = 'cp /var/lib/pgsql/9.0/data/pg_archive/%f %p'
trigger_file = '/var/lib/pgsql/PGSQL.5432.trigger'
---------------------------------------------------------------------------
* pgsql.crm for crm command
---------------------------------------------------------------------------
property \
no-quorum-policy="ignore" \
stonith-enabled="false" \
startup-fencing="false" \
rsc_defaults \
resource-stickiness="INFINITY" \
migration-threshold="1"
ms msPostgresql postgresql \
meta \
master-max="1" \
master-node-max="1" \
clone-max="2" \
clone-node-max="1" \
notify="true"
clone clnPingd pingd \
meta \
clone-max="2" \
clone-node-max="1"
primitive vip ocf:heartbeat:IPaddr2 \
params \
ip="172.20.24.127" \
nic="eth0" \
cidr_netmask="16" \
op start timeout="60s" interval="0s" on-fail="restart" \
op monitor timeout="60s" interval="10s" on-fail="restart" \
op stop timeout="60s" interval="0s" on-fail="block"
primitive postgresql ocf:heartbeat:pgsql \
params \
pgctl="/usr/pgsql-9.0/bin/pg_ctl" \
psql="/usr/pgsql-9.0/bin/psql" \
pgdata="/var/lib/pgsql/9.0/data/" \
start_opt="-p 5432" \
rep_mode="async" \
op start timeout="3600s" interval="0s" on-fail="restart" \
op monitor timeout="60s" interval="10s" on-fail="restart" \
op monitor timeout="60s" interval="9s" on-fail="restart"
role="Master" \
op promote timeout="3600s" interval="0s" on-fail="restart" \
op demote timeout="60s" interval="0s" on-fail="block" \
op stop timeout="60s" interval="0s" on-fail="block"
primitive pingd ocf:pacemaker:pingd \
params \
name="ping_set" \
host_list="172.20.24.103" \
multiplier="100" \
dampen="0" \
op start timeout="60s" interval="0s" on-fail="restart" \
op monitor timeout="60s" interval="10s" on-fail="restart" \
op stop timeout="60s" interval="0s" on-fail="ignore"
location rsc_location-1 vip \
rule -inf: not_defined ping_set or ping_set lt 100
location rsc_location-2 msPostgresql \
rule -inf: not_defined ping_set or ping_set lt 100 \
rule -inf: defined fail-count-vip
colocation rsc_colocation-1 inf: vip clnPingd
colocation rsc_colocation-2 inf: msPostgresql clnPingd
colocation rsc_colocation-3 inf: msPostgresql:Master vip
order rsc_order-1 0: clnPingd msPostgresql
order rsc_order-2 0: msPostgresql vip
order rsc_order-3 0: vip msPostgresql:promote
---------------------------------------------------------------------------
Regards,
Takatoshi MATSUO
pgsql.patch
Description: Binary data
_______________________________________________________ Linux-HA-Dev: Linux-HA-Dev@lists.linux-ha.org http://lists.linux-ha.org/mailman/listinfo/linux-ha-dev Home Page: http://linux-ha.org/
