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

User · Answer

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"**

which will add accessible port 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.

User · Answer

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  lt  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

User · Answer

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  lt host port gt   lt container port gt    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- gt 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

User · Answer

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 dont have persistant 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

User · Answer

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

User · Answer

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  lt my-user gt  -d  lt my-database gt

User · Answer

first open the docker image  for the postgres  docker exec -it  lt container name gt    then u will get the root --root 868594e88b53    it need the database connection   psql postgresql    lt username gt   lt databasepassword gt  postgres 5432  lt database gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In case  it is a django backend application  you can do something like this   docker exec -it container id python manage py dbshell

User · Answer

I managed to get it run on linux  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  quot 1234 quot  --name pg postgres alpine   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

User · Answer

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

[postgresql] Connecting to Postgresql in a docker container from outside

Examples related to postgresql

Examples related to docker

Examples related to remote-connection