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
-
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.
-
The
postgresdatabase cannot be shutdown because it stores all functions to operate this module.