This article was adapted from Rancher Labs

This section describes PostgreSQL and Pgweb

PostgreSQL is an open source database known for its reliability and performance. It is used in many industries and applications, especially by Web developers. PostgreSQL is natively supported by all the major Web frameworks, from Node.js to Django to Rails and Spring, so its applicability to web backend systems across the Internet is fairly widespread.

As with any database, developers need tools to work with them. Pgweb is an open source, Web-based PostgreSQL client. It has a very clean interface that lets you connect to any PostgreSQL instance to browse tables, run queries and export data. For lightweight database work, it is lighter and more convenient than applications such as pgAdmin.

Opening up database ports to the Internet is a terrible idea in terms of security. Developers who need quick access to debug a production database running on a Kubernetes cluster can set up Pgweb in a number of ways to secure the database and maintain its security posture. For example, deploy Pgweb to the same cluster as the database and expose it publicly, while keeping the database port accessible only within the network. Place Pgweb behind the Oauth2 agent for an extra layer of authentication, and then you can access the database from anywhere with super simplicity and super security.

Pgweb can run natively on Linux, macOS, or Windows, but we’ll explore deploying it to your Kubernetes cluster today. In this article, we will deploy the Pgweb image as a POD to our cluster, expose it using the Kubernetes service, deploy PostgreSQL and expose it (but only within the cluster for maximum security), and then connect to the database. Finally, we’ll redeploy everything with Rancher as a workload and see how easy Rancher makes getting your applications up and running.

preparation

To complete the tutorial guide for this article, you need to prepare the following in advance:

  • Rancher Deployment (you can see a quick guide on the website to get up and running Rancher: www.rancher.cn/quick-start…

  • Managed by the Rancher Kubernetes cluster (according to the import documents or configure a cluster: rancher2. Docs. The Rancher. Cn/docs/cluste…

Deploy Pgweb

Create a new file called pgweb.yaml, paste the following content into it, and apply it to deploy your POD and service.

apiVersion: v1
kind: Pod
metadata:
  labels:
    run: pgweb
  name: pgweb
spec:
  containers:
    - image: sosedoff/pgweb
      name: pgweb
      ports:
        - containerPort: 8081
---
apiVersion: v1
kind: Service
metadata:
  labels:
    run: pgweb
  name: pgweb-svc
spec:
  ports:
    - port: 8081
      targetPort: 8081
      protocol: TCP
  type: NodePort
  selector:
    run: pgweb
Copy the code
kubectl apply -f pgweb.yaml
pod/pgweb created
service/pgweb-svc created
Copy the code

Then get the service information and find the node port in use. In this article, it is 31338. Yours may vary, but is generally in the 30,000-32,767 range.

Kubectl get SVC pgweb-svc NAME TYPE cluster-ip external-ip PORT(S) AGE pgweb-svc NodePort 10.96.47.248 <none> 8081:31338/TCP 9sCopy the code

Now enter a public IP address for the node on that port in your browser. Find your PR IP and open the necessary firewall ports according to your Settings. If all goes well, you will see the Pgweb login page.

Port forwarding can also come into play in a pinch if you have problems accessing node ports.

Now we need a database to access.

Deploy PostgreSQL using sample data

Like Pgweb, we will use a YAML manifest to deploy PostgreSQL in Kubernetes. This is a little bit more difficult. We will leave the default ClusterIP service type as we do not want to access the database from outside the cluster. Otherwise, all our security Settings are useless.

The image we are using is a standard PostgreSQL image, but preloaded with sample country data:

Hub.docker.com/r/ghusta/po…

Copy the following into postgres.yaml and apply the manifest:

apiVersion: v1
kind: Pod
metadata:
  labels:
    run: postgres
  name: postgres
spec:
  containers:
    - image: ghusta/postgres-world-db
      name: postgres
      ports:
        - containerPort: 5432
---
apiVersion: v1
kind: Service
metadata:
  labels:
    run: postgres
  name: postgres-svc
spec:
  ports:
    - port: 5432
      targetPort: 5432
      protocol: TCP
  selector:
    run: postgres
Copy the code
kubectl apply -f postgres.yaml
pod/postgres created
service/postgres-svc created
Copy the code

Connect to the database through the cluster DNS

Now go back to our Pgweb login page and log in with the following information:

  • Host: postgres – SVC. Default. SVC. Cluster. The local

  • User name: world

  • Password: world123

  • Database: world-db

  • SSL mode: Disable

Now that you are connected to the database, click “City” in the table on the left:

Notice the host name we use. This is Kubernetes’ internal DNS at work, allowing you to connect to the service by name instead of pod IP. The first part is the service name, which we define in the postgres.yaml file. The second part is the namespace in which your service is deployed — default in this case. Next, the SVC makes it clear that we are using a service name (you can also specify a POD by name). Finally, cluster.local is the default DNS name for the cluster. This architecture makes it easy to wire pods together and take advantage of how services gracefully process duplicates and isolate pods that are offline.

Go back to Pgweb and click “Query” at the top to try to Query New Zealand in the database.

SELECT * FROM city WHERE country_code like 'NZL'
Copy the code

Or query cities that are more than 80 years old:

SELECT * FROM country WHERE life_expectancy > 80
Copy the code

You can spend as much time looking at the data as if you were a developer troubleshooting an application that connects to the database. When you are ready to continue, remove the resource from your cluster to proceed to the next step:

kubectl delete -f pgweb.yaml
kubectl delete -f postgres.yaml
Copy the code

Redeploy using Rancher

Now we will redeploy the same configuration, but this time we will use Rancher to deploy the workload instead of the Kubernetes manifest file.

  • In the [Global] view, open the project to which you want to deploy the workload (the default project will work if you have not created another project).

  • We can import YAML files, but this time let’s try to build our configuration using the GUI all the way, click [Deploy]

  • Name it pgWeb and set the Docker image to soseDOFF/pgWeb.

  • Click Add Port, name it pgWeb-svC and set the container Port to 8080

  • Click Launch and Rancher will deploy your Pod to the cluster

  • Now repeat the above steps for the PostgreSQL pod, give it a proper name and use ghusta/ Postgres-world-db as the mirror. The container port is 5432 and the service type is changed to Cluster IP.

  • Listing your workload on this page, you will see the node port assigned to the Pgweb cluster, use this port to access the Pgweb login page, and log in with the same credentials.

  • Go back to your terminal and run Kubectl Get Pods and Kubectl Get SVC to see the pods and services Rancher deployed.

conclusion

In this article, we’ve looked at the PostgreSQL and Pgweb client databases of choice for Web developers. We first deployed it manually to our Kubernetes cluster using the manifest file, and then redeployed it using Rancher. By comparison, we can see that Rancher eliminates some of the overhead involved in deploying and editing application Pods. Hopefully, you can use the lessons learned from deploying these resources to further practice when setting up other applications to communicate between multiple pods, such as WordPress or custom Web applications.

Accessing Pgweb and connecting to the database on your cluster public IP works the same way as deploying both through the Kubernetes manifest file.