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指定端口

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