PostGreSQL
简介
PostgreSQL是一个功能强大的开源数据库系统，通过长达15年以上的积极开发和不断改进，PostgresSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgresSQL可以运行在所有主流操作系统上，包括Liunx、Unix和Windows。
PostgreSQL是完全的事务安全性数据库，支持丰富的数据类型
作为一种企业级数据库，PostgreSQL以它所具有的各种高级功能而自豪，像多版本并发控制( MVCC )、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。它支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。它也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。
特征
多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。
数据类型：包括文本、任务京都的数值数组、JSON数据、枚举类型、XML数据等。
全文检索：通过Tsearch2或OpenFTS。
NoSQL：JSON，JSONB，XML，HStore 原生支持，甚至 NoSQL 数据库的外部数据包装器。
数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen等，使用 FDW（Foreign data wrappers） 进行 ETL（Extract-Transform-Load）。
函数：通过函数，可以在数据库服务器执行指令程序。
索引：用户可以自定义索引方法，或使用内置的B树，哈希表于Gist索引
触发器：
单机环境搭建
前言
因主要以学习了解为目标，所以服务器以在本地虚拟机的方式进行搭建，网络方式以物理网卡进行ip分配，与主机形成同网段的局域网形式。服务器系统的版本为：centos7.x的版本。
材料准备
此数据库是开源数据库，可直接在官网www.postgresql.org/上进行下载，需要找好跟自己服务器系统版本一致的安装包。安装形式分为rpm安装和tar安装，这里主要以rpm安装为主。
将安装包下载到本地，上传到内网服务器。
使用rpm -ivh xxxx.rpm进行安装。如果安装过程缺失一些依赖，不过通过一些其他途径进行下载。
准备目录
在内网的，一般都是实际使用环境，有时需要修改路径，「也可以直接进行数据库初始化」。
#创建自定义目录
mkdir /pgsqldata
# 修改所属用户和用户组
# postgres 默认安装完rpm后，会默认创建出来用户
chown postgres:postgres -R /pgsqldata
chmod -R 700 /pgsqldata
#配置PostgreSQL库自定义目录,在该路径下的/usr/lib/systemd/system/postgresql-12.service文件中修改
vim /usr/lib/systemd/system/postgresql-12.service
# 修改后执行
systemctl daemon-reload
初始化与启动数据库
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
# 初始化成功后，会出现Initializing database ...OK
# 启动PostgreSql服务
sudo systemctl enable postgresql-12
# 启动PostgreSql服务
sudo systemctl start postgresql-12
修改密码
su - postgres
# 进入数据库
psql
# 修改密码
alter user postgres with password 'postgres';
其余配置
数据库配置
#配置文件postgresql.conf,在自定义的目录/pgsqldata下,进文件中修改一下
vi /pgsqldata/postgresql.conf
# listen_addresses = '*' 表示监听所有的ip信息(记得去掉#)
# port = 5432 表示服务的端口，可以自定义为其他端口(记得去掉#)
#在最下面添加
host all all 0.0.0.0/0 md5
#修改完成，需要重启服务才生效
systemctl restart postgresql-12
服务器配置
关闭防火墙
systemctl stop firewalled
systemctl disable firewalld
setenforce 0
# 关闭并禁用NetworkManager
systemctl stop NetworkManager
systemctl disable NetworkManager
# 重启网络服务
systemctl restart network
客户端连接
集群环境搭建
前言
采用方案为PostgreSQL+PGPool-II的架构方式。
pgpool-II是一个位于PostgreSQL服务器与客户端之间的中间件，提供了连接池、复制、负载均衡、限制连接数等功能。
材料准备
附录
pgpool有四个配置文件，分别是：
-
pcp.conf 用于管理查、看节点信息，如加入新节点。该文件主要是存储用户名及md5形式的密码。
-
pgpool.conf 用于设置pgpool的模式，主次数据库的相关信息等。
-
pool_hba.conf 用于认证用户登录方式，如客户端IP限制等，类似于postgresql的pg_hba.conf文件
-
pool_passwd 用于保存相应客户端登录帐号名及md5密码
1. 服务器准备
目前计划搭建为：一主两从集群架构，且在内网进行搭建。所以我这边考虑在局域网内搭建三台虚拟机进行构建，在vmware上设置自定义网络配置，以桥接方式直接通过物理网卡分配相应虚拟机ip，需要三个服务器ip与一个虚拟ip。
前提：三台服务器已经搭配好静态IP。
| 主机名 | ip | PostGreSQL版本 | PGPool版本 |
|---|---|---|---|
| server1 | 192.168.1.211 | 13.6 | 4.2.8 |
| server2 | 192.168.1.212 | 13.6 | 4.2.8 |
| server3 | 192.168.1.213 | 13.6 | 4.2.8 |
| VIP | 192.168.1.210 | / | / |
2. 安装包准备
-
postgresql安装包可以去官方网站进行下载
-
pgpool安装包去官方下载网站进行下载
3. 环境准备
！请区分好操作用户，因为涉及到三个服务器操作。
以下操作需要在所有机器上执行、操作用户为root
1. 修改主机名称
hostnamectl set-hostname server1
2. 修改hosts文件
打开/etc/hosts文件、增加以下配置。
vim /etc/hosts
#增加以下配置
192.168.1.211 server1
192.168.1.212 server2
192.168.1.213 server3
3. 创建postgres用户
useradd -m -U postgres
# 修改postgres用户密码
passwd postgres
4. 关闭防火墙
firewall-cmd -- state
systemctl stop firewalld.service
systemctl disable firewalld.service
创建基础目录
mkdir -p /app/software
重启
reboot
安装与启动
1. PostGreSQL安装
1. 要求
因为单机环境搭建，我们选用的rpm的安装方式，这次我们采用离线包的方式进行安装，所有需要一定的编译环境。
PostGreSQL13.6版本构建需要make版本是3.80或者更高版本。可以先检查机器make是否符合要求。
make --version
2. 安装
全部服务器执行，用户postgres
1. 编译构建
# 解压安装包
tar -zxvf postgresql-13.6.tar.gz
cd postgresql-13.6
./configure --prefix=/app/postgresql-13.6
make world && make install-world
2. 配置环境变量
vim /etc/profile
LD_LIBRARY_PATH=/app/postgresql-13.6/lib
export LD_LIBRARY_PATH
PATH = /app/postgresq-13.6/bin:$PATH
export PATH
MANPATH = /app/postgresql-13.6/share/man:$MANPATH
export MANPATH
source /etc/profile
#验证环境变量配置是否成功
pg_config
# 变更安装的所属用户权限
chown -R postgres:postgres /app
3. 初始化数据库
以下操作只在server1机器上执行，以postgres用户
- 初始化
# 切换用户
su postgres
# 初始化数据库
/app/postgresql-13.6/bin/initdb -D /app/postgresql-13.6/data -W
- 修改配置文件
vim /app/postgresql-13.6/data/postgresql.conf
# 修改如下配置
listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/app/postgresql-13.6/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
wal_log_hints = on
- 修改pg_hba.conf
vim /app/postgresql-13.6/data/pg_data.conf
# 修改以下配置
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust
- 创建归档目录和日志目录
mkdir -p /app/postgresql-13.6/archivedir && mkdir -p /app/postgresql-13.6/logs
4. 启动
- 启动数据库
/app/postgresql-13.6/bin/pg_ctl start -D /app/postgresql-13.6/data/ -l /app/postgresql-13.6/logs/postgres.log
waiting for server to start.... done
server started
# 启动成功
- 创建数据库用户
/app/postgresql-13.6/bin/psql -U postgres -p 5432
postgres=# SET password_encryption = 'md5';
SET
postgres=# CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
CREATE ROLE
postgres=# \password pgpool
Enter new password for user "pgpool":
Enter it again:
postgres=# \password repl
Enter new password for user "repl":
Enter it again:
postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:
postgres=# GRANT pg_monitor TO pgpool;
GRANT ROLE
postgres=# \q
2. PGPool2安装
1. 前置准备
官网地址: 从官网下载指定版本安装包，上传到指定位置。这里以/app/software目录为例。
以下操作需要在所有机器上执行、操作用户postgres
1. 设置postgres用户免密验证
mkdir ~/.ssh
chmod 700 ~/.ssh
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
#远程拷贝给对方机器
ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
ssh-copy-id -i id_rsa_pgpool.pub postgres@server3
2. 创建.pgpass文件
vim /app/postgresql-13.6/.pgpass
#主机名：端口：库名：用户名：密码
#增加如下配置
server1:5432:replication:repl:repl
server2:5432:replication:repl:repl
server3:5432:replication:repl:repl
server1:5432:postgres:postgres:postgres
server2:5432:postgres:postgres:postgres
server3:5432:postgres:postgres:postgres
chmod 600 /app/postgresql-13.6/.pgpass
2. 安装
1. 解压安装包
tar -zxvf pgpool-II-4.2.8.tar.gz
cd /app/software/pgpool-II-4.2.8
./configure --prefix = /app/pgpool2-4.2.8 --with-pgsql=/app/postgresql-13.6/
make && make install
2. 安装pgpool-recovery
安装pgpool-recovery，使用在线恢复插件
cd /app/software/pgpool-II-4.2.8/src/sql/pgpool-recovery
make && make install
3. 配置pgpool_node_id
配置pgpool_node_id文件、server1是0、server2是1、server3是2
vim /app/pgpool2-4.2.8/etc/pgpool_node_id
0
3. 准备环境
以下操作全部在server1机器上执行、postgres用户
1. 修改failover和follow_primary文件
# 复制failover故障转移和follow_primary故障转移后备机和新的主同步
cp /app/pgpool2-4.2.8/etc/failover.sh.sample /app/pgpool2-4.2.8/etc/failover.sh && \
cp /app/pgpool2-4.2.8/etc/follow_primary.sh.sample /app/pgpool2-4.2.8/etc/follow_primary.sh && \
cd /app/pgpool2-4.2.8/etc/ && \
chmod 700 failover.sh follow_primary.sh
vim /app/pgpool2-4.2.8/etc/failover.sh
# 修改以下配置
PGHOME=/app/postgresql-13.6
vim /app/pgpool2-4.2.8/etc/follow_primary.sh
# 修改以下配置
PGHOME=/app/postgresql-13.6
ARCHIVEDIR=/app/postgresql-13.6/archivedir
REPLUSER=repl
PCP_USER=pgpool
PGPOOL_PATH=/app/pgpool2-4.2.8/bin
primary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''/app/postgresql-13.6/.pgpass'''
2. 修改pcp.conf
cp /app/pgpool2-4.2.8/etc/pcp.conf.sample /app/pgpool2-4.2.8/etc/pcp.conf
# 生成pgpool用户和密码
echo 'pgpool:'`/app/pgpool2-4.2.8/bin/pg_md5 pgpool` >> /app/pgpool2-4.2.8/etc/pcp.conf
3. 创建pcppass文件
echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass && chmod 600 ~/.pcppass
4. 修改recovery_1st_stage和pgpool_remote_start
# 复制在线恢复脚本
cp /app/pgpool2-4.2.8/etc/recovery_1st_stage.sample /app/postgresql-13.6/data/recovery_1st_stage && cp /app/pgpool2-4.2.8/etc/pgpool_remote_start.sample /app/postgresql-13.6/data/pgpool_remote_start && chmod 700 /app/postgresql-13.6/data/recovery_1st_stage /app/postgresql-13.6/data/pgpool_remote_start
vim /app/postgresql-13.6/data/recovery_1st_stage
# 修改以下配置
PGHOME=/app/postgresql-13.6
ARCHIVEDIR=/app/postgresql-13.6/archivedir
primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/app/postgresql-13.6/.pgpass'''
vim /app/postgresql-13.6/data/pgpool_remote_start
# 修改以下配置
PGHOME=/app/postgresql-13.6
# 执行
psql template1 -c "CREATE EXTENSION pgpool_recovery"
5. 修改pgpool.conf
cp /app/pgpool2-4.2.8/etc/pgpool.conf.sample-stream /app/pgpool2-4.2.8/etc/pgpool.conf
vim /app/pgpool2-4.2.8/etc/pgpool.conf
# 修改以下配置
listen_addresses = '*'
port = 9999
sr_check_user = 'pgpool'
sr_check_password = ''
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/postgresql-13.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/postgresql-13.6/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'
backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/app/postgresql-13.6/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'
failover_command = '/app/pgpool2-4.2.8/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/app/pgpool2-4.2.8/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
pid_file_name = '/app/pgpool2-4.2.8/pgpool.pid'
enable_pool_hba = on
use_watchdog = on
delegate_IP = '192.168.159.184'
# ens33是你的网卡名
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = 'server1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'server2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'server3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
wd_escalation_command = '/app/pgpool2-4.2.8/etc/escalation.sh'
log_destination = 'stderr'
logging_collector = on
log_directory = '/app/pgpool2-4.2.8/logs'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
6. 修改pool_hba.conf
cp /app/pgpool2-4.2.8/etc/pool_hba.conf.sample /app/pgpool2-4.2.8/etc/pool_hba.conf
vim /app/pgpool2-4.2.8/etc/pool_hba.conf
# 添加以下配置
host replication all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
7. 生成pool_passwd文件
# 生成pgpool和postgres密码文件
/app/pgpool2-4.2.8/bin/pg_md5 -f /app/pgpool2-4.2.8/etc/pgpool.conf -m -u pgpool pgpool
/app/pgpool2-4.2.8/bin/pg_md5 -f /app/pgpool2-4.2.8/etc/pgpool.conf -m -u postgres postgres
8. 修改escalation文件
# 复制看门狗的脚本
cp /app/pgpool2-4.2.8/etc/escalation.sh.sample /app/pgpool2-4.2.8/etc/escalation.sh && chmod 700 escalation.sh
vim /app/pgpool2-4.2.8/etc/escalation.sh
# 修改以下配置
VIP=192.168.159.184
DEVICE=ens33
9. 创建logs目录
需要所有机器都执行
mkdir -p /app/pgpool2-4.2.8/logs
10. 远程复制给其他机器
只需要server1机器执行，发送给其余两个节点
cd /app/pgpool2-4.2.8/etc
scp escalation.sh failover.sh follow_primary.sh pgpool.conf pcp.conf pool_hba.conf pool_passwd postgres@server2:/app/pgpool2-4.2.8/etc/
cd ~
scp .pcppass postgres@server2:/home/postgres/
11. 修改sudoers文件
切换成root、需要每台机器都修改
su root
chmod +w+x /etc/sudoers
vim /etc/sudoers
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD:/usr/sbin/arping
12. 配置自定义服务
需要每台机器都修改、操作用户是root
vim /usr/lib/systemd/system/pgpool.service
[Unit]
Description=Pgpool-II
After=syslog.target network.target
[Service]
Type=forking
User=postgres
Group=postgres
PIDFile=/app/pgpool2-4.2.8/pgpool.pid
ExecStart=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf
ExecStop=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf -m fast stop
ExecReload=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf reload
[Install]
WantedBy=multi-user.target
4. 启动
# 启动服务
systemctl start pgpool
# 设置开机自启
systemctl enable pgpool
[root@server1 system]# systemctl status pgpool
● pgpool.service - Pgpool-II
Loaded: loaded (/usr/lib/systemd/system/pgpool.service; disabled; vendor preset: disabled)
Active: active (running) since 一 2022-05-23 19:17:08 CST; 4s ago
Main PID: 30782 (pgpool)
Memory: 131.2M
CGroup: /system.slice/pgpool.service
├─30782 /app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpoo...
├─30783 pgpool: PgpoolLogger
└─30785 pgpool: watchdog
5月 23 19:17:08 server1 systemd[1]: Started Pgpool-II.
5月 23 19:17:08 server1 systemd[1]: Starting Pgpool-II...
5. 加入备机
su postgres
cd /app/pgpool2-4.2.8/bin
# 使用在线恢复脚本加入postgres备机 后面的1和2就是你的pgpool_node_id
./pcp_recovery_node -h 192.168.1.210 -p 9898 -U pgpool -n 1
Password:
pcp_recovery_node -- Command Successful
[postgres@server1 bin]$ ./pcp_recovery_node -h 192.168.1.210 -p 9898 -U pgpool -n 2
Password:
pcp_recovery_node -- Command Successful
集群问题总结
问题很多：重点多追踪pg_pool所产生的日志文件信息。
1. recovery请求连接被拒绝
应当是反复重启数据库导致的主节点已被漂移，可以进一步down到pid进程，然后清除postgres从库中产生的data数据目录，然后重启全部节点。
2. 流复制不生效
情况一：应当检查其配置文件，重点从模板中复制出来的几个文件，是否有拼写错误的情况。
情况二：应当授予pg_pool用户以pg_monitor角色权限。
集群功能验证
1. 查看postgresql节点状态
psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"
# 可以看到现在server1是postgres主服务、2和3同步1的数据
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | server1 | 5432 | up | 0.333333 | primary | 0 | true | 0 | | | 2022-05-23 19:21:47
1 | server2 | 5432 | up | 0.333333 | standby | 0 | false | 0 | streaming | async | 2022-05-23 19:21:47
2 | server3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | streaming | async | 2022-05-23 19:21:47
(3 rows)
2. 查看watchdog状态
/app/pgpool2-4.2.8/bin/pcp_watchdog_info -h 192.168.159.184 -p 9898 -U pgpool
# 可以看到现在server1是主。2和3是备
server1:9999 Linux server1 server1 9999 9000 4 LEADER
server2:9999 Linux server2 server2 9999 9000 7 STANDBY
server3:9999 Linux server3 server3 9999 9000 7 STANDBY
停掉server1服务、然后再查看watchdog状态。
systemctl stop pgpool
# 现在是server2是主，3是备。1是宕机
server2:9999 Linux server2 server2 9999 9000 4 LEADER
server1:9999 Linux server1 server1 9999 9000 10 SHUTDOWN
server3:9999 Linux server3 server3 9999 9000 7 STANDBY
再启动server1服务、查看状态
# server2是主，1和3是备
server2:9999 Linux server2 server2 9999 9000 4 LEADER
server1:9999 Linux server1 server1 9999 9000 7 STANDBY
server3:9999 Linux server3 server3 9999 9000 7 STANDBY
3. posrgresql故障转移
psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"
# 现在server1是主
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | server1 | 5432 | up | 0.333333 | primary | 0 | true | 0 | | | 2022-05-23 19:26:31
1 | server2 | 5432 | up | 0.333333 | standby | 0 | false | 0 | streaming | async | 2022-05-23 19:26:31
2 | server3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | streaming | async | 2022-05-23 19:26:31
停掉server1的postgresql服务。
/app/postgresql-13.6/bin/pg_ctl stop -D /app/postgresql-13.6/data/
# 可以看到现在server2是主，server3是备。1是宕机
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | server1 | 5432 | down | 0.333333 | standby | 0 | false | 0 | | | 2022-05-23 19:27:42
1 | server2 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2022-05-23 19:27:42
2 | server3 | 5432 | up | 0.333333 | standby | 0 | true | 0 | streaming | async | 2022-05-23 19:27:47
# 查看单节点信息。server3是t、备状态
[postgres@server1 bin]$ psql -h server3 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
pg_is_in_recovery
-------------------
t
(1 row)
# server2是f、主节点
[postgres@server1 bin]$ psql -h server2 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
pg_is_in_recovery
-------------------
f
(1 row)
# 查看server2的同步信息
[postgres@server1 bin]$ psql -h server2 -p 5432 -U pgpool postgres -c "select * from pg_stat_replication" -x
-[ RECORD 1 ]----+------------------------------
pid | 31103
usesysid | 16385
usename | repl
application_name | server3
client_addr | 192.168.159.183
client_hostname |
client_port | 47512
backend_start | 2022-05-23 19:27:43.700107+08
backend_xmin |
state | streaming
sent_lsn | 0/60001B8
write_lsn | 0/60001B8
flush_lsn | 0/60001B8
replay_lsn | 0/60001B8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-05-23 19:29:34.022772+08
4. 在线恢复
# 使用在线恢复脚本恢复server1
[postgres@server1 bin]$ ./pcp_recovery_node -h 192.168.159.184 -p 9898 -U pgpool -n 0
Password:
pcp_recovery_node -- Command Successful
# 可以看到server2是主，1和3是备
[postgres@server1 bin]$ psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | server1 | 5432 | up | 0.333333 | standby | 0 | false | 0 | streaming | async | 2022-05-23 19:31:02
1 | server2 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2022-05-23 19:27:42
2 | server3 | 5432 | up | 0.333333 | standby | 0 | true | 0 | streaming | async | 2022-05-23 19:27:47
(3 rows)
数据库使用
1. 创建数据库
# 创建数据库
create database test;
# 查看所有数据库
\l
# 切换当前数据库
\c 库名
# 删除数据库
drop database 库名;
2. 数据表操作
创建表格时每列都必须使用数据类型，PostgreSQL中主要有三类数据类型：
● 数值数据类型
● 字符串数据类型
● 日期/时间数据类型
3. Schema
PostgreSQL模式（schema）可以看作是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如test库和myschema库都可以包含名为test的表。
使用模式的优势：
● 允许多个用户使用一个数据可并且不会相互干扰。
● 将数据库对象组织成逻辑组以便更容易管理。
● 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。
4. 用户管理
在postgresql里没有用户和角色的概念，create user 是create role的别名，这两个命令几乎是完全相同的，唯一的区别是create user命令创建的用户默认带有login属性，而从create role命令却不带。
create user a; // 创建用户a
create role b; // 创建角色b
# 创建用户且配置密码
create user testUser with password 'password';
# 授权用户对某个数据库的读取和写入权限
grant all privileges on database testdb to testUser;
# 但此时用户还是没有读写权限，需要继续授权表
grant all privileges on all table in schema public to xxx;
# 修改用户密码
alter user testUser with password 'newpassword';
# 取消授权
revoke all privieges on database mydb from test;
# 删除用户
drop user testUser
5. 表空间管理
1. 查看表空间
# 通过bash命令登录到pg的交互模式，输入命令
\db+
2. 创建表空间
要创建表空间，首先需要先创建一个新目录，需要注意的是改目录需要属于‘postgres’用户。
使用sql创建表空间，最好使用超级管理员用户
create tableplace tdd_place owner user location '/app/tddData';
3. 使用表空间
# 在表空间内创建一个名为xxj的数据库：
#注意：此后该数据库所有对象的默认表空间也成为了赋予的表空间
create database xxjdb tableplace tdd_place;
# 创建一个表newtab,索引不会继承表的表空间
create table newtab(
id integer not null,
val text not null
) tablespace xxjdb;
# 创建索引对象
create index newtab_index1 on newtab(id) tablespace xxjdb;
# 更改表空间
alter table newtab set tablespace pg_default;
# 将一个表空间或索引一大另一个表空间
alter table in tablespace xxjdb set tablespace pg_default;
4. 表空间扩容
前言
在pg数据库实际使用过程中，随着业务数据的不断累积，会导致磁盘的可用空间越来越小，尤其是在liunx下，没办法将原来的磁盘进行扩容，只能够通过加载新的磁盘来进行扩容，只能够通过加载新的磁盘进行扩容。如果原来的pg数据库是一个簇，那么很好处理，一种将数据库簇一部分挪走，另一种就是本文讲到的表空间迁移的方式。
pg数据库一般默认安装完有两个表空间，pg_default和pg_global。这两个表空间的物理位置都默认在data目录下，
场景
工作在liunx系统中的主从pg数据库在不断存储数据过程中，挂载在/data1下的数据pgdata占用空间越来越大。
- 首先在linux系统中/data2目录挂载一块新磁盘。这个步骤需要在从数据库中同步进行。
mkfs.ext4 /dev/sdc
mount /data2 /dev/sdc
- 在/data2下创建一个目录作为新表空间的数据目录，并给与postgres权限。这个步骤也需要在从数据库中同步进行。
mkdir /data2/pg_tbs1
chown -R postgres.postgres /data2/pg_tbs1
chmod 700 /data2/pg_tbs1
- 创建新的表空间，并指定新创建的目录，这个在主数据库中进行就可以了，从会自动同步。
postgres=# create tablespace tbs_data location '/data2/pg_tbs1';
CREATE TABLESPACE
- 查看表空间
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 15 GB |
pg_global | postgres | | | | 521 kB |
tbs_data | postgres | /data2/pg_tbs1 | | | 0 bytes |
(3 rows)
- 迁移数据库，这个过程还是比较快的，基本就是复制。同样，只需要在主库中进行就行了，从库会自动同步。注意，迁移过程中，TEST库是会加上全局锁的，无法读写。所以在实际中需要协调出业务空闲时间。
迁移数据库TEST的表空间
alter database TEST set tablespace tbs_data;
- 迁移完毕后查看
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 3043 MB |
pg_global | postgres | | | | 521 kB |
tbs_data | postgres | /data2/pg_tbs1 | | | 12 GB |
5. 清除残片空间
如果删除大量数据，可以使用vacuum命令清理表并释放空间
备份
可以使用pg_dump来备份数据库，pg_dump是用于备份PostGreSQL数据库工具，
