postgresql实例初始化、基本配置

初始化数据库实例

initdb --help
initdb -D /opt/pg12/data -E UTF8 --locale=zh_CN.utf8

数据库连接

vi pg_hba.conf 

host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5 

从上到下的生效顺序
格式
	type database user address method
	以何种方式
		连接哪个库
		连接用户
		连接地址
		认证方式
		连接方式
	local
		匹配使用Unix域套接字的连接
	host
		匹配使用 TCP/IP建立的连接，同时匹配SSL和非SSL连接
	hostssl
		匹配必须是使用SSL的TCP/IP连接
	hostnossl
		只匹配使用非SSL的TCP/IP连接

认证方式

认证方式	描述
Trust	无条件地允许连接，不需要口令或者其他任何认证
Rejest	无条件拒绝连接，常用于从一个组中“过滤出”特定主机,例如一个reject行可以阻塞一个特定的主机连接，而后面一行允许一个特定网络中的其余主机进行连接
Md5	双重md5加密
Password	明文加密，注意安全
其他	https://www.postgresql.org/docs/current/static/auth-methods.html

参数配置

操作系统修改postgresql.conf文件
操作系统修改postgresql.auto.conf文件

启动时指定参数
	psql -c
sql语句修改
	alter system
	alter database

……

参数生效方式，http://postgres.cn/docs/11/view-pg-settings.html
重启数据库
重新载入数据库配置文件
pg_ctl -D $PGDATA reload
/etc/init.d/postgresql-11.x reload   (el6)
systemctl reload service.postgresql-11.x   (el7)
sql语句
select pg_reload_conf();

参数查看

格式	命令
pg_settings 系统表	select * from pg_settings
current_setting() 函数	select current_setting(name)
show all/name	show all/shared_buffers

参数优先级

postgresql.conf
postgresql.auto.conf
command line options
all role
database
role
session
transaction

初始化后基本优化

listen_addresses = '*'
max_connections = '2000'
superuser_reserved_connections = '10'
shared_buffers = ‘1024MB‘    -- ¼物理内存，一般不超过16GB
wal_buffers = '16MB‘

log_destination = 'csvlog'
logging_collector = 'on'
log_line_prefix = '%m - %a - %u - %d - %p: '

standard_conforming_strings = 'on'
escape_string_warning = 'off'

archive_mode = 'on'    -- 备库日志归档always
archive_command = '/bin/ture'

max_parallel_workers_per_gather = '0'
max_parallel_workers = '0'
log_min_duration_statement = ‘1000‘   ---超过1秒的sql记录到日志

random_page_cost = 1.1  ssd 
alter system set hot_standby = on ;
alter system set max_wal_size = '15GB';   防止备库失效
alter system set min_wal_size = '10GB';   防止备库失效
wal_keep_segments --老版本参数  不需要使用了。个数 16M一个文件。跟大小 2个参数都需要同时满足。

alter system set wal_level = 'replica';
alter system set full_page_writes = on;
alter system set tcp_keepalives_idle = 10;
alter system set tcp_keepalives_interval = 10; 
alter system set tcp_keepalives_count = 3; 
alter system set checkpoint_timeout = 1200;
alter system set effective_cache_size = ‘4GB’;      -- 物理内存-shared_buffers-用户连接数*work_mem
set maintenance_work_mem= 512M   --建索引，执行维护作业时临时设置

用户权限控制

描述	value
建用户	create user/role
授权	grant
除权	revoke
查看权限	select * from INFORMATION_SCHEMA.role_table_grants;

…
PG12 新特性
文章推荐：
https://postgres.fun/20190809161300.html