Post-Reboot Issue: Postmaster Not Accessible

Rich Shepard wrote:

After having to reboot my server/workstation this morning, I've a
problem with
postgresql; one I've never before encountered.

The postmaster is running:

9959 pts/1 S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

and the socket is present:

srwxrwxrwx 1 root root 0 2007-02-28 05:20 /tmp/.s.PGSQL.5432=

But I cannot connect to the database for SQL-Ledger nor from the command
line:

[rshepard@salmo ~]$ psql
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

The server is running locally. How can I test whether it's accepting
connections on the socket? And, what can I do to get it back to normal and
accpting connections?

psql -h localhost -U postgres databasename

Joshua D. Drake

Thanks,

Rich

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Rich Shepard <rshepard@appl-ecosys.com> writes:

The postmaster is running:
9959 pts/1 S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
and the socket is present:
srwxrwxrwx 1 root root 0 2007-02-28 05:20 /tmp/.s.PGSQL.5432=

The socket file should surely not be owned by root ... there's something
pretty weird there. How did you start the postmaster?

regards, tom lane

On Wed, 28 Feb 2007, Joshua D. Drake wrote:

psql -h localhost -U postgres databasename

Joshua,

Well, something's wrong:

[rshepard@salmo ~]$ psql -h localhost -U postgres aesi
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

aesi=# \q
[rshepard@salmo ~]$ psql aesi
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I used to be able to connect to an existing database just by typing it's
name after the command.

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

On Wed, 28 Feb 2007, Tom Lane wrote:

The socket file should surely not be owned by root ... there's something
pretty weird there. How did you start the postmaster?

Tom,

The startup script, /etc/rc.d/rc.postgresql is run when the system boots.
The result is,

16648 pts/1 S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

That startup script has been in place for about 14 months now, and through
a number of reboots as hardware's been replaced. I can send it if desired.

The socket remains when the postmaster is killed; I assume that's normal.
I see that on my notebook, the socket is owned by postgres.users, and
there's a lockfile present. No lockfile on the server.

I chown postgres.users /tmp/.s.PGSQL.5432= but this still does not allow
me to log in either from the command line or with SQL-Ledger.

Is there a method other than a reboot to remedy this?

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

On Wednesday 28 February 2007 08:12, Rich Shepard <rshepard@appl-ecosys.com>
wrote:

[rshepard@salmo ~]$ psql -h localhost -U postgres aesi
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

aesi=# \q
[rshepard@salmo ~]$ psql aesi
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Something happened to your /tmp directory after PostgreSQL started up.

Stop the postmaster, clean out the socket in /tmp, and restart the
postmaster, and it will likely fix it up. Then you can investigate your
boot process to see what's going wrong.

--
"Remember when computers were frustrating because they did exactly what
you told them to? That actually seems sort of quaint now." --J.D. Baldwin

On Wed, 28 Feb 2007, Alan Hodgson wrote:

Something happened to your /tmp directory after PostgreSQL started up.

Alan,

Seems so, doesn't it?

Stop the postmaster, clean out the socket in /tmp, and restart the
postmaster, and it will likely fix it up. Then you can investigate your
boot process to see what's going wrong.

Ahhh-h-h! That did it. I did not realize that I could kill the socket and
have postgresql start it again upon loading. It's there now (owned by
postgres.users), and I can access both the shell and my accounts via
SQL-Ledger.

Thanks very much,

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard@appl-ecosys.com> writes:

Is there a method other than a reboot to remedy this?

Stop the postmaster, remove the bogus socket file by hand, start the
postmaster.

I imagine that if you check the postmaster log you will notice a bleat
near the beginning about failing to open the socket file (because of
the pre-existence of the root-owned file). It would come up anyway as
long as it could establish a TCP listen socket.

Offhand I would guess that things got this way because of some errant
tmp-cleaning script; perhaps the socket file got chown'd to root
sometime in the past during the previous postmaster's life.

regards, tom lane

On Wed, 28 Feb 2007, Tom Lane wrote:

Stop the postmaster, remove the bogus socket file by hand, start the
postmaster.

Tom,

That did it.

I imagine that if you check the postmaster log you will notice a bleat
near the beginning about failing to open the socket file (because of the
pre-existence of the root-owned file). It would come up anyway as long as
it could establish a TCP listen socket.

I see.

Offhand I would guess that things got this way because of some errant
tmp-cleaning script; perhaps the socket file got chown'd to root sometime
in the past during the previous postmaster's life.

Something certainly did the wrong thing. Now that I know I can safely
delete the socket file I'm prepared if this ever happens again.

Thank you,

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863