迁移Wordpress网站到PostgreSQL/Nginx

发布 by 猛禽 需要2分钟读完

更新说明

  • 2024-03-31: 更新新版插件说明。
  • 2022-06-06: 增加路径配置，迁移方式seq修正。
  • 2021-12-17: 更新为最新的插件，迁移方式修改。

引子

作为一名不喜欢MySQL的pg党，最近又迁了一个WordPress到PostgreSQL上，顺便就作个笔记以备不时不之需吧。当然，web server也从Apache换成了Nginx，所以这个配置也要改一下。

WordPress的Nginx配置

这个比较简单，因为Nginx不支持 .htaccess 配置只能改在conf里（使用php-fpm）：

    location / {
        root /path_to_your_site;
        index index.html index.php;
        autoindex off;

        try_files $uri $uri/ /index.php?$args;
    }

    location ~ \.php$ {
        fastcgi_pass    unix:/run/php/php-fpm.sock;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  /path_to_your_site$fastcgi_script_name;
        include        fastcgi_params;
    }

如果不是装在根路径下，则需要在location和try_files处加上路径，如下：

    location /<path> {
        ...
        try_files $uri $uri/ /<path>/index.php?$args;
    }

安装WordPress和PG4WP

首先下载WordPress，并解压到你的网站目录。

然后下载kevinoid维护的PG4WP版本，目前这个版本可支持WP5.8.2。

PG4WP解压出来，把其中的pg4wp目录整个上传到WordPress的wp-content下，然后在wp-content目录里创建一个符号链接：

ln -s pg4wp/db.php

注意：pg4wp有一个坑就是，它只替换了mysql_系列函数，但是高版本的Wordpress检测到系统中安装了mysqli_的话，会自动改用 mysqli_系列函数，导致pg4wp替换失败，请修改w p-config.php，在其中指定使用mysql_系列函数：

define("WP_USE_EXT_MYSQL", true);

安装即完成。

创建数据库

先在PostgreSQL里创建用户（yourusername为当前的系统登录用户名，同名用户连数据库可不用密码）和数据库（wpdb）：

sudo su postgres
psql
create role yourusername;
create database wpdb owner yourusername template=template0;

现在可以开始配置一个全新的WordPress站点：用浏览器打开网站，输入数据库相关配置即可，跟用Apache+MySQL时完全一样。

等等，不是说迁移吗？怎么变成新建了呢？别急，接下来就是迁移的工作。

数据库迁移

方法一：手工迁移

之前之所以不是直接把MySQL上的数据库直接迁移过来，是因为PostgreSQL的库结构语句与MySQL有区别，为了保险起见，还是让PG4WP来建结构，只迁移数据部分。

首先是从MySQL里导出数据：

从phpMyAdmin里导出

登录原网站的phpMyAdmin导出数据库，导出时需要注意的选项有：

  • SQL格式使用Traditional，不要用MySQL特有的
  • 用事务包装全部语句
  • 仅导出数据
  • 插入前清空表
  • 插入语句包含字段名

用msyqldump导出

mysqldump -u myuser -p mypass --compatible=postgresql --single-transaction --no-create-info --complete-insert wpdb

不过这个导入前要记得把PostgreSQL库里的表都清空，否则可能因为重复数据出错。

导入PostgreSQL

导出完成后，用编辑器打开导出的SQL文件（如果以压缩方式导出则需要解压后打开），作以下修改：

  • 查找`ID`替换为’ID'（注意两个’引号’不一样）
  • 把剩余的`符号全部去掉（替换为空）
  • 注释或删除SET AUTOCOMMIT xxx和SET TIME_ZONE xxx等PostgreSQL不支持的语句

保存后导入PostgreSQL数据库，用phpPgAdmin或者命令：

psql -d dbname < oldsite.sql

其中dbname为PostgreSQL的数据库名，oldsite.sql就是修改过的原数据库导出SQL文件。

方法二：工具迁移

但是现在有好用的工具，也可以直接迁移了。

首先安装工具PGLoader：

apt install pgloader

注意要看一下版本号，3.4.1或更老的版本至少有一个BUG：即使使用了“加引号”参数，只是导入时建表会保留大小写，导入数据时用的字段名仍然会被改成小写，导致执行时出错。建议用3.6.2或更高的版本——至少我用3.6.2没发现问题。Ubuntu 18.04带的包是3.4.1，只好自己编译新版使用。

然后就可以直接导MySQL数据库了，不过不能用默认参数导，会有一些问题，需要用以下命令文件来导（参数说明见注释，myuser和mypass为MySQL用户名和密码，wpdb为数据库名）：

LOAD DATABASE
     FROM   mysql://myuser:mypass@localhost/wpdb  /* mysql数据源 */
     INTO   postgresql:///wpdb   /* posgresql数据库  */

/* 删除旧表，创建新表和索引，加引号——这个很重要，
  否则大写字段名会在导入后变成小写，
  pgloader 3.4.1或更早的版本有BUG，这个只会保留建表语句的大小写，
  导入数据的时候还是用小写，执行时会出错 */
  WITH include drop, create tables, create indexes,  quote identifiers

/* 默认导入名为wpdb的schema，不在默认的public schema里，
不加search_path会找不到表 */
  SET PostgreSQL PARAMETERS
      search_path to 'wpdb, public, "$user"'

/* 默认导入的sequence名字是：<表名>_<字段名>_seq，
新版pg4wp v3.x直接可以使用，所以以下部分可以不用。
旧版pg4wp v1.x用的是：<表名>_seq，需要改名一下。
注意：这里用的是默认的wp_前缀，如果不同需要自己修改一下。 */
ALTER SEQUENCE IF EXISTS "wp_commentmeta_meta_id_seq" RENAME TO wp_commentmeta_seq
ALTER SEQUENCE IF EXISTS "wp_comments_comment_ID_seq" RENAME TO wp_comments_seq
ALTER SEQUENCE IF EXISTS "wp_links_link_id_seq" RENAME TO wp_links_seq
ALTER SEQUENCE IF EXISTS "wp_options_option_id_seq" RENAME TO wp_options_seq
ALTER SEQUENCE IF EXISTS "wp_postmeta_meta_id_seq" RENAME TO wp_postmeta_seq
ALTER SEQUENCE IF EXISTS "wp_posts_ID_seq" RENAME TO wp_posts_seq
ALTER SEQUENCE IF EXISTS "wp_term_taxonomy_term_taxonomy_id_seq" RENAME TO wp_term_taxonomy_seq
ALTER SEQUENCE IF EXISTS "wp_termmeta_meta_id_seq" RENAME TO wp_termmeta_seq
ALTER SEQUENCE IF EXISTS "wp_terms_term_id_seq" RENAME TO wp_terms_seq
ALTER SEQUENCE IF EXISTS "wp_usermeta_umeta_id_seq" RENAME TO wp_usermeta_seq
ALTER SEQUENCE IF EXISTS "wp_users_ID_seq" RENAME TO wp_users_seq

保存为一个文件，比如wpdb.load，然后执行导入：

pgloader wpdb.load

完成后再打开网站看看，迁移完成。

经过这样的迁移以后，网站的速度更快，占用服务器资源更少，你的生活也将更愉快……（雾

实际上我在一台512M内存的机器上把精细优化内存配置的MySQL 5.7换成默认配置的PostgreSQL 10以后，内存使用下降了近100M，WordPress的响应速度基本感受不到影响，甚至还略快了一点。

推送到[go4pro.org]