如何使用 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 中的数据库

它有助于了解您将使用的数据库的确切名称,并进行仔细检查以确保您在正确的服务器上工作。 让我们首先列出我们机器上的 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.

恢复拨款

由于所有授权都存储在我们的 pg.grants.psql 文件中,我们只需要选择我们需要恢复的用户。 在我们的例子中,我们将恢复 database_user:
grep database_user pg.grants.psql | psql -U postgres

由于 pg.grants.psql 文件具有角色和授权,因此该命令将使用其原始权限和密码重新创建用户,然后授予其对所需数据库和模式的访问权限。