Обсуждение: Unable to connect remotely

<font face="David CLM">Hello everybody,<br /><br /> I've just installed PostgreSQL and I'm unable to connect to it
remotelyfrom my main desktop (I'm trying to connect with pgAdmin 3).<br /><br /> My setup:<br /> Ubuntu Server 14.04
x64(Azure Cloud)<br /> PostgreSQL v9.3.5-0ubuntu0.14.04.1<br /> contrib package is also installed.<br /> PostgreSQL is
startedon boot using Upstart. I've modified this script: <a class="moz-txt-link-freetext"
href="https://gist.github.com/haad/6020401">https://gist.github.com/haad/6020401</a>(just version numbers)<br /><br />
MyProblems:<br /> 1) For some reason, when I've installed pg, /var/run/postgresql was missing. I've manually created
thisfolder and gave my pg user write permissions. Is that a bug?<br /> 2) I've tried to change the log config in
/etc/postgresql/9.3/main/postgresql.confbut it won't work. I think that pg is ignoring that file. I also have no idea
whereis pg log file (I tried /var/log/postgresql - not there). I tried starting the DB manually with pg_ctl -l but the
logdoes say much:<br />     LOG:  database system is ready to accept connections<br />     LOG:  autovacuum launcher
started<br/>     LOG:  received SIGHUP, reloading configuration files<br />     LOG:  received smart shutdown
request<br/>     LOG:  autovacuum launcher shutting down<br />     LOG:  shutting down<br />     LOG:  database system
isshut down<br />     LOG:  database system was shut down at 2014-10-17 21:22:11 UTC<br />     LOG:  database system is
readyto accept connections<br />     LOG:  autovacuum launcher started<br /> This log contains a single DB run.<br />
3)I'm unable to connect from my main desktop. Server is listening according to netstat. There is no firewall on the
serverand I've created an Azure endpoint for port 5432. Ideas?<br /><br /> Can you help me? <br /><br /> Thanks,<br
/><br/></font><div class="moz-signature">-- <br /></div><p><font face="Liberation Serif, serif"><span
lang="en-US"><fontface="Times New Roman"><font size="4" style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a
href="www.kapot-tamar.com">KapotTamar</a> </font></font></span></font> 

