Moving database stores (docker/PostgreSQL)

Moving a PostgreSQL data store for a Docker contained server

I had to free up space on the primary OS drive, one target was to take the PostgreSQL Docker container and reconfigure to store its persistent data directory on a dedicated external volume (~HOME/data/).

Relocating PostgreSQL Storage (docker-compose.yml)

By default, Docker manages volume storage inside /var/lib/docker/. To point the database engine to a custom folder path on an external/secondary drive, I had to update the volumes mapping in docker-compose.yml:

  volumes:
    - /~HOME/data/postgis_data:/var/lib/postgresql/data

Now, I had to check read/write permissions (mine as well as the containers (UID 999) on $HOME/data/postgis_data, and may have run

sudo chown -R 999:999 /home/phewson/data/postgis_data

(The external HDD was formatted to ext4, and of course I’ve forgotted how I did that).

Back up strategy

Now, I tend to use a lot of official statistics, which I curate as csv or whatever other format you can get them in, under dvc. This means my backup strategy is really to rebuild the database from scratch. It forces you to make sure your migration scripts and importer scripts are up to date. However, in this case, I know I had some slow to import full tables, and just wanted to move a few tables over quickly. So I did a backup and restore thing, which was just as slow as it turns out.

I was running

docker compose exec -T db pg_dump -U pgdocker -d official -Fc > $HOME/data/postgis_backup/postgis.backup

and then when I’d switched over the docker container’s compose.yml I did

docker compose exec -T db pg_restore -U pgdocker -d official --clean --no-owner /home/phewson/data/postgis_backup/postgis.backup

Anyway, I’ve found it was straightforward to move the files over.