Connecting to Postgresql in a docker container from outside

Question

I have Postgresql on a server in a docker container. How can I connect to it from the outside, that is, from my local computer? What setting should I apply to allow that?

Nur

what command did you use to start the postresql? you're able to expose a port and map it – Ishtar

Refer this reachmnadeem.wordpress.com/2020/06/02/… – Psychoanalysis

Answer 1

You can run Postgres this way (map a port):

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

So now you have mapped the port 5432 of your container to port 5432 of your server. -p <host_port>:<container_port> .So now your postgres is accessible from your public-server-ip:5432

To test: Run the postgres database (command above)

docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
05b3a3471f6f        postgres            "/docker-entrypoint.s"   1 seconds ago       Up 1 seconds        0.0.0.0:5432->5432/tcp    some-postgres

Go inside your container and create a database:

docker exec -it 05b3a3471f6f bash
root@05b3a3471f6f:/# psql -U postgres
postgres-# CREATE DATABASE mytest;
postgres-# \q

Go to your localhost (where you have some tool or the psql client).

psql -h public-ip-server -p 5432 -U postgres

(password mysecretpassword)

postgres=# \l

                             List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 mytest    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres   

So you're accessing the database (which is running in docker on a server) from your localhost.

In this post it's expained in detail.

Ishtar

@Tjorriemorrie You're sure your postgres is running on your local machine? Maybe try 127.0.0.1 instead of localhost but for met it's working. – Ishtar

Get your public IP address (osx): ifconfig -u | grep 'inet ' | grep -v 127.0.0.1 | cut -d\ -f2 | head -1 – Dittany

Of all of the various postgres/docker related posts I have found this is one of the most useful. Thank you. – Acosmism

Is it possible to connect to postgres over docker without mapping a local port? For example with something like: psql -h some-internal-docker-host -p 5432 -U postgres? – Etty

@Etty if you don't want to map a port but still want to access the postgres container from your host you'll need to deploy your container on the host network like this: docker run --net=host --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres – Ishtar

docker exec -it 05b3a3471f6f bash @Ishtar at that line, don't need to write the SHA here, you can replace it as "some-postgres" because the container is named from the command above – Azilian

exiting psql works with \q (just for the newbies like me) – Papism

If you have a mac you can replace by psql -h docker.for.mac.localhost -p 5432 -U postgres. Both of those options works – Leahy

But this is access from localhost, by the point view of container. Using other words, you are inside the container and acessing the database, Ok it it useful, but how to connect from outside container? – Atalie

@Atalie you'll need to use your public ip address, I've used the one related to my Wireless LAN adapter retrieved via ipconfig and it's working :) – Wyne

Answer 2

I managed to get it run on linux

1. run the docker postgres - make sure the port is published, I use alpine because it's lightweight.

   docker run --rm -P -p 127.0.0.1:5432:5432 -e POSTGRES_PASSWORD="1234" --name pg postgres:alpine
   

2. using another terminal, access the database from the host using the postgres uri

   psql postgresql://postgres:1234@localhost:5432/postgres
   

for mac users, replace psql with pgcli

Delectate

glad that someone answered how to connect without jumping into the container. thnx. – Rorke

You really shouldn't use sudo to run your container. – Nannana

Answer 3

You can also access through docker exec command by:

$ docker exec -it postgres-container bash

# su postgres

$ psql

Or

$ docker exec -it postgres-container psql -U postgres

Eugine

psql -U postgres – Precambrian

What does su postgres do? – Ankerite

@Ankerite su postgres means: switch user to the postgres user. – Estop

WORKS amazing. First you need to get the container ID of your docker postgress, use the command "docker ps -a", then use the continerID with this command: docker exec -it container_ID psql -U postgres – Twicetold

psql after su postgres helped – Monaco

Answer 4

I am using django with postgres in Docker containers. in the docker-compose file, add the following:

db:
    image: postgres:10-alpine
    environment:
        - POSTGRES_DB=app
        - POSTGRES_USER=postgres
        - POSTGRES_PASSWORD=supersecretpassword
    ports:
        - "6543:5432"

