Project

General

Profile

tickets #94189

Matrix database wiped

Added by hellcp 4 months ago. Updated 4 months ago.

Status:
Resolved
Priority:
Normal
Assignee:
opensuse-admin
Category:
Core services and infra
Target version:
-
Start date:
2021-06-17
Due date:
% Done:

0%

Estimated time:

Description

It seems that somehow, somewhere, matrix database has been wiped and no longer contains the data that it did a few days ago. Would be good to check if dimension, discord_bridge and telegram_bridge also didn't end up in a similar position

History

#1 Updated by hellcp 4 months ago

  • Private changed from Yes to No

#2 Updated by bmwiedemann 4 months ago

Seems to be in a strange state: I can see the tables with \dt, but get

# select * from CPP_Sample;
ERROR:  relation "cpp_sample" does not exist
LINE 1: select * from CPP_Sample;
                      ^

#3 Updated by hellcp 4 months ago

bmwiedemann wrote:

Seems to be in a strange state: I can see the tables with \dt, but get

# select * from CPP_Sample;
ERROR:  relation "cpp_sample" does not exist
LINE 1: select * from CPP_Sample;
                      ^

Oh yeah, I didn't mention this, but the CPP_ tables are not a part of matrix database at all, and shouldn't be there in the first place (it seems they came from lnt database or something?). It was suggested to just use mirrordb1 version of the database in mirrordb2, but I don't have the time to set that up right now. What needs to be done basically is to migrate matrix, dimension, discord_bridge and telegram_bridge databases from there (please remember matrix needs to have C ctype!, it would be best if ctype and collation were verified after migration). I would migrate all of them from there at once for some consistency to be preserved. It would also be good to reindex just in case.

#4 Updated by andriinikitin 4 months ago

OK, let me try to recover since it looks it was me who messed it up.
So my plan:

  1. Make backup of current databases on mirrordb2:
    mkdir /var/lib/pgsql/backup_matrix_corrupted
    cd /var/lib/pgsql/backup_matrix_corrupted
    for f in matrix dimension discord_bridge telegram_bridge; do sudo -u postgres pg_dump --create -c $f > $f.sql; done

  2. Create backup on mirrordb1:
    mkdir /var/lib/pgsql/backup_matrix
    cd /var/lib/pgsql/backup_matrix
    for f in matrix dimension discord_bridge telegram_bridge; do sudo -u postgres pg_dump --port 5555 --create -c $f > $f.sql; done

  3. Copy backup_matrix to mirrordb2 /var/lib/pgsql/backup_matrix

  4. Drop databases on mirrordb2
    for f in matrix dimension discord_bridge telegram_bridge; do sudo -u postgres psql -c "drop database $f"; done

  5. Restore backup on mirrordb2
    cd /var/lib/pgsql/backup_matrix
    for f in matrix dimension discord_bridge telegram_bridge; do sudo -u postgres psql -f $f.sql; done

So the questions:

  1. Who can confirm that data on mirrordb1 is in good shape and worth migration
  2. Should matrix service be down while dump is being loaded? Or maybe have to put whole mirrordb2 down for that period?

#5 Updated by hellcp 4 months ago

andriinikitin wrote:

So the questions:

  1. Who can confirm that data on mirrordb1 is in good shape and worth migration

Last time I tried to start matrix based on mirrordb1, it complained about reindexing the database, it's hard to tell if it would actually work, but it's better than an empty database

  1. Should matrix service be down while dump is being loaded? Or maybe have to put whole mirrordb2 down for that period?

It should be down right now

#6 Updated by andriinikitin 4 months ago

I've performed the metioned steps and loaded matrix databases into mirrordb2.
I had to remove dozen duplicates in dozen tables in the process of creating unique indexes

#7 Updated by hellcp 4 months ago

It seems that all of the tables are missing indexes, would you recreate those as well?

#8 Updated by andriinikitin 4 months ago

hellcp wrote:

It seems that all of the tables are missing indexes, would you recreate those as well?

Indeed, most of tables were missing indexes, not sure how it happened. (I did troubleshoot duplicate key corruptions and probably made some mistake).
I've found missing indexes and imported them I used commands below on both mirrordb1 and mirrordb2 to make sure index definitions match properly:

mkdir /var/lib/pgsql/backup_matrix_alter
cd /var/lib/pgsql/backup_matrix_alter
for f in matrix dimension discord_bridge telegram_bridge; do sudo -u postgres pg_dump --section=post-data $f > $f.alter.sql; done

both servers show correct number of lines:

mirrordb2 (slave: postgreSQL cluster):/var/lib/pgsql/backup_matrix_alter # wc -l *
  456 dimension.alter.sql
  215 discord_bridge.alter.sql
 1888 matrix.alter.sql
  216 telegram_bridge.alter.sql
 2775 total
mirrordb1 (master: postgreSQL cluster):/var/lib/pgsql/backup_matrix_alter # wc -l *
  456 dimension.alter.sql
  215 discord_bridge.alter.sql
 1888 matrix.alter.sql
  216 telegram_bridge.alter.sql
 2775 total

#9 Updated by hellcp 4 months ago

  • Status changed from New to Resolved

I'm closing this, since it seems to be fixed

Also available in: Atom PDF