Re: Backup Functions

On Wed, Aug 18, 2021 at 10:14 AM Sridhar Parepalli <
Sridhar(dot)Parepalli(at)sas(dot)com> wrote:
> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.