添加索引

CREATE INDEX時(shí)管行，可以使用CONCURRENTLY參數(shù)并行創(chuàng)建索引业舍，使用CONCURRENTLY參數(shù)不會(huì)鎖表赎懦，創(chuàng)建索引過(guò)程中不會(huì)阻塞表的更新起胰、刪除久又、插入操作。

sky=# CREATE INDEX IF NOT EXISTS "error_record_action_log_id_idx" ON "error_record" ("action_log_id");
CREATE INDEX
sky=# CREATE INDEX IF NOT EXISTS "error_record_alert_id_idx" ON "error_record" ("alert_id");
CREATE INDEX
sky=# \d error_record
                                     Table "public.error_record"
    Column     |           Type           |                         Modifiers
---------------+--------------------------+-----------------------------------------------------------
 id            | integer                  | not null default nextval('error_record_id_seq'::regclass)
 code          | character varying(255)   | not null default ''::character varying
 message       | text                     | not null default ''::text
 details       | text                     |
 action_log_id | bigint                   |
 alert_id      | bigint                   |
 host_id       | bigint                   |
 extra_data    | text                     |
 create        | timestamp with time zone | not null
Indexes:
    "error_record_pkey" PRIMARY KEY, btree (id)
    "error_record_action_log_id_idx" btree (action_log_id)
    "error_record_alert_id_idx" btree (alert_id)

• https://devcenter.heroku.com/articles/postgresql-indexes
• https://devcenter.heroku.com/articles/postgresql-concurrency
• https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan

修改

sky=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
 id |   name   | volume_name |        image_id        | recycled | passive
----+----------+-------------+------------------------+----------+---------
 84 | lun_v2_1 | lun_v2_1    | rbd_data.4f5f04a0178de | f        | t
 85 | lun_v2_2 | lun_v2_2    | rbd_data.4f5f0672ac672 | f        | t
 86 | lun_v2_3 | lun_v2_3    | rbd_data.4f5f07f04723a | f        | t
(3 rows)

sky=# update volume set image_id=trim(leading 'rbd_data.' from volume.image_id) where image_id like '%rbd_data%';
UPDATE 3
sky=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
 id | name | volume_name | image_id | recycled | passive
----+------+-------------+----------+----------+---------
(0 rows)

sky=# select id, name, volume_name, image_id, recycled, passive from volume order by id desc;
 id |   name   | volume_name |   image_id    | recycled | passive
----+----------+-------------+---------------+----------+---------
 86 | lun_v2_3 | lun_v2_3    | 4f5f07f04723a | f        | t
 85 | lun_v2_2 | lun_v2_2    | 4f5f0672ac672 | f        | t
 84 | lun_v2_1 | lun_v2_1    | 4f5f04a0178de | f        | t
 36 | lun03    | lun03       | 18ac6c0f      | f        | t
(4 rows)

reset sequence

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1;
alter sequence alert_sequence_id start 1;
UPDATE foo SET id = DEFAULT;

List table triggers

select event_object_schema as table_schema,
       event_object_table as table_name,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') as event,
       action_timing as activation,
       action_condition as condition,
       action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
         table_name;

List idle transactions

sky=# select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null;
 datid | datname | pid | usesysid |  usename   | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                                             query
-------+---------+-----+----------+------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
 16385 | demon   |  50 |    16384 | demon_user |                  | 127.0.0.1   |                 |       33558 | 2020-10-13 02:20:02.043835+00 | 2020-10-13 03:19:35.695803+00 | 2020-10-13 03:19:35.697997+00 | 2020-10-13 03:19:35.701515+00 |                 |            | idle in transaction |        2473 |              | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9
(1 row)

sky=# SELECT pid, now() - pg_stat_activity.query_start AS duration,  query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
 pid |    duration     |                                                                             query                                                                             |        state
-----+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
  50 | 00:23:27.889451 | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9 | idle in transaction
(1 row)

sky=# SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock';

sky=# select pg_cancel_backend(50);                                                                                              pg_cancel_backend
-------------------
 t
(1 row)

sky=# select pg_terminate_backend(50);
 pg_terminate_backend
----------------------
 t
(1 row)

check replication status

On master:
select * from pg_stat_replication;

