psql主从复制

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地址和权限

1. local all all md5
2. # IPv4 local connections:
3. host all all 127.0.0.1/32 md5
4. # IPv6 local connections:
5. host all all ::1/128 md5

Configure Remote Access for PostgreSQL 9

2. listen_addresses = '*' # what IP address(es) to listen on;
3. # comma-separated list of addresses;
4. # defaults to 'localhost','*' = all
5. # (change requires restart)
6. #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;"

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!