Accessing Postgres RDS from Kubernetes cluster in AWS

Question

My Kubernetes cluster setup has n-tier web application running in dev and test environments on AWS. For the production environment, postgres RDS was chosen, to ensure periodic backup. While creating a postgres RDS instance, kubernetes-vpc was selected for db-subnet to keep networking stuff simple during pilot run. Also, security group selected is the same as kubernetes-minions.

Following is the service and endpoint yaml:

apiVersion: v1
kind: Service
metadata: 
  labels: 
    name: pgsql-rds
  name: pgsql-rds
spec: 
  ports: 
    - port: 5432
      protocol: TCP
      targetPort: 5432

--

apiVersion: v1
kind: Endpoints
metadata:
  name: pgsql-rds
subsets:
- addresses:
  - ip: 52.123.44.55
  ports:
  - port: 5432
    name: pgsql-rds
    protocol: TCP

When web-app service and deployment is created, it's unable to connect to RDS instance. The log is as follows:

java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: Connection to pgsql-rds:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

What am I missing? any pointers to resolve the issue appreciated.

Answer 1

It's been a while the issue was resolved.
Don't exactly remember now, which step I missed that caused connection problem. But, below are the steps that did work for me.

Pre-requisite: kubernetes cluster is set up with vpc ('k8s-vpc')

1. Create VPC SUBNET
   Go to vpc dashboard, ensure same aws region as k8s minion. (you will see existing 'k8s-vpc')
   Create subnet with each availability zone.
   Select 'k8s-vpc' as vpc from drop-down.
   CIDR could be 172.20.16.0/24 or 172.20.32.0/24

2. Create DB SUBNET and SUBNET GROUP FOR VPC of k8s minion if not already available.
   Go to RDS Dashboard.
   Create subnet group (e.g. my-db-subnet-group) for DB and add all subnet from step 1 to create subnet group.

3. From RDS Dashboard create Parameter Group
   (e.g. my-db-param-group) for Postgres (version 9.5 in this example)
   Copy value for max_connections to the max_prepared_transactions field and save

4. Create RDS instance for Postgres DB
   Launch DB instance -> select Engine Postgres -> Choose stage (Production or Dev/Test) -> Give instance spec.s (instance type & disk space etc.) and specify DB settings (user/password) -> Configure Advanced settings

   1. vpc selection as 'k8s-vpc'
   2. DB subnet should be one created in previous step (my-db-subnet-group)
   3. VPC security group should be from Kubernetes minions - so that no additional config. required for access from minions
   4. Select Publicly Accessible - to connect to postgres from internet
   5. Select Parameter Group as 'my-db-param-group'.
   6. Specify Database options, backup and maintenance options and finally launch the instance

5. Also check security group of VPC and add inbound rule to allow connection to postgres port.

6. You can test connection from one of the k8s pod (kubectl exec -it) where postgres client is installed.
   Make sure to change user to postgres.
   Connect to RDS using psql as shown below:
   $ psql --host=my-rds-dev.cyhi3va0y2or.ap-northeast-1.rds.amazonaws.com --port=5432 --username=<masterUserName> --password --dbname=<masterDB>

If everything is set up correctly, it should prompt you for password of db user.
Providing correct password will finally connect to RDS.

This article was of great help.