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.
Use the share button below if you liked it.
It makes me smile, when I see it.