Обсуждение: [GENERAL] 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

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.

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

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.

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

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

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