迁移MtPhotos的postgres(PG)数据库数据

Administrator

2023-11-30

教程

MT Photos是一款为Nas玩家准备的照片管理系统
支持自动整理、分类您的照片，比如：时间、地点、人物、照片类型。
您可以在任何支持Docker的系统中运行它。
支持人脸识别,地图信息,相册分享等功能

开始

一开始使用的是外置的宝塔安装pg数据库,最近在转移数据库,发现通过软件后台生成的备份文件只有512字节,里面只有10几行数据,只能通过数据库工具手动的转移数据.
这里用的是dbeaver,但使用软件的导出/备份功能时又报错:pg dump版本不对.
懒得搞pg的版本了,直接用dbeaver对拷数据吧.
大体步骤是:

连接老数据库
连接新数据库并手动创建库和表
用dbeaver的导出数据功能
设置新库的pg用户名和密码
应用到mt,成功启动

连接源数据库(从这里转移出去)

我用的是宝塔的postgres应用,可以参考我设置的参数开启远程访问,然后使用DBeaver连接上就算成功.

以防万一,附一个开放远程访问的方法(从第四步开始看):
问:使用docker安装pg数据库时设置可远程访问的参数是什么
答:要在 Docker 中安装 PostgreSQL 数据库并设置允许远程访问的参数，你可以使用以下步骤：

首先，确保已经安装了 Docker 并启动了 Docker 服务。
打开终端或命令提示符，并运行以下命令来拉取并运行 PostgreSQL 容器：
```
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres
```
这将在 Docker 中启动一个名为 "my-postgres" 的 PostgreSQL 容器，并将容器的 5432 端口映射到主机的 5432 端口。你可以根据需要修改端口映射。
接下来，使用以下命令进入正在运行的 PostgreSQL 容器的 shell：
```
docker exec -it my-postgres bash
```
在容器的 shell 中，使用以下命令编辑 PostgreSQL 的配置文件 postgresql.conf：
```
vi /var/lib/postgresql/data/postgresql.conf
```
如果你的容器中没有安装 vi，可以使用其他编辑器，如 nano。
在配置文件中找到并修改以下参数，以允许远程访问：
```
listen_addresses = '*'
```
将 listen_addresses 的值设置为 '*'，表示允许来自任何 IP 地址的连接。
保存并关闭配置文件。
接下来，使用以下命令编辑 PostgreSQL 的 pg_hba.conf 文件：
```
vi /var/lib/postgresql/data/pg_hba.conf
```
在 pg_hba.conf 文件中，找到并添加以下行，以允许所有 IP 地址的连接：
```
host    all             all             0.0.0.0/0               md5
```
这将允许来自任何 IP 地址的连接，并使用 "md5" 密码验证。
保存并关闭 pg_hba.conf 文件。
重新启动 PostgreSQL 容器，以使配置更改生效：
```
docker restart my-postgres
```

现在，你的 PostgreSQL 容器应该已经设置为允许远程访问。你可以使用远程主机的 IP 地址和相应的端口（默认为 5432）连接到 PostgreSQL 数据库。

请注意，允许远程访问可能会增加安全风险。建议在生产环境中仅允许受信任的主机进行远程访问，并使用适当的安全措施来保护数据库。

创建目标数据库(转移到这里)

使用docker创建postgres数据库:

# docker-compose
version: '3'
services:
  postgres:
    container_name: pggg
    image: postgres:15.3-alpine
    restart: always
    environment:
      - POSTGRES_PASSWORD=123456
    ports:
      - 5432:5432
    volumes:
      - ./data:/var/lib/postgresql/data

这个docker-compose是我复制的1Panel的应用商店里的pg数据库,创建后的容器后默认是开放远程访问的,只需要放行防火墙的5432端口.

创建目标中的数据库和表

创建数据库

注意数据库的所有者

2. 创建扩展

# 查询已有的扩展
SELECT * FROM pg_extension;
# 添加扩展
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

下载建表语句
链接: https://pan.baidu.com/s/1jNvzILvg2akDAMRPQ6tq-g?pwd=e4ch 提取码: e4ch 复制这段内容后打开百度网盘手机App，操作更方便哦
执行sql

Alt+x执行,注意选择的库名是否正确

创建表

问:使用pg数据库的默认用户postgres创建了一个数据库mtmt,为什么重新连接后只显示默认的postgres数据库,需要指定mtmt才能显示
答:当你使用 PostgreSQL 的默认用户 "postgres" 创建了一个名为 "mtmt" 的数据库后，重新连接到 PostgreSQL 服务器时，默认情况下只显示 "postgres" 数据库。这是因为 PostgreSQL 客户端连接时默认连接到 "postgres" 数据库。

要显示并连接到 "mtmt" 数据库，你可以在连接时指定要连接的数据库名称。可以使用以下命令连接到 "mtmt" 数据库：

psql -U postgres -d mtmt

这将使用用户名 "postgres" 连接到 "mtmt" 数据库。请确保替换为实际的用户名和数据库名称。

另一种方法是在连接到 PostgreSQL 服务器后，使用以下命令切换到 "mtmt" 数据库：

\c mtmt

这将在当前连接的会话中切换到 "mtmt" 数据库。之后，你将能够查看和操作 "mtmt" 数据库中的表和数据。

