1 of 17

II. PostgreSQL 用戶端工具

這裡包含了 PostgreSQL 用戶端應用工具程式的參考資訊。並非所有的命令都具有通用性。有些可能需要特殊權限。這些應用程式的共同特徵是它們可以在任何主機上執行，而與資料庫伺服器所在的地點無關。

在命令列上指定參數時，使用者名稱和資料庫名稱將保留大小寫，如果有空格或特殊字元，則可能需要加引號。資料表名稱和其他標識符號也不保留大小寫，除非有說明，否則可能也需要引號。

createdb

createdb — create a newPostgreSQLdatabase

Synopsis

createdb[connection-option...] [option...] [dbname[description]]

Description

createdbcreates a newPostgreSQLdatabase.

Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the-Ooption, if the executing user has appropriate privileges.

createdbis a wrapper around theSQLcommandCREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server.

Options

createdbaccepts the following command-line arguments:

dbname

Specifies the name of the database to be created. The name must be unique among allPostgreSQLdatabases in this cluster. The default is to create a database with the same name as the current system user.

description

Specifies a comment to be associated with the newly created database.

-D

tablespace

--tablespace=

tablespace

Specifies the default tablespace for the database. (This name is processed as a double-quoted identifier.)

-e

--echo

Echo the commands thatcreatedbgenerates and sends to the server.

-E

encoding

--encoding=

encoding

Specifies the character encoding scheme to be used in this database. The character sets supported by thePostgreSQLserver are described inSection 23.3.1.

-l

locale

--locale=

locale

Specifies the locale to be used in this database. This is equivalent to specifying both--lc-collateand--lc-ctype.

--lc-collate=

locale

Specifies the LC_COLLATE setting to be used in this database.

--lc-ctype=

locale

Specifies the LC_CTYPE setting to be used in this database.

-O

owner

--owner=

owner

Specifies the database user who will own the new database. (This name is processed as a double-quoted identifier.)

-T

template

--template=

template

Specifies the template database from which to build this database. (This name is processed as a double-quoted identifier.)

-V

--version

Print thecreatedbversion and exit.

-?

--help

Show help aboutcreatedbcommand line arguments, and exit.

The options-D,-l,-E,-O, and-Tcorrespond to options of the underlying SQL commandCREATE DATABASE; see there for more information about them.

createdbalso accepts the following command-line arguments for connection parameters:

-h

host

--host=

host

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

-p

port

--port=

port

Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.

-U

username

--username=

username

User name to connect as.

-w

--no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a.pgpassfile, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W

--password

Forcecreatedbto prompt for a password before connecting to a database.

This option is never essential, sincecreatedbwill automatically prompt for a password if the server demands password authentication. However,createdbwill waste a connection attempt finding out that the server wants a password. In some cases it is worth typing-Wto avoid the extra connection attempt.

--maintenance-db=

dbname

Specifies the name of the database to connect to when creating the new database. If not specified, thepostgresdatabase will be used; if that does not exist (or if it is the name of the new database being created),template1will be used.

Environment

PGDATABASE

If set, the name of the database to create, unless overridden on the command line.

PGHOST

PGPORT

PGUSER

Default connection parameters.PGUSERalso determines the name of the database to create, if it is not specified on the command line or byPGDATABASE.

This utility, like most otherPostgreSQLutilities, also uses the environment variables supported bylibpq(seeSection 33.14).

Diagnostics

In case of difficulty, seeCREATE DATABASEandpsqlfor discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by thelibpqfront-end library will apply.

Examples

To create the databasedemousing the default database server:

$ 
createdb demo

To create the databasedemousing the server on hosteden, port 5000, using thetemplate0template database, here is the command-line command and the underlying SQL command:

$ 
createdb -p 5000 -h eden -T template0 -e demo
CREATE DATABASE demo TEMPLATE template0;

createuser

createuser — 定義一個新的 PostgreSQL 使用者帳戶

語法

createuser [connection-option...] [option...] [username]

說明

createuser 建立一個新的 PostgreSQL 使用者（或更確切地說，一個角色）。只有具有 CREATEROLE 權限使用者或超級使用者才能建立新的使用者，因此必須由可以作為超級用戶或具有 CREATEROLE 權限的使用者進行連線使用 createuser。

如果要建立新的超級使用者，則必須以超級使用者身份進行連線，而不僅僅是擁有 CREATEROLE 權限。作為超級使用者意味著能夠繞過資料庫中的所有存取權限檢查，因此不應輕易授予超級使用者權限。

createuser 是 SQL 指令 CREATE ROLE 封裝的工具。透過此實用工具建立使用者和透過其他方法存取伺服器之間沒有任何區別。

選項

createuser 接受以下命令列選項：

username

指定要建立的 PostgreSQL 使用者名稱。此名稱必須與此 PostgreSQL 服務中的所有現有角色不同。

-c number --connection-limit=number

設定新使用者的最大連線數。預設為無限制。

-d --createdb

將允許新使用者建立資料庫。

-D --no-createdb

不允許新使用者建立資料庫。這是預設值。

-e --echo

顯示 createuser 産生並發送到伺服器的指令。

-E --encrypted

此選項已過時但仍可接受，為了向下相容。

-g role --role=role

表示此角色將作為新成員立即添加到的角色。可以透過加上多個 -g 來指定將此角色添加為成員的多個角色。

-i --inherit

新角色將自動繼承其所屬角色的權限。這是預設值。

-I --no-inherit

新角色不會自動繼承其所屬角色的權限。

--interactive

如果在命令列中未指定使用者名稱，則提示輸入使用者名稱，並且還會提示在命令列中未指定 -d/-D，-r/-R，-s/-S 中的任何選項。（這是 PostgreSQL 9.1 的預設行為。）

-l --login

將允許新使用者登入（即，使用者名稱可以用作初始連線使用者）。這是預設值。

-L --no-login

將不允許新使用者登入。（沒有登入權限的角色作為管理資料庫權限的作法仍然很有用。）

-P --pwprompt

如果有此選項，createuser 將發出新使用者密碼的提示。如果您不打算使用密碼身份驗證，則毌須執行此操作。

-r --createrole

將允許新使用者建立新角色（即，此使用者將具有 CREATEROLE 權限）。

-R --no-createrole

不允許新使用者建立新角色。這是預設值。

-s --superuser

新使用者將是超級使用者。

-S --no-superuser

新用戶不是超級用戶。這是預設值。

-V --version

輸出 createuser 版本然後退出。

--replication

--no-replication

-? --help

顯示有關 createuser 命令列選項的協助資訊，然後退出。

createuser 還接受以下連線參數的命令列選項：

-h host --host=host

指定運行伺服器的主機名稱。如果以斜槓開頭，則將其用作 Unix domain socke 的目錄。

-p port --port=port

指定伺服器正在監聽連線的 TCP 連接埠或本地 Unix domain socket 檔案延伸名稱。

-U username --username=username

要連線的使用者名稱（不是要建立的使用者名稱）。

-w --no-password

不要發出密碼提示。如果伺服器需要密碼身份驗證，並且其他方式（例如 .pgpass 檔案）也無法使用密碼，則連線嘗試將會失敗。此選項在沒有使用者輸入密碼的批處理作業和腳本中非常有用。

-W --password

強制 createuser 提示輸入密碼（用於連線伺服器，而不是新使用者的密碼）。

此選項都不是必須的，因為如果伺服器需要密碼身份驗證，createuser 將自動提示輸入密碼。只是，createuser 會浪費連線嘗試，為了發現伺服器需要密碼。在某些情況下，值得輸入 -W 以避免額外的連線嘗試。

環境變數

PGHOST PGPORT PGUSER

預設連線參數

例外

如果遇到困難，請參閱 CREATE ROLE 和 psql 以討論潛在問題和錯誤訊息。資料庫伺服器必須在目標主機上運行。此外，將套用 libpq 前端函式庫使用的任何預鉆水連線設定和環境變數。

範例

要在預設的資料庫伺服器上建立使用者 joe：

要在預設的資料庫伺服器上建立使用者 joe，並提示輸入一些其他的屬性：

要使用主機 eden 上的伺服器（連接埠 5000）建立相同的使用者 joe，並明確指定屬性，屬性請查看基礎指令：

要以超級使用者身份建立使用者 joe，並立即指定密碼：

在上面的範例中，新密碼在鍵入時實際上並未顯示，但為了清楚起見，我們顯示了鍵入的內容。如您所見，密碼在發送到用戶端之前已經加密過。

參閱

dropdb

dropdb — remove aPostgreSQLdatabase

Synopsis

dropdb[connection-option...] [option...]dbname

Description

dropdbdestroys an existingPostgreSQLdatabase. The user who executes this command must be a database superuser or the owner of the database.

dropdbis a wrapper around theSQLcommandDROP DATABASE. There is no effective difference between dropping databases via this utility and via other methods for accessing the server.

Options

dropdbaccepts the following command-line arguments:

dbname

Specifies the name of the database to be removed.

-e

--echo

Echo the commands thatdropdbgenerates and sends to the server.

-i

--interactive

Issues a verification prompt before doing anything destructive.

-V

--version

Print thedropdbversion and exit.

--if-exists

Do not throw an error if the database does not exist. A notice is issued in this case.

-?

--help

Show help aboutdropdbcommand line arguments, and exit.

dropdbalso accepts the following command-line arguments for connection parameters:

-h

host

--host=

host

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

-p

port

--port=

port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

-U

username

--username=

username

User name to connect as.

-w

--no-password

-W

--password

Forcedropdbto prompt for a password before connecting to a database.

This option is never essential, sincedropdbwill automatically prompt for a password if the server demands password authentication. However,dropdbwill waste a connection attempt finding out that the server wants a password. In some cases it is worth typing-Wto avoid the extra connection attempt.

--maintenance-db=

dbname

Specifies the name of the database to connect to in order to drop the target database. If not specified, thepostgresdatabase will be used; if that does not exist (or is the database being dropped),template1will be used.

Environment

PGHOST

PGPORT

PGUSER

Default connection parameters

This utility, like most otherPostgreSQLutilities, also uses the environment variables supported bylibpq(seeSection 33.14).

Diagnostics

In case of difficulty, seeDROP DATABASEandpsqlfor discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by thelibpqfront-end library will apply.

Examples

To destroy the databasedemoon the default database server:

$ 
dropdb demo

To destroy the databasedemousing the server on hosteden, port 5000, with verification and a peek at the underlying command:

$ 
dropdb -p 5000 -h eden -i -e demo
Database "demo" will be permanently deleted.
Are you sure? (y/n) 
y
DROP DATABASE demo;

dropuser

dropuser — 移除 PostgreSQL 使用者帳戶

語法

dropuser [connection-option...] [option...] [username]

說明

dropuser 移除現有的 PostgreSQL 使用者。只有具有 CREATEROLE 權限的超級使用者或一般使用者才能移除 PostgreSQL 使用者。（但要移除超級使用者，您還必須自己是超級使用者。）

dropuser 是 SQL 指令 DROP ROLE 的一個封裝。透過此實用工具和透過存取伺服器的其他方法移除使用者，之間沒有區別。

參數

dropuser 接受以下的命令列參數：

username

指定要移除的 PostgreSQL 使用者的名稱。如果在命令列中沒有指定名稱，則會提示您輸入名稱，如同使用 -i / -interactive。

-e --echo

顯示 dropuser 發送到伺服器的指令。

-i --interactive

在實際移除使用者之前提示確認，如果沒有在命令列中指定使用者名稱，會提示輸入。

-V --version

輸出 dropuser 版本然後退出。

--if-exists

如果使用者不存在，請不要拋出錯誤。在這種情況下發布 NOTICE。

-? --help

顯示有關 dropuser 命令列參數的說明，然後退出。

dropuser 還接受連線有關的以下命令列參數：

-h host --host=host

指定運行伺服器的主機名。如果以斜線開頭，則將其視為 Unix domain socket 的目錄。

-p port --port=port

指定伺服器正在監聽連線的 TCP 連接埠或本地 Unix domain socket 檔案延伸名稱。

-U username --username=username

要連線的使用者名稱（不是要移除的使用者名稱）。

-w --no-password

避免發出密碼提示。如果伺服器需要密碼驗證，請透過其他方式（如 .pgpass 檔案），無法使用密碼的話，則連線嘗試將會失敗。此選項可用於沒有使用者輸入密碼的批次處理作業和腳本。

-W --password

強制 dropuser 在連線到資料庫之前提示輸入密碼。

此選項從來不是必須的，因為如果伺服器需要密碼認證，dropuser 將自動提示輸入密碼。然而，dropuser 會浪費連線嘗試發現伺服器想要密碼。在某些情況下，值得輸入 -W 以避免額外的連線嘗試。

環境變數

PGHOST PGPORT PGUSER

預設連線參數

與其他大多數 PostgreSQL 實用工具一樣，此工具也使用 libpq 支援的環境變數（請參閱第 33.14 節）。

診斷

如果遇到困難，請參閱 DROP ROLE 和 psql，以便討論潛在問題和錯誤訊息。資料庫伺服器必須在目標主機上運行。此外，libpq 前端函式庫使用的任何預設連線設定和環境變數都將適用。

範例

要從預設資料庫伺服器中移除使用者 joe：

$ dropuser joe

使用主機 eden 的連接埠 5000 上的服務移除使用者 joe，驗證並查看基礎指令：

$ dropuser -p 5000 -h eden -i -e joe
Role "joe" will be permanently removed.
Are you sure? (y/n) y
DROP ROLE joe;

參閱

createuser, DROP ROLE

oid2name

oid2name — resolve OIDs and file nodes in a PostgreSQL data directory

Synopsis

oid2name [option...]

Description

oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL. To make use of it, you need to be familiar with the database file structure, which is described in .

Note

The name “oid2name” is historical, and is actually rather misleading, since most of the time when you use it, you will really be concerned with tables' filenode numbers (which are the file names visible in the database directories). Be sure you understand the difference between table OIDs and table filenodes!

oid2name connects to a target database and extracts OID, filenode, and/or table name information. You can also have it show database OIDs or tablespace OIDs.

Options

oid2name accepts the following command-line arguments:

-f filenode --filenode=filenode

show info for table with filenode filenode.

-i --indexes

include indexes and sequences in the listing.

-o oid --oid=oid

show info for table with OID oid.

-q --quiet

omit headers (useful for scripting).

-s --tablespaces

show tablespace OIDs.

-S --system-objects

include system objects (those in information_schema, pg_toast and pg_catalog schemas).

-t tablename_pattern --table=tablename_pattern

show info for table(s) matching tablename_pattern.

-V --version

Print the oid2name version and exit.

-x --extended

display more information about each object shown: tablespace name, schema name, and OID.

-? --help

Show help about oid2name command line arguments, and exit.

oid2name also accepts the following command-line arguments for connection parameters:

-d database --dbname=database

database to connect to.

-h host --host=host

database server's host.

-H host

database server's host. Use of this parameter is deprecated as of PostgreSQL 12.

-p port --port=port

database server's port.

-U username --username=username

user name to connect as.

