Postgresql rename1/10/2024 Connect to some other database other than the one you are trying to rename such as say the postgres db.In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. /batch/renamedatabase.sql was created with a unicode byte-order mark. Head to Data -> table-name -> Relationships in the. Just one of the ways PgAdmin III lets us down. An existing relationship can be renamed as follows: Console CLI API. If you are using PgAdmin III, you will not see this option. How do you rename this database? Solution: Last but not least, if our database is being used by applications, we will need modify, and/or inform application owners, the connection strings.You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. 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. 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. ALTER TABLE IF EXISTS ONLY name RENAME COLUMN columnname TO newcolumnname ALTER TABLE IF EXISTS ONLY name RENAME CONSTRAINT constraintname TO newconstraintname ALTER TABLE IF EXISTS name RENAME TO newname ALTER TABLE IF EXISTS name SET SCHEMA newschema ALTER TABLE ALL IN TABLESPACE name OWNED.Disconnect from the database that you want to rename and connect to a different database.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 |