PostgreSQL数据库

作者: 超天大圣JR | 来源:发表于2019-12-17 12:24 被阅读0次

一、什么是PostgreSQL?

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。
PostgreSQL(也称为Post-gress-Q-L)由PostgreSQL全球开发集团(全球志愿者团队)开发。它不受任何公司或其他私人实体控制。它是开源的，其源代码是免费提供的。
PostgreSQL是跨平台的，可以在许多操作系统上运行，如Linux，FreeBSD，OS X，Solaris和Microsoft Windows等。

二、PostgreSQL的特点

PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行。
PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口。
PostgreSQL支持SQL的许多功能，例如复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。
在PostgreSQL中，表可以设置为从“父”表继承其特征。可以安装多个扩展以向PostgreSQL添加附加功能。

三、安装并配置，并设置远程登陆的用户名和密码

1.安装postgreSQL

sudo apt-get update
主程序：
sudo apt-get install postgresql
数据库图形界面：
sudo apt install pgadmin3

在Ubuntu下安装Postgresql后，会自动注册为服务，并随操作系统自动启动。
在Ubuntu下安装Postgresql后，会自动添加一个名为postgres的操作系统用户，密码是随机的。并且会自动生成一个名字为postgres的数据库，用户名也为postgres，密码也是随机的。

2.修改postgres数据库用户的密码为123456

打开客户端工具（psql）
sudo -u postgres psql

其中，sudo -u postgres 是使用postgres 用户登录的意思
PostgreSQL数据库默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的

修改密码的命令：
postgres=# ALTER USER postgres WITH PASSWORD '123456';

postgres=#为PostgreSQL下的命令提示符，--注意最后的分号；

3. 退出PostgreSQL psql客户端

postgres=# \q

4.修改ubuntu操作系统的postgres用户的密码（密码要与数据库用户postgres的密码相同）

切换到root用户
su root

删除PostgreSQL用户密码
sudo passwd -d postgres

passwd -d 是清空指定用户密码的意思
设置PostgreSQL系统用户的密码

sudo -u postgres passwd

按照提示，输入两次新密码

输入新的 UNIX 密码
重新输入新的 UNIX 密码
passwd：已成功更新密码

5.修改PostgresSQL数据库配置实现远程访问

编辑配置文件： vi /etc/postgresql/9.4/main/postgresql.conf

添加/修改：在所有IP地址上监听，从而允许远程连接到数据库服务器：

listen_addresses = 'localhost' 改为 listen_addresses = '*'

编辑配置文件：vi /etc/postgresql/9.4/main/pg_hba.conf

添加/修改：允许任意用户从任意机器上以密码方式访问数据库，把下行添加为第一条规则：

host all all 0.0.0.0/0 md5

6.重启服务

$ sudo systemctl restart postgresql
或者
/etc/init.d/postgresql restart

四、关于PostgreSQL的登录角色，组角色，用户等概念及权限分配上的区别

登录角色就是具有登录权限的角色，是通常意义上的用户，不具有登录权限的角色就是组角色，是一些登录角色的集合。这样的目的是为了方便批量授权。在PostgreSQL中登录角色、组角色和用户本质上都是角色；看下面的操作就知道了。

CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。 在psql shell中输入这上面两条命令创建的角色如下图所示：

如图，使用CREATE ROLE创建的角色在组角色下，使用CREATE USER创建的角色在登录角色栏下。

五、角色的权限分配

PostgreSQL存在两种权限，一种是角色权限即role attribute（例如创建角色，创建数据库，修改目录等），另一种是操作数据库的权限即privilege（例如数据库的连接，数据表的各种操作等）。下面分别来说。

1.角色权限

一个数据库角色可以有一系列属性，这些属性定义他的权限，以及与客户认证系统的交互。如下图：

2.数据库权限、成员角色及权限的继承

PostgreSQL中预定义了许多不同类型的数据库内置权限，如：SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。

任何角色都可以是成员角色，但是只有组角色能拥有成员角色。成员角色会自动继承父角色（它所属的组角色）的数据库权限。

一个登录角色最终的权限等于其各个组角色所得权限的总合。

数据库的权限需要逐层授予，例如某个登录角色想要查看某张具体的表，那么他所属的组角色需要同时获得这张表所在的数据库的connect权限、所在架构的usage权限和这张表的Select权限。权限不够时系统给出的提示如以下三图：

