400-090-9964


博客 | 论坛

教学文章

PostgreSQL基础教程之:初始化配置

时间:2020-04-27 来源:

PostgreSQL基础教程之:初始化配置

一、配置pg_hba.conf

先说明客户端认证配置文件pg_hba.conf

vi $PGDATA/pg_hba.conf


# TYPE DATABASE USER ADDRESS METHOD

local all all 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 # 其它用户密码验证登陆(不太安全)


配置示例:客户端如何连接服务端?

1)需要修改listen_addresses的值为 *

2)修改pg_hba.conf后,使用pg_ctl reload重新读取pg_hba.conf文件

host all all 0.0.0.0/0 md5


注意:

如果pg_ctl找不到数据库,则用-D /.../pgsql/data/ 指定数据库目录

PostgreSQL默认只监听本地端口,用netstat -an|findstr "5866"会看到“tcp 127.0.0.1:5432 LISTEN”。

修改postgresql.conf中的listen_address=*,监听所有端口,这样远程才能通过TCP/IP登录数据库

用netstat -tuln会看到“tcp 0.0.0.0:5866 LISTEN”


二、配置postgresql.conf

配置方式,使用vi编辑器编辑。

vi $PGDATA/postgresql.conf


连接配置参数

listen_addresses = '*' #(关联配置文件pg_hba.conf)

#指定服务器在哪些 TCP/IP 地址上监听客户端连接。

值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。

特殊项*对应监听所有可用 IP 接口

0.0.0.0允许监听所有 IPv4 地址


port = 5866


#服务器监听的 TCP 端口;默认是 5866 。

请注意服务器会同一个端口号监听所有的 IP 地址。

这个参数只能在服务器启动时设置。


max_connections = 100


#决定数据库的最大并发连接数。

默认值通常是 100 个连接,但是如果内核设置不支持(initdb时决定),可能会比这个 数少。

这个参数只能在服务器启动时设置。(cpu 1核 50个链接)


几个内存相关的配置参数:

shared_buffers

它表示数据缓冲区中的数据块的个数,每个数据块的大小是8KB。

数据缓冲区位于数据库的共享内存中,它越大越好,不能小于128KB。

这个参数只有在启动数据库时,才能被设置。

默认值是128MB。

推荐值:1/4 主机物理内存


wal_buffers

用于还未写入磁盘的 WAL 数据的共享内存量。

默认值 -1 表示将该参数值设置为 shared_buffers 的 1/32 的大小 ( 大约 3%),但是不小于64kB 也不大于一个WAL段的大小(通常为 16MB)。

如果自动的选择太大或太小可以手工设置该值,但是任何小于 32kB 的正值都将被当作 32kB。

这个参数只能在服务器启动时设置。

事务日志缓冲区位于数据库的共享内存中。


推荐值:min( 2047MB, shared_buffers/32 ) = 512MB


work_mem

-------------

指定在写到临时磁盘文件之前用于内部排序操作和哈希表的内存量。

ORDER BY, DISTINCT 和合并连接( merge joins) 都会用到排序操作。

默认值为 4 兆字节( 4MB)。


推荐值:work_mem = (输入内存数量- shared_buffers)/(连接数 * 3)* 1024 (单位是 KB);


3G*1024/300 *1024 kB=10240kB


maintenance_work_mem

--------------

它决定数据库的维护操作使用的内存空间的大小。

数据库的维护操作包括VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作。

值如果比较大,通常可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。

maintenance_work_mem存放在每个数据库进程的私有内存中,而不是存放在数据库的共享内存中。


这个参数可以在任何时候被设置。


单位是KB,默认值是16384。

推荐值:如果输入内 存 数 量 >32GB 则 maintenance_work_mem =2GB

否则maintenance_work_mem=输入内存数量的 1/16;


effective_cache_size

设置单个查询可以使用的有效磁盘缓冲区的大小。默认值是128MB。

推荐值:effective_cache_size = 输入内存数量 * 3 / 4;


服务运行日志配置参数:

log_destination = 'stderr' #日志记录类型,默认是stderr,只记录错误输出

logging_collector = on #设置为On时表示收集所有信息写入标准错误输出中

log_directory = 'pg_log' #当logging_collector启用时,在用户数据库下的 pg_log 目录(通常在$PGDATA)中创建日志文件。

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #使用日期和时间命名创建的日志收集文件

log_rotation_age = '1d'; #每天生成一个新的日志文件

log_rotation_size = 0; #不限制单个日志文件大小

log_truncate_on_rotation = on; #覆盖同名文件,只保留7天日志,循环覆盖

log_line_prefix = '%t' #空的字符表示在行开头不添加任何信息。


附:日志输出格式

# %a = application name

# %u = user name

# %d = database name

# %r = remote host and port

# %h = remote host

# %p = process ID

# %t = timestamp without milliseconds

# %m = timestamp with milliseconds

# %i = command tag


log_destination(string)

postgres DB 支持多种记录服务器日志的方法, 包括 stderr, csvlog 和 syslog。

在 Windows 上还支持 eventlog。

把这个选项设置为一个逗号分隔的日志目的地列表。

缺省是只记录到 stderr。

这个选项只能在服务器命令行上或者在postgresql.conf 文件中设置。

如果 csvlog 包含在 log_destination 中, 日志项会以"逗号分隔值" ( CSV) 格式被输出, 这样可以便于向程序中录入日志。


运行日志的作用

1)日志审计

log_statement = ddl


2)定位慢查询sql

log_min_duration_statement = 2s --记录超过2秒的SQL,改完需要reload


3)监控数据库的锁

查看配置

show all; #查看所有数据库参数的值

show shared_buffers; #查看某个参数的当前值

SELECT current_setting('shared_buffers');\

版权所有@北京神脑资讯技术有限公司(CUUG,中国UNIX用户协会) Copyright 2017 ALL Rights Reserved 京ICP备11008061号 京公网110108006275号

CUUG旗下网站:www.cuug.com.cn www.cuug.com oracle.cuug.com bbs.cuug.com bd.cuug.com

电话:010-59426307 010-59426319 邮政编码:100089

海淀校区:北京市海淀区紫竹院路88号紫竹花园4号楼D座703(CUUG)