postgreSQL11备份与恢复方法是什么

2025-03-14 技术教程

本篇内容介绍了“postgreSQL11备份与恢复方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、归档目录：

[postgres@centos1arch]$pwd/home/postgres/arch

2、设置归档命令：

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参数都需要重新启动数据库才可以生效，修改archive_command不需要重启，只需要reload即可：

postgres=#SELECTpg_reload_conf();

3、验证归档：

postgres=#checkpointpostgres-#;CHECKPOINTpostgres=#selectpg_switch_wal();pg_switch_wal---------------0/11029F08(1row)[postgres@centos120200103]$lltotal16M-rw-------1postgrespostgres16MJan310:45000000010000000000000011

4、配置备份用户访问：

[postgres@centos1pg_root]$vipg_hba.confhostreplicationrep0.0.0.0/0md5

5、创建基础备份：

[postgres@centos1pgbak]$pg_basebackup-Ft-D/home/postgres/pgbak`date+%F`-h192.168.1.212-p1921-UrepPassword:[postgres@centos1pgbak2020-01-03]$lltotal96M-rw-------1postgrespostgres1.5KJan311:3426097.tar-rw-------1postgrespostgres80MJan311:34base.tar-rw-------1postgrespostgres17MJan311:34pg_wal.tar

查看备份内容：

[postgres@centos1pgbak2020-01-03]$tar-tvfbase.tar|less-rw-------postgres/postgres2262020-01-0311:34backup_label-rw-------postgres/postgres282020-01-0311:34tablespace_mapdrwx------postgres/postgres02020-01-0311:34pg_wal/drwx------postgres/postgres02020-01-0311:34./pg_wal/archive_status/drwx------postgres/postgres02019-12-1917:24global/-rw-------postgres/postgres163842019-12-1716:42global/1262-rw-------postgres/postgres491522019-06-1723:47global/1262_fsm-rw-------postgres/postgres02019-06-1723:47global/2964-rw-------postgres/postgres163842020-01-0310:45global/1213-rw-------postgres/postgres491522019-06-1723:47global/1213_fsm-rw-------postgres/postgres163842019-06-1723:47global/1136-rw-------postgres/postgres491522019-06-1723:47global/1136_fsm-rw-------postgres/postgres163842019-12-1711:49global/1260

6、生成测试恢复数据：

postgres=#createtabletest_bk(idint)tablespacetbs_pg01;CREATETABLEpostgres=#insertintotest_bkvalues(1),(2);INSERT02

由于WAL文件是写满16MB才会进行归档，测试阶段可能写入会非常少，可以在执行完基础备份之后，手动进行一次WAL切换。如：

postgres=#checkpoint;CHECKPOINTpostgres=#selectpg_switch_wal();pg_switch_wal---------------0/14027F78(1row)

7、还原部分

关闭数据库：

[postgres@centos1~]$pg_ctlstopwaitingforservertoshutdown....doneserverstopped[postgres@centos1~]$ipcs

移除数据库及表空间

[postgres@centos1~]$mvpgdatapgdatatbbk[postgres@centos1~]$mvpg_rootpg_rootbk

将备份文件拷贝到原目录

[postgres@centos1~]$echo$PGDATA/home/postgres/pg_root[postgres@centos1~]$mkdirpg_root[postgres@centos1~]$mkdirpgdata[postgres@centos1~]$cdpgbak2020-01-03[postgres@centos1pgbak2020-01-03]$lltotal96M-rw-------1postgrespostgres1.5KJan311:3426097.tar-rw-------1postgrespostgres80MJan311:34base.tar-rw-------1postgrespostgres17MJan311:34pg_wal.tar[postgres@centos1pgbak2020-01-03]$cp26097.tar/home/postgres/pgdata[postgres@centos1pgbak2020-01-03]$cpbase.tar$PGDATA[postgres@centos1pgbak2020-01-03]$cppg_wal.tar$PGDATA[postgres@centos1pgbak2020-01-03]$cd$PGDATA[postgres@centos1pg_root]$lltotal96M-rw-------1postgrespostgres80MJan312:06base.tar-rw-------1postgrespostgres17MJan312:07pg_wal.tar

