From: Sridhar Parepalli <Sridhar.Parepalli@sas.com>
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: RE: Backup Functions
Date: Wed, 18 Aug 2021 16:05:00 +0000
Message-ID: <DM6PR05MB6265007EC498CC321EF04715F3FF9@DM6PR05MB6265.namprd05.prod.outlook.com> (raw)
In-Reply-To: <CAM+6J97MFDUZQ8m8RTRsbN+BoPFMzNHBs4kebPQkmPX0nBbGeQ@mail.gmail.com>
References: <DM6PR05MB62655EA131770BD3BABA99E6F3FF9@DM6PR05MB6265.namprd05.prod.outlook.com>
<CAM+6J97MFDUZQ8m8RTRsbN+BoPFMzNHBs4kebPQkmPX0nBbGeQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Sridhar.Parepalli@sas.com, vijaykumarjain.github@gmail.com, pgsql-sql@lists.postgresql.org
Subject: RE: Backup Functions
In-Reply-To: <DM6PR05MB6265007EC498CC321EF04715F3FF9@DM6PR05MB6265.namprd05.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox