# 創(chuàng)建映射目錄
mkdir /data/postgresql/data

# 啟動(dòng)容器
docker run -d -p 5432:5432 --restart=always -v /data/postgresql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 --name postgres postgres:14

# 修改配置文件/data/postgresql/data/postgresql.conf
# 設(shè)置時(shí)區(qū)
timezone = 'Asia/Shanghai'
# 連接數(shù)
max_connections = 1000

# 創(chuàng)建映射目錄
mkdir /data/postgresql/data

# 啟動(dòng)容器
docker run -d -p 5432:5432 -v /data/postgresql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 --name postgres_master postgres:14

# 修改vi /data/postgresql/data/pg_hba.conf，允許從機(jī)復(fù)制
host replication all 192.168.0.12/32 trust
host replication all 193.168.0.13/32 trust

# 修改vi /data/postgresql/data/postgresql.conf
# 啟用歸檔模式，允許數(shù)據(jù)庫(kù)將 WAL（Write-Ahead Logging）日志文件存檔。
archive_mode = on
# 連接數(shù)
max_connections = 500
# 設(shè)置時(shí)區(qū)
timezone = 'Asia/Shanghai'

# 重啟容器
docker restart postgres_master 

# 啟動(dòng)容器，注意這里沒(méi)有進(jìn)行數(shù)據(jù)卷掛載，因?yàn)楹竺嬉獎(jiǎng)h除數(shù)據(jù)將主節(jié)點(diǎn)數(shù)據(jù)同步過(guò)來(lái)
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=123456 -v /data/postgresql/data:/var/lib/postgresql/data --name postgres_slave1 postgres:14

# 進(jìn)入容器
docker exec -it postgres_slave1 bash

# 刪除數(shù)據(jù)，并將主節(jié)點(diǎn)數(shù)據(jù)同步過(guò)來(lái)（注意-h后面跟著的是主節(jié)點(diǎn)ip，-U后面是主節(jié)點(diǎn)創(chuàng)建的用于復(fù)制的用戶名，需要輸入密碼時(shí)就是用于復(fù)制的用戶名的密碼）
rm -rf /var/lib/postgresql/data/* && pg_basebackup -h 192.168.0.11 -p 5432 -U postgres -Fp -Xs -Pv -R -D /var/lib/postgresql/data

# 數(shù)據(jù)同步后容器會(huì)關(guān)閉。再啟動(dòng)即可
docker restart postgres_slave1

# 修改文件vi /data/postgresql/data/postgresql.conf
# 配置主庫(kù)ip地址以及端口號(hào)，以及用于復(fù)制的用戶名和密碼        
primary_conninfo = 'host=192.168.0.11 port=5432 user=postgres password=123456'   
# 在恢復(fù)期間允許查詢(xún)。這是在流復(fù)制過(guò)程中，從庫(kù)在進(jìn)行 WAL 日志恢復(fù)的同時(shí)允許查詢(xún)讀取。
# 從機(jī)只讀
hot_standby = on
# 設(shè)置恢復(fù)的目標(biāo)時(shí)間線。在這里，設(shè)置為 latest 表示從庫(kù)將一直嘗試連接到主庫(kù)的最新時(shí)間線上。
recovery_target_timeline = latest
# 必須大于主節(jié)點(diǎn)的連接數(shù)。這確保從庫(kù)可以處理主庫(kù)發(fā)送的所有連接請(qǐng)求。
max_connections = 1000
# 設(shè)置時(shí)區(qū)
timezone = 'Asia/Shanghai'
                         
# 重啟從庫(kù)
docker restart postgres_slave1 

# 校驗(yàn)，在主機(jī)執(zhí)行sql
select * from pg_stat_replication;

docker run -d -p 9999:5432 --name pgpool \
--env PGPOOL_BACKEND_NODES=0:192.168.0.11:5432,1:192.168.0.12:5432,2:192.168.0.13:5432 \
--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

-- 創(chuàng)建賬號(hào)
CREATE ROLE admin WITH LOGIN PASSWORD '123456' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 給角色/賬號(hào)訪問(wèn)數(shù)據(jù)庫(kù)的權(quán)限
GRANT ALL PRIVILEGES ON DATABASE testDb TO admin;

-- 設(shè)置所有schema的訪問(wèn)權(quán)限
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的訪問(wèn)權(quán)限
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
$$;


-- 設(shè)置默認(rèn)權(quán)限，新創(chuàng)建的表都屬于這個(gè)角色/賬號(hào)
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
$$;

-- 查詢(xún)角色/賬號(hào)可以連接的數(shù)據(jù)庫(kù)
SELECT datname
FROM pg_database
WHERE has_database_privilege('admin', datname, 'CONNECT');

-- 解綁連接其他數(shù)據(jù)庫(kù)
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- 查詢(xún)數(shù)據(jù)庫(kù)所綁定的角色/用戶
SELECT datacl
FROM pg_database
WHERE datname = 'testDb';

-- 刪除角色/賬號(hào)
DROP OWNED BY admin;