pg資料庫吳mysql,pg資料庫使用

本文目錄一覽：

• 1、pgsql比mysql好在哪
• 2、postgresql的資料庫怎麼遷移到mysql-CSDN論壇
• 3、postgresql和mysql的區別
• 4、postgresql的語法與mysql有什麼區別

pgsql比mysql好在哪

Pgsql和Mysql的對比

工作中用過這兩個資料庫，但都不是太深入，僅限於用而已，但給我留下的印象就是Pgsql更好些，因為這兩個庫我都遇到過數據丟失的問題，前者我通過網上方法加自己的判斷有驚無險地恢復了，而後者搜索各種資料加問身邊的專家都沒辦法。

剛網上搜了一下兩者的區別，總體的感覺也是前者是最好的開源關係資料庫，而後者是互聯網行業應用最廣泛的資料庫， 可能應用等多發現的坑也多，網上相關資料也多。如果讓我個人選沒特殊要求情況下會選前者。

關於兩個的區別可以看知乎上相關問題，回答很精彩，

其中一個如下。

一、 PostgreSQL 的穩定性極強， Innodb 等引擎在崩潰、斷電之類的災難場景下抗打擊能力有了長足進步，然而很多 MySQL 用戶都遇到過Server級的資料庫丟失的場景——mysql系統庫是MyISAM的，相比之下，PG資料庫這方面要好一些。

二、任何系統都有它的性能極限，在高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySQL 明顯出現一個波峰後下滑（5.5版本之後，在企業級版本中有個插件可以改善很多，不過需要付費）。

三、PG 多年來在 GIS 領域處於優勢地位，因為它有豐富的幾何類型，實際上不止幾何類型，PG有大量字典、數組、bitmap 等數據類型，相比之下mysql就差很多，instagram就是因為PG的空間資料庫擴展POSTGIS遠遠強於MYSQL的my spatial而採用PGSQL的。

四、PG 的「無鎖定」特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和PGSQL的MVCC實現有關係。

五、PG 的可以使用函數和條件索引，這使得PG資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。

六、PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，比如分析函數（ORACLE的叫法，PG里叫window函數），還可以用多種語言來寫存儲過程，對於R的支持也很好。這一點上MYSQL就差很遠，很多分析功能都不支持，騰訊內部數據存儲主要是MYSQL，但是數據分析主要是HADOOP+PGSQL（聽李元佳說過，但是沒有驗證過）。

七、PG 得多種集群架構可以選擇，plproxy 可以支持語句級的鏡像或分片，slony 可以進行欄位級的同步設置，standby 可以構建WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便，操作非常簡單。

八、一般關係型資料庫的字元串有限定長度8k左右，無限長 TEXT 類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL語法內置正則表達式，可以索引，還可以全文檢索，或使用xml xpath。用PG的話，文檔資料庫都可以省了。

九，對於WEB應用來說，複製的特性很重要，mysql到現在也是非同步複製，pgsql可以做到同步，非同步，半同步複製。還有mysql的同步是基於binlog複製，類似oracle golden gate,是基於stream的複製，做到同步很困難，這種方式更加適合異地複製，pgsql的複製基於wal，可以做到同步複製。同時，pgsql還提供stream複製。

postgresql的資料庫怎麼遷移到mysql-CSDN論壇

MySQL資料庫遷移到PostgreSQL

查了不少資料，也嘗試了一些，最後採用的辦法如下：

1. 導出mysql表定義（無數據）

mysqldump –no-data [dbname] dbdef.sql

2. 使用mysql2postgres把腳本轉換為pgsql

3. 上面生成的腳本還不一定很完美，可以嘗試導入pgsql，調試錯誤並手動修改之。我遇到的問題就只有一個，mysql列定義中的zerofill需要手工去掉。一些unsinged定義會生成constraint，如果不需要可以去掉。另外，trigger都有問題，只能後面手工重建

4. 導出mysql數據：

mysqldump -v -nt –complete-insert=TRUE –compact –no-create-info –skip-quote-names [dbname] dbdata.sql

老一些版本的pgsql如果不支持批量插入的話還需要加上–extended-insert=FALSE，這個性能損失巨大。

5. 轉義符

mysql默認字元串里的’\’是轉義符，而pgsql默認不是，修改postgresql.conf:

backslash_quote = on

escape_string_warning = off

standard_conforming_strings = off

數據導入完成後可以改回默認值。

5. pgsql里導入表定義和數據

psql -d [dbname] dbdef.sql

psql -d [dbname] dbdata.sql

6. 重建trigger

7. 自增主鍵（欄位）的處理

由於導入數據時此欄位都是有值的，所以pgsql裡面seq並不會增加，可以用如下語句設置自增列的當前值：

SELECT setval(‘sample_id_seq’,max(id)) from sample;

postgresql和mysql的區別

特性 MySQL PostgreSQL

