Ⅰ 如何退出postgresq資料庫
啟動資料庫伺服器(posgres用戶)
[postgres@localhost bin]$ postgres D /opt/postgresql/data/ > /opt/postgresql/log/pg_serverlog >& &
[]
當然如果設置了環境變數
PGDATA=/opt/postgresql/data
export PGDATA
後可使用pg_ctl工具進行啟動:
[postgres@localhost log]$ pg_ctl start l /opt/postgresql/log/pg_serverlog
pg_ctl: another server might be running; trying to start server anyway
pg_ctl: could not start server
Examine the log output
[postgres@localhost log]$
因為之前已經啟動所以列印another server might be running此時查看日誌有如下信息:
[postgres@localhost log]$ cat pg_serverlog
FATAL: lock file postmasterpid already exists
HINT: Is another postmaster (PID ) running in data directory /opt/postgresql/data?
[postgres@localhost log]$
當然最簡的啟動方式是
[postgres@localhost ~]$ pg_ctl start
server starting
[postgres@localhost ~]$ LOG: database system was shut down at :: CST
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
如果要在操作系統啟動時就啟動PG可以在/etc/rcd/rclocal 文件中加以下語句
/opt/postgresql/bin/pg_ctl start l /opt/postgresql/log/pg_serverlog D /opt/postgresql/data
關閉伺服器
最簡單方法
[postgres@localhost ~]$ pg_ctl stop
waiting for server to shut down done
server stopped
與Oracle相同在關閉時也可採用不同的模式簡介如下
SIGTERM
不再允許新的連接但是允許所有活躍的會話正常完成他們的工作只有在所有會話都結束任務後才關閉這是智能關閉
SIGINT
不再允許新的連接向所有活躍伺服器發送 SIGTERM(讓它們立刻退出)然後等待所有子進程退出並關閉資料庫這是快速關閉
SIGQUIT
令 postgres 向所有子進程發送 SIGQUIT 並且立即退出(所有子進程也會立即退出)而不會妥善地關閉資料庫系統這是立即關閉這樣做會導致下次啟動時的恢復(通過重放 WAL 日誌)我們推薦只在緊急的時候使用這個方法
SIGKILL
此選項盡量不要使用這樣會阻止伺服器清理共享內存和信號燈資源那樣的話你只能在啟動伺服器之前自己手工做這件事另外SIGKILL 直接把 postgres 殺掉而不會等它把信號中繼給它的子進程因此我們還需要手工殺掉每個獨立子進程
使用方法舉例
[postgres@localhost ~]$ pg_ctl stop o SIGTERM
LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
waiting for server to shut downLOG: shutting down
LOG: database system is shut down
done
server stopped
[postgres@localhost ~]$
最快速關閉方法kill postgres 進程
[postgres@localhost ~]$ kill INT `head /opt/postgresql/data/postmasterpid`
[postgres@localhost ~]$ LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut down
附postgre啟動後的進程如下:
[postgres@localhost ~]$ ps ef|grep post
root : pts/ :: su postgres
postgres : pts/ :: bash
postgres : pts/ :: /opt/postgresql/bin/postgres
postgres : ? :: postgres: writer process
postgres : ? :: postgres: wal writer process
postgres : ? :: postgres: autovacuum launcher process
postgres : ? :: postgres: stats collector process
postgres : pts/ :: ps ef
postgres : pts/ :: grep post
[postgres@localhost ~]$
Ⅱ 如何使用drupal直接操作postgres資料庫
Drupal是主流的CMS系統之一。因此具備Drupal技能的開發人員有很多。
考慮這樣的情況，如果你有一個現有的非Drupal系統，然後有一個Drupal開發團隊，你希望這個Drupal團隊幫你擴展應用。
那麼這個時候，Drupal應用就應該設計成是支持多資料庫的。
在Drupal中使用多數據，也很簡單。
1、首先在配置文件（Settings.php)中把默認的資料庫配置項改成數組形式：
<?php
$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['jigo'] = 'mysql://user:pwd@localhost/jigo';
?>
注意這里的資料庫格式必須是一樣的。即不能一個是mysql，另外一個是pgsql。
而且默認的必須是drupal自己的資料庫。
2、然後在程序中動態切換：
<?php
global $db_url; // 資料庫鏈接的內部變數
if (!is_array($db_url)) {
$default_db = $db_url;
$db_url = array('default' => $default_db);
}
//也可以動態設置新的資料庫，這里注釋掉，因為我們已經寫在配置項中
//$db_url['jigo'] = 'mysql://user:pwd@localhost/jigo';
db_set_active('jigo'); // activation & execution same as explained above
$results = db_query($sql); //sql represents the query to be executed
db_set_active('default'); // set back to original
?>
regards,
iefreer
Ⅲ pg資料庫只讀怎麼關掉
硬鎖定，直接將資料庫切換到恢復模式，絕對不會有寫操作出現、軟鎖定，設置default_transaction_read_only為on，默認開啟的事務為只讀事務。用戶如果使用begiontransactionreadwrite可破解。
Ⅳ postgresql從一個資料庫轉到另一個資料庫有沒有方便的方法
Postgresql不支持跨資料庫操作。
可以通過pg_mp加上pg_restore或psql來完成遷移，或者直接使用sql語句中的語句來完成數據的遷移。
Ⅳ pgsql如何用後面的一條記錄替換前面多條記錄
pgsql如何用後面的一條記錄替換前面多條記錄？
答案：postgreSQL如何將一條記錄的所有數據替換調另一條記錄的所有數據（除id欄位外）。
Ⅵ pgsql的主鍵存儲方式
PostgreSQL的穩定性極強，Innodb等索引在崩潰，斷電之類的災難場景下 抗擊打能力有了長足進步，然而很多 MqSQL用戶 都遇到過 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可以之hi語句級的鏡像或分片，slony可以進行欄位級的同步配置，standby 可以構建 WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便。
一般關系型資料庫字元串有長度限制 8k 左右，無限長 TEXT類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型 可以直接訪問且無長度限制， SQL語法內置 正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG的話，文檔資料庫都可以省了。
PgSQL對於 numa 架構的支持比 MySQL強一些，比 MySQL對於讀的性能更好一些， PgSQL提交可以完全非同步提交，而 MySQL的內存表不夠實用（因為表鎖的原因）。
pgsql除了存儲正常的數據類型外，還支持存儲
array，不管是一維數組還是多維數組均支持。
json和jsonb，相比使用 text存儲要高效很多。
json和 jsonb在更高的層面上看起來幾乎是一樣的，但是存儲實現上是不同的。
json存儲完的文本，json列會每次都解析存儲的值，它不支持索引，但 可以為創建表達式索引。
jsonb存儲的二進制格式，避免了重新解析數據結構。它支持索引，這意味著 可以不使用指定索引就能查詢任何路徑。
當我們比較寫入數據速度時，由於數據存儲 的方式的原因，jsonb會比 json 稍微的慢一點。json列會每次都 解析存儲的值，這意味著鍵的順序要和輸入的 時候一樣。但是 jsonb不同，以二進制格式存儲且不保證鍵的順序。因此如果有軟體需要依賴鍵的順序，jsonb可能不是最佳選擇。使用 jsonb的優勢還在於可以輕易的整合關系型數據和非關系型 數據 ，PostgreSQL對於 mongodb這類資料庫是一個不小的威脅，畢竟如果一個表中只有一列數據的類型是半結構化的，沒有必要為了遷就它而整個表的設計都採用 schemaless的結構。
1. CPU限制
PGSQL
沒有CPU核心數限制，有多少CPU核就用多少
MySQL
能用128核CPU，超過128核用不上
2. 配置文件參數
PGSQL
一共有255個參數，用到的大概是80個，參數比較穩定，用上個大版本配置文件也可以啟動當前大版本資料庫
MySQL
一共有707個參數，用到的大概是180個，參數不斷增加，就算小版本也會增加參數，大版本之間會有部分參數不兼容情況
3. 第三方工具依賴情況
PGSQL
只有高可用集群需要依靠第三方中間件，例如：patroni+etcd、repmgr
MySQL
大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，學習成本高，高可用集群也需要第三方中間件，官方MGR集群還沒成熟
4. 高可用主從復制底層原理
PGSQL
物理流復制，屬於物理復制，跟SQL Server鏡像/AlwaysOn一樣，嚴格一致，沒有任何可能導致不一致，性能和可靠性上，物理復制完勝邏輯復制，維護簡單
MySQL
主從復制，屬於邏輯復制，（sql_log_bin、binlog_format等參數設置不正確都會導致主從不一致）
大事務並行復制效率低，對於重要業務，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復主從一致
主從復制出錯嚴重時候需要重搭主從
MySQL的邏輯復制並不阻止兩個不一致的資料庫建立復制關系
5. 從庫只讀狀態
PGSQL
系統自動設置從庫默認只讀，不需要人工介入，維護簡單
MySQL
從庫需要手動設置參數super_read_only=on，讓從庫設置為只讀，super_read_only參數有bug，鏈接：https://jiahao..com/s?id=1636644783594388753&wfr=spider&for=pc
6. 版本分支
PGSQL
只有社區版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社區版有所有功能，不像SQL Server和MySQL有標准版、企業版、經典版、社區版、開發版、web版之分
國內外還有一些基於PGSQL做二次開發的資料庫廠商，例如：Enterprise DB、瀚高資料庫等等，當然這些只是二次開發並不算獨立分支
MySQL
由於歷史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發展到目前為止各個分支基本互相不兼容
Oracle官方分支還有版本之分，分為標准版、企業版、經典版、社區版
7. SQL特性支持
PGSQL
SQL特性支持情況支持94種，SQL語法支持最完善，例如：支持公用表表達式（WITH查詢）
MySQL
SQL特性支持情況支持36種，SQL語法支持比較弱，例如：不支持公用表表達式（WITH查詢）
關於SQL特性支持情況的對比，可以參考：http://www.sql-workbench.net/dbms_comparison.html
8. 主從復制安全性
PGSQL
同步流復制、強同步（remote apply)、高安全，不會丟數據
PGSQL同步流復制：所有從庫宕機，主庫會罷工，主庫無法自動切換為非同步流復制（非同步模式），需要通過增加從庫數量來解決，一般生產環境至少有兩個從庫
手動解決：在PG主庫修改參數synchronous_standby_names =''，並執行命令： pgctl reload ，把主庫切換為非同步模式
主從數據完全一致是高可用切換的第一前提，所以PGSQL選擇主庫罷工也是可以理解
MySQL
增強半同步復制 ，mysql5.7版本增強半同步才能保證主從復制時候不丟數據
mysql5.7半同步復制相關參數：
參數rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才提交事務，一般設置為1，性能最高
參數rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應自動切換為非同步模式，一般設置為無限大，不讓主庫自動切換為非同步模式
所有從庫宕機，主庫會罷工，因為無法收到任何從庫的應答包
手動解決：在MySQL主庫修改參數rpl_semi_sync_master_wait_for_slave_count=0
9. 多欄位統計信息
PGSQL
支持多欄位統計信息
MySQL
不支持多欄位統計信息
10. 索引類型
PGSQL
多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）
MySQL
btree 索引，全文索引（低效），表達式索引(需要建虛擬列)，hash 索引只在內存表
11. 物理表連接演算法
PGSQL
支持 nested-loop join 、hash join 、merge join
MySQL
只支持 nested-loop join
12. 子查詢和視圖性能
PGSQL
子查詢，視圖優化，性能比較高
MySQL
視圖謂詞條件下推限制多，子查詢上拉限制多
13. 執行計劃即時編譯
PGSQL
支持 JIT 執行計劃即時編譯，使用LLVM編譯器
MySQL
不支持執行計劃即時編譯
14. 並行查詢
PGSQL
並行查詢（多種並行查詢優化方法），並行查詢一般多見於商業資料庫，是重量級功能
MySQL
有限，只支持主鍵並行查詢
15. 物化視圖
PGSQL
支持物化視圖
MySQL
不支持物化視圖
16. 插件功能
PGSQL
支持插件功能，可以豐富PGSQL的功能，GIS地理插件，時序資料庫插件， 向量化執行插件等等
MySQL
不支持插件功能
17. check約束
PGSQL
支持check約束
MySQL
不支持check約束，可以寫check約束，但存儲引擎會忽略它的作用，因此check約束並不起作用（mariadb 支持）
18. gpu 加速SQL
PGSQL
可以使用gpu 加速SQL的執行速度
MySQL
不支持gpu 加速SQL 的執行速度
19. 數據類型
PGSQL
數據類型豐富，如 ltree，hstore，數組類型，ip類型，text類型，有了text類型不再需要varchar，text類型欄位最大存儲1GB
MySQL
數據類型不夠豐富
20. 跨庫查詢
PGSQL
不支持跨庫查詢，這個跟Oracle 12C以前一樣
MySQL
可以跨庫查詢
21. 備份還原
PGSQL
備份還原非常簡單，時點還原操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）
假如有一個三節點的PGSQL主從集群，可以隨便在其中一個節點做完整備份和wal歸檔備份
MySQL
備份還原相對不太簡單，完整備份+binlog備份（增量）
完整備份需要percona的XtraBackup工具做物理備份，MySQL本身不支持物理備份
時點還原操作步驟繁瑣復雜
22. 性能視圖
PGSQL
需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統統計信息等
不好的地方是，安裝插件需要重啟資料庫，並且需要收集性能信息的資料庫需要執行一個命令：create extension pg_stat_statements命令
否則不會收集任何性能信息，比較麻煩
MySQL
自帶PS庫，默認很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：內存佔用導致OOM bug）
23. 安裝方式
PGSQL
有各個平台的包rpm包，deb包等等，相比MySQL缺少了二進制包，一般用源碼編譯安裝，安裝時間會長一些，執行命令多一些
MySQL
有各個平台的包rpm包，deb包等等，源碼編譯安裝、二進制包安裝，一般用二進制包安裝，方便快捷
24. DDL操作
PGSQL
加欄位、可變長欄位類型長度改大不會鎖表，所有的DDL操作都不需要藉助第三方工具，並且跟商業資料庫一樣，DDL操作可以回滾，保證事務一致性
MySQL
由於大部分DDL操作都會鎖表，例如加欄位、可變長欄位類型長度改大，所以需要藉助percona-toolkit裡面的pt-online-schema-change工具去完成操作
將影響減少到最低，特別是對大表進行DDL操作
DDL操作不能回滾
25. 大版本發布速度
PGSQL
PGSQL每年一個大版本發布，大版本發布的第二年就可以上生產環境，版本迭代速度很快
PGSQL 9.6正式版推出時間：2016年
PGSQL 10 正式版推出時間：2017年
PGSQL 11 正式版推出時間：2018年
PGSQL 12 正式版推出時間：2019年
MySQL
MySQL的大版本發布一般是2年~3年，一般大版本發布後的第二年才可以上生產環境，避免有坑，版本發布速度比較慢
MySQL5.5正式版推出時間：2010年
MySQL5.6正式版推出時間：2013年
MySQL5.7正式版推出時間：2015年
MySQL8.0正式版推出時間：2018年
26. returning語法
PGSQL
支持returning語法，returning clause 支持 DML 返回 Resultset，減少一次 Client <-> DB Server 交互
MySQL
不支持returning語法
27. 內部架構
PGSQL
多進程架構，並發連接數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼很多優化方法也是相通的，例如：開啟大頁內存
MySQL
多線程架構，雖然多線程架構，但是官方有限制連接數，原因是系統的並發度是有限的，線程數太多，反而系統的處理能力下降，隨著連接數上升，反而性能下降
一般同時只能處理200 ~300個資料庫連接
28. 聚集索引
PGSQL
不支持聚集索引，PGSQL本身的MVCC的實現機制所導致
MySQL
支持聚集索引
29. 空閑事務終結功能
PGSQL
通過設置 idle_in_transaction_session_timeout 參數來終止空閑事務，比如：應用代碼中忘記關閉已開啟的事務，PGSQL會自動查殺這種類型的會話事務
MySQL
不支持終止空閑事務功能
30. 應付超大數據量
PGSQL
不能應付超大數據量，由於PGSQL本身的MVCC設計問題，需要垃圾回收，只能期待後面的大版本做優化
MySQL
不能應付超大數據量，MySQL自身架構的問題
31. 分布式演進
PGSQL
HTAP資料庫：cockroachDB、騰訊Tbase
分片集群： Postgres-XC、Postgres-XL
MySQL
HTAP資料庫：TiDB
分片集群： 各種各樣的中間件，不一一列舉
32. 資料庫的文件名和命名規律
PGSQL
PGSQL在這方面做的比較不好，DBA不能在操作系統層面（停庫狀態下）看清楚資料庫的文件名和命名規律，文件的數量，文件的大小
一旦操作系統發生文件丟失或硬碟損壞，非常不利於恢復，因為連名字都不知道
PGSQL表數據物理文件的命名/存放規律是： 在一個表空間下面，如果沒有建表空間默認在默認表空間也就是base文件夾下，例如：/data/base/16454/3599
base：默認表空間pg_default所在的物理文件夾
16454：表所在資料庫的oid
3599：就是表對象的oid，當然，一個表的大小超出1GB之後會再生成多個物理文件，還有表的fsm文件和vm文件，所以一個大表實際會有多個物理文件
由於PGSQL的數據文件布局內容太多，大家可以查閱相關資料
當然這也不能全怪PGSQL，作為一個DBA，時刻做好資料庫備份和容災才是正道，做介質恢復一般是萬不得已的情況下才會做
MySQL
資料庫名就是文件夾名，資料庫文件夾下就是表數據文件，但是要注意表名和資料庫名不能有特殊字元或使用中文名，每個表都有對應的frm文件和ibd文件，存儲元數據和表/索引數據，清晰明了，做介質恢復或者表空間傳輸都很方便
33. 許可權設計
PGSQL
PGSQL在許可權設計這塊是比較坑爹，拋開實例許可權和表空間許可權，PGSQL的許可權層次有點像SQL Server，db=》schema=》object
要說許可權，這里要說一下Oracle，用Oracle來類比
在ORACLE 12C之前，實例與資料庫是一對一，也就是說一個實例只能有一個資料庫，不像MySQL和SQL Server一個實例可以有多個資料庫，並且可以隨意跨庫查詢
而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個資料庫，跟ORACLE類比就是有多個實例（之前說的實例與資料庫是一對一）
一個資料庫相當於一個實例，因為PGSQL允許有多個實例，所以PGSQL單實例不叫一個實例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關資料
PGSQL裡面一個實例/資料庫下面的schema相當於資料庫，所以這個schema的概念對應MySQL的database
注意點：正因為是一個資料庫相當於一個實例，PGSQL允許有多個實例/資料庫，所以資料庫之間是互相邏輯隔離的，導致的問題是，不能一次對一個PGSQL集簇下面的所有資料庫做操作
必須要逐個逐個資料庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有資料庫都做性能收集的話，需要逐個資料庫去執行載入命令
又例如跨庫查詢需要dblink插件或fdw插件，兩個資料庫之間做查詢相當於兩個實例之間做查詢，已經跨越了實例了，所以需要dblink插件或fdw插件，所以道理非常簡單
許可權操作也是一樣逐個資料庫去操作，還有一個就是PGSQL雖然像SQL Server的許可權層次結構db=》schema=》object，但是實際會比SQL Server要復雜一些，還有就是新建的表還要另外授權
在PGSQL裡面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎麼去用角色，所以PGSQL在許可權設計這一塊確實比較坑爹
MySQL
使用mysql庫下面的5個許可權表去做許可權映射，簡單清晰，唯一問題是缺少許可權角色
user表
db表
host表
tables_priv表
columns_priv表
1. 架構對比
Mysql：多線程
PostgreSql：多進程
多線程架構和多進程架構之間沒有絕對的好壞，例如oracle在unix上是多進程架構，在windows上是多線程架構。
2. 對存儲過程及事務的支持能力
MySql對於無事務的MyISAM表，採用表鎖定，一個長時間運行的查詢很可能會長時間的阻礙，而PostgreSQL不會尊在這種問題。
PostgreSQL支持存儲過程，要比MySql好，具備本地緩存執行計劃的能力。
3. 穩定性及性能
高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySql 明顯出現一個波峰後下滑（5.5版本後Mysql企業版有優化，需要付費）
MySql的InnoDB引擎，可以充分優化利用系統的所有內存，超大內存下PG對內存使用的不那麼充分（需要根據內存情況合理分配）。
4. 高可用
InnoDB的基於回滾實現的 MVCC 機制，對於 PG 新老數據一起放的基於 XID 的 MVCC機制，是占優的。新老數據一起存放，需要定時觸發 VACUUM，會帶來多餘的 IO 和資料庫對象加鎖開銷，引起資料庫整理的並發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹
5. 數據同步方式：
Mysql到現在也是非同步復制，pgsql可以做到同步、非同步、半同步復制。
Mysql同步是基於binlog復制，屬於邏輯復制，類似於oracle golden gate，是基於stream的復制，做到同步很困難，這種方式更加適合非同步復制；
Pgsql的同是基於wal，屬於物理復制，可以做到同步復制。同時，pgsql還提供stream復制。
Mysql的復制可以用多級從庫，但是在9.2之前，PgSql不能用從庫帶從庫。
Pgsql的主從復制屬於物理復制，相對於Mysql基於binlog的邏輯復制，數據的一致性更加可靠，復制性能更高，對主機性能的影響也更小。
6. 許可權控制對比
MySql允許自定義一套不同的數據級、表級和列的許可權，運行指定基於主機的許可權
Mysql的merge表提供了 一個獨特管理多個表的方法。myisampack可以對只讀表進行壓縮，以後仍然可以直接訪問該表中的行。
7. SQL語句支持能力
PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，例如分析函數（Oracle的叫法，PG里叫window函數）
支持用多種語言來寫存儲過程，對於R的支持也很好。這一點上Mysql就差的很遠，很多分析功能都不支持。
PgSql對表名大小寫的處理，只有在Sql語句中，表明加雙引號，才區分大小寫。
在Sql的標准實現上要比Mysql完善，而且功能實現比較嚴謹。
對表連接支持比較完整，優化器的功能比較完整，支持的索引類型很多，復雜查詢能力較強。
Mysql採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對表結果設計存在約束；
Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦數據量大，性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL還支持正則表達式查詢，MySql不支持。
8. 數據類型支持能力
PostgreSQL可以更方便的使用UDF（用戶定義函數）進行擴展。
有豐富的幾何類型，實際上不止集合類型，PG有大量的字典、數組、bitmap等數據類型，因此PG多年來在 GIS 領域處於優勢地位。相比之下Mysql就差很多，instagram就是因為PG的空間數據擴展 PostGIS遠遠強於 MySql的 my spatial 而採用 PgSql的。Mysql中的空間數據類型有4種，分別是 CEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎為 MyiSam的表中創建，用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL。不同的存儲親情有差別。MyISAM和InnoDB 都支持 spatial extensions，但差別在於：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。
pgsql對json支持比較好，還有很逆天的fdw功能，就是把別的資料庫中的表當自己的用。
pgsql的欄位類型支持的多，有很多mysql沒有的類型，但是實際中有時候用到。
一半關系型資料庫的字元串長度8k左右，無限長的 TEXT 類型的功能受限，只能作為外部帶數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL 語法內置正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG 的話，文檔資料庫都可以省了。
postgresql 有函數，用於報表、統計很方便
PG支持 R-Trees這樣可擴展的索引類型，可以方便的處理一些特殊數據。
PG可以使用函數和條件所以，使得資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。
9. 如可過程容錯能力
大批量數據入庫，PostgreSql要求所有的數據必須完全滿足要求，有一條錯誤，整個數據入庫過程失敗。MySql無此問題。
10. 表組織方式
pgsql用繼承的方式實現分區表，讓分區表的使用不方便且性能差，這點比不上mysql。
pg主表採用堆表存放，MySQL採用索引組織表，能夠支持比MySql更大的數據量。
MySql分區表的實現要優於PG的基於繼承表的分區實現，主要體現在分區個數達到成千上萬後的處理性能差異很大。
11. 開發結構
對於web應用來所，mysql 5.6 的內置 MC API 功能很好用，PgSQL差一些。
PG的「無鎖定」特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和 PGSQL的 MVCC 實現有關系。
好文要頂 關注我 收藏該文
茄子777
粉絲 - 0 關注 - 0
+加關注
00
« 上一篇： 多線程中的wait與join
» 下一篇： 負載均衡相關
posted @ 2022-11-02 16:20 茄子777 閱讀(55) 評論(0) 編輯 收藏 舉報
刷新評論刷新頁面返回頂部
登錄後才能查看或發表評論，立即 登錄 或者 逛逛 博客園首頁
【推薦】阿里雲新人特惠，爆款雲伺服器2核4G低至0.46元/天
【推薦】雙十一同價！騰訊雲雲伺服器搶先購，低至4.2元/月
編輯推薦：
· 一個有趣的 nginx HTTP 400 響應問題分析
· 誰說.NET沒有GC調優？只改一行代碼就讓程序不再佔用內存
· 為什麼標准庫的模板變數都是 inline 的
· .net 如何優雅的使用 EFCore
· 在 C# 中使用 Halcon 開發視覺檢測程序
閱讀排行：
· Entity Framework Core 7中高效地進行批量數據插入
· 除了 filter 還有什麼置灰網站的方式？
· 快速繪制流程圖「GitHub 熱點速覽 v.22.47」
· 使用.NET7和C#11打造最快的序列化程序-以MemoryPack為例
· 私藏！資深數據專家SQL效率優化技巧 ⛵
Ⅶ 如何快速將postgresql 轉換為mysql
MySQL資料庫遷移到PostgreSQL 查了不少資料，也嘗試了一些，最後採用的辦法如下： 1. 導出mysql表定義（無數據） mysqlmp --no-data [dbname] >dbdef.sql 2. 使用mysql2postgres把腳本轉換為pgsql 3. 上面生成的腳本還不一定很完美
Ⅷ pgsql里邊怎樣把integer類型轉換為date類型
可以轉的，只要你清楚資料庫存儲時間的真正格式。
事實上是float的
一天分為24小時，而小數點前面的整數就是天數，把24小時作為1。也就是說
1.5的意思，實際上是1天12個小時。單位可能不一定對，大概就是這么個意思
以此類推，往後的小數點排列不用我說了吧？
如果不相信，可以試試這個select cast(getdate() as float)
Ⅸ 如何導入PostgreSQL資料庫數據
說起資料庫，大家耳熟能詳的商業資料庫產品當推Oracle、微軟的SqlServer和IBM的
DB2等，而開源資料庫中則有兩大產品MySQL和PostgreSQL。
PostgreSQL 是一種對象-關系型資料庫管理系統（ORDBMS），也是目前功能最強大、
特性最豐富和最復雜的自由軟體資料庫系統。它起源於伯克利（BSD）的資料庫研究計劃，
目前是最重要的開源資料庫產品開發項目之一，有著非常廣泛的用戶。
PostgreSQL 可以說是最富特色的自由資料庫管理系統，也有人認為可以是最強大的自由
軟體資料庫管理系統。PostgreSQL 是唯一支持事務、子查詢、多版本並行控制系統、數據完
整性檢查等特性的唯一的一種自由軟體的資料庫管理系統。能在多平台下---包括Linux、
FreeBSD和Windows等---運行，並且支持多語言的開發。
在兩大開源資料庫產品的對比中，一般認為MySQL速度更快，所以得到更為廣泛的使
用；而PostgreSQL性能更為先進，PostgreSQL 提供很多 MySQL 目前所不支持的特性，比
如觸發器、視圖、存儲過程等等，在記錄數超千萬之後性能表現尤其出色。
當前的最新版本是PostgreSQL 8.2.3，官方網站是：
www.postgresql.org
1.2 什麼是PostGIS
PostGIS在對象關系型資料庫PostgreSQL上增加了存儲管理空間數據的能力，相當於
Oracle的spatial部分。PostGIS最大的特點是符合並且實現了OpenGIS的一些規范，是最著
名的開源GIS資料庫。
當前的最新版本是PostGIS 1.2.1，官方網站是：
www.postgis.org
二 PostgreSQL和PostGIS的安裝
2.1 在windows下安裝。
新版本的PostgreSQL在其安裝程序中集成了PostGIS，只需要在安裝過程中選中
PostGIS和pgsql項就可以了。
2.2 在Debian下安裝PostGIS
# apt-get install postgresql-8.1 postgresql-8.1-postgis
當前Etch中的版本：PostgreSQL是8.1.7，而PostGIS是1.1.6，雖然不是最新的版本，
但已經足夠了。
還需要做的工作是：
1) 創建一個專門用於PostGIS的資料庫並為專用資料庫導入PostGIS支持：
# su – postgres
$ cd /usr/share/postgresql-8.1-postgis
$ createdb wen1
$ createlang plpgsql wen1
$ psql -d wen1 -f lwpostgis.sql
$ psql -d wen1 -f spatial_ref_sys.sql
2) 創建一個專門的用戶，並把相應的資料庫和數據表的屬主設置為該用戶：
# su – postgres
$ psql
$ create user wen1 password 『123456』;
$ alter database wen1 owner to wen1;
$ alter table spatial_ref_sys owner to wen1;
$ alter table geometry_columns owner to wen1 ;
3) 修改PostgreSQL配置文件以便可以遠程使用。
修改PostgreSQL的配置文件/etc/postgresql/8.1/main/Postgresql.conf，將其中的一句：
listen_address=』localhost』
前面的注釋去掉，並把』localhost』該為』*』。
修改Postgresql的配置文件/etc/postgresql/8.1/main/pg_hba.conf，在文件後面加一句：
host all all 192.168.0.0/24 password
這句的意思是：同網路中192.168.0.*的機器可以以密碼的形式使用所有的資料庫。更具
體的參數意義直接看該配置文件中的注釋就可以了。
這里一定要配置正確，否則無法在遠程連接PostgreSQL資料庫。
2.3 源碼安裝PostgreSQL和PostGIS
參閱以前寫的老文章。
三 PostGIS的使用
要使用PostGIS，需要兩方面的預備知識，一是要熟悉基本的SQL語法；二是熟悉
PostgreSQL資料庫的基本使用。
(一) 快速入門
我們使用前面創建的資料庫wen2，用戶為wen1，現在創建一個包含三個點的資料庫
test1：
首先在Etch下以wen1登陸，然後打開wen2資料庫：
$ psql -d wen2 ;
再在PostgreSQL的控制台下輸入以下命令：
wen2=> create table test1 (myID int4, pt geometry, myName
varchar );
wen2=> insert into test1 values (1, 'POINT(0 0)', 'beijing'
);
wen2=> insert into test1 values (2, 'POINT(31.5 60.87)',
'shanghai' );
wen2=> insert into test1 values (3, 'POINT(10.77 85.902)',
'tianjin' );
這樣我們利用PostGIS創建了一個包含三個點的GIS數據表。
為了能在QGIS中打開這一圖層，我們還需要為數據表創建一個主鍵：
wen2=>alter table test1 add primary key (myID);
可以看到PostGIS的使用其實就相當於使用一個經過擴展的SQL語法，上述語句熟悉
SQL語法的人一看都很熟悉，都是普通的SQL語句，不同的只不過是增加了PostGIS特殊的
geometry數據類型。
你可以再試試這些SQL語句：
select * from test1;
select myID,AsText(pt) from test1;
select Distance(pt, 'POINT(0 0)') from test1;
(二) PostGIS的Geometry數據類型
Geometry可以說是PostGIS最重要的一個概念，是「幾何體」的意思，由於PostGIS很
好地遵守OGC的」Simple Feature for Specification for
SQL」規范，目前支持的幾何體類型包
含其實例有：
POINT(1 1)
MULTIPOINT(1 1, 3 4, -1 3)
LINESTRING(1 1, 2 2, 3 4)
POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
MULTIPOLYGON((0 0, 0 1, 1 1, 1 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5
5))
MULTILINESTRING((1 1, 2 2, 3 4),(2 2, 3 3, 4 5))
而geometry具體表現又有兩種形式，一種叫做WKT(Well Known Text)形式，如上面的
例子。或者使用如下SQL語句瀏覽：
select AsText(pt) from test1;
另一種叫做「Canonical Form」形式，看上去是一串古怪的數字，其實是一種增強的十六
進制編碼，使用如下SQL語句就可以瀏覽了：
select pt from test1;
(三) 讀寫PostGIS數據
建設好PostGIS資料庫之後，我們現在需要進行讀寫GIS數據了，包括把GIS寫入到
PostGIS資料庫中以及在應用程序中使用PostGIS資料庫的數據。讀寫PostGIS目前主要有以
下四種方式：
3.1 使用psql語言
Psql語言是PostgreSQL內嵌的一個命令行工具，其語法基本上和標準的SQL語法是一
致的，可以使用Psql工具，結合標准SQL語法和一些PostGIS的擴展對PostGIS資料庫進行
讀寫操作。
具體例子如上面「快速入門」中的例子。
這種方式功能強大，但全部需要手工操作，煩瑣且繁重。
3.2 使用一些小工具
有兩個很有用的小的轉換工具，一是shp2pg；一是ogr2ogr
3.2.1 shp2pgsql和pgsql2shp
shp2pgsql和pgsql2shp是PostGIS自身攜帶的一對在Shape文件和PostGIS資料庫之間進
行轉換的工具，在Debian中安裝好PostGIS之後，這兩個程序已經位於可執行文件的搜索路
徑上，因此可以在系統中任何位置使用。
假如當前目錄下有完整的china.shp文件(應該有三個同名不同擴展名的文件)，需要把其
中數據輸入到上述的wen2資料庫中的數據表china_postgis中，具體操作為(操作用戶為
wen1)：
$ shp2pgsql china china_postgis > tmp.sql
$ psql -d wen2 -f roads.sql
這個工具很好用，缺點在於使用范圍有限，只針對Shape文件格式。
3.2.2 ogr2ogr
PostGIS本身的shp2pg工具只把shape文件轉換到PostGIS 資料庫中，那如何把其他的
GIS數據加入呢？比如說MapInfo的mid格式，ESRI的e00格式呢？這就要使用ogr這個工
具了。
Ogr目前是gdal的一個組成部分，那什麼是gdal呢？其官方主頁(http://www.gdal.org)上
如此介紹：
GDAL is a translator library for raster geospatial data formats
that is released under an X/MIT
style Open Source license by the Open Source Geospatial
Foundation. As a library, it presents a
single abstract data model to the calling application for all
supported formats. It also comes with a
variety of useful commandline utilties for data translation and
processing.
簡單地說，Gdal是一個各種Gis數據格式的轉換軟體庫，ogr則是轉換矢量GIS數據的
軟體庫。
目前ogr能夠支持的數據格式包括：
Arc/Info Binary Coverage、DWG、ESRI Personal
GeoDatabase、ArcSDE、ESRI
Shapefile、GML、GRASS、Mapinfo File、Microstation DGN、ODBC、Oracle
Spatial和
PostgreSQL等。應該說，這就基本包括了我們平常用到的所有矢量型GIS文件格式了。
Gdal支持的柵格數據格式參閱http: //www.gdal.org/formats_list.html
下面我們從源碼編譯安裝gdal---因為我在Debian下使用apt方式安裝的gdal並不支持
postgresql資料庫，其實更為簡便的方法也許是去gdal.org下載一個Fwtools的工具包，可以
直接運行不用繁瑣的編譯，既有Windows的，也有Linux下用的，只不過這個包有些大。
1) 下載最新的gdal-1.3.2
2) 解壓
3) ./configure --prefix=/opt/gdal
--with-pg=/opt/pg/bin/pg_config --without-ogdi
這里我習慣把軟體安裝在/opt目錄下。
--with-pg參數很重要，使ogr可以支持PostGIS，後面的參數是我的PostGIS安裝的地方。
我在這里編譯很多次失敗，經過仔細查找，發現問題出在ogdi上，所以暫時我只好使
它不支持ogdi。
4) make
5) make install
下面是使用過程，假設現在我要把一個rai.mid文件導入到資料庫data1中：
$ ogr2ogr –f Postgresql PG:dbname=data1 rai.mid
注意，f參數後面跟的是導入的數據類型，最後那個文件才是要導出的文件。PG後面說
明的是資料庫的名字，需要是已經創建好的資料庫，而數據表則由程序自動創建。還要特別
注意許可權問題，以上命令我是由postgres用戶執行的。
還有一個要注意的是主鍵問題。一般情況下，你使用shp2pg或者 ogr2ogr向postgis中
增加了Gis數據後，在GIS客戶端添加postgis圖層的時候，常常會因為數據表沒有定義主鍵
而出錯，解決的辦法是，在伺服器上使用psql或者其他sql工具，使用命令：
alter table table_name add primary key (column_name);
另外我們發現ogr竟然是不支持最常見的GIS格式e00格式，好象是因為版權方面的原
因吧。解決的方法就是去sf.net查找相關工具。我在上面就找到一個e002pg工具，支持將
e00文件導入到postGIS資料庫中。
3.3 在其他GIS軟體中讀寫PostGIS數據
比如在QGIS中，能夠打開PostGIS圖層，還有SPIT插件可以把Shape文件輸入到
PostGIS資料庫中。
其他GIS軟體如uDig，Grass等，甚至連ArcInfo都支持或部分支持讀寫PostGIS數據。
3.4 利用介面在應用程序中讀寫PostGIS數據
廣大的開源GIS程序員幾乎為每一種程序設計語言設計好了讀寫PostGIS的介面，如利
用PostgreSQL的JDBC庫，可以使用Java語言在程序中讀寫PostGIS數據；利用libpq庫，
可以使用C語言讀寫PostGIS數據。
(四) 使用PostGIS函數
4.1 PostGIS函數
要能熟練使用PostGIS，掌握PostGIS的所有函數是關鍵。通過掌握這些PostGIS函數，
我們能夠高效、穩定地處理各種地理數據。由於PostGIS的函數設計時都遵守OpenGIS規范，
我們也可以把這些PostGIS叫做OpenGIS函數。
4.2 PostGIS函數的分類
PostGIS函數大致可以分為以下四類
1) 欄位處理函數
這類函數當前一共有3個，分別是：
AddGeometryColumn(var1,var2,var3,var4,var5,var6)：為已有的數據表增加一個地理幾何
數據欄位。Var1代表數據表的模式(schema)的名字，一般是public，也可以省略，則使用當
前的預設模式；var2是已有的數據表的名字；var3是新的地理數據欄位的名字；var4是
SRID值，不確定的話就取-1吧；var5是地理數據的類型，可以是POINT等；var6是指該幾
何數據是二維還是三維數據。
前面的SQL語句 create table test1 (myID int4, pt geometry, myName
varchar )更規范的寫
法為：
create table test1 (myID int4, myName varchar );
select AddGeometryColumn('test1','pt',-1,'GEOMETRY',2);
DropGeometryColumn函數顯然是刪除一個地理數據欄位的；
SetSRID函數顯然是設置SRID值的。
2) 幾何關系函數
這類函數目前共有10個，分別是：
Distance Equals Disjoint Intersects Touches Crosses Within
Overlaps Contains Relate
3) 幾何分析函數
這類函數目前共有12個，分別是：Centroid Area Lenth PointOnSurface Boundary
Buffer
ConvexHull Intersection SymDifference Difference GeomUnion
MemGeomUnion
4) 讀寫函數
這類函數很多，主要是用於在各種數據類型之間的轉換，尤其是在於Geometry數據類
型與其他如字元型等數據類型之間的轉換，函數名如AsText、GeomFromText等，其作用是
顯然的。
4.3 PostGIS函數使用示例。
假設在我們的wen2資料庫中，有兩張表，一張為guojia，是從「國家基礎地理數據」
網站下載的國界數據表，數據類型為LINE；二為shengqu_polygon，也從同一個網站下載，
地理數據類型為多邊形。
1) 查詢我國邊境線的長度：
wen1=> select sum(length(the_geom)) as lenth from guojie;
2) 查詢我國面積最大的省區名字：
wen1=> select name area(the_geom) as myarea
from shengqu_polygon
order by myarea DESC
LIMIT 1;
(五) 使用PostGIS擴展函數
除了上述遵循OpenGIS的函數之外，PostGIS還自行擴展了一些當前OpenGIS規范之外
的函數，主要包括以下幾類：
5.1 管理類函數
擴展的管理類函數主要包括一些軟體版本查詢函數，如
postgis_version()、postgis_geos_version()、postgis_proj_version()函數等，分別查詢當前的
PostGIS的版本及其使用的Geos和Proj庫的版本。
5.2 數據類型的輸入輸出函數
除了OpenGIS定義的地理數據類型之外，PostGIS還對數據類型進行了擴展，這種擴展
主要是兩方面的擴展，一是把二維的數據向三維和四維擴展；二就是在WKT和WKB數據
類型基礎上擴展出EWKT和EWKB數據類型。
PostGIS提供了在這些地理數據類型和常用數據類型如字元型、浮點型數據之間進行轉
換的函數。
5.3 量算函數
如length3d函數是對length2d函數的擴展。
5.4 幾何操作函數
如addBBox(geometry)函數給所給的幾何體加上一個邊框。
如simplify(geometry,tolerance)函數可以對折線和多邊形利用Douglas-Peuker演算法進行一
些節點進行刪除，從而使表現的圖形更簡單而清晰，在網路傳輸數據時具有更高的效率。
5.5 操作符號
5.6 其他擴展函數
(六) 建立PostGIS索引
當資料庫的記錄增大的時候，如果沒有建立索引的話，操作的效率就顯著下降。
POstGIS建議當記錄數超過幾千的時候就應該建立索引，而GIS資料庫一般都是海量數據，
所以對PostGIS而言，索引就非常重要。
Shapfile文件為ESRI公司的文件存儲格式，並且得到了業界廣泛的支持。Shapfile格式是一種簡單的，用非拓樸關系的形式存儲幾何位置和地
理特徵的屬性信息的格式。雖然GeoServer採用Shapfile文件可以快速的創建網上地圖服務，但它的缺點確很明顯：
1、Shapefile只支持一個圖層，在實際中沒有意義。
2、直接保用SHP文件不安全，Shapfile文件很容易被病毒或其他原因誤刪除。
3、GeoServer中用Shapfile文件作數據源的效率是很低的。
4、Shapfile中的漢字GeoServer不能解析，會出現亂碼。
5、資料庫可以方便的對地理信息進行查詢。
用PostGIS管理空間數據
PostGIS支持GIST空間索引（附錄1）、規范窗體，能很大的提高處理效率。
OGC格式只提供二維的幾何體，且相關聯的SRID從未深入的用於輸入輸出請求，PostGIS支持OpenGIS組織"Simple
Features for
SQL"規范指定的所有GIS對象和函數，並進行了擴展，格式是EWKB、EWKT，其中增加了對3DZ，3DM和4D
坐標系的支持(當然三維、四維數據的OGC標准還未完全制定)，深入引進了SRID信息。
空間數據表結構：PostGIS中存在兩個必需的元數據表格， SPATIAL_REF_SYS（空間參考表格） 和
GEOMETRY_COLUMNS（幾何體屬性列），兩個表用於存儲空間資料庫使用的坐標系統數字ID和文本描述。
PostGIS的shp2pgsql命令可以將Shapfile直接導入到資料庫中也可以導出為SQL文件，推薦先導出為SQL文件再將此文件在SQL運行窗口中執行可將數據導入資料庫。
Shapfile到SQL語句：
shp2pgsql 路徑shp數據文件名 新建的數據表名 >
路徑SQL文件名.sql
Shapfile直接入庫：
shp2pgsql -c 路徑shp數據文件名 新建的數據表名 資料庫名｜psql -d 資料庫名
舉例說明：
如將一Shapfile文件「c:road.shp」導入到數據表「road」中，資料庫為「sjzmap」。
1、運行「命令提示符」。
2、切換至PostgreSQL資料庫安裝目錄中的bin目錄下。
3、執行此目錄下的shp2pgsql命令：「shp2pgsql c:road.shp road >
c:road.sql」。
4、如將此文件直接導入資料庫（不推薦）：「shp2pgsql -c c:road.shp road
sjzmap | psql -d sjzmap」。
5、使用pgAdmin3
選擇資料庫，再導入表。