Thread: [GENERAL] pg_restore to a port where nobody is listening?
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
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
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] echo $PGPORT
5439
postgres@pgbox:/home/postgres/ [PG961] pg_restore -V
pg_restore (PostgreSQL) 9.6.1
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 -
(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?
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
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
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
5439postgres@pgbox:/home/postgres/ [PG961] pg_restore -V
pg_restore (PostgreSQL) 9.6.1postgres@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
Sent from my Phone
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
5439postgres@pgbox:/home/postgres/ [PG961] pg_restore -V
pg_restore (PostgreSQL) 9.6.1postgres@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
>> 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.)
>>
>> 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] >
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"
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>:
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.
>> 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.
--
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
>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