- Newest
- Most votes
- Most comments
The fastest way to rename your database is with a single SQL command. The main requirement is that no users can be connected to the database while you're renaming it. The process involves connecting to a different database within the same cluster, ensuring no active sessions are on the target database, and then executing the rename command.
First, you'll need to connect to your database cluster, but not to the devpg database itself. The default postgres database is the ideal choice for this kind of administrative task. Once connected, you can ensure no one is using the devpg database by terminating any active sessions.
SQL
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'devpg';
After running that command, all connections to devpg will be closed, and you can safely rename it.
SQL
ALTER DATABASE devpg RENAME TO imppg;
This command only updates the metadata and is nearly instantaneous. You can verify the change by listing the databases in your cluster. In psql, the \l command will show you the new imppg name in the list.
Hello.
Do you want to change the "DB name" which can be seen in the RDS instance Configuration?
This value will display the "Initial database name" value entered when creating the RDS.
Therefore, the displayed content cannot be changed later.
As stated in the AWS re:Post Agent's automated AI response, if you absolutely must make the change, you will need to recreate the RDS and transfer the data.
When working with Aurora PostgreSQL, there's an important distinction between the DB instance identifier (the name AWS uses to identify your instance) and the actual database name within PostgreSQL.
Unfortunately, there isn't a direct way to rename an existing database within an Aurora PostgreSQL cluster without recreating it. Unlike Oracle RDS which provides a specific procedure for changing global database names, PostgreSQL doesn't offer an equivalent administrative procedure through RDS.
To change the database name in Aurora PostgreSQL, you would need to:
- Connect to your PostgreSQL database
- Create a new database with your desired name
- Export data from the old database and import it to the new one
- Update your application connections to use the new database name
You can query the existing databases in your Aurora PostgreSQL instance by running:
SELECT datname, datcollate, datistemplate, datallowconn
FROM postgres.pg_catalog.pg_database;
If you only need to change the DB instance identifier (the AWS resource name) rather than the actual PostgreSQL database name, you can do this through the AWS Management Console, AWS CLI, or RDS API by modifying the DB instance. This process will cause a brief downtime as the instance is rebooted.
Keep in mind that renaming a DB instance has implications such as changing the endpoint URL, which would require updating application connection strings.
Sources
Changing the global name of a database - Amazon Relational Database Service
Renaming a DB instance - Amazon Relational Database Service
Databases and schemas for T-SQL - SQL Server to Aurora PostgreSQL Migration Playbook
How to rename and retain the endpoint name for Amazon RDS | AWS Database Blog
Thank you, Arvind, for your guidance!
After receiving the automated AI response, I ended up using ALTER DATABASE to change the database name, as you described above. One big disadvantage with this approach vs. recreating the database is that AWS Console will continue to show the original database name in the Configuration section. But, for all practical purposes like JDBC connectivity and psql queries, ALTER DATABASE solution works well. Hope AWS comes up with an option to change the DB Name using the Console or API.
As a note for other users, terminating the final active session, pg_cron scheduler was difficult, requiring steps like dropping the pg_cron EXTENSION and removing pg_cron from shared_preload_libraries parameter. After successfully renaming the database, these were re-added and the cron.database_name parameter needed to be manually updated.
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated 2 months ago
