内容简要：
一、操作系统优化
二、数据库配置优化
三、日常操作
四、运维方案
一、操作系统优化
操作系统优化主要从共享内存、参数设置、使用大页、信号量和Limit参数等方面阐述。
（一）共享内存
当用户使用PostgreSQL 9.3或GreenPlum5及以前的版本，为防止数据库使用Swap，需先设置下面两个参数：
Ø kernel.shmmax = 16724692992
Ø kernel.shmall = 4083177
从PostgreSQL 9.3开始，数据库不再大量使用Sysv类型的共享内存，主要使用MMAP类型的共享内存，因此上面两个参数设置一个较小的值，数据库也可以启动。
（二）参数设置
1. vm.swappiness=0
在数据库中需尽量避免使用Swap，因此将该参数值设置为0。
2. Overcommit参数
通常情况下设置vm.overcommit_memory=2，即不让系统超申请。通常情况下该值为0，表示申请的内存可以超过物理机内存。当大家都开始使用的时候，则会发生OOM，将一些进程给Cut掉，这在数据库中是比较危险的情况，因此建议将vm.overcommit_memory设为2。
除此之外，需要设置vm.overcommit_ratio= 90，需要根据实际情况设置。
当设置完这两个值后，可以申请的内存不超过：
swap的大小 + 物理内存* vm.overcommit_ratio
例如：一个256G内存的机器，16G Swap，应该把vm.overcommit_ratio= 93，这样256*95%+16=254G，内存申请不可超过254G，如果超过的话则申请失败。
（三）大页
1.为什么要使用大页
使用大页是因为页表问题的存在，使用小页会存在页表占用过多内存的问题。
假设一台256G的机器，我们分配了共享内存为128G。如果是小页，大小为4K，则有33554432页表项，每项至少占用4字节，则页表大小32M*4=128M，如果有1024个连接，则页表占用128M*1024=128G内存，占据机器总内存的一半。
如果使用2M大小的大页表，则：则有128G/2M=65536项，65536*4=256K，1024个连接：1024*256k=256M内存，内存占用率大幅降低。
通常在 Linux操作系统里面，建议使用大页。
2.大页配置
大页参数设置：vm.nr_hugepages；
这个参数设置的值为多少，则有对应数量的2M大页。大页的大小需要与数据库的Shared_buffer相一致，如果比Shared_buffer大很多则会浪费资源。
大页不会被Swap，默认Lock，即类似Oracle的lock_sga，且分配大页内存后，及时不使用大页，也不可做其他用途。
（四）信号量
PostgreSQL数据库是多进程数据库，进程和进程之间访问同一个共享内存时，需要各种各样的“锁”机制，通常信号量指的就是进程之间的“锁”。需要设置kernel.sem=20 13000 20 650，参数的4个数据对应：SEMMSL、SEMMNS、SEMOPM、SEMMNI。
l SEMMSL：信号集的最大信号量数，PostgreSQL要求大于17，取整数20， Oracle要求是250。
l SEMMNS：整个系统范围内的最大信号量数，所以SEMMNS = SEMMSL *SEMMNI。
l SEMOPM：Semop函数在一次调用中所能操作一个信号量集中最大的信号量数，所以能常与SEMMSL相同。
l SEMMNI：信号量集的最大数目，PostgreSQL数据库中要求是数据库进程数/16，假设允许10000个连接，即需要至少625，取一个整数650。这个进程数不只是用户服务进程，还需要包括一些管理的服务进程，如Autovacuum的Work进程。
（五）Limit参数
1./etc/security/limits.conf（软/硬限制一样）:
Ø soft nofile 65536
Ø hard nofile 65536（打开文件的值）
Ø soft nproc 131072
Ø hard nproc 131072（进程数）
Ø soft memlock -1
Ø hard memlock -1 （内存）
2./etc/security/limits.d/20-nproc.conf
Ø soft nproc 131072
当设置了“/etc/security/limits.d/20-nproc.conf”时，参数有时候不一定生效，因为在不同的机器中可能还有个Limits.d，下面有个配置文件优先级比limits.d/20-nproc.conf高，有些机器不一定是20，可能是其他的值。此时要将值设高一些，然后检查底下limits.d下面这篇文件中是否设置，如果没有则要把这个值设高，如果设低的话，limits.d.conf里设高也没有用。
二、数据库配置优化
数据库主要包含以下几个参数：
l Shared_buffer
1）小内存(32G)的机器上配置4GB~8GB即可；
2）小内存的机器（>32G)，配置8GB即可。
通常Shared_buffer配置4GB~8GB即可。PostgreSQL是使用这个文件缓存做的，如果Shared_buffer设大，缓存有两份。
l Work_mem
1）通常保持默认的4MB即可；
2）如果机器内存很多，可以设置为64MB，通常不要太大，防止发生OOM。
l Maintenance_work_mem:
可以在Session级别设置，当手工建索引或Vacuum慢时，可以把这个参数在Session级别调大。
l Wal_buffers
通常保持默认值-1即可，-1表示会自动根据shared_buffer的大小而自动设置一个合适的大小，最大不要超过WAL文件的大小，如16MB。
l Max_connections
可以设置的大一些，如5000，因为修改这个参数需要重启机器。
时间上还有很多的其他参数，如一些超时参数，防止长时间发呆的连接，防止长时间发呆的事务等，具体详情可关注PostgreSQL中文社区的培训认证考试PCA、PCP、PCM。
三、日常操作
数据库日常操作及对应语句：
l 查看数据库版本
select version();
l 查看数据库的启动时间
select pg_postmaster_start_time();
l 查看最后load配置文件的时间
select pg_conf_load_time();
l 显示数据库时区
show timezone;
l 查看有哪些数据库
psql –l
l 查看当前用户
select user; select current_user, session_user;
current_user, session_user指不带括号的函数。
l 查看当前连接的数据库名称
select current_catalog, current_database ();
l 查看当前客户端的IP及端口
select inet_client_addr(), inet_client_port();
l 查看当前数据库服务器的IP及端口
select inet_server_addr(), inet_server_port();
l 查询当前session的后台服务进程的pid
select pg_backend_pid();
l 查看参数配置
1）show shared_buffers
2）select current_setting('shared_buffers');
l 查看当前正在写的WAL文件
1）9.X: select pg_xlogfile_name(pg_current_xlog_location());
2）>=10版本： select pg_walfile_name(pg_current_wal_lsn ());
l 查看当前WAL的buffer还有多少字节没有刷到磁盘中
9.X:selectpg_xlog_location_diff(pg_current_xlog_insert_location(), pg_current_xlog_location());
>=10版本: : select pg_wal_lsn_diff(pg_current_wal_insert_lsn (), pg_current_wal_lsn ());
l 查看数据库实例是否正在做基础备份
select pg_is_in_backup(), pg_backup_start_time() ;
l 当前数据库实例是Hot Standby状态还是正常数据库状态
1）select pg_is_in_recovery();
如果是备库显示true，否则是主库。
2）pg_controldata |grep state
指控制文件，在生产情况“in production”情况下是主库，在恢复状态下是备库。
l 查看数据库的大小
select pg_database_size('osdba')；
l 查看表的大小
1）select pg_size_pretty(pg_relation_size('ipdb2')) ;
2）select pg_size_pretty(pg_total_relation_size('ipdb2')) ;
l 查看某个表上索引的大小
1）select pg_size_pretty(pg_indexes_size('ipdb2'));
2）“ipdb2”指表名。
l 查看表空间的大小
1）select pg_size_pretty(pg_tablespace_size('pg_global'));
2）select pg_size_pretty(pg_tablespace_size('pg_default'));
l 查看表对应的数据文件
select pg_relation_filepath('test01');
l 让配置生效
1）pg_ctl reload
2）select pg_reload_conf();
l 切换Log日志文件
select pg_rotate_logfile();
l 切换WAL日志文件
1）9.x: select pg_switch_xlog();
2）>=10版本： select pg_switch_wal();
l 手工产生checkpoint
checkpoint;
l 查询正在运行的SQL（也能看到等待事件）
select * from pg_stat_activity;
l 取消一个长时间运行的查询SQL（非DML）
select pg_cancel_backend(pid);
l 终止一个进行运行的SQL（包括DML)
select pg_terminate_backend(pid);
l 杀掉除自己之外的连接（危险）
Select usename,datname, client_addr, pg_terminate_backend(pid) from pg_stat_activity where pid<> pg_backend_pid();
l 查看备库
select * from pg_stat_replication;
l 暂停备库的wal日志应用
select pg_xlog_replay_pause();
l 继续备库的wal日志应用
pg_xlog_replay_resume();
l 检查备库的wal日志应用是否暂停了
pg_is_xlog_replay_paused();
四、运维方案
（一）制定运维整体方案
制定完善运维整体方案，包括运维环境监控、日常数据库管理、数据库备份与恢复、性能监控、性能调优。
运维环境监控:包括CPU是否过高、IO是否过忙、网络监控（网络流量是否过大）、磁盘空间监控、数据库年龄监控（如果数据年龄超了，数据库会停止工作）、表和物化视图上索引的数量、数据库级的统计信息。
日常数据库管理：包括实例状态检查、PG监听是否正常、WAL日志检查（是否出现爆增还爆减）、表空间检查、日志检查（是否报错）、备份有效性检查的方法。
数据库备份与恢复：包括备份策略设定、物理备份、逻辑备份（库表小做逻辑备份）、备份脚本、恢复脚本或恢复操作过程、如何防止误删除（是否架建延持备库）。
性能监控：包括检查等待事件、磁盘IO监控、TOP 10 SQL、数据库的每秒查询的行、插入的行、删除的行、更新的行。
性能调优：包括OS层面优化、PG参数优化、SQL优化、IO优化、架构优化：如读写分离、分库分表。
上述工作都需要提前做好，以保证后续正常运维。
（二）运维的工作
日常运维工作包括:
• 表、索引、物化视图、数据库、表空间的大小，表空间剩余可用空间；
• 数据库年龄、表的年龄；
• 表，物化视图的索引数量；
• 索引扫描次数；
• 表、物化视图、索引膨胀字节数，膨胀比例；
• Deadtuple；
• 序列剩余次数；
• HA，备份，归档，备库延迟状态；
• 错误日志统计；
• 事件触发器、触发器的情况；
• Unlogged table的情况，如果是9.X版本之前，了解Hash Index情况；
• 锁等待；
• 活跃度，Active, Idle, Idle in transaction状态会话数，剩余可用连接数；
• 带事务号的长事务，2PC事务；
• 网卡利用率，CPU利用率，IO利用率，内存利用率；
• 慢SQL及当时的Analyze执行计划；
• TOP SQL；
• 数据库级别统计信息：回滚数，提交数，命中率，死锁次数，IO TIME，Tuple DML次数。
