一、资料
文档: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