Restore a postgres backup file using the command line?

by

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.

More Related Contents:

  1. How do I specify a password to ‘psql’ non-interactively?
  2. Run a PostgreSQL .sql file using command line arguments
  3. input file appears to be a text format dump. Please use psql
  4. What is PostgreSQL equivalent to autoincrement?
  5. INSERT with dynamic table name in trigger function
  6. psql: FATAL: Ident authentication failed for user “postgres”
  7. Use multiple conflict_target in ON CONFLICT clause
  8. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  9. How to increase the max connections in postgres?
  10. Where does PostgreSQL store configuration/conf files?
  11. How to thoroughly purge and reinstall postgresql on ubuntu? [closed]
  12. Creating a copy of a database in PostgreSQL
  13. PostgreSQL function returning multiple result sets
  14. How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
  15. Can’t connect the postgreSQL with psycopg2
  16. How do you change the character encoding of a postgres database?
  17. Difference between GiST and GIN index
  18. Test for null in function with varying parameters
  19. PostgreSQL: Why psql can’t connect to server?
  20. Can I use return value of INSERT…RETURNING in another INSERT?
  21. Changing a postgres containers server port in Docker Compose
  22. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  23. no pg_hba.conf entry for host
  24. PostgreSQL – disabling constraints
  25. PostgreSQL authentication method 10 not supported
  26. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment