Export Database from Google Cloud Sql to external Database

Question

I'm trying to export my database created in Google Cloud Sql and import it into a new external server.

I tried to create a sql backup through the google console, downloaded it and copied it to the new server via filezilla and then launched the following command:

psql -U postgres -d ciclods-db -1 -f Backup-db_Cloud_SQL_Export_2019-03-23\ \(17_01_19\)

but i get this output:

ERROR: role "cloudsqladmin" does not exist REVOKE

ERROR: role "cloudsqlsuperuser" does not exist GRANT

what is the right procedure to follow in these cases?

Aristate

I am facing the same issue. Did you find any solution. – Overuse

I had the same issue and I just deleted these two lines from my script so I imported with success. I know that it is not the best to do however I just wanted to load the script in my machine to take a look into the data. Worked for my scenario. – Quinquennial

faceing the same problem. Did anyone find solution for this? – Marlea

Answer 1

In my case, my SQL file was around 10 GB. Hence, it wasn't possible to remove these two lines directly using the convention text editor. After searching around, I found that I could use sed to remove them. Here is the command:

sed '/cloudsqladmin\|cloudsqlsuperuser/d' backup.sql > fixedbackup.sql

Biegel

Answer 2

I have resolved the same problem by locating and deleting the two lines from the exported sql file with "cloudsqladmin". My app does not use it anyway.

Marlea

Answer 3

to do this task you can follow the official GCP guide about How to export data from Cloud SQL[1] in that document they give you the option to export the data into a dump file or csv files which can be used for other tools.

https://cloud.google.com/sql/docs/mysql/import-export/exporting

Araldo

yes but that file export roles that not exist and this raise an error-> ERROR: role "cloudsqladmin" does not exist REVOKE – Aristate

Answer 4

In order to create the export file, you have to do it from a command line and use additional flags. As per documentation‘s “Exporting data to a SQL dump file”, there is a section on Exporting data from an externally-managed database server.

As well you can find there the option to export the data into a CSV file.

Araldo