windows/Linux – Script to taking postgres DDL objects with individual file name

I. TAKING TABLE STRUCTRE BACKUP WITH THEIR’S OBJECT FILE NAME :

Below scripts helpful to take backup (DDL’s) of procedure, function & table definition with individual file system (object name) based on postgres schema .

1.  Windows Script for taking table definition with their object dump file name :

Below windows script will be helpful to take the backup of all the table with individual file .

D:
cd D:\PostgresPlus\9.4AS\bin
@echo off
SET TableListeFile=D:\InCampusDB\HealthCraft_LC\schemas\wards\Tables\database_list.txt

REM Saveing all tables name of database test_db on a temp file: database_list.txt  
 psql -U enterprisedb -d HealthCraft_LC -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='wards'" -o "%TableListeFile%"

REM Loop on liste tables name:
FOR /F "tokens=*" %I IN (%TableListeFile%) DO (
REM Dump each table on file
pg_dump -U enterprisedb -st wards.%I HealthCraft_LC  > "D:\InCampusDB\HealthCraft_LC\schemas\wards\Tables\%I"
)
REM Delete temp file
del /Q %TableListeFile%

Note : wards is the schema name. you can give whatever schema name you have.

2. Linux Shell Script for taking table definition with different dump file :

#!/bin/bash

# Config:
DB=rezopilotdatabase
U=postgres
# tablename searchpattern, if you want all tables enter "":
P=""
# directory to dump files without trailing slash:
DIR=~/psql_db_dump_dir

mkdir -p $DIR
TABLES="$(psql -d $DB -U $U -t -c "SELECT table_name FROM 
information_schema.tables WHERE table_type='BASE TABLE' AND table_name 
LIKE '%$P%' ORDER BY table_name")"
for table in $TABLES; do
  echo backup $table ...
  pg_dump $DB -U $U -w -t $table > $DIR/$table.sql;
done;
echo done

II. TAKING PROCEDURE AND FUNCTION’s  BACKUP WITH THEIR’S FILE NAME :

Below SQL script will take the backup of postgres function & stored procedure automatically with different file system.

Before running the SQL script , you have to check the (folder) destination permission.

TAKING FUNCTION & PROCEDURE WITH  \n  (New Line):-

SELECT FORMAT(
‘\COPY (SELECT pg_get_functiondef(%s)) TO D:\InCampusDB\HealthCraft_LC\adt\sequence\%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 pl.lanname NOT IN (‘c’,’internal’) 
and pn.nspname = ‘billing’;

TAKING  FUNCTION & PROCEDURE WITHOUT  \n :-

SELECT FORMAT(
‘\COPY (SELECT regexp_split_to_table(regexp_replace(pg_get_functiondef(%s), ”\t”, ” ”, ”g”), ”\n”)) TO D:\InCampusDB\HealthCraft_LC\schemas\adt\Procedure_function\%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 = ‘ADT’;

Note: ADT is the schema name so you are taking the function and procedure backup based on schema.

III. Windows Script for taking Sequence definition with their sequence name.

Below windows script will be helpful to take the backup of schma’s the sequence with individual file name .

D:
cd D:\PostgresPlus\9.4AS\bin
@echo off
SET TableListeFile=D:\InCampusDB\HealthCraft_LC\schemas\adt\sequences\database_list.txt

REM Saveing all tables name of database test_db on a temp file: database_list.txt  
 psql -U enterprisedb -d HealthCraft_LC -t -c "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'adt';" -o "%TableListeFile%"

REM Loop on liste tables name:
FOR /F "tokens=*" %I IN (%TableListeFile%) DO (
REM Dump each sequence on file
pg_dump -U enterprisedb -st adt.%I HealthCraft_LC  > "D:\InCampusDB\HealthCraft_LC\schemas\adt\sequences\%I"
)
REM Delete temp file
del /Q %TableListeFile%

REM  Sequence Backup are successfully completed.

Note : Here ADT is the schema name .