一、创建数据库，分配权限。
-- 先切换postgresql用户
sudo su - postgresql
mkdir /DATA/postgresql/tablespace/lcd
-- 连接数据库
psql -p 5448
-- 创建用户并设置密码
create user lcd with password '666888';
-- 创建表空间 指定目录
create tablespace lcd owner lcd location '/DATA/postgresql/tablespace/lcd';
-- 创建数据库 设置拥有者 和表空间 打开允许连接
CREATE DATABASE transfer_lcd WITH OWNER = lcd ENCODING = 'UTF8' TABLESPACE = lcd CONNECTION LIMIT = -1;
-- 配置权限
GRANT ALL PRIVILEGES ON DATABASE transfer_lcd to lcd;
REVOKE CONNECT ON DATABASE transfer_lcd FROM PUBLIC;
GRANT CONNECT ON DATABASE transfer_lcd TO lcd;
-- 分配允许连接给只读用户
GRANT CONNECT ON DATABASE transfer_lcd TO transferreadonly;
-- 新建模式
CREATE SCHEMA mrsoft_lcd;
-- 分配模式所有权限给 lcd 用户
GRANT ALL ON SCHEMA mrsoft_lcdTO lcd;
-- 分配模式 USAGE 权限给 transferreadonly
GRANT USAGE ON SCHEMA mrsoft_lcdTO transferreadonly;
-- 设置默认的模式查找顺序
SET search_path TO mrsoft_lcd;
ALTER database "transfer_lcd" SET search_path TO mrsoft_lcd;
-- 显示现有的模式查找顺序，便于问题排查。
SHOW search_path;
-- 建表之后 授权给只读用户(需要建好表之后执行才生效)
GRANT SELECT ON ALL tables in SCHEMA mrsoft_lcd to transferreadonly;
GRANT ALL ON ALL tables in SCHEMA mrsoft_lcd to lcd;
二、切换流程
（1）停 ---- 停应用，关闭相关的程序
（2）备 ---- 执行 pg_dump pg_restore 进行数据迁移
（3）启 ---- 修改相关程序的数据源，启动程序
（4）查 ---- 数据源切换后，对系统进行全面检查
三、技术要点
（1）权限分配的步骤：tablespace --> db --> connection --> schema --> table
（2） PostgreSQL有模式（schema）的概念，默认模式是public，新建模式 mrsoft_lcd，必须修改默认的查找顺序，不然会出现 relation "tableName" does not exist 的错误。使用 SHOW search_path; 显示现有的模式查找顺序。
（3）在本次迁库过程中，遇到最大的问题是 PostgreSQL 新建模式权限的问题，其他的权限参考之前的脚本也能快速处理。
查看权限：
使用 \z(tablename) 或 \dp(tablename)查看权限，也可使用navicat、pgadmin等可视化工具查看。
Access privileges 说明：
权限的含义如下：
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege
（4）基本操作指令：
\c dbname -- 切换数据库(use dbname)
\l -- 列举数据库(show databases)
\dt -- 列举表(show tables)
\d tablename -- (查看表结构)
\q -- 退出
\z或 \dp -- 查看权限
(5) 对需要备份的库没有远程整库备份的权限，只能做单表备份，并且 pg_restore 的时候必须模式名称一致才可还原。
#!/bin/bash
bak_table_ary=(table1 table2)
# dump
function start_dump_lcd(){
code=lcd
bak_path="/DATA/DataBackup/${code}"
echo "创建目录${bak_path}"
mkdir -p ${bak_path}
for table in ${bak_table_ary[@]};
do
echo "开始备份${code} ${table}"
/usr/pgsql-10/bin/pg_dump "host=192.168.100.116 port=5444 dbname=dbname user=user password=password" -a -t ${table} -F c -Z 5 -f ${bak_path}/${table}.backup
done
}
start_dump_lcd
pg_dump 脚本
#!/bin/bash
bak_table_ary=(table1 table2)
airport_code_ary=(lcd wly)
# restore
function start_restore(){
code=${1}
bak_path="/DATA/DataBackup/${code}"
for table in ${bak_table_ary[@]};
do
echo "开始还原${code} ${table}"
/usr/pgsql-10/bin/pg_restore -U ${code} -h 127.0.0.1 -p 5448 -d transfer_${code} -F c /DATA/DataBackup/${code}/${table}.backup
done
}
#restore airport_code_ary
for code in ${airport_code_ary[@]};
do
start_restore ${code}
done
pg_restore 脚本