agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedBackup Functions
7+ messages / 4 participants
[nested] [flat]
* Backup Functions
@ 2021-08-18 15:05 Sridhar Parepalli <Sridhar.Parepalli@sas.com>
0 siblings, 2 replies; 7+ messages in thread
From: Sridhar Parepalli @ 2021-08-18 15:05 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Backup Functions
@ 2021-08-18 15:30 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
1 sibling, 1 reply; 7+ messages in thread
From: Vijaykumar Jain @ 2021-08-18 15:30 UTC (permalink / raw)
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Backup Functions
@ 2021-08-18 16:05 Sridhar Parepalli <Sridhar.Parepalli@sas.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Sridhar Parepalli @ 2021-08-18 16:05 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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<mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Backup Functions
@ 2021-08-18 16:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-08-18 16:17 UTC (permalink / raw)
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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 include functions 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Backup Functions
@ 2021-08-18 17:14 Sridhar Parepalli <Sridhar.Parepalli@sas.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Sridhar Parepalli @ 2021-08-18 17:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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_directory text | func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, remote_port text | func
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, 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 include functions 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Backup Functions
@ 2021-08-18 17:43 David G. Johnston <david.g.johnston@gmail.com>
parent: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2021-08-18 17:43 UTC (permalink / raw)
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Backup Functions
@ 2021-08-19 05:12 David G. Johnston <david.g.johnston@gmail.com>
parent: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2021-08-19 05:12 UTC (permalink / raw)
To: Sridhar Parepalli <Sridhar.Parepalli@sas.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-08-19 05:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-18 15:05 Backup Functions Sridhar Parepalli <Sridhar.Parepalli@sas.com>
2021-08-18 15:30 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-08-18 16:05 ` Sridhar Parepalli <Sridhar.Parepalli@sas.com>
2021-08-18 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-18 17:14 ` Sridhar Parepalli <Sridhar.Parepalli@sas.com>
2021-08-18 17:43 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-19 05:12 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox