连接 PostgreSQL

要使用 psql 工具管理 PostgreSQL 服务器，请先连接到 PostgreSQL 服务器。 命令如下：

1
psql -d dbname -U  user -W

如果您需要连接一个远程的 PostgreSQL 服务器，请使用如下命令：

1
psql -h host -p port -d dbname -U  user -W

其中：

• -h 参数用于指定远程 PostgreSQL 服务器的主机名或者 IP 地址。 默认值为 localhost。
• -p 参数用于指定远程 PostgreSQL 服务器的端口号。默认值为 5432。

psql 常用命令

当您使用 psql 登录进 PostgreSQL 服务器以后，您就可以使用下面的命令管理服务器了。

1
\l

1
\l+

1
\c dbname

1
\c - username

1
\dt

1
\dt+

1
\d table_name

1
\d product

1
testdb=# \d product
2
                                 Table "public.product"
3
    Column    |       Type        | Collation | Nullable |           Default
4
--------------+-------------------+-----------+----------+------------------------------
5
 id           | integer           |           | not null | generated always as identity
6
 product_name | character varying |           | not null |
7
 attributes   | hstore            |           |          |
8
Indexes:
9
    "product_pkey" PRIMARY KEY, btree (id)

1
\dn

1
  List of schemas
2
  Name  |  Owner
3
--------+----------
4
 public | postgres

1
\df

1
                                                    List of functions
2
 Schema |           Name           |  Result data type  |                   Argument data types                   | Type
3
--------+--------------------------+--------------------+---------------------------------------------------------+------
4
 public | akeys                    | text[]             | hstore                                                  | func
5
 public | avals                    | text[]             | hstore                                                  | func
6
 public | defined                  | boolean            | hstore, text                                            | func
7
 public | delete                   | hstore             | hstore, hstore                                          | func
8
 public | delete                   | hstore             | hstore, text                                            | func
9
 public | delete                   | hstore             | hstore, text[]                                          | func
10
 public | each                     | SETOF record       | hs hstore, OUT key text, OUT value text                 | func
11
 public | exist                    | boolean            | hstore, text                                            | func
12
 public | exists_all               | boolean            | hstore, text[]                                          | func
13
 public | exists_any               | boolean            | hstore, text[]                                          | func
14
 public | fetchval                 | text               | hstore, text                                            | func
15
 public | ghstore_compress         | internal           | internal                                                | func
16
 public | ghstore_consistent       | boolean            | internal, hstore, smallint, oid, internal               | func
17
 public | ghstore_decompress       | internal           | internal                                                | func
18
 public | ghstore_in               | ghstore            | cstring                                                 | func
19
 public | ghstore_options          | void               | internal                                                | func
20
 public | ghstore_out              | cstring            | ghstore                                                 | func
21
 public | ghstore_penalty          | internal           | internal, internal, internal                            | func
22
 public | ghstore_picksplit        | internal           | internal, internal                                      | func
23
 public | ghstore_same             | internal           | ghstore, ghstore, internal                              | func
24
 public | ghstore_union            | ghstore            | internal, internal                                      | func
25
 public | gin_consistent_hstore    | boolean            | internal, smallint, hstore, integer, internal, internal | func
26
 public | gin_extract_hstore       | internal           | hstore, internal                                        | func
27
 public | gin_extract_hstore_query | internal           | hstore, internal, smallint, internal, internal          | func
28
 public | hs_concat                | hstore             | hstore, hstore                                          | func
29
 public | hs_contained             | boolean            | hstore, hstore                                          | func
30
 public | hs_contains              | boolean            | hstore, hstore                                          | func
31
 public | hstore                   | hstore             | record                                                  | func
32
 public | hstore                   | hstore             | text, text                                              | func
33
 public | hstore                   | hstore             | text[]                                                  | func
34
 public | hstore                   | hstore             | text[], text[]                                          | func
35
 public | hstore_cmp               | integer            | hstore, hstore                                          | func
36
 public | hstore_eq                | boolean            | hstore, hstore                                          | func
37
 public | hstore_ge                | boolean            | hstore, hstore                                          | func
38
 public | hstore_gt                | boolean            | hstore, hstore                                          | func
