Question: How to backup/restore postgres and other data in docker containers. about full-stack-fastapi-postgresql

[pschelle]

Hi @tiangolo,

Thanks for replying and the great efforts you put into FastAPI and the project generators.

Your answer on docker backup is in line with the answers I've been googling the last few days.
So now I know for sure I'm thinking in the right direction.

The trick is to map/mount a backup folder into the container and run a command in those containers to make the backup into that mounted folder.

In fact, bumping into this project, https://github.com/justb4/docker-pgbackup turned up some interesting ideas.
The project is about backing up postgres containers, but I think the idea can be further built upon.

• A python script, using docker-py, reads the docker container labels running on the docker host.
• Based on the labels, it identifies the containers that have data to be backup-ed (manually triggered or by some cron mechanism).
• For each appropriate container (eg. postgres db container or another container with (uploaded) data files not stored in the db), it will issue the necessary commands (pg_dump, rsync, etc.)
• The other way around, the same script knows how to trigger restoring any back-upped data.
• Optionally, it can also take care of moving the data to some off-site backup plan.
• This python script can be packed into a docker image as done in the above project.
• When deployed onto a "Docker Swarm Rocks" cluster, it will take care of backing up all the appropriately tagged containers.

So I'm going to fiddle with docker a bit more and probably try to build me such a "full-stack-backup" project.

Anyways, thanks again for replying and I'll close the issue as I consider my questions answered ;-).

[ebreton]

Hi @pschelle ,

I am also a newbie when it comes to docker swarm, but I can still share how we currently backup our postgresql DB on our project, with our containers and a custom Makefile.

This would need to be adapted, probably with docker-compose exec instead of docker exec. As you can see below, we use variable environments (that are loaded from a .env file, and also credentials which are loaded from a .pgpass file.

We use make backup with the following commands:

check-backup-path:
	# create ~/backups folder if does not exists. 
	# set group permissions to docker to allow containers to write into it
	[ -d $(HOME)/${BACKUP_PATH} ] || (mkdir -p $(HOME)/${BACKUP_PATH} && sudo chown ubuntu:docker $(HOME)/${BACKUP_PATH})

backup-pg:
	# use DATE var to set another date than today
	docker exec cci_postgres pg_dump -C -d ${POSTGRES_DB} -U ${POSTGRES_USER} > $(HOME)/${BACKUP_PATH}/$(DATE)_pgdump.sql
	cd $(HOME)/${BACKUP_PATH} && tar cvzf $(DATE)_pgdump.tgz $(DATE)_pgdump.sql

backup: check-backup-path backup-pg

and to restore, a the corresponding make restore

setup-pg-pass:
	# prepare credentials
	echo "#hostname:port:database:username:password" > .pgpass
	echo cci_postgres:${POSTGRES_PORT}:*:${POSTGRES_USER}:${POSTGRES_PASSWORD} >> .pgpass
	chmod 600 .pgpass
	cat .pgpass

reset-pg: setup-pg-pass
	# drop and create again DB
	docker run --rm --network=cci_backend -e PGPASSFILE=/.pgpass -v ${PWD}/.pgpass:/.pgpass laradock_postgres \
		dropdb -w -h cci_postgres -U ${POSTGRES_USER} ${POSTGRES_DB} || true
	docker run --rm --network=cci_backend -e PGPASSFILE=/.pgpass -v ${PWD}/.pgpass:/.pgpass laradock_postgres \
		createdb -w -h cci_postgres -U ${POSTGRES_USER} ${POSTGRES_DB}

get-pg-dump:
	# TODO: verify if the directory $(HOME)/${BACKUP_PATH}/ exists.
	# use DATE var to set another date than today
	scp -P ${SRC_PORT} ${SRC_USERNAME}@${SRC_IP}:${BACKUP_PATH}/$(DATE)_pgdump.tgz $(HOME)/${BACKUP_PATH}/
	cd $(HOME)/${BACKUP_PATH} && tar xvzf $(DATE)_pgdump.tgz

restore-pg-db:
	# copy and restore PG data
	docker run --rm --network=cci_backend -e PGPASSFILE=/.pgpass -v ${PWD}/.pgpass:/.pgpass -v $(HOME)/${BACKUP_PATH}/$(DATE)_pgdump.sql:/data/dump.sql laradock_postgres \
		psql -w -h cci_postgres -U ${POSTGRES_USER} -f /data/dump.sql

restore: get-pg-dump reset-pg restore-pg-db

Please let me know if you find this useful and can adpat it to your fastapi project, I am curious to know how much change is required to make it work with docke swarm

[pschelle]

Hello @ebreton,

Thanks for replying to my post.
I'll need to look into this in more detail, but in general I see what you're doing.

It's the first time I've seen using MakeFile to do the scripting. I guess that's a personal preference to use the tooling you feel most comfortable with.

I'm not sure yet what route to go yet. I might take a look into ansible as well to see if there's any common practice there to backup container databases and data.

Anyhow, if nothing else pops up, I may be starting from your input.

It would be interesting to know @tiangolo's view on this. Or at least some pointers in the right direction.

[tiangolo]

Thanks for your help here @ebreton !

@pschelle you can add another volume, in the Docker Compose file for volumes, and mount it, let's say, at /backup. Then you can do a docker exec with pgdump and outputting the dump to some file inside that /backup.

Then you can copy that file with docker cp.

Or, if you have an external volume or something similar, where you want to store the backup directly (instead of a Docker managed volume), you can mount it directly.

Let's say, like /media/user/external-volume:/backup. Then you would be able to get the file directly in your host from /media/user/external-volume (of course, adapting to wherever you have your volume on).

[ebreton]

Thanks a lot for the feedback @pschelle , I will also check your findings, much more elaborated than my Makefile 😁

[blueyed]

Regarding backup/restore with docker-compose I've just came across this: https://github.com/yourlabs/compoctl#compoctl-backup

[tiangolo]

@pschelle very interesting discussion! I remember implementing something similar for a project some time ago.

That also deserves a blog post, just saying 😉

I would also like to add a named volume for backups, and some docs for how to do it with commands. Not adding more automation stuff, just the simple docs, so that others can easily use it if needed, or update it to their needs.

But that will be a bit later (unless someone writes a PR with it before I do 😁 ).