介绍

PostgreSQL经历了长时间的演变。该项目最初开始于在加利福尼亚大学伯克利分校的Ingres计划。这个计划的领导者迈克尔·斯通布雷克在1982年离开加利福尼亚大学伯克利分校去推进Ingres的商业化，但最后还是返回了学术界。在1985年返回伯克利之后，斯通布雷克开始了post-Ingres计划，致力于解决在1980年代早期所出现一些数据库系统存在的问题。Postgres和Ingres的代码库开始（并保持）完全分离。

新项目Postgres的目的是通过增加最少的功能来完全支持所需要的类型。这些功能包括类型定义和完整描述数据关系的能力。完整描述数据关系的能力之前虽广为使用但却需要由用户来维护。Postgres的数据库能够"理解"关系，并可以使用一定的规则以自然方式在相关的表中检索信息。

安装

Debian

apt install postgresql postgresql-contrib
# 设置开机自启
systemctl enable postgresql
systemctl status postgresql

Docker

docker-compose.yaml

version: '3.8'

services:
  postgresql:
    image: postgres:latest
    container_name: postgresql
    restart: always
    environment:
      POSTGRES_USER: your_username
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: your_database
    volumes:
      - ./data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  phppgadmin:
    image: dockage/phppgadmin
    container_name: phppgadmin
    restart: always
    ports:
      - "8080:80"
    environment:
      PHP_PG_ADMIN_SERVER_HOSTS: "postgresql"
      PHP_PG_ADMIN_SERVER_PORTS: "5432"
      PHP_PG_ADMIN_DEFAULT_EMAIL: "admin"
      PHP_PG_ADMIN_DEFAULT_PASSWORD: "admin"
PGSQL的默认端口为: 5432

登录命令行

# 切换用户
sudo su - postgres
# 设置密码
psql -c "ALTER USER postgres WITH PASSWORD 'passwd';"

# 登录数据库
psql -U postgres
# 创建数据库
CREATE DATABASE your_database_name;
# 创建用户
CREATE USER username WITH PASSWORD 'password';
# 授权数据库
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO username;
# 撤销授权
REVOKE ALL PRIVILEGES ON DATABASE your_database_name FROM username;
# 设置未超级管理员
ALTER ROLE username WITH SUPERUSER;
# 列出用户列表
SELECT * FROM pg_user;
# 修改某用户密码
ALTER USER username WITH PASSWORD 'new_password';
# 列出数据库列表
\l
# 连接数据库
\connect database_name;
\c database_name;
# 查看数据表
\dt
# 查看表结构
\d tables_name
# 设置密码
ALTER USER postgres WITH PASSWORD 'passwd';

用户权限控制

  • 权限 表示被授予的权限，如 c（CREATE）、T（TEMPORARY）、r（SELECT）、w（INSERT）、d（DELETE）、D（TRUNCATE）、U（UPDATE）等。
  • / 分隔符后面是被授予权限的数据库对象的所有者。

要修改 PostgreSQL 中的权限，您可以使用 GRANT 和 REVOKE 语句。这些语句允许您向用户、角色或组授予或撤销特定数据库对象（如表、视图、函数等）的权限。

下面是一些常见的用法示例：

  1. GRANT 权限给用户或角色：

    GRANT permission(s) ON object TO user_or_role;

    其中，permission(s) 是要授予的权限，如 SELECT、INSERT、UPDATE 等；object 是要授予权限的对象，如表、视图、模式等；user_or_role 是接收权限的用户或角色。

    例如，要授予 SELECT 权限给 user1 用户：

    GRANT SELECT ON table_name TO user1;

  2. REVOKE 权限：

    REVOKE permission(s) ON object FROM user_or_role;

    这个命令用于撤销之前授予的权限。与 GRANT 语句类似，您需要指定要撤销的权限、对象以及用户或角色。

    例如，要撤销对 user1 用户的 SELECT 权限：

    sqlCopy code
REVOKE SELECT ON table_name FROM user1;

这些语句可以在 psql 命令行界面或任何连接到 PostgreSQL 数据库的客户端中执行。在执行之后，您可以使用 \dp 命令查看对象的权限分配情况。

