Postgres Pro
Downloads
Home   >   mailing lists

Thread: Upgrading cluster with thousands of DBs

Upgrading cluster with thousands of DBs

From
Alejandro Martínez
Date:
Hi list,

We're working on upgrading from 9.1 to 9.3 some of our database
clusters, while seeking to minimize (write) downtime by making the
upgrades as fast as possible.

However, our clusters have the particularity that they get to host
many different *databases*, as in, ~2000 databases per cluster. Most
of those databases are small (only a table and PostGIS functions), so
it's more a problem of the number of DBs than the space they use. The
total size of the cluster is around 60gb.

I first started using pg_upgrade but, even when enabling
parallelization and hardlinks, as most of the steps (preliminary
check?) don't seem to be parallelized, the upgrade takes ~5 hours as
it goes database-per-database serially on most steps.

I'm experimenting with reducing the time by spawning parallel pg_dump
and pg_restore instances, so far I've reached a 3:15h mark that I
still think can reduce a bit more with some tweaking.

Does anyone have any tips on reducing the time of upgrading such a
kind of a cluster?


Thanks!

Re: Upgrading cluster with thousands of DBs

From
Gabriele Bartolini
Date:
 Hi,

 On Tue, 14 Jan 2014 11:45:33 +0100, Alejandro Martínez <zen@itram.es>
 wrote:
> However, our clusters have the particularity that they get to host
> many different *databases*, as in, ~2000 databases per cluster. Most
> of those databases are small (only a table and PostGIS functions), so
> it's more a problem of the number of DBs than the space they use. The
> total size of the cluster is around 60gb.

 If this is your case, I'd suggest an "old-school" approach and migrate
 one database at a time on a new server with Postgres 9.3.

 Cheers,
 Gabriele
--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

Re: Upgrading cluster with thousands of DBs

From
Bruce Momjian
Date:
On Tue, Jan 14, 2014 at 11:45:33AM +0100, Alejandro Martínez wrote:
> Hi list,
>
> We're working on upgrading from 9.1 to 9.3 some of our database
> clusters, while seeking to minimize (write) downtime by making the
> upgrades as fast as possible.
>
> However, our clusters have the particularity that they get to host
> many different *databases*, as in, ~2000 databases per cluster. Most
> of those databases are small (only a table and PostGIS functions), so
> it's more a problem of the number of DBs than the space they use. The
> total size of the cluster is around 60gb.
>
> I first started using pg_upgrade but, even when enabling
> parallelization and hardlinks, as most of the steps (preliminary
> check?) don't seem to be parallelized, the upgrade takes ~5 hours as
> it goes database-per-database serially on most steps.

That is surprising.  I know I see parallel hard link creation and
parallel database dump in my testing of 9.3 pg_upgrade.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +