Dump/restore Postgres DB without extensions

Question

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.

Answer 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

• F.P: Unfortunately, we're using pg10, so -e is not available :-(
• Laurenz Albe: v10 will go out of service in a year or so. A good opportunity to upgrade.
• F.P: I will pass it along ;-)
• nsantana: Worked to me using pg_dump -e plpgsql [other options] mydb. pg_dump version: 16.6