To display specific tables, select which tables to show by using -o, -f and/or -t. -o takes an OID, -f takes a filenode, and -t takes a table name (actually, it's a LIKE pattern, so you can use things like foo%). You can use as many of these options as you like, and the listing will include all objects matched by any of the options. But note that these options can only show objects in the database given by -d.

If you don't give any of -o, -f or -t, but do give -d, it will list all tables in the database named by -d. In this mode, the -S and -i options control what gets listed.

If you don't give -d either, it will show a listing of database OIDs. Alternatively you can give -s to get a tablespace listing.

Environment

PGHOST PGPORT PGUSER

Default connection parameters.

Notes

oid2name requires a running database server with non-corrupt system catalogs. It is therefore of only limited use for recovering from catastrophic database corruption situations.

Examples

Author

pgbench

pgbench — 進行 PostgreSQL 效能評估

語法

pgbench-i[option...] [dbname]

pgbench[option...] [dbname]

說明

pgbench 是一個簡易型 PostgreSQL 的效能評估工具。它可以重覆執行某一系列的 SQL 指令，也可能進行大量連線的模擬情境，然後計算其平均的交易完成率（TPS, Transaction Per Second）。預設上，pgbench 使用的是 TPC-B 的標準情境，它在 1 個資料交易中會進行 5 個階段的資料操作，包含 SELECT、UPDATE、INSERT 指令。然而，你也可以使用你的腳本，來測試你的情境。

pgbench 大致上的輸出如下：

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 85.184871 (including connections establishing)
tps = 85.296346 (excluding connections establishing)

前 6 行說明該測試中最主要的操作參數。再下一行則是回報交易完成的數量，以及應該執行的數量（由 client 的數量與每個 client 須執行的交易量乘積而得）；這兩個數字應該要相等，如果每個交易都完成的話。（使用 -T 模式時，只會回報實際執行的交易數量）最後 2 行則以 TPS 回報，一行採計初始連線時間，另一行則沒有。

預設使用的 TPC-B 情境評估需要特定的資料庫結構，它們必須要在測試前先建立好。所以在測試之前，要先以「-i」參數執行初始化資料庫結構。（如果你使用自訂的情境測試，那就不需要進行這個步驟，但你可能需要另外自行建立你所需要的結構。）初始化指令如下：

pgbench -i [other-options] dbname

dbname 必須是已經存在的資料庫。（也許你需要再加上 -h、-p、--U 等參數來設定資料庫的連線參數。）

注意

pgbench -i 會建立 4 個表格：pgbench_accounts、pgbench_branches、pgbench_history、以及 pgbench_tellers。它會取代掉同名的表格。所以你如果和既有的資料庫共用的話，請注意同名的問題！

預設上，「scale factor」設定為 1，所產生的資料筆數如下：

table                   # of rows
---------------------------------
pgbench_branches        1
pgbench_tellers         10
pgbench_accounts        100000
pgbench_history         0

通常會使用 -s 參數來增加測試資料的數量。選項 -F 也可能在這時候使用。

一旦你完成了這個初始化的動作之後，後續的測試就不需要加上 -i 了：

pgbench [options] dbname

一般來說，你還會需要加上其他選項以進行更有意義的測試。最主要的測試選項為 -c （模擬用戶數量）、-t（資料交易數量）、-T（限時測試）、還有 -F（指定一個自訂的腳本）。完整選項如下。

選項

下面的部份分成三個小節：資料庫初始化專用選項、評估階段專用選項、一些通用的選項。

資料庫初始化專用選項

pgbench 在資料庫初始化時可以使用下列選項：

-i

--initialize

表示要進行資料庫初始化。

-F fillfactor

--fillfactor=fillfactor

建立 4 個表格：pgbench_accounts、pgbench_branches、pgbench_history、以及 pgbench_tellers。以預設的 fillfactor 填入資料，其預設值為 100。

-n

--no-vacuum

在初始化後不要進行資料庫整理（vacuum）的動作。

-q

--quiet

切換為安靜模式，只會每 5 秒輸出執行階段訊息。預設的模式是每 10,000 筆資料就輸出訊息，通常每秒都有很多行訊息產生（特別是在一些比較好的硬體上執行時）。

-s scale_factor

--scale=scale_factor

資料的數量是以 scale factor 的倍數來計算的。舉例來說，-s 100 將會在表格 pgbench_accounts 中產生 10,000,000 筆資料。其預設為 1。當 scale 到達 20,000 以上時，欄位 aid 就會宣告為 bigint，以有足夠的數值空間來處理。

--foreign-keys

在標準的表格結構之間建立外部鍵。

--index-tablespace=index_tablespace

把索引建在指定的表格空間（tablespace），而非預設的表格空間。

--tablespace=tablespace

把表格建在指定的表格空間，而非預設的表格空間。

--unlogged-tables

把所有表格都建立成無日誌表格，而不是永久性表格。

評估階段專用選項

pgbench 在評估階段可使用下列選項：

-b scriptname[@weight]

--builtin=scriptname[@weight]

這個選項用於指定要使用哪一個內建的評估情境。而在 @ 後面可以給一個整數，調整產生腳本的機率參數。如果未指定的話，就會設定為 1。目前內建的情境是：tpcb-like、simple-update、select-only。只要是明確內建名稱的前置縮寫（如：tpc、simple、select）都是可以接受的。而有一個特別的名稱是 list，使用這個名稱的話，就只是列出有哪些內建的情境。

-c clients

--client=clients

模擬用戶的數量，指的是同一時間連入資料庫的連線數。預設為 1。

-C

--connect

在每一個交易執行前都重新建立連線，而不是都在同一個用戶連線中完成全部交易。這在測試連線成本時特別有用。

-d

--debug

輸出程式除錯用的訊息。

-D varname=value

--define=varname=value

定義給自訂腳本使用的變數。你可以使用多個 -D 來定義多個變數。

-f filename[@weight]

--file=filename[@weight]

從 filename 所指的檔案取得腳本，組成一個資料交易區段。選擇性的參數 @，後面接的整數，用來調整使用此腳本的機率。詳情後述。

-j threads

--jobs=threads

pgbench 執行緒的數量，能夠有效利用多 CPU 的運算能力。模擬用戶會盡可能平均分配在不同執行緒中執行。預設值為 1。

-l

--log

把執行的記錄存到檔案之中，後續詳述。

-L limit

--latency-limit=limit

交易執行時間超過 limit 以上時，將會被特別計算回報。其單位是 millisecond（千分之一秒）。

而如果也使用了「--rate=...」限流時，被評估一定會超時的交易，就會被跳過不執行，而它們也會被特別回報。

-M querymode

--protocol=querymode

選擇傳送指令的通訊協定：

simple: 簡單查詢協定。
extended: 延伸查詢協定。
prepared: 延伸查詢協定，並使用預備宣告（prepared statement）方式。

預設是使用簡單查詢協定。（有關查詢協定，請參閱第 52 章）

-n

--no-vacuum

在執行測試評估前不要清理資料庫。如果你使用的是自訂的腳本，而且不包含前述四個內建表格的話，那這個選項是必要的。

-N

--skip-some-updates

使用內建的 simple-update 腳本，和 -b simple-update 是一樣的。

-P sec

--progress=sec

設定每 sec 秒回報一次進度。這個進度回報包含了執行累計時間，目前的 TPS 情況，還有每個進度階段的交易延遲時間平均值與標準差。如果使用 -R 的話，那麼延遲時間是相對於排定的啓動時間，而不是實際開始執行的時間，也就是說，它包含了平均的延遲時間。

-r

--report-latencies

回報每一個指令中每個語的平均回應時間。詳情後述。

-R rate

--rate=rate

執行的方式改為頻率而不是盡可能快速執行（預設）。執行頻率以 TPS 來指定。如果目標執行頻率高於最大可能的執行頻率的話，那就沒有意義。

目標執行頻率是以帕松分配（Poisson-distributed）來安排啓動時間的。預期的啓動時間表會隨用戶第一次開始的時間移動，而不是前一次交易結束的時間。這個方法表示，如果有交易誤點了，它仍有機會隨後趕上。

當限流機制啓動時，最後就會得到交易延遲的報告，其相對的是預排的啓動時間，所以它包含了每個交易必須要等待執行前的時間。等待時間稱作排程延遲時間，而其平均延遲與最大延遲都會被回報。交易延遲是相對於真正的開始執行間時，也就是說，交易在資料庫內被執行的時間，可視為是回報的延遲時間減去排程延遲時間。

如果 --latency-limit 和 --rate 兩個選項一起使用的話，交易可能會落後很多，當前一個交易結束時就已經超時了，因為超時是以排程的開始時間計算的。像這樣的交易就不會被執行了，它會被跳過，然後被統計出來。

如果一個系統有很長的排程延遲時間，那表示這個系統無法負擔超過某個執行頻率，當然需要搭配某個數量的用戶數及執行緒數。當平均的交易執行時間長於兩個交易排定的區間時，每一個接續的交易就會接著失敗，而排程延遲就會更長。當這種情況發生時，你就需要降低執行的頻率。

-s scale_factor

--scale=scale_factor

回報資料庫初始化的 scale factor。對於內建的測試而言，這個選項並不需要；其正確的 scale factor 將會自動以資料表 pgbench_branches 的資料筆數計算而得。而如果測試使用的是自訂的情境腳步的話（選項 -f），那會回報 1。

-S

--select-only

執行內建 select-only 的情境腳步，等同於 -b select-only。

-t transactions

--transactions=transactions

每一個模擬用戶端要執行的交易數量，預設為 10。

-T seconds

--time=seconds

執行限時測試（以秒為單位），而不是固定的交易數量。-t 和 -T 是互斥的選項。

-v

--vacuum-all

在執行測試之前，先整理四個標準的資料表。如果沒有 -n 或 -v 的話，pgbench 會整理 pgbench_tellers 和 pgbench_branches，然後清空 pgbench_history。

--aggregate-interval=seconds

彙整資訊的間隔時間（以秒為單位），通常只和 -l 選項一起使用。這個選項的執行記錄，將會包含每個間隔時間如上所述的彙整資料。

--log-prefix=prefix

設定 --log 所建立檔案的檔名前置名稱。預設是 pgbench_log。

--progress-timestamp

當顯示進度（選項 -P）時，使用時間戳記（Unix epoch）取代相對的執行時間。其單位是秒，精確度至千分之一秒。這個選項用於在多種操作工具間比較時間。

--sampling-rate=rate

取樣率，用於寫入資料到記錄檔時，可以減少記錄的輸出量。如果使用這個選項的話，只有指定比率的記錄會被輸出。如果是 1.0 的話，表示所有記錄都要輸出；而 0.05 的話，表示只輸出 5% 的記錄。

記得取樣率指的是輸出到記錄檔的比率，舉例來說，當計算 TPS 數值時，你會需要多個樣本數來彙整（使用 0.01 的取樣率時，你就只會得到原來百分之一個 TPS 數值輸出）。

通用選項

以下是 pgbench 所支援的通用選項：

-h hostname

--host=hostname

資料庫伺服器的主機名稱。

-p port

--port=port

資料庫伺服器的連接埠號碼。

-U login

--username=login

連線時要使用的使用者名稱。

-V

--version

輸出 pgbench 的版本資訊，然後就結束程式。

-?

--help

顯示 pgbench 的命令列操作資訊，然後結束程式。

進階說明

實際上是什麼樣的交易在 pgbench 中執行呢？

pgbench 會隨機選取在某個列表中的腳本來執行，包含了使用 -b 的內建腳本及 -f 的自訂腳本。每一個腳本都可以使用 @ 來指定其被選取的機率。預設為 1，而設為 0 的話就會被忽略。

預設內建的交易腳本（也就是 -b tpcb-like），使用了七個指令，並且自動隨機代入不同變數：aid、tid、bid、和 balance。這個情境來自於 TPC-B 標準，但不完全符合 TPC-B，所以取名為 tpcb-like。

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

如果你選擇了 simple-update（也是 -N），那麼就不包含步驟 4 和 5。它會避免在這些資料表更新資料的競爭行為，但會接近 TPC-B 一些。

如果你使用了 select-only（也是 -S），就會只有 SELECT 的部份被執行。

自訂腳本

pgbench 支援使用自訂的情境腳步取代內建的測試腳本（如上所述），透過選項 -f 從檔案取得。這種情況的話，一個交易指的就是一個腳本檔案執行一次。

腳本檔案包含一個或多個 SQL 指令，以分號分隔結尾。空白行和以 -- 開頭的行都會被忽略。腳本檔案也可以包含「中繼指令（meta commands）」，用於 pgbench 執行測試時的參考指令，詳述於後。

注意

在 PostgreSQL 9.6 之前，腳本檔案裡的 SQL 指令是以換行結尾的，也就是不能跨行。現在使用分號是必要的了，在分隔連續的 SQL 指令時，你得加上分號（但如果這個 SQL 指令是由中繼指令所執行的話，就不需要分號）。如果你需要建立一份相容性的腳本檔案的話，請確認你的每一條 SQL 指令都是單行，並且以分號結尾。

腳本檔案可以進行簡易的變數代換動作。變數可以由命令列的 -D 來設定，或使用下面所介紹的中繼指令。進一步來說，任何變數都可以使用 -D 選項來預先設定，而在 Table 240 的變數則會自動產生。一旦設定好之後，變數內容就可以使用 :variablename 的形式放入 SQL 指令之中。而每一個模擬用戶的連線中，他們都擁有他們自己的變數內容。

Table 240. Automatic Variables

中繼指令是以倒斜線（\）開頭的指令，一般就到行末結尾，而如果要多行的話，就在行末再加倒斜線。中繼指令的參數是以空白分隔。支援的中繼指令有：

\set varname expression

以 expression 表示式來計算 varname 數變的內容。表示式也可能包含整數常數，像 5432；或雙精確度浮點數 3.14159；或引用其他變數計算而得的表示式，可以使用的函數如後所述。

例如：

\set ntellers 10 * :scale
\set aid (1021 * random(1, 100000 * :scale)) % \
           (100000 * :scale) + 1

\sleep number[ us | ms | s ]

使腳本執行暫停一段指定的時間，百萬分之一秒（us）、千分之一秒（ms）、或秒(s）。如果省略單位的話，預設是秒。nubmer 可以是整數常數，或引用其他整數變數的內容。

例如：

\sleep 10 ms

\setshell varname command[argument... ]

設定 varname 的內容是執行另一個命令列指令的結果。該命令列指令必須透過標準輸出回傳整數。

command 和每一個 argument 都可以是文字常數或使用 :variablename 引用其他變數內容。如果你要使用 argument 的話，以冒號開始，而第一個 argument 要再多一個冒號。

例如：

\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon

\shell command[argument... ]

和 \setshell 一樣，只是不處理回傳值。

例如：

\shell command literal_argument :variable ::literal_starting_with_colon

內建函數

Table 241 是 pgbench 內建，可以在 \set 的函數。

Table 241. pgbench Functions

random 函數使用的是均勻分配亂數，也就是在指定範圍內的數值，都有相等的產生機率。random_exponential 和 random_gaussian 則需要額外的參數，來指定精確的分配情況。

指數分配，參數控制其分配情況是透過分段一個快速下降的指數分配，投影在指定範圍間的整數而得。精確來說，以下面的式子計算而得： f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter)) 區間中某個 i 值的機率為 f(i) - f(i + 1)。直覺上，越大的輸入參數，就會越多較小的數值被輸出，而較少的大數值產生。如果參數接近 0 的話，就會很接近均勻分配。一個粗略的概念是，機率最高的 1%，落於靠近最小值的一端，機率大概是百分之（parameter）。此參數必須要是正整數。
高斯分配，指定區間會映射到一個標準常態分配的空間（典型的錐型高斯曲線），分佈於 -parameter 及 +parameter 之間。靠中間的值有更高的選取機率。精確來說，如果 PHI(x) 是該常態分配的累計分配函數的話，那麼平均數 mu 就是 (max + min) / 2.0，則： f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) / (2.0 * PHI(parameter) - 1) 在區間中，數值 i 被選取的機率就是：f(i + 0.5) - f(i - 0.5)。直覺上，parameter 越大，就會有越多中間值被選值，而越小的話，兩側數側被選擇的機率就會增加。約有 67% 的結果會在靠近 1.0 / parameter 中間的值，相對於 0.5 / parameter 近乎在平均值的附近；2.0 / parameter 則是 95% 是靠近中間的值，相對於 1.0 / parameter 近乎在平均值的附近。舉例來說，如果 parameter = 4.0，大概有 67% 的值會來自於中間的四分之一（即 3.0 / 8.0 到 5.0 / 8.0），而 95% 來自於中間的一半（2.0 / 4.0），第二和第三的四分位數之間。以 Box-Muller 轉換的效率來說，parameter 最小值為 2.0。

下面是內建的 TPC-B like 交易的例子：

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

這個腳本讓每一個交易都引用不同且隨機的資料列。（這個例子也表示出每一個用戶擁有自己的變數的重要性—否則他們不會獨立地操作不同的資料列。）

記錄每筆交易

使用選項 -l（但沒有選項 --aggregate-interval）時，pgbench 將會把每一筆交易都寫入記錄檔。記錄檔的檔名會是 prefix.nnn 的形式，其中的 prefix 預設是 pgbench_log，而 nnn 則是該 pgbench 程序的 PID。prefix 可以由選項 --log-prefix 來指定。如果選項 -j 是 2 以上時，也就是同時有多個執行緒在進行交易，那麼他們會被分別寫入不同的檔案，第一個執行緒會使用前述標準單一執行緒的檔名，而其他的執行緒將會命名為 prefix.nnn.mmm，其中 mmm 則由各執行緒依序編號而得，編號從 1 開始。

記錄檔內容格式如下：

client_id transaction_no time script_no time_epoch time_us [schedule_lag]

其中 client_id 指的是哪一個用戶的連線代號，transaction_no 計算有多交易在該連線中被執行了，time 是整個交易的持續時間，單位是亳秒，script_no 指出是使用哪一個腳本（當透過 -f 或 -b 使用多個腳本時會很有用），而 time_epoch／timeus 是 Unix-epoch 格式的時間戳記，記錄該交易的完成時間，單位是亳秒（適當地建立一個 ISO 8601 時間戳記再加上小數）。schedule_lag 欄位是交易排程時間的差值，以及實際開始的時間，單位是亳秒，只有在 --rate 使用時才會出現。當 --rate 和 --latecy-limit 一起使用時，time 欄位在被跳過的交易上，會註明 skipped。

這裡是一小段記錄檔案，單一執行緒的結果：

0 199 2241 0 1175850568 995598
0 200 2465 0 1175850568 998079
0 201 2513 0 1175850569 608
0 202 2038 0 1175850569 2663

另一個例子，使用 --rate=100 及 --latency-limit=5（注意額外的 schedule_lag 欄位）：

0 81 4621 0 1412881037 912698 3005
0 82 6173 0 1412881037 914578 4304
0 83 skipped 0 1412881037 914578 5217
0 83 skipped 0 1412881037 914578 5099
0 83 4722 0 1412881037 916203 3108
0 84 4142 0 1412881037 918023 2333
0 85 2465 0 1412881037 919759 740

在這個例子中，82 號交易誤點了，因為它延遲了 6.173 ms，超過限時的 5 ms。接下來的兩個交易就被跳過了，因為他們在開始前就已經超時了。

當某個主機執行長時間的交易時，記錄檔案可能會變得非常大。選項 --sampling-rate 就可以派上用場，只存下部份的交易樣本。

彙總記錄

使用 --aggregate-interval 選項時，會是另一種記錄檔格式：

interval_start num_transactions sum_latency sum_latency_2 min_latency max_latency [sum_lag sum_lag_2 min_lag max_lag [ skipped ] ]

其中 interval_start 是區間的起始時間（Unix epoch 時間戳記），num_transactions 是區間裡交易的總數，sum_latency 是區間裡交易的延遲量，sum_latency_2 則是交易延遲的平方和，min_latency 是區間中最短延遲，而 max_latency 則是區間中的最長延遲。接下來的欄位，sum_lag、sum_lag_2、min_lag、max_lag，只有在指定 --rate 選項時才會出現。它們提供了每一個交易等待前一個交易結束時間的統計，也就是每一個交易的排程時間和實際執行時間的差異。而最後一個欄位 skipped，只會出現在也用了 --latency-limit 選項時，它計算交易被跳過的數目，因為他們太晚啓動了。每一個交易在區間中的都會被計算，當該交易被提交時。

這裡是一些輸出範例：

1345828501 5601 1542744 483552416 61 2573
1345828503 7884 1979812 565806736 60 1479
1345828505 7208 1979422 567277552 59 1391
1345828507 7685 1980268 569784714 60 1398
1345828509 7073 1979779 573489941 236 1411

注意，一般的記錄檔（非彙總式）會記錄交易由哪一個腳本產生，但彙總式記錄則不會。所以如果你需要分別不同的腳本彙總，你需要自行處理。

每個指令耗時

使用選項 -r 時，pgbench 就會收集每一個模擬用戶的每一個交易中的每一個指令的耗時，它會以平均值回報，放在最後報告中的每一個指令前。

以預設的腳本，輸出可能會是像這樣：

starting vacuum...end.
transaction type:<builtin: TPC-B (sort of)>

scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
latency average = 15.844 ms
latency stddev = 2.715 ms
tps = 618.764555 (including connections establishing)
tps = 622.977698 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
        0.002  \set aid random(1, 100000 * :scale)
        0.005  \set bid random(1, 1 * :scale)
        0.002  \set tid random(1, 10 * :scale)
        0.001  \set delta random(-5000, 5000)
        0.326  BEGIN;
        0.603  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        0.454  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        5.528  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        7.335  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.371  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        1.212  END;

如果有多個腳本被使用時，結果則會依不同的腳本檔分別回報。

注意收集每一個指令的執行時間也需要計算，會增加些許的負載。這個選項會降低一些平均執行速度和 TPS。具體上會有多少影響則視平台及硬體而定。可以比較切換此選項的 TPS 來瞭解額外負載的情況。

好的作法

使用 pgbench 產生許多無用數字是很簡單的事。這裡提供一些作法，幫助你得到一些有用的結果。

首先，不要相信任何在數秒內就能得到的結果。善用 -t 或 -T 使測試至少能執行好幾分鐘，用平均的方式降低誤差。在某個情境你可能需要幾個小時使得結果數字是可重現的。至少嘗試執行時間數分鐘以上是好主意，可以瞭解你的結果是否具重見性。

對於預設的 TPC-B like 測試情境，scale factor （-s）應該要是一個足夠大的數，超過最大的用戶數（-c），否則你會遭遇更新競爭的情況。因為每個交易都需要更新 pgbench_branches，所以如果 -c 大於 -s 時，將無可避免有些交易會被其他交易暫時阻擋。

預設的測試情境對於資料表被使用多久也很敏感：因為資料表變更會產生廢棄的資料列、資料空間。要瞭解這些情況，你必須追蹤更新資料的總數和整理資料表的時間。如果自動整理的功能開啓了，那麼就會在測試時產生無可預知的變化。

pgbench 的其中一項限制就是它自己也可能是瓶頸，在產生大量模擬用戶時。可以採用在多台主機使用多個 pgbench 來解決這個問題，雖然這樣也會帶來一些網路延遲。不過這樣就可以同時執行許多的 pgbench，在多個主機上，對同一個資料庫進行測試。

pg_basebackup

pg_basebackup — 對 PostgreSQL 叢集進行基礎備份

語法

pg_basebackup [option...]

說明

pg_basebackup 用於對正在執行的 PostgreSQL 資料庫叢集進行基礎備份。採取這些措施不會影響資料庫的其他用戶端，並且可以用於時間點隨選還原（請參閱），也可以用於日誌傳送或串流複寫備用伺服器的起點（請參閱）。

pg_basebackup 製作資料庫叢集檔案的二進位副本，同時確保系統自動進入和退出備份模式。只能對整個資料庫叢集進行備份；無法備份單個資料庫或資料庫物件。對於單一資料庫的備份，必須使用如之類的工具。

此備份是透過一般 PostgreSQL 連線所進行的，並使用複寫協定。必須由超級使用者或具有 REPLICATION 權限的使用者建立連線（請參閱），並且 pg_hba.conf 必須明確允許複寫連線。必須讓伺服器設定的設定得夠多，以使至少一個連線可用於備份，而至少一個連線可用於 WAL 串流傳輸（如果有使用的話）。

可以同時執行多個 pg_basebackup，但是從效能的角度來看，最好只執行一個備份並且複製其結果。

pg_basebackup 不僅可以從主要資料庫備份，也可以從備用資料庫進行基礎備份。要從備用資料庫中取得備份，請設定該備用資料庫，使其可以接受複寫連線（即設定 max_wal_senders 和，並配置基於主機的身份驗證）。您還需要在主要伺服器上啟用。

請注意，從備用資料庫的備份會有一些限制：

The backup history file is not created in the database cluster backed up.
If you are using -X none, there is no guarantee that all WAL files required for the backup are archived at the end of backup.
If the standby is promoted to the master during online backup, the backup fails.
All WAL records required for the backup must contain sufficient full-page writes, which requires you to enable full_page_writes on the master and not to use a tool like pg_compresslog as archive_command to remove full-page writes from WAL files.

Options

The following command-line options control the location and format of the output.

-D directory --pgdata=directory

Directory to write the output to. pg_basebackup will create the directory and any parent directories if necessary. The directory may already exist, but it is an error if the directory already exists and is not empty.

When the backup is in tar mode, and the directory is specified as - (dash), the tar file will be written to stdout.

This option is required.

-F format --format=format

Selects the format for the output. format can be one of the following:

