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

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