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.

Discomposure

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.

Vaccinia

Running the single database dump command and I get "database /path/to/my/mydb_dump.sql does not exist" – Acescent

@multigoodverse: You seem to have omitted the database name. mydb in the example. – Vaccinia

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

Moderato

Answer 3

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

Hayleyhayloft

Thanks. I don't like this tool for its ascetic interface. BTW pg_dump has no dump options like 'delete ... if ... exists', it has only '-c' option – Discomposure

That's a matter of taste. And why would pg_dump have to delete something? And yes, pg_dump is the only way to make a proper dump from the database that I know of, all GUI tools use this piece of software in the background. – Hayleyhayloft

Also, what's wrong with the --clean option? If the object to be deleted does not exist while restoring, nothing breaks. – Vaccinia

If the object to be deleted does not exist, the script stop running with an error 'ERROR: relation "public.t_payment" does not exist' on line 'ALTER TABLE ONLY public.t_payment DROP CONSTRAINT fk_user_payer;' – Discomposure

There are many ways to dump a PostgreSQL database, depending on your needs. We primarily use PITR-style backups. postgresql.org/docs/9.1/interactive/backup.html – Listel

Answer 4

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

Thinnish

Answer 5

pg_dump -U uguryilmaz modaltrans_dev > backup.sql

Redhot

Answer 6

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

Whitworth