listening on tcp socket instead of unix domain

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

listening on tcp socket instead of unix domain

David Bear
Hoping to get postgresql to listen on a tcp socket I made the
following change to postgresql.conf:

#listen_addresses = 'localhost' # what IP interface(s) to listen on;
listen_addresses = 'localhost'  # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
#port = 5432
port = 5432

I understand this to mean, listen on localhost, ie 127.0.0.1.

However, a netstat -l tells me:

netstat -l | grep 5432
unix  2      [ ACC ]     STREAM     LISTENING     732094 /tmp/.s.PGSQL.5432

netstat -l | grep postg
tcp        0      0 localhost:postgresql    *:* LISTEN
tcp        0      0 localhost:postgresql    *:* LISTEN

This doesn't look right. Moreover, when attempting to create an ssh
tunnel to connect to postgres from a windows box, the connection
fails.

Is postgres really listening on a socket that is 'connect-able'
through ssh tunneling?  If so, then would using ssh command

 ssh -L 5432:remotehost:5432 user@remotehost

be sufficient to connect to the postgres running on remote host? This
fails when running psql on the machine with message:

> psql -p 5432
psql: could not connect to server: No such file or directory
...

So, I don't think something is quite working. Please advise.

--
David Bear
phone: 480-965-8257
fax: 480-965-9189
College of Public Programs/ASU
Wilson Hall 232
Tempe, AZ 85287-0803
 "Beware the IP portfolio, everyone will be suspect of trespassing"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: listening on tcp socket instead of unix domain

Tom Lane-2
David Bear <[hidden email]> writes:
> However, a netstat -l tells me:

> netstat -l | grep 5432
> unix  2      [ ACC ]     STREAM     LISTENING     732094 /tmp/.s.PGSQL.5432

> netstat -l | grep postg
> tcp        0      0 localhost:postgresql    *:* LISTEN
> tcp        0      0 localhost:postgresql    *:* LISTEN

> This doesn't look right.

It looks fine to me.  The reason there are two listening sockets is most
likely that one is IPv4 and the other is IPv6 --- netstat's default display
fails to distinguish this (at least on Linux), but you can see it if you
do "netstat -l -n".

> Moreover, when attempting to create an ssh tunnel to connect to
> postgres from a windows box, the connection fails.

You're going to have to give more detail (like exactly what you tried
and exactly what happened) if you want help with that.  It could be a
firewall issue, but that's strictly a guess.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

wal logs in pg_xlog

Nasir Iqbal Danish
I am using postgres 8.0.3, and wanted to use Point In Time Recovery enable.
could any one tell me about how to indentify the current WAL file, also
let me know if there is any fix patteren for WAL log file generations.
documentaion says it will generate ever increasing number starting from
some thing like "0000000100000000000000000". but I have observed
different behaviour. I would also want to know, how does it archive
files. for example we have log file 000002, now when archiving  will  
it  start new  file  as  000003 or  will it  archive  000002  as  
000003  and  lastest will  be  same  as  000002.

- Nasir


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: wal logs in pg_xlog

Michael Fuhr
On Mon, Jun 27, 2005 at 11:04:50AM +0500, Nasir Iqbal Danish wrote:
>
> I am using postgres 8.0.3, and wanted to use Point In Time Recovery enable.
> could any one tell me about how to indentify the current WAL file, also
> let me know if there is any fix patteren for WAL log file generations.
> documentaion says it will generate ever increasing number starting from
> some thing like "0000000100000000000000000". but I have observed
> different behaviour.

What behavior have you observed?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: wal logs in pg_xlog

Nasir Iqbal Danish
Michael Fuhr wrote:
On Mon, Jun 27, 2005 at 11:04:50AM +0500, Nasir Iqbal Danish wrote:
  
I am using postgres 8.0.3, and wanted to use Point In Time Recovery enable.
could any one tell me about how to indentify the current WAL file, also 
let me know if there is any fix patteren for WAL log file generations.
documentaion says it will generate ever increasing number starting from 
some thing like "0000000100000000000000000". but I have observed 
different behaviour.
    

What behavior have you observed?

  
the files in pg_xlog like 000010000000000005, 000010000000000006, 000010000000000007, 000010000000000008, (in short term call these 5,6,7,8).
with file 8 being most recent and 5 being 2 months back, with respect to time stamp. after I did some work and then observed log files, now 5 and 6 were showing me latest time stamps. I was confused which is the latest one.
I also want to know when new WAL segment files is created, is it 16MB or this will start from 0 bytes and size inceases as soon as db server will manupulate.

Please provide me if u can some script to identify which one is latest file so that I can copy this to some other place.
One more question. Is there a way to build or configure the database server to replicate pg_xlogs ?
-Nasir
Reply | Threaded
Open this post in threaded view
|

Re: wal logs in pg_xlog

Jeff Frost
On Tue, 28 Jun 2005, Nasir Iqbal Danish wrote:

> Please provide me if u can some script to identify which one is latest file
> so that I can copy this to some other place.
> One more question. Is there a way to build or configure the database server
> to replicate pg_xlogs ?

I use this fairly simple method:

LASTLOG=`ls -rt /pg_xlog/ | grep -v "backup\|archive" | tail -1`

There might be something more elegant.  I also use the following method to get
rid of archived wal files that are older than the oldest base backup I am
keeping:

OLDESTFILE=$PITRBASEDIR/`ls -t $PITRBASEDIR | tail -1`/backup_label

if [ -f $OLDESTFILE ]; then
  /usr/bin/find $PITRDESTDIR -type f -not -newer $OLDESTFILE \
  -exec rm -f \{\} \;
fi

Your mileage may vary, but it appears to work for me.

--
Jeff Frost, Owner <[hidden email]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org