大数据应用方案1-Postgresql

services:
  pg-0:
    image: bitnami/postgresql-repmgr:latest
    container_name: pg-0
    network_mode: host
    ports:
      - 5432
    volumes:
      - /data0/Server/Db/pq-repmgr:/bitnami/postgresql
      - /data0/Server/Temp/pq-repmgr:/tmp
    environment:
      - POSTGRESQL_POSTGRES_PASSWORD=星星
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=星星
      - POSTGRESQL_DATABASE=postgres
      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=1
      - REPMGR_PASSWORD=星星
      - REPMGR_PRIMARY_HOST=pg-0
      - REPMGR_PRIMARY_PORT=5432
      - REPMGR_PARTNER_NODES=pg-0:5432,pg-1:5432
      - REPMGR_NODE_NAME=pg-0
      - REPMGR_NODE_NETWORK_NAME=pg-0
      - REPMGR_PORT_NUMBER=5432
    restart: always
    extra_hosts:
      - "pg-0:192.168.0.130"
      - "pg-1:192.168.0.131"

2.2.3 pg-1

services:
  pg-1:
    image: bitnami/postgresql-repmgr:latest
    container_name: pg-1
    network_mode: host
    ports:
      - 5432
    volumes:
      - /data0/Server/Db/pq-repmgr:/bitnami/postgresql
      - /data0/Server/Temp/pq-repmgr:/tmp
    environment:
      - POSTGRESQL_POSTGRES_PASSWORD=星星
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=星星
      - POSTGRESQL_DATABASE=postgres
      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=1
      - REPMGR_PASSWORD=星星
      - REPMGR_PRIMARY_HOST=pg-0
      - REPMGR_PRIMARY_PORT=5432
      - REPMGR_PARTNER_NODES=pg-0:5432,pg-1:5432
      - REPMGR_NODE_NAME=pg-1
      - REPMGR_NODE_NETWORK_NAME=pg-1
      - REPMGR_PORT_NUMBER=5432
    restart: always
    extra_hosts:
      - "pg-0:192.168.0.130"
      - "pg-1:192.168.0.131"

2.2.4 pgpool

加入配置 REPMGR_PRIMARY_HOST=pg-0 （即主服务），则读写都可以了，否则是只读模式, 当通过连接pgpool删减或增加表时，自动同步到pg-0与pg-1

services:
  pgpool:
    image: bitnami/pgpool:latest
    container_name: "pgpool"
    ports:
      - 9999:5432
    environment:
      - PGPOOL_BACKEND_NODES=0:pg-0:5432,1:pg-1:5432
      - PGPOOL_SR_CHECK_USER=repmgr
      - PGPOOL_SR_CHECK_PASSWORD=星星
      - PGPOOL_ENABLE_LDAP=no
      - PGPOOL_POSTGRES_USERNAME=postgres
      - PGPOOL_POSTGRES_PASSWORD=星星
      - PGPOOL_ADMIN_USERNAME=admin
      - PGPOOL_ADMIN_PASSWORD=星星
      - PGPOOL_ENABLE_LOAD_BALANCING=yes
      - PGPOOL_POSTGRES_CUSTOM_USERS=customuser
      - PGPOOL_POSTGRES_CUSTOM_PASSWORDS=星星
      - REPMGR_PRIMARY_HOST=pg-0
    restart: always
    extra_hosts:
      - "pg-0:192.168.0.130"
      - "pg-1:192.168.0.131"
    healthcheck:
      test: ["CMD", "/opt/bitnami/scripts/pgpool/healthcheck.sh"]
      interval: 10s
      timeout: 5s
      retries: 5

2.3 测试

2.3.1 节点断掉后重连，数据自动同步

- 停掉pg-1节点，然后连接pgpool，新增一个t1表，插入一条数据，重启pg-1节点，观察相应数据库是否有新增t1表及数据完整性，测试通过。

3. 单机开发环境安装

系统 ubuntu22.04

# 安装 postgresql16
apt-get update
apt-get install postgresql-16

# 完后开用户，修改postgres超管密码
su postgresql
psql

CREATE USER fsft_t1 PASSWORD 'xxx';
ALTER USER postgres WITH PASSWORD 'xxx';

# 允许外部ip访问
vi /etc/postgresql/16/main/pg_hba.conf

添加下面
host    all             all             0.0.0.0/0               scram-sha-256

4. 宝塔下的postgres扩展安装

宝塔下带有快速的pq数据库安装，可按以下步骤安装扩展：

1. 设置环境

# 搜索pg_config路径
find /www -name pg_config

# 添加PG_CONFIG路径到/etc/profil
export PG_CONFIG=/www/server/pgsql/bin/pg_config

# 重载环境
source /etc/profile

2 获取并安装扩展

cd /tmp
git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # may need sudo

参考 :

- pgpool 参数： containers/bitnami/pgpool at main · bitnami/containers · GitHub

- PostgreSQL添加UUID扩展

附录：

工具

pgAdmin - pgAdmin - PostgreSQL Tools

安装扩展

# 用apt安装pq扩展, 装完后无需重启pq服务
apt search postgresql-16
apt install postgresql-16-pgvector

# 查看当前数据库已启用的扩展
SELECT * FROM pg_extension;

# 查看当前pq可用的扩展
SELECT * FROM pg_available_extensions;

# 为当前数据库启用扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "vector";