Skip to main content

Useful Database Commands

Changing the Version Control System URL​

This might be useful when the version control system changed its base-url but all repositories are still present there.

update participation
set repository_url = replace(repository_url, 'some.old.domain.com', 'your.new.vcs.domain')
where repository_url is not null;

Migrating MySQL Data to PostgreSQL​

Planning the maintenance window​

The whole procedure is dominated by step 1, the Liquibase migration, and within it by the changesets that rewrite the largest tables. Everything after it is comparatively quick: transferring the data and importing the merged dump scale with the size of the database, but do not depend on how much history it holds.

Two figures are worth measuring on a copy of the database before the window, because they set the length of the longest step:

SELECT COUNT(*) FROM feedback WHERE test_case_id IS NOT NULL;
SELECT COUNT(*) FROM result WHERE submission_id IS NULL;

The first drives a single unbatched DELETE that cannot be resumed part way. On a developer machine it clears roughly 20,000 rows per second, so a database with tens of millions of matching rows should be expected to spend a good part of an hour in that one statement. It runs as one transaction, so an interruption rolls all of it back and the rollback takes time of its own.

Steps​

  1. Start Artemis at least once in version V ≧ 6.0.0 or greater to make sure the current database schema is PostgreSQL-compatible.

  1. Stop Artemis.

  2. Create a database backup using mysqldump --databases Artemis > Artemis.sql. This dump is called Artemis.sql in the following steps.

  1. Copy the docker-compose.yml file into the same directory as the Artemis.sql database dump and run the following commands to convert the Artemis.sql dump into Artemis.pg.sql that is usable by PostgreSQL.

docker-compose.yml with helper containers for MySQL and PostgreSQL:

services:
    mysql:
        image: docker.io/library/mysql:8.0.46
        environment:
            - MYSQL_DATABASE=Artemis
            - MYSQL_ALLOW_EMPTY_PASSWORD=yes
        # Bind to localhost only: this helper runs with an empty root password and TLS disabled,
        # so it must never be reachable from outside the host.
        ports:
            - 127.0.0.1:3306:3306
        command: >
            mysqld
                --lower_case_table_names=1 --skip-ssl
                --character_set_server=utf8mb4
                --collation-server=utf8mb4_unicode_ci
                --explicit_defaults_for_timestamp
                --default-authentication-plugin=mysql_native_password
                --skip-log-bin
                --innodb_buffer_pool_size=8G
                --innodb_redo_log_capacity=4G
                --innodb_flush_log_at_trx_commit=2
                --max_allowed_packet=1G
        volumes:
            - migration-mysql-data:/var/lib/mysql
        networks:
            - db-migration

    postgres:
        image: docker.io/library/postgres:18.6-alpine
        environment:
            - POSTGRES_USER=root
            - POSTGRES_DB=Artemis
            - POSTGRES_HOST_AUTH_METHOD=trust
            - TZ=UTC
            - PGTZ=UTC
        # Bind to localhost only: POSTGRES_HOST_AUTH_METHOD=trust accepts any connection without
        # a password, so this must never be reachable from outside the host.
        ports:
            - 127.0.0.1:5432:5432
        command: >
            postgres
                -c shared_buffers=4GB
                -c maintenance_work_mem=2GB
                -c max_wal_size=8GB
                -c synchronous_commit=off
        volumes:
            - migration-postgres-data:/var/lib/postgresql
        networks:
            - db-migration

volumes:
    migration-mysql-data:
    migration-postgres-data:

networks:
    db-migration:
        driver: 'bridge'
        name: artemis-db-migration

Commands to transform the MySQL dump into a PostgreSQL one:

   #! /usr/bin/env bash

   # start the temporary MySQL and Postgres containers
   docker compose up -d

   # import database dump into MySQL
   docker compose exec -T mysql mysql < Artemis.sql

   # use pgloader to transfer data from MySQL to Postgres
   docker run --rm --platform linux/amd64 --network=artemis-db-migration \
       -v "$PWD/pgloader.load:/tmp/pgloader.load:ro" \
       docker.io/dimitri/pgloader pgloader /tmp/pgloader.load

   # dump the Postgres data in a format that can be imported in the actual database
   docker compose exec -T postgres pg_dump -Ox Artemis > Artemis.pg.sql

   # clean up
   docker compose down

pgloader.load, in the same directory:

   LOAD DATABASE
        FROM     mysql://root@mysql/Artemis
        INTO postgresql://root@postgres/Artemis

    WITH include drop, create tables, create indexes, reset sequences, foreign keys,
         workers = 2, concurrency = 1,
         prefetch rows = 500, batch rows = 500, batch size = 8MB

    SET maintenance_work_mem to '512MB', work_mem to '48MB';
  1. Update the Artemis config to connect to an empty new PostgreSQL database (see Connecting Artemis to PostgreSQL). Start Artemis, wait until it has finished starting up and created the schema, and stop it again.
  1. Dump the schema Artemis has created on the PostgreSQL server in the previous step using
   pg_dump -Ox Artemis > empty.pg.sql
  1. Now the database schema as created by Artemis (empty.pg.sql) and the one containing the actual data migrated from MySQL (Artemis.pg.sql) need to be merged.

Use the following script like python3 ./merge.py > merged.pg.sql to create the merged database dump.

