PostgreSQL小记

当前环境: 

PostgreSQL 9.5.1

CentOS 6.5

 

1.查看当前客户端连接数

#查询当前的连接的pid

select pg_backend_pid();

#查看表结构，pg_stat_activity是一个系统视图

\d+ pg_stat_activity  

#查看当前连接总数

ps -ef|grep -v grep |grep postgres |grep "idle" | wc -l

select count(1) from pg_stat_activity WHERE NOT pid=pg_backend_pid();

#linux连接数进程pid,用户名,应用名称,client登录时间,运行时间,后台是否等待一个lock,当前状态,sql语句

select pid,usename ,application_name,backend_start,current_timestamp - least(query_start,xact_start) as runtime,waiting,state,query from pg_stat_activity;

#等待事件,根据阻塞的语句的会话PID做相应处理

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement 

FROM pg_locks bl 

JOIN pg_stat_activity a ON a.pid = bl.pid 

JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid 

JOIN pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted;

#撤销(需要时间)

SELECT pg_cancel_backend(30036); 

#杀掉进程(删除postgresql的进程使用kill或者pg_terminate_backend()命令，不要使用kill -9)

kill pid

select pg_terminate_backend();

【参考】:

http://blog.csdn.net/luojinbai/article/details/44586917

https://my.oschina.net/Kenyon/blog/187143

http://n3xtchen.github.io/n3xtchen/postgresql/2016/08/31/kill-postgresql

2.常用命令

\h：查看SQL命令的解释，比如\h select。

\?：查看psql命令列表。

\l：列出所有数据库。 select oid,datname from pg_database;

\c [database_name]：连接其他数据库。

\d：列出当前数据库的所有表格。

\d [table_name]：列出某一张表格的结构。

\du：列出所有用户。

\e：打开文本编辑器。

\conninfo：列出当前数据库和连接的信息。

\dn 或者 \dnS 查看当前database下的schema

\dt 查看当前database的当前搜索路径下schema的表

配置search_path路径,为了能够找到schema的表等等

denali=# \dt region_longlive

No matching relations found.

denali=# SHOW search_path

denali-# ;

   search_path   

-----------------

 "$user", public

(1 row)

denali=# set search_path to region_longlive,public;

SET

denali=# SHOW search_path;

       search_path       

-------------------------

 region_longlive, public

(1 row)

denali=# \dt

                   List of relations

     Schema      |      Name      | Type  |    Owner    

-----------------+----------------+-------+-------------

 region_longlive | entity         | table | denaliadmin

 region_longlive | entity_result  | table | denaliadmin

 region_longlive | result         | table | denaliadmin

 region_longlive | result_history | table | denaliadmin

(4 rows)

denali=#

3.查看size

#database size:

select pg_size_pretty(pg_database_size('postgres'));

\l+

\l+ <database_name>

select t1.datname AS db_name, pg_size_pretty(pg_database_size(t1.datname)) as db_size

from pg_database t1 order by pg_database_size(t1.datname) desc;

#shcema size:

SELECT schema_name, 

    pg_size_pretty(sum(table_size)::bigint) as "disk space",

    (sum(table_size) / pg_database_size(current_database())) * 100

        as "percent"

FROM (

     SELECT pg_catalog.pg_namespace.nspname as schema_name,

         pg_relation_size(pg_catalog.pg_class.oid) as table_size

     FROM   pg_catalog.pg_class

         JOIN pg_catalog.pg_namespace 

             ON relnamespace = pg_catalog.pg_namespace.oid

) t

GROUP BY schema_name

ORDER BY schema_name;

#table size:

\dt+ 

SELECT

   relname as "Table",

   pg_size_pretty(pg_total_relation_size(relid)) As "Size",

   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"

   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

【参考】:

https://wiki.postgresql.org/wiki/Disk_Usage

https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

4.修改配置参数

语法:

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

备注: 命令很简单, 命令设置的参数值会写入到一个名为 postgresql.auto.conf 的文件, 

这个文件和配置文件 postgresql.conf 不同, 它是二进制文件,不能手工编辑;

如果将参数值设置成 DEFAULT , 将在动态文件 postgresql.auto.conf 删除参数设置. 和之前一样,参数设置后需要 reload 或者 重启数据库生效,这和之前版本是一样的.

 

#查询配置名称和简短说明

SELECT * FROM pg_settings;  

show all;

SELECT name, short_desc FROM pg_settings; 

#查看系统中哪些选项被修改过。

select name, source, setting from pg_settings  where source != 'default'  and source != 'override'

#a.设置参数，当前session有效和查看参数的值

postgres=# set work_mem='16MB';

SET

postgres=# show work_mem;  

 work_mem 

----------

 16MB

(1 row)

 

#b.设置参数，永久有效

postgres=# show work_mem;

 work_mem 

----------

 16MB

(1 row)

postgres=# alter system set  work_mem to '32MB';

ALTER SYSTEM

postgres=# show work_mem;

 work_mem 

----------

 16MB

(1 row)

postgres=# select pg_reload_conf(); 

 pg_reload_conf 

----------------

 t

(1 row)

postgres=# show work_mem;

 work_mem 

----------

 32MB

(1 row)

postgres=#

 

【参考】:

http://francs3.blog.163.com/blog/static/4057672720144194492582/

http://blog.csdn.net/dyx1024/article/details/6629776

 

5.linux的tcp配置

[root@sht-sgmhadoopcm-01 ~]# sysctl -a|grep tcp_keepalive

net.ipv4.tcp_keepalive_time = 7200

net.ipv4.tcp_keepalive_probes = 9

net.ipv4.tcp_keepalive_intvl = 75

