152

I would like to use the psql in the postgres image in order to run some queries on the database. But unfortunately when I attach to the postgres container, I got that error the psql command is not found...

For me a little bit it is a mystery how I can run postgre sql queries or commands in the container.

How run the psql command in the postgres container? (I am a new guy in Docker world)

I use Ubuntu as a host machine, and I did not install the postgres on the host machine, I use the postgres container instead.

docker-compose ps
        Name                       Command               State               Ports            
---------------------------------------------------------------------------------------------
yiialkalmi_app_1        /bin/bash                        Exit 0                               
yiialkalmi_nginx_1      nginx -g daemon off;             Up       443/tcp, 0.0.0.0:80->80/tcp 
yiialkalmi_php_1        php-fpm                          Up       9000/tcp                    
yiialkalmi_postgres_1   /docker-entrypoint.sh postgres   Up       5432/tcp                    
yiialkalmi_redis_1      docker-entrypoint.sh redis ...   Up       6379/tcp

Here the containers:

docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                         NAMES
315567db2dff        yiialkalmi_nginx    "nginx -g 'daemon off"   18 hours ago        Up 3 hours          0.0.0.0:80->80/tcp, 443/tcp   yiialkalmi_nginx_1
53577722df71        yiialkalmi_php      "php-fpm"                18 hours ago        Up 3 hours          9000/tcp                      yiialkalmi_php_1
40e39bd0329a        postgres:latest     "/docker-entrypoint.s"   18 hours ago        Up 3 hours          5432/tcp                      yiialkalmi_postgres_1
5cc47477b72d        redis:latest        "docker-entrypoint.sh"   19 hours ago        Up 3 hours          6379/tcp                      yiialkalmi_redis_1

And this is my docker-compose.yml:

app:
image: ubuntu:16.04
volumes:
    - .:/var/www/html

nginx:
    build: ./docker/nginx/
    ports:
        - 80:80
    links:
        - php
    volumes_from:
        - app
    volumes:
        - ./docker/nginx/conf.d:/etc/nginx/conf.d

php:
    build: ./docker/php/
    expose:
        - 9000
    links:
        - postgres
        - redis
    volumes_from:
        - app

postgres:
    image: postgres:latest
    volumes:
        - /var/lib/postgres
    environment:
        POSTGRES_DB: project
        POSTGRES_USER: project
        POSTGRES_PASSWORD: project

redis:
    image: redis:latest
    expose:
        - 6379
Manfred Radlwimmer
  • 13,257
  • 13
  • 53
  • 62
Dabagab
  • 2,497
  • 4
  • 26
  • 31

11 Answers11

250
docker exec -it yiialkalmi_postgres_1 psql -U project -W project

Some explanation

  • docker exec -it The command to run a command to a running container. The it flags open an interactive tty. Basically it will cause to attach to the terminal. If you wanted to open the bash terminal you can do this

docker exec -it yiialkalmi_postgres_1 bash

  • yiialkalmi_postgres_1 The container name (you could use the container id instead, which in your case would be 40e39bd0329a )

  • psql -U project -W project The command to execute to the running container

  • U user

  • W Tell psql that the user needs to be prompted for the password at connection time. This parameter is optional. Without this parameter, there is an extra connection attempt which will usually find out that a password is needed, see the PostgreSQL docs.

  • project the database you want to connect to. There is no need for the -d parameter to mark it as the dbname when it is the first non-option argument, see the docs: -d "is equivalent to specifying dbname as the first non-option argument on the command line."

These are specified by you here

environment:
    POSTGRES_DB: project
    POSTGRES_USER: project
    POSTGRES_PASSWORD: project
questionto42
  • 7,175
  • 4
  • 57
  • 90
