Обсуждение: Multiple postgresql clusters with same version and separate binaries
Multiple postgresql clusters with same version and separate binaries
От
Erika Knihti-Van Driessche
Дата:
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
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...
Regards,
Michael Vitale
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
Friday, January 4, 2019 9:21 AMHi,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
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...
Regards,
Michael Vitale
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
Friday, January 4, 2019 9:21 AMHi,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
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 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
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
On Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <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
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
On Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <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
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.
Re: Multiple postgresql clusters with same version and separatebinaries
От
Erika Knihti-Van Driessche
Дата:
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> wrote:Hi Erika,You can create the different version services and use these services for PostgreSQL start/stop.ex.service postgresql-10 startservice postgresql-9.6 startOn Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <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
Re: Multiple postgresql clusters with same version and separatebinaries
От
Erika Knihti-Van Driessche
Дата:
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> wrote:Hi Erika,You can create the different version services and use these services for PostgreSQL start/stop.ex.service postgresql-10 startservice postgresql-9.6 startOn Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <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
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
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,ErikaOn 4 Jan 2019, at 15:50, Shreeyansh Dba <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 startservice postgresql-9.6 startOn Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <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.
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.
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.
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.
Friday, January 4, 2019 10:53 AMDon’t need separate binaries for each cluster; only separate binaries for each version needed; i.e. 9.6.1, 9.6.2, etc.Friday, January 4, 2019 9:32 AMTo 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
Friday, January 4, 2019 9:21 AMHi,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
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.
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.
Friday, January 4, 2019 10:53 AMDon’t need separate binaries for each cluster; only separate binaries for each version needed; i.e. 9.6.1, 9.6.2, etc.Friday, January 4, 2019 9:32 AMTo 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
Friday, January 4, 2019 9:21 AMHi,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
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
Re: Multiple postgresql clusters with same version and separate binaries
От
Erika Knihti-Van Driessche
Дата:
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 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
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
Re: Multiple postgresql clusters with same version and separate binaries
От
Erika Knihti-Van Driessche
Дата:
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:
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