CoderWithAI LogoCoderWithAI

Azure Database for PostgreSQL 教程

1. 简介

Azure Database for PostgreSQL 是微软 Azure 云平台提供的一种托管 PostgreSQL 数据库服务。它结合了开源 PostgreSQL 数据库的强大功能和 Azure 云的灵活性、可扩展性和安全性。本教程将带你从基础开始，逐步深入了解 Azure Database for PostgreSQL 的各个方面。

2. 安装和环境配置

2.1 创建 Azure Database for PostgreSQL 实例

首先，你需要在 Azure 门户中创建一个 Azure Database for PostgreSQL 实例。

  1. 登录到 Azure 门户。
  2. 点击“创建资源”。
  3. 搜索“Azure Database for PostgreSQL”并选择它。
  4. 选择“单服务器”或“灵活服务器”（根据你的需求）。
  5. 填写必要的信息，如服务器名称、管理员用户名、密码等。
  6. 选择订阅、资源组和位置。
  7. 配置计算和存储选项。
  8. 点击“查看 + 创建”，然后点击“创建”。

2.2 连接到 Azure Database for PostgreSQL

你可以使用 psql 命令行工具或其他 PostgreSQL 客户端工具连接到 Azure Database for PostgreSQL。

psql -h <server-name>.postgres.database.azure.com -U <admin-username> -d <database-name>

输入管理员密码后，你将连接到数据库。

3. 创建第一个数据库

3.1 创建数据库

使用 CREATE DATABASE 语句创建一个新的数据库。

CREATE DATABASE myfirstdb;

3.2 切换数据库

使用 \c 命令切换到新创建的数据库。

\c myfirstdb;

4. 基本 SQL 语句

4.1 SELECT 语句

从表中检索数据。

SELECT * FROM employees;

4.2 INSERT 语句

向表中插入数据。

INSERT INTO employees (name, age, department) VALUES ('John Doe', 30, 'IT');

4.3 UPDATE 语句

更新表中的数据。

UPDATE employees SET age = 31 WHERE name = 'John Doe';

4.4 DELETE 语句

删除表中的数据。

DELETE FROM employees WHERE name = 'John Doe';

5. psql 命令行工具使用

5.1 常用命令

  • \l：列出所有数据库。
  • \c <database>：连接到指定数据库。
  • \dt：列出所有表。
  • \d <table>：显示表的结构。
  • \q：退出 psql。

6. 数据类型

6.1 常见数据类型

  • INTEGER：整数。
  • VARCHAR(n)：可变长度字符串。
  • DATE：日期。
  • TIMESTAMP：日期和时间。

6.2 示例

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    hire_date DATE
);

7. 表和约束

7.1 创建表

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 18),
    department VARCHAR(50)
);

7.2 约束

  • PRIMARY KEY：主键。
  • NOT NULL：非空约束。
  • CHECK：检查约束。

8. 索引

8.1 创建索引

CREATE INDEX idx_name ON employees (name);

8.2 删除索引

DROP INDEX idx_name;

9. 视图

9.1 创建视图

CREATE VIEW young_employees AS
SELECT * FROM employees WHERE age < 30;

9.2 使用视图

SELECT * FROM young_employees;

10. 模式（Schema）

10.1 创建模式

CREATE SCHEMA hr;

10.2 在模式中创建表

CREATE TABLE hr.employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

11. 事务和 ACID

11.1 事务示例

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

12. 子查询

12.1 子查询示例

SELECT name FROM employees
WHERE department IN (SELECT department FROM departments WHERE location = 'New York');

13. 连接（JOIN）

13.1 INNER JOIN

SELECT employees.name, departments.name AS department
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

14. 集合操作（UNION, INTERSECT, EXCEPT）

14.1 UNION

SELECT name FROM employees
UNION
SELECT name FROM contractors;

15. 窗口函数

15.1 窗口函数示例

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

16. 公共表表达式（CTE）

16.1 CTE 示例

WITH high_salaries AS (
    SELECT name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM high_salaries;

17. 范式化

17.1 范式化示例

将数据分解为多个表，以减少冗余。

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER REFERENCES departments(id)
);

18. ER 图

18.1 ER 图示例

使用 ER 图表示实体和关系。

+-------------------+       +-------------------+
|   Departments     |       |   Employees       |
|-------------------|       |-------------------|
| id (PK)           |<------| department_id (FK) |
| name              |       | name              |
+-------------------+       +-------------------+

19. 主键和外键

19.1 主键

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

19.2 外键

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER REFERENCES departments(id)
);

20. 继承

20.1 继承示例

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE managers (
    department VARCHAR(100)
) INHERITS (employees);

21. 分区表

21.1 分区表示例

CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

22. 查询计划分析

22.1 EXPLAIN 示例

EXPLAIN SELECT * FROM employees WHERE age > 30;

23. 索引优化

23.1 索引优化示例

CREATE INDEX idx_age ON employees (age);

24. 查询优化

24.1 查询优化示例

SELECT name FROM employees WHERE age > 30;

25. 配置调优

25.1 配置调优示例

ALTER SYSTEM SET work_mem = '64MB';

26. VACUUM 和 ANALYZE

26.1 VACUUM 示例

VACUUM employees;

26.2 ANALYZE 示例

ANALYZE employees;

27. 存储过程和函数

27.1 存储过程示例

CREATE OR REPLACE FUNCTION get_employee_count() RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM employees;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

28. 触发器

28.1 触发器示例

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

29. 事件触发器

29.1 事件触发器示例

CREATE EVENT TRIGGER log_ddl_changes
ON ddl_command_start
EXECUTE FUNCTION log_ddl_changes();

30. 全文搜索

30.1 全文搜索示例

SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('search');

31. JSON 和 JSONB 支持

31.1 JSON 示例

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSON
);

32. 锁机制

32.1 锁机制示例

BEGIN;
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;
UPDATE employees SET salary = salary + 100 WHERE id = 1;
COMMIT;

33. MVCC（多版本并发控制）

33.1 MVCC 示例

MVCC 是 PostgreSQL 的默认并发控制机制，确保事务隔离。

34. 死锁处理

34.1 死锁处理示例

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

35. 隔离级别

35.1 隔离级别示例

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

36. pg_dump 和 pg_restore

36.1 pg_dump 示例

pg_dump -h <server-name>.postgres.database.azure.com -U <admin-username> -d <database-name> -f backup.sql

36.2 pg_restore 示例

pg_restore -h <server-name>.postgres.database.azure.com -U <admin-username> -d <database-name> backup.sql

37. 连续归档和时间点恢复（PITR）

37.1 PITR 示例

配置连续归档和时间点恢复。

38. 复制和流复制

38.1 复制示例

配置主从复制。

39. 故障转移和高可用性

39.1 高可用性示例

配置高可用性集群。

40. 用户和角色管理

40.1 用户管理示例

CREATE USER john WITH PASSWORD 'password';

41. 权限控制

41.1 权限控制示例

GRANT SELECT ON employees TO john;

42. SSL 连接

42.1 SSL 连接示例

配置 SSL 连接。

43. 行级安全性

43.1 行级安全性示例

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

44. 常用扩展介绍（如 PostGIS, pgcrypto）

44.1 扩展示例

CREATE EXTENSION postgis;

45. 自定义扩展开发

45.1 自定义扩展示例

开发自定义扩展。

46. 外部数据包装器（FDW）

46.1 FDW 示例

配置外部数据包装器。

47. 日志管理

47.1 日志管理示例

配置日志记录。

48. 性能监控

48.1 性能监控示例

使用 Azure Monitor 监控性能。

49. 连接池（如 PgBouncer）

49.1 连接池示例

配置 PgBouncer。

50. 数据库集群管理

50.1 集群管理示例

管理数据库集群。

51. JDBC 和 ODBC 驱动

51.1 JDBC 示例

使用 JDBC 连接数据库。

52. ORM 工具（如 Hibernate, SQLAlchemy）

52.1 ORM 示例

使用 Hibernate 或 SQLAlchemy。

53. 连接池最佳实践

53.1 最佳实践示例

配置连接池最佳实践。

54. 并行查询

54.1 并行查询示例

配置并行查询。

55. 物化视图

55.1 物化视图示例

CREATE MATERIALIZED VIEW mv_employees AS
SELECT * FROM employees;

56. 数据仓库设计

56.1 数据仓库示例

设计数据仓库。

57. 与 Hadoop 生态系统集成

57.1 集成示例

集成 Hadoop 生态系统。

58. Amazon RDS for PostgreSQL

58.1 RDS 示例

配置 Amazon RDS for PostgreSQL。

59. Google Cloud SQL for PostgreSQL

59.1 Cloud SQL 示例

配置 Google Cloud SQL for PostgreSQL。

60. 主要版本特性介绍

60.1 版本特性示例

介绍 PostgreSQL 主要版本特性。

61. 升级策略

61.1 升级策略示例

制定升级策略。

62. 从其他数据库迁移到 PostgreSQL

62.1 迁移示例

迁移数据到 PostgreSQL。

63. 设计和实现一个博客系统数据库

63.1 博客系统示例

设计和实现博客系统数据库。

64. 构建一个电商平台的数据模型

64.1 电商平台示例

构建电商平台数据模型。

65. 开发一个时间序列数据分析应用

65.1 时间序列示例

开发时间序列数据分析应用。

66. pgAdmin 和其他 GUI 工具

66.1 pgAdmin 示例

使用 pgAdmin 管理数据库。

67. 常用扩展和工具推荐

67.1 扩展和工具示例

推荐常用扩展和工具。

68. 社区资源和文档

68.1 社区资源示例

访问 PostgreSQL 社区资源和文档。

通过本教程，你已经掌握了 Azure Database for PostgreSQL 的基本和高级功能。继续探索和实践，你将能够构建强大的数据库应用。

← Previous: Amazon RDS for PostgreSQL 数据库管理教程Next: Google Cloud SQL for PostgreSQL 入门教程 →