Database Migration Failed with permission error to alter database

[Aa-ron]

I am trying to migrate between two Postgres Cloud SQL instances and get the following exception

"finished setup replication with errors: [my_db]: error restoring predata: failed to restore schema: stderr=pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4858; 0 0 DATABASE PROPERTIES my_db postgres
pg_restore: error: could not execute query: ERROR: permission denied to set parameter "plv8.start_proc"
Command was: ALTER DATABASE my_db SET "plv8.start_proc" TO 'plv8_init';

I get the same error running the ALTER DATABASE command in psql but after I run SELECT plv8_version() the ALTER DATABASE command runs successfully. 

[ms4446]

The error you're encountering is caused by the lack of permissions to set the plv8.start_proc parameter during the migration.

To resolve this issue:

• If you're not using the PLV8 extension in your source database, you can remove the reference to the plv8.start_proc parameter in the database dump before importing it into Cloud SQL.

• If you're using the PLV8 extension and need to keep it, you can temporarily grant the required permission to the user running the migration. To do this, connect to your source PostgreSQL database and run the following command:

GRANT pg_execute_server_program TO your_migration_user;

Replace your_migration_user with the username of the account you're using for migration. After running this command, try the migration again. If successful, remember to revoke the permission after the migration is completed:

REVOKE pg_execute_server_program FROM your_migration_user;

[Aa-ron]

Thanks for the quick reply. We do use plv8 so I'd need to keep that. When I try to run the grant command it doesn't allow me "grant or revoke of role "pg_execute_server_program" is not allowed" the source instance is a Cloud SQL instance as well so I don't think any of the cloudsqlsuperuser roles will have this ability. 

[ms4446]

You will need to create a new database dump without the plv8.start_proc parameter. To do this, use pg_dump with the --exclude-database-parameter option:

pg_dump --exclude-database-parameter=plv8.start_proc --dbname=my_db --file=my_db_dump.sql

After creating the new dump, import it into Cloud SQL.

[Aa-ron]

Is there any way to configure this within the Database Migration Service migration job or would I have to do a separate manual dump to do the migration?

[ms4446]

Unfortunately, DMS does not provide a direct way to exclude specific database parameters like plv8.start_proc during the migration job. The workaround would be to create a separate schema-only dump without the problematic parameter, and then use the migration job to transfer the data.

[Aa-ron]

It looks like the schema has copied over. I also have removed the `plv8` extension (it's something I can recreate) and restarted the instance but I am still getting the same error. When I do `SHOW ALL` I don't see this database setting listed either.

[ms4446 · Accepted solution]

If you're still getting the same error after restarting the instance and importing the database dump without the plv8.start_proc parameter, it's likely that the parameter is still present somewhere in the database dump or the schema that you imported.

To troubleshoot this issue, you can do the following:

1. Examine the database dump file you created in step 1. Look for any lines containing plv8.start_proc. If you find any references, remove them and then re-upload the updated dump file to the Google Cloud Storage bucket.

2. If you've already imported the dump file into the Cloud SQL instance, you can try dropping the database and then re-importing the updated dump file:

gcloud sql connect your-cloud-sql-instance --user=postgres --quiet

Replace your-cloud-sql-instance with the name of your Cloud SQL instance. Once connected, run the following SQL commands:

DROP DATABASE my_db;
CREATE DATABASE my_db;
\q

Replace my_db with the name of the database you're importing. After that, re-import the updated dump file:

gcloud sql import sql your-cloud-sql-instance gs://your-bucket-name/my_db_dump.sql --database=my_db

3. If you're still encountering the error, double-check that the migration process is using the correct dump file and database. Ensure that there are no other databases or instances on Cloud SQL that may be causing the error.