p plain

Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no additional tablespaces, the whole database will be placed in the target directory. If the cluster contains additional tablespaces, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

This is the default format.

t tar

Write the output as tar files in the target directory. The main data directory will be written to a file named base.tar, and all other tablespaces will be named after the tablespace OID.

If the value - (dash) is specified as target directory, the tar contents will be written to standard output, suitable for piping to for example gzip. This is only possible if the cluster has no additional tablespaces and WAL streaming is not used.

-r rate --max-rate=rate

The maximum transfer rate of data transferred from the server. Values are in kilobytes per second. Use a suffix of M to indicate megabytes per second. A suffix of k is also accepted, and has no effect. Valid values are between 32 kilobytes per second and 1024 megabytes per second.

The purpose is to limit the impact of pg_basebackup on the running server.

This option always affects transfer of the data directory. Transfer of WAL files is only affected if the collection method is fetch.

-R --write-recovery-conf

Create standby.signal and append connection settings to postgresql.auto.conf in the output directory (or into the base archive file when using tar format) to ease setting up a standby server. The postgresql.auto.conf file will record the connection settings and, if specified, the replication slot that pg_basebackup is using, so that the streaming replication will use the same settings later on.

-T olddir=newdir --tablespace-mapping=olddir=newdir

Relocate the tablespace in directory olddir to newdir during the backup. To be effective, olddir must exactly match the path specification of the tablespace as it is currently defined. (But it is not an error if there is no tablespace in olddir contained in the backup.) Both olddir and newdir must be absolute paths. If a path happens to contain a = sign, escape it with a backslash. This option can be specified multiple times for multiple tablespaces. See examples below.

If a tablespace is relocated in this way, the symbolic links inside the main data directory are updated to point to the new location. So the new data directory is ready to be used for a new server instance with all tablespaces in the updated locations.

--waldir=waldir

Specifies the location for the write-ahead log directory. waldir must be an absolute path. The write-ahead log directory can only be specified when the backup is in plain mode.

-X method --wal-method=method

Includes the required write-ahead log files (WAL files) in the backup. This will include all write-ahead logs generated during the backup. Unless the method none is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup.

The following methods for collecting the write-ahead logs are supported:

n none

Don't include write-ahead log in the backup.

f fetch

When tar format mode is used, the write-ahead log files will be written to the base.tar file.

s stream

When tar format mode is used, the write-ahead log files will be written to a separate file named pg_wal.tar (if the server is a version earlier than 10, the file will be named pg_xlog.tar).

This value is the default.

-z --gzip

Enables gzip compression of tar file output, with the default compression level. Compression is only available when using the tar format, and the suffix .gz will automatically be added to all tar filenames.

-Z level --compress=level

Enables gzip compression of tar file output, and specifies the compression level (0 through 9, 0 being no compression and 9 being best compression). Compression is only available when using the tar format, and the suffix .gz will automatically be added to all tar filenames.

The following command-line options control the generation of the backup and the running of the program.

-c fast|spread --checkpoint=fast|spread

-C --create-slot

This option causes creation of a replication slot named by the --slot option before starting the backup. An error is raised if the slot already exists.

-l label --label=label

Sets the label for the backup. If none is specified, a default value of “pg_basebackup base backup” will be used.

-n --no-clean

By default, when pg_basebackup aborts with an error, it removes any directories it might have created before discovering that it cannot finish the job (for example, data directory and write-ahead log directory). This option inhibits tidying-up and is thus useful for debugging.

Note that tablespace directories are not cleaned up either way.

-N --no-sync

By default, pg_basebackup will wait for all files to be written safely to disk. This option causes pg_basebackup to return without waiting, which is faster, but means that a subsequent operating system crash can leave the base backup corrupt. Generally, this option is useful for testing but should not be used when creating a production installation.

-P --progress

Enables progress reporting. Turning this on will deliver an approximate progress report during the backup. Since the database may change during the backup, this is only an approximation and may not end at exactly 100%. In particular, when WAL log is included in the backup, the total amount of data cannot be estimated in advance, and in this case the estimated target size will increase once it passes the total estimate without WAL.

When this is enabled, the backup will start by enumerating the size of the entire database, and then go back and send the actual contents. This may make the backup take slightly longer, and in particular it will take longer before the first data is sent.

-S slotname --slot=slotname

The specified replication slot has to exist unless the option -C is also used.

If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.

-v --verbose

Enables verbose mode. Will output some extra steps during startup and shutdown, as well as show the exact file name that is currently being processed if progress reporting is also enabled.

--no-slot

This option prevents the creation of a temporary replication slot during the backup even if it's supported by the server.

Temporary replication slots are created by default if no slot name is given with the option -S when using log streaming.

The main purpose of this option is to allow taking a base backup when the server is out of free replication slots. Using replication slots is almost always preferred, because it prevents needed WAL from being removed by the server during the backup.

--no-verify-checksums

Disables verification of checksums, if they are enabled on the server the base backup is taken from.

The following command-line options control the database connection parameters.

-d connstr --dbname=connstr

The option is called --dbname for consistency with other client applications, but because pg_basebackup doesn't connect to any particular database in the cluster, database name in the connection string will be ignored.

-h host --host=host

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-s interval --status-interval=interval

Specifies the number of seconds between status packets sent back to the server. This allows for easier monitoring of the progress from server. A value of zero disables the periodic status updates completely, although an update will still be sent when requested by the server, to avoid timeout disconnect. The default value is 10 seconds.

-U username --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_basebackup to prompt for a password before connecting to a database.

This option is never essential, since pg_basebackup will automatically prompt for a password if the server demands password authentication. However, pg_basebackup will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Other options are also available:

-V --version

Print the pg_basebackup version and exit.

-? --help

Show help about pg_basebackup command line arguments, and exit.

環境變數

環境變數 PG_COLOR 指定是否在診斷資訊中使用彩色。可能的值為 always，auto 和 never。

注意

在備份開始時，需要在備份目標的伺服器上寫入一個檢查點。特別是如果不使用 --checkpoint = fast 選項的話，這可能會花費一些時間，在此期間 pg_basebackup 會顯示為 idle。

Tablespaces will in plain format by default be backed up to the same path they have on the server, unless the option --tablespace-mapping is used. Without this option, running a plain format base backup on the same host as the server will not work if tablespaces are in use, because the backup would have to be written to the same directory locations as the original tablespaces.

When tar format mode is used, it is the user's responsibility to unpack each tar file before starting the PostgreSQL server. If there are additional tablespaces, the tar files for them need to be unpacked in the correct locations. In this case the symbolic links for those tablespaces will be created by the server according to the contents of the tablespace_map file that is included in the base.tar file.

pg_basebackup works with servers of the same or an older major version, down to 9.1. However, WAL streaming mode (-X stream) only works with server version 9.3 and later, and tar format mode (--format=tar) of the current version only works with server version 9.5 or later.

pg_basebackup will preserve group permissions in both the plain and tar formats if group permissions are enabled on the source cluster.

範例

要在 mydbserver 上建立伺服器的基礎備份並將其儲存在本機路徑 /usr/local/pgsql/data 下：

要為每個資料表空間使用一個壓縮的 tar 檔案建立本機伺服器的備份，並將其儲存在目錄備份中，且在執行時顯示進度報告：

要建立單個資料表空間本機資料庫的備份並使用 bzip2 來壓縮它：

（如果資料庫中有多個資料表空間，則此命令將會失敗。）

要建立本機資料庫的備份，其中 /opt/ts 中的資料表空間要重新定位到 ./backup/ts：

參閱

pg_dump

pg_dump — 將 PostgreSQL 資料庫匯出到腳本檔案或其他封存檔案中

語法

pg_dump [connection-option...] [option...] [dbname]

說明

pg_dump 是用於備份 PostgreSQL 資料庫的工具程式。即使同時也在使用資料庫，它會進行具有一致性的備份。pg_dump 不會阻礙其他使用者存取資料庫（讀取或寫入皆不會阻礙）。

pg_dump 只匯出一個數據庫。要備份整個叢集，或備份叢集中所有資料庫共有的全域物件（例如角色和資料表空間），請使用 pg_dumpall。

Dump 可以是腳本或封存檔案格式輸出。腳本匯出是純文字檔案，其中包含將資料庫重建到保存時所處狀態所需的 SQL 命令。要從此類腳本還原，請將其腳本檔案提供給 psql。腳本檔案甚至可以在其他機器和其他系統結構上用於重建資料庫；進行一些修改，甚至在其他 SQL 資料庫產品上還原。

另一種封存檔案格式必須與 pg_restore 一起使用才能重建資料庫。它們允許 pg_restore 對還原哪些東西有選擇性，甚至可以在還原之前對資料進行重新排序。封存檔案格式設計為可跨系統結構移植。

When used with one of the archive file formats and combined with pg_restore, pg_dump provides a flexible archival and transfer mechanism. pg_dump can be used to backup an entire database, then pg_restore can be used to examine the archive and/or select which parts of the database are to be restored. The most flexible output file formats are the “custom” format (-Fc) and the “directory”format (-Fd). They allow for selection and reordering of all archived items, support parallel restoration, and are compressed by default. The “directory” format is the only format that supports parallel dumps.

While running pg_dump, one should examine the output for any warnings (printed on standard error), especially in light of the limitations listed below.

選項

以下命令列選項來控制輸出的內容和格式。

dbname

指定要匯出的資料庫的名稱。如果未指定，則使用環境變數 PGDATABASE。如果環境變數也未設定，則使用連線的使用者名稱作為資料庫名稱。

-a --data-only

僅匯出資料，而不匯出結構（資料結構定義）。資料表的資料內容、大型物件和序列值將被匯出。

此選項與 --section = data 相似，但由於歷史原因並不完全相同。

-b --blobs

Include large objects in the dump. This is the default behavior except when --schema, --table, or --schema-only is specified. The -b switch is therefore only useful to add large objects to dumps where a specific schema or table has been requested. Note that blobs are considered data and therefore will be included when --data-only is used, but not when --schema-only is.

-B --no-blobs

Exclude large objects in the dump.

When both -b and -B are given, the behavior is to output large objects, when data is being dumped, see the -b documentation.

-c --clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

-C --create

Begin the output with a command to create the database itself and reconnect to the created database. (With a script of this form, it doesn't matter which database in the destination installation you connect to before running the script.) If --clean is also specified, the script drops and recreates the target database before reconnecting to it.

With --create, the output also includes the database's comment if any, and any configuration variable settings that are specific to this database, that is, any ALTER DATABASE ... SET ... and ALTER ROLE ... IN DATABASE ... SET ... commands that mention this database. Access privileges for the database itself are also dumped, unless --no-acl is specified.

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

-E encoding --encoding=encoding

Create the dump in the specified character set encoding. By default, the dump is created in the database encoding. (Another way to get the same result is to set the PGCLIENTENCODINGenvironment variable to the desired dump encoding.)

-f file --file=file

Send output to the specified file. This parameter can be omitted for file based output formats, in which case the standard output is used. It must be given for the directory output format however, where it specifies the target directory instead of a file. In this case the directory is created by pg_dump and must not exist before.

-F format --format=format

Selects the format of the output. format can be one of the following:

p plain

Output a plain-text SQL script file (the default).

c custom

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

d directory

Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default and also supports parallel dumps.

t tar

Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore.

-j njobs --jobs=njobs

同時執行匯出 njobs 個資料表。此選項可以節省匯出的時間，但同時也增加了資料庫伺服器上的負載。您只能將此選項與 directory 輸出格式一起使用，因為這是多個程序可以同時寫入資料的唯一輸出方式。

pg_dump 將打開 njobs + 1 個到資料庫的連線，因此請確保您的 max_connections 設定足夠多以容納所有連線。

在執行平行匯出時，對資料庫物件請求 exclusive locks (獨佔鎖定)可能會導致匯出失敗。原因是 pg_dump 主要程序會對工作程序稍後將要匯出的物件請求 shared locks (共享鎖定)，以確保沒有人會移除它們而在執行匯出時使它們消失。如果另一個用戶端隨後請求對資料進行獨佔鎖定，則不會授予該鎖定，它會排隊等待主要程序的共享鎖定被釋放。因此，對該資料表的任何其他存取也不會被允許，並且將排在排他鎖定請求之後。這當然也包括了嘗試匯出資料表的工作程序。如果沒有任何預防措施，這將是典型的 deadlock 情況。為了發現到這種衝突，pg_dump worker 程序使用 NOWAIT 選項請求另一個共享鎖定。如果未向工作程序授予該共享鎖定，那麼其他人在此期間必須已請求獨占鎖定，否則無法繼續進行匯出工作，因此 pg_dump 別無選擇，只能中止匯出。

為了實現備份的一致性，資料庫伺服器需要支援同步快照，這是 PostgreSQL 9.2 中針對主要伺服器引入的功能，針對備用伺服器引入了此功能是在版本 10 的時候。使用此功能，即使資料庫用戶端使用不同的連線，也可以確保他們看到相同的資料集。 pg_dump -j 使用多個資料庫連接；它透過主要程序一次連線到資料庫，並針對每個工作程序再次連線到資料庫。如果沒有同步快照功能，將無法保證不同的工作程序在每個連線中都看到相同的資料，這就可能導致備份的不一致。

如果要在 9.2 之前版本伺服器的平行匯出，則需要確保從主伺服器連線到資料庫到最後一個工作程序作業連線到資料庫之間的時間裡，資料庫內容沒有變化。最簡單的方法是在開始備份之前，停止所有資料庫的資料修改程序（包含 DDL 和 DML）。在9.2版之前的 PostgreSQL 服務器上執行 pg_dump -j 時，還需要指定 --no-synchronized-snapshots 參數。

-n schema --schema=schema

Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped. Multiple schemas can be selected by writing multiple -n switches. Also, the schema parameter is interpreted as a pattern according to the same rules used by psql's \d commands (see Patterns), so multiple schemas can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent the shell from expanding the wildcards; see Examples.

Note

When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database.

Note

Non-schema objects such as blobs are not dumped when -n is specified. You can add blobs back to the dump with the --blobs switch.

-N schema --exclude-schema=schema

Do not dump any schemas matching the schema pattern. The pattern is interpreted according to the same rules as for -n. -N can be given more than once to exclude schemas matching any of several patterns.

When both -n and -N are given, the behavior is to dump just the schemas that match at least one -n switch but no -N switches. If -N appears without -n, then schemas matching -N are excluded from what is otherwise a normal dump.

-o --oids

Dump object identifiers (OIDs) as part of the data for every table. Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used.

-O --no-owner

Do not output commands to set ownership of objects to match the original database. By default, pg_dump issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created database objects. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script). To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O.

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

-R --no-reconnect

This option is obsolete but still accepted for backwards compatibility.

-s --schema-only

Dump only the object definitions (schema), not data.

This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.

(Do not confuse this with the --schema option, which uses the word “schema” in a different meaning.)

To exclude table data for only a subset of tables in the database, see --exclude-table-data.

-S username --superuser=username

Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used. (Usually, it's better to leave this out, and instead start the resulting script as superuser.)

-t table --table=table

僅匯出名稱與此選項相符的資料表。為此，“資料表”還包括檢視表、具體化檢視表、序列和外部資料表。透過寫入多個 -t 選項就可以選擇多個資料表。另外，根據 psql 的 \d 命令使用的相同規則，將 table 參數解釋為 pattern（請參閱 Patterns），因此也可以透過在 pattern 中寫入萬用字元來選擇多個資料表。使用萬用字元時，如果需要，請小心引用該樣式，以防止 Shell 擴展萬用字元。請參閱範例。

The -n and -N switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches, and non-table objects will not be dumped.

當指定 -t 時，pg_dump 不會嘗試匯出所選資料表可能相依的任何其他資料庫物件。因此，不能保證自己可以成功地將特定資料表匯出的結果還原到乾淨的資料庫中。

Note

The behavior of the -t switch is not entirely upward compatible with pre-8.2PostgreSQL versions. Formerly, writing -t tab would dump all tables named tab, but now it just dumps whichever one is visible in your default search path. To get the old behavior you can write -t '*.tab'. Also, you must write something like -t sch.tab to select a table in a particular schema, rather than the old locution of -n sch -t tab.-T table --exclude-table=table

Do not dump any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. -T can be given more than once to exclude tables matching any of several patterns.

When both -t and -T are given, the behavior is to dump just the tables that match at least one -t switch but no -T switches. If -T appears without -t, then tables matching -T are excluded from what is otherwise a normal dump.

-v --verbose

Specifies verbose mode. This will cause pg_dump to output detailed object comments and start/stop times to the dump file, and progress messages to standard error.

-V --version

Print the pg_dump version and exit.

-x --no-privileges --no-acl

Prevent dumping of access privileges (grant/revoke commands).

-Z 0..9 --compress=0..9

Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all.

--binary-upgrade

This option is for use by in-place upgrade utilities. Its use for other purposes is not recommended or supported. The behavior of the option may change in future releases without notice.

--column-inserts --attribute-inserts

Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents.

--disable-dollar-quoting

This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax.

--disable-triggers

This option is relevant only when creating a data-only dump. It instructs pg_dump to include commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Presently, the commands emitted for --disable-triggers must be done as superuser. So, you should also specify a superuser name with -S, or preferably be careful to start the resulting script as a superuser.

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

--enable-row-security

This option is relevant only when dumping the contents of a table which has row security. By default, pg_dump will set row_security to off, to ensure that all data is dumped from the table. If the user does not have sufficient privileges to bypass row security, then an error is thrown. This parameter instructs pg_dump to set row_security to on instead, allowing the user to dump the parts of the contents of the table that they have access to.

Note that if you use this option currently, you probably also want the dump be in INSERT format, as the COPY FROM during restore does not support row security.

--exclude-table-data=table

Do not dump data for any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. --exclude-table-data can be given more than once to exclude tables matching any of several patterns. This option is useful when you need the definition of a particular table even though you do not need the data in it.

To exclude data for all tables in the database, see --schema-only.

--if-exists

Use conditional commands (i.e. add an IF EXISTS clause) when cleaning database objects. This option is not valid unless --clean is also specified.

--inserts

Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

--load-via-partition-root

When dumping data for a table partition, make the COPY or INSERT statements target the root of the partitioning hierarchy that contains it, rather than the partition itself. This causes the appropriate partition to be re-determined for each row when the data is loaded. This may be useful when reloading data on a server where rows do not always fall into the same partitions as they did on the original server. That could happen, for example, if the partitioning column is of type text and the two systems have different definitions of the collation used to sort the partitioning column.

It is best not to use parallelism when restoring from an archive made with this option, because pg_restore will not know exactly which partition(s) a given archive data item will load data into. This could result in inefficiency due to lock conflicts between parallel jobs, or perhaps even reload failures due to foreign key constraints being set up before all the relevant data is loaded.

--lock-wait-timeout=timeout

Do not wait forever to acquire shared table locks at the beginning of the dump. Instead fail if unable to lock a table within the specified timeout. The timeout may be specified in any of the formats accepted by SET statement_timeout. (Allowed formats vary depending on the server version you are dumping from, but an integer number of milliseconds is accepted by all versions.)

--no-comments

Do not dump comments.

--no-publications

Do not dump publications.

--no-security-labels

Do not dump security labels.

--no-subscriptions

Do not dump subscriptions.

--no-sync

By default, pg_dump will wait for all files to be written safely to disk. This option causes pg_dump to return without waiting, which is faster, but means that a subsequent operating system crash can leave the dump corrupt. Generally, this option is useful for testing but should not be used when dumping data from production installation.

--no-synchronized-snapshots

This option allows running pg_dump -j against a pre-9.2 server, see the documentation of the -j parameter for more details.

--no-tablespaces

Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore.

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

--no-unlogged-table-data

Do not dump the contents of unlogged tables. This option has no effect on whether or not the table definitions (schema) are dumped; it only suppresses dumping the table data. Data in unlogged tables is always excluded when dumping from a standby server.

--quote-all-identifiers

Force quoting of all identifiers. This option is recommended when dumping a database from a server whose PostgreSQL major version is different from pg_dump's, or when the output is intended to be loaded into a server of a different major version. By default, pg_dump quotes only identifiers that are reserved words in its own major version. This sometimes results in compatibility issues when dealing with servers of other versions that may have slightly different sets of reserved words. Using --quote-all-identifiers prevents such issues, at the price of a harder-to-read dump script.

--section=sectionname

Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

--serializable-deferrable

Use a serializable transaction for the dump, to ensure that the snapshot used is consistent with later database states; but do this by waiting for a point in the transaction stream at which no anomalies can be present, so that there isn't a risk of the dump failing or causing other transactions to roll back with a serialization_failure. See Chapter 13 for more information about transaction isolation and concurrency control.

This option is not beneficial for a dump which is intended only for disaster recovery. It could be useful for a dump used to load a copy of the database for reporting or other read-only load sharing while the original database continues to be updated. Without it the dump may reflect a state which is not consistent with any serial execution of the transactions eventually committed. For example, if batch processing techniques are used, a batch may show as closed in the dump without all of the items which are in the batch appearing.

This option will make no difference if there are no read-write transactions active when pg_dump is started. If read-write transactions are active, the start of the dump may be delayed for an indeterminate length of time. Once running, performance with or without the switch is the same.

--snapshot=snapshotname

Use the specified synchronized snapshot when making a dump of the database (see Table 9.82 for more details).

This option is useful when needing to synchronize the dump with a logical replication slot (see Chapter 49) or with a concurrent session.

In the case of a parallel dump, the snapshot name defined by this option is used rather than taking a new snapshot.

--strict-names

Require that each schema (-n/--schema) and table (-t/--table) qualifier match at least one schema/table in the database to be dumped. Note that if none of the schema/table qualifiers find matches, pg_dump will generate an error even without --strict-names.

This option has no effect on -N/--exclude-schema, -T/--exclude-table, or --exclude-table-data. An exclude pattern failing to match any objects is not considered an error.

--use-set-session-authorization

Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership. This makes the dump more standards-compatible, but depending on the history of the objects in the dump, might not restore properly. Also, a dump using SET SESSION AUTHORIZATION will certainly require superuser privileges to restore correctly, whereas ALTER OWNER requires lesser privileges.

-? --help

Show help about pg_dump command line arguments, and exit.

The following command-line options control the database connection parameters.

-d dbname --dbname=dbname

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string. See Section 34.1 for more information.-

h host --host=host

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOSTenvironment variable, if set, else a Unix domain socket connection is attempted.

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-U username --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_dump to prompt for a password before connecting to a database.

This option is never essential, since pg_dump will automatically prompt for a password if the server demands password authentication. However, pg_dump will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

--role=rolename

Specifies a role name to be used to create the dump. This option causes pg_dump to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_dump, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows dumps to be made without violating the policy.

Environment

PGDATABASE PGHOST PGOPTIONS PGPORT PGUSER

Default connection parameters.

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.14).

