PostgreSQL 管理

PostgreSQL 是一个强大的开源对象关系数据库系统，它使用和扩展了SQL语言，并结合了许多功能，可以安全地存储和扩展最复杂的数据工作负载。PostgreSQL 的起源可以追溯到1986年，当时是加州大学伯克利分校 POSTGRES 项目的一部分，在这个核心平台上已经活跃发展了超过 35 年。本文将重点介绍如何管理 PostgreSQL。

安装

以 Ubuntu jammy (22.04, LTS)为例，使用自动化存储库配置安装：

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

升级 psql

psql是 PostgreSQL 中的一个命令行交互式客户端工具，默认版本是 14，建议升级一下。要在 Ubuntu 22.04 上更新 psql，请按照以下步骤操作：

1. 更新包列表并安装必要的依赖项：

   sudo apt update
   sudo apt install -y wget gnupg2

2. 添加 PostgreSQL APT 仓库：

   wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

3. 再次更新包列表：

   sudo apt update

4. 安装所需版本的 PostgreSQL：

   sudo apt install -y postgresql-16

5. 验证安装：

   psql --version

通过以上步骤可以安装指定版本的 PostgreSQL 并相应更新 psql 客户端。

psql 连接数据库

PostgreSQL 安装好后，用默认超级用户 postgres 登录默认数据库 postgres

psql --host localhost --username postgres --dbname postgres;

连接成功后，就可以交互地键入 SQL 命令了。

数据库命令

• 查看数据库

  psql –l

• 创建数据库

只有超级用户才能为其他人(即你不是其中一员的角色)创建数据库。

CREATE DATABASE db1； # 拥有者为 postgres
CREATE DATABASE db2 OWNER tester; # 拥有者为 tester

运行上述第二条命令，角色 test 要存在才可能成功。稍后我们会讲述怎样创建角色。

创建数拥有者也可以通过更改数据库来完成。

• 更改数据库：

  ALTER DATABASE db1 OWNER TO tester;

  PostgreSQL 中数据库拥有者或超级用户可以删除数据库

• 删除数据库：

  DROP DATABASE db1

上文提到为超级用户 postgres 以外的用户角色创建数据库需要先创建它们。

用户角色命令

• 查看用户角色

下述命令可以显示所有的用户角色：

\du 或 \dg # 

等同于运行下述查询

SELECT rolname FROM pg_roles;
# SELECT rolname FROM pg_roles WHERE rolcanlogin;

• 创建用户角色

  CREATE ROLE dev LOGIN # 赋予 LOGIN 权限
  CREATE ROLE test CREATEDB # 赋予 CREATEDB 权限
  CREATE USER john WITH PASSWORD '123456';

  PostgreSQL 中角色或用户基本上是等同的，差别在于用户创建好后会自动赋予登录权限。

如果创建时没有设置密码，可以先创建再更改：

ALTER USER test WITH PASSWORD '123456';

• 更改用户角色：

• 删除

  DROP ROLE test;
  DROP User john;

授权命令

• 授予角色权限：
• 授予用户权限：

将插入权限授予表 films 上的所有用户:

Grant insert privilege to all users on table films:

• 授予全部权限

在视图类型上授予用户 manuel 所有可用的权限:

Grant all available privileges to user manuel on view kinds:

请注意，虽然上面的命令确实会授予由超级用户或所有者执行的所有权限，但当由其他人执行时，它只会授予其他人具有授予选项的权限。

将 db1 的成员资格授予用户 test:

GRANT ALL PRIVILEGES ON DATABASE db1 TO test；

其他命令

pgsql 还有一些很有用的命令：

• \l 查看当前的数据库列表
• \c 数据库名 切换当前数据库
• \d 表名; 查看表的详细信息
• \dn 显示所有 哎 `schema
• \du 或 \dg 显示所有的角色或用户
• \q 退出交互式界面

远程连接

在 pg_hba.conf 配置文件最后加一句：

host    all             riafan          0.0.0.0/0               trust

然后就可以使用 Navicat 这些工具远程连接 PostgreSQL了😄