Postgres Cheat Sheet

Install

sudo apt update

sudo apt install postgresql postgresql-contrib

psql --version

sudo systemctl start postgresql

sudo systemctl enable postgresql

sudo systemctl status postgresql

sudo -i -u postgres

psql

sudo -u postgres psql

\q

createuser --interactive

createdb sammy

psql -d mydb -U myuser

配置文件

postgresql.conf 和 pg_hba.conf 是 PostgreSQL 的两个主要配置文件，在 psql 命令行运行以下命令找到它们的位置。

SHOW config_file;
SHOW hba_file;

任何配置被修改后都需要重启 Postgres 服务。

sudo systemctl restart postgresql

添加远程连接及修改认证方式

1. 修改 postgresql.conf

找到 listen_addresses 设置，并修改它以允许PostgreSQL监听所有接口或特定的接口。例如：

• 监听所有接口（任何IP地址）：

listen_addresses = '*'

• 监听特定的IP地址：

listen_addresses = '192.168.1.100,localhost'

其中 192.168.1.100 是服务器的IP地址。

2. 修改 pg_hba.conf

在文件的末尾添加规则以允许远程连接。例如：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

这将允许所有用户从任何IP地址远程连接到所有数据库，使用 md5 密码认证。

导出和导入

pg_dump

基础命令

• 导出整个数据库到一个SQL文件：

  LocalRemote

  [path_to_backup_file].dump

• 导出整个数据库到一个自定义格式文件（更高效，适合大型数据库）：

pg_dump -h [hostname] -p [port] -U [username] -F c [database_name] > [filename].dump

• 如果你需要备份PostgreSQL实例中的所有数据库，可以使用 pg_dumpall。这个工具会导出所有数据库、角色和权限设置：

pg_dumpall -U [username] -h [hostname] -p [port] > [backup_file].sql

常用选项

• -h [hostname]: 指定数据库服务器的主机名或IP地址。
• -p [port]: 指定数据库服务器的端口。
• -U [username]: 指定连接数据库的用户名。
• -F c: 使用自定义格式进行备份。
• -v: 显示详细的操作信息。
• -f [filename]: 指定输出文件名和路径。
• -t [table_name]: 只导出指定的表。

高级用法

• 使用gzip压缩导出的文件：

pg_dump -h [hostname] -p [port] -U [username] [database_name] | gzip > [filename].sql.gz

• 导出特定的表：

pg_dump -h [hostname] -p [port] -U [username] -t [table_name] [database_name] > [filename].sql

pg_restore

基础命令

• 从自定义格式文件恢复数据库：

pg_restore -h [hostname] -p [port] -U [username] -d [database_name] [filename].dump

常用选项

• -h [hostname]: 指定数据库服务器的主机名或IP地址。
• -p [port]: 指定数据库服务器的端口。
• -U [username]: 指定连接数据库的用户名。
• -d [database_name]: 指定要恢复到的目标数据库。
• -v: 显示详细的操作信息。
• -F c: 指定输入文件格式为自定义格式。

高级用法

• 并行恢复以提高性能（需要足够的CPU和内存资源）：

pg_restore -h [hostname] -p [port] -U [username] -d [database_name] -j [number_of_jobs] [filename].dump

请注意，使用pg_dump和pg_restore时，可能需要相应的权限。此外，建议在执行这些操作之前，确认目标数据库环境（如版本兼容性和存储容量）是否适合恢复操作。

概念

CASCADE

在 PostgreSQL 中，当你尝试删除（DROP）一个表时，如果出现提示关于使用 DROP ... CASCADE，这与数据库中的外键约束和依赖关系有关。

1. 外键约束和依赖关系： 数据库表可以通过外键约束与其他表相关联。例如，如果你有一个表 A，它的某些列被其他表通过外键引用，那么表 A 被认为是这些表的依赖项。

2. DROP ... RESTRICT（默认行为）： 如果你尝试删除一个被其他对象依赖的表，PostgreSQL 默认采取的是 RESTRICT 行为。这意味着，如果存在任何依赖（如外键约束），删除操作将被阻止，并且会显示错误消息。

3. DROP ... CASCADE： 如果你确定需要删除该表，并且希望同时自动删除所有依赖于这个表的外键约束和相关对象，你可以使用 CASCADE 选项。使用 DROP TABLE table_name CASCADE; 命令时，系统不仅会删除指定的表，还会删除所有依赖于该表的对象，例如依赖于该表的外键约束、视图、索引等。

• 注意风险： 使用 CASCADE 需要谨慎，因为它会删除更多的相关对象，可能会导致数据丢失或完整性问题。

4. 示例： 假设你有两个表 orders 和 customers，其中 orders 表通过外键引用了 customers 表的主键。如果你尝试删除 customers 表，系统会阻止你并显示错误，因为 orders 表依赖于它。要删除 customers 表及其所有依赖项，你需要使用：

DROP TABLE customers CASCADE;

在实际操作中，强烈建议在使用 CASCADE 之前仔细检查将被删除的所有依赖项，并确保这样做不会对数据库的完整性和应用程序功能造成不利影响。通常，最好先手动检查和处理这些依赖关系，而不是盲目地使用 CASCADE。

运维

忘记账户密码

sudo su postgres

ALTER USER postgres WITH PASSWORD 'new_password';

删除 Schema

DROP SCHEMA myschema CASCADE;

删除 Database

dropdb -U username database_name
createdb -U username database_name

清空数据库

清空一个 PostgreSQL 数据库，即删除数据库中的所有表和数据，但保留数据库本身。

1. 连接到数据库： 使用 psql 或其他 PostgreSQL 客户端连接到您想要清空的数据库。

psql -U username -d database_name

2. 禁用外键约束（可选）： 为了防止外键约束导致删除操作失败，您可以暂时禁用外键约束：

SET session_replication_role = replica;

3. 删除所有表： 使用 SQL 命令删除所有表。这可以通过在 psql 中执行以下命令来完成：

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

这将删除 public schema 下的所有表和数据，并重新创建一个空的 public schema。

4. 重新启用外键约束（如果之前禁用）： 如果您之前禁用了外键约束，现在需要重新启用它们：

SET session_replication_role = DEFAULT;

5. 重新赋予必要的权限： 如果您有特定的权限设置在 public schema 上，您需要重新设置这些权限。

授予某用户完整权限

要授予名为 username 的用户对名为 database_name 的数据库的完整权限，您可以按照以下步骤操作：

1. 登录到 PostgreSQL 数据库： 以具有管理员权限的用户（通常是 postgres）登录到 PostgreSQL。通过命令行，可以使用以下命令：

psql -U postgres

2. 授予连接数据库的权限： 授予 username 用户连接到 database_name 数据库的权限：

GRANT CONNECT ON DATABASE database_name TO username;

3. 授予对所有现有表的权限： 授予 username 用户对 database_name 数据库中所有现有表的所有权限：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

如果您的表在不同的 schema 中，请将 public 替换为相应的 schema 名称。

4. 授予对所有现有序列的权限： 如果数据库中包含序列，授予对所有现有序列的所有权限：

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;

5. 授予对所有现有函数的权限： 授予对所有现有函数的所有权限：

GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO username;

6. 设置默认权限： 设置默认权限，以确保 username 用户对未来在 database_name 数据库中创建的所有新表、序列和函数也拥有权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO username;

7. 重新加载权限（如果需要）： 在某些情况下，可能需要重新加载或刷新权限设置：

SELECT pg_reload_conf();

执行这些步骤后，username 用户将拥有对 database_name 数据库的完整权限。请谨慎操作，特别是在生产环境中，因为这涉及到数据库的安全性和完整性。