pg

PostgreSQL

PostgreSQL单点部署

ansible-playbook -i inventories/${project} playbooks/common/postgresql_standalone.yml -e init_postgresql=true  -e postgresql_data_dir=/data/postgresql/11/main  -e postgresql_archivedir_dir=/data/postgresql/archivedir -b 
# 创建数据目录
# 默认数据目录/var/lib/postgresql/11/main
# 默认配置目录/etc/postgresql/11/main
mkdir -p /data/pgsql/11/data
# 迁移数据目录
rsync -avr /var/lib/postgresql/11/main/* /data/pgsql/11/data/
cp -fr /etc/postgresql/11/main/* /data/pgsql/11/data/
chown -R postgres.postgres /data/pgsql

PostgreSQL集群部署

新增 Apt源

# 官方源(x86)
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 国内源(x86)
echo "deb https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ `lsb_release -cs`-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -
# 官方源(arm)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

安装PG及依赖

# pg
apt install -y postgresql-11 postgresql-11-postgis-2.5 postgresql-server-dev-11
# pgpool
apt install -y postgresql-11-pgpool2 pgpool2 arping

安装分词器

cd /root
wget -c http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2 
bzip2 -d scws-1.2.3.tar.bz2
tar -xf scws-1.2.3.tar
cd scws-1.2.3 
./configure 
make install
cd /root
wget -c https://codeload.github.com/amutu/zhparser/zip/master
unzip master
cd zhparser-master
export PATH=$PATH:/usr/lib/postgresql/11/bin
SCWS_HOME=/root/scws-1.2.3 ; make && make install

PG数据目录初始化

# 创建数据和归档目录
# 默认数据目录/var/lib/postgresql/11/main
# 默认配置目录/etc/postgresql/11/main
mkdir -p /data/pgsql/11/data
mkdir -p /data/pgsql/archivedir
# 迁移数据目录[主&从]
rsync -avr /var/lib/postgresql/11/main/* /data/pgsql/11/data/
cp -fr /etc/postgresql/11/main/* /data/pgsql/11/data/
chown -R postgres.postgres /data/pgsql
# 移除原配置和数据目录[主&从]
mv /etc/postgresql/11/main /etc/postgresql/11/main_unuse
mv /var/lib/postgresql/11/main /var/lib/postgresql/11/main_unuse
ln -s /data/pgsql/11/data /etc/postgresql/11/main
ln -s /data/pgsql/11/data /var/lib/postgresql/11/main

PG流复制

# 生成postgres用户的ssh免密互登录(节点相互交换密钥)
su - postgres
ssh-keygen
# 流复制用户replica免密
su - postgres
cat > .pgpass <<EOF
pg_01:5432:replication:replica:ve406wVs2hCeh
pg_02:5432:replication:replica:ve406wVs2hCeh
EOF
# 权限
chmod 600 /var/lib/postgresql/.pgpass
# 流复制用户
create role replica login replication encrypted password 've406wVs2hCeh';
# 从节点复制数据
pg_basebackup -X stream -c fast -P -D /data/pgsql/11/data -h pg-01 -p 5432 -U replica

配置 recovery.done文件

/data/pgsql/11/data/recovery.done

standby_mode = 'on'
primary_conninfo = 'host=hhhtlt-pg2 port=5432 user=replica'
restore_command = 'scp -P 22 hhhtlt-pg2:/data/pgsql/archivedir/%f %p'

生成pgpool用户

pg_md5 --md5auth --username=root <password of root user>
pg_md5 --md5auth --username=lingmou <password of lingmou user>
pg_md5 --md5auth --username=capture <password of capture user>

生成 pcp 用户

pg_md5 -p

生成业务用户

-- lingmou
CREATE USER lingmou WITH PASSWORD 've406wVs2hCeh';
CREATE DATABASE "k8s_lingmou" OWNER lingmou;
GRANT ALL PRIVILEGES ON DATABASE "k8s_lingmou" TO lingmou;
ALTER USER lingmou WITH SUPERUSER;
-- uar
CREATE USER uar WITH PASSWORD 'CM7JwCsPn4wb7';
CREATE DATABASE "k8s_uar" OWNER uar;
GRANT ALL PRIVILEGES ON DATABASE "k8s_uar" TO uar;
-- communitySync
CREATE USER communitydatasync WITH PASSWORD 'NI3BtGigLYyEL';
CREATE DATABASE "k8s_communitySync" OWNER communitydatasync;
GRANT ALL PRIVILEGES ON DATABASE "k8s_communitySync" TO communitydatasync;

Pgpool

- 创建目录/var/log/pgpool且给pgpool启动用户rw权限,此目录failover.sh脚本需要写日志
- template1库创建扩展函数pgpool_recovery