sagemathinc/cocalc-docker

document how to migrate from postgresql 10 to postgresql 15

williamstein opened this issue · 12 comments

williamstein commented

Heh, good news -- I just upgraded my ancient project from using postgres10 to postgres15 without loss of data. All I had to do was:

  1. Dump the database using pg_dumpall -f db.sql
  2. Delete the old database files completely
  3. Start the database using postgres 15 instead via pnpm database.
  4. Create the new database via:
~$ psql -d postgres
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# create database smc;
  1. Import: psql -d smc -f db.sql

And it all worked fine with zero errors.

The nice thing is that postgres 10 --> postgres 15 for us is so stable that I didn't have to do anything clever at all.

To complete this issue, the above needs to be tested again in an old cocalc-docker instance, then added as a new .md file in docs, with a link from README.md.

szazs89 commented

I have also tried to migrate from sagemathinc/cocalc to sagemathinc/cocalc-v2 and the suggested method was not very clear to me...
So, what I did at the old server:

docker exec -it old-cocalc-container bash
kill -9 `head -1 /projects/postgres/data/postmaster.pid` # check with ps axf...

PG10=/projects/postgres10
PG14=/projects/postgres
cp -a /projects/postgres $PG10
rm -rf /projects/postgres/data/*  # does not need chown / chmod :-)
apt-get update && apt-get install postgresql-14

PG10_BIN=/usr/lib/postgresql/10/bin
PG14_BIN=/usr/lib/postgresql/14/bin
sudo -u sage $PG14_BIN/pg_ctl init -D $PG14/data
sudo -u sage $PG14_BIN/pg_upgrade -d $PG10/data -D $PG14/data -b $PG10_BIN -B $PG14_BIN
cp -a $PG10/data/{socket,pg_hba.conf} $PG14/data/
tail -3 $PG10/data/postgresql.conf >> $PG14/data/postgresql.conf
exit
docker stop old-cocalc-container

Run the new container using cocalc-v2 image.
Ta-da...

  • 👍1
haraldschilly commented

Thank you @szazs89 … this looks like a first step towards automating this. I've added a link to your instructions from the README.md file.

crockeea commented

When running

sudo -u sage $PG14_BIN/pg_upgrade -d $PG10/data -D $PG14/data -b $PG10_BIN -B $PG14_BIN

could not open log file "pg_upgrade_internal.log": Permission denied
Failure, exiting

Suggestions?

williamstein commented
szazs89 commented

@crockeea check the ownership of the directory structure (the new one and the one which the db to be migrated from...)

crockeea commented

@williamstein That command is copied from szazs89's suggestion.

williamstein commented

Please check the ownership of the relevant directories to see. Maybe use the user "postgres" or whatever the owner of the relevant files is.

crockeea commented 
> ls -l /projects
...
drwx------  3 sage      sage        18 Aug 26  2019 postgres
drwx------  3 sage      sage        18 Aug 26  2019 postgres10

I tried changing the owner of both directories to postgres and running the command with sudo -u postgres, but got the same error. I think the real problem was that I was running the command from /; I got farther after cd /tmp.

Now I'm getting

root@0bb1704ab603:/tmp# sudo -u sage $PG14_BIN/pg_upgrade -d $PG10/data -D $PG14/data -b $PG10_BIN -B $PG14_BIN

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

root@0bb1704ab603:/tmp# ps afx
  PID TTY      STAT   TIME COMMAND
   94 pts/0    Ss     0:00 bash
 3796 pts/0    R+     0:00  \_ ps afx
    1 ?        Ss     0:00 python3 /root/run.py
   20 ?        Ss     0:00 sshd: /usr/sbin/sshd [listener] 0 of 10-100 startups
   29 ?        S      0:00 /bin/bash -c mkdir -p /var/log/hub && cd /cocalc/src/packages/hub && npm run hub-docker-prod > /v
   32 ?        Sl     0:00  \_ npm run hub-docker-prod
   60 ?        Sl     0:00      \_ npm exec cocalc-hub-server --mode=multi-user --all --hostname=0.0.0.0 --https-key=/projec
   71 ?        Sl     0:14          \_ node /cocalc/src/packages/hub/node_modules/.bin/cocalc-hub-server --mode=multi-user -
williamstein commented

"There seems to be a postmaster servicing the old cluster. Please shutdown that postmaster and try again."

I think this message is based on a pid file existing, not some actual process running. You have to find that file and delete it. It's called postmaster.pid. https://dba.stackexchange.com/questions/30949/cannot-shutdown-old-postmaster-when-upgrading-to-postgres-9-2

crockeea commented

The following worked for me:

docker exec -it cocalc-server bash

PG10=/projects/postgres10
PG14=/projects/postgres
PG10_BIN=/usr/lib/postgresql/10/bin
PG14_BIN=/usr/lib/postgresql/14/bin

sudo -u sage $PG10_BIN/pg_ctl stop -D $PG14/data

cp -a /projects/postgres $PG10
rm -rf /projects/postgres/data/*  # does not need chown / chmod :-)
apt-get update && apt-get install postgresql-14

cd /tmp
sudo -u sage $PG14_BIN/pg_ctl init -D $PG14/data
sudo -u sage $PG14_BIN/pg_upgrade -d $PG10/data -D $PG14/data -b $PG10_BIN -B $PG14_BIN
cp -a $PG10/data/{socket,pg_hba.conf} $PG14/data/
tail -3 $PG10/data/postgresql.conf >> $PG14/data/postgresql.conf
exit
docker stop old-cocalc-container

I was getting errors about "The source was not shut down cleanly" and/or "could not open log file "pg_upgrade_internal.log": Permission denied" when using kill -9. Using pg_ctl stop seems to fix those issues. I also needed to change to /tmp instead of running the conversion command from /.

  • 👍1
williamstein commented

Thanks!!