Maintaining connectivity to PostgreSQL databases on different servers

Question

Currently i have 2 PostgreSQL servers

• PostgreSQL 9.4
  • Database1
  • Database2
  • Database3
• PostgreSQL 9.6
  • Database1
  • Database2
  • Database3

On each server sometimes i need to do schema modifications so i have to block all connections to that specific database and close all existing connections. Since the names of database are the same for some databases, what is the best way to do it? Any help is appreciated.

I'm using pgAdmin 4.1.3.

Whats the SQL code for disconnecting all existing and blocking all new connections and same for allowing new connections back?

Answer 1

select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid();

will kill all session but yours. You can use pg_cancel_backend to cancel instead of killing...

To block new connections you can do either of:

1. change port (requires restart)
2. change listen_addresses (requires restart)
3. add host all all 0.0.0.0/0 reject on top of other lines in pg_hba.conf (requires reload)
4. alter user set default_transaction_read_only = on for all users
5. update pg_database set datallowconn = false where datname = '...' or ALTER DATABASE "name" ALLOW_CONNECTIONS false if you run 9.5 or higher

The forth can be easily overcame, but does not require changes in config.

The fifth not meant for such usage, but definitely helpful here - thank you @Nick Barnes