document how to migrate from postgresql 10 to postgresql 15

[williamstein]

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;

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

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

[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 https://github.com/sagemathinc/cocalc-docker/issues/193#issuecomment-1786263008, 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

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

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