<font face="David CLM"><big>Hi everyone,<br /><br /> First of all it's appears that there was something wrong with my
Upstartconf file (Ubuntu is using Upstart instead of SysV init. Next year they'll also move to Systemd). Jay and Raghu:
Thatsolved my connection problem and /var/run problem.<br /> So now pg is up (according to ps -aux |grep post). However
<fontface="monospace">pg_ctl status</font> says no server is running. What's wrong? <br /><br /> About the logs: I have
pg_xlogand pg_clog under PFDATA - nothing there.<br /><br /> Remote connection: When I try to connect to the server I
getan error that my user is not present at pg_hba.conf. However, I see that I have 2 such files: One in PGDATA and one
in/etc/postgresql/9.3/main/. What's the difference between them?<br /><br /> Thanks<br /></big><br /><br /></font><div
class="moz-cite-prefix"style="direction: ltr;">On 10/18/2014 03:48 AM, <a class="moz-txt-link-abbreviated"
href="mailto:jayknowsunix@gmail.com">jayknowsunix@gmail.com</a>wrote:<br /></div><blockquote
cite="mid:638CECFF-7F53-4BEC-A58E-8939079192E7@gmail.com"style="direction: ltr;" type="cite"><div style="direction:
ltr;">HiShay,</div><div style="direction: ltr;"><br /></div><div style="direction: ltr;">Per your questions:</div><div
style="direction:ltr;">1) from my experience, /var/run is only used to write a PID file, but this file is usually only
createdby your system's service commands. I don't use Ubuntu, only CentOS, so my command is "service postgresql-9.3
[start| stop | status| ... ] and the script writes the PID file. The CentOS script also writes out a /var/subsys/lock
filefor postgresql.</div><div style="direction: ltr;">2) your logfiles, unless you've butchered the postgresql.conf
file,live in the $PGDATA/pg_log directory. Wherever that is on your system.<br /> 3) i probably can't help much here,
butfirst if you're running selinux, I'd probably turn that off first. Next, check if iptables is running and possibly
blockingyour response. Either turn that off or add a rule to permit this port.</div><div style="direction: ltr;"><br
/></div><divstyle="direction: ltr;">Hope this helps,</div><div style="direction: ltr;">Jay</div><div style="direction:
ltr;"><br/> Sent from my iPad</div><div style="direction: ltr;"><br /> On Oct 17, 2014, at 6:12 PM, Shay Gover <<a
href="mailto:shay@kapotamar.com"moz-do-not-send="true">shay@kapotamar.com</a>> wrote:<br /><br /></div><blockquote
style="direction:ltr;" type="cite"><div style="direction: ltr;"><font face="David CLM">Hello everybody,<br /><br />
I'vejust installed PostgreSQL and I'm unable to connect to it remotely from my main desktop (I'm trying to connect with
pgAdmin3).<br /><br /> My setup:<br /> Ubuntu Server 14.04 x64 (Azure Cloud)<br /> PostgreSQL
v9.3.5-0ubuntu0.14.04.1<br/> contrib package is also installed.<br /> PostgreSQL is started on boot using Upstart. I've
modifiedthis script: <a class="moz-txt-link-freetext" href="https://gist.github.com/haad/6020401"
moz-do-not-send="true">https://gist.github.com/haad/6020401</a>(just version numbers)<br /><br /> My Problems:<br /> 1)
Forsome reason, when I've installed pg, /var/run/postgresql was missing. I've manually created this folder and gave my
pguser write permissions. Is that a bug?<br /> 2) I've tried to change the log config in
/etc/postgresql/9.3/main/postgresql.confbut it won't work. I think that pg is ignoring that file. I also have no idea
whereis pg log file (I tried /var/log/postgresql - not there). I tried starting the DB manually with pg_ctl -l but the
logdoes say much:<br />     LOG:  database system is ready to accept connections<br />     LOG:  autovacuum launcher
started<br/>     LOG:  received SIGHUP, reloading configuration files<br />     LOG:  received smart shutdown
request<br/>     LOG:  autovacuum launcher shutting down<br />     LOG:  shutting down<br />     LOG:  database system
isshut down<br />     LOG:  database system was shut down at 2014-10-17 21:22:11 UTC<br />     LOG:  database system is
readyto accept connections<br />     LOG:  autovacuum launcher started<br /> This log contains a single DB run.<br />
3)I'm unable to connect from my main desktop. Server is listening according to netstat. There is no firewall on the
serverand I've created an Azure endpoint for port 5432. Ideas?<br /><br /> Can you help me? <br /><br /> Thanks,<br
/><br/></font><div class="moz-signature" style="direction: ltr;">-- <br /></div></div></blockquote></blockquote><p
style="direction:ltr;"><font face="Liberation Serif,               serif"><span lang="en-US"><font face="Times New
Roman"><fontsize="4" style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a href="www.kapot-tamar.com"
moz-do-not-send="true">KapotTamar</a> </font></font></span></font><br /><div class="moz-signature" style="direction:
ltr;">--<br /></div><p><font face="Liberation Serif, serif"><span lang="en-US"><font face="Times New Roman"><font
size="4"style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a href="www.kapot-tamar.com">Kapot Tamar</a>
</font></font></span></font>

Hi everyone,

First of all it's appears that there was something wrong with my Upstart conf file (Ubuntu is using Upstart instead of SysV init. Next year they'll also move to Systemd). Jay and Raghu: That solved my connection problem and /var/run problem.
So now pg is up (according to ps -aux |grep post). However pg_ctl status says no server is running. What's wrong?

About the logs: I have pg_xlog and pg_clog under PFDATA - nothing there.

Remote connection: When I try to connect to the server I get an error that my user is not present at pg_hba.conf. However, I see that I have 2 such files: One in PGDATA and one in /etc/postgresql/9.3/main/. What's the difference between them?

Thanks

On 10/18/2014 03:48 AM, jayknowsunix@gmail.com wrote:

Hi Shay,

Per your questions:

1) from my experience, /var/run is only used to write a PID file, but this file is usually only created by your system's service commands. I don't use Ubuntu, only CentOS, so my command is "service postgresql-9.3 [start | stop | status| ... ] and the script writes the PID file. The CentOS script also writes out a /var/subsys/lock file for postgresql.

2) your logfiles, unless you've butchered the postgresql.conf file, live in the $PGDATA/pg_log directory. Wherever that is on your system.
3) i probably can't help much here, but first if you're running selinux, I'd probably turn that off first. Next, check if iptables is running and possibly blocking your response. Either turn that off or add a rule to permit this port.

