Strapi+Postgres+Docker环境下数据库远程迁移的正确方法

问题描述 #

我有一个基于Strapi CMS和Postgres数据库的项目，通过单份docker-compose.yml脚本启动Docker容器。本地开发完成后，希望将项目迁移至远程服务器，同时保留CMS录入的所有数据状态。本地Postgres数据库包含CMS生成的表和数据，我已将CMS动态生成的所有*.js文件及public/文件夹复制到远程服务器，但无法正确导出/导入数据库的完整副本。请问从Docker容器中复制数据库的正确方式是什么？ 我偏好导出为可通过/usr/local/bin/docker-entrypoint.sh导入的单个或多个*.sql文件。

已尝试方案及问题 #

使用Adminer导出的问题 #

用Adminer导出为单个*.sql文件，将其放入Postgres容器的初始化文件夹后导入成功，本地数据库的所有CMS数据已同步到远程。但尝试从管理面板更新CMS集合时，界面出现异常错误，Postgres和Strapi日志如下：

postgres         | 2023-02-17 11:44:39.438 UTC [40] ERROR:  duplicate key value violates unique constraint "about_pages_components_pkey"
postgres         | 2023-02-17 11:44:39.438 UTC [40] DETAIL:  Key (id)=(35) already exists.
postgres         | 2023-02-17 11:44:39.438 UTC [40] STATEMENT:  insert into "about_pages_components" ("component_id", "component_type", "entity_id", "field", "order") values ($1, $2, $3, $4, $5) returning "id"
api              | [2023-02-17 11:44:39.440] error: insert into "about_pages_components" ("component_id", "component_type", "entity_id", "field", "order") values ($1, $2, $3, $4, $5) returning "id" - duplicate key value violates unique constraint "about_pages_components_pkey"
api              | error: insert into "about_pages_components" ("component_id", "component_type", "entity_id", "field", "order") values ($1, $2, $3, $4, $5) returning "id" - duplicate key value violates unique constraint "about_pages_components_pkey"
api              |     at Parser.parseErrorMessage (/opt/app/node_modules/pg-protocol/dist/parser.js:287:98)
api              |     at Parser.handlePacket (/opt/app/node_modules/pg-protocol/dist/parser.js:126:29)
api              |     at Parser.parse (/opt/app/node_modules/pg-protocol/dist/parser.js:39:38)
api              |     at Socket.<anonymous> (/opt/app/node_modules/pg-protocol/dist/index.js:11:42)
api              |     at Socket.emit (events.js:400:28)
api              |     at Socket.emit (domain.js:475:12)
api              |     at addChunk (internal/streams/readable.js:293:12)
api              |     at readableAddChunk (internal/streams/readable.js:267:9)
api              |     at Socket.Readable.push (internal/streams/readable.js:206:10)
api              |     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

Adminer导出设置截图：

使用pg_dump导出的问题 #

执行命令docker exec -t postgres pg_dump -c -U postgres > init.sql导出后，导入时出现以下错误日志：

postgres         | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
postgres         | SET
postgres         | SET
postgres         | SET
postgres         | SET
postgres         | SET
postgres         |  set_config 
postgres         | ------------
postgres         |  
postgres         | (1 row)
postgres         | 
postgres         | SET
postgres         | SET
postgres         | SET
postgres         | SET
postgres         | 2023-02-17 11:50:46.045 UTC [50] ERROR:  relation "public.up_users" does not exist
postgres         | 2023-02-17 11:50:46.045 UTC [50] STATEMENT:  ALTER TABLE ONLY public.up_users DROP CONSTRAINT up_users_updated_by_id_fk;
postgres         | psql:/docker-entrypoint-initdb.d/init.sql:19: ERROR:  relation "public.up_users" does not exist
postgres         | 2023-02-17 11:50:47.358 UTC [1] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
postgres         | 2023-02-17 11:50:47.358 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres         | 2023-02-17 11:50:47.358 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres         | 2023-02-17 11:50:47.375 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres         | 2023-02-17 11:50:47.393 UTC [24] LOG:  database system was interrupted; last known up at 2023-02-17 11:50:45 UTC
postgres         | 2023-02-17 11:50:51.704 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
postgres         | 2023-02-17 11:50:51.735 UTC [24] LOG:  invalid record length at 0/16FCE28: wanted 24, got 0
postgres         | 2023-02-17 11:50:51.735 UTC [24] LOG:  redo is not required
postgres         | 2023-02-17 11:50:52.395 UTC [1] LOG:  database system is ready to accept connections

使用pg_dumpall导出的问题 #

执行命令docker exec -t postgres pg_dumpall -c -U postgres > init.sql导出后，导入时出现以下错误日志：

postgres         | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
postgres         | SET
postgres         | SET
postgres         | SET
postgres         | 2023-02-17 12:05:48.154 UTC [49] ERROR:  current user cannot be dropped
postgres         | 2023-02-17 12:05:48.154 UTC [49] STATEMENT:  DROP ROLE postgres;
postgres         | psql:/docker-entrypoint-initdb.d/init.sql:22: ERROR:  current user cannot be dropped
postgres         | 2023-02-17 12:05:49.532 UTC [1] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
postgres         | 2023-02-17 12:05:49.532 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres         | 2023-02-17 12:05:49.533 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres         | 2023-02-17 12:05:49.554 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres         | 2023-02-17 12:05:49.581 UTC [22] LOG:  database system was interrupted; last known up at 2023-02-17 12:05:48 UTC

环境版本信息 #

• Strapi依赖版本：

"dependencies": {
    "@strapi/plugin-i18n": "4.2.3",
    "@strapi/plugin-users-permissions": "4.2.3",
    "@strapi/strapi": "4.2.3",
    "better-sqlite3": "7.4.6",
    "pg": "^8.8.0"
  },

• Postgres Docker镜像：postgres:14.3-alpine
• Adminer Docker镜像：adminer:4.8.1

内容的提问来源于stack exchange，提问作者0xC0DEGURU