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

Question

I'm having an issue getting a database restore using pg_restore to work. I've got Postgres 12.2 installed on my Mac running High Sierra (10.13.6). It was installed with Brew.

I created a simple database "foo" with table "foo" to test this out:

Nates-MacBook-Pro:k8s natereed$ psql -d foo
psql (12.2, server 12.1)
Type "help" for help.

foo=# SELECT table_schema,table_name
FROM information_schema.tables 
WHERE table_schema='public' ORDER BY table_schema,table_name;
table_schema | table_name 
--------------+------------
public       | foo
(1 row)

Generate dump:

pg_dump -Fc foo -f foo.backup

When I try to restore, nothing happens. pg_restore just hangs:

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

I've tried various permutations of this command, but every time it just hangs. In Activity Monitor, I see the process but it is not using any CPU.

-f specifies the output file, not the input file. Remove -f from the command line - the last parameter (without a "switch") specifies the input file (as documented in the manual): pg_restore -h localhost -p 5432 -U postgres -v -Fc foo.backup — user330315
– user330315, Commented Apr 3, 2020 at 16:44

gagarine · Accepted Answer · 2022-02-27 12:35:14Z

6

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

edited Feb 27, 2022 at 12:35

gagarine

4,3642 gold badges32 silver badges40 bronze badges

answered Apr 3, 2020 at 16:39

pifor

7,9722 gold badges11 silver badges18 bronze badges

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

4 Comments

Nate Reed Over a year ago

Nates-MacBook-Pro:k8s natereed$ pg_restore -h localhost -p 5432 -U natereed -v -Fc pg_restore: error: one of -d/--dbname and -f/--file must be specified

Nate Reed Over a year ago

Let's say I want the database to be created from the backup. One of either the file to restore from or the database must be specified. What's the right way to specify the backup file to restore from? Does output filename (-f) not refer to the file we're restoring from?

user330315 Over a year ago

@NateReed: From the manual: "-f Specify output file for generated script, or for the listing when used with -l"

pifor Over a year ago

Try: pg_restore -h localhost -p 5432 -U postgres -v -C -d <dbname> -Fc foo.backup . (-C means to create database).

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related