openSUSE admin

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;
                      ^

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.

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?

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

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

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

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