Subject: postgres system problems

From: [email-masked]
Date: Fri, 12 Jul 91 09:48:46 +1000

Hi, we are using postgres for our Masters By Coursework here at RMIT but we are
having a few problems. Every day the postgres backend seems to stop working
properly. Sometimes there is no response for particular DBs but others work
fine. Sometimes error messages are returned for all requests. I usually kill
and restart the postmaster and things start working again (for a while).

I don't have all the error messages people have been getting but one of them is:

could not lock buffer segment

Another thing I have noticed since we've been running postgres is that normal
users (!root) cannot run ipcs properly, eg:

numbat:719% ipcs
IPC status from numbat as of Fri Jul 12 09:44:41 1991
T     ID     KEY        MODE       OWNER    GROUP
Message Queues:
Shared Memory:
shmctl: Permission denied

We have a Sun SPARCserver2 running SUNOS 4.1.1 Rev B.

Does anyone have any ideas about what the problem is or how to solve it? It is
very annoying having to continually check the system to make sure it is
working.

	Thanks in advance, dmh

Darren Hosking			ACSnet: [email-masked]	 
Department Of Computer Science	ARPA:   [email-masked]
RMIT				CSNET:  dmh%goanna.cs.rmit.oz.au@australia
GPO Box 2476V			UUCP:   ...!uunet!goanna.cs.rmit.oz.au!dmh
Melbourne Vic., Australia 3001	PH: +61 3 660 3299 FAX: +61 3 662 1617

---

From: [email-masked]
Date: Thu, 25 Jul 91 11:10:20 +1000

With the well known multiuser problems in postgres 2.1 occurring all the time
here we need some way of making the system vaguely reliable. Is using postgres
directly the only alternative (to monitor)? If so what is the easiest way
to allow multiple users to access there own (different) databases with postgres?
Currently I get an error message from postgres complaining that the user-id
doesn't match the db owner id (postgres). If I chown all the database files
I still get the same error message.

	Thanks for any help, dmh

---

From: [email-masked] (Mike Olson)
Date: Wed, 24 Jul 91 18:44:43 PDT

> here we need some way of making the system vaguely reliable.

in a couple of weeks, 3.0 will be released, and will fix these problems.
(i know this doesn't help you immediately, but all the big database companies
say this).

> Is using postgres
> to allow multiple users to access there own (different) databases with
> postgres? Currently I get an error message from postgres complaining that
> the user-id doesn't match the db owner id (postgres). If I chown all the
> database files I still get the same error message.

postgres (the binary of that name) needs to be setuid postgres (the user
of that name) to work correctly.  user postgres should have uid 6.

running individual versions of postgres is tricky, since postgres will still
allocate semaphores for locking.  every backend will allocate its own if it's
not started by the postmaster, so you'll run out of semaphores pretty quickly
and you'll also modify shared data structures at the same time.  this is Bad
News.

the only way around these problems is

    +  make sure your system has lots of semaphores, so that several
       postgres backends can be run interactively at the same time.

    +  give every user not just his own database, but his own POSTGRESHOME,
       with a separate data/ directory.  the reason for this is to keep
       different users' backends from updating one another's pg_variable,
       pg_log, and pg_time relations, which are shared by all databases
       under a given POSTGRESHOME.

best advice is to wait for 3.0 to come out, if you can manage it.

incidentally, 3.0 will (once again) change the system metadata and layout
of user data in relations.  this means that, once again, your existing
databases will not be usable with the new release.  we encourage you to
plan ahead if you want your data to persist -- use the copy command to get
your relations to unix files, so you can copy them back into the new version
when it's released.
					mike olson
					postgres research group
					uc berkeley
					[email-masked]