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.

Answer 1

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.