要授予用户 postgres 对数据库 test 的所有权限，您可以使用 GRANT 语句来分别授予不同的权限，或者可以使用 ALL PRIVILEGES 一次性授予所有权限。以下是两种方法：

  1. 分别授予权限：

    GRANT CONNECT ON DATABASE test TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres;

    这些语句会依次授予连接到 Test 数据库的权限以及在 public 模式下所有表和序列的权限给用户 postgres。

  2. 一次性授予所有权限：

    GRANT ALL PRIVILEGES ON DATABASE test TO postgres;

    这个语句一次性授予了连接到 Test 数据库的所有权限给用户 postgres。这包括连接、创建表、执行 DDL 和 DML 操作等所有权限。

在执行这些语句后，用户 postgres 将具有对 Test 数据库的所有权限。请确保只授予实际需要的最小权限，以确保安全性和最佳实践。

配置远程连接

修改postgresql.conf文件

vim /etc/postgresql/15/main/postgresql.conf

添加

listen_addresses='*'

修改pg_hba.conf文件

vim /etc/postgresql/15/main/pg_hba.conf

添加

host    all             all             10.1.1.0/24             scram-sha-256
# 10.1.1.0/24  是网段

重启数据库服务

systemctl restart postgresql

Peewee连接PGSQL

import psycopg2 # 驱动
from peewee import PostgresqlDatabase, Model, CharField

# 创建 PostgreSQL 数据库连接
db = PostgresqlDatabase('your_database_name', user='postgres', password='passwd',
                        host='your_host', port=5432)

配置文件

当您安装 PostgreSQL 数据库时，系统会生成一个名为 postgresql.conf 的配置文件，用于配置 PostgreSQL 服务器的各种参数。这个文件位于 PostgreSQL 数据目录下的 data 子目录中，通常路径类似于 /etc/postgresql//main/postgresql.conf。

postgresql.conf 文件包含了许多配置选项，这些选项控制着 PostgreSQL 服务器的行为。以下是一些常见的配置选项：

  1. listen_addresses： 控制 PostgreSQL 服务器监听的 IP 地址。默认情况下，PostgreSQL 仅监听本地连接（localhost），如果要允许远程连接，可以将其设置为 * 或指定特定的 IP 地址。
  2. port： PostgreSQL 服务器监听的端口号，默认为 5432。
  3. max_connections： 允许的最大连接数。这个参数控制服务器同时接受的客户端连接数的最大数量。
  4. shared_buffers： 分配给 PostgreSQL 服务器的共享内存缓冲区的大小。这个参数控制着服务器用于缓存数据和索引的内存量。
  5. work_mem： 每个排序和哈希操作可以使用的内存量。较大的值可以提高排序和哈希操作的性能，但也会增加内存使用量。
  6. maintenance_work_mem： 用于维护操作的内存量，如 VACUUM、CREATE INDEX 等。较大的值可以加快维护操作的速度，但也会增加内存使用量。
  7. checkpoint_completion_target： 检查点完成时目标缓冲区的百分比。它控制着检查点过程中脏数据写入磁盘的速度。
  8. effective_cache_size： 用于估算系统的缓存大小，这对于查询优化非常重要。

以上是一些常见的配置选项，postgresql.conf 文件中还有许多其他选项，您可以根据需要进行调整。在修改配置文件后，需要重新加载 PostgreSQL 服务器才能应用新的设置。

数据备份和恢复

可以使用 pg_dump 工具来导出 PostgreSQL 数据库的备份文件。以下是导出数据库的基本步骤：

  1. 使用 pg_dump 导出数据库：

    在命令行中执行以下命令，使用 pg_dump 工具导出数据库备份文件：

    bashCopy code
