How to get into psql of a running postgres container?

Question

I created a postgres container using the tutorial on the fig website. I named the container db.

The container is running and my app connects to it fine. I tried to run the command fig run db psql with the db container running and got the error:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

How can I get into the psql interface in the running db container?

Sita

Answer 1

fig will create a docker container with a different name than the one used in the fig.yml file.

I got it working by finding the container name with docker ps and looking at the NAMES column.

Then running the psql command in the running container with docker exec -ti NAME_OF_CONTAINER psql -U YOUR_POSTGRES_USERNAME

Important Note:

• docker exec runs the psqlcommand on a running container
• docker run will start a new container.

---

Update

fig is now called docker-compose

Sita

Good answer! It's very important to distinguish between docker run <image> and docker exec <container>. You may also just docker exec -it /bin/bash for development. – Crumble

Need to dump data, can't go docker exec -it – Maudemaudie

may also need to mention the database name like this: docker exec -ti NAME_OF_CONTAINER psql -U YOUR_POSTGRES_USERNAME DATABASE – Sonora

This solution might work, but it answers the more specific question set out in the body of the answer, while the title of the question focuses on a more general question. I believe answers that are more general are of greater value to Stackoverflow. – Jerroldjerroll

Answer 2

Instead of connecting with:

psql

Use the -h and -p option:

psql -h localhost -p 5432

Why did that work?

If we run a local psql without arguments (or with incorrect arguments), psql will try to connect via a unix socket instead of tcp, because this is a tad more efficient.

However, psql's micro optimization doesn't work for our quirky setup, because our container's file system is separate by design. Even if it weren't, psql wouldn't know where to look for the socket file.

The solution is not writing a burly docker exec command, nor is it mounting a volume so that our local psql instance can find the socket in the container, but to move the whole interaction to tcp.

Sure this is slightly less efficient, but the very reason we are using a container is so that things work even if they are setup in different computers, TCP is what docker containers use to communicate between processes, whether in the same machine or not. .

To tell psql that we want to connect via TCP, we use the -h option to identify the (virtual) machine, along with -p to identify postgresql process, 5432 being the default.

psql -h localhost -p 5432

psql could try to use these default parameters before giving up when given no arguments, but it choses to fail early, issue an error message and let the admin work out the connection details theirself. More information about this decision can be found in https://www.postgresql.org/message-id/20191217141456.GA2413%40elch.exwg.net

Jerroldjerroll

implies that a psql client is installed on the local machine, but clean and logical solution to my opinion – Ludendorff

Well it for sure should not be installed in the actual server. – Jerroldjerroll

Answer 3

My 2P here; I prefer to have middlewares deployed as containers, instead of install and maintain them in the host system:

docker run --rm --name postgresql -p 5432:5432
-e POSTGRES_USER=admin -e POSTGRES_PASSWORD=admin
-e POSTGRES_DB=demodb
-d postgres:latest
docker exec -it postgresql psql -d demodb -U admin

Contrivance

it works for me docker exec -it <docker_container_name> /bin/bash and psql -d <postgres_db_name> -U <postgres_password> – Recondite

What if the container doesn't have a psql client? Which is a sensible decision. – Jerroldjerroll

I'm actually using third party clients (DBeaver, not to name it). – Contrivance

Answer 4

You need to run a new container to connect to the one started by fig. This is so because the main container by default starts the service, and if you do fig run db psql fig will NOT start the service but run the psql client instead. See the Dockerfile.

So to connect to the PostgreSQL service you need to run another container linked to the one started by fig. See https://registry.hub.docker.com/_/postgres/.

First, since fig changes the names of the containers started, check the NAMES column of the docker ps container after having done fig up. Then:

docker run -it --link <postgres_container_name>:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

You can do the docker exec trick as well as desribed by @sargas too, but the linking way sounds more canonical to me.

Opaque

The --link flag is a deprecated legacy feature of Docker: docs.docker.com/engine/userguide/networking/default_network/… – Embryology

Answer 5

Can you post the result of docker ps? My guess is you need to specify the port the postgres container is exposing. Running docker ps should give you

CONTAINER ID        IMAGE                          COMMAND                CREATED             STATUS              PORTS                    NAMES
948b1f6ebc0a        my_postgres:latest            "/usr/lib/postgresql   6 days ago          Up 6 days           0.0.0.0:49155->5432/tcp   db

and looking under the PORTS column for your db container you'll see the port the db is actually exposed on. In this case it's 49155, but docker will choose a random port between 49153 and 65535 if not explicitly specified at container start. You need to supply the -p option to psql to then target that port as such

psql -p 49155 ... 

Source: https://docs.docker.com/userguide/dockerlinks/

Plenary

For me I'll have to run psql -h localhost -p 54322 -u postgres postgres – Gaytan

Answer 6

2024 and this is still one of the top results from SO.

First, install psql on your host machine.

The simplest way to psql into your db (it doesn't matter if your db is running in a container or directly in your machine) is:

psql -h YOUR_HOST -p PORT -U POSTGRES_USER DB_NAME

For example, if you have a database called demo which is accessible on localhost port 5443 and your postgress user for the db is myuser, the pqsl command will be:

psql -h localhost -p 5443 -U myuser demo

Once you run this on your terminal, it will ask you for the postgres user password; input the password and you're good.

For the above example, if the password is superpass the postgres connection string will be:

postgresql://myuser:superpass@localhost:5443/demo

Gleeman

That'll only work a) if you have installed psql on your machine and b) exposed the container ports – Rhenium

Answer 7

It works for me docker exec + psql:

docker exec -it CONTAINER_NAME psql -v ON_ERROR_STOP=1 --username=postgres --dbname=postgres -a -f /inserts/0001-inserts.sql

Before start you need to copy this script to image for example :

  database:
    container_name: CONTAINER_NAME
    image: postgres:12-alpine
    restart: no
    volumes:
      - ./volumes/database/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - ./volumes/database/inserts:/inserts

Note that everything in folder docker-entrypoint-initdb.d will be populated on startup but /inserts you run manually later .

Brittne