How do I dump a Google Cloud SQL for PostgreSQL DB to import back into a regular PostgreSQL DB?

2
votes

I am trying to export my data from a Google Cloud SQL (PostgreSQL) instance in order to import it into a regular Postgres DB using pg_dump and pg_restore:

pg_dump -h sql_proxy -F t --no-owner --no-acl > backup.tar
pg_restore backup.tar -c

However, when running pg_restore I get these errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 197; 1259 17010 TABLE xxx postgres pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres" does not exist Command was: ALTER TABLE public.xxx OWNER TO postgres;

pg_restore: [archiver (db)] Error from TOC entry 198; 1259 17017 TABLE xxy postgres pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres" does not exist Command was: ALTER TABLE public.xxy OWNER TO postgres;

...

I tried a few variations of flags with no luck. I found many articles on how to migrate the other way around (from PostgreSQL to Google Cloud SQL for PostgreSQL) and the Google Cloud docs only describe how to export data to be imported into a Cloud SQL DB again.

I would appreciate any help on how to avoid the errors above and how to migrate the DB with as little changes as possible.

1
postgresqlgoogle-cloud-platformgoogle-cloud-sql

1 Answers

1
votes

You need to have the roles that are referenced already pre-created in the instance where you want to import the dump.

There are two ways to achieve that:

  • use pg_dumpall instead of pg_dump or
  • pg_dumpall --globals-only and then restore that dump (this will create the roles among other things)