postgresql安装hotstandby.docx
- 文档编号:10439437
- 上传时间:2023-02-11
- 格式:DOCX
- 页数:13
- 大小:19.62KB
postgresql安装hotstandby.docx
《postgresql安装hotstandby.docx》由会员分享,可在线阅读,更多相关《postgresql安装hotstandby.docx(13页珍藏版)》请在冰豆网上搜索。
postgresql安装hotstandby
Postgresql(主从)
hot_standby安装说明
一、搭建PT备份数据库
1配置环境变量
vi/etc/sysctl.conf
kernel.shmmni=4096
kernel.sem=5010064128000501001280
fs.file-max=7672460
net.ipv4.ip_local_port_range=900065000
net.core.rmem_default=1048576
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_max_syn_backlog=4096
dev_max_backlog=10000
vm.overcommit_memory=0
net.ipv4.ip_conntrack_max=655360
fs.aio-max-nr=1048576
net.ipv4.tcp_timestamps=0
使文件修改生效
sysctl-p
vi/etc/security/limits.conf
*softnofile131072
*hardnofile131072
*softnproc131072
*hardnproc131072
*softcoreunlimited
*hardcoreunlimited
*softmemlock50000000
*hardmemlock50000000
2建立数据库用户
给两个节点分别创建postgres用户并设置密码
mkdir/home/postgres
chown-R/home/postgres
groupaddpostgres
useradd-u501-gpostgres-d/home/postgres-s/bin/bashpostgres
passwdpostgres
3ssh免密钥通信
配置两台机器的ssh免密钥登录
ssh-keygen-trsa
主机:
cat~/.ssh/id_rsa.pub>>~/.ssh/authorized_keys
chmodgo-rwx~/.ssh/*
cd~/.ssh
scpid_rsa.pubpostgres@192.168.11.157:
/home/postgres/.ssh/id_rsa.pub1
备机:
cd~/.ssh
catid_rsa.pub1>>authorized_keys
chmodgo-rwx~/.ssh/*
scpid_rsa.pubpostgres@192.168.11.156:
/home/postgres/.ssh/id_rsa.pub2
主机:
catid_rsa.pub2>>authorized_keys
备机:
catid_rsa.pub1>>authorized_keys
4安装PT数据库软件
安装PG的依赖软件包:
zypperinstalllrzszsysstate4fsprogsntpreadline-develzlibzlib-developensslopenssl-develpam-devellibxml2-devellibxslt-develpython-develtcl-develgccmakeflexbison
创建源码包存储目录
mkdir/app/database
mkdir/app/pgsql
chown-Rpostgres:
users/app/database
chown-Rpostgres:
users/app/pgsql
su-postgres
postgresql必须用postgres用户安装,安装之前先改变postgresql的将要安装的目录的权限
安装PostgreSQL
wgethttps:
//ftp.postgresql.org/pub/source/v9.5.3/postgresql-9.5.3.tar.gz
cd/app/database
tarzxvfpostgresql-9.5.3.tar.gz
cdpostgresql-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
gmakeworld
gmakeinstall-world
主备节点设置相关环境变量(主备节点应该一致)
vi/home/postgres/.bash_profile
exportPGHOME=/app/pgsql
exportLD_LIBRARY_PATH=$PGHOME/lib:
/lib64:
/usr/lib64:
/usr/local/lib64:
/lib:
/usr/lib:
/usr/local/lib:
$LD_LIBRARY_PATH
exportDATE=`date+"%Y%m%d%H%M"`
exportPATH=$PGHOME/bin:
$PATH:
.
exportMANPATH=$PGHOME/share/man:
$MANPATH
备机安装pg_standby
进入编译后的/postgresql-9.5.3/contrib./pg_standby目录,执行以下命令
makeinstall
这时便安装好pg_standby。
5主机应用配置
主机初始化数据库
mkdir-pv/app/DB
chown-Rpostgres:
users/app/DB
cd/app/pgsql/bin
./initdb-D/app/DB
备库(postgres用户安装备节点只需要安装软件,不需要init数据库)
配置监听地址和端口:
vipostgresql.conf
listen_addresses='*'
port=5432
wal_level=hot_standby
max_wal_senders=1
允许远程连接:
vipg_hba.conf
添加
hostallall192.168.11.0/24trust
hostreplicationpostgres127.0.0.1/0trust
主机设定WAL归档
vipostgres.conf
设定如下参数:
archive_mode=on
archive_command='scp%p192.168.11.157:
/app/pgsql/backup/standby_log/%f
archive_timeout=1200
hot_standby=on
archive_mode参数,开启WAL归档
archive_command参数,设定归档时执行的命令,我们这里通过scp把主机的WAL归档日志传送到备机的/app/pgsql/backup/standby_log/目录下
archive_timeout参数,规定了最多多久执行一次归档,也是发生故障时数据库丢失数据的最长时间。
设定很小的话保证了安全性,增加了网络传输量。
postgresql数据库随系统启动而启动
将启动脚本拷贝到/etc/init.d/目录下,具体执行如下命令:
cd/etc/init.d
cp(第一步解压的安装文件目录)/contrib/start-scripts/linuxpostgresql
chmod+xpostgresql
vipostgresql
prefix=/app/pgsql
PGDATA="/app/DB"
PGUSER=postgres
PGLOG="/app/DB/log/pgsql.log"
chkconfig--addpostgresql
启动数据库:
mkdir/app/DB/log
servicepostgresqlstart
6备机应用配置
建立备机数据库
主机开始备份
psqlpostgres(实例名)
selectpg_start_backup('hotbak');
主机压缩备份整个data目录
mkdir/app/pgsql/backup
tar-jcv-f/app/pgsql/backup/baseline.tar.bz2/app/DB/
主机通过scp复制到备机
scp-v/app/pgsql/backup/baseline.tar.bz2192.168.11.157:
/app/pgsql/backup/baseline.tar.bz2
备机解压缩备份文件
sudotar-jxv-fbaseline.tar.bz2-C/
主机结束备份
psqlpostgres
selectpg_stop_backup();
备机进行设置,启动到恢复模式
在/app/DB/目录内建立recovery.conf文件
cp/app/pgsql/share/recovery.conf.sample/app/DB/recovery.conf
virecovery.conf
standby_mode='on'
primary_conninfo='host=192.168.11.156port=5432user=postgres'
trigger_file='/app/DB/pgsql.trigger.5432'
vipostgres.conf
hot_standby=on
#删除pid文件
rmpostmaster.pid
更改postgres.conf文件中备份设置
把archive_mode、archive_command、archive_timeout三个参数注释掉,这些参数是主机设置的,由于我们备机暂时不需归档,所以注释掉他。
启动备机postgresql数据库
建立postgresql服务
启动数据库
pg_ctl–D/app/DBstart
备机会一直读取主机传送的WAL归档日志进行恢复
---------------另一归档方法(未验证)----------------
cp/app/pgsql/share/postgresql/recovery.conf.sample/app/DB/recovery.conf
virecovery.conf
restore_command='pg_standby-d-s2-t/tmp/pgsql.trigger.5432/usr/local/pgsql/backup/standby_log%f%p%r2>>standby.log'
recovery_end_command='rm-f/tmp/pgsql.trigger.5432'
/tmp目录下创建pgsql.trigger.5432文件,停止备份后会删除该文件防止错误操作
7测试
1)主机登录standby数据库,创建表并插入相应数据
psqlpostgres
createtabletb(aint);
insertintotb(a)values
(1);
insertintotb(a)values
(2);
insertintotb(a)values(3);
2)备机查看日志,确定主机刚发送过来的WAL归档日志已经被应用到备机上。
3)登录备机standby数据库,查看数据是否恢复
psqlpostgres
select*fromtb;
a
---
1
2
3
8实现主备切换(热备)
关闭主库(onPrimary)
[postgres@pg1pg_root]$pg_ctlstop-mfast-D$PGDATA
waitingforservertoshutdown.......done
serverstopped
激活备库到主库状态(onslave)
激活备库只要创建一个文件即可,根据备库recovery.conf配置文件的参数trigger_file值,
创建这个trigger文件即可。
例如"touch/opt/pgdata/pg_root/postgresql.trigger.1921"
[postgres@pgbpg_root]$touch/opt/pgdata/pg_root/postgresql.trigger.1921
过一会儿发现recovery.conf文件变成recovery.done,说明备库已经激活。
创建监测自动激活脚本
vistandbydog.sh
#!
/bin/sh
check_process()
{
RET=`psax|grep$1|grep-v"grep"|wc-l`
if[$RET-eq0];then
return1;
else
return0;
fi
}
check_processwal
if[$?
-ne0];then
touch/app/DB/pgsql.trigger.5432
else
exit
fi
exit0
激活原来的主库,让其转变成从库(在原来的主库上执行)
--创建$PGDATA/recovery.conf文件,配置以下参数
recovery_target_timeline='latest'
standby_mode='on' --标记PG为STANDBYSERVER
primary_conninfo='host=192.168.11.157port=5432user=postgres' --标识主库信息
trigger_file='/app/DB/pgsql.trigger.5432' --标识触发器文件
--将原来的主库(现在的从库)启动
[postgres@pg1pg_root]$pg_ctlstart-D$PGDATA
serverstarting
--查看从库日志,发现大量FATAL错误信息
2011-08-2421:
31:
59.178CST,,,17889,,4e54fd4f.45e1,1,,2011-08-2421:
31:
59CST,,0,FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5",,,,,,,,,""
2011-08-2421:
32:
04.208CST,,,17891,,4e54fd54.45e3,1,,2011-08-2421:
32:
04CST,,0,FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5",,,,,,,,,""
2011-08-2421:
32:
09.135CST,,,17892,,4e54fd59.45e4,1,,2011-08-2421:
32:
09CST,,0,FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5",,,,,,,,,""
2011-08-2421:
32:
14.136CST,,,17895,,4e54fd5e.45e7,1,,2011-08-2421:
32:
14CST,,0,FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5",,,,,,,,,""
备注:
出现了大量FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5”
估计是时间线有问题,网上查了下资料也没啥结果,后来咨询了下德哥,只要将从库$PGDATA/pg_xlog
一个文件考过来就行。
--将主库文件00000006.history复制到从库
[postgres@pgbpg_xlog]$scp00000006.historypostgres@192.168.1.25:
/opt/pgdata/pg_root/pg_xlog
postgres@192.168.1.25'spassword:
00000006.history
--再次查看从库日志
2011-08-2421:
36:
04.819CST,,,17948,,4e54fe44.461c,1,,2011-08-2421:
36:
04CST,,0,FATAL,XX000,"timeline6oftheprimarydoesnotmatchrecoverytargettimeline5",,,,,,,,,""
2011-08-2421:
36:
09.742CST,,,17885,,4e54fd44.45dd,5,,2011-08-2421:
31:
48CST,1/0,0,LOG,00000,"newtargettimelineis6",,,,,,,,,""
2011-08-2421:
36:
09.824CST,,,17977,,4e54fe49.4639,1,,2011-08-2421:
36:
09CST,,0,LOG,00000,"streamingreplicationsuccessfullyconnectedtoprimary",,,,,,,,,""
备注:
根据日志信息,说明从库已经恢复正常;
9共享存储使用
应用环境:
两台主机共享存储,非集群负载
1、创建角色
postgres=#createrole"TestRole2";
postgres=#selectrolnamefrompg_roles;
2、创建用户
postgres=#createuser"TestUser2"withpassword'TestUser2'logininrole"TestRole2";
3、创建表空间
postgres=#createtablespace"TestDbs2"location'E:
\PostgreSQL\data\TestDbs2';
CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;
4、创建数据库
postgres=#createdatabase"TestDb2";
建议使用pgadmin3工具创建
10清理pg_xlog日志
postgres@test01->pg_controldata
pg_controlversionnumber:
922
Catalogversionnumber:
201204301
Databasesystemidentifier:
5849803358968196539
Databaseclusterstate:
inproduction
pg_controllastmodified:
Thu07Mar201302:
37:
23PMCST
Latestcheckpointlocation:
D/A80000F0
Priorcheckpointlocation:
D/A8000058
Latestcheckpoint'sREDOlocation:
D/A80000B8
Latestcheckpoint'sTimeLineID:
3
Latestcheckpoint'sfull_page_writes:
on
Latestcheckpoint'sNextXID:
0/1745
Latestcheckpoint'sNextOID:
24584
Latestcheckpoint'sNextMultiXactId:
1
Latestcheckpoint'sNextMultiOffset:
0
pg_ctlstop-D/data/pgsql
pg_resetxlog-o 24584-x1745-f/data/pgsql
备份到从机
cd/data/pgsql
rm–rf*
scp-v-rservername:
/data//data/pgsql
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- postgresql 安装 hotstandby