2

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.

0
2

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.

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.