在Heroku上备份/加载后，PostgreSQL数据库的大小更小 [英] PostgreSQL database size is less after backup/load on Heroku

问题描述

最近，我创建了一个用于生产的新Heroku应用程序，并使用从暂存数据库中取出的备份填充了它的数据库。

问题在于数据库的大小，正如Heroku的Postgres网页上显示的这两个数据库是不同的！

第一个数据库，我从中备份了360 MB，并且只填充了新的数据库290 MB。

在备份/加载过程中没有出现错误。并从这两个数据库中进行备份会产生相同的备份文件大小（大约40 MB）。

项目工作正常，两个应用程序看起来完全一样，但我担心我可能会丢失一些会在将来造成麻烦的数据。

更多信息：我正在使用同样的生产数据库计划在这两个应用程序。

此外，第一个数据库不附加到第一个实例（因为它是从Postgres管理页面添加的，而不是应用程序的资源页面）和新的数据库附加到新的应用程序。

在此先感谢

解决方案

postgresql DB在使用时可以消耗更多空间。

其原因是它的MVCC系统。每当您 UPDATE 数据库中的任何记录时，它都会创建该记录的另一个版本，而不是重写前一个记录。这些过时的记录将被 VACUUM 过程删除，当它们不需要时。

，当你从备份中恢复你的数据库时，它没有任何死的记录，并且它的大小更小。

详细信息和 http://www.postgresql.org/docs/current/static/sql-vacuum。 HTML 。

PS你不需要担心它。 Postgresql会自动处理 VACUUM 。

Recently I created a new Heroku app for production and populated it's database with a backup that I took from the staging database.

The problem is that the database size, as shown on Heroku's Postgres webpage for the two databases is different!

The first database, where I took the backup from was 360 MBs and the new database that was populated was only 290 MBs.

No errors showed up during the backup/load process. and taking a backup from the two databases results in the same backup file size (around 40 MBs).

The project is working fine, and the two apps look exactly the same, but I'm concerned that I might have lost some data that would cause troubles in the future.

More info: I'm using the same production database plan on both Apps.

Also, the first database is not attached to the first instance (because it was added from the Postgres management page, not from the App's resources page) and the new database is attached to the new App.

Thanks in advance

解决方案

It is ok for postgresql DB to consume more space when in use.

The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM process, when there will be no need in them.

So, when you restored your db from backup, it didn't have any "dead" records and its size was less.

Details here http://www.postgresql.org/docs/current/static/mvcc.html and http://www.postgresql.org/docs/current/static/sql-vacuum.html.

P.S. You do not need to worry about it. Postgresql will handle VACUUM automatically.

这篇关于在Heroku上备份/加载后，PostgreSQL数据库的大小更小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！