【PostgreSQL】入门学习笔记(包括安装、环境与参数配置、故障排查以及备份还原等)
前言：
以下内容为前几天在备考PostgreSQL入门考试时候做的笔记，经过了全职的两天的奋战与实验，并最终顺利通过了PCA初级认证考试。现在把我学习的笔记分享给大家，文中有对应的思维导图图片可供查看，内容与后面正文文本一致。另外，由于SQL语句部分比较基础，基本上会一门数据库就都会，所以此处部分省略掉不做过多记录了。
以下，enjoy：
# PostgreSQL
## PostgreSQL的发展历程
### 始于1986年的Postgres项目
### 1994年，新增了SQL语言解释器，Postgres95诞生
### 1996年，更名 PostgreSQL,版本号从6.0开始
## PostgreSQL的安装
### yum安装
- https://www.postgresql.org/download/linux/redhat/
- yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm - yum install -y postgresql14-server - /usr/pgsql-14/bin/postgresql-14-setup initdb - systemctl enable postgresql-14 - systemctl start postgresql-14
- 其他操作
- 关闭selinux
- setenforce 0 - sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- 关闭防火墙
- systemctl status firewalld.service - systemctl stop firewalld.service - systemctl disable firewalld.service
- 修改环境变量 /etc/profile
- export PATH=/usr/pgsql-14/bin/:$PATH - source /etc/profile
### rpm安装
- https://yum.postgresql.org/rpmchart/
- postgresql14 - postgresql14-contrib - postgresql14-libs - postgresql14-server
- rpm -ivh postgresql14*
### 源码的安装
- wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz tar xf postgresql-13.3.tar.gz - 重要的依赖：readline,flex,bison
- yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc openssl-devel
- ./configure && make && make install | install-world - 编译
- cd postgresql-xx/ - ./configure --prefix=/XXX/postgresql --with-openssl - gmake world && gmake install-world
- 授权
- chown -R postgres. /XXX/postgresql
- 环境变量 /etc/profile
- export PATH=/XXX/postgresql/bin:$PATH export PGDATA=/XXX/postgresql/data - source /etc/profile
- 初始化
- su - postgres initdb -D $PGDATA
- 启动
- pg_ctl -D $PGDATA start
- 使用系统管理
- 修改: /usr/lib/systemd/system/postgresql-xx.service
- [Unit] Description=PostgreSQL database server After=network.target [Service] Type=forking User=postgres Group=postgres Environment=PGPORT=5432 Environment=PGDATA=/XXX/postgresql/data/ OOMScoreAdjust=-1000 ExecStart=/XXX/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300 ExecStop=/XXX/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast ExecReload=/XXX/postgresql/bin/pg_ctl reload -D ${PGDATA} -s TimeoutSec=300 [Install] WantedBy=multi-user.target
- 重新加载：systemctl daemon-reload - 使用systemctl启动
- systemctl start postgresql-xx.service systemctl enable postgresql-xx.service
### 二进制的安装
- EDB
## PostgreSQL的安装(其他)
### 初始化命令
- 运行initdb创建数据库实例：
- $ su - postgres - $ initdb -D
- 指定数据目录。没有指定，将使用环境变量PGDATA
- $ initdb -U
- 指定数据库超级用户名字
- -E
- 指定数据库字符编码
- -n
- 错误后不清理文件
- -W
- 初始化时给数据库设置密码
- -x
- 预写日志目录位置
- --wal-segsize=XXX
- 指定 WAL 段大小（单位 M），默认是 16M，最大 1G
### 启动和关闭
- system管理方式
- systemctl start postgresql-xx systemctl enable postgresql-xx systemctl status postgresql-xx systemctl stop postgresql-xx
- postgresql自带的命令
- pg_ctl start pg_ctl stop pg_ctl status - pg_ctl -D $PGDATA stop -m smart pg_ctl -D $PGDATA stop -m fast pg_ctl -D $PGDATA stop -m immediate
- smart 等待客户端断开连接(执行之后会特别慢) - fast 回滚未完成的事务，断开客户端连接（推荐用法） - immediate 强行终止进程，数据库没有干净的关闭
- 启动\停止有关日志
- vim /var/lib/pgsql/xx/data/log/postgresql-Mon.log
### 注意事项
- 关闭防火墙
- systemctl status firewalld.service - systemctl stop firewalld.service - systemctl disable firewalld.service
- ntp时间同步
- 服务端配置
- yum -y install ntp - 修改配置文件 /etc/ntp.conf
- 给本机权限: restrict 127.0.0.1 restrict ::1 - 例如授权10.0.0.0网段上所有的机器允许从ntp服务器上查询和同步时间: restrict 10.0.0.0 mask 255.255.255.0 nomodify notrap - 增加时间服务器列表： 0.asia.pool.ntp.org 0.cn.pool.ntp.org time.nist.gov server 0.asia.pool.ntp.org iburst server 1.asia.pool.ntp.org iburst server 2.asia.pool.ntp.org iburst server 3.asia.pool.ntp.org iburst - 当外部时间不可用时，使用本地时间: server 127.127.1.0 iburst fudge 127.127.1.0 stratum 10
- 设置开机自启动
- systemctl enable ntpd systemctl start ntpd systemctl enable ntpdate systemctl start ntpdate
- 查看ntp情况
- ntpq -p
- remote: NTP主机的IP或主机名称； 最左边是 + 表示目前正在起作用的上层NTP；如果是 * 表示这个也连接上了，不过是作为次要联机的NTP主机 - refid: 参考上一层NTP主机的地址 - st: stratum阶层 - t: 连接类型
- u：单播(unicast) - l：本地(local) - m: 多播(multicast) - b: 广播(broadcast)
- when: 这个时间之前刚刚做过时间同步 - poll: 在几秒之后进行下次更新 - reach: 已经向上层NTP服务器要求更新的次数 - delay: 网络传输过程中的延迟时间 - offset: 时间补偿的结果 - jitter: Linux系统时间和Bios硬件时间的差异时间
- 与硬件时间进行同步
- hwclock -w
- 测试
- ntpstat
- 客户端配置
- yum -y install ntp ntpdate - 方法一
- 重启服务以使配置生效,之后大概要等10分钟左右,才会同步成功
- echo "server ip" >/etc/ntp.conf systemctl enable ntpd systemctl restart ntpd hwclock -w
- 方法二
- systemctl enable ntpdate /usr/sbin/ntpdate -u ip hwclock -w crontab -e 10 23 * * * (/usr/sbin/ntpdate -u ip && /sbin/hwclock -w)&>/var/log/ntpdate.log
- 如果配置主从，尽量保持uid和gid一致
- 检查uid和gid
- id postgres
- 通过usermod,groupmod修改OS用户uid和gid
- groupmod -g 1000 postgres usermod -u 1000 -g 1000 postgres
- 关闭selinux
- setenforce 0 - sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- psql参数的使用
- 参数说明
- -U
- 以哪个用户登录
- -W
- 强制输入密码
- -h
- 主机名/IP地址
- -p
- 端口号
- -d
- 登录哪个数据库
- 查看版本信息
- psql –version select version();
## PostgreSQL的配置
### 参数的修改
- 文件位置：$ PGDATA/postgresql.conf
- include_if_exists = 'xxx.conf'
- 开启参数，并存在可使用的 $PGDATA/xxx.conf 文件才会进行加载
- include_dir = 'conf.d'
- 加载目录下的参数文件,$PGDATA/.conf.d/xxx.conf
- include = 'xxx.conf'
- 无论参数文件是否存在都会加载 $PGDATA/xxx.conf
- 数据库启动时，会读取该文件，可手动修改
- postgresql.auto.conf
- alter system修改后的参数配置，会覆盖postgresql.conf的值
- recovery.conf
- pg12已经不存在这个文件，已经将此文件的参数合并到了postgresql.conf
- pg_hba.conf 客户端认证配置文件(PG防火墙)
- 第一列 TYPE（连接方式）
- local
- 使用Unix域套接字的连接，如果没有TYPE为local的条目则不允许通过Unix域套接字连接
- host
- 匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接
- hostssl
- 匹配必须是使用SSL的TCP/IP进行连接。
- 配置hostssl的3个条件
- 户端和服务端都安装openssl - 编译时要指定 --with-openssl 打开ssl支持 - 在postgresql.conf中配置ssl = on
- hostnossl
- 只匹配使用非SSL的TCP/IP连接
- 第二列 DATABASE（目标数据库）
- 标识该行设置对哪个数据库生效
- 第三列 USER (目标用户)
- 标识该行设置对哪个数据库用户生效
- 第四列 ADDRESS (访问来源)
- 标识该行设置对哪个IP地址或IP地址段生效
- 第五列 METHOD (认证方式)
- reject
- 无条件拒绝连接
- md5 或 password
- 双重md5加密和明文加密
- scram-sha-256
- postgresql10中新增最安全的加密方式 - 查看没有用SCRAM加密口令的用户
- create user foo password 'foopassword'; - select usename,passwd from pg_shadow where passwd not like 'SCRAM%' or passwd is null; - select usename,passwd from pg_shadow ;
- trust
- 无条件的允许连接
- cert
- 使用SSL客户端证书认证
- peer
- 本地操作系统的当前用户名和数据库的用户名一致时，可以直接使用此用户名登录而不需要密码
- ident
- 用户映射文件
- 查看当前的加密方法
- postgres=# show password_encryption;
- 修改加密方法
- postgres=# alter system set password_encryption = 'scram-sha-256'; postgres=# show password_encryption; postgres=# select pg_reload_conf();
- 修改密码
- postgres=# alter user foo password ''Xzzp2008 ';
- 远程登录
- psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W
- 加强口令复杂度管理插件
- passwordcheck
- http://www.postgresql.org/docs/current/static/passwordcheck.html
- pg_ident.conf 客户端认证映射文件
### 数据库相关命令
- 查看参数
- 查询pg_settings系统表
- SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
- select name,setting,unit,short_desc from pg_settings where name like 'work_mem%'; - SELECT current_setting(name); - SELECT current_setting('work_mem'); - 通过show 命令查看
- show all
- 参数生效几种方式
- SELECT pg_reload_conf(); - pg_ctl -D $PGDATA reload; - /etc/init.d/postgresql-11.x reload; (el6) - systemctl reload service.postgresql-11.x (el7)
- 子主题 3
### 数据库管理
- 客户端工具
- pgAdmin
- https://www.pgadmin.org - yum / apt install pgadmin
- psql
- 连接数据库
- psql -h localhost -p 5432 database_name
- 获得psql的帮助
- \?
- 获得语法的帮助
- \h STATEMENT
- 在shell中执行命令
- psql -c "STATEMENT"
- 通过psql执行sql文件
- psql < f.sql
- 其他
- \l 查看有哪些数据库 - \c 用于切换数据库 - \d 显示每个匹配关系（表，视图，索引，序列）的信息 - \d 后面跟一个表名，表示显示表结构定义 - \d 后跟一个索引名，显示索引的信息 - \d 后面跟一个视图名，显示视图信息 - \timing on 显示SQL执行的时间 - \timing off 关闭计时功能 - \dn 列出所有的schema - \db 显示所有的表空间 - \du\dg 列出所有的角色或者用户 - \dp 显示权限分配情况 - \x 行列互换显示 - \set AUTOCOMMIT off 将自动提交功能关闭
## 体系结构
### 内存结构
- shared_buffers
- 共享内存
- work_mem
- 当使用order by或distinct操作对元组仅从排序时会使用这部分内存
- wal_buffer
- wal缓存
### 进程结构
- 查看进程
- --ps –ef |grep post
- background writer
- 进程将shared buffer pool中的脏数据写到磁盘，检查点总能触发这个进程
- checkpointer
- 检查点会触发产生这个进程
- autovacuum launcher
- autovacuum的守护进程，为vacuum process周期性的调用autovacuum work processes - autovacuum的作用
- 删除或重用无效元组的磁盘空间 - 更新数据统计信息，保证执行计划更优 - 更新visibility map，加速index-only scans - 避免XID回卷造成的数据丢失
- WAL writer
- 周期性的从wal buffer刷新数据到磁盘
- statistics collector
- 收集统计信息进程，比如pg_stat_activity 和pg_stat_database的数据。（表和索引进行了多少次插入，更新，删除操作，磁盘块读写次数及行的读写次数）
- logging collector (logger)
- 将错误信息写入到日志
- archiver
- 将日志归档的进程
- postgremaster
- 监听
### 数据库集群概念
- 一个数据库集簇（database cluster）=一个数据库实例（简称“实例”） - 每个数据库实例由数据库目录组成，目录中包含了所有的数据文件和配置文件 - 不同的实例可以通过两种方式引用
- 数据目录的位置 - 端口号
- 一个服务器可以管理多个数据库实例
### 物理结构
- 数据目录
- base
- 表和索引文件存放目录
- global
- 影响全局的系统表存放目录
- pg_commit_ts
- 事务提交时间戳数据存放目录
- pg_stat
- 统计子系统信息永久文件
- pg_wal
- 事务日志（预写日志）
- 相关文件
- PG_VERSION
- 版本号文件
- pg_hba.conf
- 客户端认证控制文件
- postgresql.conf
- 参数文件
- postgresql.auto.conf
- 参数文件，只保存ALTER SYSTEM命令修改的参数
- postmaster.opts
- 记录服务器最后一次启动时使用的命令行参数
- pg_ident.conf
- 控制postgresql用户名映射文件
- postmaster.pid
- 记录数据库进程编号、PGDATA、端口等
## 数据库故障排查
### 查看操作系统错误日志
- /var/log/message 系统启动后的信息和错误日志，是Red Hat Linux中最常用的日志之一
### 查看数据库错误日志
- 查看文件$PGDATA/log 默认情况下，一天产生一个日志
## SQL入门
### DDL
- 数据定义语言，用来定义库和表
### DML
- 数据操作语言
### DCL
- 数据控制语言
### DQL
- 数据查询语言
### 数据类型
- 字符类型 - 数字类型 - 日期/时间类型 - 范围类型 - 布尔类型 - 相关约束
### 其他
## 数据库备份相关
### 物理备份
- 热备
- 全量 pg_basebackup
- 备份命令： pg_basebackup -h ip -D /XXX/backup -P -p 5432 -U postgres - 恢复命令: cd /xxx/backup/ tar xf base.tar.gz -C ../data
- 结合时间点进行恢复
- 创建归档目录
- mkdir -p /XXX/archive - chown -R postgres. /XXX/
- 开启归档，并重启数据库
- wal_level = 'replica'
- wal_level参数说明
- minimal
- 记录wal最少，记录数据库异常关闭需要恢复的wal外，其它操作都不记录
- replica
- 在minimal的基础上还支持wal归档、复制和备库中启用只读查询等操作所需的wal信息
- logical
- 记录wal日志信息最多，包含了支持逻辑解析（10版本的新特性，逻辑复制使用这种模式）所需的wal，此参数包含了minimal和replica所有的记录
- archive_mode = 'on' - archive_command = 'cp %p /XXX/archive/%f' - 重启数据库
- pg_ctl restart
- 执行全量备份
- pg_basebackup -D /xxx/backup -P -p 5432 -U postgres - 如果此时的数据有变化，也可以执行增量备份： pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_time = '2022-04-17 16:00:00.00000+08' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
- 创建备库标签
- touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合还原点进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_name = 'huanyuan' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote' - touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合事务进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_xid = '487' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合LSN号码进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_lsn = '0/4011BF8' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 冷备
### 逻辑备份
- 全库备份 - pg_dump
- 库级别备份
- 备份
- pg_dump -U postgres test >test.sql
- 恢复
- psql create database test; psql -U postgres test < test.sql
- 指定格式进行备份
- 备份
- pg_dump -Fc -U postgres test >test.dmp
- 恢复
- psql create database test; pg_restore -d test test.dmp
- 表级别备份
- 备份库下某个模式所有的表
- 备份
- pg_dump -U postgres -t 'schema1.t*' test >test.sql
- 恢复
- psql -U postgres test < test.sql
- 备份单个表
- 备份
- pg_dump -t 表名 数据库名 >dump.sql
- 恢复
- create database 数据库名; psql -U postgres 数据库名< dump.sql
- schema级别备份
- pg_dumpall
- 全库级别备份
- 备份
- pg_dumpall -U postgres > dumpall.sql
- 恢复
- psql -U postgres < dumpall.sql
- pg_dump VS pg_dumpall
- pg_dumpall 是一个用于写出（"转储"）一个数据库集群里的所有PostgreSQL 数据库到一个脚本文件的工具。 - pg_dumpall 调用pg_dump - pg_dumpall 还转储出所有数据库公用的全局对象。这些信息目前包括数据库用户和组，以及适用于整个数据库的访问权限 - pg_dumpall 无法转储"大对象"，因为pg_dump无法把这样的对象转储到纯文本文件中。如果你的数据库里有大对象，那么你应该使用pg_dump 的非文本输出格式之一转储它们。
## PostgreSQL的发展历程
### 始于1986年的Postgres项目
### 1994年，新增了SQL语言解释器，Postgres95诞生
### 1996年，更名 PostgreSQL,版本号从6.0开始
## PostgreSQL的安装
### yum安装
- https://www.postgresql.org/download/linux/redhat/
- yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm - yum install -y postgresql14-server - /usr/pgsql-14/bin/postgresql-14-setup initdb - systemctl enable postgresql-14 - systemctl start postgresql-14
- 其他操作
- 关闭selinux
- setenforce 0 - sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- 关闭防火墙
- systemctl status firewalld.service - systemctl stop firewalld.service - systemctl disable firewalld.service
- 修改环境变量 /etc/profile
- export PATH=/usr/pgsql-14/bin/:$PATH - source /etc/profile
### rpm安装
- https://yum.postgresql.org/rpmchart/
- postgresql14 - postgresql14-contrib - postgresql14-libs - postgresql14-server
- rpm -ivh postgresql14*
### 源码的安装
- wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz tar xf postgresql-13.3.tar.gz - 重要的依赖：readline,flex,bison
- yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc openssl-devel
- ./configure && make && make install | install-world - 编译
- cd postgresql-xx/ - ./configure --prefix=/XXX/postgresql --with-openssl - gmake world && gmake install-world
- 授权
- chown -R postgres. /XXX/postgresql
- 环境变量 /etc/profile
- export PATH=/XXX/postgresql/bin:$PATH export PGDATA=/XXX/postgresql/data - source /etc/profile
- 初始化
- su - postgres initdb -D $PGDATA
- 启动
- pg_ctl -D $PGDATA start
- 使用系统管理
- 修改: /usr/lib/systemd/system/postgresql-xx.service
- [Unit] Description=PostgreSQL database server After=network.target [Service] Type=forking User=postgres Group=postgres Environment=PGPORT=5432 Environment=PGDATA=/XXX/postgresql/data/ OOMScoreAdjust=-1000 ExecStart=/XXX/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300 ExecStop=/XXX/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast ExecReload=/XXX/postgresql/bin/pg_ctl reload -D ${PGDATA} -s TimeoutSec=300 [Install] WantedBy=multi-user.target
- 重新加载：systemctl daemon-reload - 使用systemctl启动
- systemctl start postgresql-xx.service systemctl enable postgresql-xx.service
### 二进制的安装
- EDB
## PostgreSQL的安装(其他)
### 初始化命令
- 运行initdb创建数据库实例：
- $ su - postgres - $ initdb -D
- 指定数据目录。没有指定，将使用环境变量PGDATA
- $ initdb -U
- 指定数据库超级用户名字
- -E
- 指定数据库字符编码
- -n
- 错误后不清理文件
- -W
- 初始化时给数据库设置密码
- -x
- 预写日志目录位置
- --wal-segsize=XXX
- 指定 WAL 段大小（单位 M），默认是 16M，最大 1G
### 启动和关闭
- system管理方式
- systemctl start postgresql-xx systemctl enable postgresql-xx systemctl status postgresql-xx systemctl stop postgresql-xx
- postgresql自带的命令
- pg_ctl start pg_ctl stop pg_ctl status - pg_ctl -D $PGDATA stop -m smart pg_ctl -D $PGDATA stop -m fast pg_ctl -D $PGDATA stop -m immediate
- smart 等待客户端断开连接(执行之后会特别慢) - fast 回滚未完成的事务，断开客户端连接（推荐用法） - immediate 强行终止进程，数据库没有干净的关闭
- 启动\停止有关日志
- vim /var/lib/pgsql/xx/data/log/postgresql-Mon.log
### 注意事项
- 关闭防火墙
- systemctl status firewalld.service - systemctl stop firewalld.service - systemctl disable firewalld.service
- ntp时间同步
- 服务端配置
- yum -y install ntp - 修改配置文件 /etc/ntp.conf
- 给本机权限: restrict 127.0.0.1 restrict ::1 - 例如授权10.0.0.0网段上所有的机器允许从ntp服务器上查询和同步时间: restrict 10.0.0.0 mask 255.255.255.0 nomodify notrap - 增加时间服务器列表： 0.asia.pool.ntp.org 0.cn.pool.ntp.org time.nist.gov server 0.asia.pool.ntp.org iburst server 1.asia.pool.ntp.org iburst server 2.asia.pool.ntp.org iburst server 3.asia.pool.ntp.org iburst - 当外部时间不可用时，使用本地时间: server 127.127.1.0 iburst fudge 127.127.1.0 stratum 10
- 设置开机自启动
- systemctl enable ntpd systemctl start ntpd systemctl enable ntpdate systemctl start ntpdate
- 查看ntp情况
- ntpq -p
- remote: NTP主机的IP或主机名称； 最左边是 + 表示目前正在起作用的上层NTP；如果是 * 表示这个也连接上了，不过是作为次要联机的NTP主机 - refid: 参考上一层NTP主机的地址 - st: stratum阶层 - t: 连接类型
- u：单播(unicast) - l：本地(local) - m: 多播(multicast) - b: 广播(broadcast)
- when: 这个时间之前刚刚做过时间同步 - poll: 在几秒之后进行下次更新 - reach: 已经向上层NTP服务器要求更新的次数 - delay: 网络传输过程中的延迟时间 - offset: 时间补偿的结果 - jitter: Linux系统时间和Bios硬件时间的差异时间
- 与硬件时间进行同步
- hwclock -w
- 测试
- ntpstat
- 客户端配置
- yum -y install ntp ntpdate - 方法一
- 重启服务以使配置生效,之后大概要等10分钟左右,才会同步成功
- echo "server ip" >/etc/ntp.conf systemctl enable ntpd systemctl restart ntpd hwclock -w
- 方法二
- systemctl enable ntpdate /usr/sbin/ntpdate -u ip hwclock -w crontab -e 10 23 * * * (/usr/sbin/ntpdate -u ip && /sbin/hwclock -w)&>/var/log/ntpdate.log
- 如果配置主从，尽量保持uid和gid一致
- 检查uid和gid
- id postgres
- 通过usermod,groupmod修改OS用户uid和gid
- groupmod -g 1000 postgres usermod -u 1000 -g 1000 postgres
- 关闭selinux
- setenforce 0 - sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- psql参数的使用
- 参数说明
- -U
- 以哪个用户登录
- -W
- 强制输入密码
- -h
- 主机名/IP地址
- -p
- 端口号
- -d
- 登录哪个数据库
- 查看版本信息
- psql –version select version();
## PostgreSQL的配置
### 参数的修改
- 文件位置：$ PGDATA/postgresql.conf
- include_if_exists = 'xxx.conf'
- 开启参数，并存在可使用的 $PGDATA/xxx.conf 文件才会进行加载
- include_dir = 'conf.d'
- 加载目录下的参数文件,$PGDATA/.conf.d/xxx.conf
- include = 'xxx.conf'
- 无论参数文件是否存在都会加载 $PGDATA/xxx.conf
- 数据库启动时，会读取该文件，可手动修改
- postgresql.auto.conf
- alter system修改后的参数配置，会覆盖postgresql.conf的值
- recovery.conf
- pg12已经不存在这个文件，已经将此文件的参数合并到了postgresql.conf
- pg_hba.conf 客户端认证配置文件(PG防火墙)
- 第一列 TYPE（连接方式）
- local
- 使用Unix域套接字的连接，如果没有TYPE为local的条目则不允许通过Unix域套接字连接
- host
- 匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接
- hostssl
- 匹配必须是使用SSL的TCP/IP进行连接。
- 配置hostssl的3个条件
- 户端和服务端都安装openssl - 编译时要指定 --with-openssl 打开ssl支持 - 在postgresql.conf中配置ssl = on
- hostnossl
- 只匹配使用非SSL的TCP/IP连接
- 第二列 DATABASE（目标数据库）
- 标识该行设置对哪个数据库生效
- 第三列 USER (目标用户)
- 标识该行设置对哪个数据库用户生效
- 第四列 ADDRESS (访问来源)
- 标识该行设置对哪个IP地址或IP地址段生效
- 第五列 METHOD (认证方式)
- reject
- 无条件拒绝连接
- md5 或 password
- 双重md5加密和明文加密
- scram-sha-256
- postgresql10中新增最安全的加密方式 - 查看没有用SCRAM加密口令的用户
- create user foo password 'foopassword'; - select usename,passwd from pg_shadow where passwd not like 'SCRAM%' or passwd is null; - select usename,passwd from pg_shadow ;
- trust
- 无条件的允许连接
- cert
- 使用SSL客户端证书认证
- peer
- 本地操作系统的当前用户名和数据库的用户名一致时，可以直接使用此用户名登录而不需要密码
- ident
- 用户映射文件
- 查看当前的加密方法
- postgres=# show password_encryption;
- 修改加密方法
- postgres=# alter system set password_encryption = 'scram-sha-256'; postgres=# show password_encryption; postgres=# select pg_reload_conf();
- 修改密码
- postgres=# alter user foo password ''Xzzp2008 ';
- 远程登录
- psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W
- 加强口令复杂度管理插件
- passwordcheck
- http://www.postgresql.org/docs/current/static/passwordcheck.html
- pg_ident.conf 客户端认证映射文件
### 数据库相关命令
- 查看参数
- 查询pg_settings系统表
- SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
- select name,setting,unit,short_desc from pg_settings where name like 'work_mem%'; - SELECT current_setting(name); - SELECT current_setting('work_mem'); - 通过show 命令查看
- show all
- 参数生效几种方式
- SELECT pg_reload_conf(); - pg_ctl -D $PGDATA reload; - /etc/init.d/postgresql-11.x reload; (el6) - systemctl reload service.postgresql-11.x (el7)
- 子主题 3
### 数据库管理
- 客户端工具
- pgAdmin
- https://www.pgadmin.org - yum / apt install pgadmin
- psql
- 连接数据库
- psql -h localhost -p 5432 database_name
- 获得psql的帮助
- \?
- 获得语法的帮助
- \h STATEMENT
- 在shell中执行命令
- psql -c "STATEMENT"
- 通过psql执行sql文件
- psql < f.sql
- 其他
- \l 查看有哪些数据库 - \c 用于切换数据库 - \d 显示每个匹配关系（表，视图，索引，序列）的信息 - \d 后面跟一个表名，表示显示表结构定义 - \d 后跟一个索引名，显示索引的信息 - \d 后面跟一个视图名，显示视图信息 - \timing on 显示SQL执行的时间 - \timing off 关闭计时功能 - \dn 列出所有的schema - \db 显示所有的表空间 - \du\dg 列出所有的角色或者用户 - \dp 显示权限分配情况 - \x 行列互换显示 - \set AUTOCOMMIT off 将自动提交功能关闭
## 体系结构
### 内存结构
- shared_buffers
- 共享内存
- work_mem
- 当使用order by或distinct操作对元组仅从排序时会使用这部分内存
- wal_buffer
- wal缓存
### 进程结构
- 查看进程
- --ps –ef |grep post
- background writer
- 进程将shared buffer pool中的脏数据写到磁盘，检查点总能触发这个进程
- checkpointer
- 检查点会触发产生这个进程
- autovacuum launcher
- autovacuum的守护进程，为vacuum process周期性的调用autovacuum work processes - autovacuum的作用
- 删除或重用无效元组的磁盘空间 - 更新数据统计信息，保证执行计划更优 - 更新visibility map，加速index-only scans - 避免XID回卷造成的数据丢失
- WAL writer
- 周期性的从wal buffer刷新数据到磁盘
- statistics collector
- 收集统计信息进程，比如pg_stat_activity 和pg_stat_database的数据。（表和索引进行了多少次插入，更新，删除操作，磁盘块读写次数及行的读写次数）
- logging collector (logger)
- 将错误信息写入到日志
- archiver
- 将日志归档的进程
- postgremaster
- 监听
### 数据库集群概念
- 一个数据库集簇（database cluster）=一个数据库实例（简称“实例”） - 每个数据库实例由数据库目录组成，目录中包含了所有的数据文件和配置文件 - 不同的实例可以通过两种方式引用
- 数据目录的位置 - 端口号
- 一个服务器可以管理多个数据库实例
### 物理结构
- 数据目录
- base
- 表和索引文件存放目录
- global
- 影响全局的系统表存放目录
- pg_commit_ts
- 事务提交时间戳数据存放目录
- pg_stat
- 统计子系统信息永久文件
- pg_wal
- 事务日志（预写日志）
- 相关文件
- PG_VERSION
- 版本号文件
- pg_hba.conf
- 客户端认证控制文件
- postgresql.conf
- 参数文件
- postgresql.auto.conf
- 参数文件，只保存ALTER SYSTEM命令修改的参数
- postmaster.opts
- 记录服务器最后一次启动时使用的命令行参数
- pg_ident.conf
- 控制postgresql用户名映射文件
- postmaster.pid
- 记录数据库进程编号、PGDATA、端口等
## 数据库故障排查
### 查看操作系统错误日志
- /var/log/message 系统启动后的信息和错误日志，是Red Hat Linux中最常用的日志之一
### 查看数据库错误日志
- 查看文件$PGDATA/log 默认情况下，一天产生一个日志
## SQL入门
### DDL
- 数据定义语言，用来定义库和表
### DML
- 数据操作语言
### DCL
- 数据控制语言
### DQL
- 数据查询语言
### 数据类型
- 字符类型 - 数字类型 - 日期/时间类型 - 范围类型 - 布尔类型 - 相关约束
### 其他
## 数据库备份相关
### 物理备份
- 热备
- 全量 pg_basebackup
- 备份命令： pg_basebackup -h ip -D /XXX/backup -P -p 5432 -U postgres - 恢复命令: cd /xxx/backup/ tar xf base.tar.gz -C ../data
- 结合时间点进行恢复
- 创建归档目录
- mkdir -p /XXX/archive - chown -R postgres. /XXX/
- 开启归档，并重启数据库
- wal_level = 'replica'
- wal_level参数说明
- minimal
- 记录wal最少，记录数据库异常关闭需要恢复的wal外，其它操作都不记录
- replica
- 在minimal的基础上还支持wal归档、复制和备库中启用只读查询等操作所需的wal信息
- logical
- 记录wal日志信息最多，包含了支持逻辑解析（10版本的新特性，逻辑复制使用这种模式）所需的wal，此参数包含了minimal和replica所有的记录
- archive_mode = 'on' - archive_command = 'cp %p /XXX/archive/%f' - 重启数据库
- pg_ctl restart
- 执行全量备份
- pg_basebackup -D /xxx/backup -P -p 5432 -U postgres - 如果此时的数据有变化，也可以执行增量备份： pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_time = '2022-04-17 16:00:00.00000+08' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
- 创建备库标签
- touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合还原点进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_name = 'huanyuan' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote' - touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合事务进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_xid = '487' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 结合LSN号码进行恢复
- 创建归档目录
- mkdir -p /xxx/archive chown -R postgres. /xxx/
- 开启归档，并重启数据库
- wal_level = 'replica' archive_mode = 'on' archive_command = 'cp %p /xxx/archive/%f'
- 重启数据库
- pg_ctl restarat
- 执行全量备份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有变化的数据，可以执行增量备份
- pg_receivewal -D /xxx/backup/ -p 5432
- 启动备份实例，进行数据恢复
- vim postgresql.conf recovery_target_lsn = '0/4011BF8' restore_command='cp /xxx/archive/%f %p' recovery_target_action = 'promote'
touch recovery.signal
- 启动数据库
- pg_ctl -D /xxx/backup start
- 关闭读模式
- select pg_wal_replay_resume();
- 冷备
### 逻辑备份
- 全库备份 - pg_dump
- 库级别备份
- 备份
- pg_dump -U postgres test >test.sql
- 恢复
- psql create database test; psql -U postgres test < test.sql
- 指定格式进行备份
- 备份
- pg_dump -Fc -U postgres test >test.dmp
- 恢复
- psql create database test; pg_restore -d test test.dmp
- 表级别备份
- 备份库下某个模式所有的表
- 备份
- pg_dump -U postgres -t 'schema1.t*' test >test.sql
- 恢复
- psql -U postgres test < test.sql
- 备份单个表
- 备份
- pg_dump -t 表名 数据库名 >dump.sql
- 恢复
- create database 数据库名; psql -U postgres 数据库名< dump.sql
- schema级别备份
- pg_dumpall
- 全库级别备份
- 备份
- pg_dumpall -U postgres > dumpall.sql
- 恢复
- psql -U postgres < dumpall.sql
- pg_dump VS pg_dumpall
- pg_dumpall 是一个用于写出（"转储"）一个数据库集群里的所有PostgreSQL 数据库到一个脚本文件的工具。 - pg_dumpall 调用pg_dump - pg_dumpall 还转储出所有数据库公用的全局对象。这些信息目前包括数据库用户和组，以及适用于整个数据库的访问权限 - pg_dumpall 无法转储"大对象"，因为pg_dump无法把这样的对象转储到纯文本文件中。如果你的数据库里有大对象，那么你应该使用pg_dump 的非文本输出格式之一转储它们。