Migration Data API

sowe1 · March 10, 2025, 2:07pm

One more time .. thanks for your reply.

Sounds like you might be on an older version.
There are upgrade notes that include the migration from Postgres 9.6 --> 10 here

I'm trying this process multiple times, and it doesn't work. I don't know why... because the script is basically just dump and restore, that's all. Maybe @yanokwa has more information... again, it doesn't work

Probably best to start with that, upgrade progressively (one version at a time) to the latest version of ODK Central (which will include all latest fixes), then if you need to backup via API at some point it will probably work (although the backup by API does sound like an XY problem to me)

I suppose the version was the problem. That's why I wrote the version 13 days ago . Anyway, this process is too long and a little bit confusing. In the notes, the only part about the database is this: [link] secction 8 . It's the same process... and it doesn’t work.

spwoodcock · March 10, 2025, 2:43pm

Ah apologies, I see you posted v1.3.3 in a previous message / log

That probably explains things: it's a pretty old version, near 4yrs old now.
If you wish to upgrade to a newer version, you will need to follow all of the upgrade notes one by one, starting with an upgrade to v1.4.0: https://docs.getodk.org/central-upgrade/#upgrading-to-central-v1-4

I don't think you can pick and choose the parts you wish to upgrade unfortunately - just updating the database won't work - you need to follow everything in the upgrade notes

If you have any issues, I'm sure someone can help, but it's not easy to when the only info is 'it doesn't work'. If you have a specific problem we can help with, please post configs, error logs, and other useful info that might be a lead to start debugging with

sowe1 · March 10, 2025, 5:19pm

I've been adding information as issues have come up. I'm really trying to use the API, but still, let's see... You're actually right, and I appreciate your time and your responses.

If you have any issues, I'm sure someone can help, but it's not easy to when the only info is 'it doesn't work'. If you have a specific problem we can help with, please post configs, error logs, and other useful info that might be a lead to start debugging with

yanokwa · March 10, 2025, 10:12pm

Do you have a full machine backup of the install you are migrating? Are you able to try things on a snapshot?

It sounds like you want to move data from a Central v1.3 install to a Central v2024.x install. Why not upgrade the Central v1.3 install in place and then migrate the updated install?

sowe1 · March 11, 2025, 7:55am

Hi @yanokwa thanks for your replay, the reason is simple the server don't have space

yanokwa · March 13, 2025, 1:32pm

I'd recommend finding some way to free up space (e.g., uninstalling a large app, increasing disk size) on the server and upgrade in place and then migrate.

Do you have a full machine backup of the install you are migrating? Are you able to try things on a snapshot?

sowe1 · March 13, 2025, 6:24pm

Hi @yanokwa
Yes, I’ve checked, but that option is impossible—there’s no space, which is why we need to proceed with the migration.

Moreover, I have tried migrating that version (1.3.3) following the steps, and as I mentioned earlier, it gets stuck at the database stage because it is different, and there is no way to make the data compatible. The rest of the files (Enketo, mail, etc.) are standard.

I have even given up on using an external database to avoid further issues. For that reason, I also don’t see it as easy or feasible to upgrade version by version

On the other hand, I managed to extract the backup via the API, but now, when I try to restore it in the new ODK instance, I’m getting errors like this:

odk@odk:/opt/odk-central/central$ docker compose exec service node /usr/odk/lib/bin/restore.js /tmp/odk-bk20230913-08AM.zip bk202309
Error: Command failed: pg_restore -e -j 4 -F d -C -c --no-owner --no-privileges -h IP -U odk -d template1 /tmp/tmp-86-KfNZ46JJeLPj
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5; 2615 2200 SCHEMA public odk
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

Before that, if I generate a backup using the API (1.3.3), can I restore this backup in a newer version? Because my gut feeling says no.

Do you have any ideas? Honestly, every step I take leads to 1,000 new errors or issues. I fix one problem, and five more appear.