39
 public | hstore_hash              | integer            | hstore                                                  | func
40
 public | hstore_hash_extended     | bigint             | hstore, bigint                                          | func
41
 public | hstore_in                | hstore             | cstring                                                 | func
42
 public | hstore_le                | boolean            | hstore, hstore                                          | func
43
 public | hstore_lt                | boolean            | hstore, hstore                                          | func
44
 public | hstore_ne                | boolean            | hstore, hstore                                          | func
45
 public | hstore_out               | cstring            | hstore                                                  | func
46
 public | hstore_recv              | hstore             | internal                                                | func
47
 public | hstore_send              | bytea              | hstore                                                  | func
48
 public | hstore_subscript_handler | internal           | internal                                                | func
49
 public | hstore_to_array          | text[]             | hstore                                                  | func
50
 public | hstore_to_json           | json               | hstore                                                  | func
51
 public | hstore_to_json_loose     | json               | hstore                                                  | func
52
 public | hstore_to_jsonb          | jsonb              | hstore                                                  | func
53
 public | hstore_to_jsonb_loose    | jsonb              | hstore                                                  | func
54
 public | hstore_to_matrix         | text[]             | hstore                                                  | func
55
 public | hstore_version_diag      | integer            | hstore                                                  | func
56
 public | isdefined                | boolean            | hstore, text                                            | func
57
 public | isexists                 | boolean            | hstore, text                                            | func
58
 public | my_time_multirange       | my_time_multirange |                                                         | func
59
 public | my_time_multirange       | my_time_multirange | VARIADIC my_time_range[]                                | func
60
 public | my_time_multirange       | my_time_multirange | my_time_range                                           | func
61
 public | my_time_range            | my_time_range      | time without time zone, time without time zone          | func
62
 public | my_time_range            | my_time_range      | time without time zone, time without time zone, text    | func
63
 public | populate_record          | anyelement         | anyelement, hstore                                      | func
64
 public | skeys                    | SETOF text         | hstore                                                  | func
65
 public | slice                    | hstore             | hstore, text[]                                          | func
66
 public | slice_array              | text[]             | hstore, text[]                                          | func
67
 public | svals                    | SETOF text         | hstore                                                  | func
68
 public | tconvert                 | hstore             | text, text                                              | func
69
(65 rows)

1
\dv

1
\du

1
                                   List of roles
2
 Role name |                         Attributes                         | Member of
3
-----------+------------------------------------------------------------+-----------
4
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

1
\timing
2
select * from product;

1
 id | product_name |                          attributes
2
----+--------------+--------------------------------------------------------------
3
  2 | Shirt B      | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
4
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"Dell", "Memory"=>"16G"
5
(2 rows)
6

7
Time: 0.281 ms

1
\s

1
\s filename

1
\g

1
\h sql_command

1
\h TRUNCATE

1
Command:     TRUNCATE
2
Description: empty a table or set of tables
3
Syntax:
4
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
5
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
6

7
URL: https://www.postgresql.org/docs/14/sql-truncate.html

1
\?

1
\i filename

1
\x
2
select * from product;

1
-[ RECORD 1 ]+-------------------------------------------------------------
2
id           | 2
3
product_name | Shirt B
4
attributes   | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
5
-[ RECORD 2 ]+-------------------------------------------------------------
6
id           | 1
7
product_name | Computer A
8
attributes   | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"Dell", "Memory"=>"16G"

1
\q

结论

本文向您展示了 psql 工具的常用的命令。

使用 \l 列出数据库

本实例演示了使用 psql 工具登录数据库并列出数据库的步骤。请按照如下步骤进行：

1. 使用 postgres 用户登录 PostgreSQL 服务器：

   1
   [~] psql -U postgres
   2
   psql (14.4)
   3
   Type "help" for help.

   注意：您也可以使用其他任何具有相应的数据库权限的用户登录。

2. 使用 \l 命令列出所有的数据库，如下：

   1
   \l

   1
                                 List of databases
   2
     Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
   3
   -----------+----------+----------+---------+---------+-----------------------
   4
   postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
   5
   template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
   6
             |          |          |         |         | postgres=CTc/postgres
   7
   template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
   8
             |          |          |         |         | postgres=CTc/postgres
   9
   testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
   10
   testdb2   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
   11
   (5 rows)

3. 如果要查看更多关于数据库的信息，请使用 \l+ 命令，如下：

   1
   \l+

   1
                                                                   List of databases
   2
     Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   |  Size   | Tablespace |                Description
   3
   -----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
   4
   postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8529 kB | pg_default | default administrative connection database
   5
   template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8377 kB | pg_default | unmodifiable empty database
   6
             |          |          |         |         | postgres=CTc/postgres |         |            |
   7
   template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8529 kB | pg_default | default template for new databases
   8
             |          |          |         |         | postgres=CTc/postgres |         |            |
   9
   testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8897 kB | pg_default |
   10
   testdb2   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8545 kB | pg_default |
   11
   (5 rows)

您可以看到， \l+ 的输出比 \l 多了 Size, Tablespace 和 Description 列。

1
SELECT datname FROM pg_database;

1
  datname
2
-----------
3
 postgres
4
 testdb
5
 template1
6
 template0
7
 testdb2
8
(5 rows)

结论

PostgreSQL 提供了两种方法列出 PostgreSQL 服务器中的所有的数据库中：

• 在 psql 工具中使用 \l 或者 \l+ 列出当所有的数据库。
• 从 pg_database 表中查询所有的数据库。

在 MySQL 中，您可以使用 SHOW DATABASES 命令列出数据库。

从模板数据库复制数据库

有时候，为了数据的安全性，您在操作之前需要先将要操作数据库复制备份。 您可以使用 CREATE DATABASE 将此数据库复制为一个新数据库，如下：

1
CREATE DATABASE new_db
2
WITH TEMPLATE old_db;

此语句将 复制 old_db 数据库到 new_db 数据库。 old_db 必须是模板数据库才能被复制。如果它不是模板数据库，您可以使用 ALTER DATABASE 语句将此数据库修改为模板数据库，如下：

1
ALTER DATABASE old_db WITH IS_TEMPLATE true;

此方法仅能用在同一个 PostgreSQL 数据库服务器内。如果您想在不同的 PostgreSQL 数据库服务器间复制数据库，请查看 PostgreSQL 备份和恢复教程。

结论

PostgreSQL 允许您使用 CREATE DATABASE 语句复制一个模板数据库。

PostgreSQL 数据库大小

您可以使用 pg_database_size() 函数获取整个数据库的大小。例如，以下语句返回 testdb 数据库的大小：

1
SELECT pg_database_size('testdb');

1
 pg_database_size
2
------------------
3
          9044483

pg_database_size() 函数以字节为单位返回数据库的大小，这不容易阅读。您可以使用 pg_size_pretty() 函数将字节转为更易于阅读值。如下：

1
SELECT
2
  pg_size_pretty(
3
    pg_database_size('testdb')
4
  );

该语句返回以下结果：

1
 pg_size_pretty
2
----------------
3
 8833 kB

如果您想要要获取当前数据库服务器中所有数据库的大小，请使用以下语句：

1
SELECT
2
  datname,
3
  pg_size_pretty(pg_database_size(datname)) AS size
4
  FROM pg_database;

1
   datname   |  size
2
-------------+---------
3
 postgres    | 8561 kB
4
 template1   | 8401 kB
5
 template0   | 8401 kB
6
 testdb      | 8833 kB
7
 sakila      | 16 MB
8
 testdb2     | 8521 kB
9
 test_new_db | 8401 kB

PostgreSQL 表大小

您可以使用 pg_relation_size() 函数获取一个表的大小。例如，以下语句返回 Sakila 示例数据库中的 actor 表的大小：

1
SELECT
2
  pg_size_pretty(
3
    pg_relation_size('actor')
4
  );

1
 pg_size_pretty
2
----------------
3
 16 kB

pg_relation_size() 函数返回表的数据的大小，不包含表中的索引的大小。如果要获取表的总的大小，请使用 pg_total_relation_size() 函数， 如下：

1
SELECT
2
  pg_size_pretty(
3
    pg_total_relation_size('actor')
4
  );

1
 pg_size_pretty
2
----------------
3
 72 kB

