How to Connect to a PSQL database deployed in a Kubernetes cluster?

Sujeet Raman

I have a local .NET Core application for a web API, and my PostgreSQL (psql) database is deployed in a Kubernetes cluster, specifically for my development environment. As per the requirements, I cannot install the database locally. Now, I need to test my API's POST code, which utilizes Entity Framework (EF), by checking if tables are created and data is saved in the database. I have obtained the connection string from the cluster, which is as follows:

Host=postgres-new-postgresql-helm.database-new.svc.cluster.local; Port=54000; Username=admin; Password=admin; Database=db; SearchPath=public;

I would like to connect to this database from my local machine and use pgAdmin4 to view the changes in the development cluster-deployed database. Specifically, I want to see the tables created and verify if the data is being saved correctly. Could you please guide me on how to establish a connection to this database using pgAdmin4 and visualize the changes made in the development cluster-deployed database? I would like to use the graphical user interface (GUI) provided by pgAdmin4 for this purpose.

Thank you in advance for your assistance.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Brahma Prakash Shukla3y ago
Accepted answer
Hi Sujeet,

To enable your local machine to access the Kubernetes cluster where the database is deployed, you need to set up the necessary network connectivity and configure the Kubernetes resources. Here are the steps you can follow:
1. Expose the PostgreSQL service:
  
  In your Kubernetes cluster, you need to expose the PostgreSQL service to make it accessible from outside the cluster.
  
  Determine the Kubernetes service associated with your PostgreSQL deployment. You can use the following command to list the services:
  kubectl get services
  
  Identify the service corresponding to your PostgreSQL deployment.
  
  Expose the service using a Kubernetes service type that allows external access, such as LoadBalancer or NodePort. For example, to expose the service using a LoadBalancer type, you can run the following command:
  kubectl expose service --type=LoadBalancer --name=
  
  Replace with the name of your PostgreSQL service and with the desired name for the externally accessible service.
  
  Obtain the external IP or domain name:
  
  After exposing the service, you need to obtain the external IP or domain name to connect from your local machine.
  
  Use the following command to check the status of the service and obtain the external IP or domain name:
  kubectl get services
  
  Look for the "EXTERNAL-IP" or "LOAD-BALANCER-IP" value in the output. If an external IP is not assigned immediately, you may need to wait a few moments for the IP to be provisioned.
  
  Update local machine's hosts file (if necessary):
  
  If the external IP obtained in the previous step is not a domain name but an IP address, you can update your local machine's hosts file to associate a hostname with that IP.
  
  Open the hosts file on your local machine (located at /etc/hosts on Unix-like systems or C:\Windows\System32\drivers\etc\hosts on Windows) using a text editor with administrative privileges.
  
  Add an entry that maps the external IP to a hostname. For example:
  my-postgres-cluster
  
  Replace with the actual IP obtained in the previous step and my-postgres-cluster with the desired hostname.
  
  Connect from local machine:
  
  With the exposed service and external IP or domain name configured, you can now connect to the PostgreSQL database from your local machine using the provided connection string.
  
  In your .NET Core application's connection string, update the "Host" value with the external IP or domain name obtained in step 2.
  
  Save the changes to the connection string and start your .NET Core application.
  
  Now, your local machine can connect to the PostgreSQL database deployed in the Kubernetes cluster.
  
  By following these steps, you should be able to establish the necessary network connectivity between your local machine and the Kubernetes cluster. Remember to ensure that your cluster's network policies and firewall rules allow inbound connections from your local machine to the exposed PostgreSQL service.
Tuhin Paul3y ago
1. If your PostgreSQL deployment is in a specific namespace within the Kubernetes cluster, you'll need to provide the namespace when running the kubectl commands.
```
kubectl get services -n 
```
2. Before exposing the service, it's essential to verify the service type associated with your PostgreSQL deployment. The service type determines how the service will be exposed.
```
kubectl get service 
```
3. Specify the port number along with the external IP or domain name when connecting to the PostgreSQL database.
Sujeet Raman3y ago
i want to know this step-local machine can access the Kubernetes cluster where the database is deployed and that the necessary network connectivity is established.what needs to be done from cluster
Brahma Prakash Shukla3y ago
To connect to your PostgreSQL database deployed in a Kubernetes cluster using pgAdmin4 and visualize the changes, you can follow these steps:

Step 1: Install pgAdmin4
- Download and install pgAdmin4 on your local machine from the official pgAdmin website: https://www.pgadmin.org/download/.
Step 2: Launch pgAdmin4
- Once installed, launch pgAdmin4. This will open a web-based interface in your default web browser.
Step 3: Add a new server
- In the pgAdmin4 web interface, click on the "Add New Server" button. This button is usually located on the top-left side of the interface.
Step 4: Configure the server connection
- In the "Create - Server" dialog, provide a name for the server in the "Name" field (e.g., "Development Cluster").
- Switch to the "Connection" tab.
- In the "Host name/address" field, enter the hostname of your PostgreSQL database, which is postgres-new-postgresql-helm.database-new.svc.cluster.local in your case.
- Set the "Port" field to 54000.
- Enter your PostgreSQL username and password in the "Username" and "Password" fields, respectively. In your case, the username is admin and the password is admin.
- Set the "Database" field to db.
- Optionally, you can provide a name for the "Service" field to help identify this connection.
- Click on the "Save" button to save the server configuration.
Step 5: Connect to the server
- After saving the server configuration, the newly created server should appear in the left sidebar of the pgAdmin4 interface.
- Click on the server name to connect to the database. This will open a new tab/window with the database details.
Step 6: Explore the database
- In the connected database tab, you can expand the tree view on the left sidebar to navigate through the database schema.
- To view the tables created, expand the "Schemas" section, then expand the "public" schema, and click on the "Tables" node. This will display all the tables present in the database.
- You can right-click on a table and select "View/Edit Data" to see the data stored in that table.
By following these steps, you should be able to connect to your PostgreSQL database deployed in the development cluster using pgAdmin4 and visualize the tables and data. Make sure that your local machine can access the Kubernetes cluster where the database is deployed and that the necessary network connectivity is established.