After restore of AIO to a new machine database seems corrupted

iceman6paq · February 23, 2025, 1:11pm

Hi,
I am in the process to move my Nextcloud AIO to a new server.

For that I copied my latest backup to a datastore. Mounted this datastore with NFS to the new machine under /mnt/backup/borg.
Started the AIO with the same docker-compose.yml that I use on the old server.

Then I successfully restored the backup without any problems.
I also did an integrity check of the backups. Everything is OK.

Also the start of the AIO has no problems. But when try to login I get in the database the following error

2025-02-23T12:48:24.667689369Z Setting postgres values...
2025-02-23T12:48:24.680901507Z chmod: /var/run/postgresql: Operation not permitted
2025-02-23T12:48:24.682912765Z 
2025-02-23T12:48:24.682926040Z PostgreSQL Database directory appears to contain a database; Skipping initialization
2025-02-23T12:48:24.682930028Z 
2025-02-23T12:48:24.856545143Z 2025-02-23 13:48:24.856 CET [14] LOG:  starting PostgreSQL 16.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit
2025-02-23T12:48:24.856612922Z 2025-02-23 13:48:24.856 CET [14] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-02-23T12:48:24.856631748Z 2025-02-23 13:48:24.856 CET [14] LOG:  listening on IPv6 address "::", port 5432
2025-02-23T12:48:24.860208966Z 2025-02-23 13:48:24.860 CET [14] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-02-23T12:48:24.868026978Z 2025-02-23 13:48:24.867 CET [24] LOG:  database system was shut down at 2025-02-22 15:10:29 CET
2025-02-23T12:48:24.875678020Z 2025-02-23 13:48:24.875 CET [14] LOG:  database system is ready to accept connections
2025-02-23T12:49:09.497012214Z 2025-02-23 13:49:09.496 CET [83] ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
2025-02-23T12:49:09.497046860Z 2025-02-23 13:49:09.496 CET [83] STATEMENT:  SELECT * FROM "oc_passwords_password_rv" WHERE "deleted" = $1
2025-02-23T12:49:10.578825961Z 2025-02-23 13:49:10.578 CET [83] ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
2025-02-23T12:49:10.578851348Z 2025-02-23 13:49:10.578 CET [83] STATEMENT:  SELECT * FROM "oc_passwords_password_rv" WHERE "deleted" = $1
2025-02-23T12:49:10.936296971Z 2025-02-23 13:49:10.936 CET [83] ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
2025-02-23T12:49:10.936319615Z 2025-02-23 13:49:10.936 CET [83] STATEMENT:  SELECT "a".* FROM "oc_passwords_password_rv" "a" INNER JOIN "oc_passwords_password" "b" ON a."model" = b."uuid" WHERE ("a"."deleted" = $1) AND ("b"."user_id" = "a"."user_id") AND ("b"."uuid" = $2)

I tried to repair the database with
docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:repair

But this does not work. I see the following:

...
 - Repair Passwords Database Objects
     - Checking 0 tag revisions
    0 [--------->------------------]
     - Fixed 0 tag revisions
     - Checking 27695 folder revisions
 27695/27695 [============================] 100%
     - Fixed 0 folder revisions
     - ERROR: An exception occurred while executing a query: SQLSTATE[XX001]: Data corrupted: 7 ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
 - init metadata
 - Switches from default updater server to the customer one if a valid subscription is available
     - Repair step already executed
 - Force-reset all Text document sessions
 - Initialize migration of background images from dashboard to theming app
 - Add background job to check for backup codes
 - Populating added database structures for workflows
exit status 1

And I see then in the database log this error message

2025-02-23T12:49:23.700801414Z 2025-02-23 13:49:23.700 CET [94] ERROR:  missing chunk number 0 for toast value 111393 in pg_toast_16514
2025-02-23T12:49:23.700828145Z 2025-02-23 13:49:23.700 CET [94] STATEMENT:  SELECT * FROM "oc_authtoken" WHERE ("uid" = $1) AND ("version" = $2) LIMIT 1000

I tried to find a solution for the problem, but I have no knowledge with postgresql. So I am unable to determine which answer could help with my problem.
I think it is a damaged index.

I looked if it is possible with occ to delete all indexs and renew them, but such command seems not to be existing.

Is their a way to rebuild all index, or an other way to repair the database?
Could I have done something wrong during restore?

Thank you for your help.

Greetings

szaimen · February 23, 2025, 1:14pm

Hi, can you try to restore a different backup archive and check if that helps?

iceman6paq · February 23, 2025, 1:26pm

I tried two older backups, one week apart.
But always the same error

2025-02-23T13:23:22.568154239Z 2025-02-23 14:23:22.567 CET [118] ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
2025-02-23T13:23:22.568191029Z 2025-02-23 14:23:22.567 CET [118] STATEMENT:  SELECT * FROM "oc_passwords_password_rv" WHERE "deleted" = $1

