September 23, 2023
Summary: In this tutorial, you will learn step by step how to rename a PostgreSQL database using the ALTER DATABASE RENAME TO
statement.
Table of Contents
PostgreSQL rename database steps
To rename a PostgreSQL database, you use the following steps:
- 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.
Let’s take a look at an example of renaming a database.
The following statement creates a new database called db
:
CREATE DATABASE db;
To rename the db
database to newdb
, you follow these steps:
First, disconnect from the database that you want to rename and connect to another database e.g., postgres
. If you use psql tool, you 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 db
database.
Next, check the all active connections to the db
database by using the following query:
SELECT *
FROM pg_stat_activity
WHERE datname = 'db';
The query returned the following output:
-[ RECORD 1 ]----+------------------------------
datid | 35918
datname | db
pid | 6904
usesysid | 10
usename | postgres
application_name | psql
client_addr | ::1
client_hostname |
client_port | 56412
backend_start | 2017-02-21 08:25:05.083705+07
xact_start |
query_start |
state_change | 2017-02-21 08:25:05.092168+07
waiting | f
state | idle
backend_xid |
backend_xmin |
query |
As you can see clearly from the output, there is only one connection to the db
database.
You may find that the database, which you want to rename, has many active connections. In this case, you need to inform the respective users as well as the application owners before terminating the connections to avoid data loss.
Then, terminate all the connections to the db
database by using the following statement:
SELECT
pg_terminate_backend (pid)
FROM
pg_stat_activity
WHERE
datname = 'db';
After that, rename the db
database to newdb
using the ALTER DATABASE RENAME TO
statement as follows:
ALTER DATABASE db RENAME TO newdb;
Last but not least, if your database is being used by applications, you should modify the connection strings.
In this tutorial, you have learned how to rename a PostgreSQL database to a new one by using the ALTER DATABASE RENAME TO
statement.