PostGreSql 12.6 的備份和恢復

備份和恢復支援如下幾種方式：

1. SQL轉儲

2. 檔案系統級別備份

3. 連續歸檔和時間點恢復（PITR）

一、SQL轉儲

1.1 pg_dump

[postgres@localhost bin]$ ./pg_dump -d mydb -f /home/postgres/mydb.dump ##全庫備份 dbname 是 mydb

[postgres@localhost bin]$ ./pg_dump -d mydb -t weather -f /home/postgres/weather.dump ##表備份， mydb 資料庫下的表 weather

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \l

資料庫列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(3 行記錄)

## mydb不存在了

postgres=# \q

[postgres@localhost bin]$ ./createdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \l

資料庫列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(4 行記錄)

postgres=# \c mydb

您現在已經連線到資料庫 "mydb",使用者 "postgres".

mydb=# \d

沒有找到任何關係.

mydb=# \q

##重建DB，但是沒有資料

[postgres@localhost bin]$ ./psql -d mydb -f /home/postgres/mydb.dump

SET

set_config

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

(1 行記錄)

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE VIEW

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE VIEW

ALTER TABLE

COPY 0

COPY 1

COPY 0

COPY 3

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \c

您現在已經連線到資料庫 "postgres",使用者 "postgres".

postgres=# \c mydb

您現在已經連線到資料庫 "mydb",使用者 "postgres".

mydb=# \d

關聯列表

架構模式 | 名稱 | 型別 | 擁有者

----------+--------------+--------+----------

public | capitals | 資料表 | postgres

public | cities | 資料表 | postgres

public | cities01 | 檢視 | postgres

public | myview | 檢視 | postgres

public | non_capitals | 資料表 | postgres

public | weather | 資料表 | postgres

(6 行記錄)

###資料表恢復了

1.2 pg_dumpall

##全庫備份

[postgres@localhost bin]$ ./pg_dumpall > /home/postgres/pg_all.dump

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 543 2

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \l

資料庫列表

-----------+----------+----------+-------------+-------------+------------------

-----

+

gres

+

gres

(3 行記錄)

##mydb 和 mydb01 不存在了

postgres=# \q

[postgres@localhost bin]$ ./psql -f /home/postgres/pg_all.dump

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \l

資料庫列表

-----------+----------+----------+-------------+-------------+-----------------------

(5 行記錄)

postgres=# \c mydb01

您現在已經連線到資料庫 "mydb01",使用者 "postgres".

mydb01=# \d

關聯列表

架構模式 | 名稱 | 型別 | 擁有者

----------+--------------+--------+----------

public | capitals | 資料表 | postgres

public | cities | 資料表 | postgres

public | cities01 | 檢視 | postgres

public | myview | 檢視 | postgres

public | non_capitals | 資料表 | postgres

public | weather | 資料表 | postgres

(6 行記錄)

mydb01=# select * from weather;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 0.25 | 1994-11-27

San Francisco | 41 | 55 | 0 | 1994-11-29

Hayward | 35 | 52 | | 1994-11-29

(3 行記錄)

##恢復之後，表、資料都恢復了

1.3 壓縮和分割

主要是利用Linux的管線命令實現備份檔案的壓縮與分割。

[postgres@localhost bin]$ ./pg_dumpall | gzip > /home/postgres/pg_all.gz

[postgres@localhost bin]$ ls -al /home/postgres/

總用量 52

drwx------. 2 postgres postgres 4096 4月 13 07:18 .

drwxr-xr-x. 4 root root 39 4月 12 15:26 ..

-rw-------. 1 postgres postgres 1400 4月 12 17:43 .bash_history

-rw-r--r--. 1 postgres postgres 18 9月 7 2017 .bash_logout

-rw-r--r--. 1 postgres postgres 211 4月 12 17:43 .bash_profile

-rw-r--r--. 1 postgres postgres 231 9月 7 2017 .bashrc

-rw-rw-r--. 1 postgres postgres 2938 4月 13 06:05 mydb.dump

-rw-rw-r--. 1 postgres postgres 10854 4月 13 07:10 pg_all.dump

