Thread: Upgrade woes

Upgrade woes

From
"Greg Kelley"
Date:
Folks,

Upgraded RH8.0 to 9.0 last saturday. Then pgsql woldn't start - failed using
the /etc/rc.d/init.d script so I manually entered the command and discovered
that it had to fix the database due to not shutting down properly(???). Now
it starts just fine, but I can't connect.

data dir is:

/var/lib/pgsql/data

ps shows:

postgres 10783  0.0  0.3  9776 1984 pts/1    S    /usr/bin/postmaster -p
5432
postgres 10784  0.0  0.3 10768 1952 pts/1    S    postgres: stats buffer
process
postgres 10785  0.0  0.3  9800 1964 pts/1    S    postgres: stats collector
process

Test command is:

psql -h localhost -U postgres

Error is:

psql: could not connect to server: Connection refused
        Is the server running on host localhost and accepting
        TCP/IP connections on port 5432?

pg_hba.conf shows:

local    all
trust
host    all            127.0.0.1       255.255.255.255     trust

I also can't seem to find the postmaster log file...?

Thanks for any suggestions to get this going again! We have an online
scheduling system that is down for the count.

Rgds,

________________________
Greg Kelley, IT Director
Britannic Aviation, US and UK
US Office:
Pease Int'l Tradeport
68 New Hampshire Ave.
Portsmouth, NH  03801
603.766.3005
http://www.britannicaviation.com
AOPA, EAA, SSA
CFII SEL, MEL; Comm Glider


Re: Upgrade woes

From
Tom Lane
Date:
"Greg Kelley" <gkelley@britannicaviation.com> writes:
> Upgraded RH8.0 to 9.0 last saturday. Then pgsql woldn't start - failed using
> the /etc/rc.d/init.d script so I manually entered the command and discovered
> that it had to fix the database due to not shutting down properly(???). Now
> it starts just fine, but I can't connect.

> psql: could not connect to server: Connection refused