Diagnostics

pg_dump internally executes SELECT statements. If you have problems running pg_dump, make sure you are able to select information from the database using, for example, psql. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

The database activity of pg_dump is normally collected by the statistics collector. If this is undesirable, you can set parameter track_counts to false via PGOPTIONS or the ALTER USER command.

Notes

If your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

CREATE DATABASE foo WITH TEMPLATE template0;

When a data-only dump is chosen and the option --disable-triggers is used, pg_dump emits commands to disable triggers on user tables before inserting the data, and then commands to re-enable them after the data has been inserted. If the restore is stopped in the middle, the system catalogs might be left in the wrong state.

The dump file produced by pg_dump does not contain the statistics used by the optimizer to make query planning decisions. Therefore, it is wise to run ANALYZE after restoring from a dump file to ensure optimal performance; see Section 24.1.3 and Section 24.1.6 for more information.

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dumpcan also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 8.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. Use of the --quote-all-identifiers option is recommended in cross-version cases, as it can prevent problems arising from varying reserved-word lists in different PostgreSQL versions.

When dumping logical replication subscriptions, pg_dump will generate CREATE SUBSCRIPTION commands that use the NOCONNECT option, so that restoring the subscription does not make remote connections for creating a replication slot or for initial table copy. That way, the dump can be restored without requiring network access to the remote servers. It is then up to the user to reactivate the subscriptions in a suitable way. If the involved hosts have changed, the connection information might have to be changed. It might also be appropriate to truncate the target tables before initiating a new full table copy.

範例

要將名稱為 mydb 的資料庫匯出到 SQL 腳本檔案中：

$ pg_dump mydb > db.sql

要將這樣的腳本重新載入到名稱為 newdb 的（新建立的）資料庫中：

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To dump a database into a directory-format archive:

$ pg_dump -Fd mydb -f dumpdir

To dump a database into a directory-format archive in parallel with 5 worker jobs:

$ pg_dump -Fd mydb -j 5 -f dumpdir

To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump

To reload an archive file into the same database it was dumped from, discarding the current contents of that database:

$ pg_restore -d postgres --clean --create db.dump

只要單獨匯出名稱為 mytab 的資料表：

$ pg_dump -t mytab mydb > db.sql

要在 detroit 綱要中匯出所有名稱以 emp 開頭的資料表，但名稱為 employee_log 的資料表除外：

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

To dump all database objects except for tables whose names begin with ts_:

$ pg_dump -T 'ts_*' mydb > db.sql

To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

參閱

pg_dumpall, pg_restore, psql

pg_dumpall

pg_dumpall — extract a PostgreSQL database cluster into a script file

Synopsis

pg_dumpall [connection-option...] [option...]

Description

pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to to restore the databases. It does this by calling for each database in the cluster. pg_dumpall also dumps global objects that are common to all databases, that is, database roles and tablespaces. (pg_dump does not save these objects.)

Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump. Also you will need superuser privileges to execute the saved script in order to be allowed to add roles and create databases.

The SQL script will be written to the standard output. Use the -f/--file option or shell operators to redirect it into a file.

pg_dumpall needs to connect several times to the PostgreSQL server (once per database). If you use password authentication it will ask for a password each time. It is convenient to have a ~/.pgpass file in such cases. See for more information.

Options

The following command-line options control the content and format of the output.

-a --data-only

Dump only the data, not the schema (data definitions).

-c --clean

Include SQL commands to clean (drop) databases before recreating them. DROP commands for roles and tablespaces are added as well.

-E encoding --encoding=encoding

Create the dump in the specified character set encoding. By default, the dump is created in the database encoding. (Another way to get the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding.)

-f filename --file=filename

Send output to the specified file. If this is omitted, the standard output is used.

-g --globals-only

Dump only global objects (roles and tablespaces), no databases.

-O --no-owner

Do not output commands to set ownership of objects to match the original database. By default, pg_dumpall issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script). To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O.

-r --roles-only

Dump only roles, no databases or tablespaces.

-s --schema-only

Dump only the object definitions (schema), not data.

-S username --superuser=username

-t --tablespaces-only

Dump only tablespaces, no databases or roles.

-v --verbose

Specifies verbose mode. This will cause pg_dumpall to output start/stop times to the dump file, and progress messages to standard error. It will also enable verbose output in pg_dump.

-V --version

Print the pg_dumpall version and exit.

-x --no-privileges --no-acl

Prevent dumping of access privileges (grant/revoke commands).

--binary-upgrade

This option is for use by in-place upgrade utilities. Its use for other purposes is not recommended or supported. The behavior of the option may change in future releases without notice.

--column-inserts --attribute-inserts

--disable-dollar-quoting

This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax.

--disable-triggers

This option is relevant only when creating a data-only dump. It instructs pg_dumpall to include commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

--extra-float-digits=ndigits

Use the specified value of extra_float_digits when dumping floating-point data, instead of the maximum available precision. Routine dumps made for backup purposes should not use this option.

--exclude-database=pattern

--if-exists

Use conditional commands (i.e. add an IF EXISTS clause) to drop databases and other objects. This option is not valid unless --clean is also specified.

--inserts

Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safer, though even slower.

--load-via-partition-root

--lock-wait-timeout=timeout

Do not wait forever to acquire shared table locks at the beginning of the dump. Instead, fail if unable to lock a table within the specified timeout. The timeout may be specified in any of the formats accepted by SET statement_timeout. Allowed values vary depending on the server version you are dumping from, but an integer number of milliseconds is accepted by all versions since 7.3. This option is ignored when dumping from a pre-7.3 server.

--no-comments

Do not dump comments.

--no-publications

Do not dump publications.

--no-role-passwords

Do not dump passwords for roles. When restored, roles will have a null password, and password authentication will always fail until the password is set. Since password values aren't needed when this option is specified, the role information is read from the catalog view pg_roles instead of pg_authid. Therefore, this option also helps if access to pg_authid is restricted by some security policy.

--no-security-labels

Do not dump security labels.

--no-subscriptions

Do not dump subscriptions.

--no-sync

By default, pg_dumpall will wait for all files to be written safely to disk. This option causes pg_dumpall to return without waiting, which is faster, but means that a subsequent operating system crash can leave the dump corrupt. Generally, this option is useful for testing but should not be used when dumping data from production installation.

--no-tablespaces

Do not output commands to create tablespaces nor select tablespaces for objects. With this option, all objects will be created in whichever tablespace is the default during restore.

--no-unlogged-table-data

Do not dump the contents of unlogged tables. This option has no effect on whether or not the table definitions (schema) are dumped; it only suppresses dumping the table data.

--on-conflict-do-nothing

Add ON CONFLICT DO NOTHING to INSERT commands. This option is not valid unless --inserts or --column-inserts is also specified.

--quote-all-identifiers

Force quoting of all identifiers. This option is recommended when dumping a database from a server whose PostgreSQL major version is different from pg_dumpall's, or when the output is intended to be loaded into a server of a different major version. By default, pg_dumpall quotes only identifiers that are reserved words in its own major version. This sometimes results in compatibility issues when dealing with servers of other versions that may have slightly different sets of reserved words. Using --quote-all-identifiers prevents such issues, at the price of a harder-to-read dump script.

--rows-per-insert=nrows

Dump data as INSERT commands (rather than COPY). Controls the maximum number of rows per INSERT command. The value specified must be a number greater than zero. Any error during reloading will cause only rows that are part of the problematic INSERT to be lost, rather than the entire table contents.

--use-set-session-authorization

Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership. This makes the dump more standards compatible, but depending on the history of the objects in the dump, might not restore properly.

-? --help

Show help about pg_dumpall command line arguments, and exit.

The following command-line options control the database connection parameters.

-d connstr --dbname=connstr

The option is called --dbname for consistency with other client applications, but because pg_dumpall needs to connect to many databases, the database name in the connection string will be ignored. Use the -l option to specify the name of the database used for the initial connection, which will dump global objects and discover what other databases should be dumped.

-h host --host=host

Specifies the host name of the machine on which the database server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

-l dbname --database=dbname

Specifies the name of the database to connect to for dumping global objects and discovering what other databases should be dumped. If not specified, the postgres database will be used, and if that does not exist, template1 will be used.

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-U username --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_dumpall to prompt for a password before connecting to a database.

This option is never essential, since pg_dumpall will automatically prompt for a password if the server demands password authentication. However, pg_dumpall will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Note that the password prompt will occur again for each database to be dumped. Usually, it's better to set up a ~/.pgpass file than to rely on manual password entry.

--role=rolename

Specifies a role name to be used to create the dump. This option causes pg_dumpall to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_dumpall, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows dumps to be made without violating the policy.

Environment

PGHOST PGOPTIONS PGPORT PGUSER

Default connection parametersPG_COLOR

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Notes

Since pg_dumpall calls pg_dump internally, some diagnostic messages will refer to pg_dump.

The --clean option can be useful even when your intention is to restore the dump script into a fresh cluster. Use of --clean authorizes the script to drop and re-create the built-in postgres and template1 databases, ensuring that those databases will retain the same properties (for instance, locale and encoding) that they had in the source cluster. Without the option, those databases will retain their existing database-level properties, as well as any pre-existing contents.

Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You can also run vacuumdb -a -z to analyze all databases.

The dump script should not be expected to run completely without errors. In particular, because the script will issue CREATE ROLE for every role existing in the source cluster, it is certain to get a “role already exists” error for the bootstrap superuser, unless the destination cluster was initialized with a different bootstrap superuser name. This error is harmless and should be ignored. Use of the --clean option is likely to produce additional harmless error messages about non-existent objects, although you can minimize those by adding --if-exists.

pg_dumpall requires all needed tablespace directories to exist before the restore; otherwise, database creation will fail for databases in non-default locations.

Examples

To dump all databases:

To reload database(s) from this file, you can use:

It is not important to which database you connect here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved databases. An exception is that if you specified --clean, you must connect to the postgres database initially; the script will attempt to drop other databases immediately, and that will fail for the database you are connected to.

參閱

pg_isready

pg_isready — check the connection status of a PostgreSQL server

Synopsis

pg_isready [connection-option...] [option...]

Description

pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check.

Options

-d dbname --dbname=dbname

Specifies the name of the database to connect to. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

-h hostname --host=hostname

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

-p port --port=port

Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

-q --quiet

Do not display status message. This is useful when scripting.

-t seconds --timeout=seconds

The maximum number of seconds to wait when attempting connection before returning that the server is not responding. Setting to 0 disables. The default is 3 seconds.

-U username --username=username

Connect to the database as the user username instead of the default.

-V --version

Print the pg_isready version and exit.

-? --help

Show help about pg_isready command line arguments, and exit.

Exit Status

pg_isready returns 0 to the shell if the server is accepting connections normally, 1 if the server is rejecting connections (for example during startup), 2 if there was no response to the connection attempt, and 3 if no attempt was made (for example due to invalid parameters).

Environment

pg_isready, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Notes

It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.

Examples

Standard Usage:

$ pg_isready
/tmp:5432 - accepting connections
$ echo $?
0

Running with connection parameters to a PostgreSQL cluster in startup:

$ pg_isready -h localhost -p 5433
localhost:5433 - rejecting connections
$ echo $?
1

Running with connection parameters to a non-responsive PostgreSQL cluster:

$ pg_isready -h someremotehost
someremotehost:5432 - no response
$ echo $?
2

pg_receivewal

pg_receivewal — stream write-ahead logs from a PostgreSQL server

Synopsis

pg_receivewal [option...]

Description

pg_receivewal is used to stream the write-ahead log from a running PostgreSQL cluster. The write-ahead log is streamed using the streaming replication protocol, and is written to a local directory of files. This directory can be used as the archive location for doing a restore using point-in-time recovery (see Section 25.3).

pg_receivewal streams the write-ahead log in real time as it's being generated on the server, and does not wait for segments to complete like archive_command does. For this reason, it is not necessary to set archive_timeout when using pg_receivewal.

Unlike the WAL receiver of a PostgreSQL standby server, pg_receivewal by default flushes WAL data only when a WAL file is closed. The option --synchronous must be specified to flush WAL data in real time. Since pg_receivewal does not apply WAL, you should not allow it to become a synchronous standby when synchronous_commit equals remote_apply. If it does, it will appear to be a standby that never catches up, and will cause transaction commits to block. To avoid this, you should either configure an appropriate value for synchronous_standby_names, or specify application_name for pg_receivewal that does not match it, or change the value of synchronous_commit to something other than remote_apply.

The write-ahead log is streamed over a regular PostgreSQL connection and uses the replication protocol. The connection must be made with a user having REPLICATION permissions (see Section 21.2) or a superuser, and pg_hba.conf must permit the replication connection. The server must also be configured with max_wal_senders set high enough to leave at least one session available for the stream.

If the connection is lost, or if it cannot be initially established, with a non-fatal error, pg_receivewal will retry the connection indefinitely, and reestablish streaming as soon as possible. To avoid this behavior, use the -n parameter.

In the absence of fatal errors, pg_receivewal will run until terminated by the SIGINT signal (Control+C).

Options

-D directory --directory=directory

Directory to write the output to.

This parameter is required.

-E lsn --endpos=lsn

Automatically stop replication and exit with normal exit status 0 when receiving reaches the specified LSN.

If there is a record with LSN exactly equal to lsn, the record will be processed.

--if-not-exists

Do not error out when --create-slot is specified and a slot with the specified name already exists.

-n --no-loop

Don't loop on connection errors. Instead, exit right away with an error.

--no-sync

This option causes pg_receivewal to not force WAL data to be flushed to disk. This is faster, but means that a subsequent operating system crash can leave the WAL segments corrupt. Generally, this option is useful for testing but should not be used when doing WAL archiving on a production deployment.

This option is incompatible with --synchronous.

-s interval --status-interval=interval

-S slotname --slot=slotname

Require pg_receivewal to use an existing replication slot (see Section 26.2.6). When this option is used, pg_receivewal will report a flush position to the server, indicating when each segment has been synchronized to disk so that the server can remove that segment if it is not otherwise needed.

When the replication client of pg_receivewal is configured on the server as a synchronous standby, then using a replication slot will report the flush position to the server, but only when a WAL file is closed. Therefore, that configuration will cause transactions on the primary to wait for a long time and effectively not work satisfactorily. The option --synchronous (see below) must be specified in addition to make this work correctly.

--synchronous

Flush the WAL data to disk immediately after it has been received. Also send a status packet back to the server immediately after flushing, regardless of

--status-interval.

This option should be specified if the replication client of pg_receivewal is configured on the server as a synchronous standby, to ensure that timely feedback is sent to the server.

-v --verbose

Enables verbose mode.

-Z level --compress=level

Enables gzip compression of write-ahead logs, and specifies the compression level (0 through 9, 0 being no compression and 9 being best compression). The suffix .gz will automatically be added to all filenames.

The following command-line options control the database connection parameters.

-d connstr --dbname=connstr

Specifies parameters used to connect to the server, as a connction string; these will override any conflicting command line options.

The option is called --dbname for consistency with other client applications, but because pg_receivewal doesn't connect to any particular database in the cluster, database name in the connection string will be ignored.

-h host --host=host

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-U username --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_receivewal to prompt for a password before connecting to a database.

This option is never essential, since pg_receivewal will automatically prompt for a password if the server demands password authentication. However, pg_receivewal will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

pg_receivewal can perform one of the two following actions in order to control physical replication slots:

--create-slot

Create a new physical replication slot with the name specified in --slot, then exit.

--drop-slot

Drop the replication slot with the name specified in --slot, then exit.

Other options are also available:

-V --version

Print the pg_receivewal version and exit.

-? --help

Show help about pg_receivewal command line arguments, and exit.

Exit Status

pg_receivewal will exit with status 0 when terminated by the SIGINT signal. (That is the normal way to end it. Hence it is not an error.) For fatal errors or other signals, the exit status will be nonzero.

Environment

This utility, like most other PostgreSQL utilities, uses the environment variables supported by libpq (see Section 33.14).

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Notes

When using pg_receivewal instead of archive_command as the main WAL backup method, it is strongly recommended to use replication slots. Otherwise, the server is free to recycle or remove write-ahead log files before they are backed up, because it does not have any information, either from archive_command or the replication slots, about how far the WAL stream has been archived. Note, however, that a replication slot will fill up the server's disk space if the receiver does not keep up with fetching the WAL data.

pg_receivewal will preserve group permissions on the received WAL files if group permissions are enabled on the source cluster.

Examples

To stream the write-ahead log from the server at mydbserver and store it in the local directory /usr/local/pgsql/archive:

$ pg_receivewal -h mydbserver -D /usr/local/pgsql/archive

pg_recvlogical

pg_recvlogical — control PostgreSQL logical decoding streams

Synopsis

pg_recvlogical [option...]

Description

pg_recvlogical controls logical decoding replication slots and streams data from such replication slots.

It creates a replication-mode connection, so it is subject to the same constraints as pg_receivewal, plus those for logical replication (see Chapter 48).

pg_recvlogical has no equivalent to the logical decoding SQL interface's peek and get modes. It sends replay confirmations for data lazily as it receives it and on clean exit. To examine pending data on a slot without consuming it, use pg_logical_slot_peek_changes.

Options

At least one of the following options must be specified to select an action:

--create-slot

Create a new logical replication slot with the name specified by --slot, using the output plugin specified by --plugin, for the database specified by --dbname.

--drop-slot

Drop the replication slot with the name specified by --slot, then exit.

--start

Begin streaming changes from the logical replication slot specified by --slot, continuing until terminated by a signal. If the server side change stream ends with a server shutdown or disconnect, retry in a loop unless --no-loop is specified.

The stream format is determined by the output plugin specified when the slot was created.

The connection must be to the same database used to create the slot.

--create-slot and --start can be specified together. --drop-slot cannot be combined with another action.

The following command-line options control the location and format of the output and other replication behavior:

-E lsn --endpos=lsn

