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

Question

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.

I faced a similar issue and discussed my solution here stackoverflow.com/questions/70648013/… — Coola
– Coola, Commented Mar 25, 2024 at 0:02

steve · Accepted Answer · 2020-01-16 02:51:14Z

3

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)

answered Jan 16, 2020 at 2:51

steve

6,0301 gold badge23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nima Over a year ago

Thank you @steve. But pg_dumpall gives me this error: query failed: ERROR: permission denied for table pg_authid. Any idea?

Stefan G. Over a year ago

Hey Nima, I think this might help you with the permission error. Have a look. stackoverflow.com/questions/39329228/…

Nima Over a year ago

I ended up creating the postgres role manually on the target database because it seems Cloud SQL doesn't give permissions necessary to make pg_dumpall work to users.

Jose V Over a year ago

Hi Nima, if you were able to find the solution, can you post it as an answer? It will make it more visible and help someone with the same issue as you find the solution. Thanks!

p13rr0m Over a year ago

This issue may be relevant here: issuetracker.google.com/issues/148423505 The problem is that CloudSQL does not allow granting superuser privileges to any role. This is way you are getting an error when executing pg_dumpall or pg_dumpall --globals-only

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related