1

This question already has an answer here:

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

So I am trying to truncate and load new data into databases on 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. Need help on how to overcome this problem.

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

marked as duplicate by Erwin Brandstetter postgresql Feb 7 '17 at 19:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

  • Do i need to do anything after maintenance is done? – uberrebu Feb 7 '17 at 20:26
  • 1
    Yes, SET LIMIT -1 == unlimited, or if you need some restriction - another value – Roman Tkachuk Feb 7 '17 at 20:27

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