免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 5196 | 回复: 0

High Availability PostgreSQL HOWTO[转贴]建议转精华 [复制链接]

论坛徽章:
0
发表于 2003-05-16 09:23 |显示全部楼层
http://www.taygeta.com/ha-postgresql.html
Introduction:
This note describes ways to implement High Availibility (HA) for PostgreSQL. HA gives the ability to transparently fail-over database connections to an alternative system in the event of some sort of failure of the primary system.


Overview:
The HA-PostgreSQL system works the following way: The HA cluster consists of two machines running heartbeat that use both a serial port and a second network interface to provide the failover heartbeat. The primary system keeps the secondary system database files synchronized by periodic calls to a script that runs rsync.

The heartbeat software provides HA in the form of a hot standby 2-node cluster. In the event of a failure of the primary database system, the heartbeat software causes the secondary to take over. Any database changes that may have occurred between the last synchronization and the failure will be lost, so this synchronization must be done relatively frequently. The synchronization is done with the same network interface that is also doing the heartbeat in order to reduce the volume of traffic on the primary network.


Setting up HA-PostgreSQL
Install heartbeat(http://linux-ha.org/) and get it running properly on the two systems. The installation should use both a serial connection and a secondary network interface (connected via a cross-over cable) to implement the heartbeat. In what follows, I will assume that the secondary network interface is eth1 on both systems and that 10.0.0.1 (hb-castor) is the primary system (castor) and 10.0.0.2 (hb-pollux) is the secondary system (pollux).

Configure and install postgreSQL (http://www.postgreSQL.org/) on both systems. You should use the same version of postgresql and the same configuration options for both systems. It will also be less confusing to use the same path, uid and gid settings for both machines. In what follows, I will assume that the postgresql datafile directory, PGDATA, is /home/pgsql on both systems.
Test postgreSQL, individually on each system. When you are satisfied that it is running properly then bring it down on both sides. On the SECONDARY, clear out the datafile directory:

cd /home/pgsql/base; rm -rf *

Next decide upon a synchronization scheme for the servers. This is tricky because postgreSQL may have cached some of its recent actions in memory and not written them to disk yet.
There are at least three synchronization methods that can be used:

use pg_dump/pg_dumpall. This will assure consistant replication even if the database is being used concurrently. The problem with this approach is that there is no way to may incremental backups so that there can be a needlessly large volume of traffic between the servers if the databases are large. Using pg_dump will require iterating the synchronization over all the individual databases on the server but can handle large binary objects. Using pg_dumpall will handle the entire collection of databases but it cannot handle large binary objects.

use rsync. This method will keep the volume of data being moved between the servers down. The problem is that since this method works with the data files, its not supposed to work. I have found that in practice, for large databases with low transaction volumes, that it can work. If you have large databases with a low INSERT/UPDATE rates, you might want to experiment with this approach to see if it works for you. Rsync setup details.

use rserv. Recent postgreSQL versions (after 7.0) contain a package in the contrib section called rserv. This is a toolset for replicating a master database to a slave database. Using rserv will require iterating the synchronization over all the individual databases on the server. It also requires your database tables to contain a column that can be monitored for updates in order for it to detect what to synchronize (I do not yet know what it does if you add/delete a row to/from a table). I have not yet tried doing this for handling failovers, for example it has a master/slave concept, but in a failover situation the roles get reversed, how rserv will work under this situation I still need to test. I will report here once I have (or you can let me know what you did if you have done it this way).


Test the transfer.
We can now test the primary-to-secondary transfer with one of the following on the appropriate system:

Method Command run from
pg_dump pg_dump -b -h 10.0.0.1 (dbname) >; /tmp/db.tar; pg_restore -c -d (dbname) /tmp/db.tar; rm /tmp/db.tar  secondary
pg_dumpall pg_dumpall -h 10.0.0.1 >; /tmp/db.dump; pg_restore -f /tmp/db.dump template1; rm /tmp/db.dump  secondary
rsync /usr/bin/rsync -auv --delete /home/pgsql/ 10.0.0.2::postgresql/ primary
rserv   

You should now see a copy of all the postgres files on the secondary system in /home/pgsql

If the primary-to-secondary transfer is working, we next need to test the secondary-to-primary transfer.
First, temporarily move the database directory out of the way (we will keep it, just in case) on the primary.



mv /home/pgsql/base /home/pgsql/base.TMP
mkdir /home/pgsql/base
chown postgres:postgres /home/pgsql/base
chmod 700 /home/pgsql/base


Then try the transfer from the secondary by going to the opposite system from the previous test and typing:

Method Command run from
pg_dump pg_dump -b -h 10.0.0.2 (dbname) >; /tmp/db.tar; pg_restore -c -d (dbname) /tmp/db.tar; rm /tmp/db.tar  primary
pg_dumpall pg_dumpall -h 10.0.0.2 >; /tmp/db.dump; pg_restore -f /tmp/db.dump template1; rm /tmp/db.dump  primary
rsync /usr/bin/rsync -auq --delete /home/pgsql/ 10.0.0.1::postgresql/ secondary
rserv   

You should now have a copy of the files in /home/pgsql/base on the primary. If so, you can get rid of the temporary copy:
rm -rf /home/pgsql/base.TMP

The data is transferred between systems by periodically calling a script, db_sync, which does the update:

The rsync version:


#!/bin/sh
# db_sync sync the db with failover copy
# rsync version

# the hb interface of the other side
rhost="hb-pollux"
lockfile="/var/lock/subsys/db_sync"

master=`/sbin/ifconfig | grep "eth0:0" | wc -l`

if [ $master -eq "0" ]; then
# this side not the master
exit 0
fi

# this side is the master

if [ -f $lockfile ]; then
# have not finished since the last time this script was invoked
logger -p daemon.notice -t db_sync "locked, sync already active"
exit 0
fi

touch $lockfile
logger -p daemon.notice -t db_sync "syncing database with $rhost"
sync
/usr/bin/rsync -auq --delete-after /home/pgsql/ $rhost::postgresql/
rm -f $lockfile

exit 0




The pg_dumpall version:


#!/bin/sh
# db_sync sync the db with failover copy
# pg_dumpall version
# the pg_dump version is a simple modification of this version

# the hb interface of the other side
rhost="hb-pollux"
lockfile="/var/lock/subsys/db_sync"

master=`/sbin/ifconfig | grep "eth0:0" | wc -l`

if [ $master -eq "1" ]; then
# this side not the slave
exit 0
fi

# this side is the slave

if [ -f $lockfile ]; then
# have not finished since the last time this script was invoked
logger -p daemon.notice -t db_sync "locked, sync already active"
exit 0
fi

touch $lockfile
logger -p daemon.notice -t db_sync "syncing database with $rhost"
pg_dumpall -U postgres -h $rhost >; /tmp/db.dump$$
pg_restore -U postgres -f /tmp/db.dump$$ template1
rm -f $lockfile /tmp/db.dump$$

exit 0


This script assumes that 'eth0:0' is the HA interface of your cluster. A copy of this script is on BOTH systems (with 'rhost' set appropriately). It should be called periodically by 'cron', e.g.
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/local/sbin/db_sync
in root's crontab file, would cause it to be invoked every 5 minutes. The frequency of this update involves a trade-off. The more often the update is done then the finer the granularity of the mirror copy of the database is, and so there will be less of a data loss when switching over to the secondary. If the synchronization is done too often, then there may be a large volume of network traffic and its possible that the synchronization has not completed when it is time do start another. The script above handles the second case (if you see 'locked, sync already active' messages in the system logs, then you know that there is too much data to transfer in the alloted time).

Edit the postgres init file so that on startup it will do an "pull" FROM the opposite system.
If using rsync, use the command (on castor).
rsync -auq --delete-after hb-pollux::postgresql/ /home/pgsql/
and then start postgresql like normal.
If using the other methods, invoke the db_sync script immediately after postgresql starts up.
This is done so that the primary has a fresh copy of the database in the event that it was down long enough that the secondary copy has substantially changed. For the secondary, it provides the chance to get anything that is new before it takes over. Doing this makes the periodic updates less critical, since that version of the database is only going to be used if the opposite system is unreachable.


Configure heartbeat to manage postgresql from the haresources file, e.g.
dbserver.mydomain.com 192.168.1.110 postgresql
and restart heartbeat.
Now postgresql connections to dbserver.mydomain.com will go to castor if its up and to pollux if its not.


This setup provides basic High Availability for PostgreSQL. It cannot handle problems that will happen to connections that are active DURING the failover; these will just fail. One should be particularly aware of this kind of problem when doing transactions with a COMMIT (i.e. the transaction is set up, a failover happens, and then the commit is executed).
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,8.5折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时8.5折扣期:2019年9月30日前


----------------------------------------

大会官网>>
  

北京盛拓优讯信息技术有限公司. 版权所有 16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122
中国互联网协会会员  联系我们:huangweiwei@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP