1

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.

| improve this 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.

| improve this answer | | | | |
  • 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.