Postgresql

Dump and restore commands for PostgreSQL in Docker

postgresql, docker | Comments

Sometimes during development it’s necessary to share the db dump with colleagues. In the previous article i provided the commands for MySQL, here is the same for PostgreSQL.

DATE = $(shell date +%Y-%m-%d)
path ?= .

help: ## show this help
	@echo 'usage: make [target] ...'
	@echo ''
	@echo 'targets:'
	@egrep '^(.+)\:\ .*##\ (.+)' ${MAKEFILE_LIST} | sed 's/:.*##/#/' | column -t -c 2 -s '#'

dump: ## dump db, usage 'make dump path=/path/to/dumps'
	docker-compose exec --user postgres db pg_dumpall --clean | gzip > $(path)/project-$(DATE).sql.gz

restore: ## restore db from dump file, usage 'make restore dump=dump.sql.gz'
	docker-compose stop web
	gunzip -c $(dump) | docker exec -i --user postgres `docker-compose ps -q db` psql
	docker-compose start web

Makefile help target

makefile, postgresql | Comments

In many projects to run complex commands or group of commands I use Makefile. To document it I add a help target.

Here is an exemple for dump and restore commands for PostgreSQL in docker-compose under alias db.

DATE = $(shell date +%d%m%Y)
path ?= .

help: ## show this help
	@echo 'usage: make [target] ...'
	@echo ''
	@echo 'targets:'
	@egrep '^(.+)\:\ .*##\ (.+)' ${MAKEFILE_LIST} | sed 's/:.*##/#/' | column -t -c 2 -s '#'

dump: ## dump db, usage 'make dump path=/path/to/dumps'
	docker-compose exec --user postgres db pg_dumpall --clean | gzip > $(path)/db_$(DATE).sql.gz
restore: ## restore db from dump file, usage 'make restore dump=dump.sql.gz'
	docker-compose stop web
	gunzip -c $(dump) | docker exec -i --user postgres `docker-compose ps -q db` psql
	docker-compose start web

So make or make help outputs:

usage: make [target] ...

targets:
help         show this help
dump         dump db, usage `make dump path=/path/to/dumps`
restore      restore db from dump file, usage `make restore dump=dump.sql.gz`

© 2019 Andrey Zhukov's Tech Blog.