配置主从PostgreSQL数据库

Posted on Edited on In Disqus:

背景

数据库的数据量开始增多，负载开始变高，需要做一个数据库主从配置。

数据量，单表超过了100万条数据，应用程序做读写分离，写到主数据库，读从从数据库。

步骤

  • 生成操作用的用户
  • 配置主数据库，激活备份模式
  • 关停从数据库，复制数据
  • 配置从数据库，从主数据库同步数据

生成操作用的用户

1
2
3
sudo apt-get install postgresql
sudo -s postgres
psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"

配置主数据库，激活备份模式

切换到配置目录

1
cd /etc/postgresql/9.1/main

主从模式

1
2
3
4
5
6
7
8
9
10
cat >> postgresql.conf <<EOF
listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
wal_keep_segments = 32
checkpoint_segments = 8
EOF

wal_keep_segments 1个16MB 32 就是512M，这个参数用来配置保存wal日志的大小。如果备份期间有很多数据库操作导致旧日志失效时，就需要配置更大的数字才能保证同步数据文件。

允许从数据库连接到主数据库

1
2
3
cat >> pg_hba.conf <<EOF
host    replication     rep     IP_address_of_slave/32   md5
EOF

重启数据库

1
service postgresql restart

配置从数据库，从主数据库同步数据

在从数据库机器执行

1
service postgresql stop
1
2
3
4
5
6
cd /var/lib/postgresql/9.1/main
echo > recovery.conf <<EOF
standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'
EOF

复制数据

在主数据库机器执行

1
2
3
psql -c "select pg_start_backup('initial_backup');"
rsync -cva -P --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"

20G 的数据文件大概复制了3个多小时，要看网络状况好不好。

启动从数据库

1
service postgresql start

参看log文件，看是否有报错

1
tail -f /var/log/postgresql/postgresql-9.1-main.log

测试

在主数据库执行

1
2
3
4
5
6
CREATE TABLE rep_test (test varchar(40));
INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');

在从数据库执行

1
SELECT * FROM rep_test;

查看数据有无同步

验证从数据库的只读属性

1
INSERT INTO rep_test VALUES ('oops');
1
ERROR:  cannot execute INSERT in a read-only transaction

查看同步状态

在主数据库中执行

1
select * from pg_stat_replication;

延伸阅读

官网文档, Ubuntu 12.04, 从0配置主从, 主从切换