Re: pg_restore to a port where nobody is listening?

2016-12-21 20:29 GMT+01:00 Daniel Westermann <
daniel(dot)westermann(at)dbi-services(dot)com>:

> >> postgres(at)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(at)pgbox:/home/postgres/ [PG961] unset PGDATABASE
> postgres(at)pgbox:/home/postgres/ [] psql
> psql (9.6.1)
> Type "help" for help.
>
> (postgres(at)[local]:5439) [postgres] >
>
> Providing "-d" gives a meaningful message at least:
>
> postgres(at)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 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