学习PostgreSQL：管理PostgreSQL数据库

在这篇文章中，我们将学习如何管理PostgreSQL数据库。我将涵盖以下主题。

1. 创建新的数据库
2. 改变一个现有的数据库
3. 删除一个现有的数据库

我已经在我的工作站上安装了PostgreSQL。了解在windows 10上逐步安装PostgreSQL的过程。我们将学习如何使用pgadmin4管理工具和psql命令行工具来管理数据库。

我们可以使用CREATE DATABASE语句来创建一个新的数据库。首先，让我们了解CREATE DATABASE语句的语法。

创建DATABASE语句

CREATE DATABASE的语法如下。

 CREATE DATABASE db_name
  WITH
     [OWNER =  db_owner_name]
     [TEMPLATE = template_db_name]
     [TABLESPACE = tablespace_name]
     [ALLOW_CONNECTIONS = true | false]
     [CONNECTION LIMIT = max_allowed_connection]
     [IS_TEMPLATE = true | false ]

在语法上，指定参数值如下。

1. db_name: 指定所需的数据库名称。db_name必须在CREATE DATABASE语句后指定。
2. OWNER：指定你想成为数据库所有者的用户名。如果我们不指定OWNER参数的值，数据库的所有者将是执行该语句的角色。
3. TEMPLATE：指定用于创建新数据库的模板数据库的名称。如果你不指定该参数的值，PostgreSQL会使用 模板1数据库。
4. TABLESPACE：指定你要创建数据库的表空间的名称。表空间是一个存储数据库的位置。如果你不指定表空间的名称，PostgreSQL将在pg_default表空间创建一个数据库。
5. ALLOW_CONNECTION：这个参数允许我们限制对数据库的访问。当我们将该值设置为FALSE时，用户不能连接到数据库。
6. 连接限制：你可以指定进入数据库的并发连接的数量。默认值是-1，是无限的。
7. IS_TEMPLATE：这个参数在你想克隆数据库的时候使用。如果该参数的值被设置为TRUE，那么任何用户都可以克隆数据库。如果该参数的值被设置为FALSE，那么只有数据库所有者或超级用户可以克隆它。

现在，让我们使用pgAdmin4创建一个新的数据库。当我们安装PostgreSQL时，pgAdmin4会自动安装。启动pgAdmin4🡪 指定主密码以连接到PostgreSQL。

一旦连接成功，你可以在 pgAdmin4 的浏览器窗格中访问对象和数据库。

要创建一个数据库，右击数据库，悬停在新建上并选择数据库。

一个名为 "创建数据库 "的对话框打开。该对话框有不同的标签，你可以在其中指定数据库的配置参数。我们正在创建一个名为EltechSales的数据库，所以在常规选项卡中，输入EltechSales作为数据库名称。我已经创建了一个名为Nisarg的用户。名为nisarg的PostgreSQL用户将是数据库的所有者，所以从用户下拉框中选择nisarg。

在定义标签中，你可以指定编码、表空间、数据库整理、字符类型和连接限制。对于EltechSales数据库，我设置了上述参数的以下值。

1. 编码：UTF8
2. 模板：无
3. 表空间：pg_admin。
4. 整理：C.
5. 字符类型：C.
6. 连接限制：-1

屏幕截图

在安全标签、参数标签和高级标签中，我们可以配置高级配置参数。我们将在我即将发表的文章中进一步了解它们。

如果你想看CREATE DATABASE语句，点击SQL标签。pgAdmin4会生成定义来创建一个PostgreSQL数据库。

点击 "保存"按钮来创建数据库。一旦数据库被创建，你可以在pgAdmin4的数据库部分查看它。请看下面的图片。

另外，你也可以运行下面的查询来查看数据库的列表。

postgres=# select datname from pg_database;

查询输出

另外，在pSQL命令行工具上运行下面的命令。

postgres=# \l

命令输出。

现在，让我们使用pSQL工具创建一个数据库。

使用pSQL创建数据库

我们可以使用CREATE DATABASE语句来创建一个新的数据库。我们将使用pSQL命令创建一个名为EltechHR的数据库。首先，启动pSQL并连接到PostgreSQL数据库服务器。

运行下面的命令来创建一个数据库。我没有指定任何配置，所以PostgreSQL数据库将使用一个名为template1的模板数据库。我没有指定数据库的所有者；因此，数据库的所有者将是Postgres。

