In pg_restore, how can you use a postgres connection string to specify the host/database/username/password?

Question

When using pg_dump, you can use a postgres connection string to specify the host/database/username/password:

pg_dump postgres://someuser:[email protected]:5432/somedatabase 

I want to use the same sort of connection string for pg_restore:

pg_restore -f dump.dump postgres://userb:[email protected]:5432/otherdatabase 

But I get an error:

pg_restore: [archiver] could not open input file "postgres://userb:[email protected]:5432/otherdatabase": No such file or directory 

Answer 1

In PostgreSQL tools wherever you can specify a database name you can instead specify a connection string.

In the syntax for pg_restore the dbname is passed with a flag, not as a positional parameter:

$ pg_restore --help 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   ... 

so you should be using:

pg_restore -d 'postgres://userb:[email protected]:5432/otherdatabase' dump.dump 

Yes, that user interface mismatch between pg_dump and pg_restore sucks, and I wish we could change it, but it's a bit late now.