如何使用 PostgreSQL 数据库

PostgreSQL 是 MySQL 或 MariaDB 的优秀替代数据库引擎，但管理这些数据库有很大不同，特别是对于那些非常习惯从命令行或 PHPMyAdmin 使用 MySQL 的系统管理员。许多 CMS 和应用程序已经知道如何与 PostgreSQL 数据库进行开箱即用的交互，但像 MySQL 一样，仍然需要您来维护这些数据库。

对于本文，我们假设您以“root”用户身份通过 SSH 连接到您的服务器，并且已使用默认配置安装了 PostgreSQL 服务器，这需要以“postgres”用户身份连接。因此，大多数命令也将采用 -U postgres 选项。在大多数现代版本中，这些命令在 CentOS 和 Ubuntu 之间通常是相同的，但主要在 CentOS 7 VPS 服务器和运行 PostgreSQL 9.2 的 Ubuntu 16.04 服务器上进行测试。

创建数据库和授权相当复杂，但如果您使用 cPanel、Plesk 或其他支持 PostgreSQL 集成的控制面板，则设置起来很简单。从命令行创建这些数据库和授权超出了本文的范围，因此我们还假设您已经运行并使用了您的数据库。

它有助于了解您将使用的数据库的确切名称，并进行仔细检查以确保您在正确的服务器上工作。让我们首先列出我们机器上的 PostgreSQL 数据库：
psql -l -U postgres

此命令的输出是一个包含数据库名称、所有者和访问权限的表。在为将来的命令描述数据库名称时，我们将使用第一列中的数据。对于本文，我们将使用一个名为“数据库名称”。

如果您只需要数据库名称，而不需要其他信息，则可以通过对数据库名称执行精确查询来删除多余的信息：
psql -U postgres -tA -c 'select datname from pg_database;'

该命令执行一个命令（由 -C 标志）的选择 数据名 的列 pg_database 全局表。这 -t flag 仅列出元组（结果行）并隐藏多余的描述符，如标题和结果计数，以及 -一种 flag 将删除对齐，每行打印一个数据库，名称前后没有字符。此命令的输出对于为数据库转储创建循环特别有用。

转储数据库

dump 命令将获取数据库的完整副本，包括所有表、模式和数据，并将其存储在单个压缩文件中：
pg_dump -c -Fc -U postgres database_name > database_name.psql

这个命令有几个重要的标志。 -C，或者 -干净的告诉 pg_dump 将 DROP 语句添加到输出中，以及 -Fc，或者 –格式=自定义，压缩和组织输出以使用 pg_restore 稍后的。自定义格式具有最大的灵活性，并且有利地将备份存储在单个文件中。

转储所有数据库

通过多次运行上述命令将每个数据库放入自己的文件中可能会更好。我们将引用我们的第二个列表命令，删除一些不需要的数据库，以制作我们的个人转储。
for db in $(psql -U postgres -tA -c 'select datname from pg_database;' | grep -E -v “^(template1|template0|postgres)$”); do echo $db pg_dump -c -Fc -U postgres $db > $db.psql done

这个 for 循环会将所有数据库的列表（默认模板和 postgres 数据库除外）传递到 pg_dump 命令制作一个 .psql 当前目录中的每个文件。

注意：也可以将所有数据库转储到单个文件中，但对于大型数据集，这可能会变得笨拙且难以恢复。因此，我不推荐这种方法，尽管它不那么复杂并且可以在某些情况下工作。

pg_dumpall --clean -U postgres > pg.all.psql

转储补助金

授权存储在 PostgreSQL 模式中。您可以使用 pg_dump 命令转储模式，从而获取系统的所有可用授权：
pg_dumpall -U postgres -s > pg.schema.psql

这不仅会转储授权，还会转储服务器的其余模式，包括数据库和表创建语句、所有权和角色成员资格以及 ACL 分配——基本上，除了数据之外的所有内容。

如果您只需要用户（角色）和授权，您可以使用以下命令收集这些：
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' -e '^(CREATE|ALTER) ROLE' > pg.grants.psql

这利用了完整的模式转储，但仅使用角色创建语句和授权/撤销语句来组合用户和授权。

删除或删除数据库

要销毁数据库，语法非常简单。输入此命令时请小心，因为它不会要求您确认！
psql -U postgres -c ‘drop database database_name’

如果数据库存在，它将不再存在。同样的任务还有一个命令行包装器：
dropdb -U postgres database_name

删除授权

通过撤销授权从用户中删除它们。首先了解特定用户拥有什么授权很重要，所以让我们通过转储模式并查找与我们的用户相关的行来搜索它们。
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' | grep database_user

就我而言，有一个如下所示的赠款：
GRANT ALL ON DATABASE database_name TO database_user;

为了解决这个问题，我们将撤销相同的描述符：
psql -U postgres -c ‘revoke all on database database_name from database_user;’

注意：注意细微的变化，GRANT 已更改为 REVOKE，TO 已更改为 FROM。该命令用单引号括起来并使用 -c 标志传递给 psql 以在 PostgreSQL 中执行该命令。

这将用架构中的 REVOKE 替换原始 GRANT 语句。再次运行本节中的第一个命令现在只显示我们刚刚通过的行：

REVOKE ALL ON DATABASE database_name FROM database_user;

恢复数据库

对于使用本文中使用的 pg_dump 方法转储的数据库，我们可以使用以下命令恢复整个数据库：
pg_restore -U postgres -c -C -O -d database_name database_name.psql

这里需要更多的标志！ -C 或者 -干净的和以前一样，在从文件写入 PostgreSQL 之前删除数据库对象。我们添加它以防它不用于转储命令。 -C，或者 -创造, 如果数据库不存在，将在还原期间生成数据库 database_name。如果数据库已经存在，则 -C flag 将在之前将其销毁 -C 重新创建它。接下来，有 -O，或者 – 无所有者，从数据库中删除所有者。这允许将任何来源的备份更改为执行还原的用户的所有者，在我们的例子中是 postgres。 -d 数据库名称 描述您将恢复到的数据库的名称。如果需要，这可以是与创建备份的名称不同的名称。最后一个参数是我们备份文件的名称， 数据库名称.psql.