哪种pgdump格式最适合较小的存储空间并快速恢复？ [英] Which pgdump format is best for small storage size and fast restore?
问题描述
这是PostgreSQL备份（数据库转储）的首次尝试，我一直在研究不同的pgdump格式，其他pgdump选项和pgdumpall。对于一个Postgres初学者来说，希望每小时对两个包含表触发器和每个数据库中两个不同模式的数据库进行一次转储（将覆盖以前的转储），那么备份格式和轻松实现以下目标的选项将是什么：
This a first time foray in PostgreSQL backups (db dumps) and I've been researching the different pgdump formats, other pgdump options, and pgdumpall. For a Postgres beginner looking at taking an hourly dump (will overwrite previous dump) of two databases that contain table triggers and two different schemas in each db, what would be the backup format and options to easily achieve the following:
- 文件大小小（每个数据库只有一个文件，或者可以选择要还原的数据库）
- 易于还原为干净文件db（具有&而没有相同的数据库名称）
- 易于在其他服务器上还原（用户可能有所不同）
- 在以下位置禁用触发恢复并在恢复后重新启用。
- Small file size (single file per db or ability to choose which db to restore)
- Easy to restore as clean db (with & without same db name[s])
- Easy to restore on different server (user maybe different)
- Triggers are disabled on restore and re-enabled after restore.
包括示例命令以进行备份和恢复。
Include example commands to backup and restore.
欢迎其他有用的pgdump / pgrestore建议。
Any other helpful pgdump/pgrestore suggestions welcome.
推荐答案
此命令将创建一个小的dmp文件，仅包含结构dattabase的名称-表格，列，触发器，视图等。（此命令仅需几分钟）
This command will create a small dmp file which includes only structure of the dattabase - tabels, columns, triggers, views etc.. (This command will just take few minutes)
pg_dump -U "dbuser" -h "host" -p "port" -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname
**ex:** pg_dump -U thames -h localhost -p 5432 -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname
此命令将备份整个数据库
This command will take the backup of complete database
pg_dump -h localhost -U "dbuser" "dbname" -Fc > "pathfilename.backup"
**ex:** pg_dump -h localhost -U thames thamesdb - Fc > "thamesdb.backup"
要恢复，您可以使用：
pg_restore -i -h localhost -U "user" -d "dbname" -v "dbname.backup"
**ex:** pg_restore -i -h localhost -U thames -d thamesdb -v "thamesdb.backup"
备份选定表格（使用正则表达式）此处
to take backup of selected tabels(uses regular expressions) here
pg_dump -t '(A|B|C)'
有关完整详细信息，您可以访问 pgdump 帮助页面，有很多选择
for full details you can visit pgdump help page there are many options out there
这篇关于哪种pgdump格式最适合较小的存储空间并快速恢复？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
