After backing up the database and restoring it back, some tables are empty

ℹ️ Support
, ,
1
Support intro

After backing up the database and restoring it back, some tables are empty.

Nextcloud version: 20.0.9
Operating system and version: Ubuntu 20.04
Apache or nginx version: nginx 1.18.0-0ubuntu1 arm64
PHP version: 7.4.3
DB version: PostgreSQL 12.6

After backing up the database and restoring it back, some tables are empty (oc_share and oc_bookmarks).

Is this the first time you’ve seen this error? (Y/N): N
I had the same problem with Nextcloud 20.0.8. Probably not version-related.

Steps to replicate it:

  1. Make sure the source database has non-empty oc_shares and oc_bookmarks tables.
  2. Backup database using the command from Backup section
  3. Restore database using the command from Restoring Backup section

It seems not all tables are restored back. I haven’t done full DB comparison yet, but at least oc_share and oc_bookmarks tables are empty after the restore. Has anyone experienced this behavior?

2

did you check the backup files if they contain data for these two tables?

3

Did those tables have data in them PRIOR to the backup?

4

Yes. For example, there is COPY public.oc_share (id, share_type, ...) FROM stdin; statement with 75 lines of data, the same number as in the source table:

nextcloud=> select count(*) from oc_share;
 count 
-------
    75
(1 row)
5

Yes. For example, the source oc_share has 75 records:

nextcloud=> select count(*) from oc_share;
 count 
-------
    75
(1 row)

But the target oc_share is empty:

nextcloud=# select count(*) from oc_share;
 count 
-------
     0
(1 row)

It is strange, because the backup data file also has 75 lines of data for oc_share. It feels something is wrong with the import, I’ll take a look at this when I have more time.

6

There were errors while restoring the database, which I didn’t notice initially:

ERROR:  character with byte sequence 0xe2 0x80 0x94 in encoding "UTF8" has no equivalent in encoding "LATIN1"
CONTEXT:  COPY oc_bookmarks, line 14

ERROR:  character with byte sequence 0xd0 0xa7 in encoding "UTF8" has no equivalent in encoding "LATIN1"
CONTEXT:  COPY oc_share, line 20

(and in a few other tables)

This probably explains the missing data in oc_bookmarks and oc_share, just need to figure out the encoding issue.

7

The issue was my destination system was configured with en_US locale instead of en_US.UTF-8 locale.

When PostgreSQL cluster was created, it used en_US to initialize itself, which cased encoding mismatch at restore time.

The fix was to drop the cluster, re-configure locale to en_US.UTF-8 with sudo dpkg-reconfigure locales, and then re-create the cluster.

Also, when testing db restoration, it is useful to run psql with --set ON_ERROR_STOP=on or --single-transaction options to immediately notice the errors.