解压base:

[postgres@centos1pg_root]$tar-xvfbase.tar

解压表空间：

[postgres@centos1pgdata]$tar-xvf26097.tarPG_11_201809051/[postgres@centos1pgdata]$lltotal4.0K-rw-------1postgrespostgres1.5KJan312:0626097.tardrwx------2postgrespostgres6Jan220:07PG_11_201809051

解压归档文件：

[postgres@centos1pg_root]$tar-xvfpg_wal.tar000000010000000000000013archive_status/000000010000000000000013.done

拷贝恢复文件

[postgres@centos1pg_root]$cp/opt/postgresql/share/recovery.conf.samplerecovery.conf配置恢复文件命令：virecovery.confrestore_command='cp/home/postgres/arch/20200103/%f%p'

启动数据库：

[postgres@centos1pg_root]$pg_ctlstart

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.

报错，修改权限：

[postgres@centos1 ~]$ chmod -R 750 ./pg_root

启动数据库：

[postgres@centos1~]$pg_ctlstartwaitingforservertostart....2020-01-0313:09:16.927CST[22152]LOG:listeningonIPv4address"0.0.0.0",port19212020-01-0313:09:16.972CST[22152]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.1921"2020-01-0313:09:17.035CST[22153]LOG:databasesystemwasinterrupted;lastknownupat2020-01-0311:34:44CST2020-01-0313:09:17.035CST[22153]LOG:creatingmissingWALdirectory"pg_wal/archive_status"2020-01-0313:09:17.446CST[22153]LOG:startingarchiverecovery2020-01-0313:09:17.457CST[22153]LOG:restoredlogfile"000000010000000000000013"fromarchive2020-01-0313:09:17.700CST[22153]LOG:redostartsat0/130000282020-01-0313:09:17.726CST[22153]LOG:consistentrecoverystatereachedat0/130001302020-01-0313:09:17.727CST[22152]LOG:databasesystemisreadytoacceptreadonlyconnections2020-01-0313:09:17.743CST[22153]LOG:restoredlogfile"000000010000000000000014"fromarchivedoneserverstarted[postgres@centos1~]$2020-01-0313:09:17.920CST[22153]LOG:restoredlogfile"000000010000000000000015"fromarchivecp:cannotstat‘/home/postgres/arch/20200103/000000010000000000000016’:Nosuchfileordirectory2020-01-0313:09:18.084CST[22153]LOG:redodoneat0/150001402020-01-0313:09:18.085CST[22153]LOG:lastcompletedtransactionwasatlogtime2020-01-0311:40:52.26971+082020-01-0313:09:18.125CST[22153]LOG:restoredlogfile"000000010000000000000015"fromarchivecp:cannotstat‘/home/postgres/arch/20200103/00000002.history’:Nosuchfileordirectory2020-01-0313:09:18.310CST[22153]LOG:selectednewtimelineID:22020-01-0313:09:18.477CST[22153]LOG:archiverecoverycompletecp:cannotstat‘/home/postgres/arch/20200103/00000001.history’:Nosuchfileordirectory2020-01-0313:09:18.840CST[22152]LOG:databasesystemisreadytoacceptconnections

启动完成，查看表是否存在：

[postgres@centos1~]$psqlpsql(11.3)Type"help"forhelp.pgdb=#\cpostgresYouarenowconnectedtodatabase"postgres"asuser"postgres".postgres=#select*fromtest_bk;id----12

恢复完成，恢复文件会变成.done

-rwxr-x---1postgrespostgres5.7KJan313:00recovery.done

顺便记一下逻辑备份的部分嘿嘿

逻辑备份

[postgres@centos1dump]$pg_dump-Fc-f./pgdb.dmp-C-EUTF8-h192.168.1.212-p1921-Upostgres-dpgdb

查看备份文件

[postgres@centos1dump]$pg_restore-l./pgdb.dmp

“postgreSQL11备份与恢复方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！