在 PostgreSQL 中导入和导出数据

概述

本指南介绍了如何从 PostgreSQL 数据库导出数据以及将数据导入 PostgreSQL 数据库。您可以在官方 PostgreSQL 文档中了解有关此主题的更多信息。

使用 pg_dump 导出数据

pg_dump 是一个原生的 PostgreSQL 实用程序，可用于从 PostgreSQL 数据库导出数据。要查看此命令的所有选项，请运行

pg_dump --help

来自 PostgreSQL 文档

这种转储方法背后的思想是生成一个包含 SQL 命令的文件，当将该文件反馈给服务器时，将以与转储时相同的状态重新创建数据库。PostgreSQL 提供了实用程序程序 pg_dump 来实现此目的。pg_dump 是一个常规的 PostgreSQL 客户端应用程序（尽管它特别聪明）。这意味着您可以从任何有权访问数据库的远程主机执行此备份过程。但请记住，pg_dump 不使用特殊权限进行操作。特别是，它必须具有对要备份的所有表的读取权限，因此为了备份整个数据库，您几乎总是必须以数据库超级用户的身份运行它。

该命令的基本语法如下所示

pg_dump DB_NAME > OUTPUT_FILE

您需要将 DB_NAME 和 OUTPUT_FILE 占位符替换为以下各项的相应值

• 您的数据库名称
• 所需的输出文件名的名称（为了获得最佳互操作性，应以 .sql 结尾）

例如，要从本地 PostgreSQL 服务器上名为 mydb 的数据库将数据导出到名为 mydb.sql 的文件中，您可以使用以下命令

pg_dump mydb > mydb.sql

如果您的数据库模式使用 对象标识符类型 (OID)，则需要使用 --oids (简写：-o) 选项运行 pg_dump

pg_dump mydb --oids > mydb.sql

提供数据库凭据

您可以添加以下参数来指定 PostgreSQL 数据库服务器的位置

要针对 PostgreSQL 数据库服务器进行身份验证，您可以使用以下参数

例如，如果您想从具有以下 连接字符串 的 PostgreSQL 数据库导出数据

postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus

您可以使用以下 pg_dump 命令

pg_dump --host ec2-46-137-91-216.eu-west-1.compute.amazonaws.com --port 5432 --user opnmyfngbknppm d50rgmkqi2ipus > backup.sql

请注意，此命令将触发一个提示，您需要在其中指定所提供用户的密码。

控制输出

在某些情况下，您可能不想转储整个数据库，例如，您可能想要

• 仅转储实际数据，但排除 DDL（即定义数据库模式的 SQL 语句，例如 CREATE TABLE 等）
• 仅转储 DDL，但排除实际数据
• 排除特定的 PostgreSQL 模式
• 排除大型文件
• 排除特定的表

以下是您可以在这些场景中使用的一些命令行选项的概述

从 SQL 文件导入数据

在使用 SQL Dump 将 PostgreSQL 数据库导出为 SQL 文件后，您可以通过将 SQL 文件输入到 psql 来恢复数据库的状态

psql DB_NAME < INPUT_FILE

您需要将 DB_NAME 和 INPUT_FILE 占位符替换为以下各项的相应值

• 您的数据库名称（必须预先创建具有该名称的数据库！）
• 目标输入文件的名称（可能以 .sql 结尾）

要预先创建数据库 DB_NAME，您可以使用 template0（它创建一个不包含任何站点本地添加的纯用户数据库）

CREATE DATABASE dbname TEMPLATE template0;

结论

从 PostgreSQL 导出数据并再次导入以重新创建数据结构和填充数据库是迁移数据、备份和恢复或为复制做准备的好方法。了解 pg_dump 和 psql 工具如何协同工作以完成此任务将帮助您跨数据库边界传输数据。