RDS Postgres to Cloud SQL Migration issue

[overtheplanet]

I'm trying to migrate our Postgres database from RDS to Cloud SQL using the migration tool, and I'm getting an error during the setup of the replication process.

I have followed the documentation on granting all the privileges on the source, however, the migration still errored out with the following error message

"error restoring predata: failed to restore schema: stderr=pg_restore: error: could not execute query: ERROR: must be superuser to create a base type Command was: CREATE TYPE public.raster; pg_restore: error: could not execute query:..." (The actual error message is long and I just extracted a portion of it)

As far as I understand it, RDS doesn't allow you to connect using superuser, and it seems migration requires superuser to proceed?

If anyone can shed some light for me on how to solve this issue that would be awesome.

[ms4446]

The error related to the need for superuser privileges for creating a base type is a common issue due to the restricted superuser access in RDS.

To address this, consider the following approaches:

• Dedicated User with Enhanced Privileges:

  • Create a user on the RDS instance with the highest possible privileges. While this user won't have true superuser capabilities (a limitation in RDS), they should have enough permissions, like CREATE, ALTER, and DROP, for the migration. However, be aware that some operations requiring superuser privileges might still be restricted.

• Utilize pg_dump with Specific Options:

  • Employ pg_dump with options tailored to your migration needs. While the --no-owner option can help with ownership issues, it might not resolve the creation of certain database objects. Consider using --exclude-table or --exclude-object to bypass problematic objects. After the migration, you can manually recreate these objects in Cloud SQL.

• Intermediate Database Step:

  • Use an intermediate PostgreSQL instance where you have superuser access. Migrate your RDS database to this instance, make necessary adjustments, and then proceed with the migration to Cloud SQL. This step allows more control over the database objects and their creation.

Each migration scenario is unique, so it's crucial to evaluate these approaches based on your database schema's complexity, the number of databases involved, and your requirements for control over ownership and permissions post-migration.

[overtheplanet]

Thanks for the quick reply. It would be ideal if I'm able to use the Cloud SQL migration tool to migrate from RDS. I would have thought it would be a standard process given RDS Postgres is in the dropdown selection for the source database

[ms4446]

It's understandable that you'd prefer using DMSl for migrating from RDS to Cloud SQL, especially since RDS is listed as a source database option in the tool. Even though RDS is listed as a source in the tool, you might face some challenges because of the superuser restrictions in RDS. Here are some ways to handle this:

1. Custom Migration Options:

   • Look into the migration tool's advanced options. There might be ways to get around the need for superuser access. For example, you can find settings that let you skip certain steps or use objects that are already created.

2. Cloud DMS Replication:

   • The Cloud DMS has a feature that keeps copying data as it changes. This can help avoid the initial step of moving your whole database, which is where you usually face superuser issues. But remember, you need to set up your database structure on Cloud SQL first before you start this.

3. Fine-Tuning Permissions:

   • Try changing the permissions of your database user in RDS. Give them only the specific rights they need for each part of the migration. This might help reduce the need for superuser access. But remember, in RDS, even the most powerful user isn't a true superuser, so some things might still not be possible.

4. Seek Google Cloud Support:

   • If you're stuck, it's a good idea to contact Google Cloud Support. They can look at your specific situation and suggest ways to work around the superuser issue.

Keep in mind that while RDS is supported by the Cloud SQL migration tool, you might still need to make some changes because of the superuser limitations in RDS. The tips above can help you use the tool effectively.

If these methods don't solve the superuser problem, you might need to think about other ways to move your database, like using a manual process or an intermediate database. The best method depends on what you need, what resources you have, and how much you know about databases. Also, it's always good to keep up with the latest guides and advice for both AWS RDS and Google Cloud SQL.