无法从在新卷中完成的恢复中加载数据库

Question

我在使用docker-compose恢复postgres数据库卷时遇到问题。

以下是步骤：

1.备份卷：

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

2. 创建一个卷（该卷已具有正确的名称，可在之后与 docker 撰写一起使用）：

$ docker volume create folder_test-db

3.还原该卷中的数据库:

$ docker run --rm -v folder_test-db:/recover \
    -v /mnt/data/dbbackups/:/backup ubuntu bash \
    -c "cd /recover && tar xvf /backup/backup.tar"

然后

$ cd /path/to/restore/test/folder
$ docker-compose up

正在给予：

Creating network "folder_default" with the default driver
Creating folder_test-db_1 ... done
Attaching to folder_test-db_1
test-db_1  | initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
test-db_1  | If you want to create a new database system, either remove or empty
test-db_1  | the directory "/var/lib/postgresql/data" or run initdb
test-db_1  | with an argument other than "/var/lib/postgresql/data".
test-db_1  | The files belonging to this database system will be owned by user "postgres".
test-db_1  | This user must also own the server process.
test-db_1  | 
test-db_1  | The database cluster will be initialized with locale "en_US.utf8".
test-db_1  | The default database encoding has accordingly been set to "UTF8".
test-db_1  | The default text search configuration will be set to "english".
test-db_1  | 
test-db_1  | Data page checksums are disabled.
test-db_1  | 
folder_test-db_1 exited with code 1

以下是合成文件的内容:

version: '3.8'

volumes:
  test-db: {}

services:
  test-db:
    image: postgres:12.3
    environment:
      POSTGRES_DB: myapp-db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - test-db:/var/lib/postgresql/data
    ports:
      - 5432:5432

postgres 映像版本与我从中转储卷的原始容器版本完全相同。

我发现了一个窍门，但也不管用:

在将数据库恢复到新卷后，我没有尝试启动新的撰写文件，而是做了相反的事情；

1. 启动compose文件；这将启动一个新的postgres数据库，其中包含compose文件中给出的用户，
2. 停止容器，
3. 现在在已经存在的卷上进行恢复，因为它是由第一步初始化的，
4. 重启容器。

数据库已创建，角色正确；

user=# \conninfo
You are connected to database "myapp-db" as user "user" via socket in "/var/run/postgresql" at port "5432".

但是，哎呀，数据在哪里？！:

user=# \dt
Did not find any relations.

我如何用docker-compose取回我的数据库？< br >因为在我最初的应用程序中，当数据库已经存在时，docker-compose跳过初始化步骤，继续处理现有内容。我希望在恢复后也能有同样的行为，尤其是在docker守护进程的帮助下！

这是组成文件的一部分（位于＜code＞rootdir

version: "3.5"

services:
  myapp-db:
    image: postgres:12.3
    environment:
      POSTGRES_DB: myapp-db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - myapp-db-data:/var/lib/postgresql/data
    networks:
      - myapp
    ports:
      - 5433:5432

volumes:
  myapp-db-data:

以下是该原始容器的 /var/lib/后gresql/数据的内容：

# ls -larth /var/lib/postgresql/data
total 132K
-rw-------  1 postgres postgres    3 Feb  6 15:13 PG_VERSION
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_twophase
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_tblspc
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_snapshots
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_serial
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_replslot
drwx------  4 postgres postgres 4.0K Feb  6 15:13 pg_multixact
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_dynshmem
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_commit_ts
-rw-------  1 postgres postgres  26K Feb  6 15:13 postgresql.conf
-rw-------  1 postgres postgres   88 Feb  6 15:13 postgresql.auto.conf
-rw-------  1 postgres postgres 1.6K Feb  6 15:13 pg_ident.conf
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_xact
drwx------  2 postgres postgres 4.0K Feb  6 15:13 pg_subtrans
-rw-------  1 postgres postgres 4.5K Feb  6 15:13 pg_hba.conf
drwx------  6 postgres postgres 4.0K Feb  6 15:13 base
drwx------  3 postgres postgres 4.0K Feb  6 17:32 pg_wal
drwx------  4 postgres postgres 4.0K Feb  6 22:44 pg_logical
drwx------  2 postgres postgres 4.0K Feb  7 08:32 pg_notify
-rw-------  1 postgres postgres   36 Feb  7 08:32 postmaster.opts
-rw-------  1 postgres postgres   94 Feb  7 08:32 postmaster.pid
drwx------  2 postgres postgres 4.0K Feb  7 08:32 pg_stat
drwx------  2 postgres postgres 4.0K Feb  7 08:32 global
drwx------  2 postgres postgres 4.0K Feb  7 08:33 pg_stat_tmp

和已恢复卷的：

