开启日志收集模式
appdb=> \c postgres pgadmin
postgres=# alter system set logging_collector=on;
修改日志存放路径
[postgres@localhost ~]$ su - root
[root@localhost ~]# mkdir /pglog
[root@localhost ~]# chown postgres.postgres /pglog
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql -U pgadmin -d postgres
postgres=# alter system set log_directory ='/pglog';
修改日志格式为csvlog
postgres=# alter system set log_destination = csvlog;
保存近一个月的日志
postgres=# alter system set log_rotation_age = '720h';
按天生成日志文件(根据日志名区分)
postgres=# alter system set log_filename = 'postgresql.%d';
不限制单个日志文件的大小
postgres=# alter system set log_rotation_size = 0;
修改日志记格式:时间戳、用户名、数据库名称、进程ID
postgres=# alter system set log_line_prefix = '%m %u %d %p';
修改数据库监听地址
postgres=# alter system set listen_addresses ='*';
开启全页写
postgres=# alter system set full_page_writes = on;
修改数据库客户端用户密码加密算法为sha-256
postgres=# alter system set password_encryption = 'scram-sha-256';
因为密码的加密算法变了 所以要重新刷新一下密码 防止加密算法不一致 导致登录失败
postgres=# alter user appuser with password '1qaz@WSX';
postgres=# alter user pgadmin with password '1qaz@WSX';
postgres=# alter user readonlyuser with password '1qaz@WSX';
postgres=# alter user postgres with password '1qaz@WSX';
修改数据库本地连接加密算法为sha-256
[postgres@localhost ~]$ vi $PGDATA/pg_hba.conf
禁止超级用户pgadmin通过网络访问 只能本地访问
[postgres@localhost ~]$ vi $PGDATA/pg_hba.conf
允许任意用户通过网络访问
1) 本地访问 (local) ,认证加密方式 scram-sha-256
$PGDATA/pg_hba.conf
local all postgres scram-sha-256
2) 禁止 postgres 通过 tcp/ip 访问实例
host all postgres 0.0.0.0/0 reject
3)不启用 hostssl
host all all 0.0.0.0/0 scram-sha-256
修改最大连接数
postgres=# alter system set max_connections = 120;
postgres=# show superuser_reserved_connections;
刷新参数
postgres=# select pg_reload_conf();