trouble to import postgresql database

[felixx9]

Hej

last night I filed https://github.com/spantaleev/matrix-docker-ansible-deploy/pull/1425 So far I didn't manage to import my old database from old manual install. I have no clue yet about containers, but did deploy a few matrix-docker-ansible-deploys without any trouble. Luckily - my old system went up almost w/o any issues (some session verification problems...).

What was my plan?

• migrate from a "real" handmade homeserver (at home) to vServer somewhere
• Only move main database synapse + media_store
• Since it was broken a lot, let go Signal- and telegram bridges, the latter rarely used.
• ...to start over again with bridges on the new server.

What I did?

• everything as with new servers, but ex- and import as described here https://github.com/spantaleev/matrix-docker-ansible-deploy/blob/master/docs/importing-postgres.md
• my main database, is named synapse already, unfortunately the tables (and the db and the tables are owned by the user matrix
• I did sudo -u postgres pg_dump synapse > synapse/synapse_dump.sql it and replaced table owner this way: sed -i "s/OWNER TO matrix;/OWNER TO synapse;/g" homeserver.sql
• I did regular ansible install, I did import of postgres via ansible, I did manual import "the long terminal line" after deleting postgres data directory, ...the long line: /usr/bin/env docker run --rm --name matrix-postgres-import --log-driver=none --user=998:1001 --cap-drop=ALL --network=matrix --env-file=/matrix/postgres/env-postgres-psql --mount type=bind,src=/migration/synapse_dump.sql,dst=/synapse_dump.sql,ro --entrypoint=/bin/sh docker.io/postgres:14.1-alpine -c "cat /synapse_dump.sql | grep -vE '^(CREATE|ALTER) ROLE (matrix)(;| WITH)' | grep -vE '^CREATE DATABASE (matrix)\s' | psql -v ON_ERROR_STOP=1 -h matrix-postgres --dbname=matrix"
• wondering, why --dbname=matrix?
• error while importing after directory deletion was stderr: 'ERROR: role "synapse" does not exist'
• database size around 8 GB - takes some time every time. Verbose output would be wish.

Any help?

Is there anybody around who can say "you did THIS wrong"? would be sooooo great. ;) Thx in advance felixx9

[felixx9]

after deleting data directory:

root@xxxxxxxxx:~# systemctl stop matrix-postgres
root@xxxxxxxxx:~# rm -rf /matrix/postgres/data/*
root@xxxxxxxxx:~# systemctl start matrix-postgres
root@xxxxxxxxx:~# /usr/local/bin/matrix-postgres-cli
psql (14.1)
Type "help" for help.

matrix=# \l
                          List of databases
   Name    | Owner  | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+---------+-------+-------------------
 matrix    | matrix | UTF8     | C       | C     | 
 postgres  | matrix | UTF8     | C       | C     | 
 template0 | matrix | UTF8     | C       | C     | =c/matrix        +
           |        |          |         |       | matrix=CTc/matrix
 template1 | matrix | UTF8     | C       | C     | =c/matrix        +
           |        |          |         |       | matrix=CTc/matrix
(4 rows)

[felixx9]

and import fails

root@xxxxxxxxx:~# /usr/bin/env docker run --rm --name matrix-postgres-import --log-driver=none --user=998:1001 --cap-drop=ALL --network=matrix --env-file=/matrix/postgres/env-postgres-psql --mount type=bind,src=/migration/synapse_dump.sql,dst=/synapse_dump.sql,ro --entrypoint=/bin/sh docker.io/postgres:14.1-alpine -c "cat /synapse_dump.sql | grep -vE '^(CREATE|ALTER) ROLE (matrix)(;| WITH)' | grep -vE '^CREATE DATABASE (matrix)\s' | psql -v ON_ERROR_STOP=1 -h matrix-postgres --dbname=matrix"
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ERROR:  role "synapse" does not exist

[felixx9]

wipe again:

root@xxxxxxxxx:~# systemctl stop matrix-postgres
root@xxxxxxxxx:~# rm -rf /matrix/postgres/data/*
root@xxxxxxxxx:~# systemctl start matrix-postgres

run ansible-playbook -i inventory/hosts setup.yml -K --tags=setup-postgres

"the long line" again. I'm soooo sure I tried this very step lots of times. Import is running. We'll se (next nights somewhen)

[spantaleev]

When you delete the data (rm -rf /matrix/postgres/data), it's not enough to just start Postgres (systemctl start matrix-postgres) and import.

You need to run --tags=setup-postgres (or --tags=setup-all) first, to prepare the various databases.

[felixx9]

yeah I think I did all combinations. wondering, where --dbname=matrix in "the long line" is coming from. Because now it looks good. At least tables are imported to the correct database (synapse)

[salabaz]

Same problem here. I think the problem is that postgres_default_import_database is by default set to matrix_postgres_db_name in import_prostgres.yaml. And postgres_db_nameis set to matrixis matrix-postgres/defaults/main.yaml

After setting postgres_default_import_database to synapse the import worked. I just started to play around with this and have no prior experience with ansible. But shouldn't postgres_default_import_database be set to matrix_synapse_database_database by default?

[spantaleev]

@salabaz, perhaps you're importing a dump of the synapse database? Or are you importing a dump that contains all databases?

[salabaz]

Yes, that's it, I import only the synapsedatabase. I found the explanation here in roles/matrix-postgres/tasks/import_postgres.yml:

38 # By default, we connect and import into the main (matrix) database. 39 # Single-database dumps for Synapse may wish to import into synapse instead.

Maybe this information should be added to the docs for people migrating from other types of installations.

[spantaleev]

Adding it to the docs sounds good! Would you like to submit a PR?