How do I upgrade Docker Postgresql without removing existing data?

Question

I am beginner both of docker and postgresql. 

How do I upgrade docker postgresql 9.5 into 9.6 without losing my  current database?  fyi: im using ubuntu version 14 and docker 17.09 

Thanks in advance.

Bronchiole

let me show please how do you run docker container. Where do you store postgress data? – Grimbly

how do i run docker container is docker run -d -p 5432:5432 my_image service postgresql start – Bronchiole

Answer 1

To preserve data across a docker container a volume is required. This volume will mount directly onto the file system of the container and be persevered when the container is killed. It sounds though that the container was created without a volume attached. The best way to get that data is to use copy the data folder for the container and move to the host file system. Then create a docker container with the new image. Copy the data directory to the running container's data directory in this case pgdata:/var/lib/postgresql/data

docker cp [containerID]:/var/lib/postgresql/data /home/user/data/data-dir/
docker stop [containerID]
docker run -it --rm -v pgdata:/var/lib/postgresql/data postgres
docker cp /home/user/data/data-dir [containereID]:/var/lib/postgresql/data

In case that doesn't work i would just dump the current databases, and re-upload them to the new container

Somebody

thanks for your help. but i still can't find how to copy the data folder for the container and move to the host file system. any idea ? – Bronchiole

Answer 2

You do not store database files to external storage (outside of container). Then i know only 1 way to store your database:

1) Backup database
2) Shutdown postgres 9.5 container
3) Run new postgres 9.6 container
4) Restore backup

You can use pg_dumpall for backuping full database:

pg_dumpall > backupfile

The resulting dump can be restored with psql:

psql -f backup postgres

Grimbly

actually when i inspect postgres, the volume already mount directly. so, should i do pg_dump again? – Bronchiole

Answer 3

I know it's been some time since you asked it, but I hope my solution will help future Googlers :)

I've tried to create a solution that is stateless as possible, to be compatible with CI and upgrade scripts.

The script:

1. Backs up the whole pg instance using pg_dumpall.
2. Uses the dump to create the new instance using initdb and psql -f.

The only requirement is a volume with some existing pg_data directory in it.

docker stop lms_db_1

DB_NAME=lms                                                              
DB_USERNAME=lmsweb                                                       
DB_PASSWORD=123456

CURRENT_DATE=$(date +%d-%m-%Y_%H_%M_%S)
MOUNT_PATH=/pg_data
PG_OLD_DATA=/pg_data/11/data
PG_NEW_DATA=/pg_data/13/data
BACKUP_FILENAME=v11.$CURRENT_DATE.sql
BACKUP_PATH=$MOUNT_PATH/backup/$BACKUP_FILENAME
BACKUP_DIR=$(dirname "$BACKUP_PATH")
VOLUME_NAME=lms_db-data-volume

# Step 1: Create a backup
docker run --rm -v $VOLUME_NAME:$MOUNT_PATH \
       -e PGDATA=$PG_OLD_DATA \
       -e POSTGRES_DB="${DB_NAME:-db}" \
       -e POSTGRES_USER="${DB_USERNAME:-postgres}" \
       -e POSTGRES_PASSWORD="${DB_PASSWORD:-postgres}" \
       postgres:11-alpine \
       /bin/bash -c "chown -R postgres:postgres $MOUNT_PATH \
                && su - postgres /bin/bash -c \"/usr/local/bin/pg_ctl -D \\\"\$PGDATA\\\" start\" \
                && mkdir -p \"$BACKUP_DIR\" \
                && pg_dumpall -U $DB_USERNAME -f \"$BACKUP_PATH\" \
                && chown postgres:postgres \"$BACKUP_PATH\""

# Step 2: Create a new database from the backup
docker run --rm -v $VOLUME_NAME:$MOUNT_PATH \
       -e PGDATA=$PG_NEW_DATA \
       -e POSTGRES_DB="${DB_NAME:-db}" \
       -e POSTGRES_USER="${DB_USERNAME:-postgres}" \
       -e POSTGRES_PASSWORD="${DB_PASSWORD:-postgres}" \
       postgres:13-alpine \
       /bin/bash -c "ls -la \"$BACKUP_DIR\" \
                && mkdir -p \"\$PGDATA\" \
                && chown -R postgres:postgres \"\$PGDATA\" \
                && rm -rf $PG_NEW_DATA/* \
                && su - postgres -c \"initdb -D \\\"\$PGDATA\\\"\" \
                && su - postgres -c \"pg_ctl -D \\\"\$PGDATA\\\" -l logfile start\" \
                && su - postgres -c \"psql -f $BACKUP_PATH\" \
                && printf \"\\\nhost all all all md5\\\n\" >> \"\$PGDATA/pg_hba.conf\" \
                "

Mentor