PostgreSQL: dump and restore

Backup your database no tool needed.we can do with terminal

All commands should be run as the postgres user.

 sudo su - postgres 

Backup a single database

pg_dump db_name > db_backup.sql

Restore a single database

psql db_name < db_backup.sql

Backup an entire postgres database cluster

pg_dumpall > cluster_backup.sql

Restore an entire postgres database cluster

psql -f cluster_backup.sql postgres

Refer this source for more commands backup commands

pgAdmin3 will do the trick, it has pg_dump and pg_restore included in the installer.

if you use md5 authentication technique and want to use a specific user to get db dump, you can do

$ pg_dump -U username -p 5432 dbname > filename-to-backup-to.sql

To avoid credential and username issues while restoring, you can use --no-owner flag

$ pg_dump --no-owner -U username -p 5432 dbname > filename-to-backup-to.sql

To restore the backup use below command

$ psql -U username -d dbname -f filename-to-backup-to.sql

pg_dump -U uguryilmaz modaltrans_dev > backup.sql

For example, you can export everything such as all databases, users(roles), etc to backup.sql with pg_dumpall as shown below. *backup.sql is created if it doesn't exist and you better use any superusers(e.g., postgres) to do it smoothly without permission errors and pg_dumpall can output SQL in only plain text format rather than custom format or tar format and my answer explains how to export schema and data with pg_dump and the doc explains how to export and import everything with pg_dumpall:

pg_dumpall -U postgres > backup.sql

Or:

pg_dumpall -U postgres -f backup.sql

Or, you can export everything except the data of all databases to backup.sql as shown below:

pg_dumpall -U postgres -s > backup.sql

Or:

pg_dumpall -U postgres --schema-only > backup.sql

Or, you can export everything except the schema of all databases to backup.sql with only INSERT statement which has column names as shown below:

pg_dumpall -U postgres -a --column-inserts > backup.sql

Or:

pg_dumpall -U postgres --data-only --column-inserts > backup.sql

Then, you will need to input multiple passwords after running the command above:

Password: 
Password: 
Password:
...

Then, you can import backup.sql(everything) into your PostgreSQL as shown below.

psql -U postgres -f backup.sql

Or, you can try this below which doesn't work on Windows:

psql -U postgres < backup.sql

Or, you can import backup.sql(everything) into your PostgreSQL with \i after login with the user(role) postgres as shown below:

psql -U postgres
postgres=# \i backup.sql

Or, you can import backup.sql into multiple databases one by one as shown below. *You have to create each database (and the schema to import only data) before hand otherwise there is error and my answer explains how to create a database and you must use psql to import SQL in plain text format rather than pg_restore which can import SQL in custom format or tar format and my answer explains how to import backup.sql into orange database:

psql -U postgres -f backup.sql orange
psql -U postgres -f backup.sql lemon
psql -U postgres -f backup.sql peach
...

In addition, you can export all databases to backup.sql without multiple password prompts by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana pg_dumpall -U postgres > backup.sql

And, you can export all databases excluding orange and *apple* databases to backup.sql as shown below. *Multiple --exclude-database are available in one command:

pg_dumpall -U postgres --exclude-database=orange --exclude-database=*apple* > backup.sql

Or:

pg_dumpall -U postgres --exclude-database orange --exclude-database *apple* > backup.sql