HiveBrain v1.2.0
Get Started
← Back to all entries
gotchasqlMinor

Why does pg_dump output not include extensions?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whypg_dumpextensionsincludeoutputdoesnot

Problem

I'm trying to do a backup / restore of a PostgreSQL 11 database from an RDS instance using pd_dump. The documentation states:

When pg_dump is used, the CREATE EXTENSION command will be included in the dump, followed by the set of GRANT and REVOKE statements necessary to set the privileges on the objects to what they were at the time the dump was taken.

However, when I run pg_dump -n ..., there are no CREATE EXTENSION statements in the output, and (unsurprisingly) extensions are not created on restore.

Querying pg_extension on the source database returns the following information about extensions installed:

extname
extowner
extnamespace
extrelocatable
extversion
extconfig
extcondition

plpgsql
10
11
false
1.0

uuid-ossp
10
2200
true
1.1

The log from pg_dump shows that it is reading extensions and identifying extension members.

What do I need to do to get the extensions to be backed up in the dump?
How can I debug why they are not being backed up?

Solution

pg_dump -n does not dump extensions (CREATE EXTENSION statements), because PostgreSQL extensions do not—perhaps surprisingly so—belong to specific schemas. Extensions have a “target schema”, but are not wholly subordinate to this schema, as extensions can encompass objects from multiple schemas.

[…] a non-relocatable extension can contain objects spread across multiple schemas – Pg. 15. Extension Relocatability

Context

StackExchange Database Administrators Q#297985, answer score: 4

Revisions (0)

No revisions yet.