Category: PostgreSQL


PostgreSQL: Dump Schema Only or Triggers Only

July 17th, 2010 — 2:59pm

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

Comment » | Bash Shell, PostgreSQL

nightly backup (pg_dump) with shell script and cron job

July 10th, 2010 — 8:00am

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

Comment » | PostgreSQL

Back to top