要获取数据库中所有的表的大小，您可以使用如下语句：

1
SELECT
2
  tablename,
3
  pg_size_pretty(pg_total_relation_size('actor')) size
4
FROM pg_tables
5
WHERE schemaname = 'public';

1
      tablename       | size
2
----------------------+-------
3
 actor                | 72 kB
4
 film                 | 72 kB
5
 payment_p2007_02     | 72 kB
6
 payment_p2007_03     | 72 kB
7
 payment_p2007_04     | 72 kB
8
 payment_p2007_05     | 72 kB
9
 payment_p2007_06     | 72 kB
10
 payment_p2007_01     | 72 kB
11
 address              | 72 kB
12
 category             | 72 kB
13
 city                 | 72 kB
14
 country              | 72 kB
15
 customer             | 72 kB
16
 film_actor           | 72 kB
17
 film_category        | 72 kB
18
 inventory            | 72 kB
19
 language             | 72 kB
20
 rental               | 72 kB
21
 staff                | 72 kB
22
 store                | 72 kB
23
 payment              | 72 kB
24
 film_copy            | 72 kB
25
 city_copy            | 72 kB
26
 film_r               | 72 kB
27
 film_ranting_g_title | 72 kB

PostgreSQL 索引大小

PostgreSQL pg_indexes_size() 函数用于获取一个指定表上的索引的大小。例如，要获取 actor 表的所有索引的总大小，请使用以下语句：

1
SELECT
2
  pg_size_pretty(
3
    pg_indexes_size('actor')
4
  );

1
 pg_size_pretty
2
----------------
3
 32 kB

PostgreSQL 表空间大小

PostgreSQL pg_tablespace_size() 函数用于获取一个指定的表空间的大小。

以下语句返回 pg_default 表空间的大小：

1
SELECT
2
  pg_size_pretty (
3
      pg_tablespace_size('pg_default')
4
  );

1
 pg_size_pretty
2
----------------
3
 67 MB

PostgreSQL 值大小

PostgreSQL pg_column_size() 函数用于获取指定的值占用的空间，例如：

以下语句返回一个 smallint 类型的值的大小：

1
select pg_column_size(1::smallint);

1
 pg_column_size
2
----------------
3
              2

以下语句返回一个 int 类型的值的大小：

1
select pg_column_size(1::int);

1
 pg_column_size
2
----------------
3
              4

以下语句返回一个 bigint 类型的值的大小：

1
select pg_column_size(1::bigint);

1
 pg_column_size
2
----------------
3
              8

结论

本文讲述了几个的函数来获取数据库、表、索引、表空间和值的大小。

使用 \dt 列出数据库中的表

本实例演示了使用 psql 工具登录数据库并列出数据库中表的过程。请按照如下步骤进行：

1. 使用 postgres 用户登录 PostgreSQL 服务器：

   1
   [~] psql -U postgres
   2
   psql (14.4)
   3
   Type "help" for help.

   注意：您也可以使用其他任何具有相应的数据库权限的用户登录。

2. 使用以下语句选择 testdb 数据库：

   1
   \c testdb;

   如果还未创建数据库，请先运行如下语句：

   1
   CREATE DATABASE testdb;

3. 使用 \dt 命令列出 testdb 数据库中的所有的表，如下：

   1
   \dt

   1
               List of relations
   2
   Schema |      Name      | Type  |  Owner
   3
   --------+----------------+-------+----------
   4
   public | mytable        | table | postgres
   5
   public | product        | table | postgres
   6
   public | test_date      | table | postgres
   7
   public | test_time      | table | postgres
   8
   public | test_timestamp | table | postgres
   9
   public | week_day_sales | table | postgres
   10
   (6 rows)

4. 如果要查看更多关于表的信息，请使用 \dt+ 命令，如下：

   1
   \dt+

   1
                                             List of relations
   2
   Schema |      Name      | Type  |  Owner   | Persistence | Access method |    Size    | Description
   3
   --------+----------------+-------+----------+-------------+---------------+------------+-------------
   4
   public | mytable        | table | postgres | permanent   | heap          | 16 kB      |
   5
   public | product        | table | postgres | permanent   | heap          | 16 kB      |
   6
   public | test_date      | table | postgres | permanent   | heap          | 8192 bytes |
   7
   public | test_time      | table | postgres | permanent   | heap          | 8192 bytes |
   8
   public | test_timestamp | table | postgres | permanent   | heap          | 8192 bytes |
   9
   public | week_day_sales | table | postgres | permanent   | heap          | 8192 bytes |
   10
   (6 rows)

