跳转到内容

将 MySQL 转换为 PostgreSQL

Add links
25% developed
来自维基教科书，开放世界中的开放书籍
 一位维基教科书用户建议将本书或章节合并到SQL 方言参考中。
请在讨论页面上讨论是否应该进行此合并。

非常简短的介绍

[编辑 | 编辑源代码]

您可能在网上阅读了许多同名的短文，但它们只是您需要的零散信息。现在是将它们整合在一起的时候了。

您有一个MySQL 项目，突然发现您需要切换到PostgreSQL。突然，您发现 SQL 有许多不同的方言，您原本看似简单的代码却抛出了很多错误。您没有时间从头重写代码，也许以后会......

实际上，可能有一些很好的理由进行切换......

使用 PostgreSQL，您仍然可能感觉自己像一个二等公民，但并不像被忽视的那样。有一些大型项目，如Asterisk、Horde 或DBMail，它们已经认识到它的优点，尽管 MySQL 是它们的首选数据库，但它们正在努力使其在这里也能正常运行。

检查服务器是否正在运行

[编辑 | 编辑源代码]

您很可能不需要这一章，但简要来说：在您在 Linux 机器上安装了 PostgreSQL 包之后（无论是从包安装还是按照这些说明），您需要执行类似的操作

su -
su - postgres
createdb test
psql test
=# create user username password ' password ';
-- To change a password:
=# alter role username password ' password ';
=# create database databasename with encoding 'utf8';
=# grant all privileges on database databasename to username;
=# \l
=# \c databasename
=# \q

vi /etc/postgresql/pg_hba.conf

host    all         all         0.0.0.0           0.0.0.0            md5

请务必使用 iptables 解决此安全问题！

/etc/init.d/postgresql reload 或 /usr/lib/postgresql/bin/pg_ctl reload

postmaster 已成功发出信号

psql -h 服务器 -d 数据库名称 -U 用户名

数据库名称=>

转换和导入

[编辑 | 编辑源代码]

使用 pgloader

[编辑 | 编辑源代码]

查看http://pgloader.io，您可以通过一条命令将 MySQL 数据库迁移到 PostgreSQL。

pgloader mysql://user@localhost/dbname postgresql:///dbname

这将使用默认的强制转换规则集处理类型强制转换，还会在 MySQL 中进行模式发现并在 PostgreSQL 中进行创建，包括表、列、约束（主键、外键、NOT NULL）、默认值和辅助索引。数据会实时转换为 PostgreSQL 可接受的格式，这包括消除零日期（我们的日历中没有零年，也没有零月或零日，虽然 MySQL 不在乎，但 PostgreSQL 对此非常有主张，如果您使用零年，那么您正在处理的不是日期）。

对于更高级的选项，或者如果您想要更改默认设置，pgloader MySQL 支持[1] 允许您使用其自己的语言编写完整的命令，其中包含不同的规则来描述您想要迁移的执行方式。

使用 SQL 转储的常见方法

[编辑 | 编辑源代码]

使用以下命令转储您的表

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

但即使如此，您仍然需要更改转义字符（替换\t为^I, \n为^M，单引号（'）为双单引号，双（转义）反斜杠（\\）为单反斜杠）。这无法通过sed 命令轻松完成，您可能需要编写脚本（Ruby、Perl 等）。有一个MySQL 到 PostgreSQL Python 转换脚本（您需要在导出 mysqldump 时使用 --default-character-set=utf8 才能使其工作）。更好的解决方案是在转储之前添加以下几行

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';

这些选项将强制 PostgreSQL 解析器接受不兼容 ANSI-SQL 的转义序列（Postgre 仍然会发出关于它的提示；您可以安全地忽略它们）。请勿在全局范围内设置这些选项：这可能会损害服务器的安全！

您还需要手动修改数据类型等，这将在后面讨论。

在转换表之后，以与在 MySQL 中使用相同的方式导入它们，即

psql -h server -d databasename -U username -f data.sql

使用 CSV 文件导出

[编辑 | 编辑源代码]

当您拥有包含二进制数据的巨大 SQL 转储时，修改数据结构并不容易，因此还有一种方法可以将数据导出到 PostgreSQL。Mysql 有一个选项可以将数据库中的每个表导出为单独的 .sql 文件，其中包含表结构，以及包含 CSV 格式表数据的 .txt 文件。

mysqldump -u username -p --compatible=postgresql -T /path/to/export databasename

请注意，/path/to/export 应该是运行 mysqld 的用户的可写目录，在大多数情况下是 mysqld。之后，您应该根据 PostgreSQL 格式修改表结构

  • 转换数据类型
  • 创建单独的键定义
  • 替换转义字符

