Docker 安装 PostgresSQL

1.拉取镜像

docker pull postgres

2.运行容器

docker run -d --restart=always \
-p 5432:5432 \
-e POSTGRES_PASSWORD=123456 \
-v /akim-boot/postgres/data:/var/lib/postgresql/data \
--name postgres \
postgres:latest

3.修改配置文件

vim /akim-boot/postgres/data/postgresql.conf

# 启用归档模式，允许数据库将 WAL（Write-Ahead Logging）日志文件存档。
archive_mode = on

# 连接数
max_connections = 500

# 设置时区
timezone = 'Asia/Shanghai'

配置文件修改完成后，删除容器再次启动容器让配置文件生效，至此，单机模式部署完成。

4.集群配置

Ⅰ、完成2、3节点，多个从库重复以下操作：

# 进入容器
docker exec -it 从数据库容器名称或容器id /bin/bash
 
# 删除数据，并将主节点数据同步过来（注意-h后面跟着的是主节点ip，-U后面是主节点创建的用于复制的用户名，需要输入密码时就是用于复制的用户名的密码）
rm -rf /var/lib/postgresql/data/* && pg_basebackup -h 主数据库ip -p 主数据库端口 -U 主数据库用户名 -Fp -Xs -Pv -R -D /var/lib/postgresql/data
 
# 数据同步后容器会关闭。再启动即可
docker restart 从数据库容器名称或容器id
 
# 修改从数据库配置文件vi /data/postgresql/data/postgresql.conf
# 配置主库ip地址以及端口号，以及用于复制的用户名和密码
primary_conninfo = 'host=主数据库ip port=主数据库端口 user=用户名 password=密码'

# 在恢复期间允许查询。这是在流复制过程中，从库在进行 WAL 日志恢复的同时允许查询读取。
# 从机只读
hot_standby = on

# 设置恢复的目标时间线。在这里，设置为 latest 表示从库将一直尝试连接到主库的最新时间线上。
recovery_target_timeline = latest

# 必须大于主节点的连接数。这确保从库可以处理主库发送的所有连接请求。
max_connections = 1000

# 设置时区
timezone = 'Asia/Shanghai'

# 重启从数据库
docker restart 从数据库容器名称或容器id
 
# 校验，在主机执行sql
select * from pg_stat_replication;

Ⅱ、负载均衡，读写分离

拉取 pgpool 镜像：

docker pull pgpool

启动 pgpool 容器：

docker run -d -p 9999:5432 --name pgpool \
--env PGPOOL_BACKEND_NODES=0:主数据库 ip:主数据库端口,1:从数据库1 ip:从数据库1 端口,2:从数据库2 ip:从数据库2 端口... \
--env PGPOOL_SR_CHECK_USER=postgres \
--env PGPOOL_SR_CHECK_PASSWORD=123456 \
--env PGPOOL_ENABLE_LDAP=no \
--env PGPOOL_POSTGRES_USERNAME=postgres \
--env PGPOOL_POSTGRES_PASSWORD=123456 \
--env PGPOOL_ADMIN_USERNAME=postgres \
--env PGPOOL_ADMIN_PASSWORD=123456 \
--env PGPOOL_USERNAME=postgres \
--env PGPOOL_PASSWORD=123456 \
--env PGPOOL_AUTHENTICATION_METHOD=scram-sha-256 \
pgpool

5.创建用户、库及授权

Ⅰ、进入容器：

# 进入容器
docker exec -it 容器名称或容器id /bin/bash

# 切换用户
su - postgres

# 执行 sql
psql

Ⅱ、创建用户、库及授权

# 创建用户
postgres=# create user 用户名 with password '密码';
CREATE ROLE

# 创建数据库
postgres=# create database 数据库名 owner 所属用户名;
CREATE DATABASE

# 授权
postgres=# grant all privileges on database 数据库名 to 所属用户名;
GRANT

#退出
postgres=# \q

6.角色权限设置

-- 创建账号
CREATE ROLE admin WITH LOGIN PASSWORD '123456' NOSUPERUSER NOCREATEDB NOCREATEROLE;
 
-- 给角色/账号访问数据库的权限
GRANT ALL PRIVILEGES ON DATABASE testDb TO admin;
 
-- 设置所有schema的访问权限
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO admin;', schema_name);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO admin;', schema_name);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I TO admin;', schema_name);
            END LOOP;
    END
$$;
 
-- 解绑所有schema的访问权限
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I FROM admin;', schema_name);
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I FROM admin;', schema_name);
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I FROM admin;', schema_name);
            END LOOP;
    END
$$;
 
 
-- 设置默认权限，新创建的表都属于这个角色/账号
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO admin;',
                               schema_name);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO admin',
                               schema_name);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO admin',
                               schema_name);
            END LOOP;
    END
$$;
 
-- 查询角色/账号可以连接的数据库
SELECT datname
FROM pg_database
WHERE has_database_privilege('admin', datname, 'CONNECT');
 
-- 解绑连接其他数据库
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
 
-- 查询数据库所绑定的角色/用户
SELECT datacl
FROM pg_database
WHERE datname = 'testDb';
 
-- 删除角色/账号
DROP OWNED BY admin;

Docker 安装 PostgresSQL

文章评论