准备工作

创建postgres用户组及用户，创建数据目录和备份目录，用于Docker数据持久化的目录映射

groupadd postgres
useradd -g postgres postgres
mkdir -p /ssndisk/pgdata/data
mkdir -p /ssndisk/pgdata/backup/archivedir
mkdir -p /ssndisk/pgdata/backup/data
chown -R postgres:postgres /ssndisk/pgdata/data
chown -R postgres:postgres /ssndisk/pgdata/backup

创建网络

docker network create ssn-network

提示

如已创建过则不需要重复创建

使用timescale/timescaledb-ha:pg14-latest镜像创建容器并初始化PG的密码

docker run \
-d \
-p 25902:5432 \
-e POSTGRES_PASSWORD=****************** \
-e TZ=Asia/Shanghai \
-v /ssndisk/pgdata/data:/home/postgres/pgdata/data \
-v /ssndisk/pgdata/backup:/home/postgres/pgdata/backup \
--name ssn-pgsql-db \
--network ssn-network \
--restart=always \
--health-cmd="pg_isready -p 5432" \
--health-interval=10s \
--health-retries=10 \
--health-timeout=5s \
timescale/timescaledb-ha:pg14-latest

修改PG配置文件

vi /ssndisk/pgdata/data/postgresql.conf

• 按需修改最大连接数：max_connections = 500
• 配置配置PG热备份：wal_level = replica
• 配置配置PG热备份：archive_mode = on
• 配置配置PG热备份：archive_command = 'cp %p /home/postgres/pgdata/backup/archivedir/%f'

创建数据库新用户并对指定的数据库进行授权

CREATE USER usriot WITH PASSWORD '***************';
GRANT ALL PRIVILEGES ON DATABASE iotdb TO usriot;
GRANT USAGE ON SCHEMA public TO usriot;
GRANT ALL PRIVILEGES ON ALL TABLES in SCHEMA public TO usriot;
GRANT ALL PRIVILEGES ON ALL sequences in SCHEMA public TO usriot;
GRANT select,insert,update,delete ON ALL TABLES in SCHEMA public TO usriot;
GRANT ALL ON SCHEMA public TO usriot;

创建基础备份

$ docker exec -it ssn-pgsql-db /bin/bash
$ pg_basebackup -D /home/postgres/pgdata/backup/data/20240715 -Ft -Pv -U postgres -W -R
Passwrod:

创建数据库WEB管理的容器

docker run \
-d \
-p 25900:8080 \
--name ssn-adminer \
--network ssn-network \
--restart=always \
adminer

提示

如已创建过则不需要重复创建

WEB管理

管理地址: http://ip:25900