DOCKER-backup & restore pgsql

• SYSADMIN

Restoring postgresql data in docker

You finally have your application running in Docker but at some point you find yourself in the position where you have to restore some database data to your postgresql container

The docker-compose.yml

I have this docker-compose.yml

version: '3.2'
volumes:
  postgres-data:
services:
  db:
    image: postgres
    volumes:
      - postgres-data:/var/lib/postgresql/data
  app:
    build:
      context: .
      dockerfile: Dockerfile
    command: bundle exec rails s -p 3000 -b '0.0.0.0'
    volumes:
      - .:/app
    ports:
      - "3000:3000"
    depends_on:
      - db

This starts PostgreSQL and rails, nothing fancy about this. But I needed to import some data from the staging server into dev and since I will most likely forget about this until next time, why not document it in a blog post?

Creating the psql backup

First dump the data from the staging server, this is quite easy with pg_dump

pg_dump foo_development > foo_development.sql

Fix data in the dump

The psql dump also contains statements like these where foo_development is the user that is used on staging.

ALTER TABLE users OWNER TO foo_development;

You can now either change your database.yml / docker env settings to create such a user in your docker environment or you can just fix the data in the sql dump. Assuming your docker PostgreSQL user will be postgres here is how to change it with good old sed

sed -i.bak -e s/foo_development/postgres/g foo_development.sql

Import the data

First, make sure that everything is up by running docker compose up also make sure you have no data in the database and that the database exists, the best thing here is to just drop and recreate the database.

docker-compose exec db dropdb foo_development -U postgres
docker-compose exec db createdb foo_development -U postgres

and now import the data from the dump

cat foo_development.sql | docker-compose exec -T db psql -d foo_development -U postgres

It's important to use exec instead of run otherwise postgresql won't be running and you can not import anything. Also, use the T parameter or you will get an error: the input device is not a TTY