實例 通過執行 MySQL 命令（mysqld）啟動實例。一個實例可以管理一個或多個資料庫。一台伺服器可以運行多個 mysqld 實例。一個實例管理器可以監視 mysqld 的各個實例。

通過執行 Postmaster 進程（pg_ctl）啟動實例。一個實例可以管理一個或多個資料庫，這些資料庫組成一個集群。集群是磁碟上的一個區域，這個區域在安裝時初始化並由一個目錄組成，所有數據都存儲在這個目錄中。使用 initdb 創建第一個資料庫。一台機器上可以啟動多個實例。

資料庫 資料庫是命名的對象集合，是與實例中的其他資料庫分離的實體。一個 MySQL 實例中的所有資料庫共享同一個系統編目。 資料庫是命名的對象集合，每個資料庫是與其他資料庫分離的實體。每個資料庫有自己的系統編目，但是所有資料庫共享 pg_databases。

數據緩衝區 通過 innodb_buffer_pool_size 配置參數設置數據緩衝區。這個參數是內存緩衝區的位元組數，InnoDB 使用這個緩衝區來緩存表的數據和索引。在專用的資料庫伺服器上，這個參數最高可以設置為機器物理內存量的 80%。 Shared_buffers 緩存。在默認情況下分配 64 個緩衝區。默認的塊大小是 8K。可以通過設置 postgresql.conf 文件中的 shared_buffers 參數來更新緩衝區緩存。

資料庫連接 客戶機使用 CONNECT 或 USE 語句連接資料庫，這時要指定資料庫名，還可以指定用戶 id 和密碼。使用角色管理資料庫中的用戶和用戶組。 客戶機使用 connect 語句連接資料庫，這時要指定資料庫名，還可以指定用戶 id 和密碼。使用角色管理資料庫中的用戶和用戶組。

身份驗證 MySQL 在資料庫級管理身份驗證。 基本只支持密碼認證。 PostgreSQL 支持豐富的認證方法：信任認證、口令認證、Kerberos 認證、基於 Ident 的認證、LDAP 認證、PAM 認證

加密 可以在表級指定密碼來對數據進行加密。還可以使用 AES_ENCRYPT 和 AES_DECRYPT 函數對列數據進行加密和解密。可以通過 SSL 連接實現網路加密。 可以使用 pgcrypto 庫中的函數對列進行加密/解密。可以通過 SSL 連接實現網路加密。

審計 可以對 querylog 執行 grep。 可以在表上使用 PL/pgSQL 觸發器來進行審計。

查詢解釋 使用 EXPLAIN 命令查看查詢的解釋計劃。 使用 EXPLAIN 命令查看查詢的解釋計劃。

備份、恢復和日誌 InnoDB 使用寫前（write-ahead）日誌記錄。支持在線和離線完全備份以及崩潰和事務恢復。需要第三方軟體才能支持熱備份。 在數據目錄的一個子目錄中維護寫前日誌。支持在線和離線完全備份以及崩潰、時間點和事務恢復。 可以支持熱備份。

JDBC 驅動程序 可以從 參考資料 下載 JDBC 驅動程序。 可以從 參考資料 下載 JDBC 驅動程序。

表類型 取決於存儲引擎。例如，NDB 存儲引擎支持分區表，內存引擎支持內存表。 支持臨時表、常規表以及範圍和列表類型的分區表。不支持哈希分區表。 由於PostgreSQL的表分區是通過表繼承和規則系統完成了，所以可以實現更複雜的分區方式。

索引類型 取決於存儲引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-樹、哈希、R-樹和 Gist 索引。

約束 支持主鍵、外鍵、惟一和非空約束。對檢查約束進行解析，但是不強制實施。 支持主鍵、外鍵、惟一、非空和檢查約束。

存儲過程和用戶定義函數 支持 CREATE PROCEDURE 和 CREATE FUNCTION 語句。存儲過程可以用 SQL 和 C++ 編寫。用戶定義函數可以用 SQL、C 和 C++ 編寫。 沒有單獨的存儲過程，都是通過函數實現的。用戶定義函數可以用 PL/pgSQL（專用的過程語言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 編寫。

觸發器 支持行前觸發器、行後觸發器和語句觸發器，觸發器語句用過程語言複合語句編寫。 支持行前觸發器、行後觸發器和語句觸發器，觸發器過程用 C 編寫。

系統配置文件 my.conf Postgresql.conf

資料庫配置 my.conf Postgresql.conf

客戶機連接文件 my.conf pg_hba.conf

XML 支持 有限的 XML 支持。 有限的 XML 支持。

數據訪問和管理伺服器 OPTIMIZE TABLE —— 回收未使用的空間並消除數據文件的碎片

myisamchk -analyze —— 更新查詢優化器所使用的統計數據（MyISAM 存儲引擎）

mysql —— 命令行工具

MySQL Administrator —— 客戶機 GUI 工具 Vacuum —— 回收未使用的空間

