标签：PostgreSQL -- CREATE 备忘录 balance my id SELECT

安装（Docker）

拉取PostgreSQL镜像

sudo docker pull postgres

运行PostgreSQL容器

运行PostgreSQL容器，指定数据库名称、用户和密码：

sudo docker run --name my_postgres -e POSTGRES_USER=myuser -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -d postgres

sudo docker ps

进入PostgreSQL容器

sudo docker exec -it my_postgres psql -U myuser -d mydb

使用psql命令行客户端

CREATE TABLE example (id SERIAL PRIMARY KEY, name VARCHAR(50));

查看所有表：

\dt

运维管理部分

基础知识-SQL数据库部分

创建数据库

CREATE DATABASE my_database;

修改数据库配置

ALTER DATABASE my_database WITH CONNECTION LIMIT 10;

删除数据库

DROP DATABASE my_database;

创建数据库模板

数据库模板可以用于创建新数据库。使用 CREATE DATABASE 语句时，可以指定模板。

CREATE DATABASE new_database TEMPLATE my_template;

管理数据库的扩展

使用扩展访问外部数据源

可以使用 CREATE EXTENSION 命令来安装扩展。例如，安装 hstore 扩展：

CREATE EXTENSION hstore;

数据预热扩展

使用 pg_prewarm 扩展可以让你预加载数据到缓存中：

CREATE EXTENSION pg_prewarm;

监控共享缓冲区

使用 pg_buffercache 扩展监控共享缓冲区的使用情况：

CREATE EXTENSION pg_buffercache;

WAL 日志解析扩展

使用 pg_walinspect 扩展可以帮助你分析 WAL 日志：

CREATE EXTENSION pg_walinspect;

数据库实例操作

-- 连接到 PostgreSQL
\c postgres;

-- 创建新数据库
CREATE DATABASE example_db;

-- 修改数据库配置
ALTER DATABASE example_db WITH CONNECTION LIMIT 5;

-- 连接到新数据库
\c example_db;

-- 创建扩展
CREATE EXTENSION hstore;

-- 使用 pg_buffercache 监控
CREATE EXTENSION pg_buffercache;

-- 查询缓冲区缓存信息
SELECT * FROM pg_buffercache;

管理数据库对象

创建数据库

CREATE DATABASE my_database;
\c my_database;

创建模式

CREATE SCHEMA my_schema;

创建表

CREATE TABLE my_schema.my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

查看表的结构

\d my_schema.my_table;

数据类型

INT：整数
VARCHAR(n)：变长度字符串
TIMESTAMP：时间戳

表的基本操作

插入数据

INSERT INTO my_schema.my_table (name, age) VALUES ('Alice', 30);
INSERT INTO my_schema.my_table (name, age) VALUES ('Bob', 25);

查询数据

SELECT * FROM my_schema.my_table;

更新数据

UPDATE my_schema.my_table SET age = 31 WHERE name = 'Alice';

删除数据

DELETE FROM my_schema.my_table WHERE name = 'Bob';

数据的约束条件

PRIMARY KEY：主键约束
NOT NULL：非空约束
UNIQUE：唯一约束
CHECK：检查约束

使用临时表

临时表在会话结束后自动删除：

CREATE TEMP TABLE temp_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);

在查询时使用索引

CREATE INDEX idx_name ON my_schema.my_table (name);

索引的维护

REINDEX INDEX idx_name;

索引（Index）该如何理解
将数据库（DATABASE）想象成一个场馆，把索引（Index）当成一个告示牌，它指定了某个地方（数据表table）在哪里，你看到了就找到了地方。

视图的创建与使用

创建视图以简化查询：

CREATE VIEW my_view AS
SELECT name, age FROM my_schema.my_table WHERE age > 20;

视图（View）该如何理解
Linux系统上的软链接（ln）指令。都是简化了路径，指向某个结果。

序列的使用

创建一个序列：

CREATE SEQUENCE my_sequence START 1;

在插入数据时使用序列：

INSERT INTO my_schema.my_table (id, name, age)
VALUES (nextval('my_sequence'), 'Charlie', 28);

序列冲突解决方法

检查序列的当前值：

SELECT last_value, is_called FROM my_sequence;

last_value 是序列最后生成的值。
is_called 表示这个值是否已经被使用过。

手动调整序列：
如果发现序列的值小于或等于表中最大 id 值，则需要更新序列的值。您可以使用以下 SQL 查询来获取表中 id 的最大值：

SELECT MAX(id) FROM my_schema.my_table;

然后，您可以使用 setval 函数更新序列的值。例如：

SELECT setval('my_sequence', (SELECT MAX(id) FROM my_schema.my_table) + 1);

这将把序列的值设置为当前最大 id 加一，从而避免冲突。

并行查询的工作原理

并行查询允许数据库在多个处理单元上并行执行查询操作。这意味着可以同时处理多个数据块，从而加速查询响应时间。

何时使用并行查询？

大数据集：当表中有大量数据时。
复杂查询：当查询涉及大量计算和数据操作时。
高可用性需求：需要快速响应时间的实时系统。

查询计划分析

在使用并行查询之前，可以通过查询计划查看执行的细节。

EXPLAIN ANALYZE SELECT * FROM my_schema.my_table WHERE name = 'value';

这将显示查询的执行计划，包括是否使用了并行查询。

并行顺序扫描

当执行并行查询时，可以使用顺序扫描来提高性能。

SET max_parallel_workers_per_gather = 4;
SELECT * FROM my_schema.my_table;

并行索引扫描

使用并行索引扫描可以加快对索引的访问速度。

my_database=# CREATE INDEX idx_name ON my_schema.my_table(name);
SET max_parallel_workers_per_gather = 4;
SELECT * FROM my_schema.my_table WHERE name = 'value';

并行位图扫描

并行位图扫描结合了顺序扫描和索引扫描的优势。

SET max_parallel_workers_per_gather = 4;
SELECT * FROM my_schema.my_table WHERE name IN ('value1', 'value2');

嵌套循环连接

在复杂的查询中，可以使用并行的嵌套循环连接来提高连接性能。

SET max_parallel_workers_per_gather = 4;
SELECT a.*, b.* FROM table_a a JOIN table_b b ON a.id = b.a_id;

哈希连接

哈希连接也可以并行执行，适用于较大的连接关系。

SET max_parallel_workers_per_gather = 4;
SELECT a.*, b.* FROM table_a a JOIN table_b b ON a.id = b.a_id USING (id);

并行查询的限制

硬件限制：并行查询的效率受限于可用的 CPU 和内存资源。
查询复杂性：某些复杂查询可能不会从并行处理中获得显著的性能提升。
数据分布：数据的分布和存储方式会影响并行查询的效率。

事务与并发控制

事务简介

事务是指一组操作，它们作为一个单独的工作单元执行。事务有以下特征：

原子性：事务中的所有操作要么全部成功，要么全部失败。
一致性：事务执行前后的数据状态是一致的。
隔离性：多个事务之间相互独立，互不干扰。
持久性：一旦事务完成，其结果是永久性的。

事务的特征

事务的控件语句：如 BEGIN, COMMIT, ROLLBACK。
使用事务的控件语句：
```
BEGIN;
-- 进行操作
COMMIT; -- 提交事务
```
如果发生错误，可以使用 ROLLBACK 来撤销操作。

事务的并发

事务允许多个用户同时对数据库进行操作。为确保数据的一致性和完整性，数据库系统实现了并发控制机制。

隔离级别

隔离级别定义了事务之间的可见性。常见的隔离级别有：

读未提交（Read Uncommitted）
读已提交（Read Committed）
可重复读（Repeatable Read）
串行化（Serializable）

事务的脏读

脏读是指一个事务读取到了另一个未提交事务的数据。为了避免脏读，可以设置隔离级别为“读已提交”。

表级锁与行级锁

表级锁：锁定整个表，适用于需要对整个表进行操作的情况。
行级锁：锁定特定的行，适用于需要高并发的场景。

死锁

死锁是指两个或多个事务相互等待对方释放锁，从而造成程序无法继续执行。避免死锁的方法包括：