-rw-rw-r--. 1 postgres postgres 1328 4月 13 07:18 pg_all.gz

-rw-------. 1 postgres postgres 3537 4月 13 07:17 .psql_history

-rw-rw-r--. 1 postgres postgres 292 4月 12 16:04 t01.txt

-rw-rw-r--. 1 postgres postgres 1070 4月 13 06:06 weather.dump

[postgres@localhost bin]$ ./pg_dumpall | split -b 10k - /home/postgres/pg_all01.dump ##測試沒有資料， 10k一個檔案

[postgres@localhost bin]$ ls -al /home/postgres/

總用量 68

drwx------. 2 postgres postgres 4096 4月 13 07:32 .

drwxr-xr-x. 4 root root 39 4月 12 15:26 ..

-rw-------. 1 postgres postgres 1400 4月 12 17:43 .bash_history

-rw-r--r--. 1 postgres postgres 18 9月 7 2017 .bash_logout

-rw-r--r--. 1 postgres postgres 211 4月 12 17:43 .bash_profile

-rw-r--r--. 1 postgres postgres 231 9月 7 2017 .bashrc

-rw-rw-r--. 1 postgres postgres 2938 4月 13 06:05 mydb.dump

-rw-rw-r--. 1 postgres postgres 10240 4月 13 07:32 pg_all01.dumpaa

-rw-rw-r--. 1 postgres postgres 735 4月 13 07:32 pg_all01.dumpab

-rw-rw-r--. 1 postgres postgres 10854 4月 13 07:10 pg_all.dump

-rw-rw-r--. 1 postgres postgres 1328 4月 13 07:18 pg_all.gz

-rw-------. 1 postgres postgres 3537 4月 13 07:17 .psql_history

-rw-rw-r--. 1 postgres postgres 292 4月 12 16:04 t01.txt

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

[postgres@localhost bin]$ cat /home/postgres/pg_all01.dump* | psql

[postgres@localhost bin]$ ./dropdb mydb

[postgres@localhost bin]$ ./dropdb mydb01

[postgres@localhost bin]$ gunzip -c /home/postgres/pg_all.gz | psql

二、檔案系統級別備份

冷備了，停機之後複製對應的檔案，一般是有重大升級、遷移前會做一次，一般不會這樣操作。

三、連續歸檔和時間點恢復

熱備了，在系統工作的時候，開啟歸檔，進行資料的備份，需要恢復的時候，先回復基礎備份，之後再透過歸檔檔案做前滾操作，恢復資料到發生事故前的狀態。

3.1 啟用歸檔

[postgres@localhost ~]$ cat /var/lib/pgsql/12/data/postgresql.conf

archive_mode = on # enables archiving; off, on, or always

# (change requires restart)

archive_command = 'test ! -f /home/postgres/arc/%f && cp %p /home/postgres/arc/%f'

# command to use to archive a logfile segment

# placeholders: %p = path of file to archive

# %f = file name only

# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedi

[postgres@localhost bin]$ ./pg_ctl status -D /var/lib/pgsql/12/data

pg_ctl: 正在執行伺服器程式(PID: 2024)

/usr/pgsql-12/bin/postgres "-D" "/var/lib/pgsql/12/data"

[postgres@localhost bin]$ ./pg_ctl restart -D /var/lib/pgsql/12/data

等待伺服器程式關閉 .... 完成

伺服器程式已經關閉

等待伺服器程式啟動 ....2021-04-13 08:04:31.954 CST [4872] 日誌: 正在啟動 PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2021-04-13 08:04:31.954 CST [4872] 日誌: 正在監聽IPv4地址"0.0.0.0"，埠 5432

2021-04-13 08:04:31.954 CST [4872] 日誌: 正在監聽IPv6地址"::"，埠 5432

2021-04-13 08:04:31.956 CST [4872] 日誌: 在Unix套接字 "/var/run/postgresql/.s.PGSQL.5432"上偵聽

2021-04-13 08:04:31.960 CST [4872] 日誌: 在Unix套接字 "/tmp/.s.PGSQL.5432"上偵聽

