揭示PostgreSQL逻辑备份与恢复进度监控的最佳方法

##

揭示PostgreSQL逻辑备份与恢复进度监控的最佳方法

1、概述

今天有一个客户提了一个需要，在postgresql做逻辑备份和恢复时希望知道两个指标，

第一个是完成备份和恢复需要多少时间.

第二个指标是备份和恢复任务的百分比.

2、解决方法

2.1、使用参数进行估算

在备份和恢复是有一个-v的参数可以帮助我们查看一些详细信息。

[postgres@aliyundb:/postgresql/backup]$pg_dump -U postgres -Fd -j 4 -v -f b1 demo
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding table default expressions
pg_dump: finding table check constraints
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: dumping contents of table "bookings.ticket_flights"
pg_dump: dumping contents of table "bookings.bookings"
pg_dump: dumping contents of table "bookings.tickets"
pg_dump: dumping contents of table "bookings.boarding_passes"
pg_dump: finished item 4145 TABLE DATA bookings
pg_dump: dumping contents of table "bookings.flights"
pg_dump: finished item 4146 TABLE DATA flights
pg_dump: dumping contents of table "bookings.seats"
pg_dump: finished item 4148 TABLE DATA seats
pg_dump: dumping contents of table "bookings.airports_data"
pg_dump: finished item 4143 TABLE DATA airports_data
pg_dump: dumping contents of table "bookings.aircrafts_data"
pg_dump: finished item 4142 TABLE DATA aircrafts_data
pg_dump: finished item 4144 TABLE DATA boarding_passes
pg_dump: finished item 4150 TABLE DATA tickets
pg_dump: finished item 4149 TABLE DATA ticket_flights

2.2、使用shell实现

网上有脚本，原理是通过对象统计的方法去实现。