建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
本篇內(nèi)容介紹了“postgreSQL11備份與恢復(fù)方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
在網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站制作過(guò)程中，需要針對(duì)客戶(hù)的行業(yè)特點(diǎn)、產(chǎn)品特性、目標(biāo)受眾和市場(chǎng)情況進(jìn)行定位分析，以確定網(wǎng)站的風(fēng)格、色彩、版式、交互等方面的設(shè)計(jì)方向。創(chuàng)新互聯(lián)建站還需要根據(jù)客戶(hù)的需求進(jìn)行功能模塊的開(kāi)發(fā)和設(shè)計(jì)，包括內(nèi)容管理、前臺(tái)展示、用戶(hù)權(quán)限管理、數(shù)據(jù)統(tǒng)計(jì)和安全保護(hù)等功能。
1、歸檔目錄：
[postgres@centos1 arch]$ pwd /home/postgres/arch
2、設(shè)置歸檔命令：
archive_command -------------------------------------------------------------------------------------------------------- DATE=`date +%Y%m%d`; DIR="/home/postgres/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f
修改wal_level和archive_mode參數(shù)都需要重新啟動(dòng)數(shù)據(jù)庫(kù)才可以生效，修改archive_command不需要重啟，只需要reload即可：
postgres=# SELECT pg_reload_conf();
3、驗(yàn)證歸檔：
postgres=# checkpoint postgres-# ; CHECKPOINT postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/11029F08(1 row) [postgres@centos1 20200103]$ ll total 16M-rw------- 1 postgres postgres 16M Jan 3 10:45 000000010000000000000011
4、配置備份用戶(hù)訪(fǎng)問(wèn)：
[postgres@centos1 pg_root]$ vi pg_hba.conf host replication rep 0.0.0.0/0 md5
5、創(chuàng)建基礎(chǔ)備份：
[postgres@centos1 pgbak]$ pg_basebackup -Ft -D /home/postgres/pgbak`date +%F` -h 192.168.1.212 -p 1921 -U rep Password: [postgres@centos1 pgbak2020-01-03]$ ll total 96M -rw------- 1 postgres postgres 1.5K Jan 3 11:34 26097.tar -rw------- 1 postgres postgres 80M Jan 3 11:34 base.tar -rw------- 1 postgres postgres 17M Jan 3 11:34 pg_wal.tar
查看備份內(nèi)容：
[postgres@centos1 pgbak2020-01-03]$ tar -tvf base.tar |less -rw------- postgres/postgres 226 2020-01-03 11:34 backup_label -rw------- postgres/postgres 28 2020-01-03 11:34 tablespace_map drwx------ postgres/postgres 0 2020-01-03 11:34 pg_wal/ drwx------ postgres/postgres 0 2020-01-03 11:34 ./pg_wal/archive_status/ drwx------ postgres/postgres 0 2019-12-19 17:24 global/ -rw------- postgres/postgres 16384 2019-12-17 16:42 global/1262 -rw------- postgres/postgres 49152 2019-06-17 23:47 global/1262_fsm -rw------- postgres/postgres 0 2019-06-17 23:47 global/2964 -rw------- postgres/postgres 16384 2020-01-03 10:45 global/1213 -rw------- postgres/postgres 49152 2019-06-17 23:47 global/1213_fsm -rw------- postgres/postgres 16384 2019-06-17 23:47 global/1136 -rw------- postgres/postgres 49152 2019-06-17 23:47 global/1136_fsm -rw------- postgres/postgres 16384 2019-12-17 11:49 global/1260
6、生成測(cè)試恢復(fù)數(shù)據(jù)：
postgres=# create table test_bk (id int) tablespace tbs_pg01; CREATE TABLE postgres=# insert into test_bk values(1),(2); INSERT 0 2
由于WAL文件是寫(xiě)滿(mǎn)16MB才會(huì)進(jìn)行歸檔，測(cè)試階段可能寫(xiě)入會(huì)非常少，可以在執(zhí)行完 基礎(chǔ)備份之后，手動(dòng)進(jìn)行一次WAL切換。如：
postgres=# checkpoint; CHECKPOINT postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/14027F78 (1 row)
7、還原部分
關(guān)閉數(shù)據(jù)庫(kù)：
[postgres@centos1 ~]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@centos1 ~]$ ipcs
移除數(shù)據(jù)庫(kù) 及表空間
[postgres@centos1 ~]$ mv pgdata pgdatatbbk [postgres@centos1 ~]$ mv pg_root pg_rootbk
將備份文件拷貝到原目錄
[postgres@centos1 ~]$ echo $PGDATA /home/postgres/pg_root [postgres@centos1 ~]$ mkdir pg_root [postgres@centos1 ~]$ mkdir pgdata [postgres@centos1 ~]$ cd pgbak2020-01-03 [postgres@centos1 pgbak2020-01-03]$ ll total 96M -rw------- 1 postgres postgres 1.5K Jan 3 11:34 26097.tar -rw------- 1 postgres postgres 80M Jan 3 11:34 base.tar -rw------- 1 postgres postgres 17M Jan 3 11:34 pg_wal.tar [postgres@centos1 pgbak2020-01-03]$ cp 26097.tar /home/postgres/pgdata [postgres@centos1 pgbak2020-01-03]$ cp base.tar $PGDATA [postgres@centos1 pgbak2020-01-03]$ cp pg_wal.tar $PGDATA [postgres@centos1 pgbak2020-01-03]$ cd $PGDATA [postgres@centos1 pg_root]$ ll total 96M -rw------- 1 postgres postgres 80M Jan 3 12:06 base.tar -rw------- 1 postgres postgres 17M Jan 3 12:07 pg_wal.tar
解壓base:
[postgres@centos1 pg_root]$ tar -xvf base.tar
解壓表空間：
[postgres@centos1 pgdata]$ tar -xvf 26097.tar PG_11_201809051/ [postgres@centos1 pgdata]$ ll total 4.0K -rw------- 1 postgres postgres 1.5K Jan 3 12:06 26097.tar drwx------ 2 postgres postgres 6 Jan 2 20:07 PG_11_201809051
解壓歸檔文件：
[postgres@centos1 pg_root]$ tar -xvf pg_wal.tar 000000010000000000000013 archive_status/000000010000000000000013.done
拷貝恢復(fù)文件
[postgres@centos1 pg_root]$ cp /opt/postgresql/share/recovery.conf.sample recovery.conf 配置恢復(fù)文件命令： vi recovery.conf restore_command = 'cp /home/postgres/arch/20200103/%f %p'
啟動(dòng)數(shù)據(jù)庫(kù)：
[postgres@centos1 pg_root]$ pg_ctl start
waiting for server to start....2020-01-03 13:05:16.488 CST [21872] FATAL: data directory "/home/postgres/pg_root" has invalid permissions
2020-01-03 13:05:16.488 CST [21872] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
stopped waiting
pg_ctl: could not start server
Examine the log output.
報(bào)錯(cuò)，修改權(quán)限：
[postgres@centos1 ~]$ chmod -R 750 ./pg_root
啟動(dòng)數(shù)據(jù)庫(kù)：
[postgres@centos1 ~]$ pg_ctl start waiting for server to start....2020-01-03 13:09:16.927 CST [22152] LOG: listening on IPv4 address "0.0.0.0", port 1921 2020-01-03 13:09:16.972 CST [22152] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921" 2020-01-03 13:09:17.035 CST [22153] LOG: database system was interrupted; last known up at 2020-01-03 11:34:44 CST 2020-01-03 13:09:17.035 CST [22153] LOG: creating missing WAL directory "pg_wal/archive_status" 2020-01-03 13:09:17.446 CST [22153] LOG: starting archive recovery 2020-01-03 13:09:17.457 CST [22153] LOG: restored log file "000000010000000000000013" from archive 2020-01-03 13:09:17.700 CST [22153] LOG: redo starts at 0/13000028 2020-01-03 13:09:17.726 CST [22153] LOG: consistent recovery state reached at 0/13000130 2020-01-03 13:09:17.727 CST [22152] LOG: database system is ready to accept read only connections 2020-01-03 13:09:17.743 CST [22153] LOG: restored log file "000000010000000000000014" from archive done server started [postgres@centos1 ~]$ 2020-01-03 13:09:17.920 CST [22153] LOG: restored log file "000000010000000000000015" from archive cp: cannot stat ‘/home/postgres/arch/20200103/000000010000000000000016’: No such file or directory 2020-01-03 13:09:18.084 CST [22153] LOG: redo done at 0/15000140 2020-01-03 13:09:18.085 CST [22153] LOG: last completed transaction was at log time 2020-01-03 11:40:52.26971+08 2020-01-03 13:09:18.125 CST [22153] LOG: restored log file "000000010000000000000015" from archive cp: cannot stat ‘/home/postgres/arch/20200103/00000002.history’: No such file or directory 2020-01-03 13:09:18.310 CST [22153] LOG: selected new timeline ID: 2 2020-01-03 13:09:18.477 CST [22153] LOG: archive recovery complete cp: cannot stat ‘/home/postgres/arch/20200103/00000001.history’: No such file or directory 2020-01-03 13:09:18.840 CST [22152] LOG: database system is ready to accept connections
啟動(dòng)完成，查看表是否存在：
[postgres@centos1 ~]$ psql psql (11.3) Type "help" for help. pgdb=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# select * from test_bk; id ---- 1 2
恢復(fù)完成，恢復(fù)文件會(huì)變成.done
-rwxr-x--- 1 postgres postgres 5.7K Jan 3 13:00 recovery.done
順便記一下邏輯備份的部分嘿嘿
邏輯備份
[postgres@centos1 dump]$ pg_dump -F c -f ./pgdb.dmp -C -E UTF8 -h 192.168.1.212 -p 1921 -U postgres -d pgdb
查看備份文件
[postgres@centos1 dump]$ pg_restore -l ./pgdb.dmp
“postgreSQL11備份與恢復(fù)方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
本文名稱(chēng)：postgreSQL11備份與恢復(fù)方法是什么
文章轉(zhuǎn)載：http://www.dlmjj.cn/article/ijjpjo.html