2021-04-13 08:04:31.964 CST [4872] 日誌: 日誌輸出重定向到日誌收集程式

2021-04-13 08:04:31.964 CST [4872] 提示: 後續的日誌輸出將出現在目錄 "log"中.完成

伺服器程式已經啟動

[postgres@localhost bin]$ ps -ef|grep postgres:|grep -v grep

postgres 4873 4872 0 08:04 ? 00:00:00 postgres: logger

postgres 4875 4872 0 08:04 ? 00:00:00 postgres: checkpointer

postgres 4876 4872 0 08:04 ? 00:00:00 postgres: background writer

postgres 4877 4872 0 08:04 ? 00:00:00 postgres: walwriter

postgres 4878 4872 0 08:04 ? 00:00:00 postgres: autovacuum launcher

postgres 4879 4872 0 08:04 ? 00:00:00 postgres: archiver last was 000000010000000000000003

postgres 4880 4872 0 08:04 ? 00:00:00 postgres: stats collector

postgres 4881 4872 0 08:04 ? 00:00:00 postgres: logical replication launcher

postgres 4969 4872 0 08:08 ? 00:00:00 postgres: postgres postgres 192.168.56.10(40860) idle

[postgres@localhost bin]$ ./psql -U postgres -d postgres -h 192.168.56.10 -p 5432

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# select pg_switch_wal();

pg_switch_wal

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

0/18EC338

(1 行記錄)

## 如果在 /var/lib/pgsql/12/data/pg_wal 和 /home/postgres/arc/ 產生新的歸檔檔案，設定就成功了。

3.2 開始備份

[postgres@localhost 12]$ touch /var/lib/pgsql/backup_in_progress

[postgres@localhost 12]$ psql -c "select pg_start_backup('hot_backup');"

pg_start_backup

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

0/20000028

(1 行記錄)

[postgres@localhost 12]$ tar -Pcvf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/data/

[postgres@localhost 12]$ psql -c "select pg_stop_backup();"

注意: 所有需要的WAL段都已經歸檔完成

pg_stop_backup

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

0/20000138

(1 行記錄)

[postgres@localhost 12]$ rm /var/lib/pgsql/backup_in_progress

[postgres@localhost 12]$ tar -Prf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/archive/

[postgres@localhost 12]$ ls -al

總用量 124556

drwx------. 6 postgres postgres 4096 4月 13 19:13 .

drwx------. 3 postgres postgres 35 4月 13 19:13 ..

drwxrwxr-x. 2 postgres postgres 6 4月 13 10:54 archive

drwx------. 2 postgres postgres 6 2月 11 09:16 backups

-rw-rw-r--. 1 postgres postgres 127528960 4月 13 19:14 backup.tar.gz

可以做成一個指令碼：

##******

touch /var/lib/pgsql/backup_in_progress

psql -c "select pg_start_backup('hot_backup');"

cd /var/lib/pgsql/12/

tar -Pcvf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/data/

psql -c "select pg_stop_backup();"

rm /var/lib/pgsql/backup_in_progress

tar -Prf /var/lib/pgsql/12/backup.tar.gz /var/lib/pgsql/12/archive/

##******

備份後的操作

mydb=# alter table weather rename to weather09;

ALTER TABLE

mydb=# \d

關聯列表

架構模式 | 名稱 | 型別 | 擁有者

----------+-----------+--------+----------

public | weather01 | 資料表 | postgres

public | weather09 | 資料表 | postgres

(2 行記錄)

mydb=# \i /home/postgres/weather.dump

mydb=# \d

關聯列表

架構模式 | 名稱 | 型別 | 擁有者

----------+-----------+--------+----------

public | weather | 資料表 | postgres

public | weather01 | 資料表 | postgres

public | weather09 | 資料表 | postgres

(3 行記錄)

mydb=# select pg_switch_wal();

pg_switch_wal

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

0/21025C90

(1 行記錄)

3.3 恢復測試

[postgres@localhost ~]$ pg_ctl stop -D /var/lib/pgsql/12/data

等待伺服器程式關閉 .... 完成

伺服器程式已經關閉

