postgresql 在线备份还原

1.备份

pg_dump -h 127.0.0.1 -U postgres XXX > XXX.bak

2.还原

##拷贝备份文件并设置postgres权限
chown postgres:postgres /opt/XXX.bak 
sudo -i -u postgres psql 
##创建数据库 CREATE DATABASE XXX;
##创建用户 XXX 
create USER XXX WITH PASSWORD 'password'; 
GRANT ALL PRIVILEGES ON DATABASE XXX TO XXX; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO XXX;
##创建用户 root 
create USER root WITH PASSWORD 'dameng123'; 
GRANT ALL PRIVILEGES ON DATABASE XXX TO root; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO root ; 
ALTER ROLE "root" SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;
##退出
exit;
##执行还原
psql -h localhost -U postgres -d XXX < /opt/XXX.bak

3.修改postgresql.conf 配置文件中的max_connections 默认100