DEV Community

Cover image for PostgreSQL: Rename database
nabbisen
nabbisen

Posted on

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
;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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';
;
Enter fullscreen mode Exit fullscreen mode

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';
;
Enter fullscreen mode Exit fullscreen mode

Finally, stop the service as needed:

# # in the case of OpenBSD's rc, for example:
# rcctl stop $DAEMON
Enter fullscreen mode Exit fullscreen mode

That's about it.

A side note is longer than main content 😅

Oldest comments (2)

Collapse
 
helenanders26 profile image
Helen Anderson

Nice post! Thanks for sharing

Collapse
 
nabbisen profile image
nabbisen

Thanks, too, for commenting, Helen 😊