教學課程:使用 Azure CLI 設計適用於 PostgreSQL 的 Azure 資料庫 - 單一伺服器Tutorial: Design an Azure Database for PostgreSQL - Single Server using Azure CLI

在本教學課程中,您將使用 Azure CLI (命令列介面) 及其他公用程式來學習如何:In this tutorial, you use Azure CLI (command-line interface) and other utilities to learn how to:

  • 建立適用於 PostgreSQL 的 Azure 資料庫伺服器Create an Azure Database for PostgreSQL server
  • 設定伺服器防火牆Configure the server firewall
  • 使用 psql 公用程式來建立資料庫Use psql utility to create a database
  • 載入範例資料Load sample data
  • 查詢資料Query data
  • 更新資料Update data
  • 還原資料Restore data

您可以在瀏覽器中使用 Azure Cloud Shell 或在自己的電腦上安裝 Azure CLI,以執行本教學課程中的命令。You may use the Azure Cloud Shell in the browser, or install Azure CLI on your own computer to run the commands in this tutorial.

開啟 Azure Cloud ShellOpen Azure Cloud Shell

Azure Cloud Shell 是裝載於 Azure 中的互動式殼層環境,並且會透過瀏覽器來使用。Azure Cloud Shell is an interactive shell environment hosted in Azure and used through your browse. Azure Cloud Shell 可讓您使用 bashPowerShell 殼層來執行各種可與 Azure 服務搭配運作的工具。Azure Cloud Shell allows you to use either bash or PowerShell shells to run a variety of tools to work with Azure services. Azure Cloud Shell 已預先安裝一些命令,可讓您執行本文的內容,而不必在本機環境上安裝任何工具。Azure Cloud Shell comes pre-installed with the commands to allow you to run the content of this article without having to install anything on your local environment.

若要在 Azure Cloud Shell 上執行本文所包含的任何程式碼,請開啟 Cloud Shell 工作階段、使用某個程式碼區塊上的 [複製] 按鈕來複製程式碼,然後使用 Ctrl+Shift+V (在 Windows 和 Linux 上) 或 Cmd+Shift+V (在 macOS 上) 將程式碼貼到 Cloud Shell 工作階段中。To run any code contained in this article on Azure Cloud Shell, open a Cloud Shell session, use the Copy button on a code block to copy the code, and paste it into the Cloud Shell session with Ctrl+Shift+V on Windows and Linux, or Cmd+Shift+V on macOS. 貼上的文字不會自動執行,因此請按 Enter 來執行程式碼。Pasted text is not automatically executed, so press Enter to run code.

您可以使用下列方式來啟動 Azure Cloud Shell:You can launch Azure Cloud Shell with:

選取程式碼區塊右上角的 [試試看] 。Select Try It in the upper-right corner of a code block. 這__不會__自動將文字複製到 Cloud Shell。This doesn't automatically copy text to Cloud Shell. Azure Cloud Shell 的試試看範例
在瀏覽器中開啟 shell.azure.comOpen shell.azure.com in your browser. 啟動 Azure Cloud Shell 按鈕Launch Azure Cloud Shell button
選取 Azure 入口網站右上角功能表上的 [Cloud Shell] 按鈕。Select the Cloud Shell button on the menu in the upper-right corner of the Azure portal. Azure 入口網站中的 [Cloud Shell] 按鈕

如果您選擇在本機安裝和使用 CLI,本文會要求您執行 Azure CLI 2.0 版或更新版本。If you choose to install and use the CLI locally, this article requires that you are running the Azure CLI version 2.0 or later. 執行 az --version 以尋找版本。Run az --version to find the version. 如果您需要安裝或升級,請參閱安裝 Azure CLIIf you need to install or upgrade, see Install Azure CLI.

如果您有多個訂用帳戶,請選擇資源所在或作為計費對象的適當訂用帳戶。If you have multiple subscriptions, choose the appropriate subscription in which the resource exists or is billed for. 使用 az account set 命令來選取您帳戶底下的特定訂用帳戶 ID。Select a specific subscription ID under your account using az account set command.

az account set --subscription 00000000-0000-0000-0000-000000000000

建立資源群組Create a resource group

