Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Batch backup and export your Postgres or PostgreSQL table schemas and stored functions or procedures into individual *.sql files --the easy way!
#!/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"
@vihaandeepa

This comment has been minimized.

Copy link

vihaandeepa commented Feb 28, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.