pg数据库对象介绍使用以及配置文件的作用

一．用户（user）

PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。一个角色可以被看成是一个数据库用户或者是一个数据库用户组。角色可以拥有数据库对象（例如，表和函数）并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。此外，还可以把一个角色中的成员资格授予给另一个角色，这样允许成员角色使用被赋予给另一个角色的权限。角色用户是没有区别的，一个用户也是一个角色。用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中看到的用户也都是相同的。在初始化数据库系统时，有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。

1.启动数据库

pg_ctl start -l /usr/local/postgresql/log/pg_server.log

2.创建用户和密码

create user highgo with password ’highgo123’;

3.修改用户密码

alter user highgo password ‘highgo123’;

4.对用户授权

alter user highgo createdb;

5.收回权限

alter user highgo nocreatedb;

6.增加操作其他数据库的权限

grant select on highgo_tb to highgo;

7.切换用户

\c - highgo

8.切换数据库

\c dbName

9.登录指定数据库

psql -U user -d dbName

10.显示所有用户

\du

或

select rolname from pg_roles;

11.显示所有数据库

\l

12.创建数据库

create database highgo_db;

13.删除数据库

drop database highgo_db;

二．表（table）

1.获取表结构

\d highgo_tb

2.创建表

create table highgo_tb (id int ,name varchar);

3.删除表

drop table highgo_tb;

4.重命名表

alter table highgo_tb rename to highgo1_tb;

5.在已有的表里添加字段

alter table highgo_tb add column name varchar;

6.删除表中字段

alter table highgo_tb drop column name;

7.重命名字段

alter table highgo_tb rename column name to name1;

8.查询表

select * from highgo_tb;

9.往表中添加数据

insert into highgo_tb values (1,’name’);

10.修改表中数据

update highgo_tb set name=’bb’ where id=1;

11.删除表中数据

delete from highgo_tb where id=1;

三.索引（index）

索引是数据库中一种快速查询数据的方法，好处是加快对表中记录的查找或排序，唯一索引起到唯一约束的作用，但是索引也是有代价的，增加了数据库的存储空间，在插入和修改数据时要花费较多的时间。PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。B-Tree，最常用的索引，B-Tree索引适合处理等值查询和范围查询；Hash，只能处理简单的的等值查询；GiST，不是一种单独的索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略；GIN，反转索引，它包含多个键的值，如数组等，支持用户定义的索引策略。

1.创建索引

create index index_highgo on highgo_tb (id);

2.多字段创建索引

create index index_highgo on highgo_tb (id, name);

3.条件索引

create index index_highgo on highgo_tb (id) where id<100;

4.删除索引

drop index if exists index_name;

5.查看索引的详细信息

\d+ pg_stat_user_indexes;

6.索引重命名

alter index if exists old_name rename to new_name;

7.查看索引的命令

\d+ index_name

四.序列（sequence）

序列，用于为行或者表生成唯一的标识符。

1.创建序列

(1)方法一：指定字段类型为serial；

(2)方法二：

create sequence sequence_name increment by 1 minvalue 1 nomaxvalue start with 1; 

使用

create table test1(id int not null default nextval('sequence_name'), ); 

2.查询序列

\d sequence_name 

3.数据有变后更新序列

指定序列从某个值从新开始计数

alter sequence sequence_name restart with 100; 

4.查看当前序列值

select currval('sequence_name'); 

5.查看下一个序列值

select nextval('sequence_name'); 

6.修改序列

重命名

alter sequence name rename to new_name; 

从200开始每次增量为2

alter sequence name increment by 2 restart with 200; 

7.删除序列

当有表字段使用到PG序列时，不能直接删除。

先删除表

drop table table_name; 

再删除序列

drop sequence sequence_name;

五. 配置文件

1.查询配置文件名称和位置

select name,setting from pg_settings where category=‘File Locations’;

2.postgresql.conf文件

该文件包含一些通用设置，比如内存分配，新建database的默认存储位置，PostgreSQL服务器的IP地址，日志的位置以及许多其他设置。

查询关键的设置

select name,context,unit,setting,boot_val,reset_val from pg_settings where name in('listen_addresses','max_connections','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem','port')order by context,name;

(1)share_buffers

用于缓存最近访问过的数据页的内存区大小，所有用户会话均可共享此缓存区。 一般来说越大越好，至少应该达到系统总内存的25%，但不宜超过8GB，因为超过后会出现“边际收益递减”效应。修改后，需重启postgreSQL服务。

(2)effective_cache_size

执行查询过程中可以使用的最大缓存，包括操作系统使用的部分以及PostgreSQL使用部分，系统并不会根据这个值来真实地分配这么多内存，但是规划器会根据这个值来判断系统能否提供执行查询过程中所需的内存。如果将此值设置得过小，远远小于系统真实可用内存量，那么可能会给规划器造成误导，让规划器认为系统可用内存有限，从而选择不使用索引而是走全表扫描（因为使用索引虽然速度快，但需要占用更多的中间内存）。在一台专用于运行PostgreSQL数据库服务的服务器上，建议将 effective_cache_size的值设为系统总内存的一半或者更多。修改后，设置可动态生效，执行重新加载即可。

(3)work_mem

此设置指定了用于执行排序，哈希关联，表扫描等操作的最大内存量。修改后，设置可动态生效，执行重新加载即可。

(4)mintenance_work_mem

此设置指定可用于vaccum操作（即清空已标记为“被删除”状态的记录）这类系统内部维护操作的内存总量。其值不应大于1GB, 修改后，设置可动态生效，执行重新加载可。

3.pg_hba.conf

该文件用于控制访问安全性，管理客户端对Postgresql服务器的访问权限，内容包括：允许哪些用户连接到哪个数据库，允许哪些IP或者哪个网段的IP连接到本服务器，以及指定连接时使用的身份验证模式。
身份验证模式，一般以下几种常用选项：ident、trust、md5以及password，1.1版本引入peer身份验证模式。ident和peer模式适用于Linux，Unix和Mac,不适用于windwos。reject模式，其作用是拒绝所有请求。

(1)trust模式，最不安全的身份验证模式，该模式允许用户“自证清白”，即可以不用密码就能连到数据库。

(2)md5模式，最常用的身份验证模式，要求连接发起者携带用md5算法加密的密码。

(3)password模式，不推荐，因为该模式使用明文密码进行身份验证，不安全。

(4)ident模式，该身份验证模式下，系统会将请求发起的操作系统用户映射PostgreSQL数据库内部用户，并以该内部用户的权限登录，且此时无需提供登录密码。操作系统用户与数据库内部用户之间的映射关系会记录在pg_ident.conf文件中。

(5)peer模式，使用发起端的操作系统名进行身份验证。

(6)reject模式，如果将+0.0.0./0 reject+规则放到+127.0.0.1/32 trust+的前面，那么此时本地用户全都无法连接，即使下面有规则允许也无法连接。