database 备份/恢复 dockerized PostgreSQL 数据库

Backup/Restore a dockerized PostgreSQL database

提问by Carl Levasseur

I'm trying to backup/restore a PostgreSQL database as is explained on the Docker website, but the data is not restored.

我正在尝试按照 Docker 网站上的说明备份/恢复 PostgreSQL 数据库，但未恢复数据。

The volumes used by the database image are:

数据库镜像使用的卷是：

VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

and the CMD is:

CMD 是：

CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"]

I create the DB container with this command:

我使用以下命令创建数据库容器：

docker run -it --name "$DB_CONTAINER_NAME" -d "$DB_IMAGE_NAME"

Then I connect another container to insert some data manually:

然后我连接另一个容器来手动插入一些数据：

docker run -it --rm --link "$DB_CONTAINER_NAME":db "$DB_IMAGE_NAME" sh -c 'exec bash'
psql -d test -h $DB_PORT_5432_TCP_ADDR
# insert some data in the db
<CTRL-D>
<CTRL-D>

The tar archive is then created:

然后创建 tar 存档：

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql

Now I remove the container used for the db and create another one, with the same name, and try to restore the data inserted before:

现在我删除用于数据库的容器并创建另一个具有相同名称的容器，并尝试恢复之前插入的数据：

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar 

But the tables are empty, why is the data not properly restored ?

但是表是空的，为什么数据没有正确恢复？

回答by Forth

Backup your databases

备份您的数据库

docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Restore your databases

恢复您的数据库

cat your_dump.sql | docker exec -i your-db-container psql -U postgres

回答by Tharindu Pradeep

I think you can also use a potgres backup container which would backup your databases within a given time duration.

我认为您还可以使用potgres 备份容器，它可以在给定的时间内备份​​您的数据库。

  pgbackups:
    container_name: Backup
    image: prodrigestivill/postgres-backup-local
    restart: always
    volumes:
      - ./backup:/backups
    links:
      - db:db
    depends_on:
      - db
    environment:
      - POSTGRES_HOST=db
      - POSTGRES_DB=${DB_NAME} 
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_EXTRA_OPTS=-Z9 --schema=public --blobs
      - SCHEDULE=@every 0h30m00s
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=81

回答by Carl Levasseur

Okay, I've figured this out. Postgresql does not detect changes to the folder /var/lib/postgresql once it's launched, at least not the kind of changes I want it do detect.

好的，我已经想通了。Postgresql 在启动后不会检测文件夹 /var/lib/postgresql 的更改，至少不是我希望它检测到的更改类型。

The first solution is to start a container with bash instead of starting the postgres server directly, restore the data, and then start the server manually.

第一种解决方案是用bash启动一个容器，而不是直接启动postgres服务器，恢复数据，然后手动启动服务器。

The second solution is to use a data container. I didn't get the point of it before, now I do. This data container allows to restore the data before starting the postgres container. Thus, when the postgres server starts, the data are already there.

第二种解决方案是使用数据容器。以前没看懂，现在懂了。这个数据容器允许在启动 postgres 容器之前恢复数据。因此，当 postgres 服务器启动时，数据已经在那里了。

回答by sjakubowski

Another approach (based on docker-postgresql-workflow)

另一种方法（基于docker-postgresql-workflow）

Local running database (not in docker, but same approach would work) to export:

本地运行的数据库（不在 docker 中，但同样的方法也可以）导出：

pg_dump -F c -h localhost mydb -U postgres export.dmp

Container database to import:

要导入的容器数据库：

docker run -d -v /local/path/to/postgres:/var/lib/postgresql/data postgres #ex runs container as `CONTAINERNAME` #find via `docker ps`
docker run -it --link CONTAINERNAME:postgres  --volume $PWD/:/tmp/  postgres  bash -c 'exec pg_restore -h postgres -U postgres -d mydb -F c /tmp/sonar.dmp'

回答by activereality

I had this issue while trying to use a db_dump to restore a db. I normally use dbeaver to restore- however received a psql dump, so had to figure out a method to restore using the docker container.

我在尝试使用 db_dump 恢复数据库时遇到了这个问题。我通常使用 dbeaver 进行恢复 - 但是收到了 psql 转储，因此必须找出使用 docker 容器进行恢复的方法。

