Ubuntu PostgreSQL 安装、配置及简单的使用
1. PostgreSQL介绍
PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。
2. 使用PostgreSql优势
PostgreSql提供了许多功能，旨在帮助开发人员构建应用程序，管理员保护数据完整性并且构建容错环境，并帮助你管理数据，无论数据集的大小。除了免费和开源之外，Postgre SQL还具有高度的可扩展性。例如，你可以定义自己的数据类型，构建自定义SQL函数，甚至可以编写来自不同编程语言的代码，而不需要重新编译数据库。
3. PostgreSql安装
使用如下命令，会自动安装最新版，这里为9.5
sudo apt-get install postgresql
安装完成后，默认会：
（1）创建名为"postgres"的Linux用户
（2）创建名为"postgres"、不带密码的默认数据库账号作为数据库管理员
（3）创建名为"postgres"的表
4. psql命令
安装完后会有PostgreSQL的客户端psql，通过 sudo -u postgres psql 进入，提示符变成： postgres=#
image.png
在这里可用执行SQL语句和psql的基本命令。可用的基本命令如下：
- \password：设置密码
- \q：退出
- \h: 查看SQL命令的解释，比如\h select。
- \?：查看psql命令列表。。
- \l：列出所有数据库。
- \c [database_name]：连接其他数据库。
- \d：列出当前数据库的所有表格。
- \d [table_name]：列出某一张表格的结构。
- \du：列出所有用户。
- \e：打开文本编辑器。
- \conninfo：列出当前数据库和连接的信息。
5. 修改数据库默认账号的密码
a. 登录
使用psql命令登录数据库的命令为：
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
输入上面命令以后，系统会提示输入dbuser用户的密码。
上面命令的参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口
登录.png
b. 修改默认管理员账号的密码
以Linux用户"postgres"的身份（此时只有该用户有psql命令）执行psql客户端，进入该客户端的提示符界面（这里系统用户名、数据库用户名、数据库名都为postgres，故可采用简写形式）
sudo -u postgres psql
postgres=# alter user postgres with password '123456';
修改默认管理员账号密码.png
这样，管理员"postgres"的密码就为"123456"。
退出psql客户端命令：\q
若要删除该管理员的密码，则可用命令：sudo -u postgres psql -d postgres
6. 配置数据库以允许远程连接访问
安装完成后，默认只能本地才能连接数据库，其他机子访问不了，需要进行配置。
a. 修改监听地址
vim /etc/postgresql/9.5/main/postgresql.conf
将 #listen_addresses = 'localhost' 的注释去掉并改为 listen_addresses = '*'
b. 修改可访问用户的IP段
vim /etc/postgresql/9.5/main/pg_hba.conf
在文件末尾添加： host all all 0.0.0.0 0.0.0.0 md5 ，表示运行任何IP连接
c. 重启数据库
/etc/init.d/postgresql restart
重启数据库.png
7. 添加新用户和新数据库
a. 使用PostgreSQL客户端psql
运行系统用户"postgres"的psql命令，进入客户端：
sudo -u postgres psql
创建用户"xiaoai"并设置密码：
postgres=# create user xiaoai with password '123456';
创建数据库exampledb_12_21，所有者为xiaoai：
postgres=# create database exampledb_12_21 owner xiaoai;
将exampledb_12_21数据库的所有权限赋予xiaoai，否则xiaoai只能登录psql，没有任何数据库操作权限：
postgres=# grant all privileges on database exampledb_12_21 to xiaoai;
客户端添加新用户和数据库.png
b. 使用shell命令行
安装PostgreSQL后提供了createuser和createdb命令行程序。
首先创建数据库用户"test"，并指定为超级用户：
sudo -u postgres createuser --superuser test;
接着登录psql控制台设置其密码后退出：
创建用户设置密码.png
然后在shell命令行下创建数据库并指定所有者：
sudo -u postgres createdb -O test testdb;
创建数据库指定作者.png
8. PostgreSql功能介绍：
a. 数据类型
基本类型：Integer, Numeric, String, Boolean
结构类型：Date/Time, Array, Range, UUID
文档类型：JSON/JSONB, XML, Key-value(Hstore)
几何类型：Point, Line, Circle, Polygon
自定义类型：Composite, Custom Types
b. 数据完整性
- 唯一性， 不为空
- 主键， 外键
- 排除约束
- 显示锁定， 咨询锁定
c. 并发性，性能
- 索引，高级索引
- 复杂的查询计划期/优化器
- 交互
- 多版本并发控制(MVCC)
- 读取查询的并行化和构建B树索引
- 表分区
- Sql标准中定义的所有事物隔离级别，包括Serializable
- 即时表达式汇编（JIT）
d. 可靠性， 灾难恢复
- 预写日志（WAL）
- 复制：异步，同步，逻辑
- 时间点恢复，主动备用
- 表空间
e. 安全性
- 身份验证： GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 证书等
- 强大的访问控制系统
- 列和行级安全性
f. 可扩展性
- 存储的功能和程序
- 程序语言：PL/PGSQL, Perl, Python等
- 外部数据包装器：使用标准SQL接口连接到其他数据库或流
- 许多提供附加功能的扩展，包括PostGIS
g. 国际化， 文本搜索
- 支付国际字符集，例如通过ICU校对
- 全文检索
基本数据库操作命令
# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('xaioai', '2019-12-21');
# 选择记录
SELECT * FROM user_tbl;
# 更新数据
UPDATE user_tbl set name = '小艾' WHERE name = 'xaioai';
# 删除记录
DELETE FROM user_tbl WHERE name = '小艾' ;
# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl;
重启服务
/etc/init.d/postgresql restart
或者
service postgresql restart
卸载
sudo apt-get purge 'postgresql-*'
sudo apt-get autoremove 'postgresql-*'
对比Mysql:
- PostgreSQL的稳定性极强，Innodb等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足的进步，然而很多Mysql用户都遇到过Server级的数据库丢失的场景---Mysql系统库是MyISAM的，相较而言，PG数据库在这方面要好一些。
- 任何系统都有他的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍然可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而MySQL明细出现一个波峰后下滑。
- PG多年在GIS领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG中有大量的字典、数组、bitmap等数据类型，相比之下MaySQL就差很多，insagram就是因为PG的空间数据库扩展POSTGIS远远强于MySQL的my spatial而采用PGSQL的。
- PG的“无锁定”特性非常突出，甚至包括vacuum这样的整理数据空间的操作，这个和PGSSQL的MVCC实现有关系。
- PG的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。
- PG有极其强悍的SQL编程能力，有丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里面叫Window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MySQL就差的很远，很多分析功能那个都没有，腾讯内部数据存储主要是Mysql，但是主要的数据分析就是Hadoop+PGsql。
- PG的有很多中集群架构可以选择，plproxy可以支持语句级的镜像或者分片，slony可以进行字段级的同步设置，standby可以构建WAL文件级或者流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。
- 一般关系型数据库的字符串有限定长度8k左右，无限长Text类型的功能受限，只能作为外部大数据访问。而PG的TEXT类型可以直接方法，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库就可以省略了
- 对于web应用来说，复制的特性很重要，Mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate, 是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时pgsql还提供stream复制。
- Pgsql对于numa架构的支持要比mysql强一些，比mysql对于读的性能要好些，pgsql提交可以完全异步，而mysql的内存表不够实用（表锁原因）
[引用链接]：https://blog.csdn.net/qq_40223688/article/details/89451616
