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.pub1>>authorized_keyschmod go-rwx /.ssh/*scp id_rsa.pub postgres56:/home/postgres/.ssh/

5、id_rsa.pub2主机：cat id_rsa.pub2>>authorized_keys备机：cat id_rsa.pub1>>authorized_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创建源码包存储目录

6、mkdir /app/databasemkdir /app/pgsqlchown -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

7、.tar.gzcd postgresql-9.5.3./configure -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_L

8、IBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=date +"%Y%m%d%H%M"export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATH备机安装pg_standby进入编译后的/postgresql-9.5.3/contrib./pg_standby目录，执行以下命令make install这时便安装好

9、pg_standby。5 主机应用配置主机初始化数据库mkdir -pv /app/DBchown -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 192

10、.168.11.0/24 trusthost replication postgres /0 trust主机设定WAL归档vi postgres.conf设定如下参数：archive_mode = onarchive_command = 'scp %p 57:/app/pgsql/backup/standby_log/%f </dev/null'archive_timeout = 1200hot_standby = onarchive_mode参数，开启WAL归档archive_command参数，设定归档时执行的命令，我们这里通

11、过scp把主机的WAL归档日志传送到备机的/app/pgsql/backup/standby_log/目录下archive_timeout参数，规定了最多多久执行一次归档，也是发生故障时数据库丢失数据的最长时间。设定很小的话保证了安全性，增加了网络传输量。 postgresql数据库随系统启动而启动将启动脚本拷贝到/etc/init.d/目录下，具体执行如下命令：cd /etc/init.dcp (第一步解压的安装文件目录) /contrib/start-scripts/linux postgresqlchmod +x postgresqlvi postgresqlprefix=/a

12、pp/pgsqlPGDATA="/app/DB"PGUSER=postgresPGLOG="/app/DB/log/pgsql.log"chkconfig -add postgresql启动数据库：mkdir /app/DB/logservice postgresql start6 备机应用配置建立备机数据库主机开始备份psql postgres（实例名）select pg_start_backup('hotbak');主机压缩备份整个data目录mkdir /app/pgsql/backuptar -jcv -f /app/pgsql/

13、backup/baseline.tar.bz2 /app/DB/主机通过scp复制到备机scp -v /app/pgsql/backup/baseline.tar.bz2 57:/app/pgsql/backup/baseline.tar.bz2备机解压缩备份文件sudo tar -jxv -f baseline.tar.bz2 -C /主机结束备份psql postgresselect pg_stop_backup();备机进行设置，启动到恢复模式在/app/DB/目录内建立recovery.conf文件cp /app/pgsql/share/recovery.con

14、f.sample /app/DB/recovery.confvi recovery.confstandby_mode = 'on'primary_conninfo = 'host=56 port=5432 user=postgres'trigger_file = '/app/DB/pgsql.trigger.5432'vi postgres.confhot_standby = on#删除pid文件rm postmaster.pid更改postgres.conf文件中备份设置把archive_mode、archive_com

15、mand、archive_timeout三个参数注释掉，这些参数是主机设置的，由于我们备机暂时不需归档，所以注释掉他。 启动备机postgresql数据库建立postgresql服务启动数据库pg_ctl D /app/DB start备机会一直读取主机传送的WAL归档日志进行恢复- -另一归档方法（未验证） -cp /app/pgsql/share/postgresql/recovery.conf.sample /app/DB/recovery.confvi recovery.confrestore_command = 'pg_standby -d -s 2 -t /tmp

16、/pgsql.trigger.5432 /usr/local/pgsql/backup/standby_log %f %p %r 2>>standby.log'recovery_end_command = 'rm -f /tmp/pgsql.trigger.5432'/tmp目录下创建pgsql.trigger.5432文件，停止备份后会删除该文件防止错误操作7 测试1) 主机登录standby数据库，创建表并插入相应数据psql postgrescreate table tb(a int);insert into tb(a) values(1);inser

17、t 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. doneserver stopped激活备库到主库状态 ( on

18、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 ，说明备库已经激活。创建监测自动激活脚本vi standbydog.sh#!/bin

19、/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_timeline = '

20、latest'standby_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 $PGDA

21、TAserver starting-查看从库日志， 发现大量 FATAL 错误信息2011-08-24 21:31:59.178 CST,17889,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,FAT

22、AL,XX000,"timeline 6 of the primary does not match recovery target timeline 5",""2011-08-24 21:32:09.135 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

23、.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",""   备注：出现了大量 FATAL,XX000,"timeline 6 of the primary does not match recovery target timeline 5”     &

24、#160;       估计是时间线有问题，网上查了下资料也没啥结果，后来咨询了下德哥，只要将从库 $PGDATA/pg_xlog               一个文件考过来就行。         -将主库文件 00000006.history 复制到从库postgrespgb pg_xlog$ scp 00000006.his

25、tory postgres5:/opt/pgdata/pg_root/pg_xlogpostgres5's password: 00000006.history                 -再次查看从库日志2011-08-24 21:36:04.819 CST,17948,4e54fe44.461c,1,2011-08-24 21:36:04 CST,0,FATAL,XX000,

26、"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-08-24 21:36:09.824 CST,17977,4e54fe49.4639,1,2011-08-24 21:36:09

27、 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 "TestUser2" with

28、password 'TestUser2' login in role "TestRole2"3、 创建表空间postgres=# create tablespace "TestDbs2" location 'E:PostgreSQLdataTestDbs2'CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;4、 创建数据库postgre

29、s=# create database "TestDb2"建议使用pgadmin3工具创建10 清理pg_xlog日志postgrestest01-> pg_controldatapg_control version number:            922Catalog version number:               201204301Database system identifier:           5849803358968196539Database cluster state: