transfersite - psql extension plpgsql error

Support
1

Migrating a site with a PostgreSQL database from 10.23 fails during Pgsql_Module::import(). The dump includes COMMENT ON EXTENSION plpgsql (pre-pg11 pg_dump behavior), which fails with ‘must be owner of extension plpgsql’ since the import runs as the site user. That’s a known benign error on restore, but the import treats it as fatal. Suggest filtering extension comment lines from the dump or not treating that specific error as fatal.

Old server: psql 10.23
New server: psql 16.14

Reason: Util_Process::formatDataCallProc(): psql: psql:/home/virtual/site26/fst/tmp/logichosts_portal.sql:30: ERROR:  must be owner of extension plpgsql

Pgsql_Module::import(): import failed: psql:/home/virtual/site26/fst/tmp/logichosts_portal.sql:30: ERROR:  must be owner of extension plpgsql
2

Migrations are 1:1. Changing ownership on a stored procedure during migration violates this contract.

What does cpcmd -d DOMAIN pgsql:get-owner DBNAME report? Where DBNAME is the full database name (including prefix).

3 
~]# cpcmd -d logichosts.com pgsql:get-owner logichosts_portal
logichosts
4

There’s not a good, safe way around this. You’ll need to save a copy of the database export, then delete the stored procedure (or database), then create on the target server.

We can’t run arbitrary SQL commands as superuser and there’s no way to reliably switch users as a non-superuser during import.

1 Like
5

For posterity, in my case the extension was unused so this was my fix to transfer.

After confirming on that database:
psql -d logichosts_portal -c "SELECT e.extname, d.description FROM pg_extension e LEFT JOIN pg_description d ON d.objoid = e.oid;"

And checking if anything else actually uses plsql
psql -d logichosts_portal -c "SELECT proname FROM pg_proc p JOIN pg_language l ON p.prolang = l.oid WHERE l.lanname = 'plpgsql' AND p.pronamespace = 'public'::regnamespace;"

Both turned up nothing so I nulled it.
psql -d logichosts_portal -c "COMMENT ON EXTENSION plpgsql IS NULL;"

Confirmed via:
pg_dump logichosts_portal | head -40

Then was able to complete a transfer with no more error.