一、概述
postgresql是开源、免费、对象-关系型数据库。
postgresql集群高可用是通过pgpool+repmgr+witness实现。
二、配置文件说明
bin目录：二进制可执行文件目录，此目录下有postgres、psql等可执行程序；pg_ctl工具在此目录，可以通过pg_ctl --help查看具体使用。
pg_hba.conf配置项解读
是下面的5个配置项的配置
TYPE DATABASE USER ADDRESS METHO| TYPE | 说明 |
| local | 定义本地的链接类型，配置时通过本地的UNIX套接字进行链接，如果配置了，我们在本机上使用psql时可以不用输入服务端的IP和端口号，默认以操作系统的用户进行登录，但是需要注意两点： 1、默认登录的端口号是5432，如果数据库没有采用该端口号，则需要显式的用-p参数进行指明 2、数据库中必须创建和和当前操作系统用户同名的数据库用户 |
| host | 该类型下，配置数据库支持远程TCP/IP链接，如果数据库需要配置局域网或者外网进行访问，需要进行该类型的配置。 |
| hostssl | 该类型的记录匹配使用TCP/IP的SSL链接，必须使用SSL加密才能链接数据库。 |
| hostnossl | 该配置和hostssl相反，只匹配不使用SSL的TCP/IP链接 |
| DATABASE | 说明 |
| 数据库名称 | 这里可以配置数据库名称，意思就是对配置的数据库支持远程或者本地链接的配置。 |
| all | 表明当前的配置针对所有的数据库生效 |
| replication | 这是流复制链接的配置，在PG的主从配置中会用到。 |
# user
配置进行链接的用户，需要注意的是这里是指的数据库的用户，而非操作系统的用户，如果配置了all表示接受所有数据库用户的链接。
# address
允许或者拒绝远端链接的IP地址，TYPE配置的是local可以为空，当时TYPE配置的是host时，必须进行配置，配置的格式是 <IP/masklen>，比如配置了192.168.5.0/24，表示针对子网是192.168.5.0的所有IP都会生效。| METHOD | 说明 |
| trust | 无条件的允许链接。在此种认证方法下，数据库用户进行连接时不需要输入任何口令。 |
| reject | 无条件的拒绝连接。在此种认证方法下，数据库会拒绝该连接（即，配置连接的黑名单）。 |
| md5 | 要求客户端提供一个MD5加密的口令的认证。这种模式下，客户端在网络上传递的口令经过了MD5加密，防止在网络上明文传输，造成口令的泄露。 |
| password | 要求客户端提供一个明文口令，这种模式下，客户端提供的口令会以明文的形式在网络上传递，具有泄露的风险，一般不会采用该种方式。 |
| ident | 服务器会联系客户端的ident服务，验证当前的用户在客户端上是不是操作系统的有效用户，如果是，则链接成功；如果不是则链接失败。这种方式通常和UNIX域套接字的链接认证方式一起使用，此时和peer认证的效果一致。 |
| peer | 使用操作系统用户登录，链接的用户是操作系统上某个操作系统的用户，但是需要注意的是数据库中要存在和操作系统一样的同名用户。 |
举例说明
pg_hba.conf（认证配置文件）1
用于配置数据库的远程连接，通过加入以下命令行，运行任何用户远程连接本数据库，连接时需要提供密码。
repmgr用户为repmgr工具默认使用和创建的数据库用户，这是pg-cluster中会使用到的用户repmgr
host replication all 127.0.0.1/32 md5
host replication all 10.99.99.99 md5 # ip为10.99.99.99机器可访问
host all all 0.0.0.0/0 md5 # navicat可访问。md5表示要求客户端提供一个 MD5 加密的口令进行认证。如果改为trust表示不需要用户名密码即可连接
pg_hba.conf（认证配置文件）2
本地trust认证
# TYPE DATABASE USER ADDRESS METHO
local all all trust
在该配置下，在使用数据库用户登录时不会提示输入密码本地MD5验证
# TYPE DATABASE USER ADDRESS METHO
local all all md5在此种认证方式下，在本地登录时会提示要输入密码
# TYPE DATABASE USER ADDRESS METHO
local all test md5使用这种方式，只能使用名为test的用户进行登录，在使用postgres用户进行登录时会报错
pg_hba.conf（认证配置文件）3
远程认证
# TYPE DATABASE USER ADDRESS METHO
host all all 0.0.0.0/0 trust在这种方式下，在远程任何一台客户端上都能使用psql进行登录，而且不会提示密码。这种配置很危险，不具有安全性。
# TYPE DATABASE USER ADDRESS METHO
host all all 0.0.0.0/0 md5这种认证方式，要求远程客户端链接服务器时要输入密码
pg_hba.conf（认证配置文件）中将配置放置在第一行会生效，具体原因不清楚。
postgresql集群版，默认情况下，pg-pod0 psql -h podIP -U postgres 连接到pg-pod1需要输入密码.
通过设置pg_hba.conf，将pg-pod1的PodIP配置成trust，就可以从 pg-pod0免密登录到 pg-pod1。
postgresql.conf（主配置文件)
所有配置信息在系统视图pg_settings中可查看，通过context可知修改相关配置后是否需要重启。
登录postgresql确认查看
$ psql -p 5432
psql (13.4)
Type "help" for help.
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)
postgres=# select setting from pg_settings where name like '%auto%';
# 输出查询到的配置项
三、pg命令行操作
# 数据库连接： psql命令在postgresql/bin目录下。
# 添加参数-E可以在执行psql快捷命令时同时输出对应sql。也可以通过命令\set ECHO_HIDDEN on|off控制
psql "host=127.0.0.1 port=5432 user=postgres password=123456 dbname=postgres"
psql -U postgres -d DB_NAME -h localhost -c 'select * from user_info'
# 数据导出-pg_dump命令
pg_dump "host=XX.XX.XX.XX port=5432 user=XXXX password=XXXX dbname=XXXXX" -t table_name -f table_name.sql
# 数据导出-psql命令
psql "host=XX.XX.XX.XX port=5432 user=XXX password=XXX dbname=XXX" -f table_name.sql
# 创建数据库
createdb myDB
# 删除数据库
dropdb myDB
# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录
SELECT * FROM user_tbl;
# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl;
常见psql快捷命令（通过psql连接数据库后，通过“\”开头的快捷命令进行数据库相关操作，tab键可补全命令）：
| 说明 | 命令 | 备注 |
| 退出命令行 | \q | |
| 查看数据库 | \l 或者 \list | 小写L |
| 列出数据库更详细的信息 | \l+ | |
支持通配符，列出 t 开头的数据库 | \l t* | |
| 查询当前登录的数据库和用户 | \c | |
| 切换数据库 | \c dbName | |
| 查看sql语法（help） | \h | |
| 列出当前数据库的所有的关系表 | \d | |
| 列出所有用户 | \du |
四、pg-cluster集群
postgresql实现高可用，是通过pg-pool+repmgr+witness进行实现。1个Primary+2 standby+3 witness+1 pgpool的方式。
集群最重要的高可用，即故障恢复，有两种方式：手动、自动。repmgr是开源的pg-cluster管理工具，支持Auto Failover自动 和 Manual Switchover手动；
| 方式 | 解释 |
| Auto Failover | 当主服务器节点不可用时，repmgr可将备用服务器自动提升为主服务器节点的过程成为failover |
| Manual Switchover | 通过repmgr工具手动将备用服务器提升为主服务器节点的过程称之为switchover |
repmgr专业术语解释
replication cluster：复制集群，指的repmgr管理的通过流复制搭建的PostgreSQL数据库集群
node：在复制集群中，一个pg数据库节点称为一个node节点
upstream node: 备用服务器的流复制链接的上游节点
fencing: 当发生failover后，确保旧的主服务器节点不会被意外重新链接上来，造成“脑裂”
witness server: 见证服务器，在多个备服务器的情况下若发生failover，见证服务器可用来决策选出新的主服务器节点。
primary节点：流复制中可用于业务读写的节点
standby节点：流复制中数据均从master节点进行复制，至允许业务查询
repmgr组件组成及功能
repmgr ： 用于执行管理任务的命令行工具，包括设置服务器角色、主动切换服务器角色、查看复制集群状态信息等
repmgrd ： 守护程序，监控和记录复制集群信息、检测集群复制故障并决策选出最佳服务器并提升为主服务器、用户自定义脚本执行电子邮件告警发送
集群的基本查询命令
查询集群各个节点信息
# 这里是通过容器化启动的pg-cluster
# cluster show
# 该命令查看集群的主节点、从节点
kubectl exec -it -n ns postgresql-cluster-pg-0 -c postgresql -- /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show
主从切换故障自愈的配置是在/postgresql/configuration中记录的，想当于postgresql.conf
> 容器中配置项记录在 /opt/bitnami/repmgr/conf/repmgr.conf
failover = 'automatic'
> postgresql.conf中设置
shared_preload_libraries = 'repmgr, pgaudit'
容器化的pg-lcuster进行高可用的测试，将某一个postgresql-pod挂掉，来模拟某一个节点故障。
通过上面的命令查看pod的primary和standby会切换。
五、备份与还原
分为逻辑备份和物理备份。
1>pg_dump/pg_dumpall命令
pg_dumpall是将一个pg集群全部转存到另一个脚本文件（sql脚本、归档文件）中，而pg_dump命令可以选择一个数据库或部份表进行备份。pg_dump结合pg_restore使用，能灵活备份和恢复。
2>冷备份
最简单的物理备份就是冷备份，即：停止pg，然后拷贝pg的data目录。
3>热备份
即不停止数据库进行备份，常见的方法有：PITR方法、使用文件系统或块设备级别的快照功能完成备份。Linux下最简单的备份方式是使用LVM的快照功能。
# 导入数据库
psql -U 用户名 -d 导入的数据库名 -f 文件名.sql
# 导出数据库
pg_dump -U 用户名 -d 导出的数据库名 -f 文件名.sql
890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
