Docker Community Forums

Share and learn in the Docker community.

Upgrade Postgres 9.6-alpine data to 13.2-alpine

General Discussions
#1

Hi,

I upgraded a while back from 9.5 to 9.6-alpine but cannot seem to get 9.6-alpine upgraded to 13.2-alpine.

Here is what I have tried:
Create a dump:
docker exec bayden10_db_1 /usr/bin/pg_dumpall -U postgres -f /var/lib/postgresql/data/dump.sql

produces an error:
OCI runtime exec failed: exec failed: container_linux.go:370: starting container process caused: exec: "/usr/bin/pg_dumpall": stat /usr/bin/pg_dumpall: no such file or directory: unknown

This dump procedure did work before upgrading from 9.5 to 9.6-alpine but I’m not able to dump or upgrade.

Any guidance with upgrading my existing DB from 9.6-alpine to 13.2-alpine would be appreciated.

~b10

#2

I was able to dump with this method:

docker exec bayden10_db_1 /usr/local/bin/pg_dump -Fd postgres -U postgres -f /var/lib/postgresql/data/dump.sql

I then tried to upgrade by way of the following

docker-compose down cp -a db db.bak mv db/production/dump.sql . rm -r db/

nano docker-compose.yml

Change 9.6-alpine to 13.2-alpine

docker-compose up -d

Then I’m seeing error with authentication on the default db:

docker logs -f bayden10_db_1

2021-03-18 15:30:57.874 UTC [1] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
2021-03-18 15:30:57.874 UTC [1] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2021-03-18 15:30:57.874 UTC [1] LOG: listening on IPv6 address “::”, port 5432
2021-03-18 15:30:57.880 UTC [1] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
2021-03-18 15:30:57.888 UTC [48] LOG: database system was shut down at 2021-03-18 15:30:57 UTC
2021-03-18 15:30:57.893 UTC [1] LOG: database system is ready to accept connections
2021-03-18 15:30:58.573 UTC [55] LOG: invalid length of startup packet
2021-03-18 15:31:06.198 UTC [56] FATAL: password authentication failed for user “postgres”
2021-03-18 15:31:06.198 UTC [56] DETAIL: Password does not match for user “postgres”.
Connection matched pg_hba.conf line 99: “host all all all md5”
2021-03-18 15:31:09.552 UTC [57] FATAL: password authentication failed for user “postgres”
2021-03-18 15:31:09.552 UTC [57] DETAIL: Password does not match for user “postgres”.
Connection matched pg_hba.conf line 99: “host all all all md5”

I then modified pg_hba.conf to

host all all all trust

This then allowed the default db start. How ever when upgrading from dump.sql

docker exec greenlight_db_1 /usr/local/bin/psql -U postgres -c “DROP DATABASE greenlight_production;”
DROP DATABASE

then

cp dump.sql db/production/
docker exec greenlight_db_1 /usr/local/bin/psql -U postgres -f /var/lib/postgresql/data/dump.sql

psql:/var/lib/postgresql/data/dump.sql: error: could not read from input file: Is a directory

Do I need to do a custom dump here?

#3

version: “3.8”
The docker-compose.yml API has different versions, this is something to be keenly aware of while researching and creating your configuration. It is typically best to use the latest version. You should also check what version of Docker Engine you are running.

services:
postgres:
A typical docker-compose.yml in a professional environment will have several services configured to work together. For our purpose we only need the one. The key for your service is arbitrary, you could name it db or banana, but for organizational purposes it makes most sense to match the key to the Docker image you are relying upon.

image: postgres:13-alpine
This is the image tag, the list of available options is available on Docker Hub. postgres refers to the Docker image you would like to use. 13-alpine is a tag of the image with 13 referring to the version of Postgres you would like to use and alpine denoting a flavor of linux that is stripped down to be as small as possible. You can always specify simply postgres or postgres:latest if size or version doesn’t matter to you.

restart: always
What it says on the label! Always restart this container when docker engine starts.

ports:

  • “5432:5432”
    This forwards the Docker containers ports to your machine’s ports. 5432 is PostgreSQL’s default port. You should only need to change this if you have a conflicting process using the same port.

environment:
POSTGRES_USER: prisma
POSTGRES_PASSWORD: prisma
POSTGRES_DB: tabata
While there are other environment variables available for your PostgreSQL container, this is all you need to get the container running. If the container and database do not yet exist docker-compose up will create a database with this name, user, and password.

#4

Hi,

This would imply to create new and I have tested that 13.2-alpine default works, I need to import the data from 9.6-alpine.

Current 9.6-alpine docker-compose.yml

version: '3'

services:
  app:
    entrypoint: [bin/start]
    image: redzed:release-v2.8.2
    container_name: bayden10-v2.8.2
    env_file: .env
    restart: unless-stopped
    ports:
      - 127.0.0.1:5000:80
# When using external logging
#    logging:
#      driver: $LOG_DRIVER
#      options:
#        syslog-address: $LOG_ADDRESS
#        tag: $LOG_TAG
    volumes:
      - ./log:/usr/src/app/log
      - ./storage:/usr/src/app/storage
#      - ./terms.md:/usr/src/app/config/terms.md
# When using sqlite3 as the database
#      - ./db/production:/usr/src/app/db/production
# When using postgresql as the database
    links:
      - db
  db:
    image: postgres:9.6-alpine
    restart: unless-stopped
    ports:
      - 127.0.0.1:5432:5432
    volumes:
      - ./db/production:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=[password]