postgresql 迁库操作记录

一、创建数据库，分配权限。

-- 先切换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 脚本