Alkis Kalogeris
  • 17,044
  • 15
  • 59
  • 113
  • Thank you very much it works. I tried with this always: `docker exect it 40e39bd0329a /bin/bash` and then in the container run the `psql` command, but it always gave me errors. – Dabagab May 08 '16 at 13:39
  • No need to run `/bin/bash` as it is already in the path. If you run `docker exec -it 40e39bd0329a bash` and then when you in the container's bash shell, you execute `psql -U project -W project project` it should work. If you execute only `psql` you should get an error about `role not found` which is postgres' way of informing you that the default user (in this case I think it's `root`) doesn't exist in postgres' specified users – Alkis Kalogeris May 08 '16 at 15:50
  • 1
    To be exact you must be getting `psql: FATAL: role "root" does not exist` where user that you are connecting as, to the container (`root@:/#`) – Alkis Kalogeris May 08 '16 at 15:56
  • Hi, thanks for your answer, I'm trying to restore a database using that command but I'm not sure is is ok, any help? docker exec avm_db_1 psql -U postgres < mydatabase.back – joselo Nov 25 '16 at 18:16
  • @joselo I haven't done this before, but I'm confident it's not that hard. Please open a new thread posting your question. That way more people will see your question. The comments are not a good place to ask a new question. – Alkis Kalogeris Nov 25 '16 at 21:18
  • May need to use `docker exec -it yiialkalmi_postgres_1 sh -c "psql -U project -W project project"` instead. – Vivek Kodira Aug 24 '17 at 12:32
  • 2
    `docker-compose exec postgres psql -U project -W project` works too if you have a running container (`docker-compose up postgres`). – gabe Nov 28 '17 at 02:24
  • How to set the hostname? – kamal Mar 06 '18 at 07:05
  • i get ```"could not connect to server: no such file or directory"``` seems to be looking in ```/var/run/postgresql/``` which does not exist – Sonic Soul Feb 11 '19 at 15:09
  • If its just one command, you could put it straight there as psql parameter `--command="CREATE extension whatever"` – jave.web Sep 12 '19 at 14:42
  • 3
    After running the above command, I was getting `psql: warning: extra command-line argument "project" ignored` For me this worked, `docker exec -it psql -U -W ` – Karthik Rao Dec 06 '19 at 03:32
  • Above command is what worked for me as well. – alex Mar 16 '21 at 16:29
  • @KarthikRao Right, `-W` does not need a value after it. The first non-option parameter stands for the dbname, which is why the double project is just wrong. I am just editing this. – questionto42 Aug 26 '21 at 17:50
39

This worked for me:

goto bash :

docker exec -it <container-name> bash

from bash :

psql -U <dataBaseUserName> <dataBaseName>

or just this one-liner :

docker exec -it  <container-name> psql -U <dataBaseUserName> <dataBaseName>

helps ?

Alferd Nobel
  • 3,185
  • 2
  • 30
  • 35
  • This works! Why doesn't it need the password though? Doing the answer by @Alkis Kalogeis with -W "your password" confuses postgresql and it thinks the password i put in is the data base – Sheng Aug 14 '21 at 16:11
26

After the Postgres container is configured using docker, open the bash terminal using:

docker exec -it <containerID>(postgres container name / ID) bash

Switch to the Postgres user:

su - postgres

Then run:

psql

It will open the terminal access for the Postgres.

Michal Karbownik
  • 1,138
  • 14
  • 28
Chinnu
  • 261
  • 3
  • 2
21

If you need to restore the database in a container you can do this:

docker exec -i app_db_1 psql -U postgres < app_development.back

Don't forget to add -i.

:)

questionto42
  • 7,175
  • 4
  • 57
  • 90
joselo
  • 681
  • 1
  • 7
  • 9
11

You can enter inside the postgres container using docker-compose by typing the following

docker-compose exec postgres bash

knowing that postgres is the name of the service. Replace it with the name of the Postgresql service in you docker-compose file.

if you have many docker-compose files, you have to add the specific docker-compose.yml file you want to execute the command with. Use the following commnand instead.

docker-compose -f < specific docker-compose.yml> exec postgres bash

For example if you want to run the command with a docker-compose file called local.yml, here the command will be 

docker-compose -f local.yml exec postgres bash

Then, use psql command and specify the database name with the -d flag and the username with the -U flag

psql -U <database username you want to connect with> -d <database name>

Baammm!!!!! you are in.

Soviut
  • 88,194
  • 49
  • 192
  • 260
thierno
  • 914
  • 11
  • 10
5

If you have running "postgres" container:

docker run -it --rm --link postgres:postgres postgres:9.6 sh -c "exec psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres"
Vojtech Vitek - golang.cz
  • 25,275
  • 4
  • 34
  • 40
3

We can enter the container with a terminal sh or bash by using,

docker run -it <container id | name> <sh | bash>

if assume it is sh,

psql -U postgres

will work

Nuwa
  • 461
  • 4
  • 10
3

Just fired up a local test, not sure if -c is what you were after from the cli.

docker run -it --rm --name psql-test-connection -e PGPASSWORD=1234 postgres psql -h kubernetes.docker.internal -U awx -c "\conninfo"

You are connected to database "awx" as user "awx" on host "kubernetes.docker.internal" (address "192.168.65.4") at port "5432".
b0bu
  • 1,062
  • 1
  • 9
  • 24
2
RUN /etc/init.d/postgresql start &&\
    psql --command "CREATE USER docker WITH SUPERUSER PASSWORD 'docker';" &&\
    createdb -O docker docker &&\
vijay
  • 10,276
  • 11
  • 64
  • 79
  • 8
    While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Pleast take some time to read [answer]. Remember that you are answering the question for readers in the future and those people might not know the reasons for your code suggestion – Simply Ged Jan 08 '19 at 05:01
2

In many common setups, the PostgreSQL port is published out to the host.

postgres:
  ports:
    - '12345:5432'

If this is the case, you don't need to do anything Docker-specific to connect to the database. You can use the psql client directly on your host system pointing to the first ports: number.

psql -h localhost -p 12345 -U project

This approach only requires psql or another ordinary PostgreSQL client be installed on the host and that the database container be configured with ports: making it accessible from outside Docker. (The ports: are not necessary for inter-container communication and a production-oriented setup could reasonably not have them.) This does not require the ability to run docker commands and the attendant security concerns, and it can avoid multiple layers of additional command quoting from a docker exec sh -c '...' sequence.

David Maze
  • 130,717
  • 29
  • 175
  • 215
1

Without using an external terminal a person can run SQL commands within the container CLI.

psql -d [database-name] -U [username] -W

** Don't forget to replace [database-name] with your db-name & [username] with your actual username

Flags:

-d : Specify the database name you want to connect
-U : Specify the username as whom you want to connect
-W : Prompt for the password
iam_anirban
  • 91
  • 1
  • 9