PostgreSQL数据库迁移案例

PostgreSQL 简介

PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL的起源可以追溯到1986年，作为加州大学伯克利分校POSTGRES项目的一部分，并且在核心平台上进行了30多年的积极开发。

	PostgresSQL凭借其经过验证的架构，可靠性，数据完整性，强大的功能集，可扩展性以及软件背后的开源社区的奉献精神赢得了良好的声誉，以始终如一地提供高性能和创新的解决方案。PostgreSQL在所有主要操作系统开始使用PostgreSQL从未如此简单。

PostgreSQL 功能介绍

数据类型

1. 基本类型：Integer, Numeric, String, Boolean
2. 结构类型：Date/Time, Array, Range, UUID
3. 文档类型：JSON/JSONB, XML, Key-value(Hstore)
4. 几何类型：Point, Line, Circle, Polygon
5. 自定义类型：Composite, Custom Types数据的完整性
6. 唯一性，不为空
7. 主键
8. 外键
9. 排除约束
10. 显式锁定，咨询锁定

并发性，性能

1. 索引：
2. 高级索引
3. 复杂的查询计划期/优化器
4. 交互
5. 多版本并发控制(MVCC)
6. 读取查询的并行化和构建B树索引
7. 表分区
8. Sql标准中定义的所有事物隔离级别，包括Serializable
9. 即时表达式汇编(JIT)

可靠性，灾难恢复

1. 预写日志（WAL）
2. 复制：异步，同步，逻辑
3. 时间点恢复（pitr），主动备用
4. 表空间

安全性

1. 身份验证：GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 证书等
2. 强大的访问控制系统
3. 列和行级安全性

可扩展性

1. 存储的功能和程序
2. 程序语言：PL/PGSQL, Perl, Python (more)
3. 外部数据包装器：使用标准SQL接口连接到其他数据库或流
4. 许多提供附加功能的扩展，包括PostGIS

国际化，文本搜索

1. 支持国际字符集，例如通过ICU校对
2. 全文检索

对比Mysql：

1. PostgreSQL的稳定性极强，Innodb等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足的进步，然而很多Mysql用户都遇到过Server级的数据库丢失的场景---Mysql系统库是MyISAM的，相较而言，PG数据库在这方面要好一些。
2. 任何系统都有他的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍然可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而MySQL明细出现一个波峰后下滑。
3. PG多年在GIS领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG中有大量的字典、数组、bitmap等数据类型，相比之下MaySQL就差很多，insagram就是因为PG的空间数据库扩展POSTGIS远远强于MySQL的my spatial而采用PGSQL的。
4. PG的“无锁定”特性非常突出，甚至包括vacuum这样的整理数据空间的操作，这个和PGSSQL的MVCC实现有关系。
5. PG的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。
6. PG有极其强悍的SQL编程能力，有丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里面叫Window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MySQL就差的很远，很多分析功能那个都没有，腾讯内部数据存储主要是Mysql，但是主要的数据分析就是Hadoop+PGsql。
7. PG的有很多中集群架构可以选择，plproxy可以支持语句级的镜像或者分片，slony可以进行字段级的同步设置，standby可以构建WAL文件级或者流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。
8. 一般关系型数据库的字符串有限定长度8k左右，无限长Text类型的功能受限，只能作为外部大数据访问。而PG的TEXT类型可以直接方法，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库就可以省略了
9. 对于web应用来说，复制的特性很重要，Mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate, 是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时pgsql还提供stream复制。
10. Pgsql对于numa架构的支持要比mysql强一些，比mysql对于读的性能要好些，pgsql提交可以完全异步，而mysql的内存表不够实用（表锁原因）

模板数据库

	template1和template0是pgsql的模板数据库。所谓模板数据库就是创建新database时，PostgreSQL会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据文件。PostgreSQL安装好以后会默认附带两个模板数据库：template0和template1。

我们知道创建数据库时的语法为：

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ LC_COLLATE [=] lc_collate ]
       [ LC_CTYPE [=] lc_ctype ]
       [ TABLESPACE [=] tablespace_name ]
       [ ALLOW_CONNECTIONS [=] allowconn ]
       [ CONNECTION LIMIT [=] connlimit ] ]
       [ IS_TEMPLATE [=] istemplate ]

如：create database aaaa with template='template0' encoding ='UTF8' lc_collate='C' lc_ctype='en_US.utf8' owner='test123';

其中template表示模板数据库。建库时如果不指定 TEMPLATE 属性，默认用的是 template1 模板库。

template1和template0

1. template1 可以连接，template0 不可以连接
2. 使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以。

使用\l 命令查看template0和template1的encoding和locale

注：template0和template1都不能被删除。

怎么创建模板数据库？

	方法是指定is_template

create database tmpdb with template template0 lc_collate 'zh_CN.UTF8' lc_ctype 'zh_CN.UTF8' is_template=true;

如何删除刚创建模板数据库?

解决需要先把模板库改成普通库再删除。

alter database tmpdb is_template false;

drop database tmpdb;

数据迁移案例

数据备份

pg_dump -h 192.168.30.1 -p 5432 -U admin articledb > articledb.sql

链接template1

psql -U uatpguser -h 192.168.30.2 -p 5432 template1

创建数据库

CREATE DATABASE "articledb";

创建用户

CREATE USER admin WITH PASSWORD 'hrkx0&3iZ1#RG^bO';
CREATE USER readonly WITH PASSWORD 'cGpCP75Nu7^q1Ziw';

将数据库 db 权限授权于 user

GRANT ALL PRIVILEGES ON DATABASE "articledb" TO admin;

分配权限

GRANT ALL PRIVILEGES ON all tables in schema public TO admin;

## 分配只读权限
GRANT SELECT ON  all tables TOreadonly;

导入数据

psql -U admin -h 192.168.30.2 -d articledb -p 5432 -f articledb.sql

链接数据库

psql -h 192.168.30.2 -p 5432 -U admin -W articledb

查询数据库大小

select pg_size_pretty(pg_database_size('admin'));