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 使用這個關(guān)

 

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   #關(guān)掉從庫

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

 

-------------------------------------------------

搭建從庫方式二：

在主庫執(zhí)行：

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下載

-------------------------------------------

恢復(fù)：

pre:

su - postgres

psql  boss </tmp/20240228.boss.tar 1>normal.txt 2>error.txt

-------------------------------------------

權(quán)限：

改密碼：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';    #感覺不穩(wěn)

 

-------------------

pgdump prod  20分鐘

傳輸?shù)給bs 12分鐘

從obs下載  2小時

恢復(fù)：