使用 az group create 命令建立 Azure 資源群組Create an Azure resource group using the az group create command. 資源群組是在其中以群組方式部署與管理 Azure 資源的邏輯容器。A resource group is a logical container into which Azure resources are deployed and managed as a group. 下列範例會在 westus 位置建立名為 myresourcegroup 的資源群組。The following example creates a resource group named myresourcegroup in the westus location.

az group create --name myresourcegroup --location westus

建立適用於 PostgreSQL 的 Azure 資料庫伺服器Create an Azure Database for PostgreSQL server

使用 az postgres server create 命令來建立適用於 PostgreSQL 的 Azure 資料庫伺服器Create an Azure Database for PostgreSQL server using the az postgres server create command. 一個伺服器會包含一組以群組方式管理的資料庫。A server contains a group of databases managed as a group.

下列範例會使用伺服器管理員登入 myadmin 在資源群組 myresourcegroup 中建立名為 mydemoserver 的伺服器。The following example creates a server called mydemoserver in your resource group myresourcegroup with server admin login myadmin. 伺服器的名稱會對應至 DNS 名稱,因此必須是 Azure 中全域唯一的。The name of a server maps to DNS name and is thus required to be globally unique in Azure. <server_admin_password> 替換成您自己的值。Substitute the <server_admin_password> with your own value. 這是一部一般用途、具有 2 個 vCore 的第 5 代伺服器。It is a General Purpose, Gen 5 server with 2 vCores.

az postgres server create --resource-group myresourcegroup --name mydemoserver --location westus --admin-user myadmin --admin-password <server_admin_password> --sku-name GP_Gen5_2 --version 9.6

sku-name 參數值會遵循慣例 {pricing tier}_{compute generation}_{vCores},如下列範例所示:The sku-name parameter value follows the convention {pricing tier}_{compute generation}_{vCores} as in the examples below:

  • --sku-name B_Gen5_2 對應於基本、第 5 代和 2 個虛擬核心。--sku-name B_Gen5_2 maps to Basic, Gen 5, and 2 vCores.
  • --sku-name GP_Gen5_32 對應於一般用途、第 5 代和 32 個 vCore。--sku-name GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
  • --sku-name MO_Gen5_2 對應於記憶體最佳化、第 5 代和 2 個 vCore。--sku-name MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.

請參閱定價層文件,以了解每個區域和每一層的有效值。Please see the pricing tiers documentation to understand the valid values per region and per tier.

重要

必須要有您在此處指定的伺服器系統管理員登入和密碼,稍後在本快速入門中才能登入伺服器及其資料庫。The server admin login and password that you specify here are required to log in to the server and its databases later in this quickstart. 請記住或記錄此資訊,以供稍後使用。Remember or record this information for later use.

根據預設,postgres 資料庫會建立在您的伺服器底下。By default, postgres database gets created under your server. postgres 資料庫是要供使用者、公用程式及第三方應用程式使用的預設資料庫。The postgres database is a default database meant for use by users, utilities, and third-party applications.

設定伺服器層級防火牆規則Configure a server-level firewall rule

使用 az postgres server firewall-rule create 命令來建立 Azure PostgreSQL 伺服器層級防火牆規則。Create an Azure PostgreSQL server-level firewall rule with the az postgres server firewall-rule create command. 伺服器層級防火牆規則可允許外部應用程式 (例如 psqlPgAdmin) 穿過 Azure PostgreSQL 服務防火牆連線到您的伺服器。A server-level firewall rule allows an external application, such as psql or PgAdmin to connect to your server through the Azure PostgreSQL service firewall.

您可以設定一個防火牆規則,來涵蓋能夠從您網路連線的 IP 範圍。You can set a firewall rule that covers an IP range to be able to connect from your network. 下列範例使用 az postgres server firewall-rule create 來建立允許從單一 IP 位址連線的防火牆規則 AllowMyIPThe following example uses az postgres server firewall-rule create to create a firewall rule AllowMyIP that allows connection from a single IP address.

az postgres server firewall-rule create --resource-group myresourcegroup --server mydemoserver --name AllowMyIP --start-ip-address 192.168.0.1 --end-ip-address 192.168.0.1

