psql主从复制_sql

概述curl -O http://yum.postgresql.org/9.1/redhat/rhel-6.3-x86_64/pgdg-centos91-9.1-4.noarch.rpm rpm -ivh pgdg-centos91-9.1-4.noarch.rpm yum install postgresql91 postgresql91-devel postgresql91-server post

curl -O http://yum.postgresql.org/9.1/redhat/rhel-6.3-x86_64/pgdg-centos91-9.1-4.noarch.rpm

rpm -ivh pgdg-centos91-9.1-4.noarch.rpm
yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib
初始化并开启数据库
service postgresql-9.1 initdb
service postgresql-9.1 start

vim /etc/profile
PGDATA=/var/lib/pgsql/9.1/data
export PGDATA
PATH=$PATH:$HOME/bin:/usr/pgsql-9.1/bin
PATH

设置postgres密码
su - postgres
psql postgres postgres
alter user postgres with password '123.com';

Configure Postgresql 9 pg_hba.conf file
添加自己想要使用的ip地址和权限

local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5

Configure Remote Access for Postgresql 9

Listen_addresses = '*' # what IP address(es) to Listen on; # comma-separated List of addresses; # defaults to 'localhost','*' = all # (change requires restart) #port = 5432 # (change requires restart)

配置postgresql的流复制主从切换
vim postgressql.conf

#Listen_addresses = 'localhost' # what IP address(es) to Listen on;
Listen_addresses = '*'

#wal_level = minimal # minimal,archive,or hot_standby
wal_level = hot_standby

#max_wal_senders = 0
max_wal_senders = 3
创建用于replication的用户
psql -h localhost -U postgres -W -c "CREATE USER ruser WITH REPliCATION PASSWORD 'password';"
vim pg_hba.conf
host replication ruser 192.168.0.2/32 md5

/etc/init.d/postgresql stop
配置从节点
vim postgresql.conf
#Listen_addresses = 'localhost' # what IP address(es) to Listen on;
Listen_addresses = '*'

#hot_standby = off
hot_standby = on
删除从节点数据目录的所有文件
#/etc/init.d/postgresql stop
#cd /var/lib/postgresql/9.1/main/
#rm -rf *
配置recovery.conf文件
vim recovery.conf
primary_conninfo = 'host=192.168.0.1 port=5432 user=ruser password=password'
standby_mode = on
copy主数据目录文件到从数据目录
rsync -av /var/lib/postgresql/9.1/main/* 192.168.0.2:/var/lib/postgresql/9.1/main/
启动主从数据库
测试：

$ su postgres
$ createdb sampledb
psql -d sampledb

CREATE table samples (
ID integerPRIMARYKEY,
name varchar(25),
stuff text
);

INSERTinto samples values (1'foo''lots of foo');
2'bar''lots of bar'3'baz''lots of baz'4'bin''lots of bin');

查看复制情况
psql -h localhost -U postgres -W -c "select * from pg_stat_replication;"