* The cover image is originally by 12091 and edited with great appreciation.
This post shows a tiny tip about PostgreSQL: how to rename exsting databases.
Suppose that you use PostgreSQL as database and want to change name of database: For example, your service name has changed; Your domain has been renewed, etc. It might be usually indispensible, but good in order to avoid confusion.
What is necessary is to connect your database server and run this
ALTER DATABASE sql:
ALTER DATABASE $DB_NAME_AS_IS RENAME TO $DB_NAME_TO_BE ;
That's it 🙂
There may be a few things to do beforehand.
When your service is active and online, you should stop it in order to keep data in it clean:
- Validate no connection to the database exists
- If some exist, cancel the process
- Stop the service
SELECT pid FROM pg_stat_activity WHERE datname = '$DB_NAME_AS_IS';
If some pid(s) exist, try to send SIGINT signals for them to be canceled with
pg_cancel_backend, a part of PostgreSQL's System Administration Functions:
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME_AS_IS'; ;
pg_terminate_backend is also available to force them to die immediately by sending SIGTERM signals. Use this very carefully.
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME_AS_IS'; ;
Finally, stop the service as needed:
# # in the case of OpenBSD's rc, for example: # rcctl stop $DAEMON
That's about it.
A side note is longer than main content 😅