用kubernetes起个PostgreSQL服务器

CrackingOysters 

前言

本文是使用kubernetes来恢复一个PostgresSQL备份文件的过程。先记录下来，有时间再整理一下。

需求

我有一个PostgreSQL的backup file。第一次restore 失败了，报了如下的错误，

pg_restore: [archiver] unsupported version (1.14) in file header

这一看，应该就是版本不对。应该用对应的版本来restore。

解决思路

于是我就想着具体的版本我也不知道，所以应该要装一个新版本PostgreSQL来试试，但是装哪个版本呢？

最近在学习kubernetes，那么就拿kubernetes来试试，这样子什么版本那不是随便装，不会怕破坏自己的电脑。

首先需要创建一个deployment来运行PostgresSQL，yaml如下，

apiVersion: apps/v1
kind: Deployment
metadata:
  name: md-postgres
  labels:
    app: md
spec:
  selector:
    matchLabels:
      app: md
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: md
    spec:
      containers:
      - image: postgres:12.10
        name: postgres
        env:
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-pass
              key: password
        ports:
        - containerPort: 5432
          name: postgres
      #   volumeMounts:
      #   - name: postgres-persistent-storage
      #     mountPath: /var/lib/postgresql/data
      # volumes:
      # - name: postgres-persistent-storage
      #   persistentVolumeClaim:
      #     claimName: postgres-pv-claim

注：我发现开始restore还是把persistent volume注释掉更好，这样我可以先试验出可以work的版本，然后再把persistent volume加上

创建一个service把这个deployment暴露在当前机器的端口，这样可以通过访问当前的ip和端口来访问。

所以使用NodePort，

apiVersion: v1
kind: Service
metadata:
  labels:
    app: md
  name: md-postgres-svc
spec:
  ports:
  - port: 5432
    protocol: TCP
    targetPort: 5432
  selector:
    app: md
  type: NodePort

创建相应的persistent volume,

apiVersion: v1
kind: PersistentVolume
metadata:
  name: task-pv-volume
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/mnt/postgres/data"

创建相应的persistent volume claim

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pv-claim
  labels:
    app: md
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

创建对应的secret来存密码

apiVersion: v1
data:
  password: <password base64>
kind: Secret
metadata:
  name: postgres-pass

本机测试能不能连上PostgreSQL代码

import psycopg2
class DB:
    def __init__(self, host, port, db_name, user, password):
        self.host = host
        self.db_name = db_name
        self.user = user
        self.port = port
        self.password = password
        self.conn = psycopg2.connect(f"host={self.host} port={self.port} dbname={self.db_name} user={self.user} password={self.password}")
    def insert_one(self):  
        try:
            with self.conn.cursor() as curs:
                curs.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "hi world"))
            self.conn.commit()
        except Exception as e:
            print(f'cannot insert with exception {e}')
    def create_table(self):
        try:
            with self.conn.cursor() as curs:
                curs.execute('CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);')
        except Exception as e:
            print(f'create table meets exception {e}')
    def select(self):
        try:
            with self.conn.cursor() as curs:
                curs.execute('select * from test;')
                print(curs.fetchone())
        except Exception as e:
            print(f'select meets exception {e}')
g_db = DB('localhost', '32339', 'postgres', 'postgres', 'password')