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.