【数据库学习】Postgres(PG)安装与运维

1. pg安装与运维
2. Postgres原理及底层实现
3. 基础语法
4. SQL优化
5. 中文文档

1，安装与配置

#安装
yum install https:....rpm

1）安装目录

bin目录：二进制可执行文件目录，此目录下有postgres、psql等可执行程序；pg_ctl工具在此目录，可以通过pg_ctl --help查看具体使用。
conf目录：
empty
include：头文件目录
lib：动态库目录，如libpq.so
share：存放文档和配置模板文件，一些扩展包的sql文件在子目录extension下。

2）数据目录

/var/lib/pgsql/<verson>/data。

i>pg_hba.conf（认证配置文件）

用于配置数据库的远程连接，通过加入以下命令行，运行任何用户远程连接本数据库，连接时需要提供密码。

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表示无条件地允许联接。

ii>postgresql.conf（主配置文件）

所有配置信息在系统视图pg_settings中可查看，通过context可知修改相关配置后是否需要重启。

1. internal 只读参数，初始化实例时写死的。
2. postmaster 改变后需要重启。
3. sighup/backend 不需要重启，需要向postmaster主进程发送SIGHUP信号，让其重启装配配置新的参数。运行pg_ctl reload命令重新装配。
4. superuser 超级用户使用set命令改变。
5. user 普通用户使用set命令改变。

## 连接配置项
listen_addresses = '*'          # 默认为localhost，这会导致远程主机无法登录数据库。写具体网络ip表示让特定机器登录，*表示所有地址都可监听。
port = 5432                             # pg默认端口为5432。多个pg实例可以设置不同端口。
max_connections         #允许数据库连接的最大并发连接数，默认100，修改后需要重启。通过sql：show max_connections;也可以查看
superuser_reserved_connections  #超级用户连接数。默认为3，为防止普通用户消费连接数过多导致超级用户无法连接pg。

## 日志配置项
logging_collector = on    #开启日志收集。pgSQL10已经默认开启
log_directory = 'log'     #日志目录。日志切换与覆盖有多种方案，可配。一是每天生成一个新的日志文件；二是日志写满到一定大小开启新文件；三是只保留最近7天日志，循环覆盖（pgSQL10默认模式)。

## 内存配置项
shared_buffers = 4096MB                 # min 128kB  共享内存大小，主要用于共享数据块。默认值为32MB，尽量设置大一些。具体说明见后文讲解。
work_mem = 4MB                         # min 64kB 单个SQL执行、排序、Hash Join时使用的内存，执行完毕后释放。设置大一些会提高排序操作效率。具体说明见后文讲解。
max_stack_depth    #服务器执行堆栈的最大安全深度，默认为2M。如果发现不能运行复杂的函数时，可以调高此配置，但一个正在运行的递归函数可能会导致pg后台服务进程崩溃，慎重设置。

iii>其它文件

文件/目录	作用	备注
PG_VERSION	pg版本号
postmaster.opts	记录服务器上次启动的命令行参数
base	默认表空间的目录	下面子目录以对应数据库的OID命名，对应OID子目录下存放着这个数据库的表、索引等数据文件。OID通过select oid,datname from pg_database;查询。
global	一些共享系统表的目录
log	程序日志目录	pg10版本之前未pg_log目录
pg_commit_ts	视图提交的时间戳数据	pg9.5之后
pg_wal	WAL日志的目录，在pg10之前此目录是pg_clog

oid（objectID，行对象标识符）
该系统字段只有在创建表时使用了with oids或配置参数default_with_oids的值为真时出现。字段类型也是oid，是4字节无符号整数，不能提供大数据范围内的唯一性保证，因此pg官方不推荐在用户表中使用oid字段。

3）修改密码

1>知道密码

navicat执行，修改后不需要重启。

ALTER USER postgres WITH PASSWORD 'xxx';

2>不知道密码

1. 修改md5验证方式为trust（主从结点都要修改）

vi /opt/test/data/postgresql/pg_hba.conf  

2. 重启pg（主结点执行）

ansible -i /opt/test/etc/service/hostlist all -b -m shell -a "/opt/test/bin/shell/stop-postgresql.sh"
ansible -i /opt/test/etc/service/hostlist all -b -m shell -a "/opt/test/bin/shell/start-postgresql.sh"

3. 修改pg密码（主从结点都要执行）

/opt/test/share/postgresql/bin/psql -U postgres -p 5432 -h 127.0.0.1
 
ALTER USER postgres WITH PASSWORD '1223456';
 
Ctrl + Z 退出

4. 修改密码文件（主从结点都要执行）

echo '123456' > /opt/test/etc/pwd/DB_PASS
echo '123456' > /opt/qax/pwd/DB_PASS

5. 修改pgpool密码文件为：123456（主从结点都要执行）

