教程:使用 Azure CLI 设计 Azure Database for PostgreSQL - 单个服务器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:

  • 创建 Azure Database for PostgreSQL 服务器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 CLI 以运行本教程中的命令。You may install Azure CLI on your own computer to run the commands in this tutorial.

如果选择在本地安装并使用 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. 以下示例在 chinaeast2 位置创建名为 myresourcegroup 的资源组。The following example creates a resource group named myresourcegroup in the chinaeast2 location.

az group create --name myresourcegroup --location chinaeast2

创建 Azure Database for PostgreSQL 服务器Create an Azure Database for PostgreSQL server

使用 az postgres server create 命令创建 Azure Database for PostgreSQL 服务器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. 它是一台常规用途第 5 代服务器,具有 2 个 vCore。It is a General Purpose, Gen 5 server with 2 vCores.

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

sku-name 参数值遵循 {定价层}_{计算层代}_{vCore 数} 约定,如以下示例中所示: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 个 vCore。--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.

Important

此处指定的服务器管理员登录名和密码是以后在本快速入门中登录到服务器及其数据库所必需的。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.

Note

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 数据库服务器。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. 记下 administratorLogin 和 fullyQualifiedDomainName 。Make a note of the administratorLogin and fullyQualifiedDomainName.

{
  "administratorLogin": "myadmin",
  "earliestRestoreDate": null,
  "fullyQualifiedDomainName": "mydemoserver.postgres.database.chinacloudapi.cn",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforPostgreSQL/servers/mydemoserver",
  "location": "chinaeast2",
  "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 连接到 Azure Database for PostgreSQL 数据库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 命令连接到 Azure Database for PostgreSQL 数据库: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.chinacloudapi.cn 上名为“postgres” 的默认数据库。For example, the following command connects to the default database called postgres on your PostgreSQL server mydemoserver.postgres.database.chinacloudapi.cn using access credentials. 提示输入密码时,输入之前选择的 <server_admin_password>Enter the <server_admin_password> you chose when prompted for password.

    psql --host=mydemoserver.postgres.database.chinacloudapi.cn --port=5432 --username=myadmin@mydemoserver --dbname=postgres
    

    Tip

    如果更喜欢使用 URL 路径连接到 Postgres,则 URL 会使用 %40 对用户名中的 @ 符号进行编码。If you prefer to use a URL path to connect to Postgres, URL encode the @ sign in the username with %40. 例如,psql 的连接字符串将是:For example the connection string for psql would be,

    psql postgresql://myadmin%40mydemoserver@mydemoserver.postgres.database.chinacloudapi.cn:5432/postgres
    
  2. 连接到服务器后,在出现提示时创建空数据库:Once you are connected to the server, create a blank database at the prompt:

    CREATE DATABASE mypgsqldb;
    
  3. 出现提示时,请执行以下命令,将连接切换到新建的数据库 mypgsqldb :At the prompt, execute the following command to switch connection to the newly created database mypgsqldb:

    \c mypgsqldb
    

在数据库中创建表Create tables in the database

现已介绍了如何连接 Azure Database for PostgreSQL,接下来你可以完成一些基本任务: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. 使用 Azure Database for PostgreSQL 可以返回到服务器有其备份的任何时间点(由所配置的备份保留期确定),并可将此时间点还原到新服务器。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 要从其还原的源服务器的名称或 ID。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:

  • 创建 Azure Database for PostgreSQL 服务器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 门户设计第一个 Azure Database for PostgreSQLNext, 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