$\color{red}{！！！注意！！！数据库对象是不能直接把权限授予普通登录角色的，只能将权限授予登录}$
$\color{red}{角色所在的组角色或者是带有超级用户属性的登录角色}$ （准确说超级用户的行为是不被检查的）。那么如果你的登录角色没有组角色，那就给他创建一个组角色，让登录角色成为组角色的成员角色。另外，我强烈建议不要随便给登录角色赋予超级用户权限，因为这是相当危险的。

六、用户、角色和组的操作命令

postgres安装完成后，会自动在操作系统和postgres数据库中分别创建一个名为postgres的用户以及一个同样名为postgres的数据库。

1.登录

方式1：指定参数登录

psql -U username -d database_name -h host -W
参数含义: -U指定用户 -d要连接的数据库 -h要连接的主机 -W提示输入密码。

方式2：切换到postgres同名用户后登录

su username
psql
当不指定参数时psql使用操作系统当前用户的用户名作为postgres的登录用户名和要连接的数据库名。所以在PostgreSQL安装完成后可以通过以上方式登录。

方式3：

psql -U jiraadmin -W jira
-U:以哪个用户登录
-W：登录哪个数据库；
psql -U postgres -W transaction
直接回车以postgres身份登录到transaction数据库
psql -U postgres
以postgres身份登录到默认数据库（即postgres数据库）

2.创建数据库新用户，如 dbuser：

postgres=# CREATE USER dbuser WITH PASSWORD '*****';
创建用户数据库，如exampledb：
postgres=# CREATE DATABASE exampledb OWNER dbuser;
将exampledb数据库的所有权限都赋予dbuser：
postgres=# GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
使用命令 \q 退出psql：
postgres=# \q
创建Linux普通用户，与刚才新建的数据库用户同名，如 dbuser：
sudo adduser dbuser
sudo passwd dbuser
以dbuser的身份连接数据库exampledb：
su - dbuser

3.创建用户时设定用户属性

基本语法：
CREATE ROLE role_name WITH optional_permissions;
示例：在创建用户时设定登录权限。
CREATE ROLE username WITH LOGIN;

可以通过\h CREATE ROLE指令查看全部可设置的管理权限

4.修改用户属性

修改权限的命令格式
ALTER ROLE username WITH attribute_options;
示例：可通过以下方式禁止用户登录
ALTER ROLE username WITH NOLOGIN;

5.设置访问权限

语法格式如下:
GRANT permission_type ON table_name TO role_name;
示例：
GRANT UPDATE ON demo TO demo_role; --赋予demo_role demo表的update权限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --赋予demo_role所有表的SELECT权限
特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

GRANT ALL ON demo TO demo_role; --赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户
\z或\dp指令显示用户访问权限。
\h GRANT显示所有可设置的访问权限

6.撤销用户访问权限

语法格式如下:
REVOKE permission_type ON table_name FROM user_name;
其中permission_type和table_name含义与GRANT指令中相同。

7.用户组

在postgres中用户实际上是role，同时组也是role。包含其他role的role就是组。

创建组示例：
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;
切换ROLE
SET ROLE role_name; --切换到role_name用户
RESET ROLE; --切换回最初的role
INHERIT权限：该属性使组成员拥有组的所有权限
ALTER ROLE test_user INHERIT;

删除用户和组
DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;
删除组role只会删除组的role本身，组的成员并不会被删除

七、PostgreSQL常用操作命令

连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname
切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname
\di 查看索引

创建数据库：
create database [数据库名];
删除数据库：
drop database [数据库名];
重命名一个表：
alter table [表名A] rename to [表名B];
删除一个表：
drop table [表名];
在已有的表里添加字段：
alter table [表名] add column [字段名] [类型];
删除表中的字段：
alter table [表名] drop column [字段名];
重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B];
给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];
去除缺省值：
alter table [表名] alter column [字段名] drop default;
在表中插入数据：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];
删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];--删空整个表

创建表：
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);
\copyright 显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
显示或设定用户端字元编码
\h [名称] SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称
提示用户设定内部变数
\password [USERNAME]
securely change the password for a user
\q 退出 psql

八、启动，停止和重启 PostgreSQL 服务器

命令提示
/etc/init.d/postgresql
启动PostgreSQL
sudo service postgresql start
停止PostgreSQL
sudo service postgresql stop
重新启动PostgreSQL服务器。
sudo service postgresql restart

九、导入和导出postgresql数据库脚本

导出

/usr/bin/pg_dump -U postgres testdb > /home/app/testdbdate +%Y%m%d.sql
cd /home/app
gzip testdbdate +%Y%m%d.sql

导入

day=date +%Y%m%d
drop database testdb;//删除原来的库
CREATE DATABASE testdb;
psql -U postgres -d testdb -f /home/app/testdb${day}.sql