Post-migration troubleshooting

Renaming databases and roles

Crunchy Bridge assigns random strings as the names for roles and databases. You may want to change these, for example to align roles and databases that you've migrated into Crunchy with ones that are created natively on Bridge.

Renaming a database

It's possible to change the name of your database, even changing it from random_string_db to postgres. However, nothing can be connected to the database when it is renamed, including yourself. The solution is to connect to the template1 database to carry out the action.

Here's an example where we rename random_string_db database to postgres. Note that we first need to rename the existing postgres to something else:

\c template1
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'postgres';
ALTER DATABASE postgres rename to postgres_old;
ALTER DATABASE random_string_db rename to postgres;
Copy

Reassigning ownership from one role to another

PG::InsufficientPrivilege: ERROR: must be owner of table my_table
Copy

Instead of changing the name of a role, it can be easier to reassign ownership of the role's objects to another role. This is often useful when you want to give the application role ownership of tables it needs to function appropriately.

To change the ownership of all the objects owned by (for example) 123456789abc_role to be owned by application you can use REASSIGN OWNED as documented here. First connect to the database where you are changing ownership, then use the REASSIGN OWNED command:

\c random_string_database
REASSIGN OWNED BY 123456789abc_role TO application;
Copy

Installing pg_stat_statements

Crunchy Bridge-created databases should already have pg_stat_statements installed. If you've migrated a database to Crunchy Bridge that was created elsewhere, you'll want to make sure to create that to ensure that the Insights dashboard will work by running:

\c postgres
CREATE extension pg_stat_statements;
Copy