In --start mode, automatically stop replication and exit with normal exit status 0 when receiving reaches the specified LSN. If specified when not in --start mode, an error is raised.

If there's a record with LSN exactly equal to lsn, the record will be output.

The --endpos option is not aware of transaction boundaries and may truncate output partway through a transaction. Any partially output transaction will not be consumed and will be replayed again when the slot is next read from. Individual messages are never truncated.

-f filename --file=filename

Write received and decoded transaction data into this file. Use - for stdout.

-F interval_seconds --fsync-interval=interval_seconds

Specifies how often pg_recvlogical should issue fsync() calls to ensure the output file is safely flushed to disk.

The server will occasionally request the client to perform a flush and report the flush position to the server. This setting is in addition to that, to perform flushes more frequently.

Specifying an interval of 0 disables issuing fsync() calls altogether, while still reporting progress to the server. In this case, data could be lost in the event of a crash.

-I lsn --startpos=lsn

In --start mode, start replication from the given LSN. For details on the effect of this, see the documentation in Chapter 48 and Section 52.4. Ignored in other modes.

--if-not-exists

Do not error out when --create-slot is specified and a slot with the specified name already exists.

-n --no-loop

When the connection to the server is lost, do not retry in a loop, just exit.

-o name[=value] --option=name[=value]

Pass the option name to the output plugin with, if specified, the option value value. Which options exist and their effects depends on the used output plugin.

-P plugin --plugin=plugin

When creating a slot, use the specified logical decoding output plugin. See Chapter 48. This option has no effect if the slot already exists.

-s interval_seconds --status-interval=interval_seconds

This option has the same effect as the option of the same name in pg_receivewal. See the description there.

-S slot_name --slot=slot_name

In --start mode, use the existing logical replication slot named slot_name. In --create-slot mode, create the slot with this name. In --drop-slot mode, delete the slot with this name.

-v --verbose

Enables verbose mode.

The following command-line options control the database connection parameters.

-d dbname --dbname=dbname

The database to connect to. See the description of the actions for what this means in detail. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options. Defaults to the user name.

-h hostname-or-ip --host=hostname-or-ip

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-U user --username=user

User name to connect as. Defaults to current operating system user name.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_recvlogical to prompt for a password before connecting to a database.

This option is never essential, since pg_recvlogical will automatically prompt for a password if the server demands password authentication. However, pg_recvlogical will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

The following additional options are available:

-V --version

Print the pg_recvlogical version and exit.

-? --help

Show help about pg_recvlogical command line arguments, and exit.

Environment

This utility, like most other PostgreSQL utilities, uses the environment variables supported by libpq (see Section 33.14).

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Notes

pg_recvlogical will preserve group permissions on the received WAL files if group permissions are enabled on the source cluster.

Examples

See Section 48.1 for an example.

pg_restore

pg_restore — restore a PostgreSQL database from an archive file created by pg_dump

Synopsis

pg_restore [connection-option...] [option...] [filename]

Description

pg_restore is a utility for restoring a PostgreSQL database from an archive created by in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved. The archive files also allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive files are designed to be portable across architectures.

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump. Some of the options controlling the output are therefore analogous to pg_dump options.

Obviously, pg_restore cannot restore information that is not present in the archive file. For instance, if the archive was made using the “dump data as INSERT commands” option, pg_restore will not be able to load the data using COPY statements.

Options

pg_restore accepts the following command line arguments.filename

Specifies the location of the archive file (or directory, for a directory-format archive) to be restored. If not specified, the standard input is used.

-a --data-only

Restore only the data, not the schema (data definitions). Table data, large objects, and sequence values are restored, if present in the archive.

This option is similar to, but for historical reasons not identical to, specifying --section=data.

-c --clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

-C --create

Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

With --create, pg_restore also restores the database's comment if any, and any configuration variable settings that are specific to this database, that is, any ALTER DATABASE ... SET ... and ALTER ROLE ... IN DATABASE ... SET ... commands that mention this database. Access privileges for the database itself are also restored, unless --no-acl is specified.

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

-d dbname --dbname=dbname

Connect to database dbname and restore directly into the database.

-e --exit-on-error

Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration.

-f filename --file=filename

Specify output file for generated script, or for the listing when used with -l. Use - for stdout.

-F format --format=format

Specify format of the archive. It is not necessary to specify the format, since pg_restore will determine the format automatically. If specified, it can be one of the following:

c custom

The archive is in the custom format of pg_dump.

d directory

The archive is a directory archive.

t tar

The archive is a tar archive.

-I index --index=index

Restore definition of named index only. Multiple indexes may be specified with multiple -I switches.

-j number-of-jobs --jobs=number-of-jobs

Run the most time-consuming steps of pg_restore — those that load data, create indexes, or create constraints — concurrently, using up to number-of-jobs concurrent sessions. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine. This option is ignored when emitting a script rather than connecting directly to a database server.

Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.

The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.

Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe or standard input). Also, multiple jobs cannot be used together with the option --single-transaction.

-l --list

List the table of contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed.

-L list-file --use-list=list-file

Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

-n schema --schema=schema

Restore only objects that are in the named schema. Multiple schemas may be specified with multiple -n switches. This can be combined with the -t option to restore just a specific table.

-N schema --exclude-schema=schema

Do not restore objects that are in the named schema. Multiple schemas to be excluded may be specified with multiple -N switches.

When both -n and -N are given for the same schema name, the -N switch wins and the schema is excluded.

-O --no-owner

Do not output commands to set ownership of objects to match the original database. By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script). With -O, any user name can be used for the initial connection, and this user will own all the created objects.

-P function-name(argtype [, ...]) --function=function-name(argtype [, ...])

Restore the named function only. Be careful to spell the function name and arguments exactly as they appear in the dump file's table of contents. Multiple functions may be specified with multiple -P switches.

-R --no-reconnect

This option is obsolete but still accepted for backwards compatibility.

-s --schema-only

Restore only the schema (data definitions), not data, to the extent that schema entries are present in the archive.

This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.

(Do not confuse this with the --schema option, which uses the word “schema” in a different meaning.)

-S username --superuser=username

Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used.

-t table --table=table

Restore definition and/or data of only the named table. For this purpose, “table” includes views, materialized views, sequences, and foreign tables. Multiple tables can be selected by writing multiple -t switches. This option can be combined with the -n option to specify table(s) in a particular schema.

Note

When -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that a specific-table restore into a clean database will succeed.

Note

This flag does not behave identically to the -t flag of pg_dump. There is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t. And, while pg_dump's -t flag will also dump subsidiary objects (such as indexes) of the selected table(s), pg_restore's -t flag does not include such subsidiary objects.

Note

In versions prior to PostgreSQL 9.6, this flag matched only tables, not any other type of relation.

-T trigger --trigger=trigger

Restore named trigger only. Multiple triggers may be specified with multiple -T switches.

-v --verbose

Specifies verbose mode.

-V --version

Print the pg_restore version and exit.

-x --no-privileges --no-acl

Prevent restoration of access privileges (grant/revoke commands).

-1 --single-transaction

Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

--disable-triggers

This option is relevant only when performing a data-only restore. It instructs pg_restore to execute commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Presently, the commands emitted for --disable-triggers must be done as superuser. So you should also specify a superuser name with -S or, preferably, run pg_restore as a PostgreSQL superuser.

--enable-row-security

Note that this option currently also requires the dump be in INSERT format, as COPY FROM does not support row security.

--if-exists

Use conditional commands (i.e. add an IF EXISTS clause) to drop database objects. This option is not valid unless --clean is also specified.

--no-comments

Do not output commands to restore comments, even if the archive contains them.

--no-data-for-failed-tables

By default, table data is restored even if the creation command for the table failed (e.g., because it already exists). With this option, data for such a table is skipped. This behavior is useful if the target database already contains the desired table contents. For example, auxiliary tables for PostgreSQL extensions such as PostGIS might already be loaded in the target database; specifying this option prevents duplicate or obsolete data from being loaded into them.

This option is effective only when restoring directly into a database, not when producing SQL script output.

--no-publications

Do not output commands to restore publications, even if the archive contains them.

--no-security-labels

Do not output commands to restore security labels, even if the archive contains them.

--no-subscriptions

Do not output commands to restore subscriptions, even if the archive contains them.

--no-tablespaces

Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore.

--section=sectionname

Only restore the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to restore all sections.

The data section contains actual table data as well as large-object definitions. Post-data items consist of definitions of indexes, triggers, rules and constraints other than validated check constraints. Pre-data items consist of all other data definition items.

--strict-names

Require that each schema (-n/--schema) and table (-t/--table) qualifier match at least one schema/table in the backup file.

--use-set-session-authorization

-? --help

Show help about pg_restore command line arguments, and exit.

pg_restore also accepts the following command line arguments for connection parameters:

-h host --host=host

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

-U username --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_restore to prompt for a password before connecting to a database.

This option is never essential, since pg_restore will automatically prompt for a password if the server demands password authentication. However, pg_restore will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

--role=rolename

Specifies a role name to be used to perform the restore. This option causes pg_restore to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_restore, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows restores to be performed without violating the policy.

Environment

PGHOST PGOPTIONS PGPORT PGUSER

Default connection parameters

PG_COLOR

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Diagnostics

Notes

If your installation has any local additions to the template1 database, be careful to load the output of pg_restore into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

The limitations of pg_restore are detailed below.

When restoring data to a pre-existing table and the option --disable-triggers is used, pg_restore emits commands to disable triggers on user tables before inserting the data, then emits commands to re-enable them after the data has been inserted. If the restore is stopped in the middle, the system catalogs might be left in the wrong state.
pg_restore cannot restore large objects selectively; for instance, only those for a specific table. If an archive contains large objects, then all large objects will be restored, or none of them if they are excluded via -L, -t, or other options.

Examples

Assume we have dumped a database called mydb into a custom-format dump file:

To drop the database and recreate it from the dump:

The database named in the -d switch can be any database existing in the cluster; pg_restore only uses it to issue the CREATE DATABASE command for mydb. With -C, data is always restored into the database name that appears in the dump file.

To reload the dump into a new database called newdb:

Notice we don't use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not template1, to ensure it is initially empty.

To reorder database items, it is first necessary to dump the table of contents of the archive:

The listing file consists of a header and one line for each item, e.g.:

Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item.

Lines in the file can be commented out, deleted, and reordered. For example:

could be used as input to pg_restore and would only restore items 10 and 6, in that order:

參閱

pg_verifybackup

pg_verifybackup — verify the integrity of a base backup of a PostgreSQL cluster

Synopsis

pg_verifybackup [option...]

Description

pg_verifybackup is used to check the integrity of a database cluster backup taken using pg_basebackup against a backup_manifest generated by the server at the time of the backup. The backup must be stored in the "plain" format; a "tar" format backup can be checked after extracting it.

It is important to note that the validation which is performed by pg_verifybackup does not and can not include every check which will be performed by a running server when attempting to make use of the backup. Even if you use this tool, you should still perform test restores and verify that the resulting databases work as expected and that they appear to contain the correct data. However, pg_verifybackup can detect many problems that commonly occur due to storage problems or user error.

Backup verification proceeds in four stages. First, pg_verifybackup reads the backup_manifest file. If that file does not exist, cannot be read, is malformed, or fails verification against its own internal checksum, pg_verifybackup will terminate with a fatal error.

Second, pg_verifybackup will attempt to verify that the data files currently stored on disk are exactly the same as the data files which the server intended to send, with some exceptions that are described below. Extra and missing files will be detected, with a few exceptions. This step will ignore the presence or absence of, or any modifications to, postgresql.auto.conf, standby.signal, and recovery.signal, because it is expected that these files may have been created or modified as part of the process of taking the backup. It also won't complain about a backup_manifest file in the target directory or about anything inside pg_wal, even though these files won't be listed in the backup manifest. Only files are checked; the presence or absence of directories is not verified, except indirectly: if a directory is missing, any files it should have contained will necessarily also be missing.

Next, pg_verifybackup will checksum all the files, compare the checksums against the values in the manifest, and emit errors for any files for which the computed checksum does not match the checksum stored in the manifest. This step is not performed for any files which produced errors in the previous step, since they are already known to have problems. Files which were ignored in the previous step are also ignored in this step.

Finally, pg_verifybackup will use the manifest to verify that the write-ahead log records which will be needed to recover the backup are present and that they can be read and parsed. The backup_manifest contains information about which write-ahead log records will be needed, and pg_verifybackup will use that information to invoke pg_waldump to parse those write-ahead log records. The --quiet flag will be used, so that pg_waldump will only report errors, without producing any other output. While this level of verification is sufficient to detect obvious problems such as a missing file or one whose internal checksums do not match, they aren't extensive enough to detect every possible problem that might occur when attempting to recover. For instance, a server bug that produces write-ahead log records that have the correct checksums but specify nonsensical actions can't be detected by this method.

Note that if extra WAL files which are not required to recover the backup are present, they will not be checked by this tool, although a separate invocation of pg_waldump could be used for that purpose. Also note that WAL verification is version-specific: you must use the version of pg_verifybackup, and thus of pg_waldump, which pertains to the backup being checked. In contrast, the data file integrity checks should work with any version of the server that generates a backup_manifest file.

Options

pg_verifybackup accepts the following command-line arguments:

-e --exit-on-error

Exit as soon as a problem with the backup is detected. If this option is not specified, pg_verifybackup will continue checking the backup even after a problem has been detected, and will report all problems detected as errors.

-i path --ignore=path

Ignore the specified file or directory, which should be expressed as a relative path name, when comparing the list of data files actually present in the backup to those listed in the backup_manifest file. If a directory is specified, this option affects the entire subtree rooted at that location. Complaints about extra files, missing files, file size differences, or checksum mismatches will be suppressed if the relative path name matches the specified path name. This option can be specified multiple times.

-m path --manifest-path=path

Use the manifest file at the specified path, rather than one located in the root of the backup directory.

-n --no-parse-wal

Don't attempt to parse write-ahead log data that will be needed to recover from this backup.

-q --quiet

Don't print anything when a backup is successfully verified.

-s --skip-checksums

Do not verify data file checksums. The presence or absence of files and the sizes of those files will still be checked. This is much faster, because the files themselves do not need to be read.

-w path --wal-directory=path

Try to parse WAL files stored in the specified directory, rather than in pg_wal. This may be useful if the backup is stored in a separate location from the WAL archive.

Other options are also available:

-V --version

Print the pg_verifybackup version and exit.

-? --help

Show help about pg_verifybackup command line arguments, and exit.

Examples

To create a base backup of the server at mydbserver and verify the integrity of the backup:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
$ pg_verifybackup /usr/local/pgsql/data

To create a base backup of the server at mydbserver, move the manifest somewhere outside the backup directory, and verify the backup:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/backup1234
$ mv /usr/local/pgsql/backup1234/backup_manifest /my/secure/location/backup_manifest.1234
$ pg_verifybackup -m /my/secure/location/backup_manifest.1234 /usr/local/pgsql/backup1234

To verify a backup while ignoring a file that was added manually to the backup directory, and also skipping checksum verification:

$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
$ edit /usr/local/pgsql/data/note.to.self
$ pg_verifybackup --ignore=note.to.self --skip-checksums /usr/local/pgsql/data

vacuumdb

vacuumdb — 資源回收並重新分析 PostgreSQL 資料庫

語法

vacuumdb [connection-option...] [option...] [ -t | --table table [( column [,...] )] ] ... [dbname]

vacuumdb [connection-option...] [option...] -a | --all

說明

vacuumdb 是一個用於清理 PostgreSQL 資料庫的工具程式。vacuumdb 也會產生 PostgreSQL 查詢最佳化程式所使用的內部統計資訊。

vacuumdb 只是一個將 SQL 指令封裝起來的工具。透過此工具與透過其他方法存取伺服器之間，對資料庫進行清理和分析的工作並沒有任何區別。

選項參數

vacuumdb 接受以下的命令列參數：

-a --all

清理所有資料庫。

[-d] dbname [--dbname=]dbname

指定要清理或分析的資料庫名稱。如果未指定，也未使用 -a（或--all），則從環境變數 PGDATABASE 中取得資料庫名稱。如果都未設定，則使用此連線所使用的使用者名稱。

--disable-page-skipping

Disable skipping pages based on the contents of the visibility map.

Note

This option is only available for servers running PostgreSQL 9.6 and later.

-e --echo

顯示 vacuumdb 產生並發送到伺服器的指令。

-f --full

執行「完全」清理。

-F --freeze

積極地「凍結」資料 tuple。

--force-index-cleanup

Always remove index entries pointing to dead tuples.

Note

This option is only available for servers running PostgreSQL 12 and later.

-j njobs --jobs=njobs

透過同時執行 njobs 指令平行執行 vacuum 或 analyze 指令。此選項可以縮短處理時間，但也會增加資料庫伺服器的負載。

vacuumdb 將打開與資料庫的 njobs 連線，因此請確保您的 max_connections 設定夠高以容納所有連線。

請注意，如果平行處理某些系統目錄，則此選項與 -f（FULL）選項一起使用可能會導致鎖死而失敗。

--min-mxid-age mxid_age

For the purposes of this option, the multixact ID age of a relation is the greatest of the ages of the main relation and its associated TOAST table, if one exists. Since the commands issued by vacuumdb will also process the TOAST table for the relation if necessary, it does not need to be considered separately.

Note

This option is only available for servers running PostgreSQL 9.6 and later.

--min-xid-age xid_age

For the purposes of this option, the transaction ID age of a relation is the greatest of the ages of the main relation and its associated TOAST table, if one exists. Since the commands issued by vacuumdb will also process the TOAST table for the relation if necessary, it does not need to be considered separately.

Note

This option is only available for servers running PostgreSQL 9.6 and later.

--no-index-cleanup

Do not remove index entries pointing to dead tuples.

Note

This option is only available for servers running PostgreSQL 12 and later.

--no-process-toast

Skip the TOAST table associated with the table to vacuum, if any.

Note

This option is only available for servers running PostgreSQL 14 and later.

--no-truncate

Do not truncate empty pages at the end of the table.

Note

This option is only available for servers running PostgreSQL 12 and later.

-P parallel_workers --parallel=parallel_workers

Note

This option is only available for servers running PostgreSQL 13 and later.

-q --quiet

不顯示進度訊息。

--skip-locked

Skip relations that cannot be immediately locked for processing.

Note

This option is only available for servers running PostgreSQL 12 and later.

-t table [ (column [,...]) ] --table=table [ (column [,...]) ]

僅清理或分析資料表。欄位名稱只能與 --analyze 或 --analyze-only 選項一起指定。以多個選項開關可以對多個資料表進行清理。

小技巧

如果指定欄位，則可能必須從 shell 中跳脫括號。（請參閱下面的例子。）

-v --verbose

處理期間輸出詳細訊息。

V --version

輸出 vacuumdb 版本後結束。

-z --analyze

同時計算最佳化程序所使用的統計資訊。

-Z --analyze-only

僅計算最佳化程序所使用的統計資訊（不做清理）。

--analyze-in-stages

僅計算最佳化程序所使用的統計資訊（不做清理），如同 --analyze-only。使用不同的設定執行幾個（目前是三個）分析階段，以更快地產可用的統計資訊。

此選項對於分析從還原備份或 pg_upgrade 新加入的資料庫非常有用。此選項將嘗試盡可能更快地建立一些統計資訊，使資料庫可用，然後在後續階段產生更完整的統計資訊。

-? --help

顯示有關 vacuumdb 命令列參數的說明，然後結束。

vacuumdb 也在命令列中接受以下連線參數：

-h host --host=host

指定執行伺服器的主機名稱。如果以斜線開頭，則將其用作 Unix domain socket 的目錄。

-p port --port=port

指定伺服器正在監聽連線的 TCP 連接埠或本地 Unix domain socket 檔案的延伸名稱。

-U username --username=username

要連線的使用者名稱。

-w --no-password

不要發出密碼提示。如果伺服器需要密碼身份驗證，而其他方式（例如 .pgpass 檔案）無法使用密碼，則連線嘗試將會失敗。此選項在沒有使用者輸入密碼的批次處理作業腳本中非常有用。

-W --password

強制 vacuumdb 在連線到資料庫之前提示輸入密碼。

此選項並不是必要的，因為如果伺服器需要密碼驗證，vacuumdb 將自動提示輸入密碼。只是，vacuumdb 會浪費連線嘗試，才能發現伺服器需要密碼。在某些情況下，值得輸入 -W 以避免額外的連線嘗試。

