Thread: Backup Functions

Backup Functions

From

Sridhar Parepalli

Date:

18 August 2021, 18:05:42

Hello experts,

I was just wondering should a schema of a database contain functions and triggering functions does the pg_dump include functions as it takes back up of the schema? I’m on postgres 11 version.

Here’s my command looks like

/pg_dump -Fd --lock-wait-timeout 300 -d tenant1 -n public -f /pgbackup/tenant1_public_20210816_233807

I know in the public schema of the Tenant database there are over 60 functions and are not backed up. Any pointers are greatly helpful.

Regards,

Sridhar Parepalli

Re: Backup Functions

From

Vijaykumar Jain

Date:

18 August 2021, 18:30:14

On Wed, 18 Aug 2021 at 20:35, Sridhar Parepalli <Sridhar.Parepalli@sas.com> wrote:

I know in the public schema of the Tenant database there are over 60 functions and are not backed up. Any pointers are greatly helpful.

dumping only objects in a specific schema, also dumped functions in that schema, at least in my demo run.

postgres@db:~/playground$ psql test

psql (14beta1)

Type "help" for help.

test=# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+-------------------------+--------------------------+-----------------------------------------+------

public | get_rounded_up_interval | timestamp with time zone | ts timestamp with time zone, i interval | func

(1 row)

test=# \q

postgres@db:~/playground$ pg_dump -n public test > /tmp/test.sql

postgres@db:~/playground$ grep -A5 get_rounded_up_interval /tmp/test.sql

-- Name: get_rounded_up_interval(timestamp with time zone, interval); Type: FUNCTION; Schema: public; Owner: postgres

CREATE FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) RETURNS timestamp with time zone

LANGUAGE plpgsql

AS $$

begin

return date_bin(i, ts, date_trunc('day', ts)) + i;

end; $$;

ALTER FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) OWNER TO postgres;

***********

I also tried using -Fd and then checked via. i do see functions dumped.

pg_dump -Fd -d test -n public -f /tmp/test.dump

pg_restore -l /tmp/test.dump | grep -i function

219; 1255 72283 FUNCTION public get_rounded_up_interval(timestamp with time zone, interval) postgres

do you see the functions expected to be dumped under \df public.* of the db ?

Thanks,

Vijay

Mumbai, India

RE: Backup Functions

From

Sridhar Parepalli

Date:

18 August 2021, 19:05:00

Hey Vijaykumar,

Thank you for the quick response.

Q) do you see the functions expected to be dumped under \df public.* of the db ?

Response) Yes, I do see all the functions listed.

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

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

-- Name: public; Type: SCHEMA; Schema: -; Owner: xxx

CREATE SCHEMA public;

ALTER SCHEMA public OWNER TO xxx;

-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: xxx

COMMENT ON SCHEMA public IS 'standard public schema';

-- Name: dmc_update_row_modified_function(); Type: FUNCTION; Schema: public; Owner: dbmsowner

CREATE FUNCTION public.dmc_update_row_modified_function() RETURNS trigger

LANGUAGE plpgsql

AS $$ BEGIN IF TG_OP = 'UPDATE' THEN NEW.modified_dttm = NOW(); RETURN NEW; ELSIF TG_OP = 'INSERT' THEN NEW.created_dttm = NOW(); RETURN NEW; END IF; END; $$;

ALTER FUNCTION public.dmc_update_row_modified_function() OWNER TO dbmsowner;

-- PostgreSQL database dump complete

From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Sent: Wednesday, August 18, 2021 11:30 AM
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Backup Functions

EXTERNAL

On Wed, 18 Aug 2021 at 20:35, Sridhar Parepalli <Sridhar.Parepalli@sas.com> wrote:

I know in the public schema of the Tenant database there are over 60 functions and are not backed up. Any pointers are greatly helpful.

dumping only objects in a specific schema, also dumped functions in that schema, at least in my demo run.

postgres@db:~/playground$ psql test

psql (14beta1)

Type "help" for help.

test=# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+-------------------------+--------------------------+-----------------------------------------+------

public | get_rounded_up_interval | timestamp with time zone | ts timestamp with time zone, i interval | func

(1 row)

test=# \q

postgres@db:~/playground$ pg_dump -n public test > /tmp/test.sql

postgres@db:~/playground$ grep -A5 get_rounded_up_interval /tmp/test.sql

-- Name: get_rounded_up_interval(timestamp with time zone, interval); Type: FUNCTION; Schema: public; Owner: postgres

CREATE FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) RETURNS timestamp with time zone

LANGUAGE plpgsql

AS $$

begin

return date_bin(i, ts, date_trunc('day', ts)) + i;

end; $$;

ALTER FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) OWNER TO postgres;

***********

I also tried using -Fd and then checked via. i do see functions dumped.

pg_dump -Fd -d test -n public -f /tmp/test.dump

pg_restore -l /tmp/test.dump | grep -i function

219; 1255 72283 FUNCTION public get_rounded_up_interval(timestamp with time zone, interval) postgres

do you see the functions expected to be dumped under \df public.* of the db ?

Thanks,

Vijay

Mumbai, India

Re: Backup Functions

From

Tom Lane

Date:

18 August 2021, 19:17:06

