pg_restore to postgres running in docker container

Question

I have a backup of a database that I would to restore to a postgres database running inside a docker container.

I'm using docker-machine on OS X.
Postgres image is postgres:9.4.

This is the script I've come up with so far:

pg_restore --verbose --clean --no-acl --no-owner \
  -h tcp://`docker-machine ip default`:5432 \
  -U postgres \
  -d tonsser-api_development latest.dump

But that doesn't work. I get the error:

pg_restore: connecting to database for restore
pg_restore: [archiver (db)] connection to database "tonsser-api_development" failed: could not translate host name "tcp://192.168.99.100:5432" to address: nodename nor servname provided, or not known

Answer 1

There seems to be no proper solution to do this on runtime, however copying the dumpfile to the container:

docker cp dumpfile DBcontainer:/dumpfile

and restore from within the container:

docker  exec -i -t DBcontainer /bin/bash
psql DBname < dumpfile

worked well for a one time use...

Answer 2

You have to use a connection string, e.g. $DATABASE_URL

The following works well for me:

docker-compose exec fooapp pg_restore 
  --verbose --clean --no-acl --no-owner 
  -d $DATABASE_URL foo.dump

For extra points, you can create a rake task (assuming you're using a Rails app), as follows:

namespace :db do
  desc 'Import a given file into the database'
  task :import, [:path] => :environment do |_t, args|
    dump_path = args.path
    system 'pg_restore --verbose --clean --no-acl --no-owner -d $DATABASE_URL '\
    + dump_path
  end
end

Put that file in lib/tasks, then you can call something like:

docker-compose exec fooapp bundle exec rails db:import[foo.dump]