pg_restore to a port where nobody is listening?

On 12/21/2016 09:22 AM, Daniel Westermann wrote:

Hi all,

I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on
the same host. I dump the 9.5.4 instance with:

pg_dump -h localhost -p 5438 -C -c -F d -j 2 -f /var/tmp/exp/ test

.. which runs fine. I get the output as expected:
postgres@pgbox:/home/postgres/ [PG954] ls /var/tmp/exp/
3016.dat.gz 3017.dat.gz toc.dat

Source instance:
(postgres@[local]:5438) [postgres] > show port;
port
------
5438
(1 row)

Time: 0.328 ms
(postgres@[local]:5438) [postgres] > select version();
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Now I try to import into 9.6.1 => the instance is not running but the
environment is set:

postgres@pgbox:/home/postgres/ [PG961] netstat -na | grep 5439
postgres@pgbox:/home/postgres/ [PG961] echo $PGPORT
5439

postgres@pgbox:/home/postgres/ [PG961] pg_restore -V
pg_restore (PostgreSQL) 9.6.1
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.

https://www.postgresql.org/docs/9.5/static/app-pgrestore.html

"pg_restore can operate in two modes. If a database name is specified,
pg_restore connects to that database and restores archive contents
directly into the database. Otherwise, a script containing the SQL
commands necessary to rebuild the database is created and written to a
file or standard output. This script output is equivalent to the plain
text output format of pg_dump. Some of the options controlling the
output are therefore analogous to pg_dump options."

So you can use pg_restore to restore all or part of a pg_dump (custom
format) file to another file. I find this very handy.

postgres@pgbox:/home/postgres/ [PG961] netstat -tulpen
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address
State User Inode PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:*
LISTEN 0 15929 -
tcp 0 0 127.0.0.1:25 0.0.0.0:*
LISTEN 0 17460 -
tcp 0 0 0.0.0.0:5438 0.0.0.0:*
LISTEN 1000 18923 2829/postgres
tcp6 0 0 :::22 :::*
LISTEN 0 15938 -
tcp6 0 0 ::1:25 :::*
LISTEN 0 17461 -
tcp6 0 0 :::5438 :::*
LISTEN 1000 18924 2829/postgres
udp 0 0 0.0.0.0:68
0.0.0.0:* 0 14940
-
udp 0 0 0.0.0.0:49566
0.0.0.0:* 0 14929
-
udp6 0 0 :::40307
:::* 0 14930
-

postgres@pgbox:/home/postgres/ [PG961] psql -h localhost -p 5439
psql: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?

What do I miss? I can give any port to pg_restore and it just seems to
be fine. Even this seems to working (the copy from stdin is displayed on
the screen):
postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p
===6666 -F d -C /var/tmp/exp/

Thanks
Daniel

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Am 21.12.2016 um 18:22 schrieb Daniel Westermann:

Now I try to import into 9.6.1 => the instance is not running but the
environment is set:

postgres@pgbox:/home/postgres/ [PG961] netstat -na | grep 5439
postgres@pgbox:/home/postgres/ [PG961] echo $PGPORT
5439

postgres@pgbox:/home/postgres/ [PG961] pg_restore -V
pg_restore (PostgreSQL) 9.6.1
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.

No, that can't run.

Andreas

It does

Sent from my Phone

On Wed, Dec 21, 2016 at 6:22 PM, Daniel Westermann
<daniel.westermann@dbi-services.com> wrote:

I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the
same host.

....
.....

What do I miss? I can give any port to pg_restore and it just seems to be
fine.

mmmm, are you by chance using debian/ubuntu/any derivative? Maybe
pg_restore is not directly executed. In Ubuntu I have this:

$ type -path pg_restore
/usr/bin/pg_restore
$ file /usr/bin/pg_restore
/usr/bin/pg_restore: symbolic link to ../share/postgresql-common/pg_wrapper
$ file /usr/share/postgresql-common/pg_wrapper
/usr/share/postgresql-common/pg_wrapper: Perl script text executable

And that pg_wrapper thingie has the habit of completely fscking my
connection options / service files ( even though I do not have a
server installed, I only install teh client programs to connect to the
remote servers ).

You could check with type/file wether you have something similar.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Daniel Westermann <daniel.westermann@dbi-services.com> writes:

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.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

Regards
Daniel

2016-12-21 20:29 GMT+01:00 Daniel Westermann <
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 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

On 12/21/2016 11:29 AM, Daniel Westermann wrote:

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?

Maybe so but that is why there is documentation for programs/commands ,
to deal with the exceptions. In this case the information is at the top
of the pg_restore docs. Generally whenever I get an odd result I head to
the bottom of the docs under the Notes section. This is usually where
the exceptions are called out.

Regards
Daniel

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

Ok, makes sense. Thanks all for your answers

Regards
Daniel