Sridhar Parepalli <Sridhar.Parepalli@sas.com> writes:
> I was just wondering should a schema of a database contain functions and triggering functions does the pg_dump
includefunctions as it takes back up of the schema? I'm on postgres 11 version. 

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.

            regards, tom lane

RE: Backup Functions

From

Sridhar Parepalli

Date:

18 August 2021, 20:14:30

Hey Tom,

Here's what I got from the \df ...

tenant1=# \df
                                                                               List of functions
 Schema |               Name               | Result data type |                                           Argument data
types                                          | Type 

--------+----------------------------------+------------------+---------------------------------------------------------------------------------------------------------+------
 public | armor                            | text             | bytea
                                               | func 
 public | armor                            | text             | bytea, text[], text[]
                                               | func 
 public | crypt                            | text             | text, text
                                               | func 
 public | dearmor                          | bytea            | text
                                               | func 
 public | decrypt                          | bytea            | bytea, bytea, text
                                               | func 
 public | decrypt_iv                       | bytea            | bytea, bytea, bytea, text
                                               | func 
 public | digest                           | bytea            | bytea, text
                                               | func 
 public | digest                           | bytea            | text, text
                                               | func 
 public | dmc_update_row_modified_function | trigger          |
                                               | func 
 public | encrypt                          | bytea            | bytea, bytea, text
                                               | func 
 public | encrypt_iv                       | bytea            | bytea, bytea, bytea, text
                                               | func 
 public | gen_random_bytes                 | bytea            | integer
                                               | func 
 public | gen_random_uuid                  | uuid             |
                                               | func 
 public | gen_salt                         | text             | text
                                               | func 
 public | gen_salt                         | text             | text, integer
                                               | func 
 public | hmac                             | bytea            | bytea, bytea, text
                                               | func 
 public | hmac                             | bytea            | text, text, text
                                               | func 
 public | pgp_armor_headers                | SETOF record     | text, OUT key text, OUT value text
                                               | func 
 public | pgp_key_id                       | text             | bytea
                                               | func 
 public | pgp_pub_decrypt                  | text             | bytea, bytea
                                               | func 
 public | pgp_pub_decrypt                  | text             | bytea, bytea, text
                                               | func 
 public | pgp_pub_decrypt                  | text             | bytea, bytea, text, text
                                               | func 
 public | pgp_pub_decrypt_bytea            | bytea            | bytea, bytea
                                               | func 
 public | pgp_pub_decrypt_bytea            | bytea            | bytea, bytea, text
                                               | func 
 public | pgp_pub_decrypt_bytea            | bytea            | bytea, bytea, text, text
                                               | func 
 public | pgp_pub_encrypt                  | bytea            | text, bytea
                                               | func 
 public | pgp_pub_encrypt                  | bytea            | text, bytea, text
                                               | func 
 public | pgp_pub_encrypt_bytea            | bytea            | bytea, bytea
                                               | func 
 public | pgp_pub_encrypt_bytea            | bytea            | bytea, bytea, text
                                               | func 
 public | pgp_sym_decrypt                  | text             | bytea, text
                                               | func 
 public | pgp_sym_decrypt                  | text             | bytea, text, text
                                               | func 
 public | pgp_sym_decrypt_bytea            | bytea            | bytea, text
                                               | func 
 public | pgp_sym_decrypt_bytea            | bytea            | bytea, text, text
                                               | func 
 public | pgp_sym_encrypt                  | bytea            | text, text
                                               | func 
 public | pgp_sym_encrypt                  | bytea            | text, text, text
                                               | func 
 public | pgp_sym_encrypt_bytea            | bytea            | bytea, text
                                               | func 
 public | pgp_sym_encrypt_bytea            | bytea            | bytea, text, text
                                               | func 
 public | pgpool_pgctl                     | boolean          | action text, stop_mode text
                                               | func 
 public | pgpool_recovery                  | boolean          | script_name text, remote_host text,
remote_data_directorytext                                          | func 
 public | pgpool_recovery                  | boolean          | script_name text, remote_host text,
remote_data_directorytext, remote_port text                        | func 
 public | pgpool_recovery                  | boolean          | script_name text, remote_host text,
remote_data_directorytext, remote_port text, recovery_node integer | func 
 public | pgpool_remote_start              | boolean          | remote_host text, remote_data_directory text
                                               | func 
 public | pgpool_switch_xlog               | text             | arcive_dir text
                                               | func 
(43 rows)


Sridhar Parepalli

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, August 18, 2021 12:17 PM
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Backup Functions

EXTERNAL

Sridhar Parepalli <Sridhar.Parepalli@sas.com> writes:
> I was just wondering should a schema of a database contain functions and triggering functions does the pg_dump
includefunctions as it takes back up of the schema? I'm on postgres 11 version. 

Functions belonging to extensions are not included in pg_dump output, maybe that explains your results?  It's hard to
besure without a lot more specifics than you've provided. 

                        regards, tom lane

Re: Backup Functions

From

"David G. Johnston"

Date:

18 August 2021, 20:43:54

On Wed, Aug 18, 2021 at 10:14 AM Sridhar Parepalli <Sridhar.Parepalli@sas.com> wrote:

Hey Tom,

Here's what I got from the \df ...

It would seem you have your answer...

David J.

Re: Backup Functions

From

"David G. Johnston"

Date:

19 August 2021, 08:12:30

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.