postgres=# CREATE DATABASE EltechHR;

输出

正如你所看到的，数据库已经被成功创建。

现在，让我们来理解ALTER DATABASE语句。

ALTER DATABASE语句

我们可以使用ALTER DATABASE语句来执行以下任务

1. 重命名数据库。
2. 改变数据库的所有者。
3. 改变数据库的默认表空间。
4. 设置模板数据库。

首先，让我们重命名数据库。

重命名数据库

我们可以使用ALTER DATABASE RENAME TO语句来重命名数据库。语法如下。

ALTER DATABASE [old_db_name] RENAME TO [new_db_name];

在这个语法中。

1. old_db_name：指定旧的数据库名称。请确保该数据库在PostgreSQL上存在。
2. new_db_name：指定数据库的新名称。

假设我们想把EltechHR数据库重命名为EltechHRDB。运行下面的命令。

postgres=# ALTER DATABASE EltechHR RENAME TO EltechHRDB;

查询输出

一旦数据库被重新命名，运行下面的命令来验证数据库的名字是否已经被改变。

postgres=# \l

输出结果

正如你所看到的，数据库已经被成功重命名。

改变数据库的所有者

我们可以使用ALTER DATABASE OWNER TO语句来改变数据库的所有者。语法如下。

ALTER DATABASE [db_name] OWNER TO [owner_name];

在这个语法中。

1. db_name：指定你想改变其所有者的数据库名称。
2. owner_name：指定你想作为数据库所有者的用户名。请确保你在命令中指定的用户名必须存在于PostgreSQL中。

例如，我们想把UKLandRegistery数据库的所有者从Postgres改为nisarg。运行下面的命令。

postgres=# ALTER DATABASE “UKLandRegistery” OWNER TO nisarg;

查询输出

一旦查询被成功执行，让我们来验证所有者是否已经被改变。

查询

postgres=# \l

输出

正如你所看到的，所有者已被改变。

改变数据库的默认表空间。

我们可以使用ALTER DATABASE SET TABLESPACE语句来改变数据库的默认表空间。在将数据库移动到新的表空间之前，确保新的表空间必须是空的。另外，当你改变表空间时，数据库将无法访问。语法如下。

ALTER DATABASE [db_name] SET TABLESPACE [tablespace_name];

在这个语法中。

1. db_name：指定数据库的名称。
2. Tablespace_name：指定你要移动数据库的表空间名称。

为了演示，我创建了一个名为tblspaceEltechSales的表空间。假设你想把EltechSales数据库移到tblspaceEltechSales数据库中。运行下面的查询。

postgres=#ALTER DATABASE “EltechSales” SET TABLESPACE “tblspaceEltechSales”;

查询输出

一旦查询成功执行，让我们验证一下数据库是否已经被转移到新的表空间。

查询

postgres=# \l+ “EltechSales”

输出结果

正如你所看到的，EltechSales数据库已经被转移到tblspaceEltechSales。

设置模板数据库。

我们可以使用ALTER DATABASE WITH语句来设置一个模板数据库。有时你想创建一个空表的模板或演示数据库，并将其作为模板数据库使用，你可以使用这个选项。语法如下。

ALTER DATABASE [db_name] WITH IS_TEMPLATE = true;

在这个语法中。

1. db_name：指定你想作为模板数据库的数据库名称。

假设我们想使用EltechHR作为模板数据库。要做到这一点，运行下面的查询。

postgres=# ALTER DATABASE “EltechSales” WITH IS_TEMPLATE=true;

一旦查询被执行，运行下面的查询来查看EltechHR数据库是否被设置为模板数据库。

postgres=# select datname, datistemplate from pg_database;

查询输出查询输出

正如你所看到的，EltechHR这一栏的值为 datistemplate列的值是true，这表明该数据库是一个模板数据库。

删除数据库

我们可以使用DROP DATABASE语句来从PostgreSQL中删除数据库。其语法如下。

DROP DATABASE db_name

在这个语法中。

1. db_name：指定你要删除的数据库的名称。

假设你想放弃名为EltechHRDB的数据库。要做到这一点，运行下面的查询。

一旦查询被执行，让我们验证一下数据库是否已经被成功删除。

查询

postgres=# select datname, datistemplate from pg_database where datname=’Eltechhrdb’;

输出结果

该查询没有返回任何记录，这表明数据库已经被成功删除。

总结

这篇文章告诉我们如何管理PostgreSQL数据库。