Taking PostgreSQL Procedure & Function backup with Seperate File

In this shell script will help you to take the backup of postgresql function & procedure with different file as schema based

# Config:
DB="tutorialdb"
U=enterprisedb
export PGPASSWORD="tcs@345"
export PGPORT="5444"
export PGHOST="20.0.4.101"
nijam="/home/Admin/"
logfile="nijam48".sql

/opt/edb/as9.6/bin/psql -d $DB -U $U -t -c "SELECT FORMAT(
'\COPY (SELECT regexp_split_to_table(regexp_replace(pg_get_functiondef(%s), ''\t'', ''   '', ''g''), ''\n'')) TO  /home/Admin/2ndquadrant.in/HealthCraft_DC/schemas/security/functions_procedures/security.%s',
pp.oid,
pp.proname
)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
WHERE PN.NSPNAME = 'security'" >>$nijam/$logfile 2>&1
/opt/edb/as9.6/bin/psql -d $DB -U $U -t -c "\i /home/Admin/nijam48.sql"
# Successfully completed the procedure & function backup

Note : Here security is the schema name tutorialdb is the database name so you can change the database and schema name as per your postgres procedure & function backup strategy. nijam48.sql is the temporary sql file

FOR IT Related SUPPORT