Upgrade to postgresql 16

[raphael0202]

Postgresql 11 is currently used, version 16 brings a lot of improvements.

[raphael0202]

Rational

Many performance improvements were added to recent PostgreSQL versions since PostgreSQL 11 (the one used by Robotoff).

This documentation explains how to perform the upgrade from PostgreSQL 11 to PostgreSQL 16.

Robotoff uses the postgres database, which is not ideal because it’s not robotoff specific (and other services use the same DB name).

We take advantage of the upgrade to also switch DB name to robotoff.

How to

1. Stop the services

Stop services that write to the SQL database: api, workers, scheduler, update-listener:

docker compose stop api update-listener worker_1 worker_2 worker_3 worker_4 scheduler

2. Perform a DB dump

make backup_postgres

The dump produces a dump file in /var/lib/docker/volumes/robotoff_backup/_data/postgres/robotoff_postgres.dump

3. Stop all containers

make down

4. Update the config docker-compose.yml file:

• docker-compose.yml:
  • set postgres:16.3-alpine as postgres image
  • Add the following name to postgres-data volume configuration, in docker-compose.yml and prod.yml:
    ${COMPOSE_PROJECT_NAME:-robotoff}_postgres-data

5. Create a new volume with previous version of PostgreSQL data:

WARNING: docker volume is named postgres-data in staging and production instead of robotoff_postgres-data!

docker volume create --name robotoff_postgres-data-11 && docker run --rm -it -v postgres-data:/from -v robotoff_postgres-data-11:/to alpine ash -c 'cd /from ; cp -av . /to' && docker volume rm postgres-data

6. Restore

Launch the PostgreSQL server (new version):

make up service=postgres

Go within the PostgreSQL container, connected as postgres user:

docker exec -it -u postgres robotoff-postgres-1 bash

Go to the backup directory:

cd /opt/robotoff-backups/postgres/

Create an empty robotoff database (postgres is the only database available at startup):

createdb -T template0 robotoff

Launch psql with psql robotoff and create existing roles:

CREATE ROLE read_only WITH LOGIN PASSWORD 'THEPASSWORD';
GRANT pg_read_all_data TO read_only;

Restore the backup:

pg_restore -c -d robotoff robotoff_postgres.dump -j 15 --if-exists -v

The options used:

• -c : clean the DB before
• --if-exists: ignore “does not exist” errors when cleaning DB at startup
• -d robotoff : connect and restore in the database robotoff
• -v: verbose mode
• -j: restore using parallel workers (15 workers here)

7. Launch the API to make some test:

First, update .env to set POSTGRES_DB=robotoff

make up service=api && make up service=elasticsearch
http :5500/api/v1/health
http :5500/api/v1/insights/detail/000001b2-5d12-44cb-8ddd-fb1639621470

8. Launch all services

make up

9. Post-upgrade

• run ANALYZE VERBOSE to update statistics
• merge upgrade-pg-16 branch and deploy again

[raphael0202]

Done!

[teolemon]

@raphael0202 is it worth putting the upgrade doc in a doc folder ?