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

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 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

(二)集群实例配置

文件名 主要作用
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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
6月前
|
存储 JSON 关系型数据库
轻松入门MySQL:MySQL字段类型精解,优化存储结构,助力系统高效运行(2)
轻松入门MySQL:MySQL字段类型精解,优化存储结构,助力系统高效运行(2)
|
6月前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:视图之美,简化查询、提升效率的数据库利器(14)
轻松入门MySQL:视图之美,简化查询、提升效率的数据库利器(14)
257 0
|
6月前
|
SQL 存储 关系型数据库
轻松入门MySQL:数据库关联与多表查询,构建高效的业务决策引擎(6)
轻松入门MySQL:数据库关联与多表查询,构建高效的业务决策引擎(6)
163 0
|
3月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
45 3
|
2月前
|
存储 缓存 关系型数据库
MySQL 视图:数据库中的灵活利器
视图是数据库中的虚拟表,由一个或多个表的数据经筛选、聚合等操作生成。它不实际存储数据,而是动态从基础表中获取。视图可简化数据访问、增强安全性、提供数据独立性、实现可重用性并提高性能,是管理数据库数据的有效工具。
|
3月前
|
存储 关系型数据库 MySQL
"深入探索MySQL临时表:性能优化利器,数据处理的灵活之选"
【8月更文挑战第9天】MySQL临时表专为存储临时数据设计,自动创建与删除,仅在当前会话中存在,有助于性能优化。它分为本地临时表和全局临时表(通过特定逻辑模拟)。创建语法类似于普通表,但加TEMPORARY或TEMP关键字。适用于性能优化、数据预处理和复杂查询,需注意内存占用和事务支持问题。合理使用可大幅提升查询效率。
212 2
|
6月前
|
SQL 关系型数据库 MySQL
【MySQL】:探秘主流关系型数据库管理系统及SQL语言
【MySQL】:探秘主流关系型数据库管理系统及SQL语言
364 0
|
6月前
|
存储 关系型数据库 MySQL
【MySQL探索之旅】数据库设计以及聚合查询
【MySQL探索之旅】数据库设计以及聚合查询
|
6月前
|
存储 SQL 关系型数据库
MySQL基础『数据库基础』
MySQL基础『数据库基础』
66 1
|
SQL 存储 Oracle
使用 NineData 高效编写 SQL
作为与数据库交互如此重要的SQL,如何准确、高效的编写正确的SQL语句得以运行,很大程度上将与研发效率直接挂钩。NineData在SQL窗口中实现了多个功能,帮助大家更便捷的书写SQL与数据库进行交互。
605 0
使用 NineData 高效编写 SQL