Hope this helps,

Jay

Sent from my iPad

On Oct 17, 2014, at 6:12 PM, Shay Gover <shay@kapotamar.com> wrote:

Hello everybody,

I've just installed PostgreSQL and I'm unable to connect to it remotely from my main desktop (I'm trying to connect with pgAdmin 3).

My setup:
Ubuntu Server 14.04 x64 (Azure Cloud)
PostgreSQL v9.3.5-0ubuntu0.14.04.1
contrib package is also installed.
PostgreSQL is started on boot using Upstart. I've modified this script: https://gist.github.com/haad/6020401 (just version numbers)

My Problems:
1) For some reason, when I've installed pg, /var/run/postgresql was missing. I've manually created this folder and gave my pg user write permissions. Is that a bug?
2) I've tried to change the log config in /etc/postgresql/9.3/main/postgresql.conf but it won't work. I think that pg is ignoring that file. I also have no idea where is pg log file (I tried /var/log/postgresql - not there). I tried starting the DB manually with pg_ctl -l but the log does say much:
    LOG:  database system is ready to accept connections
    LOG:  autovacuum launcher started
    LOG:  received SIGHUP, reloading configuration files
    LOG:  received smart shutdown request
    LOG:  autovacuum launcher shutting down
    LOG:  shutting down
    LOG:  database system is shut down
    LOG:  database system was shut down at 2014-10-17 21:22:11 UTC
    LOG:  database system is ready to accept connections
    LOG:  autovacuum launcher started
This log contains a single DB run.
3) I'm unable to connect from my main desktop. Server is listening according to netstat. There is no firewall on the server and I've created an Azure endpoint for port 5432. Ideas?

Can you help me?

Thanks,

--

Shay Gover
IT Consultant
Kapot Tamar

Shay Gover
IT Consultant
Kapot Tamar

