postgresql 如何减少存储（缩减）我的 RDS 实例？

How to reduce storage(scale down) my RDS instance?

提问by Hello lad

I have a RDS(Postgres) instance with Storage SSD 1000GB, but the data is only 100GB of size.

我有一个带有 1000GB 存储 SSD 的 RDS(Postgres) 实例，但数据只有 100GB 的大小。

How can I scale down the storage resource of RDS easily ?

如何轻松缩减RDS的存储资源？

采纳答案by Mark B

RDS does not allow you to reduce the amount of storage allocated to a database instance, only increase it.

RDS 不允许您减少分配给数据库实例的存储量，只能增加它。

To move your database to less storage you would have to create a new RDS instance with your desired storage space, then use something like pg_dump/pg_restore to move the data from the old database to the new one.

要将数据库移动到较少的存储空间，您必须使用所需的存储空间创建一个新的 RDS 实例，然后使用类似 pg_dump/pg_restore 的东西将数据从旧数据库移动到新数据库。

Also be aware that an RDS instance with 1,000GB of SSD storage has a base IOPS of 3,000. An RDS instance with 100GB of SSD storage has a base IOPS of 300, with occasional bursts of up to 3,000.

另请注意，具有 1,000GB SSD 存储的 RDS 实例的基本 IOPS 为 3,000。具有 100GB SSD 存储的 RDS 实例的基本 IOPS 为 300，偶尔突发高达 3,000。

回答by Luke

Based on AWS's help here, this is the full process that worked for me:

基于 AWS在此处的帮助，这是对我有用的完整过程：

1) Dump the database to a file: run this on a machine that has network access to the database: pg_dump -Fc -v -h your-rds-endpoint.us-west-2.rds.amazonaws.com -U your-username your-databasename > your-databasename.dump

1)将数据库转储到文件中：在可以通过网络访问数据库的机器上运行： pg_dump -Fc -v -h your-rds-endpoint.us-west-2.rds.amazonaws.com -U your-username your-databasename > your-databasename.dump

2) In the AWS console, create a new RDS instance with smaller storage. (You probably want to set it up with the same username, password, and database name.)

2) 在 AWS 控制台中，新建一个较小存储的 RDS 实例。（您可能希望使用相同的用户名、密码和数据库名称进行设置。）

3) Restore the database on the new RDS instance: run this command (obviously on the same machine as the previous command): pg_restore -v -h the-new-rds-endpoint.us-west-2.rds.amazonaws.com -U your-username -d your-databasename your-databasename.dump

3）在新的RDS实例上恢复数据库：运行这个命令（显然和上一个命令在同一台机器上）： pg_restore -v -h the-new-rds-endpoint.us-west-2.rds.amazonaws.com -U your-username -d your-databasename your-databasename.dump

(Note, in step 3, that I'm using the endpoint of the new RDS instance. Also note there's no :5432at the end of the endpoint addresses.)

（请注意，在第 3 步中，我使用的是新 RDS 实例的端点。还要注意:5432端点地址的末尾没有。）

回答by Sudhir Shekhsaria

Amazon doesn't allow to reduce size of HDD of RDS instance, you may have two options to reduce size of storage.

Amazon 不允许减少 RDS 实例的硬盘大小，您可能有两种选择来减少存储大小。

1:-if you can afford downtimes, then mysqldump backup of old instance can be restored to new instance having lesser storage size.

1：-如果您能承受停机时间，则可以将旧实例的 mysqldump 备份还原到具有较小存储大小的新实例。

2:- You can use Database migration service to move data from one instance to another instance without any downtime.

2:- 您可以使用数据库迁移服务将数据从一个实例移动到另一个实例，而无需停机。

回答by mootmoot

When using RDS, instead of doing typical hardware "capacity planning", you just provisioning just enough disk space for short or medium term (depends), expand it when needed.

使用 RDS 时，无需进行典型的硬件“容量规划”，您只需为短期或中期（视情况而定）配置足够的磁盘空间，并在需要时扩展它。

As @Mark B mentioned , you need to watchout the IOPS as well. You can use "provisioned IOPS" if you need high performance DB.

正如@Mark B 提到的，您还需要注意 IOPS。如果您需要高性能数据库，您可以使用“预配置 IOPS”。

You should make you cost vs performance adjustment before jump into the disk space storage part. E.g. if you reduce 1000GB to 120GB , for US west, you will save 0.125x 880GB = 110/month. But the Max IOPS will be 120x 3 = 360IOPS

在进入磁盘空间存储部分之前，您应该进行成本与性能的调整。例如，如果您将 1000GB 减少到 120GB ，对于美国西部，您将节省 0.125x 880GB = 110/月。但最大 IOPS 将为 120x 3 = 360IOPS

It will cost you $0.10 to provision additional IOPS to increase performance. Say if you actually need 800IOPS for higher online user response,
(800-360) x 0.10 = $44. So the actual saving may eventually "less". You will not save any money if your RDS need constant 1100 IOPS. And also other discount factor may come into play.

配置额外的 IOPS 以提高性能将花费您 0.10 美元。假设您实际上需要 800IOPS 以获得更高的在线用户响应，
(800-360) x 0.10 = $44。所以实际节省的钱最终可能会“减少”。如果您的 RDS 需要恒定的 1100 IOPS，您将不会节省任何资金。并且其他折扣因素也可能发挥作用。

回答by Marius Marais

You can do this by migrating the DB to Aurora.

您可以通过将数据库迁移到 Aurora 来做到这一点。

If you don't want Aurora, the Data Migration Service is the best option in my opinion. We're moving production to Aurora, so this didn't matter, and we can always get it back out of Aurora using pg_dumpor DMS. (I assume this will apply to MySQL as well, but haven't tested it.)

如果你不想要 Aurora，我认为数据迁移服务是最好的选择。我们正在将生产转移到 Aurora，所以这并不重要，我们总是可以使用pg_dump或 DMS将其从 Aurora 中取回。（我认为这也适用于 MySQL，但尚未对其进行测试。）

My specific goal was to reduce RDS Postgres final snapshot sizes after decommissioning some instances that were initially created with 1TB+ storage each.

我的具体目标是在停用一些最初创建的实例（每个实例具有 1TB 以上的存储空间）后减少 RDS Postgres 的最终快照大小。

1. Create the normal snapshot. The full provisioned storage size is allocated to the snapshot.
2. Upgrade the snapshot to an engine version supported by Aurora, if not already supported. I chose 10.7.
3. Migrate the snapshot to Aurora. This creates a new Aurora DB.
4. Snapshot the new Aurora DB. The snapshot storage size starts as the full provisioned size, but drops to actual used storage after completion.
5. Remove the new Aurora DB.
6. Confirm your Aurora snapshot is good by restoring it again and poking around in the new new DB until you're satisfied that the original snapshots can be deleted.
7. Remove new new Aurora DB and original snapshot.

1. 创建普通快照。完整的预配置存储大小分配给快照。
2. 将快照升级到 Aurora 支持的引擎版本（如果尚不支持）。我选择了 10.7。
3. 将快照迁移到 Aurora。这将创建一个新的 Aurora 数据库。
4. 快照新的 Aurora 数据库。快照存储大小开始时为完整配置大小，但在完成后下降到实际使用的存储。
5. 删除新的 Aurora 数据库。
6. 确认您的 Aurora 快照是好的，方法是再次恢复它并在新的新数据库中查找，直到您对可以删除原始快照感到满意为止。
7. 删除新的新 Aurora 数据库和原始快照。

You can stop at 3 if you want and just use the Aurora DB going forward.

如果需要，您可以在 3 点停止，然后继续使用 Aurora DB。