sky=# select * from pg_stat_replication ;
 pid  | usesysid |     usename      | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+------------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 3256 |    16386 | demon_replicator | walreceiver      | 10.252.3.171 |                 |       48634 | 2020-10-27 03:13:57.134488+00 |              | streaming | 0/28664B00    | 0/28664B00     | 0/28664B00     | 0/28664B00      |             0 | async
 3276 |    16386 | demon_replicator | walreceiver      | 10.252.3.172 |                 |       44150 | 2020-10-27 03:18:37.949817+00 |              | streaming | 0/28664B00    | 0/28664B00     | 0/28664B00     | 0/28664B00      |             0 | async
(2 rows)

sky=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

On replica (streaming replication in my case):
select * from pg_stat_wal_receiver;

sky=# select * from pg_stat_wal_receiver ;
 pid |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |      last_msg_send_time      |     last_msg_receipt_time     | latest_end_lsn |       latest_end_time        | slot_name |                                                                           conninfo
-----+-----------+-------------------+-------------------+--------------+--------------+------------------------------+-------------------------------+----------------+------------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
  44 | streaming | 0/6000000         |                 1 | 0/286B4320   |            1 | 2020-11-02 11:32:49.16744+00 | 2020-11-02 11:32:16.011279+00 | 0/286B4320     | 2020-11-02 11:32:49.16744+00 |           | user=demon_replicator password=******** dbname=replication host=10.252.3.170 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1
(1 row)

sky=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

sky=# set timezone=8;
SET
sky=# show timezone;
 TimeZone
----------
 <+08>-08
(1 row)

sky=# select pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp();
 pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp
-------------------------------+------------------------------+-------------------------------
 0/2BD61730                    | 0/2BD61730                   | 2021-01-20 19:32:42.419382+08
(1 row)

Restore data from pg_dump

sky=# create database sky;
CREATE DATABASE

(ENV) [root@ceph-2 ~]# docker exec -i -u postgres sds-postgres pg_restore -d sky < ~/20201031115700
(ENV) [root@ceph-2 ~]# docker exec -it -u postgres sds-postgres psql sky
psql (9.6.6)
Type "help" for help.

sky=# select count(*) from volume;
 count
-------
  1402
(1 row)

查看xlog/WAL信息

pg_current_xlog_insert_location指寫入wal buffer的位置爵卒。
pg_current_xlog_location返回已經(jīng)write到wal文件的位置珠叔。

master:

sky=# select proname from pg_proc where proname like 'pg_%_location';
             proname
---------------------------------
 pg_current_xlog_flush_location
 pg_current_xlog_insert_location
 pg_current_xlog_location
 pg_last_xlog_receive_location
 pg_last_xlog_replay_location
 pg_tablespace_location
(6 rows)

sky=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/3C44F120
(1 row)

sky=# select pg_current_xlog_insert_location();
 pg_current_xlog_insert_location
---------------------------------
 0/3C44F120
(1 row)

sky=# select pg_xlogfile_name_offset('0/3C44F120');
      pg_xlogfile_name_offset
------------------------------------
 (00000001000000000000003C,4518176)
(1 row)

sky=# select pid, usename, client_addr, state, pg_xlog_location_diff( pg_current_xlog_location(), write_location) write_delay, pg_xlog_location_diff( pg_current_xlog_location(),  flush_location) flush_delay, pg_xlog_location_diff( pg_current_xlog_location(), replay_location) replay_delay from  pg_stat_replication ;
 pid  |     usename      | client_addr |   state   | write_delay | flush_delay | replay_delay
------+------------------+-------------+-----------+-------------+-------------+--------------
 5335 | replicator | 10.0.11.9   | streaming |           0 |           0 |           56
 8242 | replicator | 10.0.11.7   | streaming |           0 |           0 |           56

The pg_lsn data type can be used to store LSN (Log Sequence Number) data which is a pointer to a location in the XLOG. This type is a representation of XLogRecPtr and an internal system type of PostgreSQL.
Internally, an LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. The pg_lsn type supports the standard comparison operators, like = and >. Two LSNs can be subtracted using the - operator; the result is the number of bytes separating those write-ahead log positions.
replica:

sky=# select pg_current_xlog_location();
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.
demon=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 0/3C43EB60
(1 row)

sky=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 0/3C43EB60
(1 row)

查詢和設(shè)置參數(shù)

