# WAL日志: # 我们对数据库的增删改查创建之前先是将sql语句记录在WAL日志中, # 只有日志记录刷新到磁盘后,才能写入数据库文件。 # 遵从这个过程,不需要在每个事务提交时都刷新数据页到数据文件。 # 理论上我们通过WAL日志可以回到数据的任意时间点 # 文件存储位置:$PGDATA/pg_wal # 归档日志: # 归档日志就是将WAL日志的完整拷贝 # 触发归档日志的条件: # 手动强制切换 select pg_switch_wall(); # wal日志写满后 # 参数archive_tomeout # 开启归档日志 alter system set wal_level = replica; # 设置日志级别 alter system set archive_mode = on; # 开启,off是关闭 修改完要重启数据库 # /hgdbbak/archhive为归档存放路劲 alter system archive_command = 'test ! -f /hgdbbak/archhive/%f && cp %p /hgdbbak/archive/%f' # 物理备份和逻辑备份的区别: # 物理备份后你可以恢复任意时间点,逻辑备份只能恢复到你备份的那个时候 # 物理备份: # pg_basebackup # 这个工具会把整个数据库实例的数据拷贝出来,不能进行单张表备份。该工具使用replication协议链接到数据库实例上,所以主数据库中的pg_hba.conf必须允许replication连接。 # 将本地集群中的数据库备份到指定的路劲下。-U指定用户,-D指定备份到哪个目录 pg_basebackup -U highgo -D /hgdbbak # 将本地集群中的数据库以压缩格式并行备份 pg_basebackup -U highgo -D /hgdbbak -Ft -z -j 5 -P # cp/rsync/tar # 以数据库超级用户省份连接数据库,然后发出命令: select pg_start_backup('lable'); # pg_start_backup开启备份后,你可以使用任何方便的文件系统工具,比如:tar、rsync、cp等,直接吧数据库目录赋值到备份位置,这些操作过程中既不需要关闭数据库,也不需要组赛数据库的任何操作。操作完成后直接执行: select pg_stop_backup(); # pg_basebackup、cp/rsync/tar备份的恢复 # 1.对压缩文件进行解压缩 # 2.修改解压缩目录下的postgresql.auto.conf文件,追加如下内容: restore_command = 'cp /hgdbbak/archive/%f %p' # 3.如果需要恢复到某一个时间点的话就追加以下内容: recovery_target_time = '2020-10-01 17:50:00' # 4.启动数据库 # HG_RMAN # 该备份工具具有备份集管理功能,只需要进行简单的配置,就可以实现对数据库备份的全面管理。 # 1.最小备份单元:基于物理块备份。 # 2.备份结果集管理:历史记录,保留策略,有效性校验等 # 3.备份模式:支持全量、增量及归档日志在线备份 # 4.恢复模式:支持Point-In-Time Recovery(PITR)也就是时间点恢复,支持完全恢复,支持不完全恢复,支持数据块恢复 # 5.独有的技术:块变化跟踪Block Change Tracking(BTC)技术,块介质修复Block Media Recovery(BMR)技术。这两个技术的实现,及时前台在做业务也不会有任何影响。 # 6.高性能,高存储利用率:备份压缩,快速完成增量备份。独有的增量备份机制,可以有效减少备份的存储空间占用。 # 冷备 # 冷备份就是停止数据库,然后将$PGDATA目录压缩成tar进行备份。这是最快最安全的方法 # 备份命令 tar -zcvf -f /dbbak/dbbak20211109.tar.gz $PGDATA # 恢复备份 tar -zxvf -f /dbbak/dbbak20211109.tar.gz -C /pgdata # 逻辑备份: # pg_dump和pg_dumpall的区别 # pg_dump支持指定丁所要被的的对象(表、schema、database),备份转储文件的时候并不会影响其他用户的访问。转储的文件可以是脚本(也就是包含SQL命令的村文本文件),也可以是自定的的文件格式,但自定义的文件格式必须用pg_restore奇异使用重建数据库。 # pg_dumpall仅支持导出全部库数据。备份的时候不影响其他用户的访问。并且只能以脚本的方式保存。 # 仅导出数据库对象结构 pg_dump -U odoo -s -f odoodb.sql highgo # 备份某个数据库,备份结果以自定义压缩格式输出 pg_dump -h localhost -p 5432 -U odoo -F c -b -v -f odoodb.back odoodb # 备份某个数据库中一个或者多个schema,压缩并导出 pg_dump -h localhost -p 5432 -U odoo -F c -b -v -n schema1 -n schema2 -j 5 -f odoodb_sch1_sch2.backup odoodb # pg_dumpall 仅导出定义,不到处数据 pg_dumpall -h localhost -U odoo --PORT 5432 -f odoodb.sql -s # pg_dumpall 导出所有数据库 pg_dumpall -U odoo > /dbbak/odoo_all.dmp # 通过pg_dumpall或者pg_dump命令指定文本格式导出的,在检查文件无误后可以直接使用psql进行导入恢复 psql -d odoodb -U odoo < /dbbak/odoodb_all.dmp # 其他自定义格式,需要借助pg_restore工具进行恢复 pg_restore -d odoodb -U odoo /dbbak/odoodb_all.dmp # copy && \copy # 该命令的主要作用是在数据库表和文件之间复制数据 # copy 与 \copy的区别 # 使用copy是从服务端寻找文件,使用\copy是从客户端寻找文件 # copy一般使用超级用户执行;copy .. to file, copy file to ..中的文件都是数据库服务器所在的服务器上的文件。具有pg_read_server_files权限的用户也可执行 # \copy一般用户即可执行;\copy保存或者读取的文件是在客户端所在的服务器中。 # 比如使用192.168.100.53 连上 192.168.100.52的数据库 # 当使用copy tb1 to '/home/pc1/test.sql',该test.sql文件是存放在52上;当使用copy tb1 to '/home/pc1/test.sql',该文件是存放在53上。 # 将表test_tab打印至屏幕,并使用'|'作为分隔符(STDOUT 无需pg_read_server_files权限) COPY test_tab TO STDOUT (DELIMITER '|'); # 将表test_tab拷贝至文件中 COPY test_tab TO '/backup/test_tab_data'; # 按查询条件导出到文件 COPY (select * from test_db where name like 'A%') TO '/backup/test_tab_data' (DELIMITER '|'); # 将表数据拷贝至压缩文件中 COPY test_tab TO PROGRAM 'gzip > /backup/test_tab_data.gz' COPY test_tab TO PROGRAM 'zip > /backup/test_tab_data.gz' # 把数据从文件中复制到表中 COPY test_tab FROM '/backup/test_tab_data'; COPY test_tab(name) FROM '/backup/test_tab_data'; # \copy 将表数据拷贝至文件中 \copy sometable to sonmefile.text # \copy 将文件中的数据插入表中 \copy sometable from somefile.txt