确保锁定顺序：始终按照相同的顺序请求锁。
设置超时：设定请求锁的超时时间。

如何避免死锁

资源分配策略：尽量减少事务持有锁的时间。
使用乐观并发控制：在提交时检查数据是否被其他事务更改。

实例教学

示例 1：创建事务

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL NOT NULL
);

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

示例 2：使用 ROLLBACK

BEGIN;
INSERT INTO accounts (id, balance) VALUES (2, 1000);
-- 发生错误
ROLLBACK; -- 撤销所有操作

示例 3：设置隔离级别

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM accounts WHERE id = 1;
COMMIT;

应用程序开发

开发第一个 PL/pgSQL 程序

CREATE OR REPLACE FUNCTION hello_world()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT hello_world();

使用 PL/pgSQL 的基本数据类型

CREATE OR REPLACE FUNCTION basic_types_example()
RETURNS VOID AS $$
DECLARE
    v_integer INTEGER := 10;
    v_text TEXT := 'PostgreSQL';
    v_decimal DECIMAL := 20.5;
BEGIN
    RAISE NOTICE 'Integer: %, Text: %, Decimal: %', v_integer, v_text, v_decimal;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT basic_types_example();

使用 PL/pgSQL 的高级数据类型

CREATE OR REPLACE FUNCTION array_example()
RETURNS VOID AS $$
DECLARE
    v_array INTEGER[] := ARRAY[1, 2, 3, 4, 5];
    v_sum INTEGER := 0;
    v_element INTEGER;
BEGIN
    FOREACH v_element IN ARRAY v_array
    LOOP
        v_sum := v_sum + v_element;
    END LOOP;

    RAISE NOTICE 'Sum of array elements: %', v_sum;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT array_example();

在 PL/pgSQL 中使用条件判断

CREATE OR REPLACE FUNCTION conditional_example(v_score INTEGER)
RETURNS TEXT AS $$
BEGIN
    IF v_score >= 90 THEN
        RETURN 'Excellent';
    ELSIF v_score >= 75 THEN
        RETURN 'Good';
    ELSE
        RETURN 'Needs Improvement';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT conditional_example(85);

在 PL/pgSQL 中使用循环

CREATE OR REPLACE FUNCTION loop_example()
RETURNS VOID AS $$
DECLARE
    v_counter INTEGER;
BEGIN
    FOR v_counter IN 1..5
    LOOP
        RAISE NOTICE 'Current counter: %', v_counter;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT loop_example();

在 PL/pgSQL 中使用游标

CREATE OR REPLACE FUNCTION cursor_example()
RETURNS VOID AS $$
DECLARE
    v_cursor CURSOR FOR SELECT id, balance FROM accounts;
    v_id INTEGER;
    v_balance DECIMAL;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_id, v_balance;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Account ID: %, Balance: %', v_id, v_balance;
    END LOOP;

    CLOSE v_cursor;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT cursor_example();

处理系统预定义例外

CREATE OR REPLACE FUNCTION exception_handling_example()
RETURNS VOID AS $$
BEGIN
    -- 故意引发一个除零错误
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Caught a division by zero error!';
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT exception_handling_example();

处理用户自定义例外

CREATE OR REPLACE FUNCTION custom_exception_example(v_value INTEGER)
RETURNS VOID AS $$
BEGIN
    IF v_value < 0 THEN
        RAISE EXCEPTION 'Value cannot be negative!';
    ELSE
        RAISE NOTICE 'Value is: %', v_value;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT custom_exception_example(-1);

PL/pgSQL 编程综合案例

