一口锭、Copy

COPY在 PostgreSQL表和標(biāo)準(zhǔn)文件系統(tǒng)文件之間 移動(dòng)數(shù)據(jù)尤泽。COPY TO把一個(gè)表的內(nèi)容復(fù)制 到一個(gè)文件，而COPY FROM 則從一個(gè)文件復(fù)制數(shù)據(jù)到一個(gè)表（把數(shù)據(jù)追加到表中原有數(shù)據(jù)）。COPY TO也能復(fù)制一個(gè) SELECT查詢的結(jié)果产园。 支持text、 csv（逗號(hào)分隔值）或者binary夜郁。 默認(rèn)是text什燕。

（一）語法：

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }

? ? TO { 'filename' | PROGRAM 'command' | STDOUT }

[ [ WITH ] ( option [, ...] ) ]

COPY table_name [ ( column_name [, ...] ) ]

? ? FROM { 'filename' | PROGRAM 'command' | STDIN }

[ [ WITH ] ( option [, ...] ) ]

（二）常用參數(shù)

table_name，column_name竞端，query屎即，filename

（三）示例：

導(dǎo)出表

COPY user TO '/tmp/data/test.csv' WITH csv;

導(dǎo)出字段

COPY user(name,password) TO '/tmp/data/test.csv' WITH csv;

COPY (select name,age from user) TO '/tmp/data/test.csv' WITH csv header;

導(dǎo)入表

COPY user from '/tmp/data/test.csv' ;

（四）注意事項(xiàng)：

COPY TO只能被用于純粹的表，不能用于視圖事富。 不過你可以寫COPY (SELECT * FROM viewname) TO ... 拷貝一個(gè)視圖的當(dāng)前內(nèi)容技俐。

COPY FROM可以被用于純粹的表和具有 INSTEAD OF INSERT觸發(fā)器的視圖。 同時(shí)調(diào)用目標(biāo)表上的任何觸發(fā)器 和檢查約束统台。

COPY命令的用戶必須是 PostgreSQL用戶（運(yùn)行服務(wù)器的用戶 ID）可訪問的并且是可讀或者可寫的雕擂。 只允許數(shù)據(jù)庫超級(jí)用戶COPY一個(gè)文件或者命令， 因?yàn)樗试S讀取或者寫入服務(wù)器有特權(quán)訪問的任何文件饺谬。

COPY默認(rèn)利用tab作為列的界限捂刺，空格作為字符

二、Pg_dump

pg_dupg_dump — 把PostgreSQL數(shù)據(jù)庫抽取為一個(gè)腳本文件或其他歸檔文件

（一）語法

Pg_dump [connection-option...] [option...] [dbname]

樣例：pg_dump dbname > outfile

psql dbname < infile

pg_restore -d newdb db.dump

（二）常用參數(shù)：

-a? --data-only

-b? --blobs

-B? --no-blobs

-c? --clean

d? directory

-j njobs? --jobs=njobs

-n schema? --schema=schema

-t table? --table=table

參數(shù)命令中大小寫會(huì)造成語義相反募寨。

（三）示例：

要把一個(gè)數(shù)據(jù)庫mydb轉(zhuǎn)儲(chǔ)到一個(gè) SQL 腳本文件：

$ pg_dump mydb > db.sql

要用 5 個(gè)并行的工作者任務(wù)轉(zhuǎn)儲(chǔ)一個(gè)數(shù)據(jù)庫到一個(gè)目錄格式的歸檔：

$ pg_dump -Fd mydb -j 5 -f dumpdir

要把一個(gè)歸檔文件重新載入到一個(gè)（新創(chuàng)建的）名為newdb的數(shù)據(jù)庫：

$ pg_restore -d newdb db.dump

要轉(zhuǎn)儲(chǔ)detroit模式中名稱以emp開始的所有表族展，排除名為employee_log的表：

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

（四）注意事項(xiàng)：

?為maintenance_work_mem和max_wal_size設(shè)置適當(dāng)?shù)模幢日Ｖ荡蟮模┲怠?/p>

?如果使用 WAL 歸檔或流復(fù)制，在轉(zhuǎn)儲(chǔ)時(shí)考慮禁用它們拔鹰。在載入轉(zhuǎn)儲(chǔ)之前仪缸，可通過將archive_mode設(shè)置為off、將wal_level設(shè)置為minimal以及將max_wal_senders設(shè)置為零（在錄入dump前）來實(shí)現(xiàn)禁用列肢。 之后恰画，將它們?cè)O(shè)回正確的值并執(zhí)行一次新的基礎(chǔ)備份宾茂。

?如果在數(shù)據(jù)庫服務(wù)器上有多個(gè) CPU 可用，可以考慮使用pg_restore的--jobs選項(xiàng)拴还。這允許并行數(shù)據(jù)載入和索引創(chuàng)建跨晴。?

