running two servers on one machine

On Saturday 28 March 2009 3:27:15 pm Eric Smith wrote:

All,

I'm trying to run two database clusters on a single machine, and am
failing. I use initdb to create the two clusters... each has their
own directory structure. I can start one server or the other with
pg_ctl, but once the first starts, the second just hangs on "waiting
for server to start...".

Are you pointing pg_ctl at the right DATA directory for each cluster using
the -D switch?

The two servers are using different ports. I'm running v 8.3 on a
Mac running 10.5.

What's the secret step I'm missing here?

Thanks,
Eric

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver wrote on 28.03.2009 23:37:

On Saturday 28 March 2009 3:27:15 pm Eric Smith wrote:

All,

I'm trying to run two database clusters on a single machine, and am
failing. I use initdb to create the two clusters... each has their
own directory structure. I can start one server or the other with
pg_ctl, but once the first starts, the second just hangs on "waiting
for server to start...".

Are you pointing pg_ctl at the right DATA directory for each cluster using
the -D switch?

You also have to make sure you assign different listener ports for both servers
in postgresql.conf

Thomas

On Sat, 2009-03-28 at 23:51 +0100, Thomas Kellerer wrote:

You also have to make sure you assign different listener ports for
both servers in postgresql.conf

... or use different unix_socket_directory for each. Make sure that they
are not under /tmp, since /tmp will be cleaned up every 15 days on most
distros :)

Regards,

--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Yes, if I start one, but not the other, the process works just great.
It's only when trying to run both that I have a problem.

Thanks,
Eric

On Mar 28, 2009, at 3:37 PM, Adrian Klaver wrote:

Thomas,

When I start the servers, I use different ports via the -p option. Is
that equivalent to using different listener ports in postgresql.conf?

Thanks,
Eric

On Mar 28, 2009, at 3:51 PM, Thomas Kellerer wrote:

Eric Smith <eric_h_smith@mac.com> writes:

Yes, if I start one, but not the other, the process works just great.
It's only when trying to run both that I have a problem.

Have you looked into the postmaster logfile?

One likely idea is that you don't have shmall cranked up high enough
for two servers, which'd result in something like

FATAL: could not create shared memory segment: Cannot allocate memory

in the postmaster log.

regards, tom lane

On Sunday 29 March 2009 7:59:19 pm Eric Smith wrote:

Thomas,

When I start the servers, I use different ports via the -p option. Is
that equivalent to using different listener ports in postgresql.conf?

Thanks,
Eric

I think you are going to have to show us the exact command you are using. If you
are using postgres -p then that is the port switch. If you are doing pg_ctl -p
then p designates the path to the executable. See below for more details:
http://www.postgresql.org/docs/8.3/interactive/reference-server.html

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:

I think you are going to have to show us the exact command you are using.

What *I* want to see is the postmaster log, which should certainly
contain an error message explaining why it failed to start.

regards, tom lane

That's it!

Log file says "could not create shared memory segment". It also says
that I should change max_connections or shared_buffers.

Error message claims that max_connections is 23, but postgresql.conf
has this listed as 20.
Error message claims that shared_buffers is 300, but postgresql.conf
has this listed as 2400kB.

What the heck is going on here? (no, the lines in the conf file are
not commented out). How do I enforce the conf file entries?

Thanks,
Eric

On Mar 29, 2009, at 8:19 PM, Tom Lane wrote:

Eric Smith <eric_h_smith@mac.com> writes:

Log file says "could not create shared memory segment". It also says
that I should change max_connections or shared_buffers.

Error message claims that max_connections is 23, but postgresql.conf
has this listed as 20.

The autovacuum workers get added on.

Error message claims that shared_buffers is 300, but postgresql.conf
has this listed as 2400kB.

Same thing, different units.

Anyway, you left out the interesting part of the error message,
ie the kernel error code. If it's "Cannot allocate memory" then
you probably need to increase SHMALL.

regards, tom lane

Ahhh! Can't seem to make this go away!

Here is the log file entry:

FATAL: could not create shared memory segment: Cannot allocate memory
DETAIL: Failed system call was shmget(key=5432001, size=4030464,
03600).
HINT: This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space. To reduce the
request size (currently 4030464 bytes), reduce PostgreSQL's
shared_buffers parameter (currently 300) and/or its max_connections
parameter (currently 23).
The PostgreSQL documentation contains more information about shared
memory configuration.

Following the queue on the postgres web page, I created /etc/
sysctl.conf and added the following five entries:

kern.sysv.shmmax=4194304
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=1024

But when I try to start two servers, I get the same error. Should I
be bumping these numbers up?

Eric

On Mar 30, 2009, at 7:18 PM, Tom Lane wrote:

... as a reminder, this is running on mac os 10.5

Eric

On Mar 30, 2009, at 7:53 PM, Eric Smith wrote:

Bingo!

I doubled each of the entries below, and the two servers are now
running together quite happily!

Regards,
Eric

On Mar 30, 2009, at 7:56 PM, Eric Smith wrote:

Eric Smith <eric_h_smith@mac.com> writes:

Following the queue on the postgres web page, I created /etc/
sysctl.conf and added the following five entries:

kern.sysv.shmmax=4194304
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=1024

But when I try to start two servers, I get the same error. Should I
be bumping these numbers up?

Yes, substantially. That's 4MB of shmem per server (SHMMAX) and
also 4MB system-wide (SHMALL, which for some reason is measured in
4KB pages not bytes).

The *minimum* recommendation for decent performance is 32MB per server,
so you'd want shmmax = 32MB and shmall = at least 64MB, maybe more to
allow for some other apps trying to use some shmem.

It appeared from your log dumps that initdb has set up the parameters
to fit into 4MB of shmem, which is what it's supposed to do when faced
with draconian shmmax settings, but you really want to bump up
shared_buffers and perhaps max_connections once you get this fixed.

regards, tom lane