yanokwa · March 13, 2025, 10:02pm

I've asked this twice and still haven't gotten a response, so I'll try one more time.

Do you have a full machine backup of the install you are migrating? Are you able to try things on a snapshot?

If you are truly out of the space (or close to it), it's likely that the backup you are taking is incomplete or corrupt because it has run out of disk space.

If you have a full machine backup or snapshot, you could restore it to a machine with more space and do the upgrade there.

If you don't have full machine backup, try mounting another drive and dumping the data to that drive so it's safe. I haven't tried these scripts recently, but should help you backup and restore manually. The only caveat is that you need to restore to the same version of Central on the destination.

central-backup.sh (run on source machine)

#!/bin/bash
  
set -ex

BACKUP_DIR="/external-drive/central-backup"
mkdir -p "$BACKUP_DIR"

# prevent incoming data during backup
docker stop central-nginx-1

docker cp central-service-1:/etc/secrets/ "$BACKUP_DIR/"

docker exec central-enketo_redis_main-1 redis-cli -p 6379 save
docker cp central-enketo_redis_main-1:/data/enketo-main.rdb "$BACKUP_DIR/enketo-main.rdb"
docker exec central-enketo_redis_cache-1 redis-cli -p 6380 save
docker cp central-enketo_redis_cache-1:/data/enketo-cache.rdb "$BACKUP_DIR/enketo-cache.rdb"

docker exec central-postgres14-1 pg_dump --verbose --format=custom --username odk odk > "$BACKUP_DIR/central.pgdump"

central-restore.sh (run on destination machine)

#!/bin/bash
  
set -ex
BACKUP_DIR="/external-drive/central-backup"

cd "/root/central"

docker stop central-nginx-1
docker stop central-service-1
docker stop central-enketo_redis_main-1
docker stop central-enketo_redis_cache-1

docker cp "$BACKUP_DIR/secrets/." central-service-1:/etc/secrets/
docker cp "$BACKUP_DIR/enketo-main.rdb" central-enketo_redis_main-1:/data/enketo-main.rdb
docker cp "$BACKUP_DIR/enketo-cache.rdb" central-enketo_redis_cache-1:/data/enketo-cache.rdb

docker exec --interactive central-postgres14-1 pg_restore --verbose --clean --if-exists --username odk --dbname odk < "${BACKUP_DIR}/central.pgdump"

docker compose stop
docker compose build
docker compose up -d

sowe1 · March 14, 2025, 7:52am

Hi @yanokwa

Do you have a full machine backup of the install you are migrating?

i've a Postgresql backup only no Snapshot of VM.

Are you able to try things on a snapshot?

No , i can't

sorry about that. Maybe I didn't explain myself well, and I apologize. I thought everything was clear in the thread, but at this point, I think it's best to summarize so that we're all on the same page.

The current machine has no available space and cannot be cleaned up. Additionally, the hard drive was configured in a way that prevents it from dynamically expanding.
I tried multiple ways to take a backup via the API, but it was impossible. As an alternative, I created a new machine and set up an SSH tunnel to access the Docker container's IP and PostgreSQL port. With this, I was at least able to obtain a database backup.
Using that backup, I attempted to restore the database in the new version of ODK, but it turned out to be incompatible, or at least I haven't found a viable way to make it work.
Since that wasn’t possible, I set up a machine with the same ODK version as the production environment (1.3.3) and restored the database. It wasn't easy, but I managed to do it, although I noticed some minor bugs.
From there, I tried multiple times to follow the upgrade process described in the documentation (I’ve tried it three times), but none of them worked. I always get stuck at the same point: database migration. The rest of the process (configuration files, etc.) is not an issue since my setup is standard and has no modifications.
Since I couldn’t complete the upgrade process, I attempted to restore the backup from version 1.3.3 into the new ODK version, but I ran into the error I mentioned yesterday.

Thanks for you time ..

Postgresql Origin 9.6 - Postgresql Target 14.