[postgres@localhost 12]$ mv data data.new.bak

[postgres@localhost 12]$ tar -Pxvf backup.tar.gz -C /

[postgres@localhost 12]$ ls -al

總用量 124560

drwx------. 7 postgres postgres 4096 4月 13 19:39 .

drwx------. 3 postgres postgres 35 4月 13 19:13 ..

drwxrwxr-x. 2 postgres postgres 6 4月 13 10:54 archive

drwx------. 2 postgres postgres 6 2月 11 09:16 backups

-rw-rw-r--. 1 postgres postgres 127528960 4月 13 19:14 backup.tar.gz

drwx------. 20 postgres postgres 4096 4月 13 19:13 data

drwx------. 20 postgres postgres 4096 4月 13 12:16 data01.bak

drwx------. 20 postgres postgres 4096 4月 13 19:38 data.new.bak

-rw-------. 1 postgres postgres 997 4月 12 15:32 initdb.log

修改恢復引數：

[postgres@localhost log]$ cat /var/lib/pgsql/12/data/postgresql.conf

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

# command to use to restore an archived logfile segment

# placeholders: %p = path of file to restore

# %f = file name only

# e.g. 'cp /mnt/server/archivedir/%f %p'

# (change requires restart)

recovery_target_time = '2021-04-13 19:43:16' ##操作的當前時間

[postgres@localhost data]$ touch /var/lib/pgsql/12/data/recovery.signal ##pg12之後使用這個標識檔案了，空的檔案就可以

[postgres@localhost data]$ pg_ctl start -D /var/lib/pgsql/12/data

等待伺服器程式啟動 ....2021-04-13 19:47:53.898 CST [9681] 日誌: 正在啟動 PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2021-04-13 19:47:53.898 CST [9681] 日誌: 正在監聽IPv4地址"0.0.0.0"，埠 5432

2021-04-13 19:47:53.898 CST [9681] 日誌: 正在監聽IPv6地址"::"，埠 5432

2021-04-13 19:47:53.900 CST [9681] 日誌: 在Unix套接字 "/var/run/postgresql/.s.PGSQL.5432"上偵聽

2021-04-13 19:47:53.904 CST [9681] 日誌: 在Unix套接字 "/tmp/.s.PGSQL.5432"上偵聽

2021-04-13 19:47:53.908 CST [9681] 日誌: 日誌輸出重定向到日誌收集程式

2021-04-13 19:47:53.908 CST [9681] 提示: 後續的日誌輸出將出現在目錄 "log"中.. 完成

伺服器程式已經啟動

[postgres@localhost log]$ psql -U postgres -d postgres -h 192.168.56.10 -p 5432

使用者 postgres 的口令：

psql (12.6)

輸入 "help" 來獲取幫助資訊.

postgres=# \c

您現在已經連線到資料庫 "postgres",使用者 "postgres".

postgres=# \c mydb

您現在已經連線到資料庫 "mydb",使用者 "postgres".

mydb=# \d

關聯列表

架構模式 | 名稱 | 型別 | 擁有者

----------+-----------+--------+----------

public | weather | 資料表 | postgres

public | weather01 | 資料表 | postgres

public | weather09 | 資料表 | postgres

(3 行記錄)

mydb=# \d weather09

資料表 "public.weather09"

欄位 | 型別 | 校對規則 | 可空的 | 預設

---------+-----------------------+----------+--------+------

city | character varying(80) | | |

temp_lo | integer | | |

temp_hi | integer | | |

prcp | real | | |

date | date | | |

mydb=# insert into weather09 values('Dalian',50,60,0.75,'1994-11-27');

INSERT 0 1

mydb=# select * from weather09;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 0.25 | 1994-11-27

San Francisco | 41 | 55 | 0 | 1994-11-29

Hayward | 35 | 52 | | 1994-11-29

Dalian | 50 | 60 | 0.75 | 1994-11-27

(4 行記錄)

##備份之後的操作結果也存在恢復的資料裡面了。資料表也可以insert shuju。

基本的一個操作記錄，備查吧。。。。。。

PostGreSql12.6的備份恢復

相關文章