dantheman213 · May 19, 2025 14:25 · Nov 14, 2016 · Nov 14, 2016 · Nov 14, 2016 · Nov 13, 2016
diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -8,7 +8,7 @@
 # Export your app's table schemas and stored functions from a PostgreSQL 
 # database into individual *.sql files for easy management and source control.
 
-# Import script located here:
+# Batch import *.sql files with this script located here:
 # https://gist.github.com/dantheman213/a281a0f7364218bfd1013565aac28830
 
 # INSTALLATION

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -8,6 +8,9 @@
 # Export your app's table schemas and stored functions from a PostgreSQL 
 # database into individual *.sql files for easy management and source control.
 
+# Import 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)

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -5,8 +5,8 @@
 # https://github.com/dantheman213
 
 # DESCRIPTION
-# Export all table schemas and functions from a PostgreSQL database into 
-# individual *.sql files for easy management and source control.
+# Export your app's table schemas and stored functions from a PostgreSQL 
+# database into individual *.sql files for easy management and source control.
 
 # INSTALLATION
 # 1. Install script at /usr/bin or /usr/sbin (if you 

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -1,13 +1,21 @@
 #!/bin/sh
 
-# Description
-# Export all table schemas and functions into individual *.sql files for easy management and source control.
+# AUTHOR
+# DANIEL E. GILLESPIE (2016)
+# https://github.com/dantheman213
 
-# Installation
-# 1. Install script at /usr/bin or /usr/sbin (if you want to make this root/admin privs only)
+# DESCRIPTION
+# Export all table schemas and functions from a PostgreSQL database into 
+# individual *.sql files for easy management and source control.
+
+# 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
+# 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"

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -9,7 +9,7 @@
 # 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 APP ###
+### 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"

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -1,13 +1,22 @@
 #!/bin/sh
 
+# Description
 # Export all table schemas and functions into individual *.sql files for easy management and source control.
 
+# 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 APP ###
 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

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -43,6 +43,7 @@ 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

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -3,7 +3,7 @@
 # Export all table schemas and functions into individual *.sql files for easy management and source control.
 
 TEMP_WORK_PATH="/tmp/postgres"
-BACKUP_ROOT_PATH="/vagrant/backup"
+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"

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -4,8 +4,8 @@
 
 TEMP_WORK_PATH="/tmp/postgres"
 BACKUP_ROOT_PATH="/vagrant/backup"
-DATABASE_NAME="pen_db"
-DATABASE_TABLES_PREFIX="pen_"
+DATABASE_NAME="myapp_db"
+DATABASE_TABLES_PREFIX="myapp_"
 POSTGRES_USER="postgres"
 POSTGRES_PASSWORD="postgres"
 

diff --git a/export_db_structure.sh b/export_db_structure.sh
@@ -0,0 +1,55 @@
+#!/bin/sh
+
+# Export all table schemas and functions into individual *.sql files for easy management and source control.
+
+TEMP_WORK_PATH="/tmp/postgres"
+BACKUP_ROOT_PATH="/vagrant/backup"
+DATABASE_NAME="pen_db"
+DATABASE_TABLES_PREFIX="pen_"
+POSTGRES_USER="postgres"
+POSTGRES_PASSWORD="postgres"
+
+
+[ -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
+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"