PostgreSQL基础教程之:初始化配置
最新学讯:近期OCP认证正在报名中,因考试人员较多请尽快报名获取最近考试时间,报名费用请联系在线老师,甲骨文官方认证,报名从速!
我要咨询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');\