Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

synapse_port_db script can't run twice on same sqlite database #16040

Closed
axelsimon opened this issue Jul 31, 2023 · 2 comments · Fixed by #16043
Closed

synapse_port_db script can't run twice on same sqlite database #16040

axelsimon opened this issue Jul 31, 2023 · 2 comments · Fixed by #16043

Comments

@axelsimon
Copy link
Contributor

Description

I tried to follow the instructions on moving from sqlite to postgres and run the synapse_port_db script on homeserver.db twice, the second time on a homeserver.db a few hours later (the rsync -Pav --append command was over in seconds).

The first run of the port script completes properly, even if it takes about 520 min (so, close to 9 hours). In fairness, the sqlite db it about 42GB.

Trying to run the script again ends abruptly with:

synapse_port_db --curses --sqlite-database /mnt/btrfs-vol/matrix/ho
meserver.db --postgres-config /home/debian/matrix-synapse/homeserver-postgres.yaml
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 719, in run
    self.postgres_store = self.build_db_store(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 635, in build_db_store
    store = Store(DatabasePool(hs, db_config, engine), db_conn, hs)  # type: ignore[arg-type]
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1753, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 256, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/client_ips.py", line 75, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/deviceinbox.py", line 905, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/devices.py", line 1469, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events_bg_updates.py", line 100, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/media_repository.py", line 70, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/registration.py", line 1985, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/registration.py", line 153, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/room.py", line 1643, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/roommember.py", line 1426, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/search.py", line 136, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/state/bg_updates.py", line 280, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/state.py", line 503, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/user_directory.py", line 96, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/end_to_end_keys.py", line 85, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/e2e_room_keys.py", line 54, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/stats.py", line 120, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/account_data.py", line 64, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/filtering.py", line 43, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/profile.py", line 38, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/push_rule.py", line 339, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/push_rule.py", line 115, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/appservice.py", line 98, in __init__
    self._as_txn_seq_gen = build_sequence_generator(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/util/sequence.py", line 283, in build_sequence_generator
    seq.check_consistency(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/util/sequence.py", line 180, in check_consistency
    raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup:
Postgres sequence 'application_services_txn_id_seq' is inconsistent with associated
table 'application_services_txns'. This can happen if Synapse has been downgraded and
then upgraded again, or due to a bad migration.

To fix this error, shut down Synapse (including any and all workers)
and run the following SQL:

    SELECT setval('application_services_txn_id_seq', (
        SELECT GREATEST(MAX(txn_id), 0) FROM application_services_txns
    ));

See docs/postgres.md for more information.


Postgres sequence 'application_services_txn_id_seq' is inconsistent with associated
table 'application_services_txns'. This can happen if Synapse has been downgraded and
then upgraded again, or due to a bad migration.

To fix this error, shut down Synapse (including any and all workers)
and run the following SQL:

    SELECT setval('application_services_txn_id_seq', (
        SELECT GREATEST(MAX(txn_id), 0) FROM application_services_txns
    ));

See docs/postgres.md for more information

Running the suggested SQL on the synapse db from psql does nothing, as far as i can see.

I've now attempted this about 4 or 5 times with the same result.

Steps to reproduce

  • stop synapse
  • rsync homeserver.db to another server with enough space
  • start synapse
  • on other server: run synapse_port_db
  • at migration time: stop synapse
  • rsync homeserver.db to other server (using --apend-verify or --append)
  • on other server: run synapse_port_db
  • postgres db appears messed up and port script crashes

Homeserver

other homeserver

Synapse Version

1.88.0+bullseye1

Installation Method

Debian packages from packages.matrix.org

Database

SQLite

Workers

Single process

Platform

Debian Bookworm 12.1 on main server
Debian Bullseye 11.7 on server running port script

Configuration

Presence is on. It's a small server (<10 users)

Relevant log output

synapse_port_db --curses --sqlite-database /mnt/btrfs-vol/matrix/ho
meserver.db --postgres-config /home/debian/matrix-synapse/homeserver-postgres.yaml
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 719, in run
    self.postgres_store = self.build_db_store(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 635, in build_db_store
    store = Store(DatabasePool(hs, db_config, engine), db_conn, hs)  # type: ignore[arg-type]
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1753, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 256, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/client_ips.py", line 75, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/deviceinbox.py", line 905, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/devices.py", line 1469, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events_bg_updates.py", line 100, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/media_repository.py", line 70, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/registration.py", line 1985, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/registration.py", line 153, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/room.py", line 1643, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/roommember.py", line 1426, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/search.py", line 136, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/state/bg_updates.py", line 280, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/state.py", line 503, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/user_directory.py", line 96, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/end_to_end_keys.py", line 85, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/e2e_room_keys.py", line 54, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/stats.py", line 120, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/account_data.py", line 64, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/filtering.py", line 43, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/profile.py", line 38, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/push_rule.py", line 339, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/push_rule.py", line 115, in __init__
    super().__init__(database, db_conn, hs)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/appservice.py", line 98, in __init__
    self._as_txn_seq_gen = build_sequence_generator(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/util/sequence.py", line 283, in build_sequence_generator
    seq.check_consistency(
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/util/sequence.py", line 180, in check_consistency
    raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup:
Postgres sequence 'application_services_txn_id_seq' is inconsistent with associated
table 'application_services_txns'. This can happen if Synapse has been downgraded and
then upgraded again, or due to a bad migration.

To fix this error, shut down Synapse (including any and all workers)
and run the following SQL:

    SELECT setval('application_services_txn_id_seq', (
        SELECT GREATEST(MAX(txn_id), 0) FROM application_services_txns
    ));

See docs/postgres.md for more information.


Postgres sequence 'application_services_txn_id_seq' is inconsistent with associated
table 'application_services_txns'. This can happen if Synapse has been downgraded and
then upgraded again, or due to a bad migration.

To fix this error, shut down Synapse (including any and all workers)
and run the following SQL:

    SELECT setval('application_services_txn_id_seq', (
        SELECT GREATEST(MAX(txn_id), 0) FROM application_services_txns
    ));

See docs/postgres.md for more information

Anything else that would be useful to know?

The two server configuration is only to run the port script on a second server that has enough space, the main one doesn't have enough space to fit both the sqlite db and the same db ported to postgres.

The second server never actually runs synapse, only the port script.
@axelsimon
Copy link
Contributor Author

Possible duplicate of #8619, #9979.

@clokep
Copy link
Member

clokep commented Aug 1, 2023

I think #15965 fixes this?

@clokep clokep mentioned this issue Aug 1, 2023
Merged
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Properly setup more sequences in portdb matrix-org/synapse
2 participants
@clokep @axelsimon