1

I'm working on a script that "deploys" a DB from one environment to another (read: dump on A, restore on B).

For this, I utilize the built-in pg_dump and pg_restore:

pg_dump -v --host="$PGHOST" --username="$PGUSER" \
    --format=c \
    --no-password \
    --no-privileges \
    --no-owner \
    mydb > mydb.pg_dump
pg_restore -v --host="$PGHOST" --username="$PGUSER" --dbname="$PGNAME" \
    --format=c \
    --single-transaction \
    --clean \
    --if-exists \
    --no-password \
    --no-privileges \
    --no-owner \
    mydb.pg_dump

Howerver, because the target dbs is managed by our DevOps team, the user I'm operating under doesn't have all privileges, which results in this error when restoring:

pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension pg_stat_statements
Command was: DROP EXTENSION IF EXISTS pg_stat_statements;

How can I disable dump/restore of extensions? All I want is that the schema and data is replaced on the target system.

1 Answer 1

1

You can use the -e option that allows you to select a pattern for all extensions to be dumped. If you want none dumped, use pg_catalog.plpgsql, because objects from the system schema will be excluded:

pg_dump -e pg_catalog.plpgsql [other options] mydb
3
  • Unfortunately, we're using pg10, so -e is not available :-(
    – F.P
    Dec 6, 2021 at 10:11
  • v10 will go out of service in a year or so. A good opportunity to upgrade. Dec 6, 2021 at 10:14
  • I will pass it along ;-)
    – F.P
    Dec 6, 2021 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.