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" |
This comment has been minimized.
This comment has been minimized.
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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
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