PG跨版本跨系统 逻辑备份还原

将数据库设置只读
vi postgresql.conf 
default_transaction_read_only=on
 
&&或者使用命令
 
ALTER SYSTEM SET default_transaction_read_only = 'on';
重启生效



执行以下命令来创建文件存储库配置
 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
安装PostgreSQL
安装特定版本:sudo apt-get install postgresql-17

安装完查看版本psql --version
停止服务：systemctl stop postgresql

到配置目录：cd /etc/postgresql/17/main
修改配置文件：vim pg_hba.conf 增加
host all all 0.0.0.0/0 md5
host    replication     all            192.168.xxx.1/24        md5


=================pgbackrest安装 省略==================

修改配置文件：vim postgresql.conf
listen_addresses = '*'
max_connections = 1000
shared_buffers = 2GB
wal_level = replica
synchronous_commit = local
max_wal_size = 10GB
min_wal_size = 100MB
archive_mode = on
archive_command = 'pgbackrest --config=/etc/pgbackrest.conf --stanza=MES2_DB archive-push %p'		# command to use to archive a logfile segment
archive_timeout = 600
max_wal_senders = 10
wal_keep_size = 800
max_slot_wal_keep_size = 8000
max_replication_slots = 10
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = off
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
shared_preload_libraries = 'pg_stat_statements'

修改密码
sudo -u postgres psql postgres -p 5432
ALTER USER postgres WITH PASSWORD 'xxx2021';



新数据库创建 数据库 和 角色
CREATE ROLE s WITH 
	SUPERUSER
	NOCREATEDB
	NOCREATEROLE
	INHERIT
	LOGIN
	NOREPLICATION
	NOBYPASSRLS
	PASSWORD 'xxx2021'
	CONNECTION LIMIT -1;
	
-- DROP ROLE mes2;

CREATE ROLE s2 WITH 
	SUPERUSER
	NOCREATEDB
	NOCREATEROLE
	INHERIT
	LOGIN
	NOREPLICATION
	NOBYPASSRLS
	PASSWORD 'xxx2023'
	CONNECTION LIMIT -1;
	
-- DROP ROLE mes2dc;

CREATE ROLE s2dc WITH 
	SUPERUSER
	NOCREATEDB
	NOCREATEROLE
	NOINHERIT
	LOGIN
	NOREPLICATION
	NOBYPASSRLS
	PASSWORD 'xxx2024'
	CONNECTION LIMIT -1;
	
-- DROP ROLE mes2read;

CREATE ROLE s2read WITH 
	NOSUPERUSER
	NOCREATEDB
	NOCREATEROLE
	INHERIT
	LOGIN
	NOREPLICATION
	NOBYPASSRLS
	PASSWORD 'xxx2024'
	CONNECTION LIMIT -1;

--只读账号设置
alter user s2read set default_transaction_read_only = on;

grant connect on database postgres to s2read;

grant usage on schema public to s2read;
grant select on all sequences in schema public to s2read;
grant select on all tables in schema public to s2read;

alter default privileges in schema public grant select on tables to s2read;

--专用账号设置
ALTER DATABASE "MES2_dd" OWNER TO mes2dc;

GRANT ALL ON SCHEMA public TO mes2dc;

Grant all on all tables in schema public to mes2dc;

ALTER table dc_sn_xxx OWNER TO mes2dc;

ALTER table pc_equip_xxxx OWNER TO mes2dc;

备份还原
sudo pg_dump -h 192.168.xx.xxx -U postgres -d MES2_DB -p 5432 -F c -f /pgdump/mes.dump -v

sudo pg_dump -h 192.168.xx.xx -U postgres -d MES2_DB -p 5432 -F d -f /pgdump/mes -j 10 -v

postgres用户 pg_restore -d MES2_DB -j 10 /pgdump/mes -v