跳轉到內容

將 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://postgres.tw/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://wikibook.tw/w/index.php?title=Converting_MySQL_to_PostgreSQL&oldid=3773254”
華夏公益教科書