您可以看到， \dt+ 的输入比 \dt 输出多了 Persistence, Access method, Size 和 Description 列。

从 pg_tables 表中查询表

除了上面的 \dt 和 \dt+ 命令，您还可以从 pg_tables 表中查询当前数据中的所有的表。

pg_tables 表是 PostgreSQL 内置的一个表，它存储了数据库中的所有的表。

1
SELECT * FROM pg_tables
2
WHERE schemaname = 'public';

1
 schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
2
------------+----------------+------------+------------+------------+----------+-------------+-------------
3
 public     | test_date      | postgres   |            | t          | f        | f           | f
4
 public     | test_time      | postgres   |            | t          | f        | f           | f
5
 public     | test_timestamp | postgres   |            | t          | f        | f           | f
6
 public     | week_day_sales | postgres   |            | t          | f        | f           | f
7
 public     | mytable        | postgres   |            | f          | f        | f           | f
8
 public     | product        | postgres   |            | t          | f        | f           | f
9
(6 rows)

结论

PostgreSQL 提供了两种方法列出一个数据库中的所有表：

• 在 psql 工具中使用 \dt 或者 \dt+ 列出当前数据库中的所有的表。
• 从 pg_tables 表中查询所有的表。

在 MySQL 中，您可以使用 SHOW TABLES 命令列出数据库。

使用 \d 查看表的信息

本实例演示了使用 psql 工具登录数据库并查看表信息的详细步骤。请按照如下步骤进行：

1. 使用 postgres 用户登录 PostgreSQL 服务器：

   1
   [~] psql -U postgres
   2
   psql (14.4)
   3
   Type "help" for help.

   注意：您也可以使用其他任何具有相应的数据库权限的用户登录。

2. 使用以下语句选择 testdb 数据库：

   1
   \c testdb;

   如果还未创建数据库，请先运行如下语句：

   1
   CREATE DATABASE testdb;

3. 以下语句使用 \d 命令查看 test_date 表的结构，如下：

   1
   \d test_date

   1
                           Table "public.test_date"
   2
      Column   |  Type   | Collation | Nullable |           Default
   3
   ------------+---------+-----------+----------+------------------------------
   4
    id         | integer |           | not null | generated always as identity
   5
    date_value | date    |           | not null | CURRENT_DATE
   6
    Indexes:
   7
       "test_date_pkey" PRIMARY KEY, btree (id)

   您可以看到，\d 输出了表的名字、表中的列，表中的约束等信息。

4. 如果要查看更多关于 test_date 表的信息，请使用 \d+ 命令，如下：

   1
   \d+ test_date

   1
                                                      Table "public.test_date"
   2
      Column   |  Type   | Collation | Nullable |           Default            | Storage | Compression | Stats target | Description
   3
   ------------+---------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
   4
    id         | integer |           | not null | generated always as identity | plain   |             |              |
   5
    date_value | date    |           | not null | CURRENT_DATE                 | plain   |             |              |
   6
    Indexes:
   7
       "test_date_pkey" PRIMARY KEY, btree (id)
   8
    Access method: heap

   您可以看到， \d+ 的输入比 \d 输出多了 Compression, Stats target 和 Description 列。

从 information_schema 中查看表中的所有列

information_schema 是一个系统级的 Schema, 其中提供了一些视图可以查看表、列、索引、函数等信息。

该 information_schema.columns 目录包含有关所有表的列的信息。

以下语句从 information_schema.columns 中查询 test_date 表的所有的列：

1
SELECT
2
   table_name,
3
   column_name,
4
   data_type,
5
   column_default
6
FROM
7
   information_schema.columns
8
WHERE
9
   table_name = 'test_date';

1
 table_name | column_name | data_type | column_default
