postgresql如何備份數據庫

postgresql中可以使用pg_dump來備份數據庫。pg_dump是用于備份PostgreSQL數據庫的工具。它可以在數據庫正在使用的時候進行完整一致的備份，并不阻塞其它用戶對數據庫的訪問。

成都創新互聯公司是一家專業提供托克托企業網站建設,專注與網站建設、成都網站制作、H5高端網站建設、小程序制作等業務。10年已為托克托眾多企業、政府機構等服務。創新互聯專業的建站公司優惠進行中。

用法：

pg_dump [選項]... [數據庫名字]

一般選項:

-f, --file=FILENAME output file or directory name

-F, --format=c|d|t|p output file format (custom, directory, tar, plain text)

-v, --verbose 詳細模式

-Z, --compress=0-9 被壓縮格式的壓縮級別

--lock-wait-timeout=TIMEOUT 在等待表鎖超時后操作失敗

--help 顯示此幫助信息, 然后退出

--versoin 輸出版本信息, 然后退出

示例：

備份數據庫，指令如下：

pg_dump -h 164.82.233.54 -U postgres databasename C:databasename.bak

開始-運行-cmd 彈出dos控制臺；然后 在控制臺里，進入PostgreSQL安裝目錄bin下：

cd C:Program FilesPostgreSQL9.0bin

最后執行備份指令：

pg_dump -h 164.82.233.54 -U postgres databasename C:databasename.bak

指令解釋：

pg_dump 是備份數據庫指令，164.82.233.54是數據庫的ip地址（必須保證數據庫允許外部訪問的權限哦~），當然本地的數據庫ip寫 localhost；

postgres 是數據庫的用戶名；databasename 是數據庫名。

意思是導出到C:databasename.bak文件里，如果沒有寫路徑，單單寫databasename.bak文件名，那么備份文件會保存在C: Program FilesPostgreSQL9.0bin 文件夾里。

推薦學習《Python教程》。

如何壓縮Mysql數據庫

壓縮表從名字上來看，簡單理解為壓縮后的表，也就是把原始表根據一定的壓縮算法按照一定的壓縮比率壓縮后生成的表。

1.1 壓縮能力強的產品

表壓縮后從磁盤占用上看要比原始表要小很多。如果你熟悉列式數據庫，那對這個概念一定不陌生。比如，基于 PostgreSQL 的列式數據庫 Greenplum；早期基于 MySQL 的列式數據庫 inforbright；或者 Percona 的產品 tokudb 等，都是有壓縮能力非常強的數據庫產品。

1.2 為什么要用壓縮表？

情景一：磁盤大小為 1T，不算其他的空間占用，只能存放 10 張 100G 大小的表。如果這些表以一定的比率壓縮后，比如每張表從 100G 壓縮到 10G，那同樣的磁盤可以存放 100 張表，表的容量是原來的 10 倍。情景二：默認 MySQL 頁大小 16K，而 OS 文件系統一般塊大小為 4K，所以在 MySQL 在刷臟頁的過程中，有一定的概率出現頁沒寫全而導致數據壞掉的情形。比如 16K 的頁寫了 12K，剩下 4K 沒寫成功，導致 MySQL 頁數據損壞。這個時候就算通過 Redo Log 也恢復不了，因為幾乎有所有的關系數據庫采用的 Redo Log 都記錄了數據頁的偏移量，此時就算通過 Redo Log 恢復后，數據也是錯誤的。所以 MySQL 在刷臟數據之前，會把這部分數據先寫入共享表空間里的 DOUBLE WRITE BUFFER 區域來避免這種異常。此時如果 MySQL 采用壓縮表，并且每張表頁大小和磁盤塊大小一致，比如也是 4K，那 DOUBLE WRITE BUFFER 就可以不需要，這部分開銷就可以規避掉了。查看文件系統的塊大小：

root@ytt-pc:/home/ytt#??tune2fs?-l?/dev/mapper/ytt--pc--vg-root??|?grep?-i?'block?size'Block size: ? ? ? ? ? ? ? 4096