<font face="David CLM">I've used Ubuntu Repositories to be on the safe side. If this is really the source of the
problemI'll build from source.</font><br /><div class="moz-cite-prefix" style="direction: ltr;">On 10/18/2014 10:05 PM,
<aclass="moz-txt-link-abbreviated" href="mailto:jayknowsunix@gmail.com">jayknowsunix@gmail.com</a> wrote:<br
/></div><blockquotecite="mid:6C164257-D38C-461E-AE0B-108BCC090297@gmail.com" style="direction: ltr;" type="cite"><div
style="direction:ltr;">Sounds maybe like a botched installation. The only pg_hba.conf that should be present is the one
in$PGDATA. Also, depending on your configuration and how long it's been running, you may not have anything the pg_xlog
directoryother than a subdirectory. I think, if I'm recalling correctly, that the subdirectory is named archive. WAL
segmentsget written out here based on the setting for wal_keep_segments in the postgresql.conf file.</div><div
style="direction:ltr;"><br /></div><div style="direction: ltr;">Not really certain when pg_clogs get written
out,</div><divstyle="direction: ltr;">--</div><div style="direction: ltr;">Jay<br /><br /> Sent from my iPad</div><div
style="direction:ltr;"><br /> On Oct 18, 2014, at 2:06 PM, Shay Gover <<a href="mailto:shay@kapotamar.com"
moz-do-not-send="true">shay@kapotamar.com</a>>wrote:<br /><br /></div><blockquote style="direction: ltr;"
type="cite"><divstyle="direction: ltr;"><font face="David CLM"><big>Hi everyone,<br /><br /> First of all it's appears
thatthere was something wrong with my Upstart conf file (Ubuntu is using Upstart instead of SysV init. Next year
they'llalso move to Systemd). Jay and Raghu: That solved my connection problem and /var/run problem.<br /> So now pg is
up(according to ps -aux |grep post). However <font face="monospace">pg_ctl status</font> says no server is running.
What'swrong? <br /><br /> About the logs: I have pg_xlog and pg_clog under PFDATA - nothing there.<br /><br /> Remote
connection:When I try to connect to the server I get an error that my user is not present at pg_hba.conf. However, I
seethat I have 2 such files: One in PGDATA and one in /etc/postgresql/9.3/main/. What's the difference between them?<br
/><br/> Thanks<br /></big><br /><br /></font><div class="moz-cite-prefix" style="direction: ltr;">On 10/18/2014 03:48
AM,<a class="moz-txt-link-abbreviated" href="mailto:jayknowsunix@gmail.com"
moz-do-not-send="true">jayknowsunix@gmail.com</a>wrote:<br /></div><blockquote
cite="mid:638CECFF-7F53-4BEC-A58E-8939079192E7@gmail.com"style="direction: ltr;" type="cite"><div style="direction:
ltr;">HiShay,</div><div style="direction: ltr;"><br /></div><div style="direction: ltr;">Per your questions:</div><div
style="direction:ltr;">1) from my experience, /var/run is only used to write a PID file, but this file is usually only
createdby your system's service commands. I don't use Ubuntu, only CentOS, so my command is "service postgresql-9.3
[start| stop | status| ... ] and the script writes the PID file. The CentOS script also writes out a /var/subsys/lock
filefor postgresql.</div><div style="direction: ltr;">2) your logfiles, unless you've butchered the postgresql.conf
file,live in the $PGDATA/pg_log directory. Wherever that is on your system.<br /> 3) i probably can't help much here,
butfirst if you're running selinux, I'd probably turn that off first. Next, check if iptables is running and possibly
blockingyour response. Either turn that off or add a rule to permit this port.</div><div style="direction: ltr;"><br
/></div><divstyle="direction: ltr;">Hope this helps,</div><div style="direction: ltr;">Jay</div><div style="direction:
ltr;"><br/> Sent from my iPad</div><div style="direction: ltr;"><br /> On Oct 17, 2014, at 6:12 PM, Shay Gover <<a
href="mailto:shay@kapotamar.com"moz-do-not-send="true">shay@kapotamar.com</a>> wrote:<br /><br /></div><blockquote
style="direction:ltr;" type="cite"><div style="direction: ltr;"><font face="David CLM">Hello everybody,<br /><br />
I'vejust installed PostgreSQL and I'm unable to connect to it remotely from my main desktop (I'm trying to connect with
pgAdmin3).<br /><br /> My setup:<br /> Ubuntu Server 14.04 x64 (Azure Cloud)<br /> PostgreSQL
v9.3.5-0ubuntu0.14.04.1<br/> contrib package is also installed.<br /> PostgreSQL is started on boot using Upstart. I've
modifiedthis script: <a class="moz-txt-link-freetext" href="https://gist.github.com/haad/6020401"
moz-do-not-send="true">https://gist.github.com/haad/6020401</a>(just version numbers)<br /><br /> My Problems:<br /> 1)
Forsome reason, when I've installed pg, /var/run/postgresql was missing. I've manually created this folder and gave my
pguser write permissions. Is that a bug?<br /> 2) I've tried to change the log config in
/etc/postgresql/9.3/main/postgresql.confbut it won't work. I think that pg is ignoring that file. I also have no idea
whereis pg log file (I tried /var/log/postgresql - not there). I tried starting the DB manually with pg_ctl -l but the
logdoes say much:<br />     LOG:  database system is ready to accept connections<br />     LOG:  autovacuum launcher
started<br/>     LOG:  received SIGHUP, reloading configuration files<br />     LOG:  received smart shutdown
request<br/>     LOG:  autovacuum launcher shutting down<br />     LOG:  shutting down<br />     LOG:  database system
isshut down<br />     LOG:  database system was shut down at 2014-10-17 21:22:11 UTC<br />     LOG:  database system is
readyto accept connections<br />     LOG:  autovacuum launcher started<br /> This log contains a single DB run.<br />
3)I'm unable to connect from my main desktop. Server is listening according to netstat. There is no firewall on the
serverand I've created an Azure endpoint for port 5432. Ideas?<br /><br /> Can you help me? <br /><br /> Thanks,<br
/><br/></font><div class="moz-signature" style="direction: ltr;">-- <br
/></div></div></blockquote></blockquote></div></blockquote></blockquote><pstyle="direction: ltr;"><font
face="Liberation                    Serif, serif"><span lang="en-US"><font face="Times                         New
Roman"><fontsize="4" style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a href="www.kapot-tamar.com"
moz-do-not-send="true">KapotTamar</a> </font></font></span></font><br /><div class="moz-signature" style="direction:
ltr;">--<br /></div><p style="direction: ltr;"><font face="Liberation Serif,               serif"><span
lang="en-US"><fontface="Times New Roman"><font size="4" style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a
href="www.kapot-tamar.com"moz-do-not-send="true">Kapot Tamar</a> </font></font></span></font><br /><div
class="moz-signature">--<br /></div><p><font face="Liberation Serif, serif"><span lang="en-US"><font face="Times New
Roman"><fontsize="4" style="font-size: 14pt">Shay Gover<br /> IT Consultant<br /><a href="www.kapot-tamar.com">Kapot
Tamar</a></font></font></span></font> 