文件	位置	备注
/opt/test/share/pgpool/etc/pgpool.confr	health_check_password
/opt/test/share/pgpool/etc/pgpool.conf	health_check_password
/opt/test/etc/service/input-params.yaml	db.password
/opt/test/share/postgresql/.pcppass	test77.cn:9898:postgres:aptsRGX aptsRGX 替换为123456
/opt/test/share/postgresql/.pgpass	test77.cn:9898:postgres:aptsRGX aptsRGX 替换为123456
/opt/test/share/pgpool/etc/pcp.conf	postgrest 替换为md5密码	echo -n ‘123456’ | md5sum | cut -d ’ ’ -f1
test/opt/t/etc/pgpool/pcp.conf	postgrest 替换为md5密码

6. 修改pgpool密码（主从结点都要执行）

sudo su postgres -c "/opt/test/share/pgpool/bin/pg_md5 -m -u postgres -f /opt/test/share/pgpool/etc/pgpool.conf 123456"

7. 重启pgpool（主节点执行）

ansible -i /opt/test/etc/service/hostlist all -b -m shell -a /opt/test/bin/shell/stop-pgpool.sh
 
ansible -i /opt/test/etc/service/hostlist all -b -m shell -a /opt/test/bin/shell/start-pgpool.sh
 
ansible -i /opt/test/etc/service/hostlist all -b -m shell -a /opt/test/bin/shell/register_pgpool.sh

8. 重启所有服务（主结点执行）

2，psql命令行操作

# 数据库连接： 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

常见psql快捷命令（通过psql连接数据库后，通过“\”开头的快捷命令进行数据库相关操作，tab键可补全命令）：

说明	命令	备注
退出命令行模式	\q
查看数据库	\l	小写L
切换数据库	\c dbName
查询当前登录的数据库和用户	\c	You are now connected to database “postgres” as user “postgres”
查看sql语法（help）	\h create user
查看更多命令	?
查看所有表	\d
查看结构	\d name	name可以包含通配符*或?，可以是表名、索引、视图、序列、函数。如果使用\d+会显示的更详细
列出所有schema	\dn
查看所有表空间	\db
查看所有角色和用户	\du或\dg	pg中用户和角色是不区分的
查看表权限分配情况	\dp或\z
查看执行时间	\timing on sql语句
指定客户端字符编码	\encoding gbk	utf8
执行外部文件的sql命令	\i fileName 或 psql -x -f fileName
编辑器	\e	类似vi，退出vi后会执行其中输入的内容
查看或编辑函数	\ef 函数名	不加函数名显示函数模板。退出vi后可\reset来清除命令缓冲区数据，防止误操作。
查看或编辑视图	\ev 视图名	不加函数名显示视图模板。退出vi后可\reset来清除命令缓冲区数据，防止误操作。

3，系统命令

1）查看版本信息

说明	命令
查看客户端版本	psql --version
查看服务器版本详细信息	select version();
查看服务器版本信息	show server_version;
查看服务器数字版本信息包括小版号	SHOW server_version_num;

2）时间相关命令

说明	命令	备注
查看数据库启动时间	select pg_postmaster_start_time()
查看最后load配置文件的时间	select pg_conf_load_time()	pg_ctl reload后改变这个时间
显示当前数据库时区	show timezone	时区不一样的情况下，数据库时间和操作系统时间不一致。;PRC： People’s Republic of China
查看当前时间	select now()
设置时区	set time zone ‘GMT’	PRC为北京时区
查看所有时区的名字	SELECT * FROM pg_timezone_names

3）其它常用命令

说明	命令	备注
当前连接数据库	select current_catalog, current_database();	两者结果一样
查看pg是否正在做基础备份	select pg_is_in_backup(),pg_backup_start_time();
查看数据库大小	select pg_database_size('ngsoc'), pg_size_pretty(pg_database_size('ngsoc'));	pg_size_pretty会转换成MB\GB等格式展示

4，服务启停相关

1）服务启动

1. 直接运行postgres进程

/lwh/postgresql/bin/postgres -D /lwh/data/postgresql &   #-D指定数据目录,&表示后台执行。postgres也可以换成postmaster，一回事。如果权限不够在命令前面添加：su postgres -c 

2. 使用pg_ctl命令启动

/lwh/postgresql/bin/pg_ctl start -D /lwh/data/postgresql  #-D指定数据目录

2）服务停止

1. 直接向运行的postgres主进程发送signal信号

signal信号	关机模式	描述
SIGTERM	Smart Shutdown 智能关机	服务器将不允许新连接，等所有连接断开才关闭数据库
SIGINT	Fast Shutdown 快速关机	不再允许新连接，并向所有子进程发送 SIGINT 信号，让它们立刻退出，然后等待子进程退出后关闭数据库
SIGQUIT	Immediate Shutdown 立即关闭	立即关闭并退出，下次启动时数据库重放WAL日志进行恢复。仅用于紧急情况的关闭。

