Migration Data API

Support
21

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 :smiley:. 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.

22

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

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 :smiley:

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

23

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

1 Like
24

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?

1 Like
25

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

26

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?

27

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.

28

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
29

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.

30

Thanks for summarizing what you've tried up to this point.

Questions:

  • Can you confirm the SSH tunnel backup was done with native pg_dump or was it something else?

Questions:

  • Was the restore with the native pg_restore or with the Central API or CLI?
  • Was the restore to the bundled PostgreSQL database that came with v1.3.3 or an external DB? What version was that external DB?
  • When you restored to v1.3.3 it sounds like everything worked, correct? What minor bugs did you notice?
  • And it also sounds like you've been able to upgrade to v1.4, v1.5, v2022.3, and v2023.1, correct? It's only when you get to v2023.2 that upgrades fail?
  • What is the specific failure when you do the v2023.2 upgrade?
31

Both API and CLI

CLI

No, because I can't change the docker-compose and configure it like this post... and Alwea gave an error... and the final use of a containerized database.

Basically, connections to an external database as mentioned in this post. It always says it can't connect.

Honestly, I’ve lost count of how many times I’ve tried to do the upgrade… But what I do know for sure is that when I go through the database update process, even setting the volume and following the steps, it doesn’t generate the file. And after checking the script, I tried to replicate everything (which is basically a backup and restore), but it doesn’t work because the database structure and fields are different.

Basically I do not do the migration I do not get an error but I can trace the container is launched, it ends and does not generate the file and I do not do the migration.

32

I'm not sure the backups you took with the Central API/CLI when you set up the tunnel were good backups. My guess is that they are corrupt due to no disk space. If I had this problem, I would:

  1. Make a written plan and have my teammates (and maybe ChatGPT?) critique it before proceeding. I'd also get https://asciinema.org installed so I have a record of all actions taken to go along with notes I took during the process.
  2. Use rsync to move the docker install from the source machine to a larger destination machine with the same OS and docker version. If rsync doesn't work, use the tunnel method to run pg_dump on the source database and restore it with pg_restore using the scripts I shared earlier.
  3. Confirm everything works after rsync move, then upgrade Central on the destination machine, version by version. Make sure you can login to every version before continuing. Use snapshots so you can easily rollback if something fails.
1 Like
33

Thanks @yanokwa for your comment! I’m going to repeat the entire process to see if I can fully understand everything.

Now, back to my question: What is the best way to migrate data from version 1.3.3 with PostgreSQL 9.6 to a newer version using PostgreSQL 14? The structure is different—it has new fields, new tables, new sequences, etc., which are causing errors.

It's clear that a simple dump and restore won’t work. But even if I create a backup via the API and the file is correct, I still get an error (as I mentioned earlier, and I’ll paste it again below).

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;

So, I’m not really sure how to approach this data migration. Do you have any suggestions?

34

I'm not aware of an easy way to go directly from an old Central version with Postgres 9.6 to a new Central version with Postgres 14.

The only reasonable suggestions I have about how you could proceed is what I previously shared at Migration Data API - #32 by yanokwa.

Sorry I can't be of more help.

35

Thanks once again! It's a shame that there isn't a tool for this migration, but I understand that at some point, when upgrading to a new version, the data will have to be transferred and adapted to the new database structure, right? Where would that happen? I only see the database upgrade process.

I'm following the steps one by one, but since I don't have a custom configuration, I assume there should be a point where the data is moved between databases…

If I manage to shed some light on this and fully understand the process (which, to be honest, is proving to be quite complex), I'll try to create a tool for this purpose.

Also, during the database upgrade and while importing the API backup, I’ve come across several required steps that aren’t mentioned in the documentation. What’s the best way to share them with you?