sky=# select  current_setting('max_standby_streaming_delay') as max_standby_streaming_delay, current_setting('hot_standby_feedback') as hot_standby_feedback, current_setting('wal_keep_segments') as wal_keep_segments, current_setting('shared_buffers') as shared_buffers, current_setting('commit_delay') as commit_delay, current_setting('synchronous_commit') as synchronous_commit, current_setting('max_wal_size') as max_wal_size, current_setting('wal_segment_size') as wal_segment_size,  current_setting('full_page_writes') as full_page_writes, current_setting('min_wal_size') as min_wal_size, current_setting('work_mem') as work_mem, current_setting('random_page_cost') as random_page_cost, current_setting('seq_page_cost') as seq_page_cost, current_setting('client_min_messages') as client_min_messages, current_setting('log_parser_stats') as log_parser_stats,  current_setting('log_statement_stats') as log_statement_stats, current_setting('log_planner_stats') as log_planner_stats;
 max_standby_streaming_delay | hot_standby_feedback | wal_keep_segments | shared_buffers | commit_delay | synchronous_commit | max_wal_size | wal_segment_size | full_page_writes | min_wal_size | work_mem | random_page_cost | seq_page_cost | client_min_messages | log_parser_stats | log_statement_stats | log_planner_stats
-----------------------------+----------------------+-------------------+----------------+--------------+--------------------+--------------+------------------+------------------+--------------+----------+------------------+---------------+---------------------+------------------+---------------------+-------------------
 30s                         | off                  | 64                | 128MB          | 0            | on                 | 1GB          | 16MB             | on               | 80MB         | 4MB      | 4                | 1             | notice              | off              | off                 | off
(1 row)

也可以通過(guò)如下命令查看參數(shù)：

sky=# SHOW max_connections;
 max_connections
-----------------
 1000
(1 row)
sky=# show max_locks_per_transaction;
 max_locks_per_transaction
---------------------------
 64
(1 row)

可以通過(guò)set_config(setting_name, new_value, is_local)進(jìn)行設(shè)置续徽。is_local用于控制是否只對(duì)當(dāng)前事務(wù)生效。

SELECT set_config('log_statement_stats', 'off', false);

 set_config
------------
 off
(1 row)

SET LOCAL synchronous_commit TO OFF;
SET synchronous_commit TO OFF
例如：synchronous_commit參數(shù)用于控制當(dāng)數(shù)據(jù)庫(kù)提交事務(wù)時(shí)是否需要等待WAL日志寫入磁盤后才向客戶端返回成功脉执。

