infinite possibility

I needed to extract triggers only, sequence/index only etc when we migrated database.
I received advice from postgresql General List and thought I should post exact step/script of how to accomplish this.

Open Terminal:

1. dump schema only

pg_dump -h localhost -p 5432 –username UserName –schema-only –format c –file path_to_backup.backup –schema ‘public’ db_name

2. create list of item that you want to extract
2.1. TRIGGERS only

pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (TRIGGER)/ { print }’ > “path_to_triggers.list”

2.2. SEQUENCE, INDEX

pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (SEQUENCE|INDEX)/ { print }’ > “path_to_seq.list”

3. Create SQL script from the list
3.1. TRIGGERS only

pg_restore -L path_to_triggers.list -f dest_path_triggers.sql path_to_backup.backup

3.2. SEQUENCE, INDEX

pg_restore -L path_to_seq.list -f dest_path_seq.sql path_to_backup.backup

1. write a shell script

#!/bin/bash

#backup.sh

logfile=”/backups/logfile.log”

# Location to place backups.

backup_dir=”/backups”

touch $logfile

timeslot=`date +%y%m%d_%H%M`

databases=(“db1″ “db2″ “db3″)

# get length of an array

tLen=${#databases[@]}

for (( i=0; i<${tLen}; i++ )); do

echo “Vacuum Starting at `date` for database: ${databases[$i]} ” >> $logfile

timeinfo=`date ‘+%T %x’`

psql -c “vacuum verbose analyze” -d ${databases[$i]} -U postgres >> $logfile

echo “Vacuum completed. Backup Starting at `date` for database: ${databases[$i]} ” >> $logfile

pg_dump ${databases[$i]} -h 127.0.0.1  -U postgres | gzip > “$backup_dir/${databases[$i]}-$timeslot-backup.gz”

echo “Backup and Vacuum complete at `date` for database: ${databases[$i]} ” >> $logfile

done

2. Add an entry to crontab to execute the script above
2.1. open terminal and login as postgres: $ su – postgres
2.2. list cron job: $ crontab -l
2.3. edit cron job:
2.3.1. $ crontab -e #in terminal vi editor will open)
2.3.2. i for insert mode
2.3.3. enter entry. for instance, to run it every 1 am:

00 01 * * * path_to_backup.sh > /dev/null

2>&1
2.3.4. ESC key to exist edit mode
2.3.5. :wq to save and quit