1.3 壓縮表的優勢

壓縮表的優點非常明顯，占用磁盤空間小！由于占用空間小，從磁盤置換到內存以及之后經過網絡傳輸都非常節省資源。

簡單來講：節省磁盤 IO，減少網絡 IO。

1.4 壓縮表的缺陷

當然壓縮表也有缺點，壓縮表的寫入（INSERT,UPDATE,DELETE）比普通表要消耗更多的 CPU 資源。

壓縮表的寫入涉及到解壓數據，更新數據，再壓縮數據，比普通表多了解壓和再壓縮兩個步驟，壓縮和解壓縮需要消耗一定的 CPU 資源。所以需要選擇一個比較優化的壓縮算法。

1.5 MySQL 支持的壓縮算法

這塊是 MySQL 所有涉及到壓縮的基礎，不僅僅用于壓縮表，也用于其它地方。比如客戶端請求到 MySQL 服務端的數據壓縮；主從之間的壓縮傳輸；利用克隆插件來復制數據庫操作的壓縮傳輸等等。

從下面結果可以看到 MySQL 支持的壓縮算法為 zlib 和 zstd，MySQL 默認壓縮算法為 zlib，當然你也可以選擇非 zlib 算法，比如 zstd。至于哪種壓縮算法最優，暫時沒辦法簡單量化，依賴表中的數據分布或者業務請求。

如何使用 PostgreSQL 數據庫去 O

1、安裝postgresql

yum install postgresql postgresql-server

mysql占用端口3306 pgsql是5432

2、導入整個數據庫

psql -U postgres(用戶名) 數據庫名(缺省時同用戶名) /data/dum.sql

3、導出整個數據庫

pg_dump -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名) /data/dum.sql

4、導出某個表

pg_dump -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名) -t table(表名) /data/dum.sql

5、壓縮方法

一般用dump導出數據會比較大，推薦使用xz壓縮

壓縮方法 xz dum.sql 會生成 dum.sql.xz 的文件

6、xz壓縮數據倒數數據庫方法

xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名)

postgresql免安裝版本怎么配置圖解

免安裝版下載鏈接：

64位：

32位：

使用方法：

1. 解壓縮至某個目錄，例如：G:/pgsql

2. 創建一個用于存放數據文件的目錄，例如：G:/pgsql/data

3. 初始化數據庫：G:/pgsql/bin/initdb.exe -D G:/pgsql/data

4. 啟動數據庫：G:/pgsql/bin/pg_ctl.exe start -D G:/pgsql/data -l g:/aaa.log

（其中aaa.log為數據庫日志文件名稱）

5. 登錄維護數據庫：G:/pgsql/bin/psql.exe postgres

6. 創建登錄用戶：create user myuser;

然后就可以用pgadmin（大象）進行登陸了，初次登陸時可以使用任意密碼，登陸之后可以再創建別的用戶，或者修改密碼。

用上述方法的話，重啟操作系統后需要重新執行步驟4。可以做成一個批處理。

附上：使PG注冊為系統服務的方法，我沒有試驗成功（報錯誤碼1063，感覺是權限方面的問題）

如何擴大postgresql存儲空間

1. 概述

cstore_fdw實現了 PostgreSQL 數據庫的列式存儲。列存儲非常適合用于數據分析的場景，數據分析的場景下數據是批量加載的。

這個擴展使用了Optimized Row Columnar (ORC)數據存儲格式，ORC改進了Facebook的RCFile格式，帶來如下好處:

壓縮:將內存和磁盤中數據大小削減到2到4倍。可以擴展以支持不同壓縮算法。

列投影:只提取和查詢相關的列數據。提升IO敏感查詢的性能。

跳過索引:為行組存儲最大最小統計值，并利用它們跳過無關的行。

2. 使用

cstore_fdw的安裝和使用都非常簡單，可以參考官方資料。

thub.com/citusdata/cstore_fdw

注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做幾個簡單的性能對比，看看cstore_fdw究竟能帶來多大的性能提升。

