Lock or disable connections to Postgres while performing maintenance

Question

I never have this issue with MySQL, but PostgreSQL is always a pain when trying to do any kind of maintenance.

I am trying to truncate and load new data into databases on a PostgreSQL server, but there are usually active connections, and this messes up things. There is also one particular database I need to drop and create, and current active doesn't allow me to drop it.

All these are setup in a script that should run. My issue is active connections is killing me and not letting the script work.

Do I lock connections to the DB or block connections? How do I do this? Not as familiar with PostgreSQL as I am with MySQL.

score 3 · Answer 1 · edited Jun 13 '17 at 03:00

3

You can set connection limit for role:

ALTER ROLE x SET CONNECTION LIMIT 0;

or for databases:

ALTER DATABASE db SET CONNECTION LIMIT 0;

After your maintenance chores are done, reset your limit to its previous value such as -1 for unlimited connections.

edited Jun 13 '17 at 03:00

joanolo

13,397
7
36
65

answered Feb 07 '17 at 18:58

Roman Tkachuk

560
3
8

1

Note: you risk leaving the database inaccessible if the automated system that runs these commands fails. – Kind Contributor Mar 24 '21 at 11:51
If this happens, how to fix it? Just restart PostgreSQL? – Alexander Farber Mar 25 '21 at 08:14
Just do not do it for admin roles – Roman Tkachuk Mar 25 '21 at 13:16

Lock or disable connections to Postgres while performing maintenance

1 Answers1