ERROR: must be owner of extension plpgsql

[Mike Hardy]

Hey Guys, I am trying to migrate our postgres DB from compute engine to Cloud SQL. I've followed the documentation, but I'm getting two error messages. 

The first message is from the console and that says: 

 

ERROR: (gcloud.sql.instances.import) ERROR_RDBMS

I understand that's related to permissions, so I inspected the logs, and I see ERROR: must be owner of extension plpgsql. This is where I get stuck - any suggestions on how to fix this?

Thanks,
Mike

[Carlos (Cloud Platform Support)]

Hi Mike,

It does seems that the dump file you are trying to import includes some plpgsql statements requiring Superuser role. This role is certainly not available on hosted PostgreSQL.

It is unlikely that you need to import plpgsql related statements since by default, the database has the plpgsql extension already installed. So there is no need to install it again via the import.

The suggested workaround is to inspect you inspect your dump file and remove all sql statement related to "plpgsql".

[Mike Hardy]

Thanks Carlos, for anyone who comes across this problem, we solved it with this:

pg_dump -Fc --no-acl --no-owner -h <connection> -d <database> -U <user>

[Yevhen Sytnik]

Hello Mike, I've tried to use this comand pg_dump -Fc --no-acl --no-owner -h <connection> -d <database> -U <user> , but I've got error when tried restore database that dump file has custom format and should be use psql. Maybe you have some ideas. P.S. I've tried to migrate postgres db from another server to Google Cloud.

[Vladimir Rusinov]

Hi Yevhen,

I'm afraid we only support plain next sql dumps, so you will need to change '-Fc' to '-Fp' or '--format=p'.

--
Vladimir Rusinov
PostgreSQL SRE, Google Ireland

Google Ireland Ltd.,Gordon House, Barrow Street, Dublin 4, Ireland
Registered in Dublin, Ireland
Registration Number: 368047

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/6087c10d-13e9-4479-84cf-fa2ae9c2b62b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Stanislas drg]

The above code doesn't work in my case.

The following command :

pg_dump -Fp --no-acl --no-owner -h localhost -d test2 -U myusername --file sth.sql

Returns : 

SET SET SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

Tbh, I find it baffling that the command provided in [the documentation](https://cloud.google.com/sql/docs/postgres/import-export/creating-sqldump-csv) doesn't work either (returns the same error as above).

I've been trying to overcome this for the past three hours using numerous methods. I've recreated an instance, dumped a test and production DB using various combinations, tried to DROP plpsql and change user privileges. Nothing worked so far.

I've also carefully looked at this [S.O thread](https://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database)

Contrarily to what is described for Amazon RDS, the error seems blocking (I can't see any changes in the DB).

What is the proper way to dump a simple DB as of now ?

Thank you for your help

[Stanislas drg]

The only thing that solved the issue in my case was commenting out all lines relating to plpgsql, by manually editing the dump.

I hope this helps anyone.

[James Petty]

The error you're seeing is annoying, but easily avoidable. The problem is that exports attempt to reimport the comments associated with extensions, and attaching the comment requires superadmin privileges. If you edit your export file to remove lines like "SET COMMENT ON EXTENSION <extension> TO ..." you should no longer have this problem.

[Tianzhou Chen]

The proper way to dump a single DB is via the export option from your console or via gcloud, which would skip dumping plpgsql stuff.

Using pg_dump directly will not work as it has the permission issue of plpgsql.

[Maksym Domarev]

so there is no way to import any big external db into gcloud, also, there is no way for master-slave config streaming replication... and 1 year passed. 

[Olu]

On the Cloud PostgreSQL, the documentation confirms that configuring External Masters are not supported. However, this is supported within the CloudSQL first and Second Generations. About the implementation on Cloud PostgreSQL, I can confirm that there are internal ongoing processes to ensure this feature is available soon.