分享至

概述

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

使用 pg_dump 导出数据

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

pg_dump --help

摘自 PostgreSQL 文档

这种转储方法的思想是生成一个包含 SQL 命令的文件,当将其重新输入到服务器时,这些命令将以转储时的相同状态重新创建数据库。PostgreSQL 为此目的提供了实用程序 pg_dumppg_dump 是一个常规的 PostgreSQL 客户端应用程序(尽管它非常巧妙)。这意味着你可以从任何可以访问数据库的远程主机执行此备份过程。但请记住,pg_dump 不具备特殊权限。特别是,它必须对所有要备份的表具有读取访问权限,因此为了备份整个数据库,你几乎总是需要以数据库超级用户身份运行它。

该命令的基本语法如下

pg_dump DB_NAME > OUTPUT_FILE

你需要将 DB_NAMEOUTPUT_FILE 占位符替换为相应的值:

  • 你的数据库名称
  • 所需的输出文件名称(为获得最佳互操作性,应以 .sql 结尾)

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

pg_dump mydb > mydb.sql

如果你的数据库模式使用了 对象标识符类型 (OIDs),你需要使用 --oids(简写:-o)选项运行 pg_dump

pg_dump mydb --oids > mydb.sql

提供数据库凭据

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

参数默认环境变量描述
--host(简写:-hlocalhostPGHOST服务器主机的地址
--port(简写:-p-PGPORTPostgreSQL 服务器正在监听的服务器主机的端口

要对 PostgreSQL 数据库服务器进行身份验证,你可以使用以下参数:

参数默认环境变量描述
--username(简写:-U你当前的操作系统用户名PGUSER数据库用户的名称。

例如,如果你想从具有以下连接字符串的 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 模式
  • 排除大文件
  • 排除特定表

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

参数默认描述
--data-only(简写:-afalse排除任何 DDL 语句,仅导出数据。
--schema-only(简写:-sfalse排除数据,仅导出 DDL 语句。
--blobs(简写:-b如果未指定 -schema--table--schema-only 选项,则为 true包含二进制大对象。
--no-blobs(简写:-Bfalse排除二进制大对象。
--table(简写:-t默认包含所有表明确指定要转储的表的名称。
--exclude-table(简写:-T-从转储中排除特定表。

从 SQL 文件导入数据

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

psql DB_NAME < INPUT_FILE

你需要将 DB_NAMEINPUT_FILE 占位符替换为相应的数值:

  • 你的数据库名称(必须事先创建同名数据库!)
  • 目标输入文件的名称(可能以 .sql 结尾)

要预先创建数据库 DB_NAME,你可以使用 template0(它会创建一个不包含任何站点本地添加内容的普通用户数据库):

CREATE DATABASE dbname TEMPLATE template0;

结论

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

关于作者
Justin Ellingwood

Justin Ellingwood

Justin 自 2013 年以来一直撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作,这让所有相关方都松了一口气。
© . All rights reserved.