16

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.

Improve this question

6 Answers 6

Reset to default
38

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Running the single database dump command and I get "database /path/to/my/mydb_dump.sql does not exist"
@multigoodverse: You seem to have omitted the database name. mydb in the example.
6

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

Improve this answer

Comments

3

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

Improve this answer

5 Comments

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
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.
Also, what's wrong with the --clean option? If the object to be deleted does not exist while restoring, nothing breaks.
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;'
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
3

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
Improve this answer

Comments

1

pg_dump -U uguryilmaz modaltrans_dev > backup.sql

Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.