如何使用具有现有数据的 PostgreSQL 容器？

How to use a PostgreSQL container with existing data?

提问by Albert Tobac

I am trying to set up a PostgreSQL container (https://hub.docker.com/_/postgres/). I have some data from a current PostgreSQL instance. I copied it from /var/lib/postgresql/dataand want to set it as a volume to a PostgreSQL container.

我正在尝试设置一个 PostgreSQL 容器 ( https://hub.docker.com/_/postgres/)。我有一些来自当前 PostgreSQL 实例的数据。我从它复制/var/lib/postgresql/data并希望将其设置为 PostgreSQL 容器的卷。

My part from docker-compose.yml file about PostgreSQL:

我在 docker-compose.yml 文件中关于 PostgreSQL 的部分：

db:
    image: postgres:9.4
    ports:
        - 5432:5432
    environment:
        POSTGRES_PASSWORD: postgres
        POSTGRES_USER: postgres
        PGDATA : /var/lib/postgresql/data
    volumes:
        - /projects/own/docker_php/pgdata:/var/lib/postgresql/data

When I make docker-compose up I get this message:

当我制作 docker-compose up 时，我收到以下消息：

db_1  | initdb: directory "/var/lib/postgresql/data" exists but is not empty
db_1  | If you want to create a new database system, either remove or empty
db_1  | the directory "/var/lib/postgresql/data" or run initdb
db_1  | with an argument other than "/var/lib/postgresql/data".

I tried to create my own image from the container, so my Dockerfile is:

我试图从容器创建我自己的镜像，所以我的 Dockerfile 是：

FROM postgres:9.4
COPY pgdata /var/lib/postgresql/data

But I got the same error, what am I doing wrong?

但是我遇到了同样的错误，我做错了什么？

Update

更新

I got SQL using pg_dumpall and put it in /docker-entrypoint-initdb.d, but this file executes every time I do docker-compose up.

我使用 pg_dumpall 获取 SQL 并将其放在 /docker-entrypoint-initdb.d 中，但此文件每次执行时都会执行docker-compose up。

回答by johntellsall

To build on irakli's answer, here's an updated solution:

以伊拉克利的回答为基础，这里有一个更新的解决方案：

• use newer version 2 Docker Compose file
• separate volumessection
• extra settings deleted

• 使用较新的版本 2 Docker Compose 文件
• 单独的volumes部分
• 额外设置已删除

docker-compose.yml

docker-compose.yml

version: '2'

services:
  postgres9:
    image: postgres:9.4
    expose:
      - 5432
    volumes:
      - data:/var/lib/postgresql/data

volumes:
  data: {}

demo

演示

Start Postgres database server:

启动 Postgres 数据库服务器：

$ docker-compose up

Show all tables in the database. In another terminal, talk to the container's Postgres:

显示数据库中的所有表。在另一个终端中，与容器的 Postgres 对话：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'

It'll show nothing, as the database is blank. Create a table:

它不会显示任何内容，因为数据库是空白的。创建一个表：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c 'create table beer()'

List the newly-created table:

列出新创建的表：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'

                         Access privileges
 Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
 public | beer      | table |                   | 

Yay! We've now started a Postgres database using a shared storage volume, and stored some data in it. Next step is to check that the data actually sticks around after the server stops.

好极了！我们现在已经使用共享存储卷启动了一个 Postgres 数据库，并在其中存储了一些数据。下一步是检查服务器停止后数据是否确实存在。

Now, kill the Postgres server container:

现在，杀死 Postgres 服务器容器：

$ docker-compose stop

Start up the Postgres container again:

再次启动 Postgres 容器：

$ docker-compose up

We expect that the database server will re-use the storage, so our very important data is still there. Check:

我们期望数据库服务器会重新使用存储，因此我们非常重要的数据仍然存在。查看：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'
                         Access privileges
 Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
public | beer      | table |                   | 

We've successfully used a new-style Docker Compose file to run a Postgres database using an external data volume, and checked that it keeps our data safe and sound.

我们已成功使用新型 Docker Compose 文件通过外部数据卷运行 Postgres 数据库，并检查它是否能确保我们的数据安全无虞。

storing data

存储数据

First, make a backup, storing our data on the host:

首先，进行备份，将我们的数据存储在主机上：

$ docker exec -it $(docker-compose ps -q postgres9 ) pg_dump -Upostgres > backup.sql

Zap our data from the guest database:

从访客数据库中提取我们的数据：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c 'drop table beer'

Restore our backup (stored on the host) into the Postgres container.

将我们的备份（存储在主机上）恢复到 Postgres 容器中。

Note:use "exec -i", not"-it", otherwise you'll get a "input device is not a TTY" error.

注意：使用“exec -i”，而不是“-it”，否则你会得到“input device is not a TTY”错误。

$ docker exec -i $(docker-compose ps -q postgres9 ) psql -Upostgres < backup.sql

List the tables to verify the restore worked:

列出表以验证还原是否有效：

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'
                         Access privileges
Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
public | beer      | table |                   | 

To sum up, we've verified that we can start a database, the data persists after a restart, and we can restore a backup into it from the host.

综上所述，我们已经验证了我们可以启动一个数据库，重启后数据仍然存在，我们可以从主机恢复一个备份到其中。

Thanks Tomasz!

谢谢托马斯！

回答by irakli

It looks like the PostgreSQL image is having issues with mounted volumes. FWIW, it is probably more of a PostgreSQL issue than Dockers, but that doesn't matter because mounting disks is not a recommended way for persisting database files, anyway.

看起来 PostgreSQL 映像在安装卷方面存在问题。FWIW，它可能比 Docker 更像是一个 PostgreSQL 问题，但这并不重要，因为无论如何，挂载磁盘不是持久化数据库文件的推荐方式。

You should be creating data-only Docker containers, instead. Like this:

相反，您应该创建仅用于数据的 Docker 容器。像这样：

postgres9:
  image: postgres:9.4
  ports:
    - 5432:5432
  volumes_from:
    - pg_data
  environment:
    POSTGRES_PASSWORD: postgres
    POSTGRES_USER: postgres
    PGDATA : /var/lib/postgresql/data/pgdata

pg_data:
  image: alpine:latest
  volumes:
    - /var/lib/postgresql/data/pgdata
  command: "true"

which I tested and worked fine. You can read more about data-only containers here: Why Docker Data Containers (Volumes!) are Good

我测试过并且工作正常。您可以在此处阅读有关纯数据容器的更多信息：Why Docker Data Containers (Volumes!) is Good

As for: how to import initial data, you can either:

至于：如何导入初始数据，您可以：

1. docker cp, into the data-only container of the setup, or
2. Use an SQL dump of the data, instead of moving binary files around (which is what I would do).

1. docker cp, 进入设置的纯数据容器，或
2. 使用数据的 SQL 转储，而不是四处移动二进制文件（我会这样做）。