I have this
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 -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
That's it! you are done!