今天抽空，将自己维护的服务都升级了一下，看了下umami从v3开始就不再支持MySQL，而我目前是用MySql版本的，本着用新不用旧、能早升级早升级的理念，那就只能升级加迁移数据库了，下面分享下过程。

导出MySql的数据

直接在宿主机执行

1
2
docker exec -i db mysqldump --no-create-info --default-character-set=utf8mb4 --quick --skip-add-locks \
-u root -p umamidb > /tmp/mydbdump.sql

根据自己习惯来。

定义环境变量

在docker-compose.yaml同级目录下，创建init-db目录，里面创建一个文件init.sql。内容如下：

1
2
3
4
5
6
7
8
9
10
-- Umami的数据库初始化脚本
CREATE USER umamiuser WITH PASSWORD 'password';
ALTER USER umamiuser CREATEDB;
CREATE DATABASE umamidb OWNER umamiuser;

-- Waline 的数据库初始化脚本
CREATE USER waline_user WITH PASSWORD 'password';
ALTER USER waline_user CREATEDB;
CREATE DATABASE waline_db OWNER waline_user;

在docker-compose.yaml同级目录下，编辑.env文件（没有就创建）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MYSQL_ROOT_PASSWORD=password
MYSQL_USER=username
MYSQL_PASSWORD=password

# PG
POSTGRES_PASSWORD=password
POSTGRES_USER=postgres
POSTGRES_UMAMI_PWD=password
POSTGRES_WALINE_PWD=password
# SMTP
SMTP_PWD=password

# umami
UMAMI_APP_SECRET=NiMZovEXm2dYdSfHb47sLcljvjFnPesxxxxxxxxxxxx
UMAMI_DATABASE_URL=postgresql://umamiuser:password@db-pg:5432/umamidb

# waline
WL_JWT_TOKEN=31HHvaaXFJ984pLc2iCxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
WL_AKISMET_KEY=a93dxxxxxxxxxxxxxx
WL_PG_USER=waline_user
WL_PG_DB=waline_db
WL_TURNSTILE_KEY=0x4AAxxxxxxxxxxxxxxxxxx
WL_TURNSTILE_SECRET=0x4AAAxxxxxxxxxxxxxxxxxxxxxx


执行docker compose config，确认下配置是否生效

修改docker-compose.yaml

在docker-compose.yaml中新增postgresql，并将原来的umami替换为postgresql版本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
services:
  db:
    image: mysql:8.4
    container_name: db
    restart: unless-stopped
    env_file: .env
    volumes:
      - dbdata:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "hc_user", "--password=yourpassword"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 10s # 给 MySQL 10秒的宽限期执行初始化
    networks:
      - app-network
  db-pg:
    image: postgres:17-alpine
    container_name: postgresql
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: $POSTGRES_PASSWORD
      POSTGRES_DB: postgres
    volumes:
      - pg-data:/var/lib/postgresql/data
      - ./init-db:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 5s
      timeout: 5s
      retries: 5
    networks:
      - app-network

  wordpress:
    depends_on:
      - db
    image: wordpress:6.8.3-php8.4-fpm-alpine
    container_name: wordpress
    restart: unless-stopped
    env_file: .env
    environment:
      - WORDPRESS_DB_HOST=db:3306
      - WORDPRESS_DB_USER=$MYSQL_USER
      - WORDPRESS_DB_PASSWORD=$MYSQL_PASSWORD
      - WORDPRESS_DB_NAME=wordpress
    volumes:
      - wordpress:/var/www/html
      - ./php-config/custom.ini:/usr/local/etc/php/conf.d/custom.ini
    networks:
      - app-network

  vps:
    depends_on:
      - db
    image: wordpress:6.8.3-php8.4-fpm-alpine
    container_name: wp-vps
    restart: unless-stopped
    env_file: .env
    environment:
      - WORDPRESS_DB_HOST=db:3306
      - WORDPRESS_DB_USER=wp-vps
      - WORDPRESS_DB_PASSWORD=$MYSQL_PASSWORD
      - WORDPRESS_DB_NAME=wp_vps
    volumes:
      - wp-vps:/var/www/html
      - ./php-config/custom.ini:/usr/local/etc/php/conf.d/custom.ini
    networks:
      - app-network

  webserver:
    depends_on:
      - wordpress
      - vps
      - umami
      - waline
    image: nginx:1.28.0-alpine
    container_name: webserver
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - wordpress:/var/www/html
      - wp-vps:/var/www/wp-vps
      - umami-data:/var/www/umami
      - waline-data:/var/www/waline
      - ./nginx-conf:/etc/nginx/conf.d
      - certbot-etc:/etc/letsencrypt
    networks:
      - app-network

  umami:
    depends_on:
      db-pg:
        condition: service_healthy
    image: ghcr.io/umami-software/umami:postgresql-latest
    container_name: umami
    restart: unless-stopped
    expose:
      - '3000'
    environment:
      DATABASE_URL: ${UMAMI_DATABASE_URL}
      DATABASE_TYPE: postgresql
      APP_SECRET: ${UMAMI_APP_SECRET}
    networks:
      - app-network
  
  waline:
    image: lizheming/waline:latest
    container_name: waline
    restart: always
    expose:
      - "8360"
    volumes:
      - waline-data:/app/data
    environment:
      TZ: 'Asia/Shanghai'
      # 数据库设置
      PG_HOST: 'db-pg'
      PG_PORT: '5432'
      PG_DB: '${WL_PG_DB}'
      PG_USER: '${WL_PG_USER}'
      PG_PASSWORD: '${POSTGRES_WALINE_PWD}'
      SITE_NAME: '码农笔记'
      SITE_URL: 'https://tech.tvzr.com'
      SECURE_DOMAINS: 'tech.tvzr.com,waline.tvzr.com'
      AUTHOR_EMAIL: 'me@tvzr.com'
      IPQPS: 20
      JWT_TOKEN: '${WL_JWT_TOKEN}'
      LEVELS: '0,10,20,50,100,200'
      AKISMET_KEY: '${WL_AKISMET_KEY}'
      #TURNSTILE_KEY: '${WL_TURNSTILE_KEY}'
      #TURNSTILE_SECRET: '${WL_TURNSTILE_SECRET}'
      SMTP_HOST: 'mail.tvzr.com'
      SMTP_PORT: '465'
      SMTP_USER: 'no.reply@tvzr.com'
      SMTP_PASS: '${SMTP_PWD}'
      SMTP_SECURE: 'true'
      SENDER_NAME: '码农笔记'
    depends_on:
      db-pg:
        condition: service_healthy
    networks:
      - app-network

  certbot:
    depends_on:
      - webserver
    image: certbot/certbot
    container_name: certbot
    volumes:
      - certbot-etc:/etc/letsencrypt
      - wordpress:/var/www/html
      - wp-vps:/var/www/wp-vps
      - umami-data:/var/www/umami
      - waline-data:/var/www/waline
    command: >
      certonly --webroot 
      --non-interactive 
      --expand
      -w /var/www/html -d tvzr.com 
      -w /var/www/wp-vps -d vps.tvzr.com 
      --email iat@outlook.com 
      --agree-tos 
      --no-eff-email 
      --force-renewal

