一、PostgreSQL简介

pg数据库 show processlist pg数据库int类型_数据库

  1. 高度可扩展性：PostgreSQL允许用户通过添加自定义函数、数据类型和索引等方式扩展其功能。此外，它还支持分区表、流复制和并行查询等高级功能，以满足处理大量数据的需求。
  2. ACID兼容性：PostgreSQL支持ACID（原子性、一致性、隔离性和持久性）事务，确保在任何情况下都可以维护数据的完整性和一致性。
  3. 多版本并发控制（MVCC）：PostgreSQL使用多版本并发控制（MVCC）来管理并发访问。MVCC可确保对数据的并发访问不会导致数据损坏或丢失。
  4. 丰富的数据类型：PostgreSQL内置了丰富的数据类型，如任意精度的数值、无限制长度的文本、几何图元、IP地址、数组等；同时还允许用户定义基于正规SQL类型的新类型，让数据库自身理解复杂数据，自定义类型中还可以包含继承关系。
  5. 编程接口：PostgreSQL同样拥有大量的编程接口供用户开发使用，如ODBC、JDBC（Java）、Libpq（C/C++）等。
  6. 可定制性：PostgreSQL拥有广泛的编程语言支持来实现函数功能，包括内置的PL/pgSQL过程语言，PL/Perl、plPHP、PL/Python、PL/Ruby、PL/Tcl等脚本语言，以及Java、C/C++等高级编程语言。函数的输出是一系列行类型的集合，可以在查询中当做表来使用，函数也可以被定义成以创建者或者调用者的身份运行。
  7. 索引手段：用户可以自定义索引方法或者使用内置B-Tree索引、Hash表索引、GiST索引、GIN索引。GiST索引不是某种特定的索引类型，而是一种通用索引基础结构，可以在这种结构上实现很多不同的索引策略。
  8. 安全性：PostgreSQL支持SSL连接、客户端认证和细粒度的访问控制等安全特性。
  9. GIS支持：PostgreSQL具有强大的GIS（地理信息系统）支持，可以存储和处理地理空间数据，并提供了丰富的GIS函数和操作符。
  10. JSON和XML支持：PostgreSQL提供了对JSON和XML数据类型的内建支持，包括查询和修改JSON数据的功能。
  11. 异步复制：PostgreSQL支持异步复制功能，可以在不同的数据库服务器之间实现数据同步。
  12. 在线备份和恢复：PostgreSQL支持在线备份和恢复功能，可以在不中断数据库服务的情况下进行备份和恢复操作。
  13. 嵌套事务：PostgreSQL支持嵌套事务，这使得应用程序可以处理更复杂的业务逻辑。
  14. 时间点恢复：PostgreSQL提供了时间点恢复功能，可以根据指定的时间点恢复数据库到该状态。
  15. 表空间机制：PostgreSQL支持表空间机制，可以将表和索引分散到不同的物理存储设备上以提高性能和可管理性。
  16. 性能调优器：PostgreSQL提供了预写日志容错技术、反向索引检索、表达式索引、部分索引、位图索引扫描等功能来提高查询性能。
  17. 嵌套查询和子查询：PostgreSQL支持嵌套查询和子查询，允许在查询中使用复杂的逻辑表达式和条件语句。
  18. 强大的触发器和规则系统：PostgreSQL支持触发器和规则系统，可以根据定义的条件自动执行相应的操作或触发其他事件。
  19. 国际化支持：PostgreSQL支持国际字符集和多字节字符编码，对格式化、排序、大小写敏感提供本地化支持。
  20. 社区支持和企业级支持：PostgreSQL有一个活跃的开发者社区和丰富的文档资源，同时也有一些公司提供企业级支持和培训服务。

二、使用场景

 

pg数据库 show processlist pg数据库int类型_数据库_02

pg数据库 show processlist pg数据库int类型_postgresql_03

三、PG的常见使用命令 

1、连接数据库


登录数据库 ,-d为指定登录的数据库
 psql -h 127.0.0.1 -p 7400  -U postgres -d postgres
查看数据库列表：\l 或者 SELECT datname FROM pg_database;
查看用户或权限：\du 或特定用户 \du+ username
连接数据库：\c cascade;

2、创建表空间


CREATE TABLESPACE 【spacename】 LOCATION '[space_path]';
---- CREATE TABLESPACE fjbdjl LOCATION '/mnt/data1/postgres/data/test'

3、创建数据库


CREATE DATABASE 【db_name】 OWNER [user] ENCODING 'UTF8' TABLESPACE [space_name];
--- CREATE DATABASE cascade OWNER cascade ENCODING 'UTF8' TABLESPACE test;

4、创建schema


连接数据库后创建schema:
create schema cascade
连接数据库后创建schema时指定一个所有者：
CREATE SCHEMA cascade AUTHORIZATION cascade;

5、创建用户

5.1.  创建普通用户


CREATE USER [user] WITH PASSWORD '[password]';     
 --- CREATE USER cascade WITH PASSWORD 'password';

5.2. 设置超级用户权限


CREATE USER [user]  WITH PASSWORD '[password]' SUPERUSER;
     --CREATE USER cascade WITH PASSWORD 'password' SUPERUSER;

5.3. 指定特定的数据库和模式访问权限


CREATE USER  [user]   WITH PASSWORD '[password]' CREATEDB CREATEROLE;
--- CREATE USER cascade WITH PASSWORD 'password' CREATEDB CREATEROLE;

5.4. 删除用户


DROP USER cascade;
--- DROP USER cascade;

5.5、授予用户登录权限


ALTER USER [user] LOGIN;
    -- ALTER USER cascade LOGIN;

6、授权与回收(直接授权)

6.1.授权schema的使用权限给用户


GRANT USAGE ON SCHEMA cascade TO cascade;

6.2. 授权schema的表的CRUD权限给用户


GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA cascade TO cascade;

6.3 授权数据库给用户


GRANT ALL PRIVILEGES ON DATABASE 【db_name】TO 【user】; 
-- GRANT ALL PRIVILEGES ON DATABASE test TO cascade;

6.4 授权用户在schema下创建表的权限

授权单个schema：


GRANT CREATE ON SCHEMA [schema_name] TO [user]; 
-- GRANT CREATE ON SCHEMA cascade  TO cascade;

6.5 授权数据库中所有的schema,包括已创建schema和未创建schema


GRANT CREATE ON ALL SCHEMAS IN DATABASE [db_name] TO  [user]; 
-- GRANT CREATE ON ALL SCHEMAS IN DATABASE "cascade" TO cascade;

6.6【权限回收】：回收表的权限(表拥有者)


REVOKE SELECT ON abc FROM cascade; 
-- REVOKE SELECT,UPDATE,DELETE,INSERT ON [table_name] FROM [user];

6.7【权限回收】：回收schema的权限(schema拥有者)


REVOKE ALL ON SCHEMA [schema_name] FROM [user];
 -- REVOKE ALL ON SCHEMA cascade FROM cascade;

6.8、修改schema的拥有者


ALTER SCHEMA [schema] OWNER TO [user]; 
--- ALTER SCHEMA cascade OWNER TO cascade;

7、 数据库查看相关操作

7.1 查询数据库拥有者


SELECT datname, pg_catalog.pg_get_userbyid(datdba) FROM pg_catalog.pg_database WHERE datname = '【db_name】'; 
-- SELECT datname, pg_catalog.pg_get_userbyid(datdba) FROM pg_catalog.pg_database WHERE datname = 'cascade';

7.2  查询schema拥有者


SELECT nspname,pg_catalog.pg_get_userbyid(nspowner) FROM pg_catalog.pg_namespace WHERE nspname = '[schema_name]';
 --- SELECT nspname,pg_catalog.pg_get_userbyid(nspowner) FROM pg_catalog.pg_namespace WHERE nspname = 'cascade';

7.3 查看表空间


SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

7.4 查看schema下的表


SELECT table_name FROM information_schema.tables WHERE table_schema = '［schema_name］';
 —-SELECT table_name FROM information_schema.tables WHERE table_schema = 'cascade'

7.5 查看当前版本


SELECT version();

7.6 查看当前用户


SELECT current_user;

四、PostgreSQL的一些主要特性

  1. 高度可扩展性：PostgreSQL允许用户通过添加自定义函数、数据类型和索引等方式扩展其功能。此外，它还支持分区表、流复制和并行查询等高级功能，以满足处理大量数据的需求。
  2. ACID兼容性：PostgreSQL支持ACID（原子性、一致性、隔离性和持久性）事务，确保在任何情况下都可以维护数据的完整性和一致性。
  3. 多版本并发控制（MVCC）：PostgreSQL使用多版本并发控制（MVCC）来管理并发访问。MVCC可确保对数据的并发访问不会导致数据损坏或丢失。
  4. 丰富的数据类型：PostgreSQL内置了丰富的数据类型，如任意精度的数值、无限制长度的文本、几何图元、IP地址、数组等；同时还允许用户定义基于正规SQL类型的新类型，让数据库自身理解复杂数据，自定义类型中还可以包含继承关系。
  5. 编程接口：PostgreSQL同样拥有大量的编程接口供用户开发使用，如ODBC、JDBC（Java）、Libpq（C/C++）等。
  6. 可定制性：PostgreSQL拥有广泛的编程语言支持来实现函数功能，包括内置的PL/pgSQL过程语言，PL/Perl、plPHP、PL/Python、PL/Ruby、PL/Tcl等脚本语言，以及Java、C/C++等高级编程语言。函数的输出是一系列行类型的集合，可以在查询中当做表来使用，函数也可以被定义成以创建者或者调用者的身份运行。
  7. 索引手段：用户可以自定义索引方法或者使用内置B-Tree索引、Hash表索引、GiST索引、GIN索引。GiST索引不是某种特定的索引类型，而是一种通用索引基础结构，可以在这种结构上实现很多不同的索引策略。
  8. 安全性：PostgreSQL支持SSL连接、客户端认证和细粒度的访问控制等安全特性。
  9. GIS支持：PostgreSQL具有强大的GIS（地理信息系统）支持，可以存储和处理地理空间数据，并提供了丰富的GIS函数和操作符。
  10. JSON和XML支持：PostgreSQL提供了对JSON和XML数据类型的内建支持，包括查询和修改JSON数据的功能。
  11. 异步复制：PostgreSQL支持异步复制功能，可以在不同的数据库服务器之间实现数据同步。
  12. 在线备份和恢复：PostgreSQL支持在线备份和恢复功能，可以在不中断数据库服务的情况下进行备份和恢复操作。
  13. 嵌套事务：PostgreSQL支持嵌套事务，这使得应用程序可以处理更复杂的业务逻辑。
  14. 时间点恢复：PostgreSQL提供了时间点恢复功能，可以根据指定的时间点恢复数据库到该状态。
  15. 表空间机制：PostgreSQL支持表空间机制，可以将表和索引分散到不同的物理存储设备上以提高性能和可管理性。
  16. 性能调优器：PostgreSQL提供了预写日志容错技术、反向索引检索、表达式索引、部分索引、位图索引扫描等功能来提高查询性能。
  17. 嵌套查询和子查询：PostgreSQL支持嵌套查询和子查询，允许在查询中使用复杂的逻辑表达式和条件语句。
  18. 强大的触发器和规则系统：PostgreSQL支持触发器和规则系统，可以根据定义的条件自动执行相应的操作或触发其他事件。
  19. 国际化支持：PostgreSQL支持国际字符集和多字节字符编码，对格式化、排序、大小写敏感提供本地化支持。
  20. 社区支持和企业级支持：PostgreSQL有一个活跃的开发者社区和丰富的文档资源，同时也有一些公司提供企业级支持和培训服务。

1>修改密码

#修改 sales_user 用户的密码。你可以选择以下任一方式修改密码：
ALTER USER sales_user WITH PASSWORD '123456@abc.COM';


#赋予超级用户权限
ALTER USER   sales_user  WITH SUPERUSER;


#取消超级用户权限
ALTER USER test WITH NOSUPERUSER;

2>PostgreSQL备份的几种小技巧

(1)使用pg_dump做导出时，比较常见下面的两种使用方式：

  • 仅导出结构

-s,--schema-only dump only the schema, no data


  • 仅导出数据

-a,--data-only dump only the data, not the schema

这两种方式的比较熟知，但pg_dump还有一个section子项，能控制导出对象的顺序。

  • section=pre-data，只导出对象DDL排除索引
  • section=data，导出数据
  • section=post-data，最后进行约束校验和索引创建

post-data导出包含索引、规则、触发器、唯一约束、主键、外键约束，不包含检查约束和非空约束。

巧用清单文件

使用pg_dump/pg_restore的自定义格式时，可以通过清单文件来恢复出特定的数据库对象，例如仅恢复函数或仅恢复表结构等。

清单文件需要导出时使用-Fc格式

pg_dump -Fc sales_db -f mydatabase.db

pg_dump -Fc mydb -f mydatabase.db

(2)备份

2.1数据库备份分单数据库备份，使用 pg_dump 命令；所有数据库备份，使用 pg_dumpall 命令


postgresql备份和恢复_c# postgres备份 csdn-CSDN博客


3.在PostgreSQL中生成日期序列

在PostgreSQL中，生成日期序列的方法更为简洁，可以使用 generate_series 函数来生成日期序列：

SELECT generate_series( '2024-01-01'::date, '2024-12-31'::date, '1 day'::interval )::date AS date

pg数据库 show processlist pg数据库int类型_数据_04