当表结构准备就绪后，您应该按照前面所示的方式加载它。您应该准备数据文件：将回车符替换为 "\r"，并删除数据编码中无效的字符。以下是一个示例 bash 脚本，说明如何执行此操作以及如何将所有数据加载到您的数据库中

#!/bin/bash

CHARSET="utf-8" #your current database charset
DATADIR="/path/to/export"
DBNAME="databasename"

for file in $DATADIR/*.txt; do
  TMP=${file%.*}
  TABLE=${TMP##*/}
  echo "preparing $TABLE"

  #replace carriage return
  sed 's/\r/\\r/g' $file > /tmp/$TABLE.export.tmp

  #cleanup non-printable and wrong sequences for current charset
  iconv -t $CHARSET -f $CHARSET -c < /tmp/$TABLE.export.tmp > /tmp/$TABLE.export.tmp.out

  echo "loading $TABLE"
  /usr/bin/psql $DBNAME -c "copy $TABLE from '/tmp/$TABLE.export.tmp.out'"

  #clean up
  rm /tmp/$TABLE.export.tmp /tmp/$TABLE.export.tmp.out
done

环境

[编辑 | 编辑源代码]

Perl

[编辑 | 编辑源代码]

您需要安装相应的 DBD 包。在 Debian/Ubuntu 中，运行 apt-get install libdbd-pg-perl。

快速修改代码

[编辑 | 编辑源代码]

Perl