若要限制只有您的網路才能存取 Azure PostgreSQL 伺服器,您可以將防火牆規則設定成只涵蓋公司的網路 IP 位址範圍。To restrict access to your Azure PostgreSQL server to only your network, you can set the firewall rule to only cover your corporate network IP address range.

注意

Azure PostgreSQL 伺服器會透過連接埠 5432 進行通訊。Azure PostgreSQL server communicates over port 5432. 當您從公司網路內進行連線時,網路的防火牆可能不允許透過連接埠 5432 的輸出流量。When connecting from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. 請要求您的 IT 部門開啟連接埠 5432,以連線至 Azure SQL Database 伺服器。Have your IT department open port 5432 to connect to your Azure SQL Database server.

取得連線資訊Get the connection information

若要連線到您的伺服器,您必須提供主機資訊和存取認證。To connect to your server, you need to provide host information and access credentials.

az postgres server show --resource-group myresourcegroup --name mydemoserver

結果會採用 JSON 格式。The result is in JSON format. 請記下 administratorLoginfullyQualifiedDomainNameMake a note of the administratorLogin and fullyQualifiedDomainName.

{
  "administratorLogin": "myadmin",
  "earliestRestoreDate": null,
  "fullyQualifiedDomainName": "mydemoserver.postgres.database.azure.com",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforPostgreSQL/servers/mydemoserver",
  "location": "westus",
  "name": "mydemoserver",
  "resourceGroup": "myresourcegroup",
  "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageMb": 5120
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"

}

使用 psql 來連線到適用於 PostgreSQL 資料庫的 Azure 資料庫Connect to Azure Database for PostgreSQL database using psql

如果您的用戶端電腦已安裝 PostgreSQL,您可以使用 psql 的本機執行個體,或 Azure 雲端主控台來連線到 Azure PostgreSQL 伺服器。If your client computer has PostgreSQL installed, you can use a local instance of psql, or the Azure Cloud Console to connect to an Azure PostgreSQL server. 現在我們將使用 psql 命令列公用程式來連線到「適用於 PostgreSQL 的 Azure 資料庫」伺服器。Let's now use the psql command-line utility to connect to the Azure Database for PostgreSQL server.

  1. 執行下列 psql 命令以連線到「適用於 PostgreSQL 的 Azure 資料庫」資料庫:Run the following psql command to connect to an Azure Database for PostgreSQL database:

    psql --host=<servername> --port=<port> --username=<user@servername> --dbname=<dbname>
    

    例如,下列命令會使用存取認證,連線到 PostgreSQL 伺服器 mydemoserver.postgres.database.azure.com 上名為 postgres 的預設資料庫。For example, the following command connects to the default database called postgres on your PostgreSQL server mydemoserver.postgres.database.azure.com using access credentials. 系統提示輸入密碼時,請輸入您選擇的 <server_admin_password>Enter the <server_admin_password> you chose when prompted for password.

    psql --host=mydemoserver.postgres.database.azure.com --port=5432 --username=myadmin@mydemoserver --dbname=postgres
    
  2. 連線到伺服器之後,在提示字元中建立空白資料庫:Once you are connected to the server, create a blank database at the prompt:

    CREATE DATABASE mypgsqldb;
    
  3. 在提示字元,執行下列命令以將連線切換到新建立的資料庫 mypgsqldbAt the prompt, execute the following command to switch connection to the newly created database mypgsqldb:

    \c mypgsqldb
    

在資料庫中建立資料表Create tables in the database

既然您已知道如何連線到「適用於 PostgreSQL 的 Azure 資料庫」,您可以了解如何完成一些基本工作:Now that you know how to connect to the Azure Database for PostgreSQL, you can complete some basic tasks:

首先,建立資料表並在其中載入一些資料。First, create a table and load it with some data. 例如,建立可追蹤清查資訊的資料表:For example, create a table that tracks inventory information:

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);

您現在可以輸入下列命令來查看資料表清單中新建立的資料表:You can see the newly created table in the list of tables now by typing:

\dt

將資料載入到資料表Load data into the table

既然已建立資料表,請在其中插入一些資料。Now that there is a table created, insert some data into it. 在開啟的命令提示字元視窗中,執行下列查詢以插入幾列資料:At the open command prompt window, run the following query to insert some rows of data:

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

您現在已將兩列範例資料新增到先前建立的資料表中。You have now added two rows of sample data into the table you created earlier.

