将本地PostgreSQL数据库文件合并到AWS RDS数据库 [英] Merging local PostgreSQL database file to AWS RDS database

问题描述

我的本​​地计算机上有一个本地Postgres数据库.现在，我想将本地Postgres数据库文件合并到AWS现有RDS数据库.有谁知道如何做到这一点?预先谢谢你.

I have a local Postgres database in my local machine. Now I would like to merge a local Postgres database file to AWS existing RDS database. Does anyone know how to do this? Thank you in advance.

推荐答案

如果RDS实例位于专用子网中，则需要通过EC2实例进行隧道传输才能到达RDS实例.假设已设置安全组，以便可以通过SSH进入EC2实例，并且EC2实例可以访问端口5432上的RDS，则可以执行以下操作:

If the RDS instance is in a private subnet, then you need to tunnel through an EC2 instance to get to your RDS instance. Assuming your security groups are set so that you can ssh into the EC2 instance, and the EC2 instance has access to RDS on port 5432, you can do the following:

1. 转储本地数据库:

$ pg_dump -Fc --no-acl --no-owner -h localhost -U<username> <database_name> -f <filename>

其中< username> 是本地计算机上的Postgres用户名("postgres"是默认用户).例如:

where <username> is the Postgres username on the local computer ('postgres' is the default user). For example:

$ pg_dump -Fc --no-acl --no-owner -h localhost -Upostgres my_development_db -f data.dump

1. 在本地计算机上，建立到RDS实例的隧道.此示例假定 ec2-user 是EC2实例上的默认用户，如果您使用的是AWS映像，则是这种情况.

1. On your local computer, set up a tunnel into the RDS instance. This example assumes that ec2-user is the default user on your EC2 instance, which is the case if you are using an AWS image.

$ ssh -i /path/to/ssh/key -fNL 5433:[database_host]:5432 ec2-user@[app_host]

例如:

$ ssh -i ~/.ssh/ida_rsa -fNL 5433:my_prod_db.cbaxyzxyz.us-west-1.rds.amazonaws.com:5432 [e-mail]

1. 然后，仍在本地计算机上，将本地数据库转储导入到远程RDS数据库服务器中:

$ pg_restore --no-owner -n public -c -1 -p 5433 -U<username> -h 127.0.0.1 -d <database_name> <filename>

例如:

$ pg_restore --no-owner -n public -c -1 -p 5433 -Umy_prod_username -h 127.0.0.1 -d prod_db_name data.dump

在出现提示时输入RDS数据库密码.

Enter the RDS database password when prompted.

请注意， -c (干净")选项将删除数据库对象，然后再从本地数据库转储中重新创建它们.

Note that the -c ("clean") option, will drop the database objects before recreating them from your local database dump.

这篇关于将本地PostgreSQL数据库文件合并到AWS RDS数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！