gotchasqlMinor
Why does pg_dump output not include extensions?
Viewed 0 times
whypg_dumpextensionsincludeoutputdoesnot
Problem
I'm trying to do a backup / restore of a PostgreSQL 11 database from an RDS instance using
When
However, when I run
Querying
extname
extowner
extnamespace
extrelocatable
extversion
extconfig
extcondition
plpgsql
10
11
false
1.0
uuid-ossp
10
2200
true
1.1
The log from
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?
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.