CODEWITHSUNDEEP
macospostgresqlosx-mountain-lionheroku-postgres
dB'
dB'

Reputation: 8330

Postgres.app: pg_restore hangs

I'm trying to troubleshoot the pg_restore command on my system. I've installed Postgresapp, and I've included its binaries on my PATH. Commands such as psql and pg_dump appear to work fine, and running which pg_restore give the expected result.

$ which pg_restore
/Applications/Postgres.app/Contents/MacOS/bin/pg_restore

The problem is that pg_restore doesn't seem to do anything. When I run it in the terminal, no output is printed, eiher to the console or to the logs. This is true no matter what arguments I pass in, including the --verbose switch. Running it does cause a pg_restore process to appear in my activity monitor, but this process doesn't use any CPU. Apart from that, nothing happens at all.

Has anyone else seen this issue? Do you have any suggestions for getting pg_restore to work?

Upvotes: 29

Views: 23977

Answers (4)

3wordchant
3wordchant

Reputation: 586

I got a hint from an answer to another question; the -f option doesn't do what you might think it does (or what I thought it does, anyway 😅), even if you're using a "custom" format dump (i.e. not one which you provide with shell redirection like | or >).

  • Incorrect ❌: pg_restore -f filename.dump – waits for a series of commands from STDIN, restoring to a database in filename.dump
  • Correct ✅: pg_restore -d database filename.dump – restores filename.dump to database.

I thought for some reason that a custom dump included the database name so you didn't need to provide it at all.

Upvotes: 38

Pencilcheck
Pencilcheck

Reputation: 2932

sometimes if you have long running queries you need to stop it so pg_restore will not get stuck.

Run this script:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and pid <> pg_backend_pid();

https://www.sqlprostudio.com/blog/8-killing-cancelling-a-long-running-postgres-query#:~:text=Terminate%20all%20queries,be%20used%20in%20special%20situations.

Upvotes: 0

Maxime Thoonsen
Maxime Thoonsen

Reputation: 21

I was also stuck for no reason. I changed from pg_restore -U seed -h localhost -p 5432 -f dump.backup -C --verbose to pg_restore -d seed -U seed -h localhost -p 5432 < dump.backup and it worked.

If this can help someone..

Upvotes: 1

dB&#39;
dB&#39;

Reputation: 8330

I think I figured it out.

The command I was running included an extra line break after the user name.

As in, I was trying to execute this

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myusername
-d mydb latest.dump

instead of this 

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myusername -d mydb latest.dump

For some reason that extra linebreak was gumming things up. Once I removed it pg_restore worked properly.

Upvotes: 23

Related Questions