一、需要创建的yaml文件

config.yml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: postgres
  labels:
    app: postgres
data:
  POSTGRES_DB: master
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: postgres

PersistentVolume.yml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-pv
spec:
  capacity:
    storage: 2Gi
  accessModes:
    - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  nfs:
    server: 192.168.11.210
    path: "/nfs/postgre-pv"

StatefulSet.yml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: postgres
spec:
  serviceName: "postgres"
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:9.5
          envFrom:
            - configMapRef:
                name: postgres-config
          ports:
            - containerPort: 5432
              name: postgredb
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
              subPath: postgres
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 2Gi

Service.yml

apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: postgres
  labels:
    app: postgres
spec:
  ports:
    - port: 5432
      name: postgres
  type: LoadBalancer
  selector:
    app: postgres

$ kubectl create ns postgres
$ kubectl create -f xxxx
$ kubectl get service -n postgres
NAME       TYPE           CLUSTER-IP    EXTERNAL-IP      PORT(S)          AGE
postgres   LoadBalancer   10.43.81.59   192.168.11.210   5432:31919/TCP   40s

常用测试命令：
首先可以写两个脚本能在pod外面执行命令在数据库
进入数据的脚本

[root@master bin]# cat gplogin.sh
#/bin/bash
ns=$1
namespace=$2
login(){

kubectl exec -it -n $namespace $(kubectl get pods -n $namespace --show-labels | grep 'master' | awk '{print $1}') -- psql -U postgres;
echo "$?"
}

然后可以使用掉脚本执行的命令

[root@master bin]# cat querygp.sh
#psql -U postgres
sh gplogin.sh << END_SCRIPT
在数库可以执行的命令
END_SCRIPT

常用命令

## 一、查询db数据库中各数据库占用的磁盘空间大小

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

## 二、查询db库中每个表占用的磁盘空间大小

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC


三、创建测试表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);


四、向测试表中写数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

































插入数据：：


create table lat_longs 
(
  c1 bigint generated always as identity,
  c2 float
)

insert into lat_longs(c2)
select random() * 100
from generate_series(1,10e6) as g(id);

insert into lat_longs(c2)
select random() * 100
from generate_series(1,10e6) as g(id);