查詢並更新資料表中的資料Query and update the data in the tables

執行下列查詢,以從清查資料表中擷取資訊:Execute the following query to retrieve information from the inventory table:

SELECT * FROM inventory;

您也可以更新清查資料表中的資料:You can also update the data in the inventory table:

UPDATE inventory SET quantity = 200 WHERE name = 'banana';

當您擷取資料時,可以看到更新的值:You can see the updated values when you retrieve the data:

SELECT * FROM inventory;

將資料庫還原至先前的時間點Restore a database to a previous point in time

假設您不小心刪除了資料表。Imagine you have accidentally deleted a table. 這是您無法輕易復原的情況。This is something you cannot easily recover from. 適用於 PostgreSQL 的 Azure 資料庫可讓您返回至伺服器上任何有備份的時間點 (取決於您設定的備份保留期限),並將此時間點還原至新的伺服器。Azure Database for PostgreSQL allows you to go back to any point-in-time for which your server has backups (determined by the backup retention period you configured) and restore this point-in-time to a new server. 您可以使用這個新的伺服器來復原已刪除的資料。You can use this new server to recover your deleted data.

下列命令會將範例伺服器還原到新增資料表之前的時間點:The following command restores the sample server to a point before the table was added:

az postgres server restore --resource-group myresourcegroup --name mydemoserver-restored --restore-point-in-time 2017-04-13T13:59:00Z --source-server mydemoserver

az postgres server restore 命令需要下列參數:The az postgres server restore command needs the following parameters:

設定Setting 建議的值Suggested value 說明Description  
resource-groupresource-group  myresourcegroupmyresourcegroup  來源伺服器所在的資源群組。The resource group in which the source server exists. 
namename mydemoserver-restoredmydemoserver-restored 還原命令所建立之新伺服器的名稱。The name of the new server that is created by the restore command.
restore-point-in-timerestore-point-in-time 2017-04-13T13:59:00Z2017-04-13T13:59:00Z 選取所要還原的時間點。Select a point-in-time to restore to. 這個日期和時間必須在來源伺服器的備份保留期限內。This date and time must be within the source server's backup retention period. 請使用 ISO8601 日期和時間格式。Use ISO8601 date and time format. 例如,您可能會使用您自己的本地時區,例如 2017-04-13T05:59:00-08:00,或使用 UTC Zulu 格式 2017-04-13T13:59:00ZFor example, you may use your own local timezone, such as 2017-04-13T05:59:00-08:00, or use UTC Zulu format 2017-04-13T13:59:00Z.
source-serversource-server mydemoservermydemoserver 要進行還原的來源伺服器之名稱或識別碼。The name or ID of the source server to restore from.

將伺服器還原至時間點會建立新的伺服器,也就是您所指定時間點的原始伺服器複本。Restoring a server to a point-in-time creates a new server, copied as the original server as of the point in time you specify. 還原伺服器的位置與定價層值與來源伺服器相同。The location and pricing tier values for the restored server are the same as the source server.

命令是同步的,將會在伺服器還原之後傳回。The command is synchronous, and will return after the server is restored. 一旦還原完成時,找出所建立的新伺服器。Once the restore finishes, locate the new server that was created. 請確認資料已如預期般還原。Verify the data was restored as expected.

後續步驟Next steps

在本教學課程中,您已了解如何使用 Azure CLI (命令列介面) 及其他公用程式來:In this tutorial, you learned how to use Azure CLI (command-line interface) and other utilities to:

  • 建立適用於 PostgreSQL 的 Azure 資料庫伺服器Create an Azure Database for PostgreSQL server
  • 設定伺服器防火牆Configure the server firewall
  • 使用 psql 公用程式來建立資料庫Use psql utility to create a database
  • 載入範例資料Load sample data
  • 查詢資料Query data
  • 更新資料Update data
  • 還原資料Restore data

接著,若要了解如何使用 Azure 入口網站來執行類似的工作,請檢閱此教學課程:使用 Azure 入口網站來設計您第一個適用於 PostgreSQL 的 Azure 資料庫Next, learn how to use the Azure portal to do similar tasks, review this tutorial: Design your first Azure Database for PostgreSQL using the Azure portal