Rename an existing PostgreSQL Database


To totally unlock this section you need to Log-in

To rename a PostgreSQL database, we will need, in most cases, to follow the below steps (if we are not database owner or a superuser in PostgreSQL):

  • Disconnect from the database that you want to rename and connect to a different database.
  • Check and terminate all active connections to the database that you want to rename.
  • Use the ALTER DATABASE statement to rename the database to the new one.

IMPORTANT NOTE: the disconnection from the database we want to rename is usually not needed, so we can even go directly to the ALTER DATABASE statement to just rename directly the database we want to rename, but it is only possible if we are database owner or a superuser for the PostgreSQL instance.

Now let’s see how to execute this kind of action by following the below example.

Let's begin with the following statement, that will create a new database called test_db:

CREATE DATABASE test_db;

To rename the test_db database to newdb, we will follow these steps:

We need to disconnect from the database that we want to rename and connect to another database e.g., postgres. If we use psql tool, we can use the following command to connect to the postgres database:

db=# \connect postgres

By connecting to the postgres database, you are automatically disconnected from the test_db database.

Next, let's check the all active connections to the test_db database by using the following query:

SELECT  *
FROM pg_stat_activity
WHERE datname = 'test_db';

The query will return a similar output:

-[ RECORD 1 ]----+------------------------------
datid            | 23547
datname          | db
pid              | 7234
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | ::1
client_hostname  |
client_port      | 56412
backend_start    | 2020-02-21 08:25:05.083705+07
xact_start       |
query_start      |
state_change     | 2020-02-21 08:25:05.092168+07
waiting          | f
state            | idle
backend_xid      |
backend_xmin     |
query            |

As we can see clearly from the output, there is only one connection to the test_db database.

You may find that the database, which we want to rename, has many active connections. In this case, we will need to inform the respective users as well as the application owners before terminating the connections to avoid data loss.

After informing all users, we can terminate all the connections to the test_db database by using the following statement:

SELECT pg_terminate_backend( pid )
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
    AND datname = 'test_db';

NOTE: the pg_backend_pid is used to get our current Process ID (Session ID), basically it is the process ID of the server process handling the current session.

After that, rename the test_db database to newdb using the ALTER DATABASE RENAME TO statement as follows:

ALTER DATABASE test_db RENAME TO newdb;

Last but not least, if our database is being used by applications, we will need modify, and/or inform application owners, the connection strings.


Summary
Article Name
Rename an existing PostgreSQL Database
Description
Renaming a PostgreSQL is one of the activities that could be necessary during a normal database lifecycle, sometimes due to testing or development related activities linked to a pre-existing database. Let's see which PL/pgSQL statement we can use to rename a database.
Author
Publisher Name
Heelpbook.net