Strategy for upgrade highly used server

From: Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Strategy for upgrade highly used server
Date: 2019-11-11 09:12:48
Message-ID: 5ce6203a-78cb-25b0-3b91-65179aa280e0@zunibal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I'm trying to upgrade all postgres servers at my work place. I've began
with oldest versions moving them to newer ones, basically from 8.4/9.2
to 9.6. When all are in 9.6, I'll go with v10 and after that... we'll see.

One of the servers to be upgraded has special needs and I'd like your
advice about how to upgrade it with the least downtime possible (and
less chance to make a mistake I would add). This server has problems
with free disk space (don't know details but it seems quite difficult to
add more disks). And the database needs to be up almost 24x7. We can
stop it if we really need it, of course, but if the stop is long then
we'll have undesirable side effects. The server has v9.2 installed:

postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

The cluster has these databases:

postgres=# select pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) as size,
pg_database_size(pg_database.datname) FROM pg_database order by 3 desc;
           datname            |  size   | pg_database_size
------------------------------+---------+------------------
 main_db                       | 332 GB  | 356418016376
 db1                                | 8078 MB | 8470254712
 db2                               | 3279 MB | 3438187640
 db3                                | 2658 MB | 2786694264
 db4 (maybe this can be deleted) | 321 MB  | 336548984
 db5                                | 175 MB  | 183596152
 db6                               | 10 MB   | 10974328
 db7                             | 6493 kB | 6648952
 postgres                     | 6493 kB | 6648952
 template1                    | 6493 kB | 6648952
 template0                    | 6377 kB | 6529540

We are going to free some space in main_db moving old data to another
server (~90GB) and changing the app that uses it but this db grows quite
fast and we'll be probably in the same size in a few months.

I know that using --link would make pg_upgrade much faster but by now
we're not using it because we want to have the old version available
just in case newer one gives us any problem.

I've read about replicas to make upgrade with almost no downtime, but
they look like a bit complex to get them running (and I'm not sure if we
have a server to be used as slave). I'm familiar with them in Sql Server
and Oracle, but I've been working with Postgres for a few months only
and I'm still "learning".

Two options I've mentioned are the only options that we have to
pg_upgrade fast or is there any other option?

Regards,

Ekaterina

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Victor Yegorov 2019-11-11 14:24:26 Re: Strategy for upgrade highly used server
Previous Message Peter Eisentraut 2019-11-09 09:19:29 Re: How to change the TLS certificate/key without restarting the server?