Multiple postgresql clusters with same version and separate binaries

To put it simply: you cannot run different major versions of PostgreSQL
with the same binaries. 3 things need to be separate. You named 2 of
them: data directory and port. The 3rd one is separate binary locations
for each PG cluster instance running on the same host.

What I do is create a separate .profile_pgsql file for each PG cluster
instance. This file contains all the stuff necessary to distinguish
between multiple PG clusters. Here is an example for a specific Linux
distro...

export VERSION=9.6
export PGROOT=/usr/pgsql-$VERSION
export PGBASE=/var/lib/pgsql/$VERSION
export PATH=$PGROOT/bin:$PGROOT/share:$PATH
export PGLIB=$PGROOT/lib
export MANPATH=$PGROOT/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGROOT/bin:$PGBASE/share:$PATH
export PGDATA=$PGBASE/data
export PGPASSFILE=~/.pgpass
export PGUSER=postgres
export PGDATABASE=postgres
export PGPORT=5432

Regards,
Michael Vitale

On 1/4/19 6:21 AM, Erika Knihti-Van Driessche wrote:

Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same binaries
though, and this is now giving me some headache.. I cannot shutdown all
clusters at the same time, so upgrading them is quite impossible.

What OS(version)?

How did you install Postgres?

I know that running several versions of postgres is possible on one
machine - I suppose this would also work out with same version running
on different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10..
etc..? Has anyone any experience with this kind of problem?

All help and ideas appreciated! :-)

Thanks,
Erika

--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/01/2019 14:21, Erika Knihti-Van Driessche wrote:

Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same binaries
though, and this is now giving me some headache.. I cannot shutdown all
clusters at the same time, so upgrading them is quite impossible.

I know that running several versions of postgres is possible on one
machine - I suppose this would also work out with same version running
on different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10..
etc..? Has anyone any experience with this kind of problem?

What OS are you running? The Debian packages from apt.postgresql.org
allows multiple versions to co-exist on the same machine (though
naturally on different ports), and multiple clusters within each version
(again on different ports).

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Hi Erika,

You can create the different version services and use these services for
PostgreSQL start/stop.
ex.
service postgresql-10 start
service postgresql-9.6 start

Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com

On Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <
erika.knihti@gmail.com> wrote:

On 1/4/19 8:21 AM, Erika Knihti-Van Driessche wrote:

Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same binaries
though, and this is now giving me some headache.. I cannot shutdown all
clusters at the same time, so upgrading them is quite impossible.

I know that running several versions of postgres is possible on one
machine - I suppose this would also work out with same version running on
different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10.. etc..?
Has anyone any experience with this kind of problem?

(Please do not cross-post.)

Since they're all the same major version (9.6), you'll need to build from
source and then install the binaries to different locations.

--
Angular momentum makes the world go 'round.

Hi,

Thanks all for your replies and help! I already thought that it’ll be possible to just install binaries in different locations. My current installation I have all done using one binary location and initdb.. not a very good solution on hindsight.. Oh, and I’m on RHEL 7. I used the installation packages from EDB for Postgres. Previously I did do a minor upgrade - shutdown all clusters and upgrade, then startup all.. but now they start doing difficult with downtime for all applications at the same time, so with next upgrade, I’ll separate all binaries.

Cheers,
Erika

On 1/4/19 7:30 AM, Erika Knihti-Van Driessche wrote:

Hi,

Thanks all for your replies and help! I already thought that it’ll be
possible to just install binaries in different locations. My current
installation I have all done using one binary location and initdb.. not
a very good solution on hindsight.. Oh, and I’m on RHEL 7. I used the
installation packages from EDB for Postgres. Previously I did do a minor
upgrade - shutdown all clusters and upgrade, then startup all.. but now
they start doing difficult with downtime for all applications at the
same time, so with next upgrade, I’ll separate all binaries.

https://www.enterprisedb.com/docs/en/11.0/EPAS_11_Inst_Linux/EDB_Postgres_Advanced_Server_Installation_Guide_Linux.1.16.html#pID0E0SH0HA

--
Adrian Klaver
adrian.klaver@aklaver.com

If uptime is a priority, then unless they're small, you should think about
separating them onto separate servers.  And implementing replication for DR.

On 1/4/19 9:30 AM, Erika Knihti-Van Driessche wrote:

Hi,

Thanks all for your replies and help! I already thought that it’ll be
possible to just install binaries in different locations. My current
installation I have all done using one binary location and initdb.. not a
very good solution on hindsight.. Oh, and I’m on RHEL 7. I used the
installation packages from EDB for Postgres. Previously I did do a minor
upgrade - shutdown all clusters and upgrade, then startup all.. but now
they start doing difficult with downtime for all applications at the same
time, so with next upgrade, I’ll separate all binaries.

Cheers,
Erika

On 4 Jan 2019, at 15:50, Shreeyansh Dba <shreeyansh2014@gmail.com
<mailto:shreeyansh2014@gmail.com>> wrote:

Hi Erika,

You can create the different version services and use these services for
PostgreSQL start/stop.
ex.
service postgresql-10 start
service postgresql-9.6 start

Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com <http://www.shreeyansh.com/&gt;

On Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche
<erika.knihti@gmail.com <mailto:erika.knihti@gmail.com>> wrote:

Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same
binaries though, and this is now giving me some headache.. I cannot
shutdown all clusters at the same time, so upgrading them is quite
impossible.

I know that running several versions of postgres is possible on one
machine - I suppose this would also work out with same version
running on different locations, like /db1/PostgreSQL/10,
/db2/PostgreSQL/10.. etc..? Has anyone any experience with this kind
of problem?

All help and ideas appreciated! :-)

Thanks,
Erika

--
Angular momentum makes the world go 'round.

On Jan 4, 2019, at 9:32 AM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

The 3rd one is separate binary locations for each PG cluster instance running on the same host.

Don’t need separate binaries for each cluster; only separate binaries for each version needed; i.e. 9.6.1, 9.6.2, etc.

I did say you need to run with different binaries for different versions:

To put it simply: you cannot run different major versions of PostgreSQL
with the same binaries.

So when I subsequently said the following it was in that context.

The 3rd one is separate binary locations for each PG cluster instance
running on the same host.

Le ven. 4 janv., vers 16:30, Erika Knihti-Van Driessche exprimait :

Hi,

Hey !

Thanks all for your replies and help! I already thought that it’ll
be possible to just install binaries in different locations.

It's not necessary, you "only" have to separate services, one
thing that manages the services files in the Debian packages. It also
manages clusters init and stop/start/etc globally for services named :

- postgresql@10-cluster1
- postgresql@10-cluster2
- ...

Oh, and I’m on RHEL 7. [...] Previously I did do a minor upgrade -
shutdown all clusters and upgrade, then startup all.. but now they
start doing difficult with downtime for all applications at the same
time, so with next upgrade, I’ll separate all binaries.

Please don't, you'll regret it when you'll have to upgrade as you'll have
to uprade all your binaries. You should create separated services
files and a global one.

Regards,
--
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ stephane@hexack.fr
 +33 6 64 31 72 52

Hi,

I'm sure I'd regret creationg all the extra work for myself, but.. the
customer insists that not all (at least production) applications may go
down at the same time.
I have streaming replication in place also, and I think for minor upgrade
it won't be a problem to first upgrade standby, resync replication, then
failover primary to newer minor version standby (altho I did read that this
is not supported but will likely work..). For major upgrade this isn't an
option though, or I would have to implement Slony (I think)..

In any case, currently I have 17 very small clusters running on an acc
env, half of them are in fact for dev and other half for acc with streaming
replication. It all started with one cluster of course, but during the
years it has got a bit out of hand. Applications are all on separate
clusters, because I have to be able to stop them separately. I have always,
without thinking further, used initdb from that one set of binaries, to
create a new cluster. If I'd separate the binaries, then I'd also have to
indeed create separate profiles to make sure I use correct set of binaries
for each cluster.. which will make it all quite complicated..

So, just creating separate services isn't really an option, because it
won't help me with the upgrades.

Cheers,
Erika

On Mon, 7 Jan 2019 at 15:36, Stéphane KANSCHINE <stephane@hexack.fr> wrote:

Le mar. 8 janv., vers 08:13, Erika Knihti-Van Driessche exprimait :

Hi,

Hey !

I'm sure I'd regret creationg all the extra work for myself, but.. the
customer insists that not all (at least production) applications may go
down at the same time.

That's precisely the purpose of services or init script if you prefer.

For a customer, i manage 10 clusters x 5 environments, entirely with 1
service per cluster and 1 binary per environment. I also use ansible
to deploy and maintain streaming replication, logical migration,
monitoring, all based on services. It's easy with Debian packages,
because service creation is included.

I have streaming replication in place also, and I think for minor upgrade
it won't be a problem to first upgrade standby, resync replication, then
failover primary to newer minor version standby (altho I did read that this
is not supported but will likely work..).

It'll work.

For major upgrade this isn't an option though, or I would have to
implement Slony (I think)..

Why don't you use logical replication ? I recently migrate the 10x5
clusters from 9.6 to 10 with pglogical in 2 days of work. It was also
from Red Hat to Debian and i automate cluster per cluster with
services. Next step is to migrate from 10 to 11, also with logical
migration, by hand this time, because pglogical don't do so much and
due to the lack of documentation, i already wrote the missing parts.

So, just creating separate services isn't really an option, because
it won't help me with the upgrades.

It does as the major version is in the service name (i.e.
postgresql@<version>-<clustername>), you won't have to update it once
it's written.

Regards,
--
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ stephane@hexack.fr
 +33 6 64 31 72 52

Thanks for your comments Stéphane!

As a matter of fact, I'm just reading about the pglogical, and that might
be a likely solution :-)
We also use ansible for automated failover, it works nicely.

Thanks,
Erika

On Tue, 8 Jan 2019 at 09:50, Stéphane KANSCHINE <stephane@hexack.fr> wrote: