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