Here are a couple commands for dealing with importing and exporting (or restoring and dumping) Postgres databases. This assumes Postgres is running on localhost.

These commands should seem similar to mysqldump / mysql mydb < mydump.sql import / exporting / overwriting the entire database.

You may also need to make sure certain users / roles exist with appropriate access in your Postgres installation.

 

Export / dump the database

pg_dump -h localhost -p 5432 -U example_user -F c -b -v -f example_db_dump example_db

Break down of command

Role / user: example_user

Output file name: example_db_dump

Database name: example_db

Options

-h

Host, localhost in this case

-p

Default Postgres port

-F c -b -v -f

(dump as custom format [-F c], include large objects (blobs) [-b], verbose [-v], file name example_db_dump [-f])

-F format
–format=format
Selects the format of the output. format can be one of the following:

p
plain
Output a plain-text SQL script file (the default).

c
custom
Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it
allows manual selection and reordering of archived items during restore. This format is also compressed by default.

d
directory
Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a
so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated
with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default and also supports
parallel dumps.

t
tar
Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a
valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be
changed during restore.

-b
–blobs
Include large objects in the dump. This is the default behavior except when –schema, –table, or –schema-only is specified. The -b switch is therefore only useful to
add large objects to dumps where a specific schema or table has been requested. Note that blobs are considered data and therefore will be included when –data-only is
used, but not when –schema-only is.

 

Import / restore the database

pg_restore -h localhost -p 5432 -U example_user --dbname example_db --clean example_db_dump

 

Break down of command

Role / user: example_user

Output file name: example_db_dump

Database name: example_db

Options

-h

Host, localhost in this case

-p

Port – default Postgres port appears to be 5432

-U

User name

–dbname

Database name

–clean

Clean (drop) database objects before recreating them.