--maintenance-db=dbname

指定要連線的資料庫名稱，以發現應該清理哪些其他資料庫。如果未指定，將使用 postgres 資料庫，如果 postgres 不存在的話，將使用 template1。

執行環境

PGDATABASE PGHOST PGPORT PGUSER

預設連線參數

問題分析

注意

`範例`

要清理資料庫 test：

為最佳化程序清理並分析名為 bigdb 的資料庫：

要清理 xyzzy 資料庫中的資料表 foo，並為最佳化程序分析資料表的單個欄位：

參閱

psql

psql — PostgreSQL 互動式終端機

語法

psql [option...] [dbname [username]]

說明

psql 是一個 PostgreSQL 終端機介面的用戶端工具程式。它讓你能夠以互動的方式輸入查詢，將其發送到 PostgreSQL，並顯示查詢結果。輸入來源可以是檔案，也可以是命令列參數。此外，psql 提供了許多自訂命令與各種類似於 shell 的功能，方便撰寫腳本和自動化各種任務的執行。

Options

-a --echo-all

Print all nonempty input lines to standard output as they are read. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all.

-A --no-align

Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned.

-b --echo-errors

Print failed SQL commands to standard error output. This is equivalent to setting the variable ECHO to errors.

-c command --command=command

Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example:

psql -c '\x' -c 'SELECT * FROM foo;'

echo '\x \\ SELECT * FROM foo;' | psql

(\\ is the separator meta-command.)

Each SQL command string passed to -c is sent to the server as a single request. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 55.2.2.1 for more details about how the server handles multi-query strings.)

If having several commands executed in one transaction is not desired, use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:

psql <<EOF
\x
SELECT * FROM foo;
EOF

--csv

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.

-d dbname --dbname=dbname

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string. See Section 33.1.1 for more information.

-e --echo-queries

Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.

-E --echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

-f filename --file=filename

Read commands from the file filename, rather than standard input. This option can be repeated and combined in any order with the -c option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. Except for that, this option is largely equivalent to the meta-command \i.

If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note however that Readline is not used in this case (much as if -n had been specified).

Using this option is subtly different from writing psql < filename. In general, both will do what you expect, but using -f enables some nice features such as error messages with line numbers. There is also a slight chance that using this option will reduce the start-up overhead. On the other hand, the variant using the shell's input redirection is (in theory) guaranteed to yield exactly the same output you would have received had you entered everything by hand.

-F separator --field-separator=separator

Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.

-h hostname --host=hostname

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

-H --html

切換到 HTML 輸出模式。這等同於 \pset format html或 \H 指令。

-l --list

List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list.

When this option is used, psql will connect to the database postgres, unless a different database is named on the command line (option -d or non-option argument, possibly via a service entry, but not via an environment variable).

-L filename --log-file=filename

除了正常的輸出目標之外，還將所有查詢輸出寫入檔名。

-n --no-readline

Do not use Readline for line editing and do not use the command history. This can be useful to turn off tab expansion when cutting and pasting.

-o filename --output=filename

Put all query output into file filename. This is equivalent to the command \o.

-p port --port=port

-P assignment --pset=assignment

Specifies printing options, in the style of \pset. Note that here you have to separate name and value with an equal sign instead of a space. For example, to set the output format to LaTeX, you could write -P format=latex.

-q --quiet

指定 psql 應靜默地執行其工作。默認情況下，它列印歡迎消息和各種信息輸出。如果使用此選項，則不會發生任何情況。這對於 -c 選項很有用。這等效於將變數 QUIET 設置為 on。

-R separator --record-separator=separator

Use separator as the record separator for unaligned output. This is equivalent to \pset recordsep.

-s --single-step

Run in single-step mode. That means the user is prompted before each command is sent to the server, with the option to cancel execution as well. Use this to debug scripts.

-S --single-line

在單行模式下運行，其中換行符終止 SQL 命令，就像分號一樣。

堅持使用此模式的人可以使用，但不一定鼓勵您使用它。特別是，如果您在一行指令上混合使用 SQL 和快捷指令的話，則對於經驗不足的使用者，執行的次序可能會搞不清楚。

-t --tuples-only

Turn off printing of column names and result row count footers, etc. This is equivalent to or \pset tuples_only.

-T table_options --table-attr=table_options

Specifies options to be placed within the HTML table tag. See \pset tableattr for details.

-U username --username=username

Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)

-v assignment --set=assignment --variable=assignment

Perform a variable assignment, like the \set meta-command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during command line processing, so variables that reflect connection state will get overwritten later.

-V --version

Print the psql version and exit.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

-W --password

Force psql to prompt for a password before connecting to a database.

This option is never essential, since psql will automatically prompt for a password if the server demands password authentication. However, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

`x` `--expanded`

使用資料表的延伸的格式。這等效於 \x 或 \pset expenaded。

-X, --no-psqlrc

Do not read the start-up file (neither the system-wide psqlrc file nor the user's ~/.psqlrc file).

-z --field-separator-zero

Set the field separator for unaligned output to a zero byte. This is equivalent to \pset fieldsep_zero.

-0 --record-separator-zero

Set the record separator for unaligned output to a zero byte. This is useful for interfacing, for example, with xargs -0. This is equivalent to \pset recordsep_zero.

-1 --single-transaction

This option can only be used in combination with one or more -c and/or -f options. It causes psql to issue a BEGIN command before the first such option and a COMMIT command after the last one, thereby wrapping all the commands into a single transaction. This ensures that either all the commands complete successfully, or no changes are applied.

If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if an individual command cannot be executed inside a transaction block, specifying this option will cause the whole transaction to fail.

-? --help[=topic]

Show help about psql and exit. The optional topic parameter (defaulting to options) selects which part of psql is explained: commands describes psql's backslash commands; options describes the command-line options that can be passed to psql; and variables shows help about psql configuration variables.

Exit Status

如果 psql 正常完成，則向shell傳回 0，如果發生自己的致命錯誤（例如，記憶體不足、找不到檔），則返回 1，如果與伺服器的連接斷開且會話不交互，則返回 2，如果腳本中發生錯誤並且設置了變數 ON_ERROR_STOP，則返回 3。

Usage

Connecting to a Database

psql is a regular PostgreSQL client application. In order to connect to a database you need to know the name of your target database, the host name and port number of the server, and what user name you want to connect as. psql can be told about those parameters via command line options, namely -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option it will be interpreted as the database name (or the user name, if the database name is already given). Not all of these options are required; there are useful defaults. If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets. The default port number is determined at compile time. Since the database server uses the same default, you will not have to specify the port in most cases. The default user name is your operating-system user name, as is the default database name. Note that you cannot just connect to any database under any user name. Your database administrator should have informed you about your access rights.

When the defaults aren't quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or PGUSER to appropriate values. (For additional environment variables, see Section 34.15.) It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 34.16 for more information.

An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:

$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require

This way you can also use LDAP for connection parameter lookup as described in Section 33.17. See Section 33.1.2 for more information on all the available connection options.

If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.

If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.

Entering SQL Commands

In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string =>. For example:

$ psql testdb
psql (12.2)
Type "help" for help.

testdb=>

At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.

If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin your session by removing publicly-writable schemas from search_path. One can add options=-csearch_path= to the connection string or issue SELECT pg_catalog.set_config('search_path', '', false) before other SQL commands. This consideration is not specific to psql; it applies to every interface for executing arbitrary SQL commands.

Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.

While C-style block comments are passed to the server for processing and removal, SQL-standard comments are removed by psql.

Meta-Commands (快捷指令)

Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands.

The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.

To include whitespace in an argument you can quote it with single quotes. To include a single quote in an argument, write two single quotes within single-quoted text. Anything contained in single quotes is furthermore subject to C-like substitutions for (new line), (tab), \b (backspace), (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal). A backslash preceding any other character within single-quoted text quotes that single character, whatever it is.

If an unquoted colon (:) followed by a psql variable name appears within an argument, it is replaced by the variable's value, as described in SQL Interpolation. The forms :'variable_name' and :"variable_name" described there work as well. The :{?variable_name} syntax allows testing whether a variable is defined. It is substituted by TRUE or FALSE. Escaping the colon with a backslash protects it from substitution.

Within an argument, text that is enclosed in backquotes (`) is taken as a command line that is passed to the shell. The output of the command (with any trailing newline removed) replaces the backquoted text. Within the text enclosed in backquotes, no special quoting or other processing occurs, except that appearances of :variable_name where variable_name is a psql variable name are replaced by the variable's value. Also, appearances of :'variable_name' are replaced by the variable's value suitably quoted to become a single shell command argument. (The latter form is almost always preferable, unless you are very sure of what is in the variable.) Because carriage return and line feed characters cannot be safely quoted on all platforms, the :'variable_name' form prints an error message and does not substitute the variable value when such characters appear in the value.

Some commands take an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Many of the meta-commands act on the current query buffer. This is simply a buffer holding whatever SQL command text has been typed but not yet sent to the server for execution. This will include previous input lines as well as any text appearing before the meta-command on the same line.

The following meta-commands are defined:

`\a`

If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]

Establishes a new connection to a PostgreSQL server. The connection parameters to use can be specified either using a positional syntax, or using conninfo connection strings as detailed in Section 33.1.1.

Where the command omits database name, user, host, or port, the new connection can reuse values from the previous connection. By default, values from the previous connection are reused except when processing a conninfo string. Passing a first argument of -reuse-previous=on or -reuse-previous=off overrides that default. When the command neither specifies nor reuses a particular parameter, the libpq default is used. Specifying any of dbname, username, host or port as - is equivalent to omitting that parameter. If hostaddr was specified in the original connection's conninfo, that address is reused for the new connection (disregarding any other host specification).

If the new connection is successfully made, the previous connection is closed. If the connection attempt failed (wrong user name, access denied, etc.), the previous connection will only be kept if psql is in interactive mode. When executing a non-interactive script, processing will immediately stop with an error. This distinction was chosen as a user convenience against typos on the one hand, and a safety mechanism that scripts are not accidentally acting on the wrong database on the other hand.

Examples:

=> \c mydb myuser host.dom 6432
=> \c service=foo
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
=> \c postgresql://tom@localhost/mydb?application_name=myapp

`\C [` `title` ]

Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from “caption”, as it was previously only used to set the caption in an HTML table.)\cd [ directory ]

Changes the current working directory to directory. Without argument, changes to the current user's home directory.

要顯示目前的工作目錄，可以使用 ! pwd

`\conninfo`

Outputs information about the current database connection.

`\copy {` `table` [ ( `column_list` ) ] | ( `query` ) } { `from` | `to` } { `'filename'` | program `'command'` | stdin | stdout | pstdin | pstdout } [ [ with ] ( `option` [, ...] ) ]

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

When program is specified, command is executed by psql and the data passed from or to command is routed between the server and the client. Again, the execution privileges are those of the local user, not the server, and no SQL superuser privileges are required.

For \copy ... from stdin, data rows are read from the same source that issued the command, continuing until \. is read or the stream reaches EOF. This option is useful for populating tables in-line within a SQL script file. For \copy ... to stdout, output is sent to the same place as psql command output, and the COPY count command status is not printed (since it might be confused with a data row). To read/write psql's standard input or output regardless of the current command source or \o option, write from pstdin or to pstdout.

The syntax of this command is similar to that of the SQL COPY command. All options other than the data source/destination are as specified for COPY. Because of this, special parsing rules apply to the \copy meta-command. Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.

獲得與 \copy ... to 相同結果的另一種方法是使用 SQL COPY ... TO STDOUT 指令並在最後以 \g filename 或 \g | program 來結束它。與 \copy 不同，此方法允許指令跨越多行。同樣地，可以使用變數插值和 backquote 擴展。

這些操作的效率不如使用檔案、程式產生資料源、或 SQL COPY 指令，因為所有資料都必須透過用戶端/伺服器連線傳遞。對於大量資料，更應該使用 SQL 指令。

`\copyright`

Shows the copyright and distribution terms of PostgreSQL.

`\crosstabview [` `colV` [ `colH` [ `colD` [ `sortcolH` ] ] ] ]

Executes the current query buffer (like \g) and shows the results in a crosstab grid. The query must return at least three columns. The output column identified by colV becomes a vertical header and the output column identified by colH becomes a horizontal header. colD identifies the output column to display within the grid. sortcolH identifies an optional sort column for the horizontal header.

Each column specification can be a column number (starting at 1) or a column name. The usual SQL case folding and quoting rules apply to column names. If omitted, colV is taken as column 1 and colH as column 2. colH must differ from colV. If colD is not specified, then there must be exactly three columns in the query result, and the column that is neither colV nor colH is taken to be colD.

The vertical header, displayed as the leftmost column, contains the values found in column colV, in the same order as in the query results, but with duplicates removed.

The horizontal header, displayed as the first row, contains the values found in column colH, with duplicates removed. By default, these appear in the same order as in the query results. But if the optional sortcolH argument is given, it identifies a column whose values must be integer numbers, and the values from colH will appear in the horizontal header sorted according to the corresponding sortcolH values.

Inside the crosstab grid, for each distinct value x of colH and each distinct value y of colV, the cell located at the intersection (x,y) contains the value of the colD column in the query result row for which the value of colH is x and the value of colV is y. If there is no such row, the cell is empty. If there are multiple such rows, an error is reported.

`\d[S+] [` `pattern` ]

For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULL or defaults. Associated indexes, constraints, rules, and triggers are also shown. For foreign tables, the associated foreign server is shown as well. (“Matching the pattern” is defined in Patterns below.)

For some types of relation, \d shows additional information for each column: column values for sequences, indexed expressions for indexes, and foreign data wrapper options for foreign tables.

The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table, the view definition if the relation is a view, a non-default replica identity setting.

By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Note

If \d is used without a pattern argument, it is equivalent to \dtvmsE which will show a list of all visible tables, views, materialized views, sequences and foreign tables. This is purely a convenience measure.

`\da[S] [` `pattern` ]

Lists aggregate functions, together with their return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

`\dA[+] [` `pattern` ]

Lists access methods. If pattern is specified, only access methods whose names match the pattern are shown. If + is appended to the command name, each access method is listed with its associated handler function and description.

`\db[+] [` `pattern` ]

Lists tablespaces. If pattern is specified, only tablespaces whose names match the pattern are shown. If + is appended to the command name, each tablespace is listed with its associated options, on-disk size, permissions and description.

`\dc[S+] [` `pattern` ]

Lists conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated description.

`\dC[+] [` `pattern` ]

Lists type casts. If pattern is specified, only casts whose source or target types match the pattern are listed. If + is appended to the command name, each object is listed with its associated description.

`\dd[S] [` `pattern` ]

Shows the descriptions of objects of type constraint, operator class, operator family, rule, and trigger. All other comments may be viewed by the respective backslash commands for those object types.

\dd displays descriptions for objects matching the pattern, or of visible objects of the appropriate type if no argument is given. But in either case, only objects that have a description are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Descriptions for objects can be created with the COMMENT SQL command.

`\dD[S+] [` `pattern` ]

Lists domains. If pattern is specified, only domains whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated permissions and description.\ddp [ pattern ]

Lists default access privilege settings. An entry is shown for each role (and schema, if applicable) for which the default privilege settings have been changed from the built-in defaults. If pattern is specified, only entries whose role name or schema name matches the pattern are listed.

The ALTER DEFAULT PRIVILEGES command is used to set default access privileges. The meaning of the privilege display is explained in Section 5.7.

\dE[S+] [ pattern ] \di[S+] [ pattern ] \dm[S+] [ pattern ] \ds[S+] [ pattern ] \dt[S+] [ pattern ] \dv[S+] [ pattern ]

In this group of commands, the letters E, i, m, s, t, and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of these types. For example, \dit lists indexes and tables. If + is appended to the command name, each object is listed with its physical size on disk and its associated description, if any. If pattern is specified, only objects whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

`\des[+] [` `pattern` ]

Lists foreign servers (mnemonic: “external servers”). If pattern is specified, only those servers whose name matches the pattern are listed. If the form \des+ is used, a full description of each server is shown, including the server's access privileges, type, version, options, and description.

`\det[+] [` `pattern` ]

Lists foreign tables (mnemonic: “external tables”). If pattern is specified, only entries whose table name or schema name matches the pattern are listed. If the form \det+ is used, generic options and the foreign table description are also displayed.

`\deu[+] [` `pattern` ]

Lists user mappings (mnemonic: “external users”). If pattern is specified, only those mappings whose user names match the pattern are listed. If the form \deu+ is used, additional information about each mapping is shown.

Caution

\deu+ might also display the user name and password of the remote user, so care should be taken not to disclose them.\dew[+] [ pattern ]

Lists foreign-data wrappers (mnemonic: “external wrappers”). If pattern is specified, only those foreign-data wrappers whose name matches the pattern are listed. If the form \dew+ is used, the access privileges, options, and description of the foreign-data wrapper are also shown.

df[anptwS+] [ pattern ]

Lists functions, together with their result data types, argument data types, and function types, which are classified as “agg” (aggregate), “normal”, “procedure”, “trigger”, or “window”. To display only functions of specific type(s), add the corresponding letters a, n, p, t, or w to the command. If pattern is specified, only functions whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If the form \df+ is used, additional information about each function is shown, including volatility, parallel safety, owner, security classification, access privileges, language, source code and description.

Tip

To look up functions taking arguments or returning values of a specific data type, use your pager's search capability to scroll through the \df output.

`\dF[+] [` `pattern` ]

Lists text search configurations. If pattern is specified, only configurations whose names match the pattern are shown. If the form \dF+ is used, a full description of each configuration is shown, including the underlying text search parser and the dictionary list for each parser token type.

`\dFd[+] [` `pattern` ]

Lists text search dictionaries. If pattern is specified, only dictionaries whose names match the pattern are shown. If the form \dFd+ is used, additional information is shown about each selected dictionary, including the underlying text search template and the option values.

`\dFp[+] [` `pattern` ]

Lists text search parsers. If pattern is specified, only parsers whose names match the pattern are shown. If the form \dFp+ is used, a full description of each parser is shown, including the underlying functions and the list of recognized token types.

`\dFt[+] [` `pattern` ]

Lists text search templates. If pattern is specified, only templates whose names match the pattern are shown. If the form \dFt+ is used, additional information is shown about each template, including the underlying function names.

`\dg[S+] [` `pattern` ]

Lists database roles. (Since the concepts of “users” and “groups” have been unified into “roles”, this command is now equivalent to \du.) By default, only user-created roles are shown; supply the S modifier to include system roles. If pattern is specified, only those roles whose names match the pattern are listed. If the form \dg+ is used, additional information is shown about each role; currently this adds the comment for each role.\dl

This is an alias for \lo_list, which shows a list of large objects.

`\dL[S+] [` `pattern` ]

Lists procedural languages. If pattern is specified, only languages whose names match the pattern are listed. By default, only user-created languages are shown; supply the S modifier to include system objects. If + is appended to the command name, each language is listed with its call handler, validator, access privileges, and whether it is a system object.

`\dn[S+] [` `pattern` ]

Lists schemas (namespaces). If pattern is specified, only schemas whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each object is listed with its associated permissions and description, if any.

\do[S+] [ pattern ]

Lists operators with their operand and result types. If pattern is specified, only operators whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, additional information about each operator is shown, currently just the name of the underlying function.

\dO[S+] [ pattern ]

Lists collations. If pattern is specified, only collations whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If + is appended to the command name, each collation is listed with its associated description, if any. Note that only collations usable with the current database's encoding are shown, so the results may vary in different databases of the same installation.

\dp [ pattern ]

Lists tables, views and sequences with their associated access privileges. If pattern is specified, only tables, views and sequences whose names match the pattern are listed.

The GRANT and REVOKE commands are used to set access privileges. The meaning of the privilege display is explained in Section 5.7.

\dP[itn+] [ pattern ]

Lists partitioned relations. If pattern is specified, only entries whose name matches the pattern are listed. The modifiers t (tables) and i (indexes) can be appended to the command, filtering the kind of relations to list. By default, partitioned tables and indexes are listed.

If the modifier n (“nested”) is used, or a pattern is specified, then non-root partitioned relations are included, and a column is shown displaying the parent of each partitioned relation.

If + is appended to the command name, the sum of the sizes of each relation's partitions is also displayed, along with the relation's description. If n is combined with +, two sizes are shown: one including the total size of directly-attached leaf partitions, and another showing the total size of all partitions, including indirectly attached sub-partitions.

\drds [ role-pattern [ database-pattern ] ]

Lists defined configuration settings. These settings can be role-specific, database-specific, or both. role-pattern and database-pattern are used to select specific roles and databases to list, respectively. If omitted, or if * is specified, all settings are listed, including those not role-specific or database-specific, respectively.

The ALTER ROLE and ALTER DATABASE commands are used to define per-role and per-database configuration settings.

\dRp[+] [ pattern ]

Lists replication publications. If pattern is specified, only those publications whose names match the pattern are listed. If + is appended to the command name, the tables associated with each publication are shown as well.

\dRs[+] [ pattern ]

Lists replication subscriptions. If pattern is specified, only those subscriptions whose names match the pattern are listed. If + is appended to the command name, additional properties of the subscriptions are shown.

\dT[S+] [ pattern ]

Lists data types. If pattern is specified, only types whose names match the pattern are listed. If + is appended to the command name, each type is listed with its internal name and size, its allowed values if it is an enum type, and its associated permissions. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

\du[S+] [ pattern ]

Lists database roles. (Since the concepts of “users” and “groups” have been unified into “roles”, this command is now equivalent to \dg.) By default, only user-created roles are shown; supply the S modifier to include system roles. If pattern is specified, only those roles whose names match the pattern are listed. If the form \du+ is used, additional information is shown about each role; currently this adds the comment for each role.

\dx[+] [ pattern ]

Lists installed extensions. If pattern is specified, only those extensions whose names match the pattern are listed. If the form \dx+ is used, all the objects belonging to each matching extension are listed.

\dy[+] [ pattern ]

Lists event triggers. If pattern is specified, only those event triggers whose names match the pattern are listed. If + is appended to the command name, each object is listed with its associated description.

\e or \edit [ filename ] [ line_number ]

If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion. Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fashion.

The new contents of the query buffer are then re-parsed according to the normal rules of psql, treating the whole buffer as a single line. Any complete queries are immediately executed; that is, if the query buffer contains or ends with a semicolon, everything up to that point is executed. Whatever remains will wait in the query buffer; type semicolon or \g to send it, or to cancel it by clearing the query buffer. Treating the buffer as a single line primarily affects meta-commands: whatever is in the buffer after a meta-command will be taken as argument(s) to the meta-command, even if it spans multiple lines. (Thus you cannot make meta-command-using scripts this way. Use \i for that.)

If a line number is specified, psql will position the cursor on the specified line of the file or query buffer. Note that if a single all-digits argument is given, psql assumes it is a line number, not a file name.

Tip

See under Environment for how to configure and customize your editor.\echo text [ ... ]

Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example:

=> \echo `date`
Tue Oct 26 21:40:57 CEST 1999

If the first argument is an unquoted -n the trailing newline is not written.

Tip

If you use the \o command to redirect your query output you might wish to use \qecho instead of this command.\ef [ function_description [ line_number ] ]

This command fetches and edits the definition of the named function or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. Editing is done in the same way as for \edit. After the editor exits, the updated command waits in the query buffer; type semicolon or \g to send it, or to cancel.

The target function can be specified by name alone, or by name and arguments, for example foo(integer, text). The argument types must be given if there is more than one function of the same name.

If no function is specified, a blank CREATE FUNCTION template is presented for editing.

If a line number is specified, psql will position the cursor on the specified line of the function body. (Note that the function body typically does not begin on the first line of the file.)

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \ef, and neither variable interpolation nor backquote expansion are performed in the arguments.

Tip

See under Environment for how to configure and customize your editor.

\encoding [ encoding ]

Sets the client character set encoding. Without an argument, this command shows the current encoding.

\errverbose

Repeats the most recent server error message at maximum verbosity, as though VERBOSITY were set to verbose and SHOW_CONTEXT were set to always.

\ev [ view_name [ line_number ] ]

This command fetches and edits the definition of the named view, in the form of a CREATE OR REPLACE VIEW command. Editing is done in the same way as for \edit. After the editor exits, the updated command waits in the query buffer; type semicolon or \g to send it, or to cancel.

If no view is specified, a blank CREATE VIEW template is presented for editing.

If a line number is specified, psql will position the cursor on the specified line of the view definition.

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \ev, and neither variable interpolation nor backquote expansion are performed in the arguments.

\f [ string ]

Sets the field separator for unaligned query output. The default is the vertical bar (|). It is equivalent to \pset fieldsep.

\g [ filename ] \g [ |command ]

Sends the current query buffer to the server for execution. If an argument is given, the query's output is written to the named file or piped to the given shell command, instead of displaying it as usual. The file or command is written to only if the query successfully returns zero or more tuples, not if the query fails or is a non-data-returning SQL command.

If the current query buffer is empty, the most recently sent query is re-executed instead. Except for that behavior, \g without an argument is essentially equivalent to a semicolon. A \g with argument is a “one-shot” alternative to the \o command.

If the argument begins with |, then the entire remainder of the line is taken to be the command to execute, and neither variable interpolation nor backquote expansion are performed in it. The rest of the line is simply passed literally to the shell.

\gdesc

Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.

If the current query buffer is empty, the most recently sent query is described instead.

\gexec

Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed. For example, to create an index on each column of my_table:

=> SELECT format('create index on my_table(%I)', attname)
-> FROM pg_attribute
-> WHERE attrelid = 'my_table'::regclass AND attnum > 0
-> ORDER BY attnum
-> \gexec
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

The generated queries are executed in the order in which the rows are returned, and left-to-right within each row if there is more than one column. NULL fields are ignored. The generated queries are sent literally to the server for processing, so they cannot be psql meta-commands nor contain psql variable references. If any individual query fails, execution of the remaining queries continues unless ON_ERROR_STOP is set. Execution of each query is subject to ECHO processing. (Setting ECHO to all or queries is often advisable when using \gexec.) Query logging, single-step mode, timing, and other query execution features apply to each generated query as well.

If the current query buffer is empty, the most recently sent query is re-executed instead.

\gset [ prefix ]

Sends the current query buffer to the server and stores the query's output into psql variables (see Variables). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column. For example:

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset
=> \echo :var1 :var2
hello 10

If you specify a prefix, that string is prepended to the query's column names to create the variable names to use:

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset result_
=> \echo :result_var1 :result_var2
hello 10

If a column result is NULL, the corresponding variable is unset rather than being set.

If the query fails or does not return one row, no variables are changed.

If the current query buffer is empty, the most recently sent query is re-executed instead.

\gx [ filename ] \gx [ |command ]

\gx is equivalent to \g, but forces expanded output mode for this query. See \x.\h or \help [ command ]

Gives syntax help on the specified SQL command. If command is not specified, then psql will list all the commands for which syntax help is available. If command is an asterisk (*), then syntax help on all SQL commands is shown.

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \help, and neither variable interpolation nor backquote expansion are performed in the arguments.

Note

To simplify typing, commands that consists of several words do not have to be quoted. Thus it is fine to type \help alter table.

\H or \html

Turns on HTML query output format. If the HTML format is already on, it is switched back to the default aligned text format. This command is for compatibility and convenience, but see \pset about setting other output options.\i or \include filename

Reads input from the file filename and executes it as though it had been typed on the keyboard.

If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note that Readline behavior will be used only if it is active at the outermost level.

Note

If you want to see the lines on the screen as they are read you must set the variable ECHO to all.

\if expression \elif expression \else \endif

This group of commands implements nestable conditional blocks. A conditional block must begin with an \if and end with an \endif. In between there may be any number of \elif clauses, which may optionally be followed by a single \else clause. Ordinary queries and other types of backslash commands may (and usually do) appear between the commands forming a conditional block.

The \if and \elif commands read their argument(s) and evaluate them as a boolean expression. If the expression yields true then processing continues normally; otherwise, lines are skipped until a matching \elif, \else, or \endif is reached. Once an \if or \elif test has succeeded, the arguments of later \elif commands in the same block are not evaluated but are treated as false. Lines following an \else are processed only if no earlier matching \if or \elif succeeded.

The expression argument of an \if or \elif command is subject to variable interpolation and backquote expansion, just like any other backslash command argument. After that it is evaluated like the value of an on/off option variable. So a valid value is any unambiguous case-insensitive match for one of: true, false, 1, 0, on, off, yes, no. For example, t, T, and tR will all be considered to be true.

Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.

Lines being skipped are parsed normally to identify queries and backslash commands, but queries are not sent to the server, and backslash commands other than conditionals (\if, \elif, \else, \endif) are ignored. Conditional commands are checked only for valid nesting. Variable references in skipped lines are not expanded, and backquote expansion is not performed either.

All the backslash commands of a given conditional block must appear in the same source file. If EOF is reached on the main input file or an \include-ed file before all local \if-blocks have been closed, then psql will raise an error.

Here is an example:

-- check for the existence of two separate records in the database and store
-- the results in separate psql variables
SELECT
    EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
    EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
\gset
\if :is_customer
    SELECT * FROM customer WHERE customer_id = 123;
\elif :is_employee
    \echo 'is not a customer but is an employee'
    SELECT * FROM employee WHERE employee_id = 456;
\else
    \if yes
        \echo 'not a customer or employee'
    \else
        \echo 'this will never print'
    \endif
\endif

\ir or \include_relative filename

The \ir command is similar to \i, but resolves relative file names differently. When executing in interactive mode, the two commands behave identically. However, when invoked from a script, \ir interprets file names relative to the directory in which the script is located, rather than the current working directory.

\l[+] or \list[+] [ pattern ]

List the databases in the server and show their names, owners, character set encodings, and access privileges. If pattern is specified, only databases whose names match the pattern are listed. If + is appended to the command name, database sizes, default tablespaces, and descriptions are also displayed. (Size information is only available for databases that the current user can connect to.)

\lo_export loid filename

Reads the large object with OID loid from the database and writes it to filename. Note that this is subtly different from the server function lo_export, which acts with the permissions of the user that the database server runs as and on the server's file system.

Tip

Use \lo_list to find out the large object's OID.

\lo_import filename [ comment ]

Stores the file into a PostgreSQL large object. Optionally, it associates the given comment with the object. Example:

foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
lo_import 152801

The response indicates that the large object received object ID 152801, which can be used to access the newly-created large object in the future. For the sake of readability, it is recommended to always associate a human-readable comment with every object. Both OIDs and comments can be viewed with the \lo_list command.

Note that this command is subtly different from the server-side lo_import because it acts as the local user on the local file system, rather than the server's user and file system.

\lo_list

Shows a list of all PostgreSQL large objects currently stored in the database, along with any comments provided for them.

\lo_unlink loid

Deletes the large object with OID loid from the database.

Tip

Use \lo_list to find out the large object's OID.\o or \out [ filename ] \o or \out [ |command ]

Arranges to save future query results to the file filename or pipe future results to the shell command command. If no argument is specified, the query output is reset to the standard output.

“Query results” includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d); but not error messages.

Tip

To intersperse text output in between query results, use \qecho.\p or \print

Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.\password [ username ]

Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.\prompt [ text ] name

Prompts the user to supply text, which is assigned to the variable name. An optional prompt string, text, can be specified. (For multiword prompts, surround the text with single quotes.)

By default, \prompt uses the terminal for input and output. However, if the -f command line switch was used, \prompt uses standard input and standard output.\pset [ option [ value ] ]

This command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unset, as described under the particular option. If no such behavior is mentioned, then omitting value just results in the current setting being displayed.

\pset without any arguments displays the current status of all printing options.

Adjustable printing options are:border

The value must be a number. In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. In HTML format, this will translate directly into the border=... attribute. In most other formats only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense, and values above 2 will be treated the same as border = 2. The latex and latex-longtable formats additionally allow a value of 3 to add dividing lines between data rows.columns

Sets the target width for the wrapped format, and also the width limit for determining whether output is wide enough to require the pager or switch to the vertical display in expanded auto mode. Zero (the default) causes the target width to be controlled by the environment variable COLUMNS, or the detected screen width if COLUMNS is not set. In addition, if columns is zero then the wrapped format only affects screen output. If columns is nonzero then file and pipe output is wrapped to that width as well.csv_fieldsep

Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.expanded (or x)

If value is specified it must be either on or off, which will enable or disable expanded mode, or auto. If value is omitted the command toggles between the on and off settings. When expanded mode is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data wouldn't fit on the screen in the normal “horizontal” mode. In the auto setting, the expanded mode is used whenever the query output has more than one column and is wider than the screen; otherwise, the regular mode is used. The auto setting is only effective in the aligned and wrapped formats. In other formats, it always behaves as if the expanded mode is off.fieldsep

Specifies the field separator to be used in unaligned output format. That way one can create, for example, tab-separated output, which other programs might prefer. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is '|' (a vertical bar).fieldsep_zero

Sets the field separator to use in unaligned output format to a zero byte.footer

If value is specified it must be either on or off which will enable or disable display of the table footer (the (n rows) count). If value is omitted the command toggles footer display on or off.format

Sets the output format to one of aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, or wrapped. Unique abbreviations are allowed.

aligned format is the standard, human-readable, nicely formatted text output; this is the default.

unaligned format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs, for example, tab-separated or comma-separated format. However, the field separator character is not treated specially if it appears in a column's value; so CSV format may be better suited for such purposes.

csv format writes column values separated by commas, applying the quoting rules described in RFC 4180. This output is compatible with the CSV format of the server's COPY command. A header line with column names is generated unless the tuples_only parameter is on. Titles and footers are not printed. Each row is terminated by the system-dependent end-of-line character, which is typically a single newline () for Unix-like systems or a carriage return and newline sequence (\r) for Microsoft Windows. Field separator characters other than comma can be selected with \pset csv_fieldsep.

wrapped format is like aligned but wraps wide data values across lines to make the output fit in the target column width. The target width is determined as described under the columns option. Note that psql will not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.

The asciidoc, html, latex, latex-longtable, and troff-ms formats put out tables that are intended to be included in documents using the respective mark-up language. They are not complete documents! This might not be necessary in HTML, but in LaTeX you must have a complete document wrapper. The latex format uses LaTeX's tabular environment. The latex-longtable format requires the LaTeX longtable and booktabs packages.linestyle

Sets the border line drawing style to one of ascii, old-ascii, or unicode. Unique abbreviations are allowed. (That would mean one letter is enough.) The default setting is ascii. This option only affects the aligned and wrapped output formats.

ascii style uses plain ASCII characters. Newlines in data are shown using a + symbol in the right-hand margin. When the wrapped format wraps data from one line to the next without a newline character, a dot (.) is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

old-ascii style uses plain ASCII characters, using the formatting style used in PostgreSQL 8.4 and earlier. Newlines in data are shown using a : symbol in place of the left-hand column separator. When the data is wrapped from one line to the next without a newline character, a ; symbol is used in place of the left-hand column separator.

unicode style uses Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline character, an ellipsis symbol is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

When the border setting is greater than zero, the linestyle option also determines the characters with which the border lines are drawn. Plain ASCII characters work everywhere, but Unicode characters look nicer on displays that recognize them.null

Sets the string to be printed in place of a null value. The default is to print nothing, which can easily be mistaken for an empty string. For example, one might prefer \pset null '(null)'.numericlocale

If value is specified it must be either on or off which will enable or disable display of a locale-specific character to separate groups of digits to the left of the decimal marker. If value is omitted the command toggles between regular and locale-specific numeric output.pager

Controls use of a pager program for query and psql help output. If the environment variable PSQL_PAGER or PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default program (such as more) is used.

When the pager option is off, the pager program is not used. When the pager option is on, the pager is used when appropriate, i.e., when the output is to a terminal and will not fit on the screen. The pager option can also be set to always, which causes the pager to be used for all terminal output regardless of whether it fits on the screen. \pset pager without a value toggles pager use on and off.pager_min_lines

If pager_min_lines is set to a number greater than the page height, the pager program will not be called unless there are at least this many lines of output to show. The default setting is 0.recordsep

Specifies the record (line) separator to use in unaligned output format. The default is a newline character.recordsep_zero

Sets the record separator to use in unaligned output format to a zero byte.tableattr (or T)

In HTML format, this specifies attributes to be placed inside the table tag. This could for example be cellpadding or bgcolor. Note that you probably don't want to specify border here, as that is already taken care of by \pset border. If no value is given, the table attributes are unset.

In latex-longtable format, this controls the proportional width of each column containing a left-aligned data type. It is specified as a whitespace-separated list of values, e.g. '0.2 0.2 0.6'. Unspecified output columns use the last specified value.title (or C)

Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title is unset.tuples_only (or t)

If value is specified it must be either on or off which will enable or disable tuples-only mode. If value is omitted the command toggles between regular and tuples-only output. Regular output includes extra information such as column headers, titles, and various footers. In tuples-only mode, only actual table data is shown.unicode_border_linestyle

Sets the border drawing style for the unicode line style to one of single or double.unicode_column_linestyle

Sets the column drawing style for the unicode line style to one of single or double.unicode_header_linestyle

Sets the header drawing style for the unicode line style to one of single or double.

Illustrations of how these different formats look can be seen in the Examples section.

Tip

There are various shortcut commands for \pset. See \a, \C, \f, \H, , , and \x.\q or \quit

Quits the psql program. In a script file, only execution of that script is terminated.\qecho text [ ... ]

This command is identical to \echo except that the output will be written to the query output channel, as set by \o. or \reset

Resets (clears) the query buffer.\s [ filename ]

Print psql's command line history to filename. If filename is omitted, the history is written to the standard output (using the pager if appropriate). This command is not available if psql was built without Readline support.\set [ name [ value [ ... ] ] ]

Sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set to an empty-string value. To unset a variable, use the \unset command.

\set without any arguments displays the names and values of all currently-set psql variables.

Valid variable names can contain letters, digits, and underscores. See the section Variables below for details. Variable names are case-sensitive.

Certain variables are special, in that they control psql's behavior or are automatically set to reflect connection state. These variables are documented in Variables, below.

Note

This command is unrelated to the SQL command SET.\setenv name [ value ]

Sets the environment variable name to value, or if the value is not supplied, unsets the environment variable. Example:

testdb=> \setenv PAGER less
testdb=> \setenv LESS -imx4F

\sf[+] function_description

This command fetches and shows the definition of the named function or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. The definition is printed to the current query output channel, as set by \o.

The target function can be specified by name alone, or by name and arguments, for example foo(integer, text). The argument types must be given if there is more than one function of the same name.

If + is appended to the command name, then the output lines are numbered, with the first line of the function body being line 1.

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \sf, and neither variable interpolation nor backquote expansion are performed in the arguments.\sv[+] view_name

This command fetches and shows the definition of the named view, in the form of a CREATE OR REPLACE VIEW command. The definition is printed to the current query output channel, as set by \o.

If + is appended to the command name, then the output lines are numbered from 1.

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \sv, and neither variable interpolation nor backquote expansion are performed in the arguments.

Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.\T table_options

Specifies attributes to be placed within the table tag in HTML output format. This command is equivalent to \pset tableattr table_options.\timing [ on | off ]

With a parameter, turns displaying of how long each SQL statement takes on or off. Without a parameter, toggles the display between on and off. The display is in milliseconds; intervals longer than 1 second are also shown in minutes:seconds format, with hours and days fields added if needed.\unset name

Unsets (deletes) the psql variable name.

Most variables that control psql's behavior cannot be unset; instead, an \unset command is interpreted as setting them to their default values. See Variables, below.\w or \write filename \w or \write |command

Writes the current query buffer to the file filename or pipes it to the shell command command. If the current query buffer is empty, the most recently executed query is written instead.

Repeatedly execute the current query buffer (as \g does) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions. Each query result is displayed with a header that includes the \pset title string (if any), the time as of query start, and the delay interval.

If the current query buffer is empty, the most recently sent query is re-executed instead.\x [ on | off | auto ]

Sets or toggles expanded table formatting mode. As such it is equivalent to \pset expanded.\z [ pattern ]

Lists tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed.

This is an alias for \dp (“display privileges”).\! [ command ]

With no argument, escapes to a sub-shell; psql resumes when the sub-shell exits. With an argument, executes the shell command command.

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \!, and neither variable interpolation nor backquote expansion are performed in the arguments. The rest of the line is simply passed literally to the shell.\? [ topic ]

Shows help information. The optional topic parameter (defaulting to commands) selects which part of psql is explained: commands describes psql's backslash commands; options describes the command-line options that can be passed to psql; and variables shows help about psql configuration variables.\;

Backslash-semicolon is not a meta-command in the same way as the preceding commands; rather, it simply causes a semicolon to be added to the query buffer without any further processing.

Normally, psql will dispatch a SQL command to the server as soon as it reaches the command-ending semicolon, even if more input remains on the current line. Thus for example entering

select 1; select 2; select 3;

will result in the three SQL commands being individually sent to the server, with each one's results being displayed before continuing to the next command. However, a semicolon entered as \; will not trigger command processing, so that the command before it and the one after are effectively combined and sent to the server in one request. So for example

select 1\; select 2\; select 3;

results in sending the three SQL commands to the server in a single request, when the non-backslashed semicolon is reached. The server executes such a request as a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 52.2.2.1 for more details about how the server handles multi-query strings.) psql prints only the last query result it receives for each request; in this example, although all three SELECTs are indeed executed, psql only prints the 3.

Patterns

The various \d commands accept a pattern parameter to specify the object name(s) to be displayed. In the simplest case, a pattern is just the exact name of the object. The characters within a pattern are normally folded to lower case, just as in SQL names; for example, \dt FOO will display the table named foo. As in SQL names, placing double quotes around a pattern stops folding to lower case. Should you need to include an actual double quote character in a pattern, write it as a pair of double quotes within a double-quote sequence; again this is in accord with the rules for SQL quoted identifiers. For example, \dt "FOO""BAR" will display the table named FOO"BAR (not foo"bar). Unlike the normal rules for SQL names, you can put double quotes around just part of a pattern, for instance \dt FOO"FOO"BAR will display the table named fooFOObar.

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern.

Within a pattern, * matches any sequence of characters (including no characters) and ? matches any single character. (This notation is comparable to Unix shell file name patterns.) For example, \dt int* displays tables whose names begin with int. But within double quotes, * and ? lose these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables whose table name includes bar that are in schemas whose schema name starts with foo. When no dot appears, then the pattern matches only objects that are visible in the current schema search path. Again, a dot within double quotes loses its special meaning and is matched literally.

Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for . which is taken as a separator as mentioned above, * which is translated to the regular-expression notation .*, ? which is translated to ., and $ which is matched literally. You can emulate these pattern characters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as a regular-expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words, $ is automatically appended to your pattern). Write * at the beginning and/or end if you don't wish the pattern to be anchored. Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally. Also, the regular expression special characters are matched literally in operator name patterns (i.e., the argument of \do).

Advanced Features

Variables

psql provides variable substitution features similar to common Unix command shells. Variables are simply name/value pairs, where the value can be any string of any length. The name must consist of letters (including non-Latin letters), digits, and underscores.

To set a variable, use the psql meta-command \set. For example,

testdb=> \set foo bar

sets the variable foo to the value bar. To retrieve the content of the variable, precede the name with a colon, for example:

testdb=> \echo :foo
bar

This works in both regular SQL commands and meta-commands; there is more detail in SQL Interpolation, below.

If you call \set without a second argument, the variable is set to an empty-string value. To unset (i.e., delete) a variable, use the command \unset. To show the values of all variables, call \set without any argument.

Note

The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as \set :foo 'something' and get “soft links” or “variable variables” of Perl or PHP fame, respectively. Unfortunately (or fortunately?), there is no way to do anything useful with these constructs. On the other hand, \set bar :foo is a perfectly valid way to copy a variable.

A number of these variables are treated specially by psql. They represent certain option settings that can be changed at run time by altering the value of the variable, or in some cases represent changeable state of psql. By convention, all specially treated variables' names consist of all upper-case ASCII letters (and possibly digits and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.

Variables that control psql's behavior generally cannot be unset or set to invalid values. An \unset command is allowed but is interpreted as setting the variable to its default value. A \set command without a second argument is interpreted as setting the variable to on, for control variables that accept that value, and is rejected for others. Also, control variables that accept the values on and off will also accept other common spellings of Boolean values, such as true and false.

The specially treated variables are:AUTOCOMMIT

When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM).

Note

In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost.

Note

The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file.COMP_KEYWORD_CASE

Determines which letter case to use when completing an SQL key word. If set to lower or upper, the completed word will be in lower or upper case, respectively. If set to preserve-lower or preserve-upper (the default), the completed word will be in the case of the word already entered, but words being completed without anything entered will be in lower or upper case, respectively.DBNAME

The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.ECHO

If set to all, all nonempty input lines are printed to standard output as they are read. (This does not apply to lines read interactively.) To select this behavior on program start-up, use the switch -a. If set to queries, psql prints each query to standard output as it is sent to the server. The switch to select this behavior is -e. If set to errors, then only failed queries are displayed on standard error output. The switch for this behavior is -b. If set to none (the default), then no queries are displayed.ECHO_HIDDEN

When this variable is set to on and a backslash command queries the database, the query is first shown. This feature helps you to study PostgreSQL internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch -E.) If you set this variable to the value noexec, the queries are just shown but are not actually sent to the server and executed. The default value is off.ENCODING

The current client character set encoding. This is set every time you connect to a database (including program start-up), and when you change the encoding with \encoding, but it can be changed or unset.ERROR

true if the last SQL query failed, false if it succeeded. See also SQLSTATE.FETCH_COUNT

If this variable is set to an integer value greater than zero, the results of SELECT queries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows.

Tip

Although you can use any output format with this feature, the default aligned format tends to look bad because each group of FETCH_COUNT rows will be formatted separately, leading to varying column widths across the row groups. The other output formats work better.HIDE_TABLEAM

If this variable is set to true, a table's access method details are not displayed. This is mainly useful for regression tests.HISTCONTROL

If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If set to none (the default), all lines read in interactive mode are saved on the history list.

Note

This feature was shamelessly plagiarized from Bash.HISTFILE

The file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is ~/.psql_history, or %APPDATA%\postgresql\psql_history on Windows. For example, putting:

\set HISTFILE ~/.psql_history- :DBNAME

in ~/.psqlrc will cause psql to maintain a separate history for each database.

Note

This feature was shamelessly plagiarized from Bash.HISTSIZE

The maximum number of commands to store in the command history (default 500). If set to a negative value, no limit is applied.

Note

This feature was shamelessly plagiarized from Bash.HOST

The database server host you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.IGNOREEOF

If set to 1 or less, sending an EOF character (usually Control+D) to an interactive session of psql will terminate the application. If set to a larger numeric value, that many consecutive EOF characters must be typed to make an interactive session terminate. If the variable is set to a non-numeric value, it is interpreted as 10. The default is 0.

Note

This feature was shamelessly plagiarized from Bash.LASTOID

The value of the last affected OID, as returned from an INSERT or \lo_import command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. PostgreSQL servers since version 12 do not support OID system columns anymore, thus LASTOID will always be 0 following INSERT when targeting such servers.LAST_ERROR_MESSAGE LAST_ERROR_SQLSTATE

The primary error message and associated SQLSTATE code for the most recent failed query in the current psql session, or an empty string and 00000 if no error has occurred in the current session.ON_ERROR_ROLLBACK

When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. When set to interactive, such errors are only ignored in interactive sessions, and not when reading script files. When set to off (the default), a statement in a transaction block that generates an error aborts the entire transaction. The error rollback mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.ON_ERROR_STOP

By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.PORT

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be changed or unset.PROMPT1 PROMPT2 PROMPT3

These specify what the prompts psql issues should look like. See Prompting below.QUIET

Setting this variable to on is equivalent to the command line option -q. It is probably not too useful in interactive mode.ROW_COUNT

The number of rows returned or affected by the last SQL query, or 0 if the query failed or did not report a row count.SERVER_VERSION_NAME SERVER_VERSION_NUM

The server's version number as a string, for example 9.6.2, 10.1 or 11beta1, and in numeric form, for example 90602 or 100001. These are set every time you connect to a database (including program start-up), but can be changed or unset.SHOW_CONTEXT

This variable can be set to the values never, errors, or always to control whether CONTEXT fields are displayed in messages from the server. The default is errors (meaning that context will be shown in error messages, but not in notice or warning messages). This setting has no effect when VERBOSITY is set to terse or sqlstate. (See also \errverbose, for use when you want a verbose version of the error you just got.)SINGLELINE

Setting this variable to on is equivalent to the command line option -S.SINGLESTEP

Setting this variable to on is equivalent to the command line option -s.SQLSTATE

The error code (see Appendix A) associated with the last SQL query's failure, or 00000 if it succeeded.USER

The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be changed or unset.VERBOSITY

This variable can be set to the values default, verbose, terse, or sqlstate to control the verbosity of error reports. (See also \errverbose, for use when you want a verbose version of the error you just got.)VERSION VERSION_NAME VERSION_NUM

These variables are set at program start-up to reflect psql's version, respectively as a verbose string, a short string (e.g., 9.6.2, 10.1, or 11beta1), and a number (e.g., 90602 or 100001). They can be changed or unset.

SQL Interpolation

A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands. Furthermore, psql provides facilities for ensuring that variable values used as SQL literals and identifiers are properly quoted. The syntax for interpolating a value without any quoting is to prepend the variable name with a colon (:). For example,

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;

would query the table my_table. Note that this may be unsafe: the value of the variable is copied literally, so it can contain unbalanced quotes, or even backslash commands. You must make sure that it makes sense where you put it.

When a value is to be used as an SQL literal or identifier, it is safest to arrange for it to be quoted. To quote the value of a variable as an SQL literal, write a colon followed by the variable name in single quotes. To quote the value as an SQL identifier, write a colon followed by the variable name in double quotes. These constructs deal correctly with quotes and other special characters embedded within the variable value. The previous example would be more safely written this way:

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";

Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn't work to produce a quoted literal from a variable's value (and it would be unsafe if it did work, since it wouldn't correctly handle quotes embedded in the value).

One example use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then interpolate the variable's value as a quoted string:

testdb=> \set content `cat my_file.txt`
testdb=> INSERT INTO my_table VALUES (:'content');

(Note that this still won't work if my_file.txt contains NUL bytes. psql does not support embedded NUL bytes in variable values.)

Since colons can legally appear in SQL commands, an apparent attempt at interpolation (that is, :name, :'name', or :"name") is not replaced unless the named variable is currently set. In any case, you can escape a colon with a backslash to protect it from substitution.

The :{?name} special syntax returns TRUE or FALSE depending on whether the variable exists or not, and is thus always substituted, unless the colon is backslash-escaped.

The colon syntax for variables is standard SQL for embedded query languages, such as ECPG. The colon syntaxes for array slices and type casts are PostgreSQL extensions, which can sometimes conflict with the standard usage. The colon-quote syntax for escaping a variable's value as an SQL literal or identifier is a psql extension.

Prompting

The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:%M

The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.%m

The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.%>

The port number at which the database server is listening.%n

The database session user name. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)%/

The name of the current database.%~

Like %/, but the output is ~ (tilde) if the database is your default database.%#

If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)%p

The process ID of the backend currently connected to.%R

In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't produce anything.%x

Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).%l

The line number inside the current statement, starting from 1.%digits

The character with the indicated octal code is substituted.%:name:

The value of the psql variable name. See the section Variables for details.%`command`

The output of command, similar to ordinary “back-tick” substitution.%[ ... %]

Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example:

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals.

To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

Note

This feature was shamelessly plagiarized from tcsh.

Command-Line Editing

psql supports the Readline library for convenient line editing and retrieval. The command history is automatically saved when psql exits and is reloaded when psql starts up. Tab-completion is also supported, although the completion logic makes no claim to be an SQL parser. The queries generated by tab-completion can also interfere with other SQL commands, e.g. SET TRANSACTION ISOLATION LEVEL. If for some reason you do not like the tab completion, you can turn it off by putting this in a file named .inputrc in your home directory:

$if psql
set disable-completion on
$endif

(This is not a psql but a Readline feature. Read its documentation for further details.)

Environment

COLUMNS

If \pset columns is zero, controls the width for the wrapped format and width for determining if wide output requires the pager or should be switched to the vertical format in expanded auto mode.PGDATABASE PGHOST PGPORT PGUSER

Default connection parameters (see Section 33.14).PG_COLOR

Specifies whether to use color in diagnostics messages. Possible values are always, auto, never.PSQL_EDITOR EDITOR VISUAL

Editor used by the \e, \ef, and \ev commands. These variables are examined in the order listed; the first that is set is used. If none of them is set, the default is to use vi on Unix systems or notepad.exe on Windows systems.PSQL_EDITOR_LINENUMBER_ARG

When \e, \ef, or \ev is used with a line number argument, this variable specifies the command-line argument used to pass the starting line number to the user's editor. For editors such as Emacs or vi, this is a plus sign. Include a trailing space in the value of the variable if there needs to be space between the option name and the line number. Examples:

PSQL_EDITOR_LINENUMBER_ARG='+'
PSQL_EDITOR_LINENUMBER_ARG='--line '

The default is + on Unix systems (corresponding to the default editor vi, and useful for many other common editors); but there is no default on Windows systems.PSQL_HISTORY

Alternative location for the command history file. Tilde (~) expansion is performed.PSQL_PAGER PAGER

If a query's results do not fit on the screen, they are piped through this command. Typical values are more or less. Use of the pager can be disabled by setting PSQL_PAGER or PAGER to an empty string, or by adjusting the pager-related options of the \pset command. These variables are examined in the order listed; the first that is set is used. If none of them is set, the default is to use more on most platforms, but less on Cygwin.PSQLRC

Alternative location of the user's .psqlrc file. Tilde (~) expansion is performed.SHELL

Command executed by the \! command.TMPDIR

Directory for storing temporary files. The default is /tmp.

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 33.14).

Files

psqlrc and ~/.psqlrc

Unless it is passed an -X option, psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.

The system-wide startup file is named psqlrc and is sought in the installation's “system configuration” directory, which is most reliably identified by running pg_config --sysconfdir. By default this directory will be ../etc/ relative to the directory containing the PostgreSQL executables. The name of this directory can be set explicitly via the PGSYSCONFDIR environment variable.

The user's personal startup file is named .psqlrc and is sought in the invoking user's home directory. On Windows, which lacks such a concept, the personal startup file is named %APPDATA%\postgresql\psqlrc.conf. The location of the user's startup file can be set explicitly via the PSQLRC environment variable.

Both the system-wide startup file and the user's personal startup file can be made psql-version-specific by appending a dash and the PostgreSQL major or minor release number to the file name, for example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific version-matching file will be read in preference to a non-version-specific file..psql_history

The command-line history is stored in the file ~/.psql_history, or %APPDATA%\postgresql\psql_history on Windows.

The location of the history file can be set explicitly via the HISTFILE psql variable or the PSQL_HISTORY environment variable.

Notes

psql works best with servers of the same or an older major version. Backslash commands are particularly likely to fail if the server is of a newer version than psql itself. However, backslash commands of the \d family should work with servers of versions back to 7.4, though not necessarily with servers newer than psql itself. The general functionality of running SQL commands and displaying query results should also work with servers of a newer major version, but this cannot be guaranteed in all cases.
If you want to use psql to connect to several servers of different major versions, it is recommended that you use the newest version of psql. Alternatively, you can keep around a copy of psql from each major version and be sure to use the version that matches the respective server. But in practice, this additional complication should not be necessary.
Before PostgreSQL 9.6, the -c option implied -X (--no-psqlrc); this is no longer the case.
Before PostgreSQL 8.4, psql allowed the first argument of a single-letter backslash command to start directly after the command, without intervening whitespace. Now, some whitespace is required.

Notes for Windows Users

psql 被建構為“console application”。由於 Windows 的 console 視窗使用與系統其餘部分不同的編碼，因此在 psql 中使用 8 位元字元時必須格外小心。如果 psql 檢測到有問題的語系代碼頁，它將在啟動時警告您。要更改語系代碼頁，需要做兩件事：

透過執行 cmd.exe /c chcp 1252 來設定語言代碼頁。（1252 是適用於德文的代碼頁；你需要將其代換為你的語言代碼。）如果使用的是 Cygwin，則可以將此命令放在 /etc/profile 中。
將 console 字體設定為 Lucida Console，因為 raster 字體不適用於 ANSI 代碼頁。

Examples

The first example shows how to spread a command over several lines of input. Notice the changing prompt:

testdb=> CREATE TABLE my_table (
testdb(>  first integer not null default 0,
testdb(>  second text)
testdb-> ;
CREATE TABLE

Now look at the table definition again:

testdb=> \d my_table
              Table "public.my_table"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 first  | integer |           | not null | 0
 second | text    |           |          |

Now we change the prompt to something more interesting:

testdb=> \set PROMPT1 '%n@%m %~%R%# '
peter@localhost testdb=>

Let's assume you have filled the table with data and want to take a look at it:

peter@localhost testdb=> SELECT * FROM my_table;
 first | second
-------+--------
     1 | one
     2 | two
     3 | three
     4 | four
(4 rows)

You can display tables in different ways by using the \pset command:

peter@localhost testdb=> \pset border 2
Border style is 2.
peter@localhost testdb=> SELECT * FROM my_table;
+-------+--------+
| first | second |
+-------+--------+
|     1 | one    |
|     2 | two    |
|     3 | three  |
|     4 | four   |
+-------+--------+
(4 rows)

peter@localhost testdb=> \pset border 0
Border style is 0.
peter@localhost testdb=> SELECT * FROM my_table;
first second
----- ------
    1 one
    2 two
    3 three
    4 four
(4 rows)

peter@localhost testdb=> \pset border 1
Border style is 1.
peter@localhost testdb=> \pset format csv
Output format is csv.
peter@localhost testdb=> \pset tuples_only
Tuples only is on.
peter@localhost testdb=> SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4
peter@localhost testdb=> \pset format unaligned
Output format is unaligned.
peter@localhost testdb=> \pset fieldsep '\t'
Field separator is "    ".
peter@localhost testdb=> SELECT second, first FROM my_table;
one     1
two     2
three   3
four    4

Alternatively, use the short commands:

peter@localhost testdb=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
peter@localhost testdb=> SELECT * FROM my_table;
-[ RECORD 1 ]-
first  | 1
second | one
-[ RECORD 2 ]-
first  | 2
second | two
-[ RECORD 3 ]-
first  | 3
second | three
-[ RECORD 4 ]-
first  | 4
second | four

When suitable, query results can be shown in a crosstab representation with the \crosstabview command:

testdb=> SELECT first, second, first > 2 AS gt2 FROM my_table;
 first | second | gt2 
-------+--------+-----
     1 | one    | f
     2 | two    | f
     3 | three  | t
     4 | four   | t
(4 rows)

testdb=> \crosstabview first second
 first | one | two | three | four 
-------+-----+-----+-------+------
     1 | f   |     |       | 
     2 |     | f   |       | 
     3 |     |     | t     | 
     4 |     |     |       | t
(4 rows)

This second example shows a multiplication table with rows sorted in reverse numerical order and columns with an independent, ascending numerical order.

testdb=> SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
testdb(> row_number() over(order by t2.first) AS ord
testdb(> FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
testdb(> \crosstabview "A" "B" "AxB" ord
 A | 101 | 102 | 103 | 104 
---+-----+-----+-----+-----
 4 | 404 | 408 | 412 | 416
 3 | 303 | 306 | 309 | 312
 2 | 202 | 204 | 206 | 208
 1 | 101 | 102 | 103 | 104
(4 rows)

II. PostgreSQL 用戶端工具

createdb

Synopsis

Description

Options

Environment

Diagnostics

Examples

See Also

createuser

語法

說明

選項

環境變數

例外

範例

參閱

dropdb

Synopsis

Description

Options

Environment

Diagnostics

Examples

See Also

dropuser

語法

說明

參數

環境變數

診斷

範例

參閱

oid2name

Synopsis

Description

Note

Options

Environment

Notes

Examples

Author

pgbench

語法

說明

注意

選項

資料庫初始化專用選項

評估階段專用選項

通用選項

進階說明

實際上是什麼樣的交易在 pgbench 中執行呢？

自訂腳本

注意

Table 240. Automatic Variables

內建函數

Table 241. pgbench Functions

記錄每筆交易

彙總記錄

每個指令耗時

好的作法

pg_basebackup

語法

說明

Options

環境變數

注意

範例

參閱

pg_dump

語法

說明

選項

Note

Note

Note

Environment

Diagnostics

Notes

範例

`範例`