Summary: in this tutorial, you will learn step by step how to rename a PostgreSQL database even with the open connections.
PostgreSQL rename database steps
To rename a PostgreSQL database, you use the following steps:
- Disconnect from the database that you want to rename by connecting to a different database.
- Check and terminate all connections to the database that is being renamed.
- Use
ALTER TABLEstatement to rename the database to the new one.
Let’s take a look at an example of renaming a database to the new one.
Suppose the database that you want to rename is db. If the db database is not available in your database server, you can create it by using the CREATE DATABASE statement as follows:
1 | CREATE DATABASE db; |
To rename the db database to newdb database, you follow these steps:
First, disconnect from the database that you want to rename by connecting to another database e.g., postgres. If you use the psql command line, you can use the following command to connect to the postgres database:
1 | db=# \connect postgres; |
Next, check the all active connections to the db database by using the following query:
1 2 3 4 5 6 | SELECT * FROM pg_stat_activity WHERE datname = 'db'; |
The query returned the following output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -[ 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, currently, there is one connection to the db database.
You may find that the database that 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:
1 2 3 4 5 6 | SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db'; |
After that, rename the db database to newdb by using the ALTER TABLE RENAME TO statement as follows:
1 | ALTER DATABASE db RENAME TO newdb; |
Finally, if your database is being used by applications, you should modify the connection string.
In this tutorial, you have learned how to rename a PostgreSQL database to a new one by using the ALTER TABLE RENAME TO statement.