How to Restore Postgresql Dump File into Postgres Databases

Restore a postgres backup file using the command line?

There are two tools to look at, depending on how you created the dump file.

Your first source of reference should be the man page pg_dump as that is what creates the dump itself. It says:

Dumps can be output in script or
archive file formats. Script dumps are
plain-text files containing the SQL
commands required to reconstruct
the database to the state it was
in at the time it was saved. To
restore from such a script, feed it to
psql(1). Script files can be used
to reconstruct the database even
on other machines and other
architectures; with some modifications
even on other SQL database products.

The alternative archive file formats
must be used with pg_restore(1) to
rebuild the database. They allow
pg_restore to be selective about what
is restored, or even to reorder the
items prior to being restored. The
archive file formats are designed to
be portable across architectures.

So depends on the way it was dumped out. If using Linux/Unix, you can probably figure it out using the excellent file(1) command - if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore.

Restoring is pretty easy:

psql -U username -d dbname < filename.sql

-- For Postgres versions 9.0 or earlier
psql -U username -d dbname -1 -f filename.sql

or

pg_restore -U username -d dbname -1 filename.dump

Check out their respective manpages - there's quite a few options that affect how the restore works. You may have to clean out your "live" databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.

How to restore a PostgreSQL database from dump file in dbeaver?

When you right click on the database you want to restore into, under "Tools" you will find "execute script". This is how you restore a plain-format dump file, which is what db.sql probably is.

This will require you to have psql, but dbeaver will offer to download and install its own copy of it for you.

Restore a psql dump to an other (new) database name -d/--dbname and -f/--file cannot be used together

With pg_restore, the dump file is not the argument to --file. It is not an option argument at all.

pg_restore --clean \
           --create \
           --no-privileges \
           --no-owner \
           --format=c \
           -U "username" \
           -d "newdatabasename" \
           "dump_file.sql"

Import SQL dump into PostgreSQL database

psql databasename < data_base_dump

That's the command you are looking for.

Beware: databasename must be created before importing.
Have a look at the PostgreSQL Docs Chapter 23. Backup and Restore.

How to restore a postgres database from a custom-format dump?

Online help says:

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

For pg_restore -f is parameter for the output file: it is not the input file. Remove -f in your example:

pg_restore -h localhost -p 5432 -U postgres -v -Fc foo.backup

How can I restore a postgresql db dump from a local db to a server via psql?

You can use pg_dump.

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

If you want to use your dump you could of course also transfer your dump file over to the new server securly using e.g. SFTP and then restore it there as you would on your local machine.

Problems encountered in recovering three tables from a dump file

Solved, as I've demonstrated above.

---

Related Topics