[编辑 | 编辑源代码]
MySQL PostgreSQL 注释
$db=DBI->connect("dbi:mysql:database= ... )
 
$db=DBI->connect("dbi:Pg:database= ... )
 您所要做的就是将mysql更改为Pg。注意大小写敏感性。

SQL

[编辑 | 编辑源代码]

语法

[编辑 | 编辑源代码]
MySQL PostgreSQL 注释
#
 
--
 MySQL 接受非标准的 # 来开始注释行；PostgreSQL 使用 ANSI 标准双破折号；使用 ANSI 标准，两个数据库都能理解。（但是，MySQL 要求 -- 之后有一个空格，而在 PostgreSQL 中这不是强制性的）
' " vs. `
 
' vs. "
 MySQL 使用 ' 或 " 来引用值（例如 WHERE name = "John"）。这不是数据库的 ANSI 标准。PostgreSQL 只使用单引号来引用值（例如 WHERE name = 'John'）。双引号用于引用系统标识符；字段名、表名等（例如 WHERE "last name" = 'Smith'）。MySQL 使用 `（重音符或反引号）来引用系统标识符，这绝对是非标准的。注意：您可以使用 SET sql_mode='ANSI_QUOTES' 让 MySQL 像 PostgreSQL 一样解释引号。
... WHERE lastname="smith"
 
... WHERE lower(lastname)='smith'
 PostgreSQL 对字符串比较区分大小写。值 'Smith' 与 'smith' 不相同。对于许多来自 MySQL（在 MySQL 中，VARCHAR 和 TEXT 列区分大小写，除非设置了“binary”标志）和其他小型数据库系统（如 Microsoft Access）的用户来说，这是一个很大的变化。在 PostgreSQL 中，您可以：
  • 在查询中使用正确的大小写。（例如 WHERE lastname='Smith'）
  • 使用转换函数，例如 lower() 进行搜索。（例如 WHERE lower(lastname)='smith'）
  • 使用不区分大小写的运算符，例如 ILIKE 或 *~
`LastName` = `lastname`

可能还有其他方法？

 
"LastName" <> "lastname"
 PostgreSQL 中的数据库、表、字段和列名称不区分大小写，除非您在创建它们时在名称周围使用双引号，在这种情况下它们区分大小写。在 MySQL 中，表名区分大小写与否取决于您使用的操作系统。
注意，PostgreSQL 会积极地将所有未加引号的名称转换为小写，并在查询结果中返回小写！
'foo' || 'bar'
表示 OR		 
'foo' || 'bar'
表示字符串连接 (= 'foobar')		 MySQL 接受 C 语言运算符进行逻辑运算，SQL 要求 AND、OR；使用 SQL 标准关键字进行逻辑运算，两个数据库都能理解。

数据类型

[编辑 | 编辑源代码]

此表的想法部分来自自动转储转换脚本 [1]。官方文档

可以使用 psql 的内部斜杠命令 \dT 查看可用数据类型的列表。

MySQL PostgreSQL ANSI 标准 SQL 注释
TINYINT
SMALLINT
MEDIUMINT
BIGINT
 
SMALLINT
SMALLINT
INTEGER
BIGINT

INTEGER
INTEGER
INTEGER
NUMERIC(20)
请参见 [2]；PostgreSQL 中的 integer 大小为 4 字节有符号 (-2147483648 – +2147483647)
TINYINT UNSIGNED
SMALLINT UNSIGNED
MEDIUMINT UNSIGNED
INT UNSIGNED
BIGINT UNSIGNED

SMALLINT
INTEGER
INTEGER
BIGINT
NUMERIC(20)

INTEGER
INTEGER
INTEGER
NUMERIC(10)
NUMERIC(20)
SQL 不认识 UNSIGNED，所有数字都是有符号的。
FLOAT
FLOAT UNSIGNED

REAL
REAL

FLOAT4
FLOAT4

DOUBLE
 
DOUBLE PRECISION
 
FLOAT8
 
BOOLEAN
 
BOOLEAN
 
BOOLEAN
 MySQL 布尔值是 TINYINT(1) 的别名；PostgreSQL 不会自动将数字转换为布尔值。
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

TEXT
TEXT
TEXT
TEXT

TEXT
TEXT
TEXT
TEXT

BINARY(n)
VARBINARY(n)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

BYTEA
BYTEA
BYTEA
BYTEA
BYTEA
BYTEA

BIT(n)
BIT VARYING(n)
TEXT
TEXT
TEXT
TEXT

ZEROFILL
 
not available
 
not available
 
DATE
TIME
DATETIME
TIMESTAMP

DATE
TIME [WITHOUT TIME ZONE]
TIMESTAMP [WITHOUT TIME ZONE]
TIMESTAMP [WITHOUT TIME ZONE]

DATE
TIME
TIMESTAMP
TIMESTAMP

column SERIAL

等于

column BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

或

column INT DEFAULT SERIAL

等于

column INT NOT NULL AUTO_INCREMENT UNIQUE

column SERIAL

等于

CREATE SEQUENCE name;
CREATE TABLE table ( 
	column INTEGER NOT NULL
	DEFAULT nextval(name)
);

column GENERATED BY DEFAULT
PostgreSQL 注意

SERIAL = 1 – 2147483647
BIGSERIAL = 1 – 9223372036854775807

SERIAL 实际上是一个名为 SEQUENCE 的实体。它独立于您的表存在。如果您想在删除表后清理系统，还需要 DROP SEQUENCE name。 关于该主题的更多信息...

MySQL 注意

column SERIAL PRIMARY KEY

或

column SERIAL,
PRIMARY KEY(column)

这将导致 column 有 2 个索引。一个将由 PRIMARY KEY 约束生成，另一个由 SERIAL 别名中存在的隐式 UNIQUE 约束生成。这已被报告为错误，可能会得到纠正。

column ENUM (value1, value2, [...])

column VARCHAR(255) NOT NULL,
CHECK (column IN (value1, value2, [...]))

或

CREATE TYPE mood AS ENUM ('sad','ok','happy');
CREATE TABLE person ( current_mood mood ... )

column VARCHAR(255) NOT NULL,
CHECK (column IN (value1, value2, [...]))
在 8.3 之前的 PostgreSQL 中没有 ENUM 类型，因此在使用 < 8.3 时需要使用约束来模拟它。

语言结构

[编辑 | 编辑源代码]
MySQL PostgreSQL 注释
DESCRIBE table
使用 psql
\d table

或

SELECT
	a.attname AS Field,
	t.typname || '(' || a.atttypmod || ')' AS Type,
	CASE WHEN a.attnotnull = 't' THEN 'YES' ELSE 'NO' END AS Null,
	CASE WHEN r.contype = 'p' THEN 'PRI' ELSE '' END AS Key,
	(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid), '\'(.*)\'')
		FROM
			pg_catalog.pg_attrdef d
		WHERE
			d.adrelid = a.attrelid
			AND d.adnum = a.attnum
			AND a.atthasdef) AS Default,
	'' as Extras
FROM
	pg_class c 
	JOIN pg_attribute a ON a.attrelid = c.oid
	JOIN pg_type t ON a.atttypid = t.oid
	LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid 
		AND r.conname = a.attname
WHERE
	c.relname = 'tablename'
	AND a.attnum > 0
	
ORDER BY a.attnum
 PostgreSQL 没有实现 SQL 扩展；它使用 psql 的内部斜杠命令代替。（注意：在 mysql 客户端中，\d 是 DROP TABLE 的简写）
DROP TABLE IF EXISTS table

DROP TABLE IF EXISTS table
DROP TABLE 子句中的 IF EXISTS 直到 PostgreSQL 8.2 才可用。
REPLACE [INTO] table [(column, [...])] VALUES (value, [...])

或

INSERT INTO table
	(column1, column2, [...])
VALUES
	(value1, value2, [...])
ON DUPLICATE KEY UPDATE
	column1 = value1, column2 = value2

CREATE FUNCTION someplpgsqlfunction() RETURNS void AS $$
BEGIN
	IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN
		UPDATE phonebook
		SET extension = '1234' WHERE name = 'john doe';
	ELSE
		INSERT INTO phonebook VALUES( 'john doe', '1234' );
	END IF;

	RETURN;
