Thread: Access Problem After Version Upgrade
Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data
because of both my ignorance of the proper syntax and the move from
/usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server:
[rshepard@salmo ~]$ psql -l
psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
This also prevents me from logging in to SQL-Ledger and other applications.
Now, I don't know that the server is accepting connections, but
srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432=
exists as a socket.
What do I do to trace the source of this problem and fix it?
TIA,
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On 1/26/06 11:53 AM, "Rich Shepard" <rshepard@appl-ecosys.com> wrote: > Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data > because of both my ignorance of the proper syntax and the move from > /usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server: > > [rshepard@salmo ~]$ psql -l > psql: could not connect to server: Permission denied > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? > > This also prevents me from logging in to SQL-Ledger and other applications. > Now, I don't know that the server is accepting connections, but > srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432= > exists as a socket. > > What do I do to trace the source of this problem and fix it? You did start the server? Did you fix the pg_hba.conf file? Does your postgresql.conf file allow tcp connections? Those are the places I would look. Sean
On Thu, 26 Jan 2006, Rich Shepard wrote:
> psql: could not connect to server: Permission denied
> What do I do to trace the source of this problem and fix it?
Some progress to report. A Google search found a reply from Tom Lane last
month to someone reporting the same error. I changed permissions on the
socket to make them world writable, and that fixed one problem:
[rshepard@salmo ~]$ psql -l
List of databases
Name | Owner | Encoding
-------------+------------+-----------
aesi | sql-ledger | LATIN1
eiabusiness | rshepard | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
test | postgres | SQL_ASCII
webcollab | rshepard | SQL_ASCII
(7 rows)
So, now I can see the tables in the various databases, but SQL-Ledger still
cannot:
Internal Server Error
The server encountered an internal error or misconfiguration and was unable
to complete your request.
How do I get this fixed, please?
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Thu, 26 Jan 2006, Sean Davis wrote:
> You did start the server? Did you fix the pg_hba.conf file? Does your
> postgresql.conf file allow tcp connections? Those are the places I would
> look.
Sean,
Thank you.
Yes, the server is running:
21839 ? S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
I don't know what needs fexing in /var/lib/pgsql/data/pg_hba.conf. It has:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
which should cover both socket and tcp connections, if I understand
correctly.
The header of postgresql.conf says that the commented options are defaults,
and the connections section has:
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = off
#password_encryption = on
#db_user_namespace = off
Is this correct?
Thanks again,
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
Rich Shepard <rshepard@appl-ecosys.com> writes:
> Now, I don't know that the server is accepting connections, but
> srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432=
> exists as a socket.
> What do I do to trace the source of this problem and fix it?
That's got the wrong ownership (should be postgres not root) and
the wrong permissions (should be world-writable). Did somebody
do something silly like chown everything in /tmp?
You could manually fix the socket file's ownership and permissions,
or just remove it and restart the postmaster.
regards, tom lane
Rich Shepard <rshepard@appl-ecosys.com> writes: > So, now I can see the tables in the various databases, but SQL-Ledger still > cannot: > > Internal Server Error > The server encountered an internal error or misconfiguration and was unable > to complete your request. > > How do I get this fixed, please? That's a webserver error usually meaning a CGI script crashed or gave bad output. Look in the webserver error log file to see what happened. -Doug
On Thu, 26 Jan 2006, Doug McNaught wrote: > That's a webserver error usually meaning a CGI script crashed or gave bad > output. Look in the webserver error log file to see what happened. Doug, Sigh. All I see there are references to SQL-Ledger scripts not finding libraries (which are installed where SQL-Ledger is looking for them), and it's login.pl script failing to run. When I posted on that mail list, Dieter pulled the thread and wrote me an angry message that SQL-Ledger is not broken, but PostgreSQL is. The postmaster is running, and I (as an individual) can now see what databases are there. So, that permission error on the socket is fixed and it looks like postgres is doing just what it should be doing. Dieter Simander tells me there's nothing wrong with SL. Here's /var/log/apache/error_log: [Thu Jan 26 08:59:36 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3:cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.7/i486-linux/DynaLoader.pm line230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a required shared library or dll isn't installed where expected at SL/User.pm line 116 Compilation failed in require at /usr/local/sql-ledger/login.pl line 92. [Thu Jan 26 09:03:29 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl Now, when I point firefox at: http://eia.appl-ecosys.com/webcollab-1.70/index/php Up comes the Webcollab login page and I can log in. This suggests to me that there's nothing wrong with apache (httpd), either. Checkmate. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Thu, 26 Jan 2006, Tom Lane wrote: > That's got the wrong ownership (should be postgres not root) Tom, I wondered about that. > and the wrong permissions (should be world-writable). Did somebody do > something silly like chown everything in /tmp? Not me ... intentionally. > You could manually fix the socket file's ownership and permissions, or just > remove it and restart the postmaster. I did the permissions before and just did the ownership. Still no happiness with SL. Thanks very much, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
looks like perl/DBD::Pg is looking for libpq.so.3 but postgresql 8.1.X supplies libpq.so.4. You need to rebuild DBD::Pg to get this to work (atleast that is what I have been doing for my upgrades from 7.4.X to 8.1.2) Jim ---------- Original Message ----------- From: Rich Shepard <rshepard@appl-ecosys.com> To: Doug McNaught <doug@mcnaught.org> Cc: pgsql-general@postgresql.org Sent: Thu, 26 Jan 2006 09:30:05 -0800 (PST) Subject: Re: [GENERAL] Access Problem After Version Upgrade -- Update > On Thu, 26 Jan 2006, Doug McNaught wrote: > > > That's a webserver error usually meaning a CGI script crashed or gave bad > > output. Look in the webserver error log file to see what happened. > > Doug, > > Sigh. All I see there are references to SQL-Ledger scripts not finding > libraries (which are installed where SQL-Ledger is looking for them), and > it's login.pl script failing to run. When I posted on that mail list, Dieter > pulled the thread and wrote me an angry message that SQL-Ledger is not > broken, but PostgreSQL is. > > The postmaster is running, and I (as an individual) can now see what > databases are there. So, that permission error on the socket is fixed and it > looks like postgres is doing just what it should be doing. > > Dieter Simander tells me there's nothing wrong with SL. Here's > /var/log/apache/error_log: > > [Thu Jan 26 08:59:36 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl > install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module > DBD::Pg: libpq.so.3: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.7/i486- > linux/DynaLoader.pm line 230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a > required shared library or dll isn't installed where expected at SL/User.pm line 116 Compilation failed in > require at /usr/local/sql-ledger/login.pl line 92. > [Thu Jan 26 09:03:29 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl > > Now, when I point firefox at: > http://eia.appl-ecosys.com/webcollab-1.70/index/php > > Up comes the Webcollab login page and I can log in. > > This suggests to me that there's nothing wrong with apache (httpd), either. > > Checkmate. > > Thanks, > > Rich > > -- > Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental > Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" > <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings ------- End of Original Message -------
Rich Shepard <rshepard@appl-ecosys.com> writes:
> install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module
DBD::Pg:libpq.so.3: cannot open shared object file: No such file or directory at
/usr/lib/perl5/5.8.7/i486-linux/DynaLoader.pmline 230.
This seems to be your problem right here. Is libpq.so.3 installed (as
opposed to some other version number)? Is it where the dynamic loader
will look for it?
I'm guessing your upgrade replaced libpq.so.3 with libpq.so.4. Did you
perhaps override RPM's complaints that there was a dependency on
libpq.so.3? (If there wasn't one, there's something wrong with the
packaging of DBD::Pg.) You probably need to either put back libpq.so.3,
or update DBD::Pg.
regards, tom lane
On Thu, 26 Jan 2006, Jim Buttafuoco wrote: > looks like perl/DBD::Pg is looking for libpq.so.3 but postgresql 8.1.X > supplies libpq.so.4. You need to rebuild DBD::Pg to get this to work > (atleast that is what I have been doing for my upgrades from 7.4.X to > 8.1.2) Jim, I thought I had mentioned this, but that might have been in my message to the sql-ledger list. [rshepard@salmo ~]$ locate libpq.so.3 /usr/local/pgsql/lib/libpq.so.3.0 /usr/local/pgsql/lib/libpq.so.3.1 /usr/local/pgsql/lib/libpq.so.3 ... but that's not where SL is looking for them! When I searched for libpq.so.4, I discovered that in /usr/lib. As soon as I copied all the '3' versions to that directory, SL found its way. Thank you _very_ much, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Thu, 26 Jan 2006, Rich Shepard wrote: >> What do I do to trace the source of this problem and fix it? Thanks to Jim I found that libpq.so.3 were in the old directory. As soon as I moved them to /usr/lib (with libpq.so.4), SQL-Ledger allowed me to log in. All of this reminds me of the time, about 8 years ago, when postgres would install in one directory, but Red Hat's rpms put it in another directory. That caused all sorts of problems until I started building from source and ignoring the packages. Here, it was the move from /usr/local/pgsql to /var/lib/pgsql that caused me all this grief. A huge "thank you" to all of you -- and especially Jim. Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
> Here, it was the move from /usr/local/pgsql to /var/lib/pgsql that caused > me all this grief. > > A huge "thank you" to all of you -- and especially Jim. Although I am glad you were able to get up and running, typically you don't want to "move" libs like that. Instead update your /etc/ld.so.conf and run ldconfig. Sincerely, Joshua D. Drake > > Rich > -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
On Thu, 26 Jan 2006, Joshua D. Drake wrote: > Although I am glad you were able to get up and running, typically you don't > want to "move" libs like that. Instead update your /etc/ld.so.conf and run > ldconfig. True, Josh. What I'd prefer to do is remove /usr/local/pgsql/ once I know that nothing there is being used any more with the 8.x versions of postgres. I thought of making softlinks, but those would fail as soon as the directory tree was removed. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
Rich Shepard <rshepard@appl-ecosys.com> writes: > On Thu, 26 Jan 2006, Joshua D. Drake wrote: > >> Although I am glad you were able to get up and running, typically you don't >> want to "move" libs like that. Instead update your /etc/ld.so.conf and run >> ldconfig. > > True, Josh. What I'd prefer to do is remove /usr/local/pgsql/ once I know > that nothing there is being used any more with the 8.x versions of postgres. > I thought of making softlinks, but those would fail as soon as the directory > tree was removed. FWIW, my practice when compiling PG from source (which I usually do) is as follows: * Configure each release with '--prefix=/usr/local/pgsql-8.1' (or whatever) * Create (as far as disk space permits) independent data directories for each version--e.g. '/var/lib/pgsql-8.1/data'. * Symlink whatever version I'm running to '/usr/local/pgsql'. Have the standard PATH contain '/usr/local/pgsql/bin'. This way, when I do an upgrade, I can test beforehand by setting PATH and LD_LIBRARY_PATH (if applicable) appropriately, migrating my data, running whatever programs I want to test, then switching the symlink. It's worked fairly well, and it's nice to have the old binaries and data directory sitting there to switch back to of something breaks horribly. The problem I've seen with RPM upgrades is that if something breaks in the data upgrade process, the old binaries are gone and it's a pain to get back to where you were. Putting locally-compiled software in /usr/bin or /usr/lib is a bad idea, generally, as you may confuse your package manager. -Doug