Postgres: change database name without interruption?

Question

I have a production database that I'm told is "named incorrectly". Is there a way to change the name without interruption?

The database in question is named something like prod_api_db and should be named api_db.

I was hoping there would be a way to create an "alias" so to speak, such that both names are valid and point to the same database for a time period while all of the app configurations are updated with the new name. Once everything's updated, the old name could be removed.

 -- Stage 1, create alias "api_db":
apps ==> prod_api_db
 > actual_database
 api_db
-- Stage 2, Update app configs:
 prod_api_db
 > actual_database
apps ==> api_db
-- Stage 3, Remove old name:
apps ==> api_db > actual_database

Does that sort of functionality exist in postgres?

Just for background, this is postgres 11. I do have dev, qa, and production servers to work with, so I can test before apply a change.

Pardon me if I'm being very ignorant about this. I'm not a DBA... just an SRE working for a small company who's been given charge of some database, and I haven't found a good answer yet.

• user1822: No, there is no such thing. I don't think this can be done without any interruption.

Answer 1

No, a database can have only a single name.

You will have to disconnect all sessions before you can rename the database.

You will have to update the database name in all connection strings. The latter can be avoided if you use LDAP connection string lookup, but I guess you don't want to run an LDAP server in a small organization.

Answer 2

Depending on size, and your application needs: You could consider to make the database read-only, dump it, and restore it under a new name. For instance, if your app is a web-shop, it would still be able to serve, but no new articles or orders can be posted.

psql -c 'ALTER DATABASE foolish_db_name SET default_transaction_read_only = true;'
pg_dump -f dumpfile -F custom foolish_db_name
createdb better_name
pg_restore -d better_name --single-transaction dumpfile

Reconfigure your app to point to the new DB and drop the old DB:

dropdb foolish_db_name

• vastlysuperiorman: +1 for a clever solution. In my case, the application is quite write-heavy, so I think I'm better off just stopping the application for a few minutes during a maintenance window to alter the name and connection strings.
• Jasen: could possibly pipe data between pg_dump and pg_restore for less latency.

Answer 3

You might be ale to squeeze a proxy (eg: pg_bouncer) in between your application and postgres. then define both names in the proxy

Then reconfigure your application to use the new name then finally rename the database and remove the proxy.

Alternatively you might be able to fake it using foreign data wrappers to allow queries against the old database name to access the new name.