END;
$$ LANGUAGE plpgsql;
PostgreSQL 没有实现 REPLACE SQL 扩展。提供的解决方案使用 PL/pgSQL。

（注意：MySQL REPLACE INTO 会删除旧行并插入新行，而不是就地更新。）

SELECT ... INTO OUTFILE '/var/tmp/outfile'
 
COPY ( SELECT ... ) TO '/var/tmp/outfile'
 
SHOW DATABASES
 使用 -l 参数运行 psql

或者使用 psql

\l

或

SELECT datname AS Database FROM pg_database
	WHERE datistemplate = 'f'
PostgreSQL 没有实现 SQL 扩展。
SHOW TABLES
 使用 psql
\dt

或

SELECT c.relname AS Tables_in FROM pg_catalog.pg_class c
	LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
	AND c.relkind = 'r'
	AND relname NOT LIKE 'pg_%'
ORDER BY 1
PostgreSQL 没有实现 SQL 扩展；它使用 psql 的内部斜杠命令代替。
SELECT ... LIMIT offset, limit

或

SELECT ... LIMIT limit OFFSET offset

SELECT ... LIMIT limit OFFSET offset

CREATE TABLE table (
	column ... ,
	{INDEX|KEY} [name] (column, [...])
)

或

CREATE INDEX name ON table (column, [...])

CREATE INDEX name ON table (column, [...])

USE database ;
使用 psql
\c database

UNLOCK TABLES;

-- nothing
"没有 UNLOCK TABLE 命令；锁总是在事务结束时释放。"（https://postgresql.ac.cn/docs/8.1/static/sql-lock.html）

函数

[编辑 | 编辑源代码]
MySQL PostgreSQL 注释
LAST_INSERT_ID() CURRVAL('serial_variable') 注意：这不仅是“替换字符串”解决方案，因为您需要知道 SERIAL 变量的名称（不像 MySQL 中的 AUTO_INCREMENT）。还要注意，PostgreSQL 可以使用最近 SQL 命令插入的最后一行数据的 OID。

注意 2：替换 LAST_INSERT_ID() 的更好方法是创建一个规则，因为这样可以避免竞争条件

CREATE RULE get_{table}_id_seq AS ON INSERT TO {table} DO SELECT currval('{table}_id_seq'::text) AS id;

（使用方法有点奇怪，您从 INSERT 语句中获得了结果，但它运行得很好）

注意 3：另一种更易读的方法

INSERT INTO mytable VALUES (...) RETURNING my_serial_column_name;

常见错误

[编辑 | 编辑源代码]
  • 错误：关系“something”不存在 - 通常表不存在，因为您可能没有使用新的数据类型或语法创建它。还要注意大小写折叠问题；PostgreSQL = postgresql != "PostgreSQL"。
  • 准备好的语句“dbdpg_X”不存在 -

PL/pgSQL

[编辑 | 编辑源代码]

安装

[编辑 | 编辑源代码]

在 9.0 之前的版本中，您必须为每个数据库显式地启用它

your_unix$ su - postgres
your_unix$ .../pgsql/bin/createlang plpgsql -h localhost -d databasename

（在 BSD 系统上，用户名是 pgsql）

运行函数

[编辑 | 编辑源代码]
SELECT definedfunction();

管理

[编辑 | 编辑源代码]

要使用与 MySQL 相同的 备份技术，在 /etc/logrotate.d/postgresql-dumps 中

/dumps/postgresql/*/*.dump.gz {
        daily
        rotate 20
        dateext
        nocompress
        sharedscripts
        create
        postrotate
                for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do
                        if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi
                        # compress even in custom format, because it can be compressed more
                        su - postgres -c "pg_dump --format=custom $i" | gzip > /dumps/postgresql/$i/$i.dump.gz
                done
        endscript
}
/dumps/postgresql/*/*.sql.gz {
        daily
        rotate 20
        dateext
        nocompress
        sharedscripts
        create
        postrotate
                for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do
                        if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi
                        su - postgres -c "pg_dump --format=plain $i" | gzip > /dumps/postgresql/$i/$i.sql.gz
                done
        endscript
}
/dumps/postgresql/*/*.tar.gz {
        daily
        rotate 20
        dateext
        nocompress
        sharedscripts
        create
        postrotate
                for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do
                        if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi
                        su - postgres -c "pg_dump --format=tar $i" | gzip > /dumps/postgresql/$i/$i.tar.gz
                done
        endscript
}
[编辑 | 编辑源代码]

参考资料

[编辑 | 编辑源代码]
  1. ↑ http://pgloader.io/howto/mysql.html
取自“https://wikibooks.cn/w/index.php?title=Converting_MySQL_to_PostgreSQL&oldid=3773254”
华夏公益教科书