To rename the db database to newdb, you follow these steps:įirst, disconnect from the database that you want to rename and connect to another database e.g., postgres. The following statement creates a new database called db: CREATE DATABASE db Code language: SQL (Structured Query Language) ( sql ) Let’s take a look at an example of renaming a database. Use the ALTER DATABASE statement to rename the database to the new one.Check and terminate all active connections to the database that you want to rename.Disconnect from the database that you want to rename and connect to a different database.To rename a PostgreSQL database, you use the following steps: 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: in this tutorial, you will learn step by step how to rename a PostgreSQL database using the ALTER DATABASE RENAME TO statement. 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.Īfter that, rename the test_db database to newdb using the ALTER DATABASE RENAME TO statement as follows: ALTER DATABASE test_db RENAME TO newdb 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.Īfter informing all users, we can terminate all the connections to the test_db database by using the following statement: SELECT pg_terminate_backend( pid ) You may find that the database, which we want to rename, has many active connections. The query will return a similar output: -+-Īs we can see clearly from the output, there is only one connection to the test_db database. Next, let's check the all active connections to the test_db database by using the following query: SELECT * If we use psql tool, we can use the following command to connect to the postgres database: db=# \connect postgresīy connecting to the postgres database, you are automatically disconnected from the test_db database. We need to disconnect from the database that we want to rename and connect to another database e.g., postgres. To rename the test_db database to newdb, we will follow these steps: Let's begin with the following statement, that will create a new database called test_db: CREATE DATABASE test_db Now let’s see how to execute this kind of action by following the below example. 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. 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):
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |