/
Infrastructure
Infrastructure
PostgreSQL
Default Helm Chart
https://github.com/helm/charts/tree/master/stable/postgresql
ubuntu@ip-172-31-27-4:~$ sudo helm install --name pgstg stable/postgresql NAME: pgstg LAST DEPLOYED: Sat Apr 27 21:51:58 2019 NAMESPACE: default STATUS: DEPLOYED RESOURCES: ==> v1/Secret NAME TYPE DATA AGE pgstg-postgresql Opaque 1 0s ==> v1/Service NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE pgstg-postgresql-headless ClusterIP None <none> 5432/TCP 0s pgstg-postgresql ClusterIP 10.43.163.107 <none> 5432/TCP 0s ==> v1beta2/StatefulSet NAME DESIRED CURRENT AGE pgstg-postgresql 1 1 0s ==> v1/Pod(related) NAME READY STATUS RESTARTS AGE pgstg-postgresql-0 0/1 Pending 0 0s NOTES: ** Please be patient while the chart is being deployed ** PostgreSQL can be accessed via port 5432 on the following DNS name from within your cluster: pgstg-postgresql.default.svc.cluster.local - Read/Write connection To get the password for "postgres" run: export POSTGRES_PASSWORD=$(kubectl get secret --namespace default pgstg-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode) To connect to your database run the following command: kubectl run pgstg-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:10.7.0 --env="PGPASSWORD=$POSTGRES_PASSWORD" --command -- psql --host pgstg-postgresql -U postgres To connect to your database from outside the cluster execute the following commands: kubectl port-forward --namespace default svc/pgstg-postgresql 5432:5432 & PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U postgres describe pod shows Warning FailedScheduling 21s (x6 over 3m42s) default-scheduler pod has unbound immediate PersistentVolumeClaims Workaround: Modify underlying yaml files to use a persistent volume with ReadWriteMany access K8s only it clone https://github.com/helm/charts.git sudo helm install postgresql --name pg ubuntu@ip-172-31-27-4:~/charts/stable$ helm delete pg release "pg" deleted ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pv --all-namespaces No resources found. ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pvc --all-namespaces NAMESPACE NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE default data-pg-postgresql-0 Pending 4m48s default data-pgstg-postgresql-0 Pending 14h default data-wishful-skunk-postgresql-0 Pending 13m ubuntu@ip-172-31-27-4:~/charts/stable$ vi pg-pv.yaml apiVersion: v1 kind: PersistentVolume metadata: name: nfs-serv-prov-nfs-server-provisioner-0 spec: capacity: storage: 200Gi accessModes: - ReadWriteOnce hostPath: path: /srv/volumes/nfs-serv-prov-nfs-server-provisioner-0 claimRef: namespace: kube-system name: nfs-serv-prov-nfs-server-provisioner-0 ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl apply -f pg-pv.yaml persistentvolume/nfs-serv-prov-nfs-server-provisioner-0 created ubuntu@ip-172-31-27-4:~/charts/stable$ helm delete --purge pg release "pg" deleted sudo helm install postgresql --name pg ubuntu@ip-172-31-27-4:~/charts/stable$ helm delete --purge pg release "pg" deleted ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pvc --all-namespaces NAMESPACE NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE default data-pg-postgresql-0 Pending 7m23s ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl delete pvc data-pg-postgresql-0 persistentvolumeclaim "data-pg-postgresql-0" deleted change storage-class from - to nfs-provisioner ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pvc --all-namespaces NAMESPACE NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE default data-pg-postgresql-0 Pending nfs-provisioner 7s follow https://severalnines.com/blog/using-kubernetes-deploy-postgresql ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl create -f postgres-storage.yaml persistentvolume/postgres-pv-volume created persistentvolumeclaim/postgres-pv-claim created ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pv --all-namespaces NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE nfs-serv-prov-nfs-server-provisioner-0 200Gi RWO Retain Available kube-system/nfs-serv-prov-nfs-server-provisioner-0 10m postgres-pv-volume 5Gi RWX Retain Bound default/postgres-pv-claim manual 23s ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get pvc --all-namespaces NAMESPACE NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE default postgres-pv-claim Bound postgres-pv-volume 5Gi RWX manual 32s ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl create -f postgres-deployment.yaml deployment.extensions/postgres created ubuntu@ip-172-31-27-4:~$ kubectl get pods --all-namespaces NAMESPACE NAME READY STATUS RESTARTS AGE default nfs-serv-prov-nfs-server-provisioner-0 1/1 Running 0 26m default postgres-78f78bfbfc-pw4zp 1/1 Running 0 22s ubuntu@ip-172-31-27-4:~/charts/stable$ vi postgres-service.yaml ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl create -f postgres-service.yaml service/postgres created ubuntu@ip-172-31-27-4:~/charts/stable$ kubectl get svc postgres NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE postgres NodePort 10.43.57.215 <none> 5432:30170/TCP 25s ubuntu@ip-172-31-27-4:~/charts/stable$ sudo apt install postgresql-client-common ubuntu@ip-172-31-27-4:~/charts/stable$ sudo apt-get install postgresql-client ubuntu@ip-172-31-27-4:~/charts/stable$ psql -h localhost -U postgresadmin --password -p 30170 postgresdb Password for user postgresadmin: psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1), server 10.4 (Debian 10.4-2.pgdg90+1)) Type "help" for help. postgresdb-# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------+----------+----------+------------+------------+----------------------- postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgresdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) dump ubuntu@ip-172-31-27-4:~/charts/stable$ pg_dump -h localhost -U postgresadmin -p 30170 -W -F t postgresdb Password: woc.dat0000600 0004000 0002000 00000003034 13461323457 0014447 0ustar00postgrespostgres0000000 0000000 PGDMP postgresdb10.4 (Debian 10.4-2.pgdg90+1)#10.7 (Ubuntu 10.7-0ubuntu0.18.04.1) 0ENCODINENCODINGSET client_encoding = 'UTF8'; false 0 STDSTRINGS STDSTRINGS(SET standard_conforming_strings = 'on'; false 00 SEARCHPATH SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false); false 126216384 postgresdDATABASEzCREATE DATABASE postgresdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8'; DROP DATABASE postgresdb; postgresfalse26152200publicSCHEMACREATE SCHEMA public; DROP SCHEMA public; postgresfalse SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema'; postgresfalse3307912980plpgsql EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; DROP EXTENSION plpgsql; false 00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; false1restore.sql0000600 0004000 0002000 00000002333 13461323457 0015375 0ustar00postgrespostgres0000000 0000000 -- -- NOTE: -- -- File paths need to be edited. Search for $$PATH$$ and -- replace it with the path to the directory containing -- the extracted data files. -- -- -- PostgreSQL database dump -- -- Dumped from database version 10.4 (Debian 10.4-2.pgdg90+1) -- Dumped by pg_dump version 10.7 (Ubuntu 10.7-0ubuntu0.18.04.1) SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodie