How to dump and restore Postgresql databases

Only dump

pg_dump –host HOST_1  –port 5432 –username SOURCE_db_username –schema SCHEMA_DO_BE_BACKED_UP –password –format custom –no-owner -x –no-privileges –blobs –verbose –file SCHEMA_BACKUP_DUMP_FILE.backup SOURCE_DB_NAME

Only Restore

Target database must exist.

pg_restore –host HOST_2 –port 5432 –username TARGET_db_username –dbname TARGET_DB_NAME –password –no-owner -x –no-privileges –jobs 2 –verbose SCHEMA_BACKUP_DUMP_FILE.backup

Dump and restore with data streaming

env PGPASSWORD=source_db_user_pass –host=HOST_1 –port=5432 –schema SCHEMA_DO_BE_BACKED_UP –username=”source_db_username” –format=custom –no-owner -x –no-privileges source_db_name | env PGPASSWORD=target_db_user_pass pg_restore –host=HOST_2 –no-privileges –no-owner –no-tablespaces –verbose –username=target_db_username –dbname=target_db_name –clean


Running liquibase from command line

Create file next to the changeLogFile (which by default is named install.xml)

promptOnNonLocalDatabase false
url jdbc:postgresql://hostname/db_name
username my_user
password my_pass
driver org.postgresql.Driver
defaultSchemaName localizer

Execute commands:

$ cd my_project/src/main/resources

$ ~/liquibase-3.0.0-bin/liquibase –driver=org.postgresql.Driver –classpath=/path/to/postgresql-9.0-801.jdbc4.jar –changeLogFile=install.xml – –logLevel=debug –logFile=/tmp/localizer-liquibase.log update -DschemaName=my_schema

Liquibase Home is not set.
Liquibase Home: /my/userhomedir/liquibase-3.0.0-bin
Liquibase update Failed: Validation Failed:
8 change sets check sum


For more information, use the –logLevel flag