# 部署 PostgreSQL 流复制

- 按实际环境修改
    - docker-compose.yml
    - pgsql1/conf.d/\*.cnf
    - pgsql2/conf.d/\*.cnf

- 创建目录
    ```
    grep '\<source:' docker-compose.yml | cut -d: -f2 | xargs mkdir -p
    chmod 0750 pgsql{1..2}/db
    ```

- 启动
    ```
    docker-compose up -d
    ```

- 查看日志，直至出现 "PGSQL is ready for connections."
    ```
    docker-compose logs pgsql1
    docker-compose logs pgsql2
    ```

- 登录 pgsql1 数据库，创建测试表，写入测试数据
    ```
    docker exec -u postgres pgsql1 \
        psql -c "create table t1(id int, name varchar)"
    docker exec -u postgres pgsql1 \
        psql -c "insert into t1 values (1, 'aa'),(2, 'bb')"
    ```

- 登录 pgsql1 数据库，创建同步用户，配置权限
    ```
    docker exec -u postgres pgsql1 \
        psql -c "create user replicator login replication password 'Replicator_1234'"
    docker exec -u postgres pgsql1 \
        echo 'host replication replicator pgsql2 trust' >> pgsql1/db/pg_hba.conf
    #docker exec -u postgres pgsql1 pg_ctl reload
    ```

- 增加主库配置
    ```
    echo '
    wal_level = hot_standby
    max_wal_senders = 8
    wal_keep_size = 512
    wal_sender_timeout = 60s
    max_connections = 100
    ' > pgsql1/conf.d/replication.conf

- 重启 pgsql1 数据库
    ```
    docker-compose restart pgsql1
    ```

- 登录 pgsql2 数据库，同步 pgsql1 的数据目录
    ```
    docker exec -u postgres pgsql2 mkdir -p /var/log/pgsql/db_pg1
    docker exec -u postgres pgsql2 \
        pg_basebackup \
            -h pgsql1 \
            -D /var/log/pgsql/db_pg1 \
            -p 5432 \
            -U replicator \
            -Fp -Xs -P -c fast
    ```

- 停止并删除 pgsql2 容器，替换其数据目录，**注意数据目录权限**
    ```
    docker-compose stop pgsql2
    docker-compose rm -f pgsql2
    rm -rf pgsql2/db
    mv pgsql2/log/db_pg1 pgsql2/db
    chmod 0750 pgsql2/db
    ```

- 增加从库配置
    ```
    echo '
    primary_conninfo = 'host=pgsql1 port=5432 user=replicator'
    recovery_target_timeline = latest
    hot_standby = on
    max_standby_streaming_delay = 30s
    wal_receiver_status_interval = 10s
    hot_standby_feedback = on
    max_connections = 200
    max_worker_processes = 20
    ' > pgsql2/conf.d/replication.conf
    ```

- 启动 pgsql2 数据库
    ```
    docker-compose up -d pgsql2
    ```

- 登录 pgsql1 数据库，查看从库信息
    ```
    docker exec -u postgres pgsql1 \
        psql -c "select
            client_addr,
            usename,
            backend_start,
            application_name,
            sync_state,
            sync_priority
            FROM pg_stat_replication;"
    # 在 pgsql1 里创建数据库、表或更改数据，在 pgsql2 里可以看到
    ```

- 查看状态
    ```
    docker exec -u postgres pgsql1 pg_controldata -D /var/lib/pgsql | grep cluster
    # 主库(pgsql1)输出: in production
    docker exec -u postgres pgsql2 pg_controldata -D /var/lib/pgsql | grep cluster
    # 从库(pgsql2)输出: in archive recovery
    ```

- 从库提升为主库
    ```
    docker exec -u postgres pgsql2 pg_ctl promote
    ```