Importing existing postgresql fails on 'ALTER SCHEMA public OWNER TO postgres;'

[Torxed]

Describe the bug Trying to import a matrix server that's lived for almost 10 years now. And the postgresql data is quite old, which might play a role here.

Matrix Server:

• OS: Arch Linux
• Architecture amd64
• Synapse 0.2.99.4-4
• PostgreSQL 16.3-3

Additional context

  - set -o pipefail && cat /synapse_2024_11_02.sql.gz | gunzip | grep -vE '^(CREATE|ALTER) ROLE "?(matrix|synapse)"?(;| WITH)' | grep -vE '^CREATE DATABASE "?(matrix|synapse)"?\s' | psql -b -v ON_ERROR_STOP=1 -h matrix-postgres --dbname=synapse
  delta: '0:00:00.598362'
  end: '2024-11-02 20:01:05.464000'
  failed_when_result: true
  finished: 1
  msg: non-zero return code
  rc: 3
  results_file: /root/.ansible_async/j208183810947.10728
  start: '2024-11-02 20:01:04.865638'
  started: 1
  stderr: |-
    ERROR:  role "postgres" does not exist
    STATEMENT:  ALTER SCHEMA public OWNER TO postgres;

I suspect it's just a matter of grep -vE '^(CREATE|ALTER) SCHEMA public ?' or something similar.

[Torxed]

I should probably have read the sections after how to import as it contained some tips.

Unzipping the sql dump and ran sed over it fixed it:

$ sed -i 's/ALTER SCHEMA public OWNER TO postgres;/ALTER SCHEMA public OWNER TO pg_database_owner;/g' synapse_2024_11_02.sql

pg_database_owner appears to be the legit owner of the database, rather than something more standard. so it's not a placeholder. And the operation might be slow if the database is big.

Apologies for the noise!