How to rename a PostgreSQL database?

Renaming a PostgreSQL database

You may need to rename a PostgreSQL database from time to time, such as when you migrate data from one account or hosting provider to another, or during site development.

The steps to rename a PostgreSQL database depend on whether or not your A2 Hosting account includes cPanel access.

Managed accounts with cPanel

If your A2 Hosting account includes cPanel access, you can use phpPgAdmin to rename a PostgreSQL database. You must export the database, create a new database, and then import the data from the old database into the new database.

To do this, follow these steps:

1. Log in to cPanel.
2. To export data from the existing database, follow these steps:
   • In the Databases section of the cPanel home screen, click phpPgAdmin. The phpPgAdmin administration page appears in a new window.
   • In the left pane of the phpPgAdmin window, expand Servers, expandPostgreSQL, and then click the name of the database that you want to export.
   • On the top menu bar, click Export.
   • In the Format column, click Structure and data.
   • In the Options column, in the Format list box, select SQL.
   • Under Options, click Download.
   • Click Export.
   • In the file dialog, select a location and filename for the file on your local computer, and then click Save.
3. To create a new database, follow these steps:
   • In the Databases section of the cPanel home screen, click PostgreSQL Databases.
   • Under Create New Database, type the name of the database in the Database Name text box.
   • Click Create Database.
   • After the database is created, click Go Back.
   • Under Add User to Database, select the user that you want to add in the Userlist box.
   • In the Database list box, select the new database.
   • Click Submit.
4. To import the data into the new database, follow these steps:
   • In the Databases section of the cPanel home screen, click phpPgAdmin.
   • In the left pane of the phpPgAdmin window, expand Servers, expandPostgreSQL, and then click the name of the database that you created in step 3.
   • On the top menu bar, click SQL.
     The SQL link is located between the Schemas and Find links.
   • Click Browse.
   • In the file dialog, select the file that you saved on your local computer in step 2, and then click Open.
   • Click Execute. phpPgAdmin imports the data from the file into the new database.
5. The new database now contains all of the tables and data from the old database.
   You can delete the old database if you want, but this is not required. In fact, you may want to keep it as an archive copy.

Semi-managed accounts without cPanel

If your A2 Hosting account does not include cPanel, you can quickly and easily rename a PostgreSQL database from the command line. To do this, follow these steps:

1. Log in to your server using SSH.
2. At the command prompt, type the following command to connect to PostgreSQL. Replace DBNAME with the name of a database that is not the database you want to rename, and replace USERNAME with a PostgreSQL username that has access privileges to the database:

   psql DBNAME USERNAME

   You cannot rename a database while you are currently connected to it.
3. To rename the database, type the following command. Replace OLD_DBNAME with the name of the database that you want to rename, and replace NEW_DBNAME with the database's new name:

   ALTER DATABASE "OLD_DBNAME" RENAME TO "NEW_DBNAME"

4. To verify that the database was renamed, type the following command to list the databases:

   \list

Resource Link: https://www.a2hosting.com/kb/developer-corner/postgresql/renaming-a-postgresql-database