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

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');\