# ls -larth /var/lib/postgresql/data
total 136K
-rw-------  1 postgres postgres    3 Feb  7 00:10 PG_VERSION
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_twophase
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_tblspc
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_snapshots
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_serial
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_replslot
drwx------  4 postgres postgres 4.0K Feb  7 00:10 pg_multixact
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_dynshmem
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_commit_ts
-rw-------  1 postgres postgres  26K Feb  7 00:10 postgresql.conf
-rw-------  1 postgres postgres   88 Feb  7 00:10 postgresql.auto.conf
-rw-------  1 postgres postgres 1.6K Feb  7 00:10 pg_ident.conf
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_xact
drwx------  3 postgres postgres 4.0K Feb  7 00:10 pg_wal
drwx------  2 postgres postgres 4.0K Feb  7 00:10 pg_subtrans
-rw-------  1 postgres postgres 4.5K Feb  7 00:10 pg_hba.conf
drwx------  6 postgres postgres 4.0K Feb  7 00:10 base
drwxr-xr-x  3 postgres root     4.0K Feb  7 00:10 var
-rw-------  1 postgres postgres   36 Feb  7 00:14 postmaster.opts
drwx------  2 postgres postgres 4.0K Feb  7 00:14 pg_notify
-rw-------  1 postgres postgres   94 Feb  7 00:14 postmaster.pid
drwx------  2 postgres postgres 4.0K Feb  7 00:14 pg_stat
drwx------  2 postgres postgres 4.0K Feb  7 00:15 global
drwx------  4 postgres postgres 4.0K Feb  7 00:19 pg_logical
drwx------  2 postgres postgres 4.0K Feb  7 08:34 pg_stat_tmp

一切都托管在Ubuntu 18.04服务器上。

Answer 1

RTFM我必须改变压缩初始卷的方式，因为按照我的方式进行，实际上存储了每个被压缩的单个文件的完整绝对路径。请参阅man tar并检查-C（大写C）选项。

所以之前（坏）：

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

（好）之后：

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar -C /var/lib/postgresql/data .

好的，通过进一步探索托管已恢复数据库的新创建的卷，我可以看到我在 /var/lib/postgres/data/ 文件夹中有这样一行让我眼花缭乱：

drwxr-xr-x  3 postgres root     4.0K Feb  7 00:10 var

当我进去的时候，原来数据库中有一整份/var/lib/postgres/data/！E、 g.＜code＞/var/lib/postgres/data/var/libs/postgres/data/

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar /var/lib/postgresql/data

事实上，tar cvf /backup/backup.tar /var/lib/postgresql/确实考虑了它被要求压缩的文件的绝对路径！

因此，回到基础#rtfm：

$ man tar
...
       -C, --directory=DIR
              Change to DIR before performing any operations.
              This option is order-sensitive, i.e. it affects all options that follow.

对我来说足够清楚，我将以前的备份命令更改为：

$ docker run --rm --volumes-from rootdir_myapp-db_1 \
    -v /mnt/data/dbbackups:/backup ubuntu tar cvf /backup/backup.tar -C /var/lib/postgresql/data .

请不要忘记最后的那个小点，因为它实际上是说要将tar刚才“cd-ed”的目录压缩到！

从现在开始，恢复保持不变，我再也看不到新卷中/var/lib/postgres/data/中的var文件夹了！

当我重新准备我的撰写文件时，现在我最终得到的是我在等待吗：

$ cd /path/to/restore/test/folder
$ docker-compose up

Starting folder_test-db_1 ... done
Attaching to folder_test-db_1
test-db_1  | 
test-db_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
test-db_1  | 
test-db_1  | 2021-02-07 08:55:30.849 UTC [1] LOG:  starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
test-db_1  | 2021-02-07 08:55:30.849 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
test-db_1  | 2021-02-07 08:55:30.850 UTC [1] LOG:  listening on IPv6 address "::", port 5432
test-db_1  | 2021-02-07 08:55:30.858 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
test-db_1  | 2021-02-07 08:55:30.888 UTC [26] LOG:  database system was interrupted; last known up at 2021-02-07 08:37:37 UTC
test-db_1  | 2021-02-07 08:55:33.044 UTC [26] LOG:  database system was not properly shut down; automatic recovery in progress
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  redo starts at 0/1EA5948
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  invalid record length at 0/1EA5A30: wanted 24, got 0
test-db_1  | 2021-02-07 08:55:33.051 UTC [26] LOG:  redo done at 0/1EA59F8
test-db_1  | 2021-02-07 08:55:33.096 UTC [1] LOG:  database system is ready to accept connections

在该容器中，在 psql -U 用户 -d 中：

# \conninfo
You are connected to database "myapp-db" as user "user" via socket in "/var/run/postgresql" at port "5432".

# \dt
                                List of relations
 Schema |                          Name                           | Type  | Owner 
--------+---------------------------------------------------------+-------+-------
 public | attachments_attachment                                  | table | user
 public | auth_group                                              | table | user
 public | auth_group_permissions                                  | table | user
 public | auth_permission                                         | table | user
....

以前的两种恢复方法都非常有效。
您可能更喜欢其中之一。

Answer 2

您是否使用docker卷插件来创建卷？如果其中有任何文件或文件夹，例如丢失找到，它可能会无法初始化。如果您想将文件保留在卷中（或无法控制），您可以调整PGDATA环境变量以指向其中的子目录，例如

-e PGDATA=/var/lib/postgresql/data/db files/

...
volumeMounts:
- mountPath: /var/lib/postgresql/data
  name: test-db-volume
  subPath: postgres
...

也可以参考这个更多的理解