请注意，如果你在创建数据库时指定了不同的用户名和密码，你需要在连接时提供相应的用户名和密码。例如：

psql -U your_username -d mtmt -W

将 "your_username" 替换为实际的用户名，并在连接时输入密码。

开始转移数据

选中源数据中的所有表,开始数据转换
选择目标数据库,下一步
点一下Auto assign,让他自动映射
变成这样,下一步
默认,下一步
核对信息,确认Source 和 Target,一定不要弄反.点击开始
遇到错误:

原因：
 SQL 错误 [08006]: Batch entry 0  xxx An I/O error occurred while sending to the backend.  Call getNextException to see other errors in the batch.

意思时网络超时,重试就行
7. 打开新数据库,验证数据正确

更改mtmt的用户

数据转移完成后mtmt库只能被postgres或启动容器时指定的默认用户访问(比如我的是user_xxx).需要为mtmt库设置一个用户和密码.
问;通过docker创建pg并创建了mtmt库,怎么为mtmt指定一个不存在的用户名和密码
答:
如果你使用 Docker 创建了 PostgreSQL 并创建了 "mtmt" 数据库，现在想为 "mtmt" 数据库指定一个不存在的用户名和密码，可以按照以下步骤进行操作：

首先，确认你的 PostgreSQL Docker 容器正在运行。你可以使用以下命令检查容器的状态：
```
docker ps
```
连接到正在运行的 PostgreSQL 容器：
```
docker exec -it <container_name> psql -U postgres
 # postgres是默认的超级用户,自行替换
```
将 <container_name> 替换为你的 PostgreSQL 容器的名称或 ID。

比如我的用户是user_McMbrS:
在 PostgreSQL 服务器上创建新用户：
```
CREATE USER your_username WITH PASSWORD 'your_password';
```
将 "your_username" 替换为你想要创建的用户名，将 "your_password" 替换为你想要设置的密码。

授予新用户在 "mtmt" 数据库上的权限：

GRANT ALL PRIVILEGES ON DATABASE mtmt TO your_username;

补充:

# 切换数据库到mtmt
\c mtmt
# 将表权限应用于mtmt数据库中的 "public" 模式下(问了三遍给了三个赋权语句,建议都执行一遍,然后手动增删改查下数据,不报错再继续)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mtmt;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mtmt;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mtmt;

退出 PostgreSQL 容器：
```
\q
```

现在，你可以使用新创建的用户名和密码连接到 "mtmt" 数据库：

docker exec -it <container_name> psql -U your_username -d mtmt

将 <container_name> 替换为你的 PostgreSQL 容器的名称或 ID，将 "your_username" 替换为你创建的用户名。在连接时输入密码，这将使用新用户连接到 "mtmt" 数据库，并允许你查看和操作该数据库中的表和数据。请确保在连接时使用正确的用户名和密码。
6. 验证

# 执行下sql,能正常输出即可
输入: select count(0) from public.file f ;
输出:
 count 
-------
  2343
(1 row)

重新启动mt

修改配置文件的pg用户名和密码,重启,无报错,数据和相册也都在

遇到的问题

无法创建表

mt需要两个额外的扩展,安装一下

天知道当时用宝塔的pg是怎么安装上的扩展,还得手动编译,复制文件,然后进库里在加载

# 查询已有的扩展
SELECT * FROM pg_extension;
# 添加扩展
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

数据库无法删除

第一次执行建表语句只添加了一个扩展,导致后面的表创建失败.这个需要需要先删库重建,但直接删除会报错:There are 2 other sessions using the database.(当然也可以只重新创建失败的表)

来自chatGPT的回答
记得删除后重新执行添加扩展语句.

当在 PostgreSQL 中删除数据库时，如果有其他正在使用该数据库的会话存在，将会出现 "There are X other sessions using the database" 的提示，其中 X 是正在使用数据库的会话数量。

这个提示是为了确保在删除数据库之前没有其他活动会话，以防止意外的数据丢失或损坏。如果你确定要删除数据库并且不再需要其中的数据，你可以采取以下步骤来解决这个问题：

确定正在使用数据库的会话：首先，你需要确定正在使用目标数据库的会话。可以使用以下 SQL 查询来查找正在使用数据库的会话：
```
SELECT pid, usename, application_name FROM pg_stat_activity WHERE datname = 'your_database_name';
```
将 "your_database_name" 替换为你要删除的数据库的名称。这将返回正在使用该数据库的会话的进程 ID（pid）、用户名（usename）和应用程序名称（application_name）。
终止使用数据库的会话：对于每个返回的会话，你可以使用以下 SQL 命令来终止会话：
```
SELECT pg_terminate_backend(pid);
```
将 "pid" 替换为要终止的会话的进程 ID。执行该命令将终止该会话，确保没有其他会话在使用数据库。
重复步骤 1 和步骤 2，直到没有返回任何会话。
删除数据库：现在，你可以尝试再次删除数据库。使用以下 SQL 命令删除数据库：
```
DROP DATABASE your_database_name;
```
将 "your_database_name" 替换为要删除的数据库的名称。

完成上述步骤后，你应该能够成功删除数据库。请注意，在执行删除操作之前，请确保你确实不再需要数据库中的数据，因为删除数据库将永久删除其中的所有数据。