0

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.

bornomala
  • 115
  • 2
  • 12
  • Is your RDS instance in a public or private subnet? – littleforest Apr 23 '19 at 19:06
  • RDS instance is in private subnet. I am using the command to restore the Postgres database pg_restore -v -h xxx -U master -d test /home/xxx/Documents/xxx/DB/back.backup – bornomala Apr 24 '19 at 13:24
  • I am getting an error during the time of connection to rds "Is the server running on host "xxx" (xxx) accepting TCP/IP connections on port 5432?" – bornomala Apr 24 '19 at 13:25

1 Answers1

1

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. Make a dump of your local database:
$ pg_dump -Fc --no-acl --no-owner -h localhost -U<username> <database_name> -f <filename>

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. 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]

For example:

$ ssh -i ~/.ssh/ida_rsa -fNL 5433:my_prod_db.cbaxyzxyz.us-west-1.rds.amazonaws.com:5432 ec2-user@ec2-11-222-333-4.us-west-1.compute.amazonaws.com
  1. Then, still on your local computer, import the local database dump into the remote RDS database server:
$ pg_restore --no-owner -n public -c -1 -p 5433 -U<username> -h 127.0.0.1 -d <database_name> <filename>

For example:

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

Enter the RDS database password when prompted.

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

littleforest
  • 2,057
  • 21
  • 29
  • I am using AWS elastic beanstalk. I am not able to establish the tunnel by using the following command `ssh -i ~/.ssh/xyz.pub -fNL 5433:xxx.rds.amazonaws.com:5432 ec2-user@xxx.elasticbeanstalk.com` – bornomala Apr 25 '19 at 10:22
  • I am not sure what will be the username here. Is this rds database username or aws elastic beanstalk username? – bornomala Apr 25 '19 at 10:31
  • Moreover, I also created ssh for aws elastic beanstalk environment by eb ssh --setup commands and using the public key ~/.ssh/xyz.pub. Not sure I am doing anything wrong here. – bornomala Apr 25 '19 at 10:38
  • `debug1: Reading configuration data /etc/ssh/ssh_config debug1: /etc/ssh/ssh_config line 19: Applying options for * debug2: resolving "xxx.elasticbeanstalk.com" port 22 debug2: ssh_connect_direct: needpriv 0 debug1: Connecting to xxx.central-1.elasticbeanstalk.com [xxx] port 22. debug1: connect to address xxx port 22: Connection refused debug1: Connecting to xxx.elasticbeanstalk.com [xxx] port 22. debug1: connect to address xxx port 22: Connection refused ssh: connect to host xxx.elasticbeanstalk.com port 22: Connection refused` – bornomala Apr 25 '19 at 12:53
  • I can connect to by using eb ssh environment name – bornomala Apr 25 '19 at 13:08
  • First, can you just connect to the ec2 instance via: `ssh -i ~/.ssh/xyz ec2-user@ec2-11-222-333-4.us-west-1.compute.amazonaws.com`. Do NOT put the `.pub` extension on the ssh key. – littleforest Apr 25 '19 at 13:29
  • I runs `ssh -i ~/.ssh/xyz ec2-user@xyz.xyz.eu-central-1.elasticbeanstalk.com` But getting some error connection refuse – bornomala Apr 25 '19 at 13:35
  • Make sure that you modify your security group for the EC2 instance to allow ssh from port 22 for your home IP address. Elastic Beanstalk will force that port when you use `eb ssh environment-name`, but to ssh in directly, you need to make sure the port is open for your IP address. You can also just allow port 22 to be open to everyone for now while you are testing, since no one else should have a private key allowing them to ssh in to the instance. – littleforest Apr 25 '19 at 13:36
  • Security group is open for ssh port 22. – bornomala Apr 25 '19 at 13:40
  • Is your ssh key added to the ssh-agent on your local machine? `$ eval "$(ssh-agent -s)"`, `$ ssh-add ~/.ssh/id_xyz`? – littleforest Apr 25 '19 at 13:50
  • ssh-add ~/.ssh/id_rsa shows Identity added: /home/xyz/.ssh/id_rsa (/home/xyz/.ssh/id_rsa) – bornomala Apr 25 '19 at 13:57
  • `xyz@xyz-VirtualBox:~/.ssh$ ll -rw------- 1 xyz xyz 1675 Apr 24 19:31 id_rsa -rw-r--r-- 1 xyz xyz 406 Apr 24 19:31 id_rsa.pub -rw-r--r-- 1 xyz xyz 1106 Apr 25 13:55 known_hosts -rw------- 1 xyz xyz 1675 Apr 24 22:08 zxyzr -rw------- 1 xyz xyz 388 Apr 24 22:08 zxyz.pub` – bornomala Apr 25 '19 at 14:01
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192377/discussion-between-littleforest-and-jisan). – littleforest Apr 25 '19 at 14:05