PostgreSQL基础1

什么是DBMS？

DBMS是用于存储和管理数据的软件。DBMS是在1960年代引入的，用于存储任何数据。它还提供对数据的操作，如数据的插入，删除和更新。DBMS系统还执行诸如定义，创建，修改和控制数据库的功能。它专门用于创建和维护数据，并使单个业务应用程序能够提取所需的数据。

什么是RDBMS？

关系数据库管理系统（RDBMS）是DBMS系统的高级版本。它在1970年代问世。与DBMS相比，RDBMS系统还允许组织更有效地访问数据。RDBMS是一个软件系统，用于仅存储需要以表格形式存储的数据。在这种系统中，数据以行和列的形式进行管理和存储，这被称为元组和属性。RDBMS是功能强大的数据管理系统，在世界范围内得到广泛使用

关键区别？

DBMS将数据存储为文件，而在RDBMS中，数据以表的形式存储。 DBMS支持单个用户，而RDBMS支持多个用户。 DBMS不支持客户端-服务器体系结构，但是RDBMS支持客户端-服务器体系结构。 DBMS具有较低的软件和硬件要求，而RDBMS具有较高的硬件和软件要求。 在DBMS中，数据冗余是常见的，而在RDBMS中，键和索引不允许数据冗余。

PostgreSQL

PostgreSQL 是一个先进的，开放源代码的[对象]-关系型数据库管理系统，它的主要目标是实现标准和可扩展性. PostgreSQL, 或者说是 Postgres, 试图把对 ANSI/ISO SQL标准的采用与修正结合起来.
对比其他的RDBMS, PostgreSQL以它对于对象-关系和\或关系型数据库功能，比如对于可靠事务，例如原子性，一致性，隔离性和持久性（ACID）的完全支持，这些东西的高度需求和集合的支持，以示其独特性.
由于强大的底层技术, Postgres对于高效的完成许多处理任务很有一手. 得益于其多版本并发控制 (MVCC)的实现，在没有读取锁的前提下也能达成并发, 这也同样确保了ACID的实施.
PostgreSQL是高度可编程的, 因而可以使用被称作“存储过程”的自定义程序进行扩展. 这些功能可以被创建用来简化一个写重复、复杂并且常常需要数据库操作的任务的执行.
虽然特性强大，但这个 DBMS并没有MySQL那么流行, 可还是有许多迷人的第三方工具和库被设计出来用于使得对PostgreSQL的操作简化. 如今通过许多操作系统默认的包管理器轻松的获取PostgreSQL已成为可能

PostgreSQL支持的数据类型

bigint: 有符号的八位整数
bigserial: 自增长的八位整数
bit [(n)]: 固定长度的位串
bit varying [(n)]: 可变长度的位串
boolean: 逻辑布尔值(true/false)
box: 在一个平面上的矩形框
bytea: 二进制数据("位数组")
character varying [(n)]: 可变长度的字符串
character [(n)]: 固定长度的字符串
cidr: IPv4 或者 IPv6 网络地址
circle: 平面上的一个圆
date: 日历日期 ( 年月日)
double precision: 双精度浮点数（8位）
inet: IPv4 或者 IPv6 主机地址
integer: 有符号的四位整数
interval [fields] [(p)]: 时间跨度
line: 平面上的一个无限长的直线
lseg: 平面上的一个线段
macaddr: MAC (媒体访问控制)地址
money: 货币金额
numeric [(p, s)]: 可选精度的精确数字
path: 一个平面上的几何路径
point: 一个平面上的几何点
polygon: 一个平面上的闭合的几何路径
real: 单精度浮点数(4 位)
smallint: 有符号的两位整数
serial: 自增长4位整数
text: 可变长度字符创
time [(p)] [without time zone]: 一天中的时间(无时区)
time [(p)] with time zone: 一天中的时间，包含时区
timestamp [(p)] [without time zone]: 日期和时间（没有时区）
timestamp [(p)] with time zone: 日期和时间，包含时区
tsquery: 文本搜索查询
tsvector: 文本搜索文档
txid_snapshot: 用户级事务ID快照
uuid: 通用的唯一标识符
xml: XML 数据

PostgreSQL的优点

1、标准支持 SQL 的开源关系型数据库: PostgreSQL 是一个开源的，自由(free)的，同时非常强大的关系型数据管理系统。
2、强大的社区: PostgreSQL 背后有热忱而经验丰富的社区，可以通过知识库和问答网站获取支持，全天候免费。
3、强大的第三方支持: 即使其本身功能十分强大，PostgreSQL 仍附带有许多强大的开源第三方工具来辅助系统的设计、管理和使用。
4、可扩展性: 可以用预先存储的流程来程序性扩展 PostgreSQL ，一个高级的关系型数据库理应如此。
5、面向对象: PostgreSQL 不只是一个关系型数据库，还是一个面向对象数据库——支持嵌套，及一些其他功能。

PostgreSQL的缺点：

性能: 对于简单而繁重的读取操作, PostgreSQL 会小题大作而可能会出现比同行（如MySQL）更低的性能。 普及: 根据该工具的性质，从普及度来说它还缺乏足够后台支撑，尽管有大量的部署——这可能会影响能够获得支持的容易程度。 托管: 由于上述因素的影响，要让主机或服务提供商提出使用PostgreSQL实例是很难的。

何时使用PostgreSQL？

数据完整性: 当可靠性和数据完整性是绝对必要而无需理由时，PostgreSQL是更好的选择。 复杂的自定义过程: 如果你需要你的数据库执行自定义过程，可扩展的PostgreSQL是更好的选择。 整合: 在将来，如果可能要把整个数据库系统迁移到另一个适当的解决方案（例如Oracle）中，PostgreSQL对于这种切换将是最兼容和易于操作的。 复杂的设计: 相比其他的开源和自由的 RDBMS（关系数据库管理系统）实现来说，对于复杂的数据库设计，PostgreSQL提供了大部分的功能和可能性，同时并没放弃其他有价值的地方。

何时不用 PostgreSQL？

速度: 如果你需要的只是快速的读取操作, PostgreSQL 不是为此而准备的工具。
简化体制:除非你需要绝对的数据完整性，原子性，一致性，隔离性，耐久性，或复杂的设计，PostgreSQL 对简化体制来说是杀手。
复制: 除非你愿意花不少时间，精力和资源，否则对于那些缺乏数据库和系统管理经验的人来说，实现与MySQL的（主从）复制可能不容易。

开发环境中的 PostgreSQL

在 Linux 上，使用 yum 或 apt 安装 PostgreSQL 后，sudo su postgres 切换到 postgres 用户，可以执行 psql 登入数据库
对于自己安装的数据库服务器（非RDS），如果需要开放远程登录，需要编辑数据目录下的 postgresql.conf 和 pg_hua.conf
对于使用 linux 的开发者，通常需要安装 postgresql 的开发包，这通常可以在主流的 linux 发行版的软件服务中找到，一般叫 postgresql-devel 或 postgresql-dev

什么是PSQL

psql是PostgreSQL安装后，系统自带的一个命令行交互式的客户端工具。这个命令行工具中，不仅可以执行SQL命令，还可以执行元命令。

什么是元命令

psql元命令是指以反斜线开头的命令，元命令使我们可以更便捷地管理数据库，比如列出中数据库各种对象的名称等，不需要书写sql语句，直接使用元命令就可以简单地查看。

PSQL命令行

psql 命令行内置在 postgresql 安装包中；
psql 命令行默认以系统当前用户身份登录本机，连接用户同名数据库；
进入 psql 命令行，可以使用 \? 查看内置的快捷指令；
在 psql 命令行中，可以通过 \! 在服务端执行shell命令；
在 psql 中执行 \d 可以查看 relations 列表，即用户表和视图；
在 psql 中执行 \l 可以查看数据库列表；
在 psql 中执行 \d 表名 可以查看指定表或视图的详细信息；
在 psql 中执行 \timing 可以开关查询计时；

常用元命令

\?:  查看元命令的帮助。
\l: 列出所有数据库。
\encoding: 查看字符集。
\encoding 字符集: 设置字符集。
\password user_name: 修改用户密码。
\x: 以列显示的开关。相当于mysql中的\G。执行一次为打开，再执行一次为关闭。
\timing on｜off: 设置是否显示执行时长。
\set AUTOCOMMIT on|off: 打开/关闭自动提交功能。
\conninfo: 显示连接信息。
\! : 执行shell命令。如：\! date， 输出当前日期。
\i filename: 执行filename文件中的sql语句，也可用psql -s filename。
\q: 退出psql命令行环境。
\e：打开文本编辑器。
## \pset
\pset border 0/1/2：设置执行结果的边框样式。
\pset border 0: 输出内容无边框，无任何|
\pset border 1: 边框只在内部有，无外边框
\pset border 2: 内外都有边框
\gexec：将当前查询缓冲区发送到服务器，然后将查询输出（如果有的话）的每一行的每一列都要作为要执行的SQL语句处理。这个用法可参考：Psql之\gexec命令。
## \c
\c: 查看当前数据库和用户。查询当前数据库等同于select current_database();查询当前用户等同于select current_user;
\c db_name: 进入指定的数据库。
\c database user_name : 切换到某个数据库下某个角色
## \d
\dn: 列出当前库下所有schema。
\d: 查看当前数据库下的所有表、视图和序列。
\dt: 只查看数据库中的所有表。
\d tb_name： 查看表结构定义。
\dt+ tb_name: 查看表大小等属性。
\db: 查看表空间。
\du: 列出所有用户及其用户权限。
\ds: 查看用户自定义序列。
\df: 查看用户自定义函数。

怎么查看查看元命令执行的具体sql语句？

1. 在psql启动命令行中加入-E。例：/usr/local/postgresql/bin/psql -h 127.0.01 -p 5432 -d postgres -E
2. 如果是已经登入服务器，可以通过 \set ECHO_HIDDEN on|off 开启或关闭输出执行SQL的功能。

常用sql语句

## 查看数据库版本。
select version();
## 查看表空间
select * from pg_tablespace;
## 大小相关的。
# 查看表空间大小
select pg_tablespace_size('pg_default');
# 查看各个表空间的大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;
# 查看DB大小
select pg_size_pretty(pg_database_size(db_name)); 
# 查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
# 查看表大小
select pg_size_pretty(pg_relation_size(table_name))
# 按占空间大小，顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
# 按占空间大小，顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1;
# 查看各数据库数据创建时间
select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;
## 索引相关
#创建索引。
CREATE INDEX index_name ON table_name (column_name, ...);
# 并发创建索引（在线创建索引）。
CREATE INDEX CONCURRENTLY
# 删除索引
DROP INDEX idx_name; 
drop index concurrently idx_name;
# 查看表的所有索引信息
select * from pg_indexes where tablename='student';
# 显示关于访问特定索引的I/O统计信息。
select * from pg_statio_all_indexes where relname='events';
# 显示索引类型
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'events');
# 显示索引大小
SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'events' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname; 
## 查看表的约束
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'events';
## 查看表所对应的数据文件路径与大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'events';
## 序列相关
# 查看序列
select * from information_schema.sequences where sequence_schema = 'public';
# 创建序列：
create sequence seq_user_camera_version increment by 1 minvalue 1 no maxvalue start with 1;
# 建表，并用上面的序列作为主键自增序列
CREATE TABLE public.user_camera_version (
    id int4 NOT NULL DEFAULT nextval('seq_user_camera_version'::regclass),
    user_id int4 NULL,
    user_type varchar(1) NULL,
    hardware_version varchar(100) NULL,
    software_version varchar(100) NULL,
    modify_date timestamp NULL,
    CONSTRAINT user_camera_version_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
) ;
## 函数相关
# 查看所有用户自定义函数。
\df 
或
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
# 查看函数定义
select oid,* from pg_proc where proname = 'insert_platform_action_exist'; 
select * from pg_get_functiondef(oid);
# 创建函数
CREATE FUNCTION add1(integer, integer) RETURNS integer

        AS 'select $1 + $2;'

        LANGUAGE SQL

        IMMUTABLE

RETURNS NULL ON NULL INPUT;
## 查看视图
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';
## 锁相关
# 查看锁等待信息。
select * from pg_locks where granted is not true;
# 查看会话。
select * from pg_stat_activity;
## 查看postgresql数据库启动时间
select pg_postmaster_start_time();
## 查询当前客户端的端口号
select inet_client_port();
# 查看与当前会话相关联的服务器进程ID
select pg_backend_pid();
# 查看配置文件最后一次载入时间
select pg_conf_load_time
## 查看参数文件
show config_file;
show hba_file;
show ident_file;
## 查看当前会话的参数值
show all;
## 查看参数值
select * from pg_settings;
## 查看某个参数值,比如参数work_mem
show work_mem
##修改某个参数值,比如参数work_mem
alter system set work_mem='8MB'
--使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件，加入你使用很多alter system命令后搞的一团糟，那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。
## 查看是否开启归档
show archive_mode;
## 运行日志相关
--运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。
show logging_collector;--启动日志收集
show log_directory;--日志输出路径
show log_filename;--日志文件名
show log_truncate_on_rotation;--当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名
show log_statement;--设置日志记录内容
show log_min_duration_statement;--运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置
## 用户和权限相关
# 创建用户
create user u2 with login CREATEROLE CREATEDB password 'u2';
create user u2  password 'u2';
# 修改数据库owner
ALTER DATABASE name OWNER TO new_owner;
# 设置用户对某个数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE database_name to username;
# 授予用户CONNECT到数据库的权限
GRANT CONNECT ON DATABASE database_name TO username;
# 授予public模式中所有表的所有权限给用户。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
# 授予public模式中所有序列的所有权限给用户:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;

部分实例

# 使用pg_database_size()查看数据库的大小
xxf=# select pg_database_size('xxf');
 pg_database_size 
------------------
          8831779
(1 row)

xxf=# select pg pg_database_size('xxf');

xxf=# select pg_size_pretty(pg_database_size('xxf'));
 pg_size_pretty 
----------------
 8625 kB
(1 row)

xxf=# \d tb_users
                                   Table "public.tb_users"
 Column |         Type          | Collation | Nullable |               Default                
--------+-----------------------+-----------+----------+--------------------------------------
 id     | bigint                |           | not null | nextval('tb_users_id_seq'::regclass)
 age    | integer               |           |          | 0
 name   | character varying(20) |           |          | 

# 查询current_timestamp
xxf=# select current_timestamp;
       current_timestamp       
-------------------------------
 2022-07-24 22:34:49.861384+08
(1 row)

# 使用current_timestamp(0)定义时间类型精度为0：（有时区）
xxf=# select current_timestamp(0);
   current_timestamp    
------------------------
 2022-07-24 22:35:08+08
(1 row)

# 使用current_timestamp(0)定义时间类型精度为0：（去掉时区）
xxf=# select current_timestamp(0)::timestamp without time zone;
  current_timestamp  
---------------------
 2022-07-24 22:35:45
(1 row)
xxf=# select cast (current_timestamp(0) as timestamp without time zone);
  current_timestamp  
---------------------
 2022-07-24 22:45:21
(1 row)

# 时间戳：
xxf=# select extract(epoch from now());
      extract      
-------------------
 1658673970.994191
(1 row)

# 设置数据库时区：
# 视图pg_timezone_names保存了所有可供选择的时区
# 查看看时区
select * from pg_timezone_names;
# 比如可以选择上海 Asia/Shanghai 或重庆 Asia/Chongqing， 最简单的直接 PRC:
# PRC：是中国的意思，这段代码是把默认时区设置成了中国标准时间。
xxf=# set zone 'PRC';
ERROR:  syntax error at or near "'PRC'"
LINE 1: set zone 'PRC';
                 ^
xxf=# set time zone 'PRC';
SET
xxf=# show time zone;
 TimeZone 
----------
 PRC
(1 row)
xxf=# select LOCALTIMESTAMP(0);
   localtimestamp    
---------------------
 2022-07-24 23:01:24
(1 row)

编辑于 2022-07-26 00:08