postgresql_postgresql localdate-优快云博客

一、资料

文档：https://www.runoob.com/postgresql/postgresql-tutorial.html

二、整理

1.Docker快速搭建环境

docker run -d --name postgres -e POSTGRES_PASSWORD=123456 -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432 --restart always postgres:latest

2.常用操作

su postgres

# 执行sql脚本，-f指定外部脚本文件

# -W密码强制提示
psql -U postgres -W
# 指定数据库
psql -U astp -d astpdb

3.Java数据类型对应Postgresql数据类型关系

序号	java8	postgreSQL
1	LocalDate	date
2	LocalTime	time
3	LocalDateTime	timestamp without timezone
4	OffsetDateTime	timestamp with timezone
5	String	varchar
6	String	text
7	Integer	int2
8	Integer	int4
9	Long	int8
10	Float	float4
11	Double	float8
12	BigDecimal	numeric
13	Boolean	bool

4.常用命令

CREATE DATABASE dbname;

createdb -E utf8 -U user

# 列举数据库
\l

# 进入数据库
\c xxx

# 查看表格
\d
\d tablename

5.PostgreSQL配置优化

https://cloud.tencent.com/developer/article/2311555
https://support.huawei.com/enterprise/zh/doc/EDOC1100283327?section=j00d

6.备份

# 案例 Fc为二进制格式，可被pg_resotre用于精细还原
pg_dump -U astp -c -C -Fc -f astpdb.dump astpdb
pg_dumpall -U astp -W -f db_bak.sql
psql -U postgres -d astpdb -Ft -f postgres_backup.sql
# pg_restore
pg_restore -U postgres --no-owner --role astp -d astpdb postgres_back.dump

# 单库备份
pg_dump –h 127.0.0.1 -p 5432 -U postgres -c -C –f dbname.sql dbname

# 全量备份
pg_dumpall –h 127.0.0.1 –p 5432 -U postgres –f db_bak.sql

# 恢复
psql –h 127.0.0.1 -p 5432 -U postgres –f db_bak.sql


# pg_dump 常用参数的说明：
-h host 或 --host=host：指定数据库服务器主机名或 IP 地址，默认值是本地主机（localhost）。
-p port 或 --port=port：指定 PostgreSQL 服务器监听的端口号，默认通常是 5432。
-U username 或 --username=username：指定连接数据库所使用的用户名。
-W 或 --password：要求用户输入数据库密码。如果不使用这个选项，pg_dump 将尝试以无密码方式连接，或者使用操作系统认证（如 ~/.pgpass 文件中的密码）。
-d dbname 或 --dbname=dbname：要备份的数据库名称。
-F format 或 --format=format：指定输出格式，可以是 p (plain text), c (custom), d (directory) 或 t (tar archive)，默认是 plain text 格式。
-f filename 或 --file=filename：指定备份文件的名称，如果省略，则输出到标准输出。
-a 或 --data-only：只导出数据，不包括表结构和其他模式对象。
-s 或 --schema-only：只导出模式定义，不包括数据。
-c 或 --clean：在脚本开头添加删除现有数据库对象的命令，使得导入后会先清除目标数据库中已存在的相关对象再进行导入。
-C：与 -c 类似，但在每个表的数据之前和之后插入 TRUNCATE 和 DELETE 命令，而不是 DROP 命令。
-v 或 --verbose：详细模式，输出更多的处理信息。
-Z level 或 --compress=level：启用压缩，可以指定压缩级别（1-9），默认不启用压缩。
-j jobs 或 --jobs=jobs（仅在目录格式 -F d 下有效）：并行导出多个表，加快备份速度。

