postgresql 中的几个 timeout参数 用法说明

select version();
                         version                         
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
select *
from pg_settings ps
where 1=1
and ps.name like '%timeout%'
;
        name         | setting | unit |              category              |                    short_desc                     |       extra_desc       | context | vartype | source | min_val | max_val  | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart 
-------------------------------------+---------+------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------+-----------+---------+---------+---------+------------+----------+----------+-----------+------------+------------+-----------------
 archive_timeout           | 0    | s  | Write-Ahead Log / Archiving                 | Forces a switch to the next WAL file if a new file has not been started within N seconds. |                   | sighup  | integer | default | 0    | 1073741823 |     | 0    | 0     |      |      | f
 authentication_timeout       | 60   | s  | Connections and Authentication / Security and Authentication | Sets the maximum allowed time to complete client authentication.             |                   | sighup  | integer | default | 1    | 600    |     | 60    | 60    |      |      | f
 checkpoint_timeout         | 300   | s  | Write-Ahead Log / Checkpoints                | Sets the maximum time between automatic WAL checkpoints.                 |                   | sighup  | integer | default | 30   | 86400   |     | 300   | 300    |      |      | f
 deadlock_timeout          | 1000  | ms  | Lock Management                       | Sets the time to wait on a lock before checking for deadlock.               |                   | superuser | integer | default | 1    | 2147483647 |     | 1000   | 1000   |      |      | f 
 idle_in_transaction_session_timeout | 0    | ms  | Client Connection Defaults / Statement Behavior       | Sets the maximum allowed duration of any idling transaction.               | A value of 0 turns off the timeout. | user   | integer | default | 0    | 2147483647 |     | 0    | 0     |      |      | f
 lock_timeout            | 0    | ms  | Client Connection Defaults / Statement Behavior       | Sets the maximum allowed duration of any wait for a lock.                 | A value of 0 turns off the timeout. | user   | integer | default | 0    | 2147483647 |     | 0    | 0     |      |      | f
 statement_timeout          | 0    | ms  | Client Connection Defaults / Statement Behavior       | Sets the maximum allowed duration of any statement.                    | A value of 0 turns off the timeout. | user   | integer | default | 0    | 2147483647 |     | 0    | 0     |      |      | f
 wal_receiver_timeout        | 60000  | ms  | Replication / Standby Servers                | Sets the maximum wait time to receive data from the primary.               |                   | sighup  | integer | default | 0    | 2147483647 |     | 60000  | 60000   |      |      | f
 wal_sender_timeout         | 60000  | ms  | Replication / Sending Servers                | Sets the maximum time to wait for WAL replication.                    |                   | sighup  | integer | default | 0    | 2147483647 |     | 60000  | 60000   |      |      | f
(9 rows)

bash-4.1$ cd /home
bash-4.1$ ls
aquota.user enterprisedb lost+found prouser puppet

500 1891 1 0 2018 "htmlcode">

bash-4.1$ su - enterprisedb
bash-4.1$ psql -U enterprisedb
psql (9.2.14.31)
Type “help” for help.



查看当前该参数配置 3s


edb=# show deadlock_timeout;


deadlock_timeout


3s
(1 row)


（4）查看该参数



pending_restart —boolean— true if the value has been changed in the configuration file but needs a restart; or false otherwise.


即该参数为实时生效型参数
（5）尝试使用


test=# alter system set deadlock_timeout=‘1s';


报错如下：


ERROR: syntax error at or near “system”
LINE 1: alter system set deadlock_timeout=‘1s';


查询到解释：
alter system 命令 只对 9.4以后的版本 的版本有效，
（6）尝试执行


set deadlock_timeout=‘1s'
edb=# show deadlock_timeout;



显示为1s
但是其余用户登录路，发现该参数依然为 3s
----即该方法为session级别设置，类似于oracle的alter session set
（7） 尝试修改 pg配置文件，进入/DATA/data_utl 路径修改


bash-4.1$ pwd
/DATA/data_utl
bash-4.1$ ls
base pgbin pg_ident.conf pg_notify pg_stat_tmp pg_twophase postgresql.conf postmaster.opts
dbms_pipe pg_clog pg_log pg_serial pg_subtrans PG_VERSION postgresql.conf.20191119 postmaster.pid
global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xlog postgresql.conf.bak utlp.sql
vi postgresql.conf



将其中 deadlock_timeout 配置修改为1s
（8）重新加载数据库


bash-4.1$ ./pg_ctl reload
server signaled


（9）查看该参数已生效


edb=# show deadlock_timeout;
deadlock_timeout
1s
(1 row)



退出


edb=# \q


第9步也可通过重启数据库生效
停止指令：


./pg_ctl stop -m fast


启动指令：


./pg_ctl -D /DATA/data_utl start


以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。