一、服务管理
（一）自动服务管理（Linux）
将昨日编译安装的pg14数据库添加自启动服务
1、将“源码包/contrib/start-scripts”目录下的脚本重命名后放到/etc/init.d目录下
[root@localhost start-scripts]# cp /postgresql/soft/postgresql-14.12/contrib/start-scripts/linux /etc/init.d/postgres
2、通过下面的命令添加到服务中
[root@localhost init.d]# chkconfig —add postgres
3、查看是否添加成功
[root@localhost init.d]# chkconfig —list
注意：在 Centos 7中 systemctl 是设置系统服务（service）的命令，它融合之前service和chkconfig的功能于一体，昨日安装时已进行演示。
（二）命令行服务管理
通过pg_ctl命令来实现数据库的启动、关闭、加载管理
在线帮助 pg_ctl —help
主要选项
start：启动
stop：关闭
reload：重新加载
restart：重启
pg_ctl stop关闭数据库时，可以通过“-m”参数定义关闭方式
smart：缺省模式，等待所有客户端断开
fast：强制模式，对未断开的客户端进行回滚，类似oracle中的immediate选项
immediate：强制模式，但不回滚，重启时需要自动恢复
关于pg_ctl命令的几个常用参数解释如下：
-D：数据库data目录的路径
-w：等待启动或关闭完成，等待是关闭的默认选项，但不是启动的默认选项
-t：等待时间
-W：不等待命令成功完成
-l：指定服务器日志记录文件
详细参数：
pg_ctl start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o “OPTIONS”]
pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o “OPTIONS”]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
pg_ctl promote [-D DATADIR] [-s]
pg_ctl logrotate [-D DATADIR] [-s]
pg_ctl kill SIGNALNAME PID
二、服务配置
（一）操作系统配置
1、Linux系统内核调整
cat << EOF >> /etc/security/limits.conf
soft nofile 131072 #当前系统生效的打开文件的数目
hard nofile 131072 #系统中所能设定的打开文件的最大值
soft nproc 131072 #当前系统生效的进程的数目
hard nproc 131072 #系统中所能设定的进程数目的最大值
soft core unlimited #当前系统生效的内核文件的大小
hard core unlimited #系统中所能设定的内核文件的最大值
soft memlock 50000000 #当前系统生效的锁定内存地址空间的大小
hard memlock 50000000 #系统中所能设定的的锁定内存地址空间最大值
EOF
sysctl -p #使配置生效
2、Linux资源限制调整
cat << EOF >> /etc/security/limits.conf
soft nofile 131072 #当前系统生效的打开文件的数目
hard nofile 131072 #系统中所能设定的打开文件的最大值
soft nproc 131072 #当前系统生效的进程的数目
hard nproc 131072 #系统中所能设定的进程数目的最大值
soft core unlimited #当前系统生效的内核文件的大小
hard core unlimited #系统中所能设定的内核文件的最大值
soft memlock 50000000 #当前系统生效的锁定内存地址空间的大小
hard memlock 50000000 #系统中所能设定的的锁定内存地址空间最大值
EOF
3、系统防火墙配置（或者直接关闭防火墙）
vi /etc/sysconfig/iptables-config
-A INPUT -s 192.168.59.0/16 -j ACCEPT#允许源IP
-A INPUT -s 192.168.59.0/24 -m state —state NEW -m tcp -p tcp —dport 1922 -j ACCEPT#允许源IP访问目标端口
-A INPUT -p tcp -m state —state NEW -m tcp -p tcp —dport 5432 -j ACCEPT #允许任意IP访问目标端口
4、用户环境配置（.bash_profile）
当安装完成后，用户如果想直接用命令行进行操作，需要进行环境变量的设置
否则直接执行psql、pg_ctl等操作，会提示命令无法找到
以下操作过程结束后，就可以直接使用psql连接数据库了，昨日安装部分已演示。
cat >> ~/.bash_profile <<”EOF”
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg14
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF
source ~/.bash_profile
（二）集群实例配置
| 文件名 | 主要作用 |
| pg_hba.conf | 客户端访问认证文件 |
| postgresql.auto.conf | 保存ALTER SYSTEM修改后的参数、不要手动修改它、优先级较高 |
| postgresql.conf | 主要配置文件 |
| pg_ident.conf | 配置哪些操作系统用户可以映射为数据库用户 |
1、pg_hba.conf
该配置文件有5个参数，分别为：
TYPE（主机类型）、DATABASE（数据库名）、USER（用户名）、ADDRESS（IP地址和掩码）、METHOD（加密方法）
1.1、TYPE（主机类型）
local匹配使用Unix域套接字的连接
如果没有TYPE为local的条目则不允许通过Unix域套接字连接
host匹配使用 TCP/IP建立的连接，同时匹配SSL和非SSL连接
缺省安装只监听本地环回地址localhost的连接，不允许使用TCP/IP远程连接
启用远程连接需要修改postgresql.conf中的listen_addresses参数
hostssl匹配必须是使用SSL的TCP/IP连接
客户端和服务器端都安装OpenSSL
编译PostgreSQL的时候指定configure参数—with-openssl打开SSL支持
在postgresql.conf中配置ssl = on
hostnossl只匹配使用非SSL的TCP/IP连接
1.2认证方式
trust
无条件地允许连接。
允许任何可以与PostgreSQL数据库服务器连接的用户身份登入
不需要口令或者其他任何认证。
reject
无条件拒绝连接。常用于从一个组中“过滤出”特定主机
例如一个reject行可以阻塞特定的主机连接,而后面一行允许特定网络中的其余主机进行连接
md5和password口令认证
md5认证方式为双重md5加密，password指明文密码
不能在非信任网络使用password方式。
peer
从操作系统获得客户端的操作系统用户,并且检查它是否匹配被请求的数据库用户名
只对本地连接可用
其它认证方式
https://www.postgresql.org/docs/current/static/auth-methods.html
1.3、pg_hba.conf-配置示例
vi $PGDATA/pg_hba.conf
local all all trust # 服务端本地用户可信登录
IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host replication replica 0.0.0.0/0 md5 # 流复制用户密码验证登录
host all postgres 0.0.0.0/0 reject # 拒绝超级用户从网络登录
host all all 0.0.0.0/0 md5（昨日安装新添加）
local 备注:
Linux下进程通讯方式有很多,比较典型的有套接字,平时比较常用的套接字是基于TCP/IP协议的,适用于两台不同主机上两个进程间通信, 通信之前需要指定IP地址.
同一台主机上两个进程间通信用套接字,还需要指定ip地址,有点过于繁琐. 这个时候就需要用到UNIX Domain Socket, 简称UDS。
1.4、postgresql.conf配置文件关键参数说明
listen_addresses = ‘‘ #（关联配置文件pg_hba.conf）
指定服务器在哪些 TCP/IP 地址上监听客户端连接。
值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。
特殊项对应监听所有可用 IP 接口0.0.0.0允许监听所有 IPv4 地址
::允许监听所有 IPv6 地址
如果列表为空，服务器将根本不会监听任何 IP 接口，在这种情况中只能使用 Unix 域套接字来连接它。
默认值是localhost，它只允许建立本地 TCP/IP “环回”连接。
这能帮助在不安全网络接口上阻止重复的恶意连接请求。
port = 5432（生产环境中建议改掉默认端口）
服务器监听的 TCP 端口；默认是 5432 。
请注意服务器会同一个端口号监听所有的 IP 地址。
这个参数只能在服务器启动时设置。
max_connections = 100
决定数据库的最大并发连接数。
默认值通常是 100 个连接，但是如果内核设置不支持（initdb时决定），可能会比这个 数少。
这个参数只能在服务器启动时设置。（cpu 1核 50个链接）
shared_buffers
它表示数据缓冲区中的数据块的个数，每个数据块的大小是8KB。
数据缓冲区位于数据库的共享内存中，它越大越好，不能小于128KB。
这个参数只有在启动数据库时，才能被设置。
默认值是128MB。
wal_buffers
用于还未写入磁盘的 WAL 数据的共享内存量。
默认值 -1 表示将该参数值设置为 shared_buffers 的 1/32 的大小 （ 大约 3%），但是不小于64kB 也不大于一个WAL段的大小（通常为 16MB）。
如果自动的选择太大或太小可以手工设置该值，但是任何小于 32kB 的正值都将被当作 32kB。
事务日志缓冲区位于数据库的共享内存中。
work_mem
指定在写到临时磁盘文件之前用于内部排序操作和哈希表的内存量。
ORDER BY， DISTINCT 和合并连接（ merge joins） 都会用到排序操作。
默认值为 4 兆字节（ 4MB）。
推荐值：work_mem = (输入内存数量- shared_buffers)/(连接数 3） 1024 (单位是 KB);
maintenance_work_mem
它决定数据库的维护操作使用的内存空间的大小。
数据库的维护操作包括VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作。
值如果比较大，通常可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。
maintenance_work_mem存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。这个参数可以在任何时候被设置。
2、配置参数级别
系统级别 /集群服务级别/实例级别（全局）
用户/角色级别
用户/角色 + 数据库级别
会话级别
| 参数设置级别 | 参数存储位置 |
| cluster | postgresql.conf or postgresql.auto.conf |
| db | pg_db_role_setting |
| role | pg_db_role_setting |
| db 和 role的组合 | pg_db_role_setting |
2.1、配置文件生效办法
使配置生效的几种方法
1）用超级用户运行
SELECT pg_reload_conf();
2）使用pg_ctl命令触发SIGHUP信号
pg_ctl reload
3）用UNIX的kill手动发起HUP信号
ps -ef|grep -i postmaster|grep -v grep|xargs kill -HUP
4）重启数据库服务
pg_ctl restart
2.2、配置查看方法
通过 SHOW 命令查看特定系统设置
SHOW 命令的输出结果会自动根据数值大小选择合适的单位的所有设置
SHOW ALL 命令，其输出结果会针对每个设置选用合适的单位
—配置查看示例
show all; #查看所有数据库参数的值
show shared_buffers; #查看某个参数的当前值(可查看当前会话值)
select current_setting(‘shared_buffers’);
2.3、配置查看方法
查询 pg_settings 视图可以很方便地检查当前设置
它本质上是SHOW和SET命令的可替换接口
它还提供了SHOW不能提供的关于每一个参数的一些实现，例如最大值和最小值
—配置查看参考SELECT name, setting FROM pg_settings WHERE category = ‘File Locations’;
select name,context,unit,boot_val,setting,reset_val from pg_settings where name in(‘listen_addresses’,’max_connections’, ‘shared_buffers’,’effective_cache_size’,’work_mem’,’maintenance_work_mem’)
order by context, name;
SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
SELECT current_setting(name);
2.4、配置示例-用户角色级修改参数
—查询某参数在某用户级别的设置
create user test password ‘test’;
alter role test set log_min_duration_statement = 100;
—方法一：查询pg_user表
select * from pg_user where usename=’test’;
—方法二：查询pg_db_role_setting表
select * from pg_db_role_setting where setrole in (select usesysid from pg_user where usename in (‘test’))
order by setrole,setdatabase;
—参数在某用户下针对数据库级别的设置
alter role test in database postgres set client_min_messages=’warning’;
select from pg_db_role_setting where setrole in (select usesysid from pg_user where usename in (‘test’)) order by setrole,setdatabase;
*2.5、配置示例-postgresql.auto.conf
运行日志相关配置变更
alter system set loggingcollector = on;
alter system set log_destination = ‘csvlog’;
alter system set log_directory = ‘log’;
alter system set log_filename =’postgresql-%Y-%m-%d%H%M%S.log’;alter system set log_rotation_age = ‘1d’; #每天生成一个新的日志文件
alter system set log_rotation_size = 0; #不限制单个日志文件大小
alter system set log_truncate_on_rotation = on; #覆盖同名文件
alter system set log_hostname = on;
alter system set log_line_prefix = ‘%m’; #控制每条日志信息的前缀格式，默认值是空串
alter system set log_statement = ‘ddl’;
2.6、WAL和归档参数变更
alter system set wal_level = replica;
alter system set archive_mode = on;
alter system set archive_command = ‘’test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f’’;
参数修改完成后重新启动数据库
pg_ctl -m fast stop
pg_ctl start