[root@sht-sgmhadoopcm-01 ~]# 

tcp_keepalive_time:    一个连接需要TCP开始发送keepalive探测数据包之前的空闲时间。以秒为单位

tcp_keepalive_probes: 发送TCP keepalive探测数据包的最大数量，默认是9.如果发送9个keepalive探测包后对端仍然没有响应，就关掉这个连接

tcp_keepalive_intvl:  发送两个TCP keepalive探测数据包的间隔时间，默认是75秒

 

【案例】:

系统内核参数配置

tcp_keepalive_time，在TCP保活打开的情况下，最后一次数据交换到TCP发送第一个保活探测包的间隔，即允许的持续空闲时长，或者说每次正常发送心跳的周期，默认值为7200s（2h）。

tcp_keepalive_probes 在tcp_keepalive_time之后，没有接收到对方确认，继续发送保活探测包次数，默认值为9（次）

tcp_keepalive_intvl，在tcp_keepalive_time之后，没有接收到对方确认，继续发送保活探测包的发送频率，默认值为75s。

发送频率tcp_keepalive_intvl乘以发送次数tcp_keepalive_probes，就得到了从开始探测到放弃探测确定连接断开的时间

若设置，服务器在客户端连接空闲的时候，每90秒发送一次保活探测包到客户端，若没有及时收到客户端的TCP Keepalive ACK确认，将继续等待15秒*2=30秒。

总之可以在90s+30s=120秒（两分钟）时间内可检测到连接失效与否。

以下改动，需要写入到/etc/sysctl.conf文件：

net.ipv4.tcp_keepalive_time=90

net.ipv4.tcp_keepalive_intvl=15

net.ipv4.tcp_keepalive_probes=2

保存退出，然后执行sysctl -p生效。可通过 sysctl -a | grep keepalive 命令检测一下是否已经生效。

针对已经设置SO_KEEPALIVE的套接字，应用程序不用重启，内核直接生效。

 

6.postgresql tcp配置

在网络上连接远程服务器postgresql时，不活动时间稍长就会自动断开连接，不利于操作。

[postgres@sht-sgmhadoopcm-01 ~]$ cat /usr/local/pgsql/data/postgresql.conf |grep keepalives

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;

#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;

#tcp_keepalives_count = 0               # TCP_KEEPCNT;

[postgres@sht-sgmhadoopcm-01 data]$

因为postgresql支持TCP_KEEPLIVE机制。

有三个系统变量tcp_keepalives_idle，tcp_keepalives_interval ，tcp_keepalives_count 来设置postgresql如何处理死连接。

在postgresql, 这三个参数都设为0将使用操作系统的默认值，在linux下，tcp_keepalives_idle一般是2个小时，也就是2个小时后，服务器才可以自动关掉死连接。

在实际应运中，可以自行调整以上参数。

tcp_keepalives_idle = 30                # TCP_KEEPIDLE, in seconds;

tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;

tcp_keepalives_count = 10               # TCP_KEEPCNT;

参数对应:

 linux-tcp                                                     postgresql               

-------------------------------------------------------------------

tcp_keepalive_time       TCP_KEEPIDLE        tcp_keepalives_idle

tcp_keepalive_probes    TCP_KEEPCNT         tcp_keepalives_count

tcp_keepalive_intvl        TCP_KEEPINTVL      tcp_keepalives_interval

【参考】:

http://blog.csdn.net/yohoph/article/details/41512935

http://www.centoscn.com/CentOS/help/2014/0902/3662.html

http://www.aiuxian.com/article/p-2283684.html

http://www.blogjava.net/yongboy/archive/2015/04/14/424413.html

7.postgresql参数配置(调优--内存)

share_buffers

该参数主要是跟操作系统的共享内存有关，简单的说，就是不要超过物理内存的总量。

相关操作系统的(linux)下，使用 cat /etc/sysctl.conf 命令后，查看两个属性kernel.shmall和kernel.shmmax

temp_buffers

这个参数很简单，就是设置连接数据库的session大小。

work_mem

这个参数主要用于查询条件带排序的，或者是hash表等相关的操作。work_mem是一个连接session独享的，不是多个连接session共享的。

effective_cache_size

是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G(437500)

maintenance_work_mem

该参数主要是一个database session在执行相关操作时所需要的空间容量。

max_connections

通常，max_connections的目的是防止max_connections * work_mem超出了实际内存大小。

比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使 用2％的内存，则会导致swap的产生，系统性能就会大大降低。

当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系

假如有几千个连接，则需要考虑使用连接池管理软件，来减少并发连接的开销。

wal_buffers

 WAL共享数据存 储 器使用的内存量。 这个参数要求足够大，如果太小的话， log关 联的磁盘操作过频繁。

 

【内存使用相关】：

   shared_buffers   wal_buffers   work_mem   effective_cache_size

   计算公式：内存使用量 = work_mem * max_connections + shared_buffers

   (不过，对此我并不是非常认可，实际上会有很大误差，试想如果shared_buffers好work_mem都很小，而你却成功读取一个非常大的表的情景)

【磁盘I/O 相关】：

   checkpoint_segments   checkpoint_timeout   checkpoint_completion_target   shared_buffers

 

【参考】:

http://blog.csdn.net/found2008/article/details/6825576

http://blog.csdn.net/kyle__shaw/article/details/17576259

http://www.phperz.com/article/16/0226/200162.html

http://blog.csdn.net/chenyi8888/article/details/7019348

http://www.cnblogs.com/gaojian/p/topindex.html

https://yq.aliyun.com/articles/57563

http://blog.csdn.net/xing1989/article/details/8919280