教程:使用 Azure 门户设计 Azure Database for PostgreSQL - 单个服务器Tutorial: Design an Azure Database for PostgreSQL - Single Server using the Azure portal

Azure Database for PostgreSQL 数据库是一种托管服务,可用于在云中运行、管理和缩放具有高可用性的 PostgreSQL 数据库。Azure Database for PostgreSQL is a managed service that enables you to run, manage, and scale highly available PostgreSQL databases in the cloud. 使用 Azure 门户可以轻松管理服务器和设计数据库。Using the Azure portal, you can easily manage your server and design a database.

本教程介绍如何使用 Azure 门户完成以下操作:In this tutorial, you use the Azure portal 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

先决条件Prerequisites

如果没有 Azure 订阅,请在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

创建用于 PostgreSQL 的 Azure 数据库Create an Azure Database for PostgreSQL

创建的 Azure Database for PostgreSQL 服务器中包含一组已定义的计算和存储资源。An Azure Database for PostgreSQL server is created with a defined set of compute and storage resources. 将在 Azure 资源组中创建服务器。The server is created within an Azure resource group.

可以按照以下步骤创建用于 PostgreSQL 的 Azure 数据库:Follow these steps to create an Azure Database for PostgreSQL server:

  1. 在 Azure 门户的左上角单击“创建资源”。 Click Create a resource in the upper left-hand corner of the Azure portal.

  2. 从“新建”页中选择“数据库”,并从“数据库”页中选择“Azure Database for PostgreSQL”。 Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page. 用于 PostgreSQL 的 Azure 数据库 - 创建数据库Azure Database for PostgreSQL - Create the database

  3. 选择“单个服务器”部署选项 。Select the Single server deployment option.

    选择 Azure Database for PostgreSQL - 单个服务器部署选项

  4. 填写“基本”表单,其中包含以下信息 :Fill out the Basics form with the following information:

    创建服务器

    设置Setting 建议的值Suggested Value 说明Description
    订阅Subscription 订阅名称Your subscription name 要用于服务器的 Azure 订阅。The Azure subscription that you want to use for your server. 如果有多个订阅,请选择要计费的资源所在的订阅。If you have multiple subscriptions, choose the subscription in which you're billed for the resource.
    资源组Resource group myresourcegroupmyresourcegroup 新的资源组名称,或订阅中的现有资源组。A new resource group name or an existing one from your subscription.
    服务器名称Server name mydemoservermydemoserver 用于标识用于 PostgreSQL 的 Azure 数据库服务器的唯一名称。A unique name that identifies your Azure Database for PostgreSQL server. 域名 postgres.database.chinacloudapi.cn 将追加到所提供的服务器名称后面。The domain name postgres.database.chinacloudapi.cn is appended to the server name you provide. 服务器名称只能包含小写字母、数字和连字符 (-) 字符。The server can contain only lowercase letters, numbers, and the hyphen (-) character. 该名称必须至少包含 3 到 63 个字符。It must contain at least 3 through 63 characters.
    数据源Data source None 选择“无”,从头开始创建新的服务器 。Select None to create a new server from scratch. (如果是从现有 Azure Database for PostgreSQL 服务器的异地备份创建服务器,则会选择“备份” )。(You would select Backup if you were creating a server from a geo-backup of an existing Azure Database for PostgreSQL server).
    管理员用户名Admin username myadminmyadmin 连接到服务器时使用的自己的登录帐户。Your own login account to use when you connect to the server. 管理员登录名不能是 azure_superuserazure_pg_adminadminadministratorrootguestpublicThe admin login name can't be azure_superuser, azure_pg_admin, admin, administrator, root, guest, or public. 不能以 pg_ 开头。It can't start with pg_.
    密码Password 你的密码Your password 服务器管理员帐户的新密码。A new password for the server admin account. 该密码必须包含 8 到 128 个字符。It must contain between 8 and 128 characters. 密码必须包含以下三个类别的字符:英文大写字母、英文小写字母、数字 (0 到 9)和非字母数字字符(!, $, #, % 等)。Your password must contain characters from three of the following categories: English uppercase letters, English lowercase letters, numbers (0 through 9), and non-alphanumeric characters (!, $, #, %, etc.).
    位置Location 离用户最近的区域The region closest to your users 最靠近用户的位置。The location that is closest to your users.
    版本Version 最新主版本The latest major version 除非另有特定的要求,否则为最新 PostgreSQL 主版本。The latest PostgreSQL major version, unless you have specific requirements otherwise.
    计算 + 存储Compute + storage 常规用途第 5 代2 vCore5 GB7 天异地冗余General Purpose, Gen 5, 2 vCores, 5 GB, 7 days, Geographically Redundant 新服务器的计算、存储和备份配置。The compute, storage, and backup configurations for your new server. 选择“配置服务器” 。Select Configure server. 接下来,选择“常规用途” 选项卡。“第 5 代”、“4 个 vCore”、“100 GB”和“7 天”分别是“计算代系”、“vCore”、“存储”和“备份保持期”的默认值 。Next, select the General Purpose tab. Gen 5, 4 vCores, 100 GB, and 7 days are the default values for Compute Generation, vCore, Storage, and Backup Retention Period. 可以按原样保留这些滑块,也可以对其进行调整。You can leave those sliders as is or adjust them. 若要在异地冗余存储中启用服务器备份,请从“备份冗余选项” 中选择“异地冗余” 。To enable your server backups in geo-redundant storage select Geographically Redundant from the Backup Redundancy Options. 若要保存此定价层选择,请选择“确定” 。To save this pricing tier selection, select OK. 下一个屏幕截图捕获了这些选择。The next screenshot captures these selections.

    Note

    如果轻量级计算和 I/O 足以满足工作负荷要求,请考虑使用“基本”定价层。Consider using the Basic pricing tier if light compute and I/O are adequate for your workload. 请注意,在“基本”定价层中创建的服务器以后不能扩展到“常规用途”或“内存优化”定价层。Note that servers created in the Basic pricing tier cannot later be scaled to General Purpose or Memory Optimized. 有关详细信息,请参阅定价页See the pricing page for more information.

    “定价层”窗格

    Tip

    启用“自动增长” 后,当接近分配的限制时,服务器会增加存储空间,而不会影响工作负荷。With auto-growth enabled your server increases storage when you are approaching the allocated limit, without impacting your workload.

  5. 选择“查看 + 创建”,查看所选内容 。Select Review + create to review your selections. 选择“创建” 以预配服务器。Select Create to provision the server. 此操作可能需要几分钟的时间。This operation may take a few minutes.

  6. 在工具栏上选择“通知”图标(铃铛)以监视部署过程。 On the toolbar, select the Notifications icon (a bell) to monitor the deployment process. 完成部署后,可以选择“固定到仪表板” ,以便在 Azure 门户仪表板上为此服务器创建磁贴作为到此服务器“概述” 页的快捷方式。Once the deployment is done, you can select Pin to dashboard, which creates a tile for this server on your Azure portal dashboard as a shortcut to the server's Overview page. 选择“转到资源” 可打开此服务器的“概述” 页。Selecting Go to resource opens the server's Overview page.

    “通知”窗格

    默认情况下,会在服务器下创建 postgres 数据库。By default, a postgres database is created under your server. postgres 是供用户、实用工具和第三方应用程序使用的默认数据库。The postgres database is a default database that's meant for use by users, utilities, and third-party applications. (另一个默认数据库是 azure_maintenance(The other default database is azure_maintenance. 其功能是将托管服务进程与用户操作分开。Its function is to separate the managed service processes from user actions. 你不能访问此数据库。)You cannot access this database.)

配置服务器级防火墙规则Configure a server-level firewall rule

Azure Database for PostgreSQL 服务在服务器级别使用防火墙。The Azure Database for PostgreSQL service uses a firewall at the server-level. 默认情况下,除非创建了防火墙规则来为特定 IP 地址范围打开防火墙,否则此防火墙会阻止所有外部应用程序和工具连接到服务器和服务器上的任何数据库。By default, this firewall prevents all external applications and tools from connecting to the server and any databases on the server unless a firewall rule is created to open the firewall for a specific IP address range.

  1. 部署完成后,请单击左侧菜单中的“所有资源”,并键入名称“mydemoserver”来搜索新创建的服务器 。After the deployment completes, click All Resources from the left-hand menu and type in the name mydemoserver to search for your newly created server. 单击搜索结果中列出的服务器名称。Click the server name listed in the search result. 服务器的“概述” 页面随即打开,其中提供了用于进一步配置的选项。The Overview page for your server opens and provides options for further configuration.

    用于 PostgreSQL 的 Azure 数据库 - 搜索服务器

  2. 在服务器页中,选择“连接安全性” 。In the server page, select Connection security.

  3. 单击“规则名称”下的文本框 ,并添加新的防火墙规则,以指定连接的 IP 范围。Click in the text box under Rule Name, and add a new firewall rule to specify the IP range for connectivity. 输入 IP 范围。Enter your IP range. 单击“保存” 。Click Save.

    用于 PostgreSQL 的 Azure 数据库 - 创建防火墙规则

  4. 单击“保存”,并单击“X”以关闭“连接安全性”页。 Click Save and then click the X to close the Connections security page.

    Note

    Azure PostgreSQL 服务器通过端口 5432 进行通信。Azure PostgreSQL server communicates over port 5432. 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 5432 的出站流量。If you are trying to connect from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. 若是如此,则无法连接到 Azure SQL 数据库服务器,除非 IT 部门启用了端口 5432。If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 5432.

获取连接信息Get the connection information

创建 Azure Database for PostgreSQL 服务器时,还会创建默认的 postgres 数据库 。When you created the Azure Database for PostgreSQL server, the default postgres database was also created. 若要连接到数据库服务器,需要提供主机信息和访问凭据。To connect to your database server, you need to provide host information and access credentials.

  1. 在 Azure 门户中的左侧菜单中,单击“所有资源” ,并搜索刚创建的服务器。From the left-hand menu in the Azure portal, click All resources and search for the server you just created.

    用于 PostgreSQL 的 Azure 数据库 - 搜索服务器

  2. 单击服务器名称 mydemoserverClick the server name mydemoserver.

  3. 选择服务器的“概述” 页面。Select the server's Overview page. 记下“服务器名称” 和“服务器管理员登录名” 。Make a note of the Server name and Server admin login name.

    用于 PostgreSQL 的 Azure 数据库 - 服务器管理员登录名

使用 psql 连接到 PostgreSQL 数据库Connect to PostgreSQL database using psql

如果客户端计算机已安装 PostgreSQL,则可以使用 psql 的本地实例。If your client computer has PostgreSQL installed, you can use a local instance of psql. 现在,使用 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. 使用此 SQL 代码创建一个跟踪库存信息的表:Let's create a table that tracks inventory information using this SQL code:

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 tables

表格创建好后,可向其插入一些数据。Now that you have a table, 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 two rows of sample data into the inventory table you created earlier.

查询和更新表中的数据Query and update the data in the tables

执行以下查询,从库存数据库表中检索信息。Execute the following query to retrieve information from the inventory database table.

SELECT * FROM inventory;

还可以更新表中的数据。You can also update the data in the table.

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

检索数据时,可以看到更新后的值。You can see the updated values when you retrieve the data.

SELECT * FROM inventory;

将数据还原到之前的时间点Restore data to a previous point in time

假设意外删除了此表。Imagine you have accidentally deleted this table. 这种情况无法轻易还原。This situation 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. 以下步骤将 mydemoserver 服务器还原到添加库存表之前的时间点。The following steps restore the mydemoserver server to a point before the inventory table was added.

  1. 在服务器的 Azure Database for PostgreSQL“概述”页中,单击工具栏上的“还原” 。On the Azure Database for PostgreSQL Overview page for your server, click Restore on the toolbar. 此时会打开“还原” 页。The Restore page opens.

    Azure 门户 - 还原窗体选项

  2. 使用必需信息填充“还原” 窗体:Fill out the Restore form with the required information:

    Azure 门户 - 还原窗体选项

    • 还原点:选择更改服务器前的时间点Restore point: Select a point-in-time that occurs before the server was changed
    • 目标服务器:提供一个要还原到的新服务器名称Target server: Provide a new server name you want to restore to
    • 位置:不能选择区域,此区域默认与源服务器相同Location: You cannot select the region, by default it is same as the source server
    • 定价层:还原服务器时不能更改此值。Pricing tier: You cannot change this value when restoring a server. 此值与源服务器相同。It is same as the source server.
  3. 单击“确定” ,将服务器还原到删除该表之前的时间点Click OK to restore the server to a point-in-time before the table was deleted. 将服务器还原到不同的时间点会基于原始服务器到指定时间点为止的内容创建一个新的副本服务器,前提是该时间点在定价层的保留期内。Restoring a server to a different point in time creates a duplicate new server as the original server as of the point in time you specify, provided that it is within the retention period for your pricing tier.

后续步骤Next steps

本教程介绍如何使用 Azure 门户和其他实用工具完成以下操作:In this tutorial, you learned how to use the Azure portal 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 CLI 执行类似任务,请查看此教程:使用 Azure CLI 设计第一个 Azure Database for PostgreSQLNext, to learn how to use the Azure CLI to do similar tasks, review this tutorial: Design your first Azure Database for PostgreSQL using Azure CLI