Importing an older Database into the latest docker image

[test-erik]

Hi all,

we run an older CoCalc installation which uses PostgreSQL 14. Since some internal restructuring (nothing to do with CoCalc itself) the older version doesn't run anymore. Dumping and importing the old database in a simple way failed because the schema changed.

Old CoCalc docker image: sagemathinc/cocalc-v2 uses PostgreSQL 14.8
Old CoCalc docker image: sagemathinc/cocalc-docker (the actual one with PostgreSQL 16)

The Import of the Dump of the smc fails:

psql:/tmp/pg_dumpall.dump:2582: ERROR:  column "purchase_quota" of relation "accounts" does not exist
psql:/tmp/pg_dumpall.dump:2583: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2584: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2585: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2586: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2587: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2588: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2589: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2590: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2591: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2592: error: invalid command \N
psql:/tmp/pg_dumpall.dump:2593: error: invalid command \.
psql:/tmp/pg_dumpall.dump:2600: ERROR:  trailing junk after numeric literal at or near "31889b50"
LINE 1: 31889b50-628f-420d-adb3-c8ce03c7f74a 2023-08-23 14:14:04.350...
        ^
psql:/tmp/pg_dumpall.dump:2601: error: invalid command \.

Is there a way to import the old data?

Thank you,
Erik

[haraldschilly]

I'm guessing, you have to do a database update, since the major version changed: https://www.postgresql.org/docs/current/pgupgrade.html

[test-erik]

Yes, but that's not the main problem.

Crucial is that the underlying database smc has changed its tables. In total, there are 5 tables with added/removed columns, and 2 constraints. That sounds like something I could still do manually. Hence my question for a guide on how to proceed.

[haraldschilly]

CoCalc should do this on its own (it checks for tables and columns in the database, and modifies the schema)

[test-erik]

I had success with

• initializing the plain actual CoCalc Docker image
• manually converting the PostgreSQL data from the old CoCalc installation using two PostgreSQL containers and
• copying the most old directories in the new data directory, incl. data/postgres/
• starting the actual CoCalc Docker image with the copied data.

It's nothing I want to do again, but at least CoCalc runs for now.

Thanks
Erik