postgresql安装hot_standby

1、Postgresql （主从）hot_standby安装说明一、搭建PT备份数据库1 配置环境变量vi /etc/sysctl.confkernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048

2、576net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_max_syn_backlog = 4096dev_max_backlog = 10000vm.overcommit_memory = 0net.ipv4.ip_conntrack_max = 655360fs.aio-max-nr = 1048576net.ipv4.tcp_timestamps = 0使文件修改生效sysctl -pvi /etc/security/limits.conf* soft nofile 131072* hard nofile 131072* soft nproc 131072*

3、 hard nproc 131072* soft core unlimited* hard core unlimited* soft memlock 50000000* hard memlock 500000002 建立数据库用户给两个节点分别创建postgres用户并设置密码mkdir /home/postgreschown -R /home/postgresgroupadd postgresuseradd -u 501 -g postgres -d /home/postgres -s /bin/bash postgrespasswd postgres3 ssh免密钥通信配置两台机器的ssh

4、免密钥登录ssh-keygen -t rsa主机：cat /.ssh/id_rsa.pub /.ssh/authorized_keyschmod go-rwx /.ssh/*cd /.sshscp id_rsa.pub postgres57:/home/postgres/.ssh/id_rsa.pub1备机：cd /.sshcat id_rsa.pub1authorized_keyschmod go-rwx /.ssh/*scp id_rsa.pub postgres56:/home/postgres/.ssh/id_rsa.pub2主机：cat

5、 id_rsa.pub2authorized_keys备机：cat id_rsa.pub1authorized_keys4 安装PT数据库软件安装PG的依赖软件包：zypper install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison创建源码包存储目录mkdir /app/databasemkdir /app/pgs

6、qlchown -R postgres:users /app/databasechown -R postgres:users /app/pgsqlsu - postgrespostgresql必须用postgres 用户安装，安装之前先改变postgresql的将要安装的目录的权限安装PostgreSQLwget /pub/source/v9.5.3/postgresql-9.5.3.tar.gzcd /app/database tar zxvf postgresql-9.5.3.tar.gzcd postgresql-9.5.3./confi

7、gure -prefix=/app/pgsql -with-pgport=5432 -with-perl -with-python -with-openssl -with-pam -without-ldap -with-libxml -with-libxslt -with-blocksize=8 gmake worldgmake install-world主备节点设置相关环境变量(主备节点应该一致)vi /home/postgres/.bash_profileexport PGHOME=/app/pgsqlexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/u

8、sr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=date +%Y%m%d%H%Mexport PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATH备机安装pg_standby进入编译后的/postgresql-9.5.3/contrib./pg_standby目录，执行以下命令make install这时便安装好pg_standby。5 主机应用配置主机初始化数据库mkdir -pv /app/DBc

9、hown -R postgres:users /app/DBcd /app/pgsql/bin./initdb -D /app/DB 备库(postgres用户安装 备节点只需要安装软件，不需要init数据库)配置监听地址和端口：vi postgresql.conflisten_addresses = *port = 5432wal_level = hot_standbymax_wal_senders = 1允许远程连接：vi pg_hba.conf添加host all all /24 trusthost replication postgres /0

10、 trust主机设定WAL归档vi postgres.conf设定如下参数：archive_mode = onarchive_command = scp %p 57:/app/pgsql/backup/standby_log/%f standby.logrecovery_end_command = rm -f /tmp/pgsql.trigger.5432/tmp目录下创建pgsql.trigger.5432文件，停止备份后会删除该文件防止错误操作7 测试1) 主机登录standby数据库，创建表并插入相应数据psql postgrescreate table tb(a

11、 int);insert into tb(a) values(1);insert into tb(a) values(2);insert into tb(a) values(3);2) 备机查看日志，确定主机刚发送过来的WAL归档日志已经被应用到备机上。3) 登录备机standby数据库，查看数据是否恢复psql postgresselect * from tb;a-1238 实现主备切换（热备）关闭主库(on Primary)postgrespg1 pg_root$ pg_ctl stop -m fast -D $PGDATAwaiting for server to shut down.

12、doneserver stopped激活备库到主库状态 ( on slave )激活备库只要创建一个文件即可，根据备库 recovery.conf 配置文件的参数 trigger_file 值，创建这个 trigger 文件即可。 例如 touch /opt/pgdata/pg_root/postgresql.trigger.1921 postgrespgb pg_root$ touch /opt/pgdata/pg_root/postgresql.trigger.1921过一会儿发现 recovery.conf 文件变成 recovery.done ，说明备库已经激活。创建监测自动激活脚本v

13、i standbydog.sh#!/bin/shcheck_process() RET=ps ax | grep $1 | grep -v grep | wc -l if $RET -eq 0 ; then return 1; else return 0; ficheck_process walif $? -ne 0 ;then touch /app/DB/pgsql.trigger.5432else exitfiexit 0激活原来的主库，让其转变成从库(在原来的主库上执行) -创建 $PGDATA/recovery.conf 文件，配置以下参数recovery_target_timelin

14、e = lateststandby_mode = on -标记PG为STANDBY SERVERprimary_conninfo = host=57 port=5432 user=postgres -标识主库信息trigger_file = /app/DB/pgsql.trigger.5432 -标识触发器文件-将原来的主库（现在的从库）启动postgrespg1 pg_root$ pg_ctl start -D $PGDATAserver starting-查看从库日志， 发现大量 FATAL 错误信息2011-08-24 21:31:59.178 CST,17889

15、,4e54fd4f.45e1,1,2011-08-24 21:31:59 CST,0,FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5,2011-08-24 21:32:04.208 CST,17891,4e54fd54.45e3,1,2011-08-24 21:32:04 CST,0,FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5,2011-08-24 21:32:09.1

16、35 CST,17892,4e54fd59.45e4,1,2011-08-24 21:32:09 CST,0,FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5,2011-08-24 21:32:14.136 CST,17895,4e54fd5e.45e7,1,2011-08-24 21:32:14 CST,0,FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5, 备注：出现了大量

17、 FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5” 估计是时间线有问题，网上查了下资料也没啥结果，后来咨询了下德哥，只要将从库 $PGDATA/pg_xlog 一个文件考过来就行。 -将主库文件 00000006.history 复制到从库postgrespgb pg_xlog$ scp 00000006.history postgres5:/opt/pgdata/pg_root/pg_xlogpostgres5s password: 00

18、000006.history -再次查看从库日志2011-08-24 21:36:04.819 CST,17948,4e54fe44.461c,1,2011-08-24 21:36:04 CST,0,FATAL,XX000,timeline 6 of the primary does not match recovery target timeline 5,2011-08-24 21:36:09.742 CST,17885,4e54fd44.45dd,5,2011-08-24 21:31:48 CST,1/0,0,LOG,00000,new target timeline is 6,2011-

19、08-24 21:36:09.824 CST,17977,4e54fe49.4639,1,2011-08-24 21:36:09 CST,0,LOG,00000,streaming replication successfully connected to primary, 备注：根据日志信息，说明从库已经恢复正常；9 共享存储使用应用环境：两台主机共享存储，非集群负载1、 创建角色postgres=# create role TestRole2;postgres=# select rolname from pg_roles;2、 创建用户postgres=# create user Test

20、User2 with password TestUser2 login in role TestRole2;3、 创建表空间postgres=# create tablespace TestDbs2 location E:PostgreSQLdataTestDbs2;CREATEDATABASEdbnameOWNERkanonTEMPLATEtemplate1TABLESPACEtablespacename;4、 创建数据库postgres=# create database TestDb2;建议使用pgadmin3工具创建10 清理pg_xlog日志postgrestest01- pg_co