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. 数据完整性

  1. 唯一性, 不为空
  2. 主键, 外键
  3. 排除约束
  4. 显示锁定, 咨询锁定

c. 并发性,性能

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

d. 可靠性, 灾难恢复

  1. 预写日志(WAL)
  2. 复制:异步,同步,逻辑
  3. 时间点恢复,主动备用
  4. 表空间

e. 安全性

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

f. 可扩展性

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

g. 国际化, 文本搜索

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

基本数据库操作命令

# 创建新表 
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:

  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的内存表不够实用(表锁原因)
    [引用链接]:https://blog.csdn.net/qq_40223688/article/details/89451616