How to take the postgres DDL statement with different file system ?

[Nijamutheen J]

any idea to take the postgres data definition backup with different file system for example if schema ADT means i want DDL statement with different file system table…., function…., procedure wise..

[Nijamutheen J]

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 :

I you need to extract SQL files from multiple tables of a PostgreSQL schema.
In this shell scrip and windows script will be helpfule to take the backup of all the table with individual file .
In this following script is windows based script will be helful to dump the 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 their object dump file name

#!/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.

• This reply was modified 2 years, 5 months ago by Nijamutheen J.

[Author]

Posts