This ports setting uses the port 6543 (it just needs to be different from 5432) that is accessible by your local machine. For myself, I connected DBeaver to it. this will prevent port clashes between your app request and local machine request.

At first, I got a message saying that the port 5432 is in use (which is by django app) so I couldn't access by pgAdmin or DBeaver.

Gestate

I found this to be the most helpful. For people uisng docker-compose, this seems like the best way to go. – Gobble

I can confirm this, also this seemingly strange setting of using a port number for the host that is different from the standard while the standard 5432 can be used further for the container at no harm. I had a Windows PostgreSQL installation which was occupying the port 5432 needed to reacht the Docker host, see Connect to dockerized postgres from Windows Docker host?. – Transposal

And then which IP do you use to connect? – Brinton

This worked for me. Port 5432 didn't work. Unsure why as the port is apparently not occupied or excluded on my computer, but alas, switching it to a random port I picked ended up being my solution. – Seismo

It's a bit surprising that the docker image can be built and run when mapping to a host port that has been connected to; this effectively means host can NOT connect to the postgres image – Keir

This one worked for me just fine. I've created postgres on docker using docker-compose. I've been trying different configurations to connect to it but this one worked. – Super

Answer 5

I'm assuming that you want to be able to view data present in your container everytime you connect to it from outside. To do this, you will have to persist data on the postgres image.

If you don't have persistent data, you will have to repeat everything you did the first time.
Steps 3, 5, 6, 7, and 8 answer your question directly.

Here is the detailed overview of the entire process I followed on Windows 10 powershell (commands are the same in Linux and macOS as well):

Step 1: Start powershell in non-admin mode

Step 2: Download postgres docker image:

docker pull postgres:latest

Step 3: Start docker container in detached mode and persist data on postgres image by creating a volume and binding it to a destination
(Note: by default 5432 is the default port that is used; but state it explicitly to prevent connection errors from clients like pgadmin, dbeaver, etc.)

docker run --name postgres-test -e POSTGRES_PASSWORD=password \
    -p 5432:5432 -v postgres-data:/var/lib/postgresql/data \
    -d postgres:latest

Step 4: Check status of running containers

docker ps -a

Step 5: Go inside container_name in interactive mode
(Note: commands like ls, pwd, etc. can be executed here if you've checked Linux containers during installation)

docker exec -it postgres-test psql -U postgres

Step 6: Create sample data. At this point, you can play with psql commands in the following manner:

# CREATE DATABASE test;
# \c test
# CREATE TABLE test_table(something int);
# INSERT INTO test_table VALUES (123);
# SELECT * FROM test_table;
# \q

Step 7: Open a database client application like pgadmin or dbeaver and enter the below in the connection fields:

Host: localhost
Database: test
User: postgres
Password: password

Step 8: Enter the query

SELECT * FROM test_table

in the query editor and you should be able to see the output 123.

Untwist

I've followed this exactly, but when I enter the details in dbeaver it says the test database doesn't exist – Marchpane

Answer 6

I know this is late, if you used docker-compose like @Martin

These are the snippets that helped me connect to psql inside the container

docker-compose run db bash

root@de96f9358b70:/# psql -h db -U root -d postgres_db

I cannot comment because I don't have 50 reputation. So hope this helps.

Romansh

Op wants to connect from outside. – Crain

@Crain this is just an alternative to what Ashutosh Gupta said https://mcmap.net/q/73377/-connecting-to-postgresql-in-a-docker-container-from-outside Should've commented there too. Not like I care but should be consistent in life right? – Romansh

@RishabhAnand well from now you will be :) – Camden

Answer 7

I already had running postgres on host machine and didn't want to allow connections from network, so I did run temporary postgres instance in container and created database in just two lines:

# Run PostgreSQL
docker run --name postgres-container -e POSTGRES_PASSWORD=password -it -p 5433:5432 postgres

# Create database
docker exec -it postgres-container createdb -U postgres my-db

Mallarme

if you want to create a single default database you can also add: -e POSTGRES_DB=my-db to create my-db instead of postgres – Duvalier

Answer 8

