Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script to taking Backup of postgres object's DDL with individual files

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 : Here wards is the schema name.
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 STRUCTRE 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 .

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

7 Steps to configure BDR replication in postgresql