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

[Daniel Westermann]

>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 

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

[Adrian Klaver]

On 12/21/2016 12:59 PM, Guillaume Lelarge wrote:

2016-12-21 20:29 GMT+01:00 Daniel Westermann
>:

>> 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
=== -d postgres -F d -C /var/tmp/exp/
pg_restore: [archiver (db)] connection to database "postgres"
failed: invalid port number: "==="

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.kla...@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

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

[Adrian Klaver]

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
=== -d postgres -F d -C /var/tmp/exp/
pg_restore: [archiver (db)] connection to database "postgres" failed:
invalid port number: "==="

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.kla...@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

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

[Guillaume Lelarge]

2016-12-21 20:29 GMT+01:00 Daniel Westermann <
daniel.westerm...@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 ===
> -d postgres -F d -C /var/tmp/exp/
> pg_restore: [archiver (db)] connection to database "postgres" failed:
> invalid port number: "==="
>
> 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

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

[Daniel Westermann]

>> 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 === -d 
postgres -F d -C /var/tmp/exp/ 
pg_restore: [archiver (db)] connection to database "postgres" failed: invalid 
port number: "===" 

Maybe it is only me, but this is not consistent behavior, is it? 


Regards 
Daniel 

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

[Tom Lane]

Daniel Westermann  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

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

[Francisco Olarte]

On Wed, Dec 21, 2016 at 6:22 PM, Daniel Westermann
 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.

, 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

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

[Andreas Kretschmer]

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

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

[Daniel Westermann]

It does

Sent from my Phone

> On 21 Dec 2016, at 18:40, Andreas Kretschmer  wrote:
> 
> 
> 
>> 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

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

[Adrian Klaver]

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
tcp0  0 0.0.0.0:22  0.0.0.0:*
LISTEN  0  15929  -
tcp0  0 127.0.0.1:250.0.0.0:*
LISTEN  0  17460  -
tcp0  0 0.0.0.0:54380.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
udp0  0 0.0.0.0:68
0.0.0.0:*   0  14940
-
udp0  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
=== -F d -C /var/tmp/exp/


Thanks
Daniel




--
Adrian Klaver
adrian.kla...@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

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

[Daniel Westermann]

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. 

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 === -F d 
-C /var/tmp/exp/ 


Thanks 
Daniel