PostgreSQL: dump and restore

Question

I use EMS SQL Manager for PostgreSQL and I need to dump difficult database(domains, 300+ stored procedures/functions, triggers, data, etc). This tool cannot do it.

Please advice me good GUI tool for postgres.

Answer 1

You can always just use the command line utility.
Dump the cluster:

pg_dumpall -p 5432 > /path/to/my/dump_file.sql

Dump a single database:

pg_dump -p 5432 mydb > /path/to/my/mydb_dump.sql

Dump the schema only:

pg_dump -p 5432 mydb -s > /path/to/my/mydb_dump_schema.sql

More in the manual.

If you want to restore to an empty database, you might want to run before restoring:

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;

The --clean option for pg_dump is not needed in this case.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

pg_dump -U uguryilmaz modaltrans_dev > backup.sql

Answer 6

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