迁移Wordpress网站到PostgreSQL/Nginx

更新说明

  • 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]