Thread: Second postmaster / second DB cluster

Second postmaster / second DB cluster

From
Oli Sennhauser
Date:
Hello Admins

I would like to start a second postmaster on my server.
First problem was the lock file /tmp/.s.PGSQL.5432.lock
and its socket. But you can workaround that by the -k
parameter. So I was able to start at least 3 clusters...

But then I run into the problem with the psql-Client. It
was not able to connect anymore. Finally I was able to
isolate the problem on the -k parameter as the commands
below show. But without a propper running -k I do not
see a proper way to solve the problem.

Could someone please explain me, why

1. the lock file is located in /tmp and not e.g. in the
    preseen directory /var/lock or like ORACLE does in a
    directory called $PG_BASE/data/CLUSTER1 ?
    In my opinion /tmp is to dangerous for such a
    important file?

2. how do I properly start a second postmaster?

I found many comments like

  * Removing the lock file (igitigit!)
  * "i don't know why you would like to run multiple postmaster"
    --> In a perfect world there is may be no reason?

but now solutions.

Thanks for helping
Oli

Examples

nohup postmaster -D /u00/app/pgsql/data/CLUSTER1 \
                  >>/u00/app/pgsql/data/CLUSTER1/CLUSTER1.log 2>&1 &
--> OK

nohup postmaster -D /u00/app/pgsql/data/CLUSTER1 \
                  -p 5432 \
                  >>/u00/app/pgsql/data/CLUSTER1/CLUSTER1.log 2>&1 &
--> OK

nohup postmaster -D /u00/app/pgsql/data/CLUSTER1 \
                  -p 5433 \
                  >>/u00/app/pgsql/data/CLUSTER1/CLUSTER1.log 2>&1 &
--> OK

nohup postmaster -D /u00/app/pgsql/data/CLUSTER1 \
                  -p 5432 \
                  -k /u00/app/pgsql/data/CLUSTER1 \
                  >>/u00/app/pgsql/data/CLUSTER1/CLUSTER1.log 2>&1 &
--> ERR
postgres@master:~/data/CLUSTER1> psql -l
psql: could not connect to server: No such file or directory
         Is the server running locally and accepting
         connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster
Telefon 01 940 24 82 oder 079 450 49 14


Re: Second postmaster / second DB cluster

From
Stephan Szabo
Date:
On Thu, 16 Oct 2003, Oli Sennhauser wrote:

> I would like to start a second postmaster on my server.
> First problem was the lock file /tmp/.s.PGSQL.5432.lock
> and its socket. But you can workaround that by the -k
> parameter. So I was able to start at least 3 clusters...

If you want to use separate directories for the socket, you
need to use -h <directory>  (and -p <port> if it's not the default)
when connecting with psql.  However, you probably just want to
use different port numbers to start the postmasters and then use
psql -p <port> to connect.

Solution: Second postmaster / second DB cluster

From
Oli Sennhauser
Date:
Stephan Szabo wrote:
> On Thu, 16 Oct 2003, Oli Sennhauser wrote:
>
>
>>I would like to start a second postmaster on my server.
>>First problem was the lock file /tmp/.s.PGSQL.5432.lock
>>and its socket. But you can workaround that by the -k
>>parameter. So I was able to start at least 3 clusters...
>
>
> If you want to use separate directories for the socket, you
> need to use -h <directory>  (and -p <port> if it's not the default)
> when connecting with psql.  However, you probably just want to
> use different port numbers to start the postmasters and then use
> psql -p <port> to connect.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

 >>Thank you for answering.
 >>>
 >>> Do you mean -k <directory> (or -h <hostname>)? -k <directory> is
exactly
 >>> what I did. And it did not work in my case. In my opinion we should not
 >>> need -i and -h in this example, because I only want to do local
connects.
 >
 >psql -h /u00/app/pgsql/data/CLUSTER1
 >means use a local socket located in that directory (with the
 >"port" number of 5432 - which is used in determing the filename).
 >
 >>From the 7.3 psql manpage:
 >-h hostname
 >--host hostname
 >Specifies the host name of the machine on which the postmaster is
 >running. If host begins with a slash, it is used as the directory for >the
 >Unix-domain socket.

Ok, now I understand it! Thank you verry much Stephan

Here is my final solution:

$ CLUSTER1
$ pg_ctl start -D $PG_DATA -l $PG_DATA/$PG_CID.log -o "-k $PG_DATA
          -p $PG_PORT"
postmaster successfully started

$ CLUSTER2
$ pg_ctl start -D $PG_DATA -l $PG_DATA/$PG_CID.log -o "-k $PG_DATA
          -p $PG_PORT"
postmaster successfully started

$ CLUSTER3
$ pg_ctl start -D $PG_DATA -l $PG_DATA/$PG_CID.log -o "-k $PG_DATA
          -p $PG_PORT"
postmaster successfully started

$ ps -fu postgres
UID        PID  PPID  C STIME TTY        TIME CMD
postgres   402     1  0 10:29 pts/1  00:00:00
/u00/app/pgsql/products/7.4.0/bin/postmaster -k
/u00/app/pgsql/data/CLUSTER1 -p 5
postgres   404   402  0 10:29 pts/1  00:00:00 postgres: stats buffer process
postgres   406   404  0 10:29 pts/1  00:00:00 postgres: stats collector
process
postgres   436     1  0 10:29 pts/1  00:00:00
/u00/app/pgsql/products/7.4.0/bin/postmaster -k
/u00/app/pgsql/data/CLUSTER2 -p 5
postgres   438   436  0 10:29 pts/1  00:00:00 postgres: stats buffer process
postgres   439   438  0 10:29 pts/1  00:00:00 postgres: stats collector
process
postgres   469     1  0 10:29 pts/1  00:00:00
/u00/app/pgsql/products/7.3.3/bin/postmaster -k
/u00/app/pgsql/data/CLUSTER3 -p 5
postgres   471   469  0 10:29 pts/1  00:00:00 postgres: stats buffer process
postgres   473   471  0 10:29 pts/1  00:00:00 postgres: stats collector
process

$ netstat -ap | grep PGSQL
unix  2  [ ACC ]  STREAM  LISTENING  287991 402/postmaster
/u00/app/pgsql/data/CLUSTER1/.s.PGSQL.5432
unix  2  [ ACC ]  STREAM  LISTENING  288022 436/postmaster
/u00/app/pgsql/data/CLUSTER2/.s.PGSQL.5434
unix  2  [ ACC ]  STREAM  LISTENING  288051 469/postmaster
/u00/app/pgsql/data/CLUSTER3/.s.PGSQL.5436

$ CLUSTER1
$ psql -l -h $PG_DATA
         List of databases
    Name    |  Owner   | Encoding
-----------+----------+-----------
  T740A     | postgres | SQL_ASCII
  T740B     | postgres | SQL_ASCII
  template0 | postgres | SQL_ASCII
  template1 | postgres | SQL_ASCII

$ CLUSTER2
$ psql -l -h $PG_DATA
         List of databases
    Name    |  Owner   | Encoding
-----------+----------+-----------
  template0 | postgres | SQL_ASCII
  template1 | postgres | SQL_ASCII

$ CLUSTER3
$ psql -l -h $PG_DATA
         List of databases
    Name    |  Owner   | Encoding
-----------+----------+-----------
  template0 | postgres | SQL_ASCII
  template1 | postgres | SQL_ASCII