Analyze —— 更新查詢優化器所使用的統計數據

psql —— 命令行工具

pgAdmin —— 客戶機 GUI 工具

並發控制 支持表級和行級鎖。InnoDB 存儲引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 語句在事務級設置隔離級別。 支持表級和行級鎖。支持的 ANSI 隔離級別是 Read Committed（默認 —— 能看到查詢啟動時資料庫的快照）和 Serialization（與 Repeatable Read 相似 —— 只能看到在事務啟動之前提交的結果）。使用 SET TRANSACTION 語句在事務級設置隔離級別。使用 SET SESSION 在會話級進行設置。

MySQL相對於PostgreSQL的劣勢：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整個MySQL資料庫都由Oracle控制。

BSD協議，沒有被大公司壟斷。

對複雜查詢的處理較弱，查詢優化器不夠成熟

很強大的查詢優化器，支持很複雜的查詢處理。

只有一種表連接類型:嵌套循環連接(nested-loop),不支持排序-合併連接(sort-merge join)與散列連接(hash join)。

都支持

postgresql的語法與mysql有什麼區別

一、 PostgreSQL 的穩定性極強， Innodb 等引擎在崩潰、斷電之類的災難場景下抗打擊能力有了長足進步，然而很多 MySQL 用戶都遇到過Server級的資料庫丟失的場景——mysql系統庫是MyISAM的，相比之下，PG資料庫這方面要好一些。

二、任何系統都有它的性能極限，在高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySQL 明顯出現一個波峰後下滑（5.5版本之後，在企業級版本中有個插件可以改善很多，不過需要付費）。

三、PG 多年來在 GIS 領域處於優勢地位，因為它有豐富的幾何類型，實際上不止幾何類型，PG有大量字典、數組、bitmap 等數據類型，相比之下mysql就差很多，instagram就是因為PG的空間資料庫擴展POSTGIS遠遠強於MYSQL的my spatial而採用PGSQL的。

四、PG 的「無鎖定」特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和PGSQL的MVCC實現有關係。

五、PG 的可以使用函數和條件索引，這使得PG資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。

六、PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，比如分析函數（ORACLE的叫法，PG里叫window函數），還可以用多種語言來寫存儲過程，對於R的支持也很好。這一點上MYSQL就差的很遠，很多分析功能都不支持，騰訊內部數據存儲主要是MYSQL，但是數據分析主要是HADOOP+PGSQL（聽李元佳說過，但是沒有驗證過）。

七、PG 的有多種集群架構可以選擇，plproxy 可以支持語句級的鏡像或分片，slony 可以進行欄位級的同步設置，standby 可以構建WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便，操作非常簡單。

八、一般關係型資料庫的字元串有限定長度8k左右，無限長 TEXT 類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL語法內置正則表達式，可以索引，還可以全文檢索，或使用xml xpath。用PG的話，文檔資料庫都可以省了。

九，對於WEB應用來說，複製的特性很重要，mysql到現在也是非同步複製，pgsql可以做到同步，非同步，半同步複製。還有mysql的同步是基於binlog複製，類似oracle golden gate,是基於stream的複製，做到同步很困難，這種方式更加適合異地複製，pgsql的複製基於wal，可以做到同步複製。同時，pgsql還提供stream複製。

十，pgsql對於numa架構的支持比mysql強一些，比MYSQL對於讀的性能更好一些，pgsql提交可以完全非同步，而mysql的內存表不夠實用（因為表鎖的原因）

最後說一下我感覺 PG 不如 MySQL 的地方。

第一，MySQL有一些實用的運維支持，如 slow-query.log ，這個pg肯定可以定製出來，但是如果可以配置使用就更好了。

第二是mysql的innodb引擎，可以充分優化利用系統所有內存，超大內存下PG對內存使用的不那麼充分，

第三點，MySQL的複製可以用多級從庫，但是在9.2之前，PGSQL不能用從庫帶從庫。

第四點，從測試結果上看，mysql 5.5的性能提升很大，單機性能強於pgsql，5.6應該會強更多.

第五點，對於web應用來說,mysql 5.6 的內置MC API功能很好用，PGSQL差一些。

另外一些：

pgsql和mysql都是背後有商業公司，而且都不是一個公司。大部分開發者，都是拿工資的。

說mysql的執行速度比pgsql快很多是不對的，速度接近，而且很多時候取決於你的配置。

對於存儲過程，函數，視圖之類的功能，現在兩個資料庫都可以支持了。

另外多線程架構和多進程架構之間沒有絕對的好壞，oracle在unix上是多進程架構，在windows上是多線程架構。

很多pg應用也是24/7的應用，比如skype. 最近幾個版本VACUUM基本不影響PGSQL 運行，8.0之後的PGSQL不需要cygwin就可以在windows上運行。

至於說對於事務的支持，mysql和pgsql都沒有問題。