merge.py database dump merge script:

   #! /usr/bin/env python3

   """
   Merges two database dumps
   - empty.pg.sql
   - Artemis.pg.sql
   created from an Artemis database where `empty.pg.sql` contains a fresh DB
   schema as created by the first start of Artemis from a new database, and
   `Artemis.pg.sql` is a dump from an Artemis database that was converted from
   MySQL to PostgreSQL using pgloader.

   It is merged so that the schema definitions are taken from `empty.pg.sql` and
   the actual data comes from `Artemis.pg.sql`. That is what undoes pgloader's
   lowercasing of constraint names and its conversion of MySQL enum columns into
   PostgreSQL enum types.

   Two tables are taken from the schema side as well: databasechangelog and
   databasechangeloglock. Their contents describe the schema rather than the
   application's data, and both the list of applied changesets and the checksum
   recorded for each of them differ between MySQL and PostgreSQL. A changeset
   written for one database only is absent from the other's list, and a changeset
   whose content varies by database, either through a dbms attribute or through a
   property that resolves to a different value, hashes differently.

   Both the empty database dump and the original MySQL data must come from an
   _identical_ version of Artemis. Otherwise, the data to be inserted might not
   match the schema definition.

   """

   import re
   from pathlib import Path
   from typing import Iterator, Optional

   # Tables whose contents describe the schema, not the application's data.
   SCHEMA_OWNED_TABLES = ("databasechangelog", "databasechangeloglock")

   # Matches: COPY artemis.feedback (id, ...) FROM stdin;
   _COPY = re.compile(r'^COPY\s+(?:"?(?P<schema>[^".\s]+)"?\.)?"?(?P<table>[^".\s]+)"?\s*\(')

   def _copied_table(line: str) -> Optional[str]:
       match = _COPY.match(line)
       return match.group("table").lower() if match else None

   def _detect_schema(data_file_path: Path) -> str:
       """pgloader names the schema after the MySQL database it read from."""
       with open(data_file_path, encoding="utf-8") as data_file:
           for line in data_file:
               match = _COPY.match(line)
               if match and match.group("schema"):
                   return match.group("schema")
       raise SystemExit(f"{data_file_path} contains no COPY statement")

   def _to_public(line: str, schema_prefix: "re.Pattern[str]") -> str:
       return schema_prefix.sub(r"\1public.", line, count=1)

   def _schema_prefix(schema: str) -> "re.Pattern[str]":
       """Matches the schema qualifier pgloader wrote, quoted or not.

       pg_dump quotes an identifier that would not survive being folded to lower
       case, so a database left under the default lower_case_table_names=0 gives
       COPY "Artemis".feedback while the helper's setting gives COPY artemis.feedback.
       """
       return re.compile(r"(COPY |setval\(')\"?" + re.escape(schema) + r"\"?\.")

   def _extract_data(data_file_path: Path, schema_prefix: "re.Pattern[str]") -> None:
       """Print the table data and the sequence positions from the pgloader dump.

       COPY blocks are tracked explicitly, because in PostgreSQL's text format
       every data row is a line of its own: a row whose first column happens to
       begin with "ALTER TABLE " would otherwise be mistaken for the end of the
       data section and silently truncate everything after it.
       """
       with open(data_file_path, encoding="utf-8") as data_file:
           data_started = False
           inside_block = False
           skipping = False
           for line in data_file:
               if inside_block:
                   block_ended = line.rstrip("\n") == "\\."
                   if not skipping:
                       print(line, end="")
                   if block_ended:
                       inside_block = False
                       skipping = False
                   continue

               table = _copied_table(line)
               if table is not None:
                   data_started = True
                   inside_block = True
                   skipping = table in SCHEMA_OWNED_TABLES
                   if not skipping:
                       print(_to_public(line, schema_prefix), end="")
                   continue

               if not data_started:
                   continue
               if line.startswith("ALTER TABLE "):
                   break
               print(_to_public(line, schema_prefix), end="")

   def _extract_schema_owned_data(schema_file_path: Path) -> None:
       """Print databasechangelog and databasechangeloglock from the schema dump."""
       with open(schema_file_path, encoding="utf-8") as schema_file:
           inside_block = False
           for line in schema_file:
               if inside_block:
                   print(line, end="")
                   if line.rstrip("\n") == "\\.":
                       inside_block = False
                   continue
               if _copied_table(line) in SCHEMA_OWNED_TABLES:
                   inside_block = True
                   print(line, end="")

   def _merge_files(*, schema_file_path: Path, data_file_path: Path) -> None:
       schema_prefix = _schema_prefix(_detect_schema(data_file_path))

       with open(schema_file_path, encoding="utf-8") as schema_file:
           schema_file_iter: Iterator[str] = iter(schema_file)
           for line in schema_file_iter:
               if line.startswith("COPY "):
                   break
               print(line, end="")

           _extract_data(data_file_path, schema_prefix)
           _extract_schema_owned_data(schema_file_path)

           alter_table_found = False
           for line in schema_file_iter:
               if line.startswith("ALTER TABLE "):
                   alter_table_found = True
               if alter_table_found:
                   print(line, end="")

   def main() -> None:
       print("-- ensure fresh schema")
       print("drop schema if exists public cascade;")
       print("create schema public;")
       print()

       _merge_files(
           schema_file_path=Path("empty.pg.sql"), data_file_path=Path("Artemis.pg.sql")
       )

   if __name__ == "__main__":
       main()
  1. Import the merged database dump merged.pg.sql into the production PostgreSQL database using psql < merged.pg.sql.
  1. Start Artemis against the new PostgreSQL database and confirm that it reports readiness. Liquibase should apply no changesets at all: the schema came from step 5, and merge.py carried over the record of how it was built.

Connecting Artemis to PostgreSQL​

In your Artemis config the following values might need to be added/updated to connect to PostgreSQL instead of MySQL:

spring:
    datasource:
        url: 'jdbc:postgresql://<IP/HOSTNAME of PostgreSQL database host>/Artemis?ssl=false'
        username: <YOUR_DB_USER>
        password: <YOUR_DB_PASSWORD>
    jpa:
        database: POSTGRESQL
Search documentation