Re: [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