postgres安装和主从复制
------------------------------------------------------------------------
一、操作环境
采用VMware ESXi虚拟出的2台服务器:
host1:psql_master,172.16.101.220
host2:psql_standby,172.16.101.221
二.安装
master和slave 安装postgres
yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install -y postgresql96
yum install -y postgresql96-server
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6
----------------------------------------------------------
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql96-server
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
sudo systemctl enable postgresql-9.6
sudo systemctl start postgresql-9.6
三.配置使用
1. 修改用户密码
su - postgres
-bash-4.2$ psql -U postgres
#进入数据库后修改密码;
postgres=# alter user postgres with password 'postgres@123'
2.允许远程访问
[root@psql_master ~]# sed -i "s|#listen_addresses = 'localhost'|listen_addresses = '*'|g" /var/lib/pgsql/9.6/data/postgresql.conf
3、主机认证
vim /var/lib/pgsql/9.6/data/pg_hba.conf
4. 设置环境变量
[root@psql_master ~]# vim /etc/profile
export PATH=$PATH:/usr/pgsql-9.6/bin
[root@psql_master ~]# source /etc/profile
5. 重启服务
[root@psql_master ~]# systemctl restart postgresql-9.6
6、验证
[root@psql_master ~]# netstat -tunlp
四、配置主从(主库配置)
1. 创建复制用户
#需要一个账号进行主从同步
su - postgres
psql -U postgres
postgres=#create role repl login replication encrypted password 'repl@123';
2. 认证文件pg_hba.conf
#配置从库可以采用repl账号进行同步
[root@psql_master ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf
host replication repl 172.16.101.221/24 md5
3、主库配置文件postgresql.conf
#监听端口
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2
wal_keep_segments = 64
max_connections = 100
4. 重启服务
#同时注意打开防火墙端口打开
systemctl restart postgresql-9.6
五、从库配置
从库安装postgresql后,暂不初始化,如果从库已初始化,可以清空其data目录(默认安装是/var/lib/pgsql/9.6/data/目录)。
1. 基础备份
[root@psql_standby ~]# pg_basebackup -h 10.11.4.186 -p 5432 -U repl -F p -P -D /var/lib/pgsql/9.6/data/
2. 备份目录权限
#基于root账号做的基础备份,需要将相关目录文件的权限变更
[root@psql_standby ~]# chown -R postgres:postgres /var/lib/pgsql/9.6/data/
3. 从库配置文件postgresql.conf
#在基础备份时,初始化文件是从主库复制来的,所以配置文件一致,可将wal_level,max_wal_senders与wal_keep_segments等参数注释,以下是新增或修改的参数
[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
#在备份的同时允许查询
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
#默认参数,非主从配置相关参数,表示到数据库的连接数,一般从库做主要的读服务时,设置值需要高于主库
max_connections = 1000
4. 恢复文件recovery.conf
#在做基础备份时,也可通过-R参数在备份结束后自动生产一个recovery.conf文件
[root@psql_standby ~]# cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf
[root@psql_standby ~]# chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf
[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.101.220 port=5432 user=repl password=repl@123'
5. 设置环境变量 (很重要)
[root@psql_master ~]# vim /etc/profile
export PATH=$PATH:/usr/pgsql-9.6/bin
[root@psql_master ~]# source /etc/profile
6. 重启服务
[root@psql_standby ~]# systemctl restart postgresql-9.6
五.使用验证
1)主库sender进程
[root@psql_master ~]# ps -ef | grep postgres
2)从库receiver过程
[root@psql_standby ~]# ps -ef | grep postgres
[root@psql_standby ~]# su - postgres
-bash-4.2$ psql
postgres=# \x
postgres=# select * from pg_stat_replication;
3. 表复制测试
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)查询(从库)
[root@psql_standby ~]# su - postgres
-bash-4.2$ psql
postgres=# \d
postgres=# select * from postgrestb;
3)从库写测试
#从库只读,不能写入数据
postgres=# insert into postgrestb values(8, 'Iniesta', 10000.00);
4)激活从库
#采用”pg_ctl promote“切换从库为主库;
#切换后,从库的recovery.conf文件名字变成了recovery.done
[root@psql_standby ~]# su - postgres -c "pg_ctl promote"
server promoting
切换后,从库的recovery.conf文件名字变成了recovery.done
5)查看从库日志,状态与进程
[root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log
[root@localhost ~]# pg_controldata /var/lib/pgsql/9.6/data/
6、主库启动,由从库转换到主库。
启动主库
[root@localhost ~]# su - postgres -c "pg_ctl start -m fast"
server starting
< 2018-08-28 09:42:46.745 CST > LOG: redirecting log output to logging collector process
< 2018-08-28 09:42:46.745 CST > HINT: Future log output will appear in directory "pg_log".
主库的状态
切换到从库
首先关闭从库
[root@localhost data]# su - postgres -c "pg_ctl stop -m fast"
waiting for server to shut down.... done
server stopped
#手动更改从库的recovery.done文件名字变成了recovery.conf
[root@psql_standby data]# mv recovery.done recovery.conf
[root@psql_standby data]# su - postgres -c "pg_ctl start -m fast"
server starting