PostgreSQL: Rename database

* The cover image is originally by 12091 and edited with great appreciation.

---

Summary

Hello.
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 🙂

A side note

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:

1. Validate no connection to the database exists
2. If some exist, cancel the process
3. Stop the service

pg_stat_activity, a part of PostgreSQL's Statistics Collector subsystem gives a way to check process:

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';
;

Still remains?
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 😅

Comments

[Helen Anderson]

Nice post! Thanks for sharing

[nabbisen]

Thanks, too, for commenting, Helen 😊