The methodology recommended by Forth and edited by Soviut worked for me:

Forth 推荐并由 Soviut 编辑的方法对我有用：

cat your_dump.sql | docker exec -i your-db-container psql -U postgres -d dbname

cat your_dump.sql | docker exec -i your-db-container psql -U postgres -d dbname

(since this was a single db dump and not multiple db's i included the name)

（因为这是一个单一的数据库转储而不是多个数据库，我包括了名称）

However, in order to get this to work, I had to also go into the virtualenv that the docker container and project were in. This eluded me for a bit before figuring it out- as I was receiving the following docker error.

但是，为了让它工作，我还必须进入 docker 容器和项目所在的 virtualenv。在弄清楚之前，这让我有点困惑 - 因为我收到了以下 docker 错误。

read unix @->/var/run/docker.sock: read: connection reset by peer

read unix @->/var/run/docker.sock: read: connection reset by peer

This can be caused by the file /var/lib/docker/network/files/local-kv.db .I don't know the accuracy of this statement: but I believe I was seeing this as I do not user docker locally, so therefore did not have this file, which it was looking for, using Forth's answer.

这可能是由文件 /var/lib/docker/network/files/local-kv.db 引起的。我不知道此声明的准确性：但我相信我看到了这一点，因为我没有在本地使用 docker，因此，使用 Forth 的答案，没有它正在寻找的这个文件。

I then navigated to correct directory (with the project) activated the virtualenv and then ran the accepted answer. Boom, worked like a top. Hope this helps someone else out there!

然后我导航到正确的目录（使用项目）激活 virtualenv，然后运行接受的答案。繁荣，工作得像个陀螺。希望这可以帮助其他人！

回答by Jacob Nelson

This is the command worked for me.

这是对我有用的命令。

cat your_dump.sql | sudo docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

for example

例如

cat table_backup.sql | docker exec -i 03b366004090 psql -U postgres -d postgres

Reference: Solution given by GMartinez-Sistiin this discussion. https://gist.github.com/gilyes/525cc0f471aafae18c3857c27519fc4b

参考：GMartinez-Sisti在本次讨论中给出的解决方案。 https://gist.github.com/gilyes/525cc0f471aafae18c3857c27519fc4b

回答by Alex Fedoseev

cat db.dump | docker exec ...way didn't work for my dump (~2Gb). It took few hours and ended up with out-of-memory error.

cat db.dump | docker exec ...方式不适用于我的转储（~2Gb）。花了几个小时，最终出现内存不足错误。

Instead, I cp'ed dump into container and pg_restore'ed it from within.

相反，我将 cp 转储到容器中，然后从内部进行 pg_restore 处理。

Assuming that container id is CONTAINER_IDand db name is DB_NAME:

假设容器 idCONTAINER_ID和 db name 是DB_NAME：

# copy dump into container
docker cp local/path/to/db.dump CONTAINER_ID:/db.dump

# shell into container
docker exec -it CONTAINER_ID bash

# restore it from within
pg_restore -U postgres -d DB_NAME --no-owner -1 /db.dump

回答by Kevin Lin

dksnap(https://github.com/kelda/dksnap) automates the process of running pg_dumpalland loading the dump via /docker-entrypoint-initdb.d.

dksnap( https://github.com/kelda/dksnap)pg_dumpall通过/docker-entrypoint-initdb.d.

It shows you a list of running containers, and you pick which one you want to backup. The resulting artifact is a regular Docker image, so you can then docker runit, or share it by pushing it to a Docker registry.

它会向您显示正在运行的容器列表，您可以选择要备份的容器。生成的工件是一个常规的 Docker 映像，因此您可以使用docker run它，或者通过将其推送到 Docker 注册表来共享它。

(disclaimer: I'm a maintainer on the project)

（免责声明：我是该项目的维护者）

回答by Shubham

The below command can be used to take dump from docker postgress container

以下命令可用于从 docker postgress 容器中获取转储

docker exec -t <postgres-container-name> pg_dump --no-owner -U <db-username> <db-name> > file-name-to-backup-to.sql