Обсуждение: Strategy for upgrade highly used server

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

Hi Monica,

Please, remember to answer to the list or your answer will be only available to me.

You say ...

1 -  we want to have the old version available  just in case newer one gives us any problem.

And also 

2 - This server has problems with free disk space 

And 

3 -  And the database needs to be up almost 24x7 (please describe ALMOST in hours/min/sec and

indicate if it applies to all dbs)

1 and 2 are not compatible for your task. If you can't solve 2 you've only have pg_upgrade, so, you 

will lose old version.

Using pg_upgrade it's possible to have old and new postgres versions running. You only need minor reconfiguration. Of course, this is only possible if the --link option is not used, which is the option that needs less disk space.

I you can solve 2  as  you need old version you must have double the space of PG_DATA.

You have to decide this before building a plan.

Talking about 3, all dba's here this, negotiate.

Your timeline depends on   main_db  (332 GB). You have to test pg_upgrade time for this database

in a similar server, and also test pd_dump/pg_restore.

Testing before any change in production environment is a must, always.

IF databases are not related ... and only in this case you have another option that is install new version

and migrate or upgrade bid database first and then add  databases db1 to db7  (pg_dump/pg_restore) 

one by one to new version (with this you minimize the amount of disk space you need  for pg_upgrade 

and downtime).

I've used this strategy before, when upgrading other servers with databases without the almost-no-downtime needs.

Choices.

A) all with pg_upgrade 

- less space needed

- NO old version

B) pg_dump/pg_restore

- you need double disk space 

- you will have to versions

- more downtime needed (you need to test it no know how much)

C) A combination of both - ONLY for desperates

[ dump databases db1 to db7,  delete databases  db1 to db7 from old version,  pg_upgrade  main_db, pg_restore db1 to db 7 ]

- less space needed

- NO old version

- less downtime for main_db

You have to TEST all plans before.

Good luck!

Thank you for the schematic summary of this options (I' already knew). You've commented anything about replication option though.

--

............................................
Mónica Gamarra Barrios
monica.gamarra@gmail.com

пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez <ekaterina.amez@zunibal.com>:

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.

I would go to 11.6 straight away (11.6 will be available later this week).
By making intermediate stops you'll make total downtime bigger.
 

I intended to have all database servers at the same version level, that's why I have chosen 9.6 (when I began with this task we had v7.4 in one of the servers, and now lower version in our database servers is 9.2).

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.

Before upgrading master DB, we always make sure that we have physical replica, that is up to date.
Then we're free to use pg_upgrade in link mode. In any case, having replica around for the major maintenance activities

is a good thing to do in any case.

For the upgrade, typical procedure is:
- create a new cluster on the upgrade-to version (11)
- open configuration files of new and old cluster side by side and transfer settings.
  Do not overwrite new configuration file, as it typically contains quite some new options.
  By overwriting the config, you will not be able to “see” them.
- transfer pg_hba (and other) settings
- transfer any custom extensions/FTS dictionaries/etc.

- make sure pg_wal points to the right location, if you're using symlinks
- do a schema-only dump of the old cluster and try to load it into the new cluster.
  If fails, correct errors, re-initdb new cluster and try again, till schema loads fine.
- run pg_upgrade in the `--check` mode to make sure no surprises will pop up during the upgrade
  (typical thing to remember — tablescpaces locations)
- perform the upgrade

We've been using this procedure without issues for years.

When you describe the upgrade procedure, do you mean upgrading the master or the slave? Do you promote server as master while upgrading original master? Let people access only to the replica in read-only mode while upgrading the master?

--

Victor Yegorov