?恢復(fù)后執(zhí)行統(tǒng)計(jì)信息收集

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]描述

ANALYZE收集一個(gè)數(shù)據(jù)庫中的表的內(nèi)容的統(tǒng)計(jì)信息，并且將結(jié)果存儲(chǔ)在pg_statistic系統(tǒng)目錄中片林。

三端盆、PG_DUMPALL

pg_dumpall備份一個(gè)給定集簇中的每一個(gè)數(shù)據(jù)庫，并且也保留了集簇范圍的數(shù)據(jù)费封，如角色和表空間定義焕妙。

（一）語法

pg_dumpall > outfile

轉(zhuǎn)儲(chǔ)的結(jié)果可以使用psql恢復(fù)：

psql -f infile postgres

（二）注意事項(xiàng)

?PG_DUMPALL的過程中，每個(gè)數(shù)據(jù)庫自身是一致的弓摘，但是不同數(shù)據(jù)庫的快照并不同步焚鹊。

?建議在每個(gè)數(shù)據(jù)庫上運(yùn)行ANALYZE，這樣優(yōu)化器就可以得到有用的統(tǒng)計(jì)信息韧献。你也可以運(yùn)行vacuumdb -a -z來分析所有數(shù)據(jù)庫末患。

四、PITR

在任何時(shí)間势决，PostgreSQL在數(shù)據(jù)集簇目錄的pg_wal/子目錄下都保持有一個(gè)預(yù)寫式日志（WAL）阻塑。這個(gè)日志存在的目的是為了保證崩潰后的安全：如果系統(tǒng)崩潰蓝撇，可以“重放”從最后一次檢查點(diǎn)以來的日志項(xiàng)來恢復(fù)數(shù)據(jù)庫的一致性果复。我們可以把一個(gè)文件系統(tǒng)級(jí)別的備份和WAL文件的備份結(jié)合起來。當(dāng)需要恢復(fù)時(shí)渤昌，我們先恢復(fù)文件系統(tǒng)備份虽抄，然后從備份的WAL文件中重放來把系統(tǒng)帶到一個(gè)當(dāng)前狀態(tài)。

（一）修改配置文件 postgresql.conf

archive_mode = on

archive_command = 'cp -i %p /home/sure/mywork/archive/%f'

wal_level = replica

重啟數(shù)據(jù)庫

./pg_ctl -l logfile start

（二）對(duì)數(shù)據(jù)庫進(jìn)行物理備份

select pg_start_backup('stm');

打包數(shù)據(jù)庫

tar -cvzf data.tar data

結(jié)束備份

select pg_stop_backup();

這時(shí)會(huì)再備份出的data下產(chǎn)生一個(gè)backup_label的文件独柑，記錄了可以查看內(nèi)容有checkpoint時(shí)間,基礎(chǔ)備份的開始和結(jié)束時(shí)間迈窟，以及標(biāo)簽名稱等

這里也可以用pg_basebackup工具備份

pg_basebackup? -F t -R -D /home/postgres/bak

（三）更新數(shù)據(jù)庫

copy? weather from '/home/postgres/test.txt';

切換歸檔產(chǎn)生新歸檔文件

PostgreSQL手動(dòng)切換WAL日志的命令：?

在PG10之前：

highgo=# select pg_switch_xlog(); pg_switch_xlog

在PG10之后：

highgo=# select pg_switch_wal(); pg_switch_wal

（四）模擬數(shù)據(jù)庫毀壞并恢復(fù)

模擬毀壞? cp -r data data1

解壓備份：$ tar xvf data.tar

清理pg_wal? ? rm -rf? ./*

修改 postgresql.conf? 將之前的配置去掉

配置recovery.conf

restore_command = 'cp /home/postgres/archive/%f %p'

archive_cleanup_command='pg_archivecleanup /home/sure/mywork/archivedir %r'

recovery_target_time='2018-07-21 14:35:12'

重啟數(shù)據(jù)庫

Pg_ctl start

結(jié)束后,recovery.conf會(huì)改名變成recovery.done。

驗(yàn)證數(shù)據(jù)

Select? * from weather;

五忌栅、總結(jié)：

COPY適合單表或者一個(gè)查詢的結(jié)果车酣，小范圍的移動(dòng)

PG_DUMP適用于數(shù)據(jù)實(shí)時(shí)性要求低，單個(gè)數(shù)據(jù)庫的備份恢復(fù)

PG_DUMPALL適用于數(shù)據(jù)實(shí)時(shí)性要求低索绪，整個(gè)數(shù)據(jù)庫集簇的備份與恢復(fù)

PITR? 適用于數(shù)據(jù)量較大湖员，實(shí)時(shí)性較高的備份和恢復(fù)