Re: [GENERAL] pg_restore to a port where nobody is listening?

On 12/21/2016 12:59 PM, Guillaume Lelarge wrote:
> 2016-12-21 20:29 GMT+01:00 Daniel Westermann
> <daniel.westermann@dbi-services.com
> <mailto:daniel.westermann@dbi-services.com>>:
>
>     >> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d -C -j 2 /var/tmp/exp/
>     >>
>     >> This runs fine but where does it connect to? Nothing is listening on port 5439.
>
>     >Given the lack of a -d switch, I'd expect it not to try to connect
>     >anywhere, just emit the restore script on stdout.  At least, that's
>     >what happens for me.  It's weird that you don't see any printout.
>
>     >(To be clear: it's -d that triggers a connection attempt in pg_restore.
>     >Without that, -h and -p are just noise.)
>
>     Ok, that makes sense. I got the output on screen, as mentioned.
>
>     What I would have expected is at least a hint or warning that host
>     and port are ignored if you do not specify the "-d" switch. Giving
>     port and host clearly indicates that I want to connect to what I
>     provided, doesn't it? psql uses the os username as default database,
>     pg_restore doesn't?
>
>     postgres@pgbox:/home/postgres/ [PG961] unset PGDATABASE
>     postgres@pgbox:/home/postgres/ [] psql
>     psql (9.6.1)
>     Type "help" for help.
>
>     (postgres@[local]:5439) [postgres] >
>
>     Providing "-d" gives a meaningful message at least:
>
>     postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p
>     ===6666 -d postgres -F d -C /var/tmp/exp/
>     pg_restore: [archiver (db)] connection to database "postgres"
>     failed: invalid port number: "===6666"
>
>     Maybe it is only me, but this is not consistent behavior, is it?
>
>
> It isn't consistent but it's by purpose. And there's a really good
> reason for that behaviour. There's no issue with psql connecting to a
> default database because psql doesn't do anything by itself. pg_restore

That is not entirely accurate. psql -f some_destructive_script.sql could
ruin you day.

> will do something to the database it connects to. It might drop some
> objects, create some, add data. I want to be sure it's restored in the
> right database. I don't want it to second-guess what I want to do.
> Otherwise, I'll have a really hard time fixing everything it did. So -d
> is required by pg_restore to connect to some database, whereas there's
> no big deal with psql connecting to a default database.
>
>
> --
> Guillaume.
>   http://blog.guillaume.lelarge.info
>   http://www.dalibo.com


--
Adrian Klaver
adrian.klaver@aklaver.com