postgresql中几个超时参数的用法说明

今天整理了下 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)

下面简单介绍下这几个参数

archive_timeout控制服务器周期性地切换到一个新的 WAL 段文件，通俗的讲，就是定时归档。

authentication_timeout完成服务器认证的最长时间，如果在这个时间内没有完成认证，服务器将关闭连接。

checkpoint_timeout自动 WAL 检查点之间的最长时间，增大这个参数会使数据库崩溃后恢复的时间加长。

deadlock_timeout进行死锁检测之前在一个锁上等待的总时间

（编辑：ASP站长网）