2020-04-27
Motivation
This article is a hands-on guide on how to create and operate the Heroku PostgreSQL database.
Goals
- Setup and config Heroku addon that supports PostgreSQL operation
- Implement basic user management (CURD) for the bdmhero project.
References
- Heroku Postgres: https://devcenter.heroku.com/articles/heroku-postgresql
- Install PostgreSQL on Mac https://postgresapp.com
Steps
- Create DB plan for PostgreSQL (DB provision process)
Command to display all created addons
heroku addons
Add-on Plan Price State
────────────────────────────────────────── ───────── ───── ───────
heroku-postgresql (postgresql-rigid-16229) hobby-dev free created
└─ as DATABASE
The table above shows add-ons and the attachments to the current app (bdmhero) or other apps.
Command to create PostgreSQL if it's not available yet.
heroku addons:create heroku-postgresql:hobby-dev
Creating heroku-postgresql:hobby-dev on ⬢ bdmhero... free
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy
Created postgresql-rigid-16229 as DATABASE_URL
Use heroku addons:docs heroku-postgresql to view documentation
Command to check DATABASE_URL, as well as other defined configs
heroku config
=== bdmhero Config Vars
DATABASE_URL: postgres://hxgombqjtymuoh:bc403c8a4db1581bf339672b93911da6170872c052fb4cde8e94574ef942d7e2@ec2-54-88-130-244.compute-1.amazonaws.com:5432/dkak1glce3uq8
MYSQL_DOCKER_CONTAINER_NAME: bdhero
MYSQL_HOST_ADDR: 192.168.7.169
MYSQL_HOST_DB_NAME: dkak1glce3uq8
MYSQL_HOST_PORT: 5432
MYSQL_HOST_PSWD: bc403c8a4db1581bf339672b93911da6170872c052fb4cde8e94574ef942d7e2
MYSQL_HOST_USER: hxgombqjtymuoh
......
Command to check Postgres DB info
heroku pg:info
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
Connections: 0/20
PG Version: 12.2
Created: 2020-04-27 11:20 UTC
Data Size: 7.9 MB
Tables: 0
Rows: 0/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
Add-on: postgresql-rigid-16229
Running Postgre Locally
- Install PostgreSQL on Mac
- Export DATABASE_URL locally
export DATABASE_URL=postgres://$(whoami)
- Establish a PostgreSQL session with the remote database
heroku pg:psql
➜ cloudme git:(gs-accessing-data-mysql) ✗ heroku pg:psql
--> Connecting to postgresql-rigid-16229
psql (12.1, server 12.2 (Ubuntu 12.2-2.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
