4

I'm trying to import dump with extensions it it, excerpt from SQL:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;
COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value) pairs';

Import (through Gcloud SQL UI) fails with SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql.

That is an understandable error (GCloud service user does not have the permissions), but it doesn't help. I cannot control restore process if triggered by UI and set ownerships manually, Google cloud SQL does not allow giving a role superuser permissions (and doing so is not a good way to deal with this).

What is best course of action in such situation?

Олексій Донога
  • 171
  • 1
  • 5

1 Answers1

3

So long no one responded, so I'll tell how I've overcome this.

  1. Connect to your instance as postgres user, connect to template1 DB and install required extensions (if they're in the list of supported ones).

  2. Drop and recreate your DB from Cloud SQL UI. Maybe manual works too, but I decided not to test. UI tells you that new DB will be created using template1.

  3. Modify your dump and comment out any extension creation.
  4. Import via UI and hope it works.

In my case there were only plpgsql and hstore extensions, so no problem.

Олексій Донога
  • 171
  • 1
  • 5