Pankaj kushwaha
2 min readJul 2, 2020


Modify deadlock_timeout parameters in Postgresql

A product line proposed to modify the deadlock_timeout parameter in the Postgresql test library to 1s

The library is usually not maintained in its own hands, so the actual steps are as follows

(1) Determine which user the database installation belongs to

Check which accounts are available, which may be

bash-4.1$ cd /homebash-4.1$ ls

(2) Determine the database installation path:

bash-4.1$ ps -ef | grep post View the service process, find the application installation directory /u01/app/PostgresPlus/9.2AS/500 1891 1 0 2018? 01:01:47 /opt/ app/PostgresPlus/9.2AS/bin/edb-postgres -D /u01/app/data/data_utl

(3) Switch to the pankajdb user and log in to the database

bash-4.1$ su-pankajdbbash-4.1$ psql -U pankajdbpsql ( “help” for help.View the current configuration of this parameter 3sedb=# show deadlock_timeout;deadlock_timeout3s(1 row)

(4) View the parameter

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

That is, this parameter is a real-time effective parameter

(5) Try to use

test=# alter system set deadlock_timeout=’1s’;The errors are reported as follows:ERROR: syntax error at or near “system”LINE 1: alter system set deadlock_timeout=’1s’;Explanation found: Thealter system command is only valid for versions after 9.4,

(6) Try to execute

set deadlock_timeout=’1s’edb=# show deadlock_timeout;displayed as 1sbut the rest of the users log in and find that the parameter is still 3s

— — This method is set for the session level , similar to Oracle’s alter session set

(7) Try to modify the pg configuration file, enter the/u01/app/data/data_utl path to modify

bash-4.1$ pwd/DATA/data_utlbash-4.1$ lsbase pgbin pg_ident.conf pg_notify pg_stat_tmp pg_twophase postgresql.conf postmaster.optsdbms_pipe pg_clog pg_log pg_serial pg_subtrans PG_VERSION postgresql.conf.20191119 postmaster.pidglobal pg_xba.conf pg_t.pg_nbavi postgresql.confmodify the deadlock_timeout configuration to 1s

(8) Reload the database

bash-4.1$ ./pg_ctl reloadserver signaled

(9) Check that the parameter has taken effect

edb=# show deadlock_timeout;deadlock_timeout1s(1 row)Exitedb=# \qStep 9 can also take effect by restarting the database.Stop command:./pg_ctl stop -m fastStart command:./pg_ctl -D/u01/app/data/data_utl start

— — — — — — — — — — — — — — — —



Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS