Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
executable file 110 lines (103 sloc) 2.7 KB
#!/usr/bin/env zsh
# Usage: a postgres [subcommand] [-d DATABASE_NAME] [-f FILENAME]
# Summary: Common operations with PostgreSQL databases
# Help: Connect to, manage or import/export a PostgreSQL database.
#
# Available operations:
#
# psql: open a prompt to the current database
# start: start the database server
# stop: stop the database server
# dump: export database into a dump file
# restore: import into a database from a dump file.
# kill: kill all connections to a database
# heroku: download a Heroku postgrest dump into current database
#
# You should provide the database name for these operations
# using the -d option. The dump and restore subcommands
# take another argument -f for the file to use.
#
# When in a Rails project, you can omit the -d option; the
# script will try to look up the database name from your
# config/database.yml file, using the 'development'
# environment.
#
# Example usage:
#
# a postgres psql -d mydb
set -e
# Provide a completions
if [ "$1" = "--complete" ]; then
echo "psql"
echo "dump"
echo "restore"
echo "kill"
echo "start"
echo "stop"
echo "heroku"
exit
fi
subcommand=$1
if [ $# -gt 0 ]; then
shift 1
fi
# Parse options
while getopts t:f:d:c: OPT; do
case "$OPT" in
c)
PSQL_ARGS=$@
;;
t)
TABLE_NAME=$OPTARG
;;
d)
DATABASE_NAME=$OPTARG
;;
f)
FILENAME=$OPTARG
;;
\?)
echo "Unknown options. Only -f, -t and -d are allowed."
exit 1
;;
esac
done
if [ -z "$DATABASE_NAME" ] && [ -f "config/database.yml" ]; then
DATABASE_NAME=$(ruby -ryaml -e "puts YAML.load_file('config/database.yml')['development']['database']")
else
echo "Could not auto-detect database name from config.yml."
echo "Specify the database name with the -d option."
exit 1
fi
case "$subcommand" in
"" | "psql")
psql -d "$DATABASE_NAME" $PSQL_ARGS
;;
"dump")
pg_dump -Fc --no-acl --no-owner "$DATABASE_NAME"
;;
"restore")
pg_restore --clean --no-acl --no-owner -d "$DATABASE_NAME" "$FILENAME"
;;
"schema")
psql -d "$DATABASE_NAME" -c "\\d $TABLE_NAME"
;;
"kill")
ps aux | grep "$DATABASE_NAME" | grep -v grep | awk "{print \$2}" | xargs kill
;;
"start")
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
;;
"stop")
pg_ctl -D /usr/local/var/postgres stop -s -m fast
;;
"heroku")
/usr/bin/heroku pgbackups:capture --expire --app "$1"
wget -O "$1".dump $(/usr/bin/heroku pgbackups:url --app "$1")
pg_restore --clean --no-acl --no-owner -d "$DATABASE_NAME" "$1".dump
;;
*)
echo "Unrecognized subcommand $@."
echo "Use one of psql, dump, restore, kill, start or stop."
exit 1
esac