PostgreSQL数据库

一、什么是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

推荐阅读更多精彩内容