从外部连接到 docker 容器中的 Postgresql

Question

提问by Sojo

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?

我在 docker 容器中的服务器上有 Postgresql。我如何从外部连接到它，即从我的本地计算机？我应该应用什么设置来允许它？

Answer 1

回答by lvthillo

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

您可以通过这种方式运行 Postgres（映射端口）：

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

所以现在您已经将容器的端口 5432 映射到服务器的端口 5432。-p <host_port>:<container_port>.所以现在你的 postgres 可以从你的public-server-ip:5432

To test: Run the postgres database (command above)

测试：运行 postgres 数据库（上面的命令）

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 客户端）。

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

(password mysecretpassword)

（密码 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.

因此，您正在从本地主机访问数据库（在服务器上的 docker 中运行）。

In this postit's expained in detail.

在这篇文章中，它被详细解释。

Answer 2

回答by Thomas Webber

I managed to get it run on linux

我设法让它在 linux 上运行

run the docker postgres - make sure the port is published, I use alpine because it's lightweight.
sudo docker run --rm -P -p 127.0.0.1:5432:5432 -e POSTGRES_PASSWORD="1234" --name pg postgres:alpine
using another terminal, access the database from the host using the postgres uri
psql postgresql://postgres:1234@localhost:5432/postgres

运行 docker postgres - 确保端口已发布，我使用 alpine，因为它是轻量级的。
sudo docker run --rm -P -p 127.0.0.1:5432:5432 -e POSTGRES_PASSWORD="1234" --name pg postgres:alpine
使用另一个终端，使用 postgres uri 从主机访问数据库
psql postgresql://postgres:1234@localhost:5432/postgres

for mac users, replace psql with pgcli

对于 mac 用户，用 pgcli 替换 psql

Answer 3

回答by SuperNova

You can also access through docker exec command by:

您还可以通过 docker exec 命令通过以下方式访问：

$ docker exec -it postgres-container bash

# su postgres

$ psql

Or

或者

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

Answer 4

回答by Eugene

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:

我已经在主机上运行了 postgres 并且不想允许来自网络的连接，所以我确实在容器中运行了临时 postgres 实例并仅用两行创建了数据库：

# 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

Answer 5

回答by omeraiman

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

我在 Docker 容器中使用带有 postgres 的 django。在 docker-compose 文件中，添加以下内容：

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.

这将通过您的本地机器添加可访问的端口。对于我自己，我将 DBeaver 连接到它。这将防止您的应用程序请求和本地机器请求之间的端口冲突。起初，我收到一条消息，说 5432 端口正在使用中（由 django 应用程序提供），所以我无法通过 pgAdmin 或 DBeaver 访问。

Answer 6

回答by user2627846

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

要从本地主机连接，您需要添加“--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:

您可以使用以下命令直接访问服务器，而无需从本地主机使用 exec：

psql -h localhost -p 5432 -U postgres

Answer 7

回答by Martin

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

我试图从 localhost (mac) 连接到 postgres 容器。我将 docker-compose 文件中的端口从 5432 更改为 3306 并启动了容器。不知道我为什么这样做:|

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

然后我尝试通过 PSquel 和 adminer 连接到 postgres，但无法建立连接。

After switching back to port 5432 all works fine.

切换回端口 5432 后一切正常。

  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.

这是我想分享的经验。也许有人可以利用它。

Answer 8

回答by Martin

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

我假设您希望每次从外部连接到容器时都能够查看容器中存在的数据。为此，您必须在 postgres 映像上保留数据。

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

如果您没有持久数据，您将不得不重复您第一次所做的一切。
步骤 3、5、6、7 和 8直接回答您的问题。

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):

以下是我在 Windows 10 powershell 上遵循的整个过程的详细概述（Linux 和 macOS 中的命令也相同）：

Step 1: Start powershell in non-admin mode

第 1 步：以非管理员模式启动 powershell

Step 2: Download postgres docker image:
docker pull postgres:latest

第 2 步：下载 postgres docker 镜像：
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

第 3 步：以分离模式启动 docker 容器并通过创建卷并将其绑定到目标来将数据持久保存在 postgres 映像上
（注意：默认情况下 5432 是使用的默认端口；但明确声明它以防止来自客户端的连接错误，例如pgadmin、dbeaver 等）
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

第 4 步：检查正在运行的容器的状态
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

第5步：以交互模式进入container_name
（注意：如果您在安装过程中检查了linux容器，则可以在此处执行ls，pwd等命令）
docker exec -it postgres-test psql -U postgres

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

第 6 步：创建示例数据。此时，您可以psql通过以下方式使用命令：

# 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 pgadminor dbeaverand enter the below in the connection fields:

第 7 步：打开一个数据库客户端应用程序，如pgadmin或dbeaver并在连接字段中输入以下内容：

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

Step 8: Enter the query select * from test_tablein the query editor and you should be able to see the output 123

第 8 步：select * from test_table在查询编辑器中输入查询，您应该能够看到输出123

Answer 9

回答by Marc Perrin-Pelletier

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

出于某种原因，5432 端口似乎受到保护。我将端口配置从更改为5432:5432，5416:5432并且以下命令可以从其 docker 容器外部连接到您的 postgres 数据库：

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

Answer 10

回答by ashutosh gupta

first open the docker image for the postgres

首先打开postgres的docker镜像

docker exec -it <container_name>

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

然后你会得到根——root@868594e88b53:/#它需要数据库连接

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

从外部连接到 docker 容器中的 Postgresql

提问by Sojo

回答by lvthillo

回答by Thomas Webber

回答by SuperNova

回答by Eugene

回答by omeraiman

回答by user2627846

回答by Martin

回答by Martin

回答by Marc Perrin-Pelletier

回答by ashutosh gupta

相关推荐

最近更新

标签

从外部连接到 docker 容器中的 Postgresql

提问by Sojo

回答by lvthillo

回答by Thomas Webber

回答by SuperNova

回答by Eugene

回答by omeraiman

回答by user2627846

回答by Martin

回答by Martin

回答by Marc Perrin-Pelletier

回答by ashutosh gupta

相关推荐

如何连接 GraphQL 和 PostgreSQL

windows 如何强制删除文件？

哪些适用于 Windows 的 Subversion 客户端不需要安装？

如何在 POSTGRESQL 中从日期中提取年份

相关推荐

最近更新

标签