2023年7月10日发(作者：)

postgresql中的⼏个timeout参数⽤法说明今天整理了下 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 pswhere 1=1and 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 | | 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 | |

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 |

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)下⾯简单介绍下这⼏个参数archive_timeout控制服务器周期性地切换到⼀个新的 WAL 段⽂件，通俗的讲，就是定时归档。authentication_timeout完成服务器认证的最长时间，如果在这个时间内没有完成认证，服务器将关闭连接。checkpoint_timeout⾃动 WAL 检查点之间的最长时间，增⼤这个参数会使数据库崩溃后恢复的时间加长。deadlock_timeout进⾏死锁检测之前在⼀个锁上等待的总时间idle_in_transaction_session_timeout空闲事务超时。终⽌任何已经闲置超过这个参数所指定的时间（以毫秒计）的打开事务的会话。 这使得该会话所持有的任何锁被释放，并且其所持有的连接槽可以被重⽤， 它也允许只对这个事务可见的元组被清理。lock_timeout锁等待超时。语句在试图获取表、索引、⾏或其他数据库对象上的锁时等到超过指定的毫秒数，该语句将被中⽌。不推荐在中设置。statement_timeout控制语句执⾏时长，单位是ms。超过设定值，该语句将被中⽌。不推荐在中设置，如⾮要设置，应该设置⼀个较⼤值。wal_receiver_timeout中⽌处于⾮活动状态超过指定毫秒数的复制链接。这对于正在接收的后备服务器检测主服务器崩溃或⽹络断开有⽤。设置为0会禁⽤超时机制。这个参数只能在⽂件中或在服务器命令⾏上设置。默认值是 60 秒。wal_sender_timeout中断那些停⽌活动超过指定毫秒数的复制连接。这对发送服务器检测⼀个后备机崩溃或⽹络中断有⽤。设置为0将禁⽤该超时机制。这个参数只能在⽂件中或在服务器命令⾏上设置。默认值是 60 秒。补充：Postgresql数据库修改deadlock_timeout参数某产品线提出要将Postgresql测试库中 deadlock_timeout 参数 修改为1s该库平时不在⾃⼰⼿中维护，故实际操作步骤如下（1）确定数据库安装所属⽤户查看下都有哪些账户，可能是postgresql使⽤的bash-4.1$ cd /homebash-4.1$ enterprisedb lost+found prouser puppet（2）确定数据库安装路径：bash-4.1$ ps -ef | grep post 查看服务进程，找到应⽤安装⽬录 /opt/app/PostgresPlus/9.2AS/500 1891 1 0 2018 ? 01:01:47 /opt/app/PostgresPlus/9.2AS/bin/edb-postgres -D /DATA/data_utl（3）切换到 enterprisedb ⽤户，并登陆数据库bash-4.1$ su - enterprisedbbash-4.1$ psql -U enterprisedbpsql (9.2.14.31)Type “help” for help.查看当前该参数配置 3sedb=# show deadlock_timeout;deadlock_timeout3s(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_utlbash-4.1$ lsbase pgbin pg_ pg_notify pg_stat_tmp pg_twophase ms_pipe pg_clog pg_log pg_serial pg_subtrans PG_VERSION .20191119 bal pg_ pg_multixact pg_snapshots pg_tblspc pg_xlog 将其中 deadlock_timeout 配置修改为1s（8）重新加载数据库bash-4.1$ ./pg_ctl reloadserver signaled（9）查看该参数已⽣效edb=# show deadlock_timeout;deadlock_timeout1s(1 row)退出edb=# q第9步也可通过重启数据库⽣效停⽌指令：./pg_ctl stop -m fast启动指令：./pg_ctl -D /DATA/data_utl start以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。如有错误或未考虑完全的地⽅，望不吝赐教。