Project

General

Profile

Actions

Task #4382

closed

Upgrade PostgreSQL to version 11

Added by Jan Mach almost 4 years ago. Updated almost 4 years ago.

Status:
Closed
Priority:
High
Assignee:
Category:
Installation
Target version:
Start date:
10/18/2018
Due date:
% Done:

100%

Estimated time:
To be discussed:

Description

Upgrade PostgreSQL database to latest version 11.

Start with upgrading the mentat-dev, then mentat-alt, and when the process is tested and documented perform migration on mentat-hub server.

Actions #1

Updated by Jan Mach almost 4 years ago

  • Priority changed from Normal to High

For testing purposes please upgrade PostgreSQL to v11 on mentat-alt server first.

Actions #2

Updated by Radko Krkoš almost 4 years ago

This should be postponed a bit. Debian package tracker lists PostgreSQL 11 as what I gather is not ready for upgrade due to dependency issues. I advise to test the upgrade manually on mentat-dev but right now it is busy with #4383 and interrupting this would make comparing gathering the results there harder (first I waited for this to be resolved but it seems to linger hence the other course as plotted above).

Actions #3

Updated by Radko Krkoš almost 4 years ago

Everything seems to be working OK on mentat-dev after upgrade using the following (official) procedure:

$ sudo systemctl stop postgresql

$ sudo apt-get update

$ sudo apt-get install postgresql-11 postgresql-11-ip4r

$ sudo pg_lsclusters                                                                                                                                                                          
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

$ sudo systemctl stop postgresql

$ sudo pg_dropcluster 11 main

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

$ sudo pg_upgradecluster --method=upgrade 10 main

$ sudo pg_dropcluster --stop 10 main

$ sudo apt-get remove --purge postgresql-10 postgresql-client-10

$ sudo systemctl start postgresql