2. 使用pg_ctl命令停止数据库

#没有权限需要在最前面添加su postgres -c
pg_ctl stop -D dataDir -m smart  #对应Smart Shutdown 模式；fast对应 Fast Shutdown；immediate 对应 Immediate Shutdown.具体说明可通过--help查看。默认模式是哪个？谁知道啊？？？

3）服务检测是否启动

ps aux | grep /lwh/data/postgresql | grep -v grep | wc -l    #返回值不为0表示服务存在
ps aux | grep /lwh/data/postgresql | grep -v grep | awk '{print $2}' #返回具体的pid表示服务存在
或者：
netstat -ntlp | grep 5432  #5432为pg默认端口

4）pg_prewarm插件：pg重启时加载缓存

1. 场景：手动加载经常访问的表到操作系统的cache或PG的shared buffer，从而减少检查系统重启对应用的影响；
   执行1次select * from 不会将表的数据读入shared buffer和OS cache：实际上，对于较大的表（大小超过shared buff的1/4），进行全表扫描时，PG认为没必要为这种操作使用所有shared buffer，只会让其使用很少的一部分buffer，一般只有几百K。

5，备份与还原

分为逻辑备份和物理备份。

1）pg_dump/pg_dumpall命令

pg_dumpall是将一个pg集群全部转存到另一个脚本文件（sql脚本、归档文件）中，而pg_dump命令可以选择一个数据库或部份表进行备份。pg_dump结合pg_restore使用，能灵活备份和恢复。

2）数据库备份分类

1>冷备 (Cold Standby)

定期对生产系统数据库备份，并将备份数据 存储在磁带、磁盘等介质上。备份的数据平时处于一种非激活的状态，直到故障发生导致生产数据库系统部可用时才激活。冷备数据的时效性取决于最近一次的数据库备份。数据库冷备的周期一般较长。

最简单的物理备份就是冷备份，即：停止pg，然后拷贝pg的data目录。

2>热备 (Warm Standby)

定期将数据备份到Standby db，当发生故障时，通过备用数据库的数据进行业务恢复。
主要是通过不断将WAL日志回放，加载到备份数据库来实现的。
时效性来自最近一次备份。

常见的方法有：PITR方法、使用文件系统或块设备级别的快照功能完成备份。Linux下最简单的备份方式是使用LVM的快照功能。

3>完全热备 (Hot Standby)

Standby db和master db完全同步，Standby 提供只读服务（pg9.x提供）.

6，主从同步

逻辑解码（Logical Decoding）原理

1）主备同步wal相关配置

参数名	说明	优化方案
synchronous_commit	如果双节点，设置为ON，如果是多副本，同步模式，建议设置为remote_write。如果磁盘性能很差，并且是OLTP业务。可以考虑设置为off降低COMMIT的RT，提高吞吐(设置为OFF时，可能丢失部分XLOG RECORD)
full_page_writes	如果文件系统支持COW例如ZFS，则建议设置为OFF。 如果文件系统可以保证datafile block size的原子写，在对齐后也可以设置为OFF。
wal_writer_delay	wal写的延迟	缩短延迟，加快wal写的速度
wal_writer_flush_after
checkpoint_timeout	不建议频繁做检查点，否则XLOG会产生很多的FULL PAGE WRITE(when full_page_writes=on)	提高该值，降低FULL PAGE WRITE，较少XLOG，降低wal压力
max_wal_size	建议等于SHARED BUFFER，或2倍。
min_wal_size	建议是SHARED BUFFER的2分之一
wal_receiver_status_interval	反馈给主节点自己已经接受（ replies ）到数据信息。	减小，加快反馈速度
wal_buffers	基于shared_buffers，shared_buffers/32	增大，加快wal落盘速度

2）主从混乱

--pg排查：确定当前是否有主从混乱问题 如果role是一master多standby说明正常
show pool_nodes;

node_id	hostname	port	status	lb_weight	role	select_cnt	load_balance_node	replication_delay	last_status_change
0	xx.cn	5432	up	1.000000	primary	200706	true 0	2020-03-13 12:36:59

其中：
status：pgpool状态 （up、down）
role：primary （主节点） standby（从节点）
replication_delay：复制延迟。如果延时过大需要重建备库。

解决：

/postgresql/postgresql.conf位置下修改配置：
max_wal_size = 16GB
min_wal_size = 4GB

--登录主库，数据库重新加载配置
select pg_reload_conf();

3）备库激活

重建备库：

/postgresql/recovery.conf文件修改：(有这个文件的表示是从结点，主结点文件为recovery.done)
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=postgresql.host port=5432 user=repuser password=xxx'
--重启数据库、重启pgpool即可