pg_dump -U your_username -d your_database -f backup.sql

    其中：

    • -U your_username：指定连接数据库的用户名。
    • -d your_database：指定要备份的数据库名称。
    • -f backup.sql：指定导出的备份文件名。

    这将导出数据库的结构和数据到 backup.sql 文件中。

  2. 导出特定模式或表：

    如果您只想导出数据库中的特定模式或表，可以使用 -n 和 -t 参数。例如，要导出特定模式的备份文件：

    pg_dump -U postgres -d your_database -n your_schema -f backup.sql

    或者，要导出特定表的备份文件：

    pg_dump -U postgres -d your_database -t your_table -f backup.sql

  3. 导出时包含 CREATE DATABASE 语句：

    如果您想要包含 CREATE DATABASE 语句，以便在恢复备份时创建新的数据库，可以使用 -C 参数：

    pg_dump -U postgres -d your_database -C -f backup.sql

    这样将在备份文件中包含创建数据库的语句。

  4. 导出时只包含结构，不包含数据：

    如果您只想导出数据库的结构而不包含数据，可以使用 -s 参数：

    pg_dump -U postgres -d your_database -s -f backup.sql

    这样将只导出数据库的结构信息，不包含数据。

导出备份文件后，您将得到一个名为 backup.sql 的文件，其中包含了指定数据库的备份内容。您可以将此文件保存在安全的地方，以备将来恢复数据时使用。

要恢复 PostgreSQL 数据库的备份文件，您可以使用 psql 命令行工具。以下是恢复备份的基本步骤：

  1. 使用 psql 命令恢复备份：

    在命令行中执行以下命令，使用 psql 命令恢复数据库备份文件：

    psql -U postgres -d your_database -f backup.sql

    其中：

    • -U your_username：指定连接数据库的用户名。
    • -d your_database：指定要恢复备份的数据库名称。
    • -f backup.sql：指定要恢复的备份文件名。

    这将读取 backup.sql 文件中的 SQL 命令，并将其应用到指定的数据库中，从而恢复数据库的结构和数据。

  2. 恢复备份时创建新的数据库：

    如果备份文件中包含了 CREATE DATABASE 语句，并且您希望在恢复备份时创建一个新的数据库，可以使用 -d 参数：

    psql -U postgres -d your_database -f backup.sql -d new_database

    这样将在恢复备份时创建名为 new_database 的新数据库，并将备份文件中的内容应用到新数据库中。

  3. 恢复时输出详细信息：

    如果您想要在恢复备份时输出详细信息，可以使用 -v 参数：

    psql -U postgres -d your_database -f backup.sql -v ON_ERROR_STOP=1

    这样将在遇到错误时停止恢复过程，并输出详细的错误信息。

  4. 恢复时仅执行特定语句：

    如果备份文件中包含了多个 SQL 语句，但您只想恢复其中的特定语句，可以使用 -t 参数：

    psql -U postgres -d your_database -f backup.sql -t your_table

    这样将只执行备份文件中涉及到 your_table 表的 SQL 语句。

通过执行以上命令，您将能够使用备份文件恢复 PostgreSQL 数据库的结构和数据。请确保备份文件的路径和名称正确，并且您有足够的权限在目标数据库上执行相应的操作。

WAL日志

在 PostgreSQL 中，有一种称为 WAL（Write-Ahead Logging，预写式日志）的机制，它用于记录数据库的所有变更操作。WAL 日志是一种二进制日志，它记录了数据库中发生的每个事务的修改操作。WAL 日志具有持久性，可以用于在数据库发生崩溃或故障时恢复数据。

WAL 日志通常位于 PostgreSQL 数据目录下的 pg_xlog 或 pg_wal 目录中。这些日志文件由 PostgreSQL 自动管理，并在数据库中进行写入操作时创建和更新。

WAL 日志的主要作用有两个方面：

  1. 持久性和恢复：WAL 日志记录了数据库中每个事务的修改操作，确保了数据的持久性。在数据库发生崩溃或故障时，可以使用 WAL 日志来恢复数据库，保证数据的一致性和完整性。
  2. 流复制：WAL 日志也用于流复制（Streaming Replication）功能，允许将一个数据库的修改操作复制到另一个数据库，实现主从复制和高可用性。