In off mode, there is no waiting, so there can be a delay between when success is reported to the client and when the transaction is later guaranteed to be safe against a server crash. (The maximum delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction.

查詢當(dāng)前數(shù)據(jù)庫(kù)的緩存命中率以及事務(wù)提交率

sky=# select blks_hit::float/( blks_read + blks_hit ) as hit_ratio from pg_stat_database where datname=current_database();
     hit_ratio
-------------------
 0.999590362075571
(1 row)

sky=# select xact_commit::float/( xact_commit + xact_rollback ) as xact_ratio from pg_stat_database where datname=current_database();
    xact_ratio
-------------------
 0.994138864502206
(1 row)

索引掃描所占的比率

sky=# select sum(idx_scan)/(sum(idx_scan) +sum(seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname ='public';
     idx_scan_ratio
------------------------
 0.66835685593313328948
(1 row)

sky=# select relname, idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio from pg_stat_all_tables where schemaname ='public' order by  idx_scan_ratio asc;
                   relname                   |    idx_scan_ratio
---------------------------------------------+----------------------
 fs_arbitration_pool                         |                    0
 dp_block_backup_job                         |                    0
 crypto_key                                  |                    0
 fc_port                                     |                    0
 fs_user_group_fs_users                      |                    0
 alert_rule_resource_blacklist               |                    0
 dns_gateway_group                           |                    0

pg_stat_statements

sky=# \d pg_stat_statements
Did not find any relation named "pg_stat_statements".
sky=# create extension pg_stat_statements;
CREATE EXTENSION
sky=# \d pg_stat_statements
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers
---------------------+------------------+-----------
 userid              | oid              |
 dbid                | oid              |
 queryid             | bigint           |
 query               | text             |
 calls               | bigint           |
 total_time          | double precision |
 min_time            | double precision |
 max_time            | double precision |
 mean_time           | double precision |
 stddev_time         | double precision |
 rows                | bigint           |
 shared_blks_hit     | bigint           |
 shared_blks_read    | bigint           |
 shared_blks_dirtied | bigint           |
 shared_blks_written | bigint           |
 local_blks_hit      | bigint           |
 local_blks_read     | bigint           |
 local_blks_dirtied  | bigint           |
 local_blks_written  | bigint           |
 temp_blks_read      | bigint           |
 temp_blks_written   | bigint           |
 blk_read_time       | double precision |
 blk_write_time      | double precision |

sky=# select calls, total_time/calls as avg_time, left(query, 80) from pg_stat_statements order by 2 desc limit 10;
 calls |     avg_time     |                                       left
-------+------------------+----------------------------------------------------------------------------------
     9 | 17498.0615555556 | INSERT INTO "sync_test" (seq) SELECT g.id FROM generate_series(?, ?) AS g (id) ;
     1 |          898.476 | create extension pg_stat_statements;
     1 |          464.437 | ALTER TABLE "dp_block_async_replication_pair" ADD COLUMN "verified" BOOL NOT NUL
     1 |          310.666 | update volume set passive=?;
     1 |          211.801 |  SELECT pg_catalog.quote_ident(name)    FROM pg_catalog.pg_available_extensions
    27 | 199.369074074074 | DELETE FROM "volume_migration_job" WHERE "id" = $1
     1 |          177.588 | ALTER TABLE "host" ADD COLUMN "kvm_validation" bool NOT NULL DEFAULT FALSE;
     2 |         147.9175 | UPDATE "service" SET "pid" = $1 WHERE "id" = $2
     1 |          124.925 | select * from pg_statistic ;
     1 |          122.981 | ALTER TABLE "pool" ADD COLUMN IF NOT EXISTS "compressed" BOOL NOT NULL DEFAULT '
(10 rows)

Explain

Explain可以有如下選項(xiàng)：

sky=# \h explain
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

需要注意的是： 使用ANALYZE選項(xiàng)時(shí)語(yǔ)句會(huì)被執(zhí)行，所以在分析INSERT戒劫，DELETE半夷， UPDATE婆廊，CREATE TABLE AS或者EXECUTE命令的查詢計(jì)劃時(shí)，應(yīng)該使用一個(gè)事務(wù)來(lái)執(zhí)行巫橄，得到真正的查詢計(jì)劃后對(duì)該事務(wù)進(jìn)行回滾操作淘邻，避免因?yàn)槭褂肁NALYZE選項(xiàng)而修改了數(shù)據(jù)。