2
------------+-------------+-----------+----------------
3
 test_date  | id          | integer   |
4
 test_date  | date_value  | date      | CURRENT_DATE
5
(2 rows)

以上语句返回了 test_date 表的所有的列的信息，包括 列名，数据类型，默认值。

结论

PostgreSQL 提供了两种方法查看一个现有的表的定义或者结构：

• 在 psql 工具中使用 \d 或者 \d+ 列出当前当前数据库中的所有的表。
• 从 information_schema.columns 中查询表中的列。

在 MySQL 中，您可以使用 DESCRIBE 命令列出查看表中的列。

使用 CREATE TABLE ... AS TABLE ... 语句复制一个表

要将已有的 table_name 表复制为新表 new_table，包括表结构和数据，请使用以下语句：

1
CREATE TABLE new_table
2
AS TABLE table_name;

如果仅复制表结构，不复制数据，请在以上 CREATE TABLE 语句中添加 WITH NO DATA 子句，如下所示：

1
CREATE TABLE new_table
2
AS TABLE table_name
3
WITH NO DATA;

使用 CREATE TABLE ... AS SELECT ... 语句复制一个表

您还可以使用 CREATE TABLE ... AS SELECT ... 语句复制一个表。 这种方法可以复制部分数据到新表中。

要将已有的 table_name 表复制为新表 new_table，包括表结构和数据，请使用以下语句：

1
CREATE TABLE new_table AS
2
SELECT * FROM table_name;

如果您只需要复制部分满足条件的数据，请在 SELECT 语句中添加 WHERE 子句，如下：

1
CREATE TABLE new_table AS
2
SELECT * FROM table_name
3
WHERE contidion;

如果您只需要复制部分列到新表，请在 SELECT 语句中指定要复制的列的列表，如下：

1
CREATE TABLE new_table AS
2
SELECT column1, column2, ... FROM table_name
3
WHERE contidion;

如果您只需要复制表结构，请按如下方式使用 WHERE 子句：

1
CREATE TABLE new_table AS
2
SELECT * FROM table_name
3
WHERE 1 = 2;

这里在 WHERE 子句中使用了一个永远为假的条件。

使用 SELECT ... INTO ... 语句复制一个表

要使用 PostgreSQL SELECT INTO 语句将已有的 table_name 表复制为新表 new_table，请使用以下语法：

1
SELECT *
2
INTO new_table
3
FROM table_name

如果您只需要复制部分满足条件的数据，请添加 WHERE 子句，如下：

1
SELECT *
2
INTO new_table
3
FROM table_name
4
WHERE contidion;

如果您只需要复制部分列到新表，请在 SELECT 语句中指定要复制的列的列表，如下：

1
SELECT column1, column2, ...
2
INTO new_table
3
FROM table_name
4
WHERE contidion;

结论

本文阐述了在 PostgreSQL 中复制表的几种方法。注意，这几种方法都只能复制列的定义和数据，都不能将索引复制过去。

使用 pg_dump 备份一个数据库

PostgreSQL 自带了 pg_dump 工具用于备份单个 PostgreSQL 数据库。 以下是一个常用的备份命令：

1
pg_dump -U username -W -F t db_name > output.tar

说明：

• -U username: 指定连接 PostgreSQL 数据库服务器的用户。您可以在 username 位置使用自己的用户名。
• -W: 强制 pg_dump 在连接到 PostgreSQL 数据库服务器之前提示输入密码。按回车后， pg_dump 会提示输入 postgres 用户密码。
• -F : 指定输出文件的格式，它可以是以下格式之一：
  • c: 自定义格式
  • d: 目录格式存档
  • t: tar 文件包
  • p: SQL 脚本文件
• db_name 是要备份的数据库的名字。
• output.tar 是输出文件的路径。

如果您在命令行或者终端工具中运行命令是提示找不到 pg_dump 工具，请先导航到 PostgreSQL bin 文件夹。例如：

1
C:\>cd C:\Program Files\PostgreSQL\14\bin

使用 pg_dumpall 备份所有数据库

除了 pg_dump 工具，PostgreSQL 还提供了可以一次备份所有数据库的 pg_dumpall 工具备份。 该 pg_dumpall 工具的用法如下：

