刘刚刚的个人博客

postgreSQL的服务管理与配置

创建时间:2021-07-07 21:58:49
更新时间:2021-07-07 21:58:49

本文主要介绍了pgsql数据库的参数配置及生效方式.

概述

pgSQL数据库的服务具有以下特点:

  • 多数操作系统上,pgSQL是被作为一种服务(或叫守护进程)来安装的
  • 多个pgSQL服务可以运行在同一台服务器上
  • 不同的pgSQL服务之间侦听的端口不能重复,也不能共享同一个数据存储目录
  • 多数情况下,在一台服务器上只会安装一个pgSQL服务
  • 一个pgSQL服务可以包含多个独立的database

pg_ctl

start:启动

stop:关闭

​ -m 参数,可以用来指定断开的方式。

​ smart: 等到所有客户端断开后再关闭,且期间不允许新的用户连接,同时也会等待正在进行的备份完成。

​ fase: 默认方式,立即关闭所有连接,并对未断开的连接进行回滚操作,如果服务器处于备份状态,则终止此次备份。

​ immediate:强制模式,立即中止服务的所有进程,因此重启时会导致服务会使用wal进行恢复。

reload:重新加载

restart: 重启

statu: 查看服务的状态

服务配置

pgSQL常见的配置文件及作用(位于cluster的目录下):

文件名主要作用
pg_hba.conf客户端访问认证文件
postgresql.auto.conf保存ALTER SYSTEM修改后的参数不要手动修改它优先级较高
postgresql.conf主要配置文件
recovery.conf基于wal日志恢复的配置文件
pg_ident.conf配置哪些操作系统用户可以映射为数据库用户

pg_hba.conf

# TYPEDATABASEUSERADDRESSMETHOD
localdatabaseuser auth-method [auth-options]
hostdatabaseuseraddressauth-method [auth-options]
hostssldatabaseuseraddressauth-method [auth-options]
hostnossldatabaseuseraddressauth-method [auth-options]
hostdatabaseuserIP-address IP-maskauth-method [auth-options]
hostssldatabaseuserIP-address IP-maskauth-method [auth-options]
hostnossldatabaseuserIP-address IP-maskauth-method [auth-options]

type

  • local:本地用户,将使用unix套接字连接
  • host:远程用户,tcp/ip连接,ssl无论是否开启都可以
  • hostssl:远程用户,tcp/ip连接,且开启ssl
  • hostnosll:远程用户,tcp/ip连接,且不开启ssl

database:可以是数据库名称或者replication(流复制)

ADDRESS:

​ 例如: 192.168.1.10/32 代表只允许 192.168.1.10这台机器访问

​ 192.168.1.0/24 代表只允许 192.168.1.x 的机器访问

method:代表认证的方式,常见的认证方式包括:

  • trust:无条件连接,不需要口令
  • reject:无条件拒绝连接
  • MD5:密码通过md5加密
  • password:明文口令
  • 其他.....ident,peer,cert等

postgresql.conf

配置介绍
listen_addresses = '*'默认为localhost,只允许本地连接<br/>0.0.0.0 代表所有ipv4地址<br/>:: 代表所有ipv6地址
port=5432端口,默认为5432
max_connections=100最大连接数(建议一个cpu核心50个链接)
shared_buffers数据缓冲区的内存大小,默认为128M,,建议为内存的1/4,如果超过内存的40%可能会因为文件系统的缓存导致双缓存过多,从而影响性能
wal_buffers还未写入磁盘的 WAL 数据的共享内存量,默认为-1,代表为shared_buffers的1/32
work_mem写到临时磁盘文件之前用于内部排序操作和哈希表的内存量(单会话所占用),如果超过该量,则系统会使用临时文件.work_mem = (输入内存数量- shared_buffers)/(连接数 3) 1024 (单位是 KB);
maintenance_work_mem维护数据库时(VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作),占用的内存大小. 设置的较大会提高VACUUM数据库和从dump文件中恢复数据库的速度,且此类操作很少执行,一般建议设置大店,但如果设置了 auto vacuum ,每次触发都会分配内存,因此也不建议设置太大. 最好是在每次进行操作时,使用set 命令临时调整该参数的大小.

参数的级别

参数设置级别参数存储位置
clusterpostgresql.conf or postgresql.auto.conf
dbpg_db_role_setting
rolepg_db_role_setting
db 和 role的组合pg_db_role_setting

参数修改的方式(全局)

  1. 编辑配置文件
  2. 启动时设置(不推荐)
  3. psql -c [参数]=[值]
  4. ALTER SYSTEM 语句(修改的是postgresql.auto.conf)

    ALTER SYSTEM SET 配置参数 { TO | = } { 值 | '值' | DEFAULT }
    ALTER SYSTEM RESET 配置参数
    ALTER SYSTEM RESET ALL 

配置修改的方式(非全局)

配置生效级别修改方式
DatabaseALTER DATABASE name SET configparameter { TO \= } { value \DEFAULT }ALTER DATABASE name RESET configuration
Session通过SET命令设置当前Session的配置SET configparameter { TO \= } { value \'value' \DEFAULT } SET configparameter TO DEFAULT;
更新pg_settings视图UPDATE pg_settings SET setting = new_value WHERE name = 'configparameter';
使用set_config函数更新会话配置 SELECT set_config('configparameter',new_value,false);
user/RoleALTER ROLE name [ IN DATABASE database_name ] SET configparameter { TO \= } { value \DEFAULT } 重置/取消这些参数的设置ALTER ROLE name [ IN DATABASE database_name ] RESET configparameter ALTER ROLE name [ IN DATABASE database_name ] RESET ALL;

参数的类型

参数主要包括以下6种

Context(上下文)生效方式
sihup给服务器发送HUP信号会使服务器重新加载postgresql.conf配置,可以立即生效
postmaster只有服务重启才能生效
internal编译期间的设置,只有重新编译才能生效。
backend与sighup类似,但是不影响正在运行的会话,只在新会话中生效
superuser使用superuser(如postgres)才能更改,不用重新加载所有配置即可生效
user单个会话用户可以在任意时间做修改,只会影响该会话

配置生效的方式

  1. 重启生效

    pg_ctl restart

  2. 重载

    pg_ctl reload

  3. 由超级用户运行SQL

    SELECT pg_reload_conf()

配置的查看方式

  1. show命令

    show all;                       #查看所有数据库参数的值
    show shared_buffers;   #查看某个参数的当前值(可查看当前会话值)
    select current_setting('shared_buffers');
  2. 通过查询 pg_settings

    SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
    SELECT current_setting(name);

image-20210707215436691

我的名片

昵称:shuta

职业:后台开发(python、php)

邮箱:648949076@qq.com

站点信息

建站时间: 2020/2/19
网站程序: ANTD PRO VUE + TP6.0
晋ICP备18007778号