postgres优化项及linux上pg操作记录

1、linux切换到pg命令：

$ su - postgres
$ psql
postgres=#

2、查看/退出pg

ps -ef |grep postgres
postgres=# q

3、一般优化项

参数名	作用	64GB建议值	256GB建议值
shared_buffers	设置PostgreSQL中用于缓存的专用内存量。建议的设置值为机器总内存大小的25％。	16GB	64GB
wal_buffers	WAL（预写日志）记录缓冲区，由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。	32MB	64MB
max_wal_size	WAL（预写日志）,增大max_wal_size可以延长checkpoint时间间隔	10GB	10GB
effective_cache_size	用于磁盘高速缓存的内存量的估计值。更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。	32GB	128GB
work_mem	用于复合排序，全局设置此参数可能会导致内存使用率过高，强烈建议在会话级别修改此参数。	4MB	16MB
maintenance_work_mem	用于维护任务的内存设置。	64MB	256MB
checkpoint_completion_target	在两个checkpoint之间多长时间内完成刷盘动作，数值越小对磁盘IO能力要求越高	0.8	0.8

4、查看现有参数值

postgres=# show effective_cache_size；

动态修改现有参数：

alter system set effective_cache_size='16GB'；(单位大写字母，单引号)

动态修改参数后生效：

postgres=# select pg_reload_conf();

以上优化项中shared_buffers需要重启pg，其他的修改完后直接执行一次  select pg_reload_conf(); 即可
5、快速停止，启动，重启pg

#su - postgres -c "/usr/pgsql-9.6/bin/pg_ctl stop -m fast -D /opt/pgsql/9.6/data"
#su - postgres -C "/usr/pgsql-9.6/bin/pg_ctl start -m fast -D /opt/pgsql/9.6/data"
#su - postgres -C "/usr/pgsql-9.6/bin/pg_ctl restart -m fast -D /opt/pgsql/9.6/data"
-D /opt/pgsql/9.6/data表示pg中数据存储所在的路径

 6、查看长事务

SELECT * FROM pg_stat_activity

WHERE STATE <> 'idle'

        AND pg_backend_pid () != pid

        AND ( backend_xid IS NOT NULL OR backend_xmin IS NOT NULL )

        AND EXTRACT (epoch FROM ( now() - xact_start )) > 60

ORDER BY xact_start;　　

杀掉长事务会话：select pg_terminate_backend(pid)；

7、杀掉空闲语句

select pg_terminate_backend(pid) from pg_stat_activity where state='idle';