Thread: listening on tcp socket instead of unix domain
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"
David Bear <David.Bear@asu.edu> 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
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
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/
Michael Fuhr wrote:
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
the files in pg_xlog like 000010000000000005, 000010000000000006, 000010000000000007, 000010000000000008, (in short term call these 5,6,7,8).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?
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
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 <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954