4

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?

3
  • PL/pgSQL does not need to be dumped as it is always available in all modern Postgres version. The uuid-ossp should be dumped though. What is your Postgres version (select version();) and what is your pg_dump version (pg_dump --version) . I would suspect you are checking the extensions from a database different than the one you are dumping
    – user1822
    Aug 13, 2021 at 5:28
  • 4
    What is the command line you use with pg_dump? pg_dump -n ..., for example, does not dump extensions .
    – jjanes
    Aug 13, 2021 at 13:45
  • @jjanes I was using the -n argument. It was not obvious to me that extensions that were part of one of the specified schemas would not be included in the backup. If you will provide your comment as an answer, I will mark it as the accepted answer.
    – FisherKing
    Aug 13, 2021 at 16:26

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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