sky=# set log_parser_stats = on;
SET
sky=# set log_planner_stats = on;
SET
sky=# set client_min_messages = log;
SET
sky=# explain (analyze on, timing on, verbose on, buffers on) select * from error_record where id=10;                                                                                                                      LOG:  PARSER STATISTICS
DETAIL:  ! system usage stats:
!       0.000058 elapsed 0.000059 user 0.000000 system sec
!       [0.261612 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
!       0.000076 elapsed 0.000076 user 0.000000 system sec
!       [0.261796 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
!       0.000001 elapsed 0.000002 user 0.000000 system sec
!       [0.261846 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  PLANNER STATISTICS
DETAIL:  ! system usage stats:
!       0.000138 elapsed 0.000137 user 0.000000 system sec
!       [0.262110 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on public.error_record  (cost=0.00..6.29 rows=1 width=184) (actual time=0.029..0.064 rows=1 loops=1)
   Output: id, code, message, details, action_log_id, alert_id, host_id, extra_data, "create"
   Filter: (error_record.id = 10)
   Rows Removed by Filter: 107
   Buffers: shared hit=5
 Planning time: 0.192 ms
 Execution time: 0.103 ms
(7 rows)

構(gòu)造測(cè)試數(shù)據(jù)

sky=# DROP TABLE IF EXISTS "sync_test";
NOTICE:  table "sync_test" does not exist, skipping
DROP TABLE
sky=# CREATE TABLE IF NOT EXISTS "sync_test" ( "id" serial NOT NULL PRIMARY KEY, "seq" bigint );
CREATE TABLE
sky=# INSERT INTO "sync_test" (seq) SELECT g.id FROM generate_series(1, 100000) AS g (id) ;
INSERT 0 100000
sky=# select count(*) from sync_test;
 count
--------
 100000
(1 row)

stop a Postgres script when it encounters an error

add following to .psqlrc is far from perfection

\set ON_ERROR_STOP on

or use psql with parameter:

psql -v ON_ERROR_STOP=1

better to use also -X parameter turning off .psqlrc file usage.
or
psql --single-transaction ...
PGOPTIONS='--client-min-messages=warning' psql -X -q -a -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb -f dump.sql

[root@ceph-3 ~]# docker exec -i -u postgres sds-postgres pg_restore --single-transaction -c -d sky -p 5432 < ~/20201031115700
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4707; 2620 21400 TRIGGER vip operation_feedback_insert demon_user
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.vip" does not exist
    Command was: DROP TRIGGER operation_feedback_insert ON public.vip;

read unix @->/var/run/docker.sock: read: connection reset by peer

查詢連接數(shù)

sky=# SELECT datname, numbackends FROM pg_stat_database;
  datname  | numbackends
-----------+-------------
 postgres  |           0
 template1 |           0
 template0 |           0
 demon     |          93
(4 rows)

也可以指定database進(jìn)行查詢

sky=# select count(*) from pg_stat_activity where datname='demon';
count
-------
   93
(1 row)

另外也可以通過(guò)如下命令查詢連接數(shù)以及保留的連接數(shù)湘换。
參考鏈接: Right query to get the current number of connections in a PostgreSQL DB

sky=# select  * from (select count(*) used from pg_stat_activity) q1,  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;
 used | res_for_super | max_conn
------+---------------+----------
   95 |             3 |     1000
(1 row)

也可以根據(jù)狀態(tài)進(jìn)行分組：

sky=# select state, count(*) from pg_stat_activity  where pid <> pg_backend_pid() group by 1 order by 1;
 state  | count
--------+-------
 active |     3
 idle   |    90
(2 rows)

sky=# SELECT count(*), state FROM pg_stat_activity GROUP BY 2;
 count |        state
-------+---------------------
     1 | active
    29 | idle
     1 | idle in transaction
(3 rows)

參考鏈接：Connection handling best practice with PostgreSQL

active – Identifies currently running queries; in a sense, this is truly how many connections you may require at a time
idle – Identifies connections opened to the DB (most frameworks do this and maintain a pool of connections), that are not in use. This is the one area in which a connection pooler such as PgBouncer can help most.
idle in transaction – Identifies connections against which your app has run a BEGIN but it is now waiting somewhere in a transaction and not doing work.
idle in transaction (aborted) – Identifies connections that were idle in the transaction that have since been aborted.

超時(shí)設(shè)置

statement_timeout 用來(lái)控制語(yǔ)句執(zhí)行時(shí)長(zhǎng)宾舅，單位是ms
idle_in_transaction_session_timeout 用來(lái)控制事務(wù)執(zhí)行時(shí)長(zhǎng)，單位是ms枚尼。

sky=# show statement_timeout;
 statement_timeout
-------------------
 6min
(1 row)

sky=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 1h
(1 row)

PgBouncer

max_client_conn配置項(xiàng)表示最多允許用戶多少個(gè)連接到pgbouncer贴浙；
default_pool_size表示默認(rèn)連接池中建多少個(gè)到后端數(shù)據(jù)庫(kù)的連接。

支持三種連接池模式：
session
Server is released back to pool after client disconnects. Default.
transaction
Server is released back to pool after transaction finishes.
statement
Server is released back to pool after query finishes. Transactions spanning multiple statements are disallowed in this mode.

pg locks

Lock monitoring
PostgreSQL rocks, except when it blocks: Understanding locks

查詢表中的列是否存在

SELECT column_name FROM information_schema.columns 
WHERE table_name='your_table' and column_name='your_column';

References

• Finding and killing long running queries on PostgreSQL
• # 9.26. System Administration Functions
  
• In PostgreSQL, as in life, don’t wait too long to commit
• Monitoring Postgres Replication
• pgmetrics
• pgmetrics使用介紹
• How to Restore Database Dumps for Postgres in Docker Container
• Quick: How to dump & restore a PostgreSQL database from a docker container
• PostgreSQL數(shù)據(jù)庫(kù)xlog文件命名為何如此優(yōu)美
• 19.5. Write Ahead Log
• # 9.26. System Administration Functions
  
• psql
• How to Manage Connections Efficiently in Postgres, or Any Database
• PostgreSQL Connection Pooling
• pgbouncer quick start
• pgbouncer config
• Deep dive into postgres stats: pg_stat_replication