您可以通过配置 PostgreSQL 的参数来调整 WAL 日志的相关设置，如日志文件的大小、保留时间等。WAL 日志是 PostgreSQL 中非常重要的组成部分，对于数据库的持久性和安全性至关重要。

要使用 WAL 日志进行文件恢复，您需要执行以下步骤：

  1. 确定恢复点：

    首先，您需要确定要恢复到的恢复点。这可以是一个特定的时间点、事务 ID、日志文件名等。根据您的需求和情况，选择一个适当的恢复点。

  2. 获取恢复命令：

    在确定恢复点后，您需要使用 pg_wal_replay_pause 和 pg_wal_replay_resume 命令来暂停 WAL 日志的重放，以便在恢复期间防止系统进一步修改数据。执行以下命令来暂停 WAL 日志的重放：

    SELECT pg_wal_replay_pause();

  3. 复制 WAL 日志文件：

    在恢复点之前，您需要将所有 WAL 日志文件从备份位置复制到 PostgreSQL 的 WAL 日志目录中。确保复制的 WAL 日志文件包含恢复点之后的所有变更。

  4. 恢复数据库：

    使用 pg_ctl 命令启动 PostgreSQL 服务器，并将其设置为恢复模式。执行以下命令：

    pg_ctl start -D /path/to/postgres/data -o "-c recovery_target_time='YYYY-MM-DD HH:MM:SS'"

    其中 /path/to/postgres/data 是您的 PostgreSQL 数据目录的路径，YYYY-MM-DD HH:MM:SS 是您确定的恢复点的时间戳。

  5. 等待恢复完成：

    PostgreSQL 服务器将开始应用 WAL 日志文件中的修改，直到达到指定的恢复点。等待恢复过程完成，并确保没有错误发生。

  6. 恢复 WAL 日志重放：

    在完成恢复后，使用以下命令恢复 WAL 日志的重放：

    SELECT pg_wal_replay_resume();

  7. 验证恢复：

    最后，验证数据库已成功恢复到指定的恢复点。检查数据完整性和一致性，以确保恢复过程成功完成。

请注意，使用 WAL 日志进行恢复需要谨慎操作，并确保备份的完整性和准确性。建议在生产环境中进行测试和演练，并确保您了解 WAL 日志恢复的工作原理和相关风险。

缓存配置

在 PostgreSQL 中，有几种不同类型的缓存，它们在数据库性能优化和查询加速方面发挥着重要作用。以下是 PostgreSQL 中常见的几种缓存类型及其作用：

  1. 共享缓冲区（Shared Buffer）：

    共享缓冲区是 PostgreSQL 中最常见的缓存类型，它用于存储数据块（Block）的副本，这些数据块通常是最近访问的数据和索引。共享缓冲区通过减少磁盘 I/O 操作来提高查询性能，因为大部分数据可以直接从内存中读取而不是从磁盘中读取。共享缓冲区的大小可以通过 shared_buffers 参数进行配置。

  2. 操作系统缓存：

    PostgreSQL 运行在操作系统之上，因此它可以利用操作系统提供的文件系统缓存来加速数据访问。当 PostgreSQL 发送 I/O 请求时，操作系统可以将数据缓存在内存中，从而加速后续的读取操作。尽管共享缓冲区是 PostgreSQL 内部的缓存机制，但操作系统缓存也对数据库性能起到了重要作用。

  3. 查询缓存（Query Cache）：

    PostgreSQL 以前支持查询缓存，它允许存储执行过的查询结果以供后续查询直接重用。然而，由于查询缓存存在一些限制和性能问题，并且往往不够灵活，因此在 PostgreSQL 9.6 版本之后，查询缓存默认已被禁用。现在，PostgreSQL 更倾向于通过共享缓冲区和其他手段来优化查询性能。

这些缓存类型在不同层面上提高了数据库的性能和响应速度。共享缓冲区和操作系统缓存可以减少磁盘 I/O 操作，加快数据访问速度；查询缓存可以直接重用先前执行过的查询结果，节省查询执行时间。通过合理配置和优化这些缓存，可以显著提升 PostgreSQL 数据库的性能。

可以在postgresql.conf里修改缓存配置.