Common postgresql tasks

postgresql

# environment variables: http://www.postgresql.org/docs/9.2/static/libpq-envars.html
# PGUSER      - default username
# PGDATABASE  - default database name
# PGDATA      - default database cluster directory


# initializing a cluster
initdb --encoding=UTF-8 --locale=pt_BR.UTF-8 [--pgdata=$PGHOME/data] [--username=superuser]


# CREATE ROLE superuser
CREATE ROLE name SUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN;




# CREATING DUMPS:
# ----
# NOTE: in Rails the option --exclude-table=schema_migrations can be helpful
# NOTE2: we're using the --no-owner and --no-privileges options, which means that the restoring --username user will be the new owner
#
#   extra options:
#     --no-owner         do not issue any SET OWNER command
#     --no-privileges    do not issue any GRANT/REVOKE 
#     --schema-only      ddl dump
#     --data-only        dml dump
#     --column-inserts   dml with inserts (compatibility? - you should use --format=plain too)
#     --table=TABLE      dumps only the specified table

DBUSER= DBNAME=; pg_dump --format=custom --encoding=UTF-8 --no-owner --no-privileges --username=$DBUSER --file=$DBNAME.dump.`hostname -s`.`date +%Y-%m-%d-%Hh%Mm%S`.sql $DBNAME


# RESTORING DUMPS
#  - NOTE: we're using the --no-owner option, which means that the --username user will be the new owner
#  - when creating (--create), the database name will be the one included in the dump file
#  - when restoring, the database name will be the one defined by --dbname
#  - with --data-only, the option --clean is not working! (9.2.4 OS X homebrew). So, delete the table data before restoring. 
#      (same problem reported http://www.postgresql.org/message-id/1348242845.21480.0@mofo)
#      psql --username=$DBUSER --dbname=DB$NAME --command="DELETE FROM $TABLE;"
# ----

# creating a new database from a dump (database name is already set on dump file. --dbname here is only for initial connection)
pg_restore -v --exit-on-error --create --dbname=template1 --username=$DBUSER $DUMP_FILE

# restoring an existent database with a dump (the encoding was set on dump creation)
# or "reseting a database with a dump"
pg_restore -v --exit-on-error --single-transaction --clean --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# NOTE, it may fail if the SCHEMA in the dump is different from the DATABASE in which we're trying to restore it. If so, drop the database, create a new one and try the restore without the --clean option
dropdb --username=$DBUSER $DBNAME
createdb --template=templateX --username=$DBUSER $DBNAME
pg_restore -v --exit-on-error --single-transaction --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# restoring a table schema from a dump
pg_restore -v --exit-on-error --single-transaction --clean --schema-only --no-owner --table=$TABLE --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# restoring a table from a dump (schema + data)
pg_restore -v --exit-on-error --single-transaction --clean --table=$TABLE --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# restoring a table schema from a dump
pg_restore -v --exit-on-error --single-transaction --clean --schema-only --table=$TABLE --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# creating a table schema from a dump
pg_restore -v --exit-on-error --single-transaction --schema-only --table=$TABLE --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE

# restoring a table data from a dump 
#   NOTE: if the table is populated consider deleting its data first: 
#      psql --username=$DBUSER --dbname=DB$NAME --command="DELETE FROM $TABLE;"
pg_restore -v --exit-on-error --single-transaction --data-only --table=$TABLE --no-owner --dbname=$DBNAME --username=$DBUSER $DUMP_FILE