将postgres转储迁移到RDS [英] Migrate postgres dump to RDS

问题描述

我有一个在数字海洋上运行的Django postgres数据库(v9.3.10)，正在尝试将其迁移到Amazon RDS(postgres v 9.4.5).RDS是具有300GB的db.m3.xlarge实例.我已将Digital Ocean db转储为:

I have a Django postgres db (v9.3.10) running on digital ocean and am trying to migrate it over to Amazon RDS (postgres v 9.4.5). The RDS is a db.m3.xlarge instance with 300GB. I've dumped the Digital Ocean db with:

sudo -u postgres pg_dump -Fc -o -f /home/<user>/db.sql <dbname>

现在我正尝试通过以下方式进行迁移:

And now I'm trying to migrate it over with:

 pg_restore -h <RDS endpoint> --clean -Fc -v -d <dbname> -U <RDS master user> /home/<user>/db.sql

我看到的唯一错误是:

  pg_restore: [archiver (db)] Error from TOC entry 2516; 0 0 COMMENT EXTENSION plpgsql
  pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
  Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

除此之外，一切似乎都很好，然后又停了下来.转储的文件约为550MB，并且有一些带有多个索引的表，否则非常标准.

Apart from that everything seems to be going fine and then it just grinds to a halt. The dumped file is ~550MB and there are a few tables with multiple indices, otherwise pretty standard.

AWS接口上的读写IOPS接近0，CPU，内存和存储也是如此.我对AWS非常陌生，并且知道参数组可能需要进行调整才能更好地做到这一点.任何人都可以建议将Django db迁移到RDS的这种或更好的方法吗?

The Read and Write IOPS on the AWS interface are near 0, as is the CPU, memory, and storage. I'm very new to AWS and know that the parameter groups might need tweaking to do this better. Can anyone advise on this or a better way to migrate a Django db over to RDS?

查看数据库用户，DO数据库看起来像这样:

Looking at the db users the DO db looks like:

Role Name   Attr                                           Member Of
<user>      Superuser                                      {}
postgres    Superuser, Create role, Create DB, Replication {}

RDS如下:

Role Name      Attr                     Member Of
<user>        Create role, Create DB    {rds_superuser}
rds_superuser Cannot login              {}
rdsadmin      ...                        ...

所以对我来说这似乎不是权限问题，因为< user>在每种情况下都具有超级用户权限.

So it doesn't look like it's a permissions issue to me as <user> has superuser permissions in each case.

寻找任何人的解决方案:

我终于使用:

cat <db.sql> | sed -e '/^COMMENT ON EXTENSION plpgsql IS/d' > edited.dump
psql -h <RDS endpoint> -U <user> -e <dname> < edited.dump

对于可靠的备份/还原机制而言，它不是理想的选择，但是鉴于我只是想说一句，我想我不能没有.我唯一的观察结果是将psql/pg_restore运行到远程主机很慢.希望新的数据库迁移服务会添加一些内容.

It's not ideal for a reliable backup/restore mechanism but given it is only a comment I guess I can do without. My only other observation is that running psql/pg_restore to a remote host is slow. Hopefully the new database migration service will add something.

推荐答案

我认为它没有停止.它只是重新创建索引，外键等.使用 pg_restore -v 可以查看还原过程中发生的情况.检查日志或将输出重定向到文件以检查导入后是否有任何错误，因为这很冗长.

I think it did not halt. It was just recreating indexes, foreign keys etc. Use pg_restore -v to see what's going on during the restore. Check the logs or redirect output to a file to check for any errors after import, as this is verbose.

我还建议使用目录格式( pg_dump -v -Fd )，因为它允许并行还原( pg_restore -v -j4 ).

Also I'd recommend using directory format (pg_dump -v -Fd) as it allows for parallel restore (pg_restore -v -j4).

您可以忽略此 ERROR:必须是扩展程序plpgsql的所有者.这只是对扩展名设置注释，该扩展名还是默认安装的.这是由于PostgreSQL的RDS风格的特殊性导致的，该特性不允许以postgres用户身份连接时还原数据库.

You can ignore this ERROR: must be owner of extension plpgsql. This is only setting a comment on extension, which is installed by default anyway. This is caused by a peculiarity in RDS flavor of PostgreSQL, which does not allow to restore a database while connecting as postgres user.

这篇关于将postgres转储迁移到RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！