pgsync cannot connect to Amazon PostgreSQL database on RDS

Question

I am trying to synchronize data from my local dev database to a test DB running on Amazon RDS using the pgsync gem.

My .pgsync.yml page is simple:

from: postgres://localhost:5432/imports_development?sslmode=require
to: [See attempts below]
exclude:
  - [A few tables]

I have tried many approaches but none of them are working. Here are all approaches and the error messages I've received:

to: postgres://awsuser:[email protected]/postgres?sslca=config/rds-combined-ca-bundle.pem
  => invalid URI query parameter: "sslca"

to: postgres://awsuser:[email protected]
  => connection to server at "52.4.150.10", port 5432 failed: FATAL:  database "awsuser" does not exist

to: $(heroku config:get DATABASE_URL)
  => invalid URI parameter: "sslca"

to: imports-test.abcdefg.us-east-1.rds.amazonaws.com
  => connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "imports-test.abcdefg.us-east-1.rds.amazonaws.com" does not exist

These are the credentials used by database.yml for the RDS database:

rds_db_name: postgres
rds_username: awsuser
rds_password: mypassword
rds_hostname: imports-test.abcdefg.us-east-1.rds.amazonaws.com
rds_port: 5432

I can connect to both databases with rails console, so it should just be a matter of getting the above statements right. What is missing here?

Answer 1

Have you tried postgres://awsuser:[email protected]/postgres ? Attempt no.2 seems to work, but doesn't include the DB name, based on the error.

Roughly the URI is in the format:

postgres://{USER}:{PASS}@{HOSTNAME}/{DBNAME}

The ?sslca=123 and other options AFTER ? (the query string) is meant as options. Not all clients support all options here.