学习是一种信仰
分类: Mysql/postgreSQL
2015-09-30 16:58:59
在主库上的操作
1. 初始化数据库
[root@localhost]su – postgres
[postgres@localhost ~]$/usr/local/postgresql-9.4.1/bin/initdb
2.编辑pg_hba.conf添加如下内容
[postgres@localhost ~]$cd /PGDATA/data
[postgres@localhost ~]$vi pg_hba.conf
host replication rep 192.168.xxx.x/32 md5
host all all 192.168.xxx.x/32 md5
注:这里假设备节点是192.168.179.228这台服务器,
数据库名填写的是replication,这并不是表示连接到名称为replication数据库上,而是表示准许replication链接
3.编辑postgresql.conf
如不方便比对可参考《postgres linux安装》
5. 新建数据库,角色
[ ~]$ pg_ctl start
server starting
主节点创建用户
create role rep login replication encrypted password 'rep';
6.新建主节点家目录创建密码文件,创建.pgpass文件
如下
[ ~]$ vi.pgpass
192.168.xxx.x:5432:replication:rep:rep
[ ~]$ chmod 600 .pgpass
在备库上的操作
1.在备节点也创建.pgpass
[ ~]$ cat .pgpass
192.168.xxx.y:5432:replication:rep:rep
[ ~]$ chmod 600 .pgpass
2.备库创建基础备份
[]su – postgres
[postgres@ localhost ~]$ psql -d postgres -c "select pg_start_backup('first_backup');"
[postgres@localhost ~]$ tar --exclude $PGDATA/pg_xlog -cvjpf ./pgbackup.tar.bz2 pgdata
[postgres@localhost ~]$ psql -d postgres -c "select pg_stop_backup();"
3.修改recovery.conf文件(修改为如下形式)
[ ~]$cd /PGDATA/data
[ ~]$vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=rep password=rep host=192.168.xxx.y port=5432'
restore_command = 'cp /PGDATA/archive_log/%f %p'
trigger_file = '/PGDATA/trigger_activestandby'
recovery_target_timeline = 'latest'
4.修改备库的pg_hba.conf文件将备库的ip改为主库的ip
host replication rep 192.168.xxx.y/32 md5
host all rep 192.168.xxx.y/32 md5
5.查看备节点是否正常,并启动备库
[ ~]$ pg_ctl start
server starting
流复制环境验证
1. 查看主库与备库的进程与状态
主节点状态
[ ~]$ pg_controldata
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6176739732962101575
Database cluster state: in production --正在运行,可读写
备节点状态
[ ~]$ pg_controldata
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6176739732962101575
Database cluster state: in archive recovery --正在恢复,只读数据库
2.设置Postgres开机自启动(在主备两个节点上执行)
[ start-scripts]# pwd
/usr/local/src/postgresql-9.4.1/contrib/start-scripts
[ start-scripts]# cp /usr/local/src/postgresql-9.4.1/contrib/start-scripts/linux /etc/init.d/postgresql
[ start-scripts]# cd /etc/init.d/
[ init.d]# ls -l|grep -i postgresql
-rw-r--r-- 1 root root 3437 Jul 29 09:45 postgresql
[ init.d]# chmod +x postgresql
[ init.d]# vi postgresql
#prefix=/usr/local/pgsql
prefix=/usr/local/pgsql --postgresql安装路径
#PGDATA="/usr/local/pgsql/data"
PGDATA=/PGDATA/data --postgresql的数据目录路径
设置postgresql服务开机自启动
[ init.d]# chkconfig --add postgresql
--------以上为流复制环境搭建,下面是流复制切换---------------------------------------------
流复制主备切换
1. 模拟主节点正常关机时的流复制切换
①关闭主节点
[postgres@localhost pg_xlog]$ pg_ctl stop
waiting for server to shut down................... done
server stopped
②将主库的相关日志(pg_xlog,archive,及数据库备份)copy到备库restore_command做指定的位置
[ pg_xlog]$ scp 00*
注作为初次切换需要copy日志文件,当主节点ps –ef|grep postgres 时看不到xxxx.history进程即为初次切换
③激活备节点,备库激活成主库后,把应用改为连接新的主库。
[postgres@localhost data]$ pg_ctl promote
server promoting
注:会使recovery.conf文件变为recovery.done
会在pg_xlog中产生 .history文件
查看原备节点状态会显示需要copy到原主节点的.history文件
[postgres@ localhost pg_xlog]$ ps -ef|grep postgres
postgres 30728 30656 0 15:15 ? 00:00:00 postgres: archiver process last was 00000002.history
④编辑recovery.conf文件(从备库上copy模板)
standby_mode = 'on'
primary_conninfo = 'user=rep password=rep host=192.168.xxx.x port=5432'
restore_command = 'cp /PGDATA/archive_log/%f %p'
trigger_file = '/PGDATA/trigger_activestandby'
recovery_target_timeline = 'latest'
⑤启动原主节点
[postgres@ localhost data]$ pg_ctl start
server starting
[postgres@ localhost data]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "/PGDATA/data/pg_log".