Re: Backup Functions

On Wed, 18 Aug 2021 at 20:35, Sridhar Parepalli <Sridhar(dot)Parepalli(at)sas(dot)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(at)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(at)db:~/playground$ pg_dump -n public test > /tmp/test.sql
postgres(at)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