2.1 數據加載

2.1.1 普通表

CREATE TABLE tb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

);

注:要和普通表的全表掃描作對比，所以不建主鍵和索引。

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"

COPY 10000000

1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

776inputs+0outputs (17major+918minor)pagefaults 0swaps

real 6m42.402s

user 0m15.174s

sys 0m14.904s

postgres=# select pg_total_relation_size('tb1'::regclass);

pg_total_relation_size

------------------------

1161093120

(1 row)

postgres=# \timing

Timing is on.

postgres=# analyze tb1;

ANALYZE

Time: 11985.070 ms

插入1千萬條記錄，數據占用存儲大小1.16G，插入耗時6分42秒，分析耗時12秒。

2.1.2 cstore表

$ mkdir -p /home/chenhj/data94/cstore

CREATE EXTENSION cstore_fdw;

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

CREATE FOREIGN TABLE cstb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

)

SERVER cstore_server

OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',

compression 'pglz');

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"

COPY 10000000

1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

968inputs+0outputs (20major+920minor)pagefaults 0swaps

real 7m35.520s

user 0m14.809s

sys 0m14.170s

[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore

-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore

postgres=# \timing

Timing is on.

postgres=# analyze cstb1;

ANALYZE

Time: 5946.476 ms

插入1千萬條記錄，數據占用存儲大小390M，插入耗時7分35秒，分析耗時6秒。

使用cstore列存儲后，數據占用存儲大小降到普通表的3分之1。需要說明的是，由于所有TEXT列填充了隨機數據，壓縮率不算高，某些實際的應用場景下壓縮效果會比這更好。

2.2 Text列的like查詢性能對比

2.2.1 普通表

清除文件系統緩存，并重啟PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 771356 1284152 0 9900 452256

-/+ buffers/cache: 309200 1746308

Swap: 4128760 387624 3741136

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 326788 1728720 0 228 17636

-/+ buffers/cache: 308924 1746584

Swap: 4128760 381912 3746848

對Text列執行like查詢

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.42 0.00 95.40

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.55 330.68 212.08 7351441 4714848

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m7.051s

user 0m0.001s

sys 0m0.004s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.90 381.53 211.90 8489597 4714956

耗時7.1秒，產生IO讀1.14G,IO寫108K。

不清文件系統緩存，不重啟PostgreSQL，再執行一次。消耗時間降到1.6秒，幾乎不產生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.81 332.20 213.06 7350301 4714364

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.601s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.38

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.80 332.12 213.01 7350337 4714364

2.2.2 cstore表

清除文件系統緩存，并重啟PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

對Text列執行like查詢

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.45

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 376.42 209.04 8492017 4716048

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m2.786s

user 0m0.002s

sys 0m0.003s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.44

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 378.75 208.89 8550761 4716048

耗時2.8秒，產生IO讀59M,IO寫0K。執行時間優化的雖然不是太多，但IO大大減少，可見列投影起到了作用。

不清文件系統緩存，不重啟PostgreSQL，再執行一次。消耗時間降到1.4秒，幾乎不產生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.75 376.33 207.58 8550809 4716524

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.424s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.70 375.96 207.38 8550809 4716588

2.3 對Int列執行=查詢

2.3.1 普通表

清除文件系統緩存，并重啟PostgreSQL后

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

對Int列執行=查詢

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.25 373.21 205.67 8560897 4717624

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

1

(1 row)

real 0m6.844s

user 0m0.002s

sys 0m0.006s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.34 0.00 95.49

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.60 422.57 205.54 9699161 4717708

耗時6.8秒，產生IO讀1.14G,IO寫84K

不清緩存，再執行一次。消耗時間降到1.1秒，幾乎不產生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.44 421.37 204.97 9699177 4718032

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

當前名稱：postgresql壓縮的簡單介紹
本文路徑：http://vcdvsql.cn/article12/dsdgogc.html

成都網站建設公司_創新互聯，為您提供網站內鏈、軟件開發、服務器托管、微信公眾號、動態網站、云服務器

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