PostgreSQL基础之如何高效管理服务

一、服务管理

（一）自动服务管理（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

（二）集群实例配置

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、配置参数级别

系统级别 /集群服务级别/实例级别（全局）

用户/角色级别

用户/角色 + 数据库级别

会话级别

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