databasepostgresqlrestoredumpdatabase-dump

Reputation: 7553

PostgreSQL: dump and restore

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.

Upvotes: 16

Answers (6)

Super Kai - Kazuya Ito

Reputation: 1

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

Upvotes: 0

Shubham

Reputation: 2210

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

Upvotes: 3

Erwin Brandstetter

Reputation: 658672

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

PostgreSQL: dump and restore

Answers (6)

Related Questions