docker-compose: accessing postgres' shell (psql)

Question

I'm trying to access PostgreSQL's shell (psql) using docker-compose, but I'm having some difficulties... Here's my docker-compose file:

main:
  build: .
  volumes:
    - .:/code
  links:
    - postgresdb
  environment:
    - DEBUG=true


postgresdb:
  build: utils/sql/
  ports:
    - "5432"
  environment:
    - DEBUG=true

I've tried to access psql by going through the main as well as the postgresdb services, by running

docker-compose run postgresdb psql -h postgresdb -U docker mydatabase

but all I get is psql: could not translate host name "postgresdb" to address: Name or service not known... which I don't understand because I use postgresdb as the host in the database configuration file e.g.:

DB_ACCESS = {
    'drivername': 'postgres',
    # Name of docker-compose service
    'host': 'postgresdb',
    'port': '5432',
    'username': 'docker',
    'password': '',
    'database': 'mydatabase'
}

Answer 1

Things have changed a bit since the question was originally asked.

Here's how you can access PostgreSQL's shell (psql) using docker-compose today:

1. Use a docker-compose.yml version '2' config file
2. Use 'depends_on' rather than 'links' (new to version '2')
3. Break up your containers using 'services'

docker-compoose.yml

version: '2'
services:      
   postgresdb:
     build: utils/sql/
     ports:
       - "5432"
     environment:
       - DEBUG=true
   main:
     build: .
     volumes:
       - .:/code
     depends_on:
       - postgresdb
     environment:
       - DEBUG=true

In your docker engine shell ...

1. Find the container id (or container name) from the docker ps command
2. Use that id to open a shell prompt
3. Switch user to the postgres user account
4. Run psql

See example below:

                        ##         .
                  ## ## ##        ==
               ## ## ## ## ##    ===
           /"""""""""""""""""\___/ ===
      ~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ /  ===- ~~~
           \______ o           __/
             \    \         __/
              \____\_______/
 _                 _   ____     _            _
| |__   ___   ___ | |_|___ \ __| | ___   ___| | _____ _ __
| '_ \ / _ \ / _ \| __| __) / _` |/ _ \ / __| |/ / _ \ '__|
| |_) | (_) | (_) | |_ / __/ (_| | (_) | (__|   <  __/ |
|_.__/ \___/ \___/ \__|_____\__,_|\___/ \___|_|\_\___|_|
Boot2Docker version 1.11.2, build HEAD : a6645c3 - Wed Jun  1 22:59:51 UTC 2016
Docker version 1.11.2, build b9f10c9
docker@default:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
b563764171e2        poc_web             "bundle exec rails s "   43 minutes ago      Up 43 minutes       0.0.0.0:3000->3000/tcp   poc_web_1
c6e480b0f26a        postgres            "/docker-entrypoint.s"   49 minutes ago      Up 49 minutes       0.0.0.0:5432->5432/tcp   poc_db_1
docker@default:~$ docker exec -it c6e480b0f26a sh
# su - postgres
No directory, logging in with HOME=/
$ psql
psql (9.5.3)
Type "help" for help.

postgres=#

Notes:

1. docker-compose up will start services in dependency order

2. If you want to stand up a postgres container using the official image, you docker-compose.yml might look more like the following:

   version: '2'
   services:      
     db:
       image: postgres
       ports:
         - "5432:5432"
   . . .
   

References

https://docs.docker.com/compose/compose-file/

Answer 2

Let's start with a minimal docker-compose.yml file for reference:

version: "3"

services:
  postgres:
    image: postgres:9.5

You bring your services up with docker-compose up.

Let's assume you have a database you want to connect to called test.

Answer: docker-compose run --rm postgres psql -h YOUR_SERVICE -U YOUR_USER -d YOUR_DATABASE

The --rm option removes the container after exit, so you don't create unnecessary duplicate containers just for accessing the database.

In our example, this would be docker-compose run --rm postgres psql -h postgres -U postgres -d test

Alternatively, you could use psql's connection string: docker compose run --rm postgres psql -d postgres://YOUR_USER@YOUR_SERVICE/YOUR_DATABASE

Answer 3

You're trying to connect the Postgresdb container from itself, but container is not aware about the alias postgresdb that you have set up inside your main container.

Consider the following example:

$ docker run -it --rm ubuntu hostname --fqdn
817450b29b34

Now you see that Postgresdb doesn't know how to resolve postgresdb. But postgresdb should be resolvable from main anyway.

You have several opportunities to fix this:

1. Try to set hostname for Postgresdb explicitly (in your docker-compose.yml);

2. Access the Postgresdb from main container in such way:

   $ docker exec -it main_container_full_name psql -h postgresdb -U docker mydatabase
   

Answer 4

The following worked for me:

in docker-compose.yaml

services:
  db:
    container_name: db
    image: postgres

then, after running docker-compose up

I can run

sudo docker exec -it -u postgres db psql
postgres=#

to get the shell

Notes:

1. I'm using docker-compose version 3
2. I've given the psql container a name (db). Which allowed me to avoid running docker ps to get the container_id
3. the -u postgres allows me to access the container as the postgres superuser

Answer 5

2023:

docker-compose up -d <service-name>
docker-compose exec -u postgres <service-name> psql

Voilá!