如何在CentOS中安装配置和使用PostgreSQL
如何在CentOS中安装配置和使用PostgreSQL?针对这个问题,今天小编总结这篇有关PostgreSQL的文章,可供感兴趣的小伙伴们参考借鉴,希望对大家有所帮助。
创新互联建站是一家专业提供无极企业网站建设,专注与网站制作、成都网站建设、HTML5建站、小程序制作等业务。10年已为无极众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。
1、环境
操作系统版本:CentOS Linux release 8.0.1905 (Core)
PostgreSQL版本:10.6
主机:
test1 192.168.1.11
test2 192.168.1.12
test3 192.168.1.13
2、在3台机器安装并初始化PostgreSQL
[root@test1 ~]# yum install postgresql-server -y
[root@test1 ~]# postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
3、主库配置
编辑主库配置文件
[root@test1 ~]# vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '192.168.1.11'
wal_log_hints = on
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'
配置认证文件
[root@test1 ~]# vim /var/lib/pgsql/data/pg_hba.conf
#追加三行
host replication replica 192.168.1.11/32 md5
host replication replica 192.168.1.12/32 md5
host replication replica 192.168.1.13/32 md5
创建pg_archive目录
[root@test1 ~]# mkdir -p /var/lib/pgsql/pg_archive
[root@test1 ~]# chown postgres:postgres /var/lib/pgsql/pg_archive
配置recovery.conf
[root@test1 ~]# vim /var/lib/pgsql/data/recovery.bak
standby_mode = on
primary_conninfo = 'host=192.168.1.11 port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'
[root@test1 ~]# chown postgres:postgres /var/lib/pgsql/data/recovery.bak
新建pgpass文件
[postgres@test1 ~]$ vim ~/.pgpass
192.168.1.11:5432:replication:replica:replica
192.168.1.12:5432:replication:replica:replica
192.168.1.13:5432:replication:replica:replica
[root@test1 ~]# chown 600 /var/lib/pgsql/.pgpass
启动数据库,关闭服务
[root@test1 ~]# systemctl start postgresql
[root@test1 ~]# systemctl stop firewalld.service
创建同步用户
[root@test3 ~]# su - postgres
[postgres@test3 ~]$ psql
psql (10.6)
Type "help" for help.
postgres=# create role replica login replication encrypted password 'replica';
CREATE ROLE
postgres=# \q
[postgres@test1 ~]$
4、配置两台从库
从主库复制备份过来
[root@test2 ~]# rm -rf /var/lib/pgsql/data/*
[root@test2 ~]# pg_basebackup -h 192.168.1.11 -p 5432 -U replica -F p -P -D /var/lib/pgsql/data/
Password: replica
22797/22797 kB (100%), 1/1 tablespace
[root@test2 ~]# chown postgres:postgres -R /var/lib/pgsql/data
重命名recovery配置文件
[root@test2 ~]# mv /var/lib/pgsql/data/recovery.bak /var/lib/pgsql/data/recovery.conf
新建pgpass文件
[root@test2 ~]# su - postgres
[postgres@test2 ~]$ vim ~/.pgpass
192.168.1.11:5432:replication:replica:replica
192.168.1.12:5432:replication:replica:replica
192.168.1.13:5432:replication:replica:replica
[root@test2 ~]# chown 600 /var/lib/pgsql/.pgpass
创建pg_archive目录
[root@test2 ~]# mkdir -p /var/lib/pgsql/pg_archive
[root@test2 ~]# chown postgres:postgres /var/lib/pgsql/pg_archive
修改监听ip地址,并启动服务
[root@test2 ~]# vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '192.168.1.12'
[root@test2 ~]# systemctl start postgresql
[root@test2 ~]# systemctl stop firewalld.service
在test3重复做一次
5、测试主从同步状态
在主库查看同步节点
[postgres@test1 ~]$ psql
psql (10.6)
Type "help" for help.
postgres=# select * from pg_stat_replication;
创建测试库,然后检查两个从库是否同步
在主库操作
postgres=# CREATE DATABASE test_db;
CREATE DATABASE
查看从库
test2同步了
test3同步了
搭建完成
6、主从切换
模拟主库故障,切换至从库,然后把原来的主库设置为从库
查看同步状态
主库在in production状态
[root@test1 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6782563721072319907
Database cluster state: in production
pg_control last modified: Fri 17 Jan 2020 10:39:41 PM CST
test2从库在in archive recovery
[root@test2 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6782563721072319907
Database cluster state: in archive recovery
pg_control last modified: Fri 17 Jan 2020 10:39:44 PM CST
test3从库在in archive recovery
[root@test3 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6782563721072319907
Database cluster state: in archive recovery
pg_control last modified: Fri 17 Jan 2020 10:39:47 PM CST
模拟主库故障
[root@test1 ~]# systemctl stop postgresql.service
把test2提升为主库,查看test2状态,这是test2变成主库了,可以写了
[root@test2 ~]# su - postgres -c "pg_ctl promote"
waiting for server to promote.... done
server promoted
[root@test2 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6782563721072319907
Database cluster state: in production
pg_control last modified: Fri 17 Jan 2020 10:48:12 PM CST
把test3指向新主库,修改recovery里面的host,然后重启
[root@test3 ~]# vim /var/lib/pgsql/data/recovery.conf
standby_mode = on
primary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'
[root@test3 ~]# systemctl restart postgresql.service
这时候test2就可以看到test3从库连过来了
把test1旧主库变成从库,指向test2
[root@test1 ~]# mv /var/lib/pgsql/data/recovery.bak /var/lib/pgsql/data/recovery.conf
[root@test1 ~]# vim /var/lib/pgsql/data/recovery.conf
standby_mode = on
primary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'
[root@test1 ~]# systemctl start postgresql.service
去test2查看节点,test1连接上来了
test1的状态也变成in archive recovery了
[root@test1 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6782563721072319907
Database cluster state: in archive recovery
pg_control last modified: Fri 17 Jan 2020 10:54:51 PM CST
切换完成
以上就是在CentOS中安装配置和使用PostgreSQL的介绍,内容较为全面,小编相信有部分知识点可能是我们日常工作可能会见到或用到的。希望你能通过这篇文章学到更多知识。
本文题目:如何在CentOS中安装配置和使用PostgreSQL
本文路径:http://hbruida.cn/article/jcoioj.html