2

I need to upgrade a postgresql database inside a running container. For that I need to stop the old server so I can start the new. The problem is that I didn't find how to stop the running postgres process thourgh systemctl, pg_ctl or service.

pg_ctl:

root@postgres-7ffd788bc9-g2dkv:/# su postgres
$ pg_ctl status
sh: 1: pg_ctl: not found

systemctl:

root@postgres-7ffd788bc9-g2dkv:/# systemctl status postgresql
bash: systemctl: command not found

service (returns nothing):

root@postgres-7ffd788bc9-g2dkv:/# service postgresql status
root@postgres-7ffd788bc9-g2dkv:/#

The postgresql was created using this kubernetes yaml:

    spec:
      containers:
        - name: postgres
          image: postgres:10.4

The server is running:

root@postgres-7ffd788bc9-g2dkv:/# psql postgresql://user:pass@127.0.0.1:5432/postgresdb
psql (12.5 (Debian 12.5-1.pgdg90+1), server 10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.
postgresdb=# select VERSION();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

What do you suggest to do?

justadev
  • 1,168
  • 1
  • 17
  • 32
  • A docker container is typically supposed to perform just a single task. Assuming you took the default Postgres container from the docker repo this means that stopping the container is equivalent to stopping the service. In fact, if you were to log into the container using SSH, you might find that halting the service causes the container to stop. So like @Marc Salvetti mentions in his answer, just stop the container and rebuild. If you have data inside the container (which would not be good practice), you will need to backup said data first. – JustLudo Nov 16 '20 at 10:09

2 Answers2

1

What I did at the end is creating a parallel postgres pod in Kubernetes which holds the new version. Then I dumped database from old server, copied it to the new server, and used it to initialize the data base there.

Here are the steps one by one:

  1. Create a parallel postgres service with the new version

  2. In old version pod:

pg_dumpall -U postgresadmin -h localhost -p 5432 > dumpall.sql
  1. In the host:
kubectl cp postgres-old-pod:/dumpall.sql dumpall.sql
kubectl cp dumpall.sql postgres2-new-pod:/dumpall.sql

  1. ssh to new-pod

  2. extra step that I needed, becuase for some reason new pod didn't had 'postgres' user created: get into postgres client using your credentials:

psql postgresql://postgresadmin:pass1234@127.0.0.1:5432/postgresdb?sslmode=disable
postgresdb=# CREATE ROLE postgres LOGIN SUPERUSER PASSWORD 'somepassword123';

then exit postgres and exit to normal user

  1. Finally update the database:
psql -U postgres -W -f dumpall.sql
justadev
  • 1,168
  • 1
  • 17
  • 32
-1

how about stopping the container, changing the version in the yaml then rebuild ?

Marc Salvetti
  • 352
  • 1
  • 10
  • Changing the version in the yaml didn't work because `The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 12.5` – justadev Nov 16 '20 at 10:05
  • 1
    @justadev what if you run `brew postgresql-upgrade-database` as described in this answer: https://stackoverflow.com/questions/17822974/postgres-fatal-database-files-are-incompatible-with-server – Lajos Arpad Nov 16 '20 at 10:13
  • 1
    I think the link from Lajos could be the solution. You need to launch the upgrade process on the data dir, then rebuild the container with the new version. – Marc Salvetti Nov 16 '20 at 10:20
  • I am trying to install brew inside the postgres pod, but the install script wants sudo, and it doesn't exist in the image. – justadev Nov 16 '20 at 11:06
  • @justadev do you have a highly privileged user in your container? – Lajos Arpad Nov 16 '20 at 11:22
  • The container is generated with just the root user. This is the official image for postgres from dockerhub – justadev Nov 16 '20 at 11:25