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;
Reassigning ownership from one role to another
PG::InsufficientPrivilege: ERROR: must be owner of table my_table
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;
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;