服务器postgresql主备部署
目的:服务器postgresql主备部署实施记录,下次有需要备查。
master主节点
slave1备份节点
1. 安装编译环境
yum -y install autogen cpp- gcc-c++ glibc-devel glibc-headers keyutils krb5-devel libcom_err-devel libmpc libselinux-devel libsepol-devel libstdc++-devel libverto-devel mpfr btp ntpdate openssl pkgconfig tcl zlib readline-devel zlib-devel
2.下载并解压安装文件
cd /opt
tar -zxvf postgresql-10.0.tar.gz
3.进入解压文件,执行编译
cd postgresql-10.0
./configure --prefix=/opt/pg
make
make install
4.创建postgres用户
useradd postgres
passwd postgres # 输入密码,这里记得要记录密码
mkdir /opt/pg/data
mkdir /opt/pg/log
chown -R postgres:postgres /opt/pg
5.配置环境变量
#配置环境变量,向其中追加如下内容:
export PGDATA=/opt/pg/data
export PGHOME=/opt/pg
export PATH=$PGHOME/bin:$PATH
#使得配置生效
source /etc/profile
6.初始化数据库
su - postgres
initdb
cp /opt/postgresql-10.0/contrib/start-scripts/linux /etc/init.d/postgresql
vi /etc/init.d/postgresql
修改内容:
prefix=
PGDATA=
PGUSER=
PGLOG=
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql
service postgresql start
su postgres
psql
\password
这里输入密码
7.关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
8.配置数据库
master部分:
vi /opt/pg/data/pg_hba.conf
#如下:
host all all slave1ip/32 trust #允许连接到主服务器
host replication replica slave1ip/32 md5 #允许使用replica用户来复制
vi /opt/pg/data/postgresql.conf
#如下:
data_directory = '/opt/pg/data' #自定义data目录
listen_addresses = '*' #监听所有ip
archive_mode = on #允许归档
archive_command = 'cp %p /opt/pg/data/pg_archive/%f' #使用命令归档
wal_level = hot_standby #选择热备
max_wal_senders = 16 #最多多少个流复制链接
wal_keep_segments = 256 #流复制保留最多的xlog数
wal_sender_timeout = 60s #流复制主机发送数据超时时间
max_connections = 99 #从库的max_connections必须大于主库的
创建用户replica进行主从同步,并赋予登录和复制的权限
登陆到数据库里:
su postgres
psql
CREATE ROLE replica login replication encrypted password '密码';
防火墙开放5432端口
firewall-cmd –zone=public –add-port=5432/tcp –permanent
登录测试:
在从库上:
psql -h masterip -U postgres
slave1部分:
#自定义目录
mkdir /opt/pg/data/pg_archive/
cd /opt/pg/data/
chmod 700 pg_archive && chown postgres:postgres pg_archive/
#备份数据:
su – postgres
rm -rf /data/pgsql/data/* #先将data目录下的数据都清空
pg_basebackup -P -h masterip -U replica -D /opt/pg/data -X stream
#配置recovery.conf
cp /opt/pg/share/postgresql/recovery.conf.sample /opt/pg/data/recovery.conf
vi /opt/pg/data/recovery.conf
standby_mode = on \#该节点为从
primary_conninfo = 'host=masterip port=5432 user=replica password=replica'
\#主服务器的ip、user
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0'
#配置postgresql.conf
vi /opt/pg/data/postgresql.conf
max_connections = 999 #大于主的连接数
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
重启数据库
9.验证主备结果
9.1查看进程
1)主库sender进程
ps -ef | grep postgres
其中有一行为wal sender process
。
2)从库receiver过程
ps -ef | grep postgres
其中有一行为wal receiver process
。
9.2查看复制状态(主库)
select client_addr,sync_state from pg_stat_replication;
1)建表(主库)
postgres=# create table postgrestb(id int primary key,name VARCHAR(20),salary real);
postgres=# insert into postgrestb values(10, 'Messi', 10000.00);
postgres=# insert into postgrestb values(6, 'Xavi', 10000.00);
postgres=# select * from postgrestb;
2)查询(从库)
su - postgres
psql
\d
select * from postgrestb;