Upgrade Postgresql to 13.3

chef / supermarket

Chef's community platform

https://supermarket.chef.io/

Apache License 2.0

214 stars 113 forks source link

Upgrade Postgresql to 13.3 #1922

Open tas50 opened 3 years ago

tas50 commented 3 years ago

Context: Supermarket is running on an unsupported postgres version. It needs to be upgraded into 13.3 Acceptance criteria:

[x] Version to be upgraded to 13.3
[x] Docs for upgrade to be created
[x] Upgrade/migration scripts to be created if needed
[x] Real world example for upgrade to be tested with mocked data
[x] New scenarios to test dependancies to be added if needed

RajeshPaul38 commented 2 years ago

There is a vacuumdb util under /opt/supermarket/embedded/bin. This can be used to reduce the size of the database by deleting unnecessary data and speeds up the migration. Syntax below:

/opt/supermarket/embedded/bin/vacuumdb --all --full It's throwing error:

vacuumdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

RajeshPaul38 commented 2 years ago

As discussed with @saghoshprogress below are the steps we need to follow to check to introduce a new feature in supermarket for upgrade.

steps:

build supermarket with postgres 9.3 in one ec2 build server
build supermarket with postgres 13.3 in another ec2 build server
transfer the first build pkg to a different ec2 install server and install
now transfer the 2nd build pkg to the ec2 install server and install
Check if supermarket is working with the latest build.

Once these are done and verified check for supermarket-ctl upgrade which should take care of upgrading from older supermarket version to the latest available version. For dev testing we need to reference the latest version as the local build pkg. This upgrade should take care of deletion of older versions of dependencies. Once all these are verified we need to check for data sanity/persistence.

RajeshPaul38 commented 2 years ago

we were getting error like this:

LOG:  unrecognized configuration parameter "checkpoint_segments" in file "/var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf" line 174

FATAL:  configuration file "/var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf" contains errors

This error is coming because this attribute is not compliant with postgresql 13.3. Solution link below: https://gitlab.com/gitlab-org/gitlab/-/issues/1746

RajeshPaul38 commented 2 years ago

We were able to resolve the postgres conf issue to make it compatible with postgresql 13.3 We were able to properly install new supermarket build that includes postgresql 13.3 in place of the older version of 9.3 Now we need to do the data migration from 9.3 to 13.3. The data directories are as follows:

/var/opt/supermarket/postgresql/9.3/data/
/var/opt/supermarket/postgresql/13.3/data/

We will explore how in chef-server they have done it. They have used a gem chef_backup(https://github.com/chef/chef_backup)