I made a PostgreSQL’s module called shutdown_db using weekend time.

This module emulates the Oracle’s shutdown commands by controlling access for each DB.

This looks like a joke program, but it really works.

How to use

After installation and restarting your server, access to the postgres database.

$ psql postgres

psql (12.2)
Type "help" for help.

If you want to prohibit access to the database test3, execute the shutdown_db.shutdown_transactional() function.

postgres=# SELECT shutdown_db.shutdown_transactional('test3');
 shutdown_transactional 
------------------------
 
(1 row)

The shutdown_db.show_db_list view shows the list of shutdown databases.

postgres=# SELECT * FROM shutdown_db.show_db_list;
 dbid  | datname |     mode      | num_users | killer_process_running 
-------+---------+---------------+-----------+------------------------
 24929 | test3   | TRANSACTIONAL |         0 | f
(1 row)

The database test3 is no longer accessible.

$ psql test3
psql: error: could not connect to server: FATAL:  database "test3" is not currently accepting connections

This module provides four modes: shutdown_normal, shutdown_abort, shutdown_immediate and shutdown_transactional.

postgres=# SELECT shutdown_db.shutdown_immediate('test1');
 shutdown_immediate 
--------------------
 
(1 row)

postgres=# SELECT * FROM shutdown_db.show_db_list;
 dbid  | datname |     mode      | num_users | killer_process_running 
-------+---------+---------------+-----------+------------------------
 24929 | test3   | TRANSACTIONAL |         0 | f
 24927 | test1   | IMMEDIATE     |         0 | f
(2 rows)

If you want to start the shutdown database, execute the shutdown_db.startup() function.

postgres=# SELECT shutdown_db.startup('test3');
 startup 
---------
 
(1 row)

WARNING

1. Do not use ALTER DATABASE ALLOW_CONNECTIONS if you use this module. The reason why is that this module internally uses ALTER DATABASE ALLOW_CONNECTIONS command, so inconsistency in administrative data occurs.

2. The postgres database cannot be shutdown because it stores all functions to operate this module.