如何在 PostgreSQL 中导出数据库的模式？

How can I export the schema of a database in PostgreSQL?

提问by programmer

My computer broke down but fortunately I backed up the folder C:\Program Files\PostgreSQL.

我的电脑坏了，但幸运的是我备份了文件夹 C:\Program Files\PostgreSQL。

Now I'm working in a new computer and I would like to import the previous Postgres databases that are stored in the external disk.

现在我在一台新计算机上工作，我想导入以前存储在外部磁盘中的 Postgres 数据库。

I would like to export the schema of a specific database that is located in the backup folder.

我想导出位于备份文件夹中的特定数据库的架构。

The file PostgreSQL\8.3\data\global\pg_databasecontains information about databases and their OIDs; for example:

该文件PostgreSQL\8.3\data\global\pg_database包含有关数据库及其 OID 的信息；例如：

"db1" 20012
"db2" 23456

I would like to export the schema of "db1".

我想导出“db1”的架构。

There is a folder named "20012" in folder "PostgreSQL\8.3\data\base\20012"that contains a lot of files [500 files].

文件夹中有一个名为“20012”的文件夹"PostgreSQL\8.3\data\base\20012"，里面有很多文件[500个文件]。

Is there any way to export the schema of that database?

有没有办法导出该数据库的架构？

Note that all of the Postgresql database files are located in an external hard disk and I would like to export the schema of that database in an SQL file, take that file, run it and create the same exact database locally.

请注意，所有 Postgresql 数据库文件都位于外部硬盘中，我想将该数据库的架构导出到一个 SQL 文件中，获取该文件，运行它并在本地创建完全相同的数据库。

回答by Anew

You should take a look at pg_dump:

你应该看看pg_dump：

pg_dump -s databasename

Will dump only the schema to stdout as .sql.

将仅将模式转储到 stdout 作为 .sql。

For windows, you'll probably want to call pg_dump.exe. I don't have access to a Windows machine but I'm pretty sure from memory that's the command. See if the help works for you too.

对于 Windows，您可能需要调用pg_dump.exe. 我无法访问 Windows 机器，但我很确定这是命令。看看帮助是否也适合你。

回答by lev09

In Linuxyou can do like this

在Linux 中你可以这样做

pg_dump -U postgres -s postgres > exportFile.dmp

Maybe it can work in Windowstoo, if not try the same with pg_dump.exe

也许它也可以在Windows中工作，如果不尝试与pg_dump.exe相同

pg_dump.exe -U postgres -s postgres > exportFile.dmp

回答by James Jithin

I am running Postgres 9.6 where I had to export a particular schema along with data.

我正在运行 Postgres 9.6，我必须在其中导出特定模式和数据。

I used the following command:

我使用了以下命令：

pg_dump.exe -U username -d databasename -n schemaname > C:\mylocation\mydumpfilename.dmp

If you want only the schema without data, use the switch sinstead of n

如果您只想要没有数据的架构，请使用 switchs而不是n

Below is the pg_dump switch list:

下面是 pg_dump 开关列表：

C:\Program Files\PostgreSQL.6\bin>pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=SCHEMA          dump the named schema(s) only
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only
  -T, --exclude-table=TABLE    do NOT dump the named table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <[email protected]>.

回答by alfons

pg_dump -d <databasename> -h <hostname> -p <port> -n <schemaname> -f <location of the dump file>

Please notice that you have sufficient privilege to access that schema. If you want take backup as specific user add user name in that command preceded by -U

请注意，您有足够的权限访问该架构。如果您想以特定用户的身份进行备份，请在该命令中添加用户名-U

回答by arod

If you only want the create tables, then you can do pg_dump -s databasename | awk 'RS="";/CREATE TABLE[^;]*;/'

如果你只想要创建表，那么你可以做 pg_dump -s databasename | awk 'RS="";/CREATE TABLE[^;]*;/'

回答by drone.ah

set up a new postgresql server and replace its data folder with the files from your external disk.

设置一个新的 postgresql 服务器并将其数据文件夹替换为外部磁盘中的文件。

You will then be able to start that postgresql server up and retrieve the data using pg_dump (pg_dump -s for the schema-only as mentioned)

然后，您将能够启动该 postgresql 服务器并使用 pg_dump 检索数据（pg_dump -s 仅用于模式如上所述）

回答by Lalit Bangad

For Linux:(data excluded)

对于Linux：（数据除外）

• pg_dump -s -t tablename databasename > dump.sql(For a specific table in database)

• pg_dump -s databasename > dump.sql(For the entire database)

• pg_dump -s -t tablename databasename > dump.sql（对于数据库中的特定表）

• pg_dump -s databasename > dump.sql（对于整个数据库）

回答by Hayk Petrosyan

You should use something like this pg_dump --schema=your_schema_name db1, for details take a look here

你应该使用这样的东西pg_dump --schema=your_schema_name db1，详情请看这里