Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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="myapp_db"
- DATABASE_TABLES_PREFIX="myapp_"
- 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"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement