安装PostgreSQL

• Ubuntu安装:sudo apt install postgresql postgresql-contrib
• CentOS安装:sudo yum install postgresql-server postgresql-contrib
• 初始化数据库:sudo service postgresql initdb
• 启动数据库:sudo service postgresql start

创建数据库和用户

• 切换到postgres用户:sudo su postgres
• 进入交互式终端:psql
• 创建用户:CREATE USER myuser WITH PASSWORD 'password';
• 创建数据库:CREATE DATABASE mydb OWNER myuser;

表操作

• 创建表:CREATE TABLE mytable (id INT, name VARCHAR(50));
• 插入数据:INSERT INTO mytable VALUES(1, 'John');
• 查询数据:SELECT * FROM mytable;

数据类型

• 数值类型:INTEGER、BIGINT、DECIMAL等
• 时间日期类型:DATE、TIME、TIMESTAMP、INTERVAL
• 数组类型:INTEGER ARRAY、VARCHAR ARRAY
• JSON类型:JSON, JSONB

索引和视图

• 创建索引:CREATE INDEX myindex ON mytable (name);
• 创建视图:CREATE VIEW myview AS SELECT * FROM mytable;

事务处理

• 开始事务:BEGIN;
• insert/update操作
• 提交事务:COMMIT;
• 回滚事务:ROLLBACK;

高级数据类型

• 枚举类型:

CREATE TYPE rainbow AS ENUM ('红', '橙', '黄', '绿', '蓝', '靛', '紫');CREATE TABLE colors (id rainbow);

• 网络地址类型:

CREATE TABLE servers (  hostname text,  ip inet);

• 范围类型:

CREATE TABLE reservations (  room int,  during tsrange);INSERT INTO reservations VALUES  (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

高级数据类型

• 枚举类型:

CREATE TYPE rainbow AS ENUM ('红', '橙', '黄', '绿', '蓝', '靛', '紫');CREATE TABLE colors (  id serial PRIMARY KEY,  name rainbow);INSERT INTO colors (name) VALUES   ('红'), ('橙'), ('黄');

• 网络地址类型:

CREATE TABLE servers (  id serial PRIMARY KEY,   hostname text,  ip inet);INSERT INTO servers (hostname, ip) VALUES  ('server1', '192.168.0.100'),  ('server2', '10.23.200.150');

高级函数

• 聚集函数:count、sum、avg等
• 窗口函数:over()进行分组

SELECT id, COUNT(*) OVER() AS total FROM table;

• 生成序列:SERIAL数据类型

CREATE TABLE users (  id SERIAL PRIMARY KEY,  name text);

高级特性

• 继承:表继承

CREATE TABLE users (  ...) INHERITS (person);

• 规则系统:数据操作规则
• 触发器功能:触发特定事件响应

CREATE TRIGGER ...;

• 存储过程:复杂业务逻辑封装

CREATE FUNCTION myfunc() RETURNS void AS $BEGIN  ...END;$ LANGUAGE plpgsql;

• 权限控制:精细的权限设计
• 外键约束:保证数据一致性

管理操作

• 监控统计信息:pg_stat_*
• 参数调优:内存、并发连接等
• 日志记录:psycopg的日志接口
• 查询统计信息

SELECT * FROM pg_stats WHERE tablename = 'mytable';

• 解释执行计划:EXPLAIN

EXPLAIN SELECT * FROM mytable;

数据接口

• 数据流复制:streaming replication
• 多版本并发控制:MVCC