document how to migrate from postgresql 10 to postgresql 15 about cocalc-docker

[szazs89]

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

[crockeea]

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 /.

[haraldschilly]

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]

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]

Why are you becoming the “Sage” user? -- William Stein

…

On Mon, Oct 30, 2023 at 5:47 PM crockeea ***@***.***> wrote: 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? — Reply to this email directly, view it on GitHub <#193 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJXS5UPNZJRJF65KEKJEU3YCBDBHAVCNFSM6AAAAAAY6EO6A2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBWGI3DGMBQHA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[szazs89]

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

[crockeea]

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

[williamstein]

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]

> 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]

"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

[williamstein]

Thanks!!

[crockeea]

One further note: if, in the process of doing this upgrade, you're moving /projects to another location/drive, use cp -p to preserve permissions. Otherwise, when you start the new server, your projects and files will appear but you will get an Error: EACCES, permission denied error when you try to open a file.