Taking Backup of postgres Objects (DDL) Data definition with individual files

I.TAKING TABLE STRUCTRE BACKUP WITH THEIR’S OBJECT FILE NAME :
In this script helpful to take backup (DDL’s) of procedure, function & table definition with seperate file system (object name) based on postgres schema , this script is mostly used for for source control purpose

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

In this shell script and windows script will be helpful to take the backup of all the table with individual file .
In this following script is windows based script will be helpful to take the dump of tables with seperate file system based on schema.

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 : Here wards is the schema name first it will take the backup of tables name the it will dump the data definition of the the table with the table name file, you can use this code for source control

2. Linux 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

(I think you forgot to add $DB in the pg_dumb command, and I added a -w, for an automated script, it is better not to have a psw prompt I guess, for that, I created a ~/.pgpass file with my password in it I also gave the user for the command to know which password to fetch in .pgpass) Hope this helps someone someday.

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

By following anyone script you can take backup of procedure & fucntion with seperate file name
first you have to issue this script then you will get some generated script , copy & paste the generated script to your postgresql SQL prompt now you can see the copy of your procedure and function one by one to your destination location

—-TO GET 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’;

—-TO GET 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 you are taking the function and procedure backup based on schema.

III. Windows Script for taking Sequence definition with their object dump file name Seperately :

In this shell script and windows script will be helpful to take the backup of all the sequence with individual file name means their’s sequence name .
following script is windows based script, will be helpful to take the dump of sequence with their sequence names file seperately based on schema.

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 first it will take the backup of the sequence definition individually with their sequence’s name, you can use this files as for source control

Quote With Us!  for IT Related Support