psql:
VACUUM VERBOSE;
ANALYZE VERBOSE:
Only outstanding issues are:
  1. New comments and options are not added to the postgresql.conf (Oh how I miss the Slackware's diff and merge approach to config file upgrade);
  2. What about the postgresql-server-dev-10 package? Any idea what it was used for? It does not seem to be required.

Debian devs are still trying to resolve some issues with llvm and/or postgresql-common update, neither is tremendously important to us.

I am happy with the outcome, requesting green light to go for mentat-alt (Downtime required).

Actions #4

Updated by Jan Mach almost 4 years ago

Radko Krkoš wrote:

Everything seems to be working OK on mentat-dev after upgrade using the following (official) procedure:

[...]

Only outstanding issues are:
  1. New comments and options are not added to the postgresql.conf (Oh how I miss the Slackware's diff and merge approach to config file upgrade);
  2. What about the postgresql-server-dev-10 package? Any idea what it was used for? It does not seem to be required.

This is a requirement for compiling the python psycopg2 library. So sadly it needs to stay. Or to put it more correctly, the appropriate package for v11 needs to be installed as well.

Debian devs are still trying to resolve some issues with llvm and/or postgresql-common update, neither is tremendously important to us.

I am happy with the outcome, requesting green light to go for mentat-alt (Downtime required).

Perfect, but please wait until Wednesday 31. I will be at Prague premises on Monday and Tuesday and will need working environment to demonstrate current work in progress. But on Wednesday 31 you may proceed, outstanding work.

Actions #5

Updated by Radko Krkoš almost 4 years ago

Jan Mach wrote:

Radko Krkoš wrote:

  1. What about the postgresql-server-dev-10 package? Any idea what it was used for? It does not seem to be required.

This is a requirement for compiling the python psycopg2 library. So sadly it needs to stay. Or to put it more correctly, the appropriate package for v11 needs to be installed as well.

I expected some such thing. Good to know, I will fix mentat-dev and alter the procedure.

Perfect, but please wait until Wednesday 31. I will be at Prague premises on Monday and Tuesday and will need working environment to demonstrate current work in progress. But on Wednesday 31 you may proceed, outstanding work.

OK, postponing the upgrade on mentat-alt until next week.

Actions #6

Updated by Jan Mach almost 4 years ago

  • Target version changed from 2.2 to 2.3
Actions #7

Updated by Radko Krkoš almost 4 years ago

  • % Done changed from 0 to 50

The PostgreSQL upgrade was done on mentat-alt successfully using the slightly modified procedure (accounting for the dev package). Basic tests show that the database interaction works as expected. As part of the upgrade a CLUSTER of the events table was performed (resulting in reduced table/indices size).

Actions #8

Updated by Radko Krkoš almost 4 years ago

  • % Done changed from 50 to 90

The resulting procedure steps are as follows:

All as root:

# Stop all processes touching the DB
systemctl stop apache2
mentat-controller.py --command stop
mentat-controller.py --command disable
systemctl stop postgresql

# Install PostgreSQL 11 & friends
apt-get update
apt-get install postgresql-11 postgresql-11-ip4r postgresql-server-dev-11

# Verify the installation success (output included)
pg_lsclusters
:Ver Cluster Port Status Owner    Data directory              Log file
:10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
:11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

# PostgreSQL was started during installation, stop it again
systemctl stop postgresql

# Drop the default PostgreSQL 11 cluster created during installation
pg_dropcluster 11 main

# Verify the clusters (output included)
pg_lsclusters
:Ver Cluster Port Status Owner    Data directory              Log file
:10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

# Perform the data migration (slow to complete)
pg_upgradecluster --method=upgrade 10 main

# Drop the PostgreSQL 10 data as there are two copies (10+11)
pg_dropcluster --stop 10 main

# Remove the old PostgreSQL version & friends
apt-get remove --purge postgresql-10 postgresql-client-10 postgresql-server-dev-10

# Start the DB (maintenance still required, not ready for system uptime)
systemctl start postgresql

# From the PostgreSQL shell (psql):
# The CLUSTER is optional, it takes time but can shrink the DB size considerably if not done recently
VACUUM VERBOSE;
-- CLUSTER VERBOSE events;
ANALYZE VERBOSE;

# This is a good time for restart (optional). New kernel? Long uptime & non-ECC RAM?

# Now the system is ready for production, start it up
mentat-controller.py --command enable
systemctl start apache2
mentat-controller.py --command start

This is for hands-on upgrade. For automated variant (Ansible?) the verification steps should be skipped.

How to perform the upgrade on mentat-hub? Ansible? Manual?

Actions #9

Updated by Radko Krkoš almost 4 years ago

Upgraded to PostgreSQL 11.1 on mentat-dev and mentat-alt. The update contained some security fixes which should not affect us much and also a set of other fixes, nothing too important. The developers recommended an ASAP update nevertheless, so we complied.
No change is required for the update procedure.

Actions #10

Updated by Jan Mach almost 4 years ago

Radko Krkoš wrote:

The resulting procedure steps are as follows:

All as root:
[...]

This is for hands-on upgrade. For automated variant (Ansible?) the verification steps should be skipped.

How to perform the upgrade on mentat-hub? Ansible? Manual?

Hi Radko, I would like to plan the database upgrade on Friday the 16th of November, if that is OK with you. Also I would like to ask you to do it, because I do not want any hiccups on the production server. I think, that the downtime beginning on Friday morning will have the smallest possible impact on the users. Please reboot the whole server as mentioned in the upgrade checklist, so that the new kernel gets loaded and disks checked. Great work on this issue so far, thank you very much.

Actions #11

Updated by Jan Mach almost 4 years ago

To Radko: I have just added your database update procedure checklist to the upgrade section of the documentation. I have created the commit with you as an author, so you can get the credit for coming up with that procedure. I have basically just pasted the checklist as you have created it to appropriate documentation file.

Actions #12

Updated by Radko Krkoš almost 4 years ago

  • Status changed from New to Feedback

OK, the upgrade is planned for Friday morning, I will take care of that.
What about informing the users about the down-time? Has the relevant information been sent to them? I believe I am not a member of any Mentat related mailing lists. Any advice on how to take care of informing the users?

Actions #13

Updated by Radko Krkoš almost 4 years ago

  • Status changed from Feedback to Resolved
  • Assignee changed from Radko Krkoš to Jan Mach
  • % Done changed from 90 to 100

The upgrade was performed on mentat-hub successfully. Some unexpected problems not related to the database were encountered. Separate issues will be opened for those. Marking resolved.

Actions #14

Updated by Jan Mach almost 4 years ago

  • Status changed from Resolved to Closed

Perfect work, I have just checked the whole system and everything is working and in order. Thank you. Closing this issue.

Actions

Also available in: Atom PDF