Batch backup and export your Postgres or PostgreSQL table schemas and stored functions or procedures into individual *.sql files --the easy way!

export_db_structure.sh

#!/bin/sh

# AUTHOR
# DANIEL E. GILLESPIE (2016)
# https://github.com/dantheman213

# DESCRIPTION
# Export your app's table schemas and stored functions from a PostgreSQL 
# database into individual *.sql files for easy management and source control.

# Batch import *.sql files with this script located here:
# https://gist.github.com/dantheman213/a281a0f7364218bfd1013565aac28830

# INSTALLATION
# 1. Install script at /usr/bin or /usr/sbin (if you 
# want to make this root/admin privs only)
# 2. chmod +x /usr/bin/export_db_structure.sh
# 3. Make sure your Postgres database will accept a local 
# connection with password authentication
# 4. Execute the script.. check BACKUP_ROOT_PATH directory 
# for backup of your table schemas and stored functions

### CHANGE THESE TO YOUR SERVER/APP INFO ###
TEMP_WORK_PATH="/tmp/postgres"
BACKUP_ROOT_PATH="/vagrant/backup" # This is where your *.sql files will be exported at
DATABASE_NAME="myapp_db"
DATABASE_TABLES_PREFIX="myapp_"
POSTGRES_USER="postgres"
POSTGRES_PASSWORD="postgres"
### END CONFIGURATION ###


[ -d $TEMP_WORK_PATH ] || mkdir -p $TEMP_WORK_PATH
rm -rf $TEMP_WORK_PATH/*

[ -d $BACKUP_ROOT_PATH ] || mkdir -p $BACKUP_ROOT_PATH
rm -rf $BACKUP_ROOT_PATH/*

mkdir $BACKUP_ROOT_PATH/tables
mkdir $BACKUP_ROOT_PATH/routines

export PGPASSWORD=$POSTGRES_PASSWORD

cd $TEMP_WORK_PATH

# Get all table schemas and write to individual files
echo "Export table schemas..."
for table in $(psql -U $POSTGRES_USER -d $DATABASE_NAME -t -c "Select table_name From information_schema.tables Where table_type='BASE TABLE' and table_name like '$DATABASE_TABLES_PREFIX%'");
do pg_dump -st $table -U $POSTGRES_USER $DATABASE_NAME > $BACKUP_ROOT_PATH/tables/$table.sql;
done;

# Get all functions in db and output to one file
echo "Getting stored functions..."
psql -U $POSTGRES_USER -At $DATABASE_NAME > $TEMP_WORK_PATH/db_functions.sql <<EOF
SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';
EOF

# Split function file into separate files per function
echo "Exporting stored functions..."
csplit -f function -b '%d.sql' db_functions.sql '/FUNCTION/' '{*}'

# Rename the function files to match the function name in the file
# then place the files into the target backup directory
counter=1
while [ -f $TEMP_WORK_PATH/function$counter.sql ]
do
  name=$(head -1 function$counter.sql | awk {'print $5'})
  name=$(echo $name | cut -d "." --f 2 | cut -d "(" --f 1)
  mv function$counter.sql $BACKUP_ROOT_PATH/routines/$name.sql
  counter=$((counter+1))
done

echo "done"

when i executed this i am getting cannot create directory db_functions.sql . here i need to create file manually or it will create pls clarity
Thanks inadvance

Hi Brother, I am expecting same for my widows. I dont have knowledge to covert above source to Windows compatible. Can u please share the code for windows, which helps me a lot. Thanks in advance.

Hi, fine piece of work. Under which licsense may I use your script ?
Thank You