Drop, create and import database (PostgreSQL)

postgres-import-db.sh

#export PATH=$PATH:/Library/PostgreSQL/8.4/bin/

dropdb -U postgres mydb

createdb -E UTF8 -U postgres mydb

psql -U postgres -d mydb < mydb-snapshot.sql
# or pg_restore -U postgres --dbname mydb --verbose mydb-snapshot.backup
# or pg_restore -U postgres --dbname mydb --verbose --no-owner mydb-snapshot.backup

postgres-pgdump-sample.sh

pg_dump -U postgres -h localhost -Fc --blobs -f mydb_20130401.dump mydb
# or pg_dump -U postgres -h localhost -Fc --blobs --no-owner --no-acl -f mydb_20130401.dump mydb

# if you want to export as sql format
pg_dump -U syspdvweb -h localhost -Fp --blobs -f mydb_20130401.sql mydb

postgres-pgrestore-sample.sh

# para carregar o dump na sua base local de desenvolvimento
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

# outro exemplos
psql -U postgres -d mydb < mydb-snapshot.sql

pg_restore -U postgres --dbname mydb --verbose mydb-snapshot.backup

pg_restore -U postgres --dbname mydb --verbose --no-owner mydb-snapshot.backup

psql-create-db.sh

$ psql

CREATE DATABASE mydb WITH OWNER mydb_en ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE='en_US.UTF-8' TEMPLATE template0;
CREATE DATABASE mydb WITH OWNER mydb_pt_BR_with_ABNT_sorting ENCODING 'UTF8' LC_CTYPE 'pt_BR.UTF-8' LC_COLLATE='pt_BR.UTF-8' TEMPLATE template0;
CREATE DATABASE mydb WITH OWNER mydb_pt_BR_with_C_sorting ENCODING 'UTF8' LC_CTYPE 'pt_BR.UTF-8' LC_COLLATE='C' TEMPLATE template0;

https://gist.github.com/turboladen/6790847