debian11 bullsye postgresql-11
echo "
deb https://mirrors.aliyun.com/debian/ bullseye main non-free contrib
deb-src https://mirrors.aliyun.com/debian/ bullseye main non-free contrib
deb https://mirrors.aliyun.com/debian-security/ bullseye-security main
deb-src https://mirrors.aliyun.com/debian-security/ bullseye-security main
deb https://mirrors.aliyun.com/debian/ bullseye-updates main non-free contrib
deb-src https://mirrors.aliyun.com/debian/ bullseye-updates main non-free contrib
deb https://mirrors.aliyun.com/debian/ bullseye-backports main non-free contrib
deb-src https://mirrors.aliyun.com/debian/ bullseye-backports main non-free contrib">/etc/apt/sources.list
apt-get update
apt-get install -y curl
apt-get install -y gnupg2
curl http://mirrors.zju.edu.cn/postgresql/repos/apt/ACCC4CF8.asc | apt-key add -
vim /etc/apt/sources.list.d/pgdg.list
deb http://mirrors.zju.edu.cn/postgresql/repos/apt/ bullseye-pgdg main
apt-get install -y locales-all
---------------------
字符集问题
dpkg-reconfigure locales
-----------------------
apt-get install -y postgresql-11
启动 pg_ctlcluster 11 main start
#pg_ctlcluster 11 main stop #不建议
systemctl stop postgresql@11-main 使用这个关
rm -rf /var/lib/postgresql/11/main/*
chown -R postgres:postgres /home/postgres/全备文件.tar.gz
tar -zxf /home/postgres/全备文件.tar.gz -C /home/postgres/backuprds
mv /home/postgres/backuprds/base/* /var/lib/postgresql/11/main/
mv /home/postgres/backuprds/pg_wal/* /var/lib/postgresql/11/main/pg_wal/
vim /etc/postgresql/11/main/pg_hba.conf
host all all 0.0.0.0/0 md5
host replication repl 0.0.0.0/0 md5
systemctl start postgresql@11-main
-----------------------------------------------
apt安装初始化：
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main --auth-local peer --auth-host md5
--------------------------------------------------
CREATE ROLE repl login replication encrypted password 'repl';
------------------------------------------------
搭建从库方式一：
curl http://mirrors.zju.edu.cn/postgresql/repos/apt/ACCC4CF8.asc | apt-key add -
vim /etc/apt/sources.list.d/pgdg.list
deb http://mirrors.zju.edu.cn/postgresql/repos/apt/ bullseye-pgdg main
apt-get install postgresql-11
启动 pg_ctlcluster 11 main start
systemctl stop postgresql@11-main #关掉从库
cd /var/lib/postgresql/11/
mv main mainbak
su - postgres
pg_basebackup -R -D /var/lib/postgresql/11/main/ -Fp -Xs -v -P -h 10.80.1.100 -U repl -W
-------------------------------------------------
搭建从库方式二：
在主库执行：
SELECT pg_start_replication('replica', '123456', '10.80.1.70');
------------------------------------------------
备份：
pg_dump -h 10.80.0.94 --username=root --dbname=boss >20240207.boss.tar #主库slave
pg_dump -h 10.80.1.242 --username=root --dbname=bossuat0407 >20240207.bossuat0407.tar #uat
pg_dump -h 10.80.0.72 --username=root --dbname=boss >20240207.bosspre.tar #pre
上传obs：
wget https://obs-community.obs.cn-north-1.myhuaweicloud.com/obsutil/current/obsutil_linux_amd64.tar.gz
tar zxvf obsutil_linux_amd64.tar.gz
cd obsutil_linux_amd64_5.5.12/
chmod 755 obsutil
./obsutil config -i=ak -k=sk -e=obs.cn-east-2.myhuaweicloud.com #华东2
./obsutil config -i=ak -k=sk -e=obs.cn-east-3.myhuaweicloud.com #华东1
./obsutil cp /root/test.txt obs://alexonly/ #华东1
./obsutil cp /root/test.txt obs://bosshwy/alex/ #华东2 会同步到华东1
实测速度160G 10分钟 252.79MB/s
./obsutil cp /root/20240207.boss.tar obs://veeamhuadong2/alex/ #上传到华东2 自动同步到华东1
./obsutil cp /root/20240207.boss.tar obs://annualmeeting/alex/ #备用方案，自动同步到华东1
./obsutil cp obs://veeamhuadong1/alex/20240207.boss.tar ./ # 从华东1下载
-------------------------------------------
恢复：
pre:
su - postgres
psql boss </tmp/20240228.boss.tar 1>normal.txt 2>error.txt
-------------------------------------------
权限：
改密码：alter user postgres with password 'alex1234';
pre：
sa
Only2019
CREATE ROLE "sa" CREATEDB LOGIN PASSWORD 'Only2019';
uat：
CREATE ROLE "sa" CREATEDB LOGIN PASSWORD 'Only2019';
prod：
CREATE ROLE "boss_user" CREATEDB LOGIN PASSWORD 'OnlyBoss@2023';
CREATE ROLE "bossread" CREATEDB LOGIN PASSWORD 'bossOnly2024';
--------------------
conf:
wget www.alexman.cn/prepostgresql.conf
--------------------
改所有者：
select 'ALTER TABLE "' || table_name || '" OWNER TO boss_user;' from information_schema.tables where table_schema='public'; #感觉不稳
-------------------
pgdump prod 20分钟
传输到obs 12分钟
从obs下载 2小时
恢复：