# pg_dumpall 常用参数的说明：
-h host 或 --host=host：指定 PostgreSQL 服务器主机名或 IP 地址，默认值是本地主机。
-p port 或 --port=port：指定 PostgreSQL 服务器监听的端口号，默认通常是 5432。
-U username 或 --username=username：指定连接数据库所使用的用户名。
-W 或 --password：要求用户输入数据库密码。如果不使用此选项，则尝试无密码连接或者通过操作系统认证获取密码。
-f filename 或 --file=filename：指定输出的 SQL 脚本文件名称，如果省略，则输出到标准输出。
-g 或 --globals-only：仅导出全局对象，如角色和表空间定义，不包含任何数据库的数据和模式信息。
-a 或 --data-only：仅导出数据，而不包含创建数据库、角色和其他全局对象的命令。
-s 或 --schema-only：与 -g 类似，但只针对单个数据库，导出每个数据库的模式定义而不包含数据。
-v 或 --verbose：详细模式，输出更多的处理信息。
-x 或 --no-privileges：不导出权限相关的 SQL 语句。
-O 或 --no-owner：在生成的脚本中禁用对象的所有权设置。

# pg_restore --help           可以查看用法
-U, --username=NAME     以指定的数据库用户联接
-O, --no-owner                  跳过对象所有权的恢复
-d, --dbname=DBNAME   指定数据库名
-v, --verbose                      详细信息模式
--role=ROLENAME            在恢复之前执行SET ROLE命令

7.k8s环境备份postgresql方案

crontab -e 设置定时任务
0 0 * * * /opt/k8s_bak/backup_pg.sh > /dev/null 2>&1

#!/bin/bash

# 定义数据库连接信息和标签
NAMESPACE="infra"
DB_USER="astp"
DB_NAME="astpdb"
BACKUP_FILE="postgres_backup.dump"
POD_LABEL_SELECTOR="app=postgres"
BACKUP_PATH="/opt/k8s_bak/"

# 获取Pod名称
POSTGRES_POD=$(kubectl get pods -n $NAMESPACE -l "$POD_LABEL_SELECTOR" -o jsonpath='{.items[0].metadata.name}')

# 执行pg_dump命令进行备份
kubectl exec $POSTGRES_POD -n $NAMESPACE -- pg_dump -U $DB_USER -c -C -Fc -f $BACKUP_FILE $DB_NAME
kubectl cp $POSTGRES_POD:$BACKUP_FILE $BACKUP_PATH$BACKUP_FILE -n $NAMESPACE
echo "Save PostgreSQL backup file in $BACKUP_PATH$BACKUP_FILE"
kubectl exec $POSTGRES_POD -n $NAMESPACE -- rm -rf $BACKUP_FILE

echo "PostgreSQL backup completed at $(date)"

# pg_restore
pg_restore -U postgres --no-owner --role astp -d astpdb postgres_back.dump

三、k8s部署

https://zhuanlan.zhihu.com/p/584605185

创建动态持久卷存储

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgresql-disk
  namespace: default
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Gi
  storageClassName: alicloud-disk-available
  volumeMode: Filesystem

创建PostgresSQL配置文件

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
  namespace: foundation
data:
  POSTGRES_DB: postgresdb
  POSTGRES_USER: postgresadmin
  POSTGRES_PASSWORD: xxxxxx
  PGDATA: /var/lib/postgresql/data/pgdata

创建PostgresSQL工作负载

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgresql
  namespace: default
  labels:
    app: postgresql
spec:
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: postgresql
  template:
    metadata:
      labels:
        app: postgresql
    spec:
      containers:
        - image: registry.cn-beijing.cr.aliyuncs.com/postgres:15.1
          name: postgresql
          ports:
            - containerPort: 5432
              name: postgresql
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - name: postgresql-disk
              mountPath: /var/lib/postgresql/data
      volumes:
        - name: postgresql-disk
          persistentVolumeClaim:
            claimName: postgresql-disk
      imagePullSecrets:
        - name: aliyun-docker-registry

创建服务

# ClusterIP用于K8s集群内部连接
apiVersion: v1
kind: Service
metadata:
  labels:
    app: postgresql
  name: postgresql-svc
  namespace: default
spec:
  ports:
    - name: postgresql
      port: 5432
      protocol: TCP
      targetPort: 5432
  selector:
    app: postgresql
  type: ClusterIP

# Nodeport用于本地连接
apiVersion: v1
kind: Service
metadata:
  labels:
    app: postgresql
  name: postgresql-nodeport
  namespace: foundation
spec:
  ports:
    - name: postgresql
      nodePort: 32432
      port: 5432
      protocol: TCP
      targetPort: 5432
  selector:
    app: postgresql
  type: NodePort