This is a kernel-level refusal.  Check your packet filtering setup --- I
bet you have traffic to port 5432 filtered out :-(

            regards, tom lane


Re: Upgrade woes

From
Lamar Owen
Date:
On Tuesday 15 April 2003 09:56, Tom Lane wrote:
> "Greg Kelley" <gkelley@britannicaviation.com> writes:
> > Upgraded RH8.0 to 9.0 last saturday. Then pgsql woldn't start - failed
> > using the /etc/rc.d/init.d script so I manually entered the command and
> > discovered that it had to fix the database due to not shutting down
> > properly(???). Now it starts just fine, but I can't connect.

> > psql: could not connect to server: Connection refused

> This is a kernel-level refusal.  Check your packet filtering setup --- I
> bet you have traffic to port 5432 filtered out :-(

Which is very easy to do with Red Hat Linux, since the install or upgrade
defaults to 'medium' security on the netfilter config.  Which is set to block
5432 by default; not on localhost, though, IIRC.

However, there's something very disturbing here.  Greg, what version of
PostgreSQL were you running on RH8?  RH8 shipped with 7.2.2, with an errata
to 7.2.4.  Did you upgrade to 7.3.x at some point?

Tom, if he was running 7.2.x prior, the initscript is programmed to prevent
starting postmaster if the database structure is too old.  Why did postmaster
subsequently startup when started manually, if he was running 7.2.x prior?

I sincerely hope you had upgraded to 7.3.x prior to upgrading to RHL 9, or we
have something very bad going on here.

Since you are attempting to connect to localhost, why not just let it use the
Unix domain socket (by not specifying the hostname) and see if that connects?
This will tell us whether netfilter is interposing itself.

Greg, if you were running 7.2.x prior, RPMs for 7.2.4 built on RHL9 are
available by FTP from ftp.postgresql.org, in /pub/binary/v7.2.4/RPMS/redhat-9

Please let us know what you have found out.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re: Upgrade woes

From
Tom Lane
Date:
Lamar Owen <lamar.owen@wgcr.org> writes:
> Tom, if he was running 7.2.x prior, the initscript is programmed to prevent
> starting postmaster if the database structure is too old.

Hardly necessary --- the postmaster itself checks that, too.  If he was
able to start the postmaster then he doesn't have a version problem.

            regards, tom lane


Re: Upgrade woes

From
Lamar Owen
Date:
On Tuesday 15 April 2003 11:28, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > Tom, if he was running 7.2.x prior, the initscript is programmed to
> > prevent starting postmaster if the database structure is too old.

> Hardly necessary --- the postmaster itself checks that, too.  If he was
> able to start the postmaster then he doesn't have a version problem.

In theory.

In any case, had he upgraded to 7.3.x prior to the RHL8.0 to RHL9 upgrade, why
did he receive any errors?  The OS upgrade process for Red Hat Linux, as you
well know, involves shutting the database system down prior to rebooting with
the OS CD.  So the very fact that there were any errors upon initial restart
is disturbing to me, even if he was on 7.3.x prior to his RHL9 upgrade.

Now if I just had a sacrificial RHL8.0 machine to test this with....
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re: Upgrade woes

From
"Greg Kelley"
Date:
Sorry folks, brain fart - I had upgraded to 7.3 before the RH 8-9 fun.
Initial postmaster failure to start was because of a 'bad' shutdown or
something during the upgrade but a manual run of postmaster seems to have
cleand up the database - I thought it was a version issue.

Now if I can figure out why RH9 has blocked port 5432...???


----- Original Message -----
From: "Lamar Owen" <lamar.owen@wgcr.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>; "Greg Kelley"
<gkelley@britannicaviation.com>
Cc: <pgsql-admin@postgresql.org>
Sent: 15 April, 2003 11:18 AM
Subject: Re: [ADMIN] Upgrade woes


On Tuesday 15 April 2003 09:56, Tom Lane wrote:
> "Greg Kelley" <gkelley@britannicaviation.com> writes:
> > Upgraded RH8.0 to 9.0 last saturday. Then pgsql woldn't start - failed
> > using the /etc/rc.d/init.d script so I manually entered the command and
> > discovered that it had to fix the database due to not shutting down
> > properly(???). Now it starts just fine, but I can't connect.

> > psql: could not connect to server: Connection refused

> This is a kernel-level refusal.  Check your packet filtering setup --- I
> bet you have traffic to port 5432 filtered out :-(

Which is very easy to do with Red Hat Linux, since the install or upgrade
defaults to 'medium' security on the netfilter config.  Which is set to
block
5432 by default; not on localhost, though, IIRC.

However, there's something very disturbing here.  Greg, what version of
PostgreSQL were you running on RH8?  RH8 shipped with 7.2.2, with an errata
to 7.2.4.  Did you upgrade to 7.3.x at some point?

Tom, if he was running 7.2.x prior, the initscript is programmed to prevent
starting postmaster if the database structure is too old.  Why did
postmaster
subsequently startup when started manually, if he was running 7.2.x prior?

I sincerely hope you had upgraded to 7.3.x prior to upgrading to RHL 9, or
we
have something very bad going on here.

Since you are attempting to connect to localhost, why not just let it use
the
Unix domain socket (by not specifying the hostname) and see if that
connects?
This will tell us whether netfilter is interposing itself.

Greg, if you were running 7.2.x prior, RPMs for 7.2.4 built on RHL9 are
available by FTP from ftp.postgresql.org, in
/pub/binary/v7.2.4/RPMS/redhat-9

Please let us know what you have found out.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Re: Upgrade woes

From
Lamar Owen
Date:
On Tuesday 15 April 2003 11:55, Greg Kelley wrote:
> Sorry folks, brain fart - I had upgraded to 7.3 before the RH 8-9 fun.
> Initial postmaster failure to start was because of a 'bad' shutdown or
> something during the upgrade but a manual run of postmaster seems to have
> cleand up the database - I thought it was a version issue.

Good.  /me breathes sigh of relief.

> Now if I can figure out why RH9 has blocked port 5432...???

As root run /sbin/iptables-save.  This will tell you what has been blocked. By
way of telling you what is allowed.

To unblock, you can either disable iptables, or you can add a rule to allow
TCP traffic incoming on port 5432.  See the netfilter documentation on how to
do this.

Just as a double-check: when you enabled TCP/IP connections under RH80, did
you do it in /etc/rc.d/init.d/postgresql (adding a -i to the pg_ctl line)?
Or did you edit /var/lib/pgsql/data/postgresql.conf, and change the
tcpip_socket line?  If the former, you need to do the latter.  An upgrade
will replace the initscript, but it shouldn't touch the postgresql.conf.

And does a Unix domain connection (no -h directive on the machine running
postmaster) with psql work?
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re: Upgrade woes

From
Lamar Owen
Date:
On Tuesday 15 April 2003 12:05, Lamar Owen wrote:
> To unblock, you can either disable iptables, or you can add a rule to allow
> TCP traffic incoming on port 5432.  See the netfilter documentation on how
> to do this.

Note:
If you have lokkit or gnome-lokkit installed, there is a way to customize the
setup.  You can allow tcp port 5432 using that interface.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re: Upgrade woes

From
"Greg Kelley"
Date:
I have not modified postgresql.conf, tcpip_socket = false is commented out.
I cannot connect using localhost, either. I did not even have iptables
installed, just did the RPM installation and ran iptables-save and it shows:

*filter
:INPUT ACCEPT [3152:151165]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [2072:908048]
COMMIT

Not sure what that means, never had to use or adjust iptables. Server is
behind separate firewall.

Thanks for your help.

Greg


----- Original Message -----
From: "Lamar Owen" <lamar.owen@wgcr.org>
To: "Greg Kelley" <gkelley@britannicaviation.com>
Cc: <pgsql-admin@postgresql.org>
Sent: 15 April, 2003 12:05 PM
Subject: Re: [ADMIN] Upgrade woes


On Tuesday 15 April 2003 11:55, Greg Kelley wrote:
> Sorry folks, brain fart - I had upgraded to 7.3 before the RH 8-9 fun.
> Initial postmaster failure to start was because of a 'bad' shutdown or
> something during the upgrade but a manual run of postmaster seems to have
> cleand up the database - I thought it was a version issue.

Good.  /me breathes sigh of relief.

> Now if I can figure out why RH9 has blocked port 5432...???

As root run /sbin/iptables-save.  This will tell you what has been blocked.
By
way of telling you what is allowed.

To unblock, you can either disable iptables, or you can add a rule to allow
TCP traffic incoming on port 5432.  See the netfilter documentation on how
to
do this.

Just as a double-check: when you enabled TCP/IP connections under RH80, did
you do it in /etc/rc.d/init.d/postgresql (adding a -i to the pg_ctl line)?
Or did you edit /var/lib/pgsql/data/postgresql.conf, and change the
tcpip_socket line?  If the former, you need to do the latter.  An upgrade
will replace the initscript, but it shouldn't touch the postgresql.conf.

And does a Unix domain connection (no -h directive on the machine running
postmaster) with psql work?
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Connection errors: Was:(Re: Upgrade woes)

From
Lamar Owen
Date:
On Tuesday 15 April 2003 12:13, Greg Kelley wrote:
> I have not modified postgresql.conf, tcpip_socket = false is commented out.
> I cannot connect using localhost, either. I did not even have iptables
> installed, just did the RPM installation and ran iptables-save and it
> shows:

Edit postgresql.conf, uncomment the tcpip_socket line, change it to true
instead of false, and restart postmaster (as root /sbin/service postgresql
restart)

Then retry the connection.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11