ODK Central v2026.2.0 direct backup restore fails with "cannot drop objects owned by role odk because they are required by the database system"

Support
1

1. What is the issue? Please be detailed.

I'm testing the new v2026.2 direct backup/restore workflow and am unable to restore a direct backup to a fresh Central instance of the same version. The restore fails during the database cleanup phase with:

ERROR: cannot drop objects owned by role odk because they are required by the database system

Source instance:

Central v2026.2.0

Destination instance (freshly provisioned):

versions:
3e9b78f4c7d1636982643585759c3d8a904904e3 (v2026.2.0-1-g3e9b78f)
0000000000000000000000000000000000000000 client (v2026.2.0)
bee93414a2215bd73d7f371113c6640c66d90c95 server (v2026.2.0)

PostgreSQL: 14.23 (Debian 14.23-1.pgdg13+1)

2. What steps can we take to reproduce this issue?

  1. Create a direct backup on a Central v2026.2.0 instance using the documented streaming approach:

curl -X POST \
-u "$EMAIL:$PASSWORD" \
-H "Content-Type: application/json" \
-d "{\"passphrase\":\"$PASSPHRASE\"}" \
https://<domain>/v1/backup \
--output backup.pgdump.enc.bin

  1. Verify the backup file is valid (file reports openssl enc'd data with salted password).
  2. Attempt restore on a fresh v2026.2.0 instance using the documented command:

cat backup.pgdump.enc.bin \
| docker compose exec -T service \
node /usr/odk/lib/bin/restore.js - 'PASSPHRASE'

3. What have you tried to fix the issue?

  • Confirmed the backup file integrity using the documented openssl | pg_restore --file /dev/null verification method.
  • Attempted restore on the original source instance — same failure.
  • Provisioned a brand-new Central v2026.2.0 instance and attempted restore — same failure. This rules out existing project data as a cause.

Error output

The restore begins and reaches the database cleanup phase:

BEGIN;

DROP OWNED BY CURRENT_USER CASCADE;

then fails with:

ERROR: cannot drop objects owned by role odk because they are required by the database system
pg_restore: error: could not write to output file: Connection reset by peer

process exited due to stream error (ABORT_ERR):
/bin/bash -c openssl enc -d -pbkdf2 -pass env:ODK_BACKUP_PASSPHRASE -chacha20 | pg_restore --exit-on-error --no-owner --no-acl --file=-

DATABASE RESTORE FAILED.

bad decrypt
Broken pipe

Note: bad decrypt and Broken pipe appear to be secondary failures caused by the restore aborting, not the root cause.

Additional context

The failing SQL originates from /usr/odk/lib/util/backup.js:141. The odk role owns system-level objects including the plpgsql, citext, and pg_trgm extensions, as well as the postgres, template0, and template1 databases — which PostgreSQL appears to consider required system objects that cannot be dropped.

Question

Is DROP OWNED BY CURRENT_USER CASCADE in the v2026.2 restore script expected to handle the case where the odk role owns PostgreSQL system objects? Has anyone else reproduced this with v2026.2 direct backups?

2

Hi @andhulthen,

Central's backup API and restore functionality assumes that the whole application stack is running with the default Docker Compose setup, which means that the PostgreSQL database is also running in the provided Docker container. In that setup, there is a default postgres database and an application database odk, so restore.js can safely drop all objects owned by the odk user.

Having said that the latest version of Central has made significant improvements in how the backup file is generated, so that it can be restored without depending on the provided restore.js. In your case, you would need to manually drop all the tables/views/functions/etc. related to the application, and then run:

openssl enc -d -pbkdf2 -pass BACKUP_PASSPHRASE -chacha20 < backup.bin | \
    pg_restore --exit-on-error --no-owner --no-acl --single-transaction -d odk

If you are using a postgresql-as-a-service offering on the cloud, I would recommend relying on their backup/restore features. Otherwise, it is recommended to keep the default postgres database and the application database odk separate, for administrative convenience.

Thanks,

Sadiq

3

Thanks @Sadiq_Khoja for your guidance. I was able to successfully restore the backup using the pg_restore approach you suggested.

On a fresh Central v2026.2.0 instance, I first reset the application schema using:

docker compose exec -T postgres14 psql -U odk -d odk -c "
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
"

And then restored the database with:

openssl enc -d -pbkdf2 -pass BACKUP_PASSPHRASE -chacha20 < backup.bin | \
    pg_restore --exit-on-error --no-owner --no-acl --single-transaction -d odk

The restore completed successfully, and project, form and submission counts matched the source instance.

Is resetting the application schema in this way is a reasonable approach for preparing a fresh Central instance for a direct backup restore? If not, what would be the recommended method for preparing a fresh iinstance before running a direct backup restore?

I am also interested in understanding the original error a little better. Is it expected that PostgreSQL objects are owned by odk, and if so, which objects is restore.js attempting to drop that result in the failure?

Thanks again for the assistance and clarification.

4

Hi @andhulthen, I am glad that using pg_restore manually worked for you.

Regarding dropping public schema before restore is fine approach. But my recommendation would be to create a super admin like postgres which should own the instance level objects like template0, template1 and plpgsql extension. Currently (i'm assuming) odk is owning those objects in your case hence DROP OWNED BY CURRENT_USER CASCADE fails.

Hope this answers your question.