1
pg_dumpall -U username > output.sql

使用 pg_restore 恢复数据库

PostgreSQL 提供了 pg_restore 工具用于恢复由 pg_dump 工具产生的 tar 文档和目录文档。

pg_restore 工具的用法如下：

1
pg_restore [option...] file_path

说明：

• file_path 是要恢复的文件或者目录的路径。

• 1
  option

  是一些恢复数据时用到的参数，比如，数据库，主机，端口 等。 您可以使用如下选项：

  参数	说明
  -a --data-only	只恢复数据，而不恢复表模式（数据定义）。
  -c --clean	创建数据库对象前先清理（删除）它们。
  -C --create	在恢复数据库之前先创建它。
  -d dbname --dbname=dbname	与数据库 dbname 联接并且直接恢复到该数据库中。
  -e --exit-on-error	如果在向数据库发送 SQL 命令的时候碰到错误，则退出。缺省是继续执行并且在恢复 结束时显示一个错误计数。
  -f filename --file=filename	声明生成的脚本的输出文件，或者出现-l 选项时用于列表的文件，缺省是标准输出。
  -F format --format=format	声明备份文件的格式。
  -i --ignore-version	忽略数据库版本检查。
  -I index --index=index	只恢复命名的索引。
  -l --list	列出备份的内容。这个操作的输出可以用 -L 选项限制和重排所恢复的项目。
  -L list-file --use-list=list-file	只恢复在 list-file 里面的元素，以它们在文件中出现的顺序。
  -n namespace --schema=schema	只恢复指定名字的模式里面的定义和/或数据。不要和 -s 选项混淆。这个选项可以和 -t 选项一起使用。
  -O --no-owner	不要输出设置对象的权限，以便与最初的数据库匹配的命令。
  -s --schema-only	只恢复表结构（数据定义）。不恢复数据，序列值将重置。
  -S username --superuser=username	设置关闭触发器时声明超级用户的用户名。只有在设置了 –disable-triggers 的时候 才有用。
  -t table --table=table	只恢复表指定的表的定义和/或数据。
  -T trigger --trigger=trigger	只恢复指定的触发器。
  -v --verbose	声明冗余模式。
  -x --no-privileges --no-acl	避免 ACL 的恢复（grant/revoke 命令）
  -X use-set-session-authorization --use-set-session-authorization	输出 SQL 标准的 SET SESSION AUTHORIZATION 命令，而不是 OWNER TO 命令。
  -X disable-triggers --disable-triggers	这个选项只有在执行仅恢复数据的时候才相关。
  -h host --host=host	声明服务器运行的机器的主机名。
  -p port --port=port	声明服务器侦听的 TCP 端口或者本地的 Unix 域套接字文件扩展。
  -U username	以给出用户身分联接。
  -W	强制给出口令提示。如果服务器要求口令认证，那么这个应该自动发生。

最常用的 pg_restore 用法如下：

1
pg_restore -d db_name path_to_db_backup_file.tar

如何使用 psql 恢复数据库

您可以使用 psql 工具从一个 sql 文件中恢复数据。 以下是使用 psql 从 sql 文件恢复数据的基本用法：

1
psql -U username -f path_to_db_backup_file.sql

使用 \i 命令导入 sql 文件

您还可以 \i 命令导入 sql 文件。 以下演示了导入 sakila 示例数据库的步骤：

1. 启动 psql 工具并连接 PostgreSQL 服务器：

   1
   .\psql.exe -U postgres

   根据提示输入 postgres 用户的密码，然后按下回车键。

2. 创建 sakila 数据库

   1
   CREATE DATABASE sakila;

3. 连接 sakila 数据库

   1
   \c sakila;

4. 分别使用以下两个语句以导入刚刚下载的两个文件 postgres-sakila-schema.sql 和 postgres-sakila-insert-data.sql ：

   1
   \i C:/Users/Adam/Downloads/postgres-sakila-schema.sql
   2
   \i C:/Users/Adam/Downloads/postgres-sakila-insert-data.sql

   请注意，请使用 / 替换文件路径中的 \ 。

结论

本文讨论了备份和恢复 PostgreSQL 数据库的几种方法。