volumes:
  certbot-etc:
  wordpress:
  dbdata:
  wp-vps:
  umami-data:
  waline-data:
  pg-data:

networks:
  app-network:
    driver: bridge

重新启动docker compose

在docker-compose.yml目录，执行

1
2
3
4
5
6
# 停止
docker compose down

# 启动
docker compose up -d
# 此时会自动拉取最新的镜像

启动时，umami会自动检测到URL变更，并且自动初始化所需要的表。

可以通过docker logs -f umami查看日志，如果日志中出现如下，即代表初始化运行完成：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
All migrations have been successfully applied.

✓ Database is up to date.

> umami@3.0.3 update-tracker /app
> node scripts/update-tracker.js


> umami@3.0.3 start-server /app
> node server.js

   ▲ Next.js 15.5.9
   - Local:        http://localhost:3000
   - Network:      http://0.0.0.0:3000

 ✓ Starting...
 ✓ Ready in 1235m

转换mysql导出的表

mysql导出的表不能直接给postgresql使用，可以参考官网的方案进行处理。将MySQL迁移到PostgreSQL

用双引号替换反勾号，使其兼容PostgreSQL。

1
sed -i 's/`/"/g' mydbdump.sql

清空postgresql的数据

如果直接导入，会提示如下错误：

1
2
3
4
5
6
7
8
INSERT 0 13
INSERT 0 201
INSERT 0 1
INSERT 0 2
ERROR:  duplicate key value violates unique constraint "user_pkey"
DETAIL:  Key (user_id)=(41e2b680-648e-4b09-bcd7-3e2b10c06264) already exists.
INSERT 0 1
INSERT 0 38

看日志是里面的主键重复，因为初始化的时候会创建一条数据，我们需要先清空表数据。执行下面的语句

1
2
docker exec -i psql -U umamiuser -d umamidb -c "TRUNCATE TABLE \"user\", \"session\", \"website\", \"team\", \"team_user\", \"_prisma_migrations\" CASCADE;"
TRUNCATE TABLE

导入数据到postgresql

1
docker exec -i postgresql psql -U umamiuser -d umamidb < /tmp/mydbdump.sql

此时就可以正常导入数据了。

1
2
3
4
5
6
7
INSERT 0 13
INSERT 0 201
INSERT 0 1
INSERT 0 2
INSERT 0 2
INSERT 0 1
INSERT 0 386

升级完成

到这里就升级完成了，整个过程还是比较简单和顺利的。对比之前，我就需要多运行一个postgresql容器，不过也没有占用多少资源。

1
2
3
4
5
6
CONTAINER ID   NAME         CPU %     MEM USAGE / LIMIT    MEM %     NET I/O           BLOCK I/O         PIDS 
520d2037e3b8   webserver    0.00%     9.168MiB / 3.83GiB   0.23%     12.6MB / 14.3MB   9.34MB / 4.1kB    4 
1857b85d681c   umami        0.00%     229.4MiB / 3.83GiB   5.85%     1.15MB / 2.09MB   41.5MB / 16.4kB   44 
4c2f0afb1926   waline       0.00%     169MiB / 3.83GiB     4.31%     33.2kB / 14.3kB   67.1MB / 4.1kB    18 
99783b642219   postgresql   0.02%     33.93MiB / 3.83GiB   0.87%     202kB / 120kB     7.67MB / 88.4MB   7 
f35027f6d422   db           1.26%     460MiB / 3.83GiB     11.73%    8.06MB / 119MB    941MB / 163MB     47