以下示例展示了一个综合案例，创建一个账户管理系统，支持账户的创建和查询。

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION create_account(v_balance DECIMAL)
RETURNS VOID AS $$
BEGIN
    INSERT INTO accounts (balance) VALUES (v_balance);
    RAISE NOTICE 'Account created with balance: %', v_balance;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_balance(v_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_balance DECIMAL;
BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE id = v_id;

    IF v_balance IS NULL THEN
        RAISE EXCEPTION 'Account not found!';
    END IF;

    RETURN v_balance;
END;
$$ LANGUAGE plpgsql;

-- 创建账户
SELECT create_account(1000);

-- 查询账户余额
SELECT get_balance(1);

存储过程与触发器的使用

创建和使用存储过程

存储过程是一个可以执行多条 SQL 语句的程序单元。

CREATE OR REPLACE PROCEDURE add_account(IN p_balance DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO accounts (balance) VALUES (p_balance);
    RAISE NOTICE 'Account created with balance: %', p_balance;
END;
$$;

-- 调用存储过程
CALL add_account(1500);

创建和使用存储函数

存储函数与存储过程类似，但它会返回一个值。

CREATE OR REPLACE FUNCTION get_account_balance(p_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_balance DECIMAL;
BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE id = p_id;
    RETURN v_balance;
END;
$$ LANGUAGE plpgsql;

-- 调用存储函数
SELECT get_account_balance(1);

设置存储过程中的 INOUT 参数

使用 INOUT 参数，您可以在存储过程内修改参数的值，并将其返回。

CREATE OR REPLACE PROCEDURE update_balance(INOUT p_id INTEGER, IN p_amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_id;
    RAISE NOTICE 'Account ID: %, New Balance: %', p_id, get_account_balance(p_id);
END;
$$;

-- 调用存储过程
CALL update_balance(1, 500);

在 INOUT 参数中使用游标

游标可以在存储过程中用于处理多行数据。

CREATE OR REPLACE PROCEDURE update_multiple_balances(INOUT p_amount DECIMAL)
LANGUAGE plpgsql AS $$
DECLARE
    v_cursor CURSOR FOR SELECT id FROM accounts WHERE balance < p_amount;
    v_id INTEGER;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_id;
        EXIT WHEN NOT FOUND;

        UPDATE accounts SET balance = balance + p_amount WHERE id = v_id;
        RAISE NOTICE 'Updated Account ID: %, New Balance: %', v_id, get_account_balance(v_id);
    END LOOP;

    CLOSE v_cursor;
END;
$$;

-- 调用存储过程
CALL update_multiple_balances(200);

常规触发器

触发器是在特定事件（如 INSERT、UPDATE 或 DELETE）发生时自动执行的函数。

CREATE OR REPLACE FUNCTION log_account_change()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Account ID: %, Action: %', NEW.id, TG_OP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER account_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_account_change();

常规触发器的定义

常规触发器的定义包括事件、触发时机和执行的函数。
假设我们要在每次插入新账户时记录插入信息：

CREATE OR REPLACE FUNCTION log_insertion()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO account_log (account_id, action, log_time) 
    VALUES (NEW.id, 'INSERT', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insertion_trigger
AFTER INSERT ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_insertion();

常规触发器应用案例

下面是一个应用案例，记录账户的所有变化到 account_log 表。

创建日志表

CREATE TABLE account_log (
    log_id SERIAL PRIMARY KEY,
    account_id INTEGER,
    action TEXT,
    log_time TIMESTAMP
);

创建触发器

CREATE OR REPLACE FUNCTION log_account_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO account_log (account_id, action, log_time) 
    VALUES (NEW.id, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER account_changes_trigger
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_account_changes();

事件触发器

事件触发器用于响应数据库事件，如 DDL 语句（创建、修改或删除数据库对象）。

CREATE OR REPLACE FUNCTION ddl_event_logging()
RETURNS EVENT_TRIGGER AS $$
BEGIN
    RAISE NOTICE 'DDL Event: %', TG_EVENT;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_trigger 
ON ddl_command_start 
EXECUTE FUNCTION ddl_event_logging();

管理数据库安全

PostgreSQL 中的用户和角色是数据库安全的基础。角色可以有不同的权限，用户可以被分配到这些角色中。

创建用户和角色

-- 创建一个新角色（用户）
CREATE ROLE my_user WITH LOGIN PASSWORD 'my_secure_password';

-- 创建一个角色（可以与其他用户共享权限）
CREATE ROLE my_role;

授权角色给用户

-- 将角色分配给用户
GRANT my_role TO my_user;

管理用户的密码

PostgreSQL 允许您管理用户密码，包括设置、重置和强度检查。

ALTER ROLE my_user WITH PASSWORD 'new_secure_password';

使用 passwordcheck 扩展

您可以使用 passwordcheck 扩展来增强密码强度检查。

-- 安装 passwordcheck 扩展
CREATE EXTENSION passwordcheck;

-- 设置角色的密码安全性
ALTER ROLE my_user WITH PASSWORD 'weak_password';  -- 这将失败

预定义角色

PostgreSQL 提供了一些预定义角色，例如 pg_read_all_data 和 pg_write_all_data，以便于权限管理。

-- 授予读取所有数据的权限
GRANT pg_read_all_data TO my_user;

权限管理

权限管理职责是将数据库对象的访问权限授予或撤回给用户或角色。

使用 GRANT 和 REVOKE 命令

-- 授予对表的 SELECT 权限
GRANT SELECT ON my_table TO my_user;

-- 撤回对表的 SELECT 权限
REVOKE SELECT ON my_table FROM my_user;

使用 ADMIN OPTION 和 GRANT OPTION

GRANT OPTION 允许用户将权限授予其他用户。

-- 授予权限并允许用户再授予其他用户
GRANT SELECT ON my_table TO my_user WITH GRANT OPTION;

使用组合角色管理权限

组合角色是将多个角色的权限合并到一个用户的方式。

创建组合角色

-- 创建多个角色
CREATE ROLE role1;
CREATE ROLE role2;

-- 授权角色权限
GRANT SELECT ON my_table TO role1;
GRANT INSERT ON my_table TO role2;

-- 创建一个新角色并组合这两个角色
CREATE ROLE combined_role;
GRANT role1 TO combined_role;
GRANT role2 TO combined_role;

-- 将组合角色分配给用户
GRANT combined_role TO my_user;

使用 SET ROLE 显示启用角色的权限

SET ROLE 允许用户在会话中临时切换到另一个角色。

-- 切换到组合角色
SET ROLE combined_role;

-- 查询表以验证权限
SELECT * FROM my_table;

-- 重置角色
RESET ROLE;

审计日志功能

审计日志可以帮助您跟踪数据库中的所有活动。

启用审计日志

在 PostgreSQL 中，您可以通过设置 postgresql.conf 文件中的参数来启用审计功能。

# 修改 postgresql.conf
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

使用 pgaudit 扩展

pgaudit 是一个用于 PostgreSQL 的审计扩展，可以提供更全面的审计功能。

-- 安装 pgaudit 扩展
CREATE EXTENSION pgaudit;

-- 配置 pgaudit
ALTER SYSTEM SET pgaudit.log = 'all';

备份与恢复

本章将深入探讨 PostgreSQL 数据库的备份与恢复策略，包括各种备份方法、工具及其使用案例。

备份与恢复的基本概念

备份是将数据库数据复制到安全位置的过程，以防数据丢失或损坏。恢复是将备份的数据恢复到数据库中的过程。

数据库的故障类型

硬件故障：如磁盘损坏或电源故障。
软件故障：应用程序错误或数据库崩溃。
人为错误：如误删除数据或表。
自然灾害：如洪水或火灾。

备份的基本术语

全备份：完整备份数据库中的所有数据。
增量备份：只备份自上次备份后更改的数据。
差异备份：备份自上次全备份后的所有更改数据。

设置 PG 的日志归档

在 PostgreSQL 中，您可以启用 WAL（Write Ahead Logging）归档，以便在恢复时使用。

启用 WAL 归档

在 postgresql.conf 中进行以下设置：

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'  -- 根据需要调整路径

通过 SQL 转储实现逻辑备份

逻辑备份是指将数据库对象和数据导出为 SQL 文件。

使用 pg_dump 生成 SQL 转存

pg_dump -U username -d database_name -f backup.sql

使用 pg_dumpall 完成 SQL 转存

pg_dumpall 用于备份整个集群的所有数据库。

pg_dumpall -U username > all_databases_backup.sql

文件系统级别的备份与恢复

文件系统级别的备份是指直接从操作系统层面备份数据文件。

进行文件系统级别的备份

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 备份数据目录
cp -r /var/lib/pgsql/data /path/to/backup/data

# 重新启动 PostgreSQL 服务
sudo systemctl start postgresql

第一文件系统级别的备份与恢复

使用文件系统级别的备份进行恢复时

恢复过程

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 恢复数据目录
rm -rf /var/lib/pgsql/data/*
cp -r /path/to/backup/data/* /var/lib/pgsql/data/

# 设置权限
chown -R postgres:postgres /var/lib/pgsql/data

# 重新启动 PostgreSQL 服务
sudo systemctl start postgresql

使用 pg_basebackup 完成热备份

pg_basebackup 是一个用于在线备份的工具，可以在数据库运行时执行。

pg_basebackup -U username -D /path/to/backup/ -Ft -z -P

-Ft 表示以 tar 格式备份。
-z 表示压缩备份。
-P 表示显示进度。

连续归档与基于时间点的恢复

基于时间点的恢复（PITR）是通过恢复备份和应用 WAL 日志来恢复到特定时间的过程。

设置连续归档

确保在 postgresql.conf 中启用 WAL 归档。

执行基于时间点的恢复

恢复全备份。
使用归档的 WAL 日志文件恢复到指定时间。

执行数据库快照恢复

使用快照恢复可以迅速恢复数据库到某个特定状态。

进行快照备份恢复

# 停止 PostgreSQL 服务并进行快照
sudo systemctl stop postgresql
# 进行快照操作（根据具体环境而定）
# 恢复快照
cp -r /path/to/snapshot/* /var/lib/pgsql/data/
# 启动 PostgreSQL 服务
sudo systemctl start postgresql

使用第三方备份工具 pg_rman

pg_rman 是一个用于 PostgreSQL 的备份和恢复管理工具。

# 安装 pg_rman
sudo apt install pg_rman

# 进行备份
pg_rman backup

# 恢复备份
pg_rman restore --target-time="YYYY-MM-DD HH:MM:SS"

标签：PostgreSQL,--,CREATE,备忘录,balance,my,id,SELECT
From： https://www.cnblogs.com/mugetsukun/p/18384701

PostgreSQL备忘录

安装（Docker）

拉取PostgreSQL镜像

运行PostgreSQL容器

进入PostgreSQL容器

使用psql命令行客户端

运维管理部分

创建数据库

修改数据库配置

删除数据库

创建数据库模板

管理数据库的扩展

使用扩展访问外部数据源

数据预热扩展

监控共享缓冲区

WAL 日志解析扩展

数据库实例操作

管理数据库对象

创建数据库

创建模式

创建表

查看表的结构

数据类型

表的基本操作

插入数据

查询数据

更新数据

删除数据

数据的约束条件

使用临时表

在查询时使用索引

索引的维护

视图的创建与使用

序列的使用

序列冲突解决方法

并行查询的工作原理

何时使用并行查询？

查询计划分析

并行顺序扫描

并行索引扫描

并行位图扫描

嵌套循环连接

哈希连接

并行查询的限制

事务与并发控制

事务简介

事务的特征

事务的并发

隔离级别

事务的脏读

表级锁与行级锁

死锁

如何避免死锁

实例教学

示例 1：创建事务

示例 2：使用 ROLLBACK

示例 3：设置隔离级别

应用程序开发

开发第一个 PL/pgSQL 程序

使用 PL/pgSQL 的基本数据类型

使用 PL/pgSQL 的高级数据类型

在 PL/pgSQL 中使用条件判断

在 PL/pgSQL 中使用循环

在 PL/pgSQL 中使用游标

处理系统预定义例外

处理用户自定义例外

PL/pgSQL 编程综合案例

存储过程与触发器的使用

创建和使用存储过程

创建和使用存储函数

设置存储过程中的 INOUT 参数

在 INOUT 参数中使用游标

常规触发器

常规触发器的定义

常规触发器应用案例

创建日志表

创建触发器

事件触发器

管理数据库安全

创建用户和角色