For some reason 5432 port seems protected. I changed my port config from 5432:5432to 5416:5432 and the following command worked to connect to your postgres database from outside its docker container:

psql -h localhost -p 5416 -U <my-user> -d <my-database>

Scarito

It´s works for me but I did not find an explanation on Internet. Did you find? – Fasces

@Fasces You are probably already running the postgres service on your host machine which will already bind to localhost:5432 preventing you from using it. Mapping a different host port to the default port 5432 inside the container is a good solution to that; alternatively you could stop the postgres service on your host but perhaps it's used for something you need. – Carryingon

worked with me after removing the port – Terranceterrane

Answer 9

Connect to a local container running postgres

1. Install psql

   brew search postgres
   brew install postgresql
   

2. Start the Docker

   docker run --name postgres -e POSTGRES_DB=users \
     -e POSTGRES_USER=john \
     -e POSTGRES_PASSWORD=password \
     -p 5432:5432 -d postgres
   

3. Connect to database

   psql --host=localhost --username=john --dbname=users
   

Polyhistor

that psql command does not work on windows. getting authentication failed error – Mastitis

Answer 10

To connect from the localhost you need to add '--net host':

docker run --name some-postgres --net host -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

You can access the server directly without using exec from your localhost, by using:

psql -h localhost -p 5432 -U postgres

Gentility

Answer 11

I tried to connect from localhost (mac) to a postgres container. I changed the port in the docker-compose file from 5432 to 3306 and started the container. No idea why I did it :|

Then I tried to connect to postgres via PSequel and adminer and the connection could not be established.

After switching back to port 5432 all works fine.

  db:
    image: postgres
    ports:
      - 5432:5432
    restart: always
    volumes:
      - "db_sql:/var/lib/mysql"
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: password
      POSTGRES_DB: postgres_db

This was my experience I wanted to share. Perhaps someone can make use of it.

Presser

Volume path: /var/lib/mysql ? – Decrescent

5432 is Postgres default port. 3306 is MySQL default port. If you change the published port in docker-compose then any client tools you try to use to connect will also default to trying to connect to port 5432 unless you tell them to use another port. – Carryingon

Answer 12

first open the docker image for the postgres

docker exec -it <container_name>

then u will get the root --root@868594e88b53:/# it need the database connection

psql postgresql://<username>:<databasepassword>@postgres:5432/<database>

Denman

Answer 13

This one worked for me:

PGPASSWORD=postgres psql -h localhost -p 3307 -U postgres -d postgres

Use the above to load an initial script as:

PGPASSWORD=postgres psql -h localhost -p 3307 -U postgres -d postgres < src/sql/local/blabla.sql

Do not that i remap my ports as:

docker run -p3307:5432 --name postgres -e POSTGRES_PASSWORD=postgres -d postgres

Egomania

Answer 14

In case, it is a django backend application, you can do something like this.

docker exec -it container_id python manage.py dbshell

Eugine

Answer 15

After building my gateway-microservice application i had the same issue. Can not to connect to contenerized postgresql from Heidisql.

At this moment i have solved it by simply specifying postgresql password to docker-compose.yml as well as port.

So you should find and open docker-compose.yml. Then you should enter POSTGRES_PASSWORD (don`t let it to be empty), and specify the port “5432:5432”

services: microservice33-postgresql: environment: - POSTGRES_USER=microservice33 - POSTGRES_PASSWORD=wwww - POSTGRES_HOST_AUTH_METHOD=trust ports: - 5432:5432

link for reference and screenshots post

Phosphorite

Answer 16

The answers in this thread helped me figure out how to this in Docker Desktop. Figure I would share that info. There are optional settings you can access when creating a new container and you can set the host port here.

When you go to your containers you should see value in the ports, that's how you know you can connect to the container from your local computer.

Lewan

Answer 17

There are good answers here but If you like to have some interface for postgres database management, you can install pgAdmin on your local computer and connect to the remote machine using its IP and the postgres exposed port (by default 5432).

Baronetcy

Answer 18

docker ps -a to get container ids then docker exec -it psql -U -W

Entomostracan