jtr · February 23, 2025, 1:55pm

If you do manual backup (i.e. a pg_dump) of the live (working) environment’s database do you get similar errors?

Are both AIO stacks exactly the same version?

iceman6paq · February 23, 2025, 2:14pm

Hi jtr,

yes both AIO stacks are the same.

How do I a manual backup from my database in AIO database?
I see the commands, but I do not have no idea how to execute it.

PGPASSWORD="password" pg_dump [db_name] -h [server] -U [username] -f nextcloud-sqlbkp_`date +"%Y%m%d"`.bak

Where do I get my database password, db_name?
Can I run it in the postgresql container?
Or do I need to install something?

I am little bit lost at the moment.

iceman6paq · February 23, 2025, 3:21pm

Ok, I was able to get a dump of the database.

I did this.

I located the password and user data

docker exec nextcloud-aio-nextcloud grep dbpassword config/config.php
  'dbpassword' => 'aXXXXXXXXXXXXXXXXXXXXX3',

Then I entered maintenance mode and dumped the database and turned maintenance off

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --on 

docker exec nextcloud-aio-database sh -c "PGPASSWORD=\"aXXXXXXXXXXXXXXXXX3\" pg_dump nextcloud_database -U oc_nextcloud" > nextcloud-sqlbkp.bak"

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --off

Now I need to do the same reverse.

First I tried to do the same with the from the backup restored database.

docker exec nextcloud-aio-database sh -c "PGPASSWORD=\"aXXXXXXXXXXXXXXXX3\" pg_dump nextcloud_database -U oc_nextcloud" > nextcloud-sqlbkp2.bak
pg_dump: error: Dumping the contents of table "oc_passwords_password_rv" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  missing chunk number 0 for toast value 111010 in pg_toast_17285
pg_dump: detail: Command was: COPY public.oc_passwords_password_rv (id, user_id, uuid, model, folder, hidden, trashed, cse_type, cse_key, sse_type, sse_key, username, password, label, url, notes, custom_fields, hash, favorite, status, status_code, deleted, edited, created, updated, client) TO stdout;
exit status 1

So the database is damaged.

Now I try to drop the damaged database and import the dump file.

iceman6paq · February 23, 2025, 4:18pm

I fixed it.
Thank you for the tips.

Was a lot of work to find all the needed information but I did it.

Here the full write up what I did to the final solution. Maybe this can help other people if they run into the same Problem.

To dump the database you need your database password.

located the password and user data

docker exec nextcloud-aio-nextcloud grep dbpassword config/config.php
  'dbpassword' => 'aXXXXXXXXXXXXXXXXXXXXX3',

You can cat the whole file to see all settings

Then enter maintenance mode and dumped the database and turn maintenance off

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --on 

docker exec nextcloud-aio-database sh -c "PGPASSWORD=\"aXXXXXXXXXXXXXXXXX3\" pg_dump nextcloud_database -U oc_nextcloud" > nextcloud-sqlbkp.bak"

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --off

Transfer the file to your second machine.

First again turn maintenance mode on

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --on

Then you need to drop the database

docker exec nextcloud-aio-database sh -c "PGPASSWORD=\"aXXXXXXXX3\" psql -U oc_nextcloud -d postgres -c \"drop database nextcloud_database WITH (FORCE);\""

You need to use WITH (FORCE) because it looks like nextcloud still hold a lock.

Now recreate the database

docker exec nextcloud-aio-database sh -c "PGPASSWORD=\"aXXXXXXXXXXX3\" createdb -U oc_nextcloud nextcloud_database"

And then import the backup

docker exec -i nextcloud-aio-database /bin/bash -c "PGPASSWORD=aXXXXXXXX3 psql --username oc_nextcloud nextcloud_database" < /root/nextcloud-sqlbkp.bak

You need to use the full path to your backup file. Otherwise it will not work.

Turn the maintenance mode off.

docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:mode --off

Now go into the AIO interface and stop the container.
DO NOT try to use you nextcloud instance. This will result in errors. And I am not sure if it can damage your data.

Start the container and you should be able to login.

jtr · February 23, 2025, 4:28pm

Glad the manual dump worked.

Though still unclear what happened with the standard backup mechanism in AIO.

iceman6paq · February 24, 2025, 12:28pm

Hi @jtr ,

today I tried to figure out what is wrong with the backup.
I compared my original _data with the restored _data and nothing is missing.
All files are checksum identical.
The minor differences are only new created files and some cache files from plugins.

What I now want to do is to see if I can extract the database backup data from the borg backup.

But for that I need to understand how to extract the database dump from the backup.

system · March 4, 2025, 12:28pm

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.