Re: Backup Functions - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: Backup Functions |
| Date | |
| Msg-id | CAKFQuwYPU78VPCGigX99cFr+tZXaokHw03bVMBXxrJKm+obRwA@mail.gmail.com Whole thread Raw |
| In response to | RE: Backup Functions (Sridhar Parepalli <Sridhar.Parepalli@sas.com>) |
| List | pgsql-sql |
On Wed, Aug 18, 2021 at 10:14 AM Sridhar Parepalli <Sridhar.Parepalli@sas.com> wrote:
> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>> Functions belonging to extensions are not included in pg_dump output, maybe that explains your results? It's hard to be sure without a lot more specifics than you've provided.
Here's what I got from the \df ...
[...]
public | pgpool_remote_start | boolean | remote_host text, remote_data_directory text | func
public | pgpool_switch_xlog | text | arcive_dir text | func
(43 rows)
#pg_dump -Fd --lock-wait-timeout 300 -d tenant1 -n public -f /pgbackup/tenant1_public_20210816_233807
However, when tried to dump it does not include all functions.
-- PostgreSQL database dump-- Dumped from database version 11.10
-- Dumped by pg_dump version 11.10
CREATE SCHEMA public;
CREATE FUNCTION public.dmc_update_row_modified_function() RETURNS trigger
-- PostgreSQL database dump complete
======================================================================
David Johnston:
So, from the \df output I observe there are two or three extensions installed in this database and their functions were installed into the public schema. The pg_dump dumps the public schema but does not dump objects belonging to extensions. Instead it should (POLA violation, since WITH SCHEMA public should make this possible) dump the CREATE EXTENSION command itself. It will do so for a non-schema-limited dump. However, it does not do that for a limited dump - confirmed by way of observing that the reported pg_dump file output does not include any CREATE EXTENSION commands. This is due to the fact, as noted on the CREATE EXTENSION page, that "Remember that the extension itself is not considered to be within any schema...". Also, as noted on the pg_dump page, when choosing to specify "-n", your dump may not be a fully consistent dump. While the warning only talks about dependent objects, in addition to the obvious fact that other schemas are omitted, this "not considered to be within any schema" omission comes along for the ride.
In short, as Tom said, you have extensions installed in this database. To restore those extensions either create a complete dump file (i.e., don't use the -n switch) or one of the adjustments you will need to make to your restore script will be to add "CREATE EXTENSION ... WITH SCHEMA public" commands to it.
David J.