I've used Ubuntu Repositories to be on the safe side. If this is really the source of the problem I'll build from source.

On 10/18/2014 10:05 PM, jayknowsunix@gmail.com wrote:

Sounds maybe like a botched installation. The only pg_hba.conf that should be present is the one in $PGDATA. Also, depending on your configuration and how long it's been running, you may not have anything the pg_xlog directory other than a subdirectory. I think, if I'm recalling correctly, that the subdirectory is named archive. WAL segments get written out here based on the setting for wal_keep_segments in the postgresql.conf file.

Not really certain when pg_clogs get written out,

--

Jay

Sent from my iPad

On Oct 18, 2014, at 2:06 PM, Shay Gover <shay@kapotamar.com> wrote:

Hi everyone,

First of all it's appears that there was something wrong with my Upstart conf file (Ubuntu is using Upstart instead of SysV init. Next year they'll also move to Systemd). Jay and Raghu: That solved my connection problem and /var/run problem.
So now pg is up (according to ps -aux |grep post). However pg_ctl status says no server is running. What's wrong?

About the logs: I have pg_xlog and pg_clog under PFDATA - nothing there.

Remote connection: When I try to connect to the server I get an error that my user is not present at pg_hba.conf. However, I see that I have 2 such files: One in PGDATA and one in /etc/postgresql/9.3/main/. What's the difference between them?

Thanks

On 10/18/2014 03:48 AM, jayknowsunix@gmail.com wrote:

Hi Shay,

Per your questions:

1) from my experience, /var/run is only used to write a PID file, but this file is usually only created by your system's service commands. I don't use Ubuntu, only CentOS, so my command is "service postgresql-9.3 [start | stop | status| ... ] and the script writes the PID file. The CentOS script also writes out a /var/subsys/lock file for postgresql.

2) your logfiles, unless you've butchered the postgresql.conf file, live in the $PGDATA/pg_log directory. Wherever that is on your system.
3) i probably can't help much here, but first if you're running selinux, I'd probably turn that off first. Next, check if iptables is running and possibly blocking your response. Either turn that off or add a rule to permit this port.

Hope this helps,

Jay

Sent from my iPad

On Oct 17, 2014, at 6:12 PM, Shay Gover <shay@kapotamar.com> wrote:

Hello everybody,

I've just installed PostgreSQL and I'm unable to connect to it remotely from my main desktop (I'm trying to connect with pgAdmin 3).

My setup:
Ubuntu Server 14.04 x64 (Azure Cloud)
PostgreSQL v9.3.5-0ubuntu0.14.04.1
contrib package is also installed.
PostgreSQL is started on boot using Upstart. I've modified this script: https://gist.github.com/haad/6020401 (just version numbers)

My Problems:
1) For some reason, when I've installed pg, /var/run/postgresql was missing. I've manually created this folder and gave my pg user write permissions. Is that a bug?
2) I've tried to change the log config in /etc/postgresql/9.3/main/postgresql.conf but it won't work. I think that pg is ignoring that file. I also have no idea where is pg log file (I tried /var/log/postgresql - not there). I tried starting the DB manually with pg_ctl -l but the log does say much:
    LOG:  database system is ready to accept connections
    LOG:  autovacuum launcher started
    LOG:  received SIGHUP, reloading configuration files
    LOG:  received smart shutdown request
    LOG:  autovacuum launcher shutting down
    LOG:  shutting down
    LOG:  database system is shut down
    LOG:  database system was shut down at 2014-10-17 21:22:11 UTC
    LOG:  database system is ready to accept connections
    LOG:  autovacuum launcher started
This log contains a single DB run.
3) I'm unable to connect from my main desktop. Server is listening according to netstat. There is no firewall on the server and I've created an Azure endpoint for port 5432. Ideas?

Can you help me?

Thanks,

--

Shay Gover
IT Consultant
Kapot Tamar

Shay Gover
IT Consultant
Kapot Tamar

Shay Gover
IT Consultant
Kapot Tamar