Create new user

psql --host=RDS_HOST --port=5432 --username=RDS_USERNAME --password --dbname=postgres

OR

psql "postgres://RDS_USERNAME:RDS_PASSWORD@RDS_HOST:5432/postgres" -c "CREATE USER app_name_env WITH CREATEDB PASSWORD 'password';"

OR
psql "postgres://RDS_USERNAME:RDS_PASSWORD@RDS_HOST:5432/postgres"


CREATE USER app_name_env WITH CREATEDB PASSWORD 'password';

\q

Create new database

psql --host=RDS_HOST --port=5432 --username=app_name_env --password --dbname=postgres

OR

psql "postgres://app_name_env:password@RDS_HOST:5432/postgres" -c "CREATE DATABASE app_name_env WITH OWNER = 'app_name_env' ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"

OR

psql "postgres://app_name_env:password@RDS_HOST:5432/postgres"

CREATE DATABASE app_name_env WITH OWNER = "app_name_env" ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

\q

Create extensions if necessary

psql --host=RDS_HOST --port=5432 --username=RDS_USERNAME --password --dbname=postgres

OR

psql "postgres://RDS_USERNAME:RDS_PASSWORD@RDS_HOST:5432/postgres" -c "CREATE EXTENSION .... (create the necessary extensions)"

OR

psql "postgres://RDS_USERNAME:RDS_PASSWORD@RDS_HOST:5432/postgres"

CREATE EXTENSION .... (create the necessary extensions)

\q

Stop application

heroku maintenance:on -a app_name_env

heroku ps:scale web=0 -a app_name_env

Stop all conections (if something goes wrong)

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();

Create, download and rename backup to latest.dump

From Heroku

heroku pg:backups capture -a app_name_env

heroku pg:backups public-url -a app_name_env

From RDS (Amazon)

 pg_dump --format=custom --host=RDS_HOST --port=5432 --username=RDS_USERNAME --password --dbname=app_name_env --file=latest.dump

OR

 pg_dump --format=custom "postgres://RDS_USERNAME:RDS_PASSWORD@RDS_HOST:5432/postgres" --file=latest.dump

Restore .dump

pg_restore -O --schema=public --host=RDS_HOST --port=5432 --username=app_name_env --password --dbname=app_name_env latest.dump

OR

 pg_restore -O --schema=public -d "postgres://app_name_env:password@RDS_HOST:5432/app_name_env" latest.dump

Add ENV_VARS with new connection string

heroku config:add RDS_POSTGRESQL=postgres://app_name_env:password@RDS_HOST:5432/app_name_env -a app_name_env

heroku config:add DATABASE_URL=postgres://app_name_env:password@RDS_HOST:5432/app_name_env -a app_name_env

Start application

heroku maintenance:off -a app_name_env

heroku ps:scale web=1 -a app_name_env

Source

https://reinteractive.net/posts/128-heroku-app-backed-by-an-aws-rds-postgres-database