玩转PostgreSQL（一）：学习如何创建新数据库

本文中，我们将学习如何使用PostgreSQL创建数据库语句以在PostgreSQL数据库服务器中创建新数据库。

? PostgreSQL CREATE DATABASE语句简介

CREATE DATABASE 语句允许我们创建新的PostgreSQL数据库。

以下显示声明CREATE DATABASE的语法:

CREATE DATABASE database_name
WITH
   [OWNER =  role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false ]

执行CREATE DATABASE语句我们需要具有超级用户角色或特殊角色CREATE DATABASE特权。

创建新数据库:

• 首先，指定CREATE DATABASE关键词。数据库名称在PostgreSQL数据库服务器中必须是唯一的。如果我们尝试创建名称已经存在的数据库，PostgreSQL将发出错误。
• 然后，为新数据库指定一个或多个参数。

?参数

OWNER 所有者

给创建的数据库分配一个角色，这将是数据库的所有者。如果你省略了OWNER选项，数据库的所有者是执行CREATE DATABASE时的角色。

TEMPLATE 模板

默认情况下，PostgreSQL使用template指定从中创建新数据库的模板数据库。如果未明确指定模板数据库，则将默认数据库作为模板数据库.

ENCODING 字符集编码

确定新数据库中的字符集编码。

LC_COLLATE 排序规则

指定排序规则顺序 (LC_COLLATE)，新数据库将使用该排序规则。此参数影响的排序顺序字符串查询包含Order By模板数据库。

LC_CTYPE 语言符号及其分类

指定新数据库将使用的字符分类。 它影响字符的分类，例如大写, 小写, 和数字. 它默认为模板数据库的LC_CTYPE

TABLESPACE 表空间

指定新数据库TABLESPACE的名称。默认值为模板数据库的表空间。

CONNECTION LIMIT 最大连接数

指定到新数据库的最大并发连接。默认值为-1，即无限制。此参数在共享托管环境中非常有用，我们可以在其中配置特定数据库的最大并发连接。

ALLOW_CONNECTIONS 是否允许连接

参数allow_connections的数据类型是布尔值。如果是false,我们无法连接到数据库。

IS_TEMPLATE 是否为模板

如果IS_TEMPLATE是真的，任何角色的CREATE DATABASE都可以克隆它。如果为false，则只有超级用户或数据库所有者可以克隆它。

?PostgreSQL创建数据库示例

1) 使用默认参数创建数据库

首先，使用任何客户端工具登录到PostgreSQL。

然后，使用默认参数对新数据库执行以下语句:

CREATE DATABASE sales;

PostgreSQL创建了一个名为sales具有来自默认模板数据库的默认参数 (template1)。

第三，如果你使用psql客户端工具，可以使用l命令查看数据库服务器上的所有数据库PostgreSQL:

l

2) 创建一个带有一些参数的数据库

以下示例使用CREATE DATABASE语句以创建创建一个名为hr带有一些参数的数据库:

CREATE DATABASE hr 
WITH 
   ENCODING = 'UTF8'
   OWNER = hr
   CONNECTION LIMIT = 100;

在此示例中，我们创建了hr编码为UTF8的数据库，所有者为hr，数据库的并发连接数为100。

3) 使用pgAdmin创建新数据库

pgAdmin工具为我们提供了创建新数据库的直观界面。

首先，使用pgAdmin登录到PostgreSQL数据库服务器。

第二，右键单击 "Databases" 节点，然后选择"Create"->"Database..." 菜单项

它将显示一个对话框，供我们输入有关新数据库的详细信息。

第三，输入数据库的名称，然后在 "General" 选项卡中选择一个所有者。

在此示例中，我们输入sampledb作为新数据库的名称，选择postgres作为所有者。

第四，选择Definition选项卡设置数据库的属性:

在Definition选项卡，我们可以选择编码、选择模板、表空间、排序规则、字符类型和连接限制。

Security选项卡允许我们定义安全标签并分配特权。Security选项卡允许我们为角色分配特权。

第五，单击 SQL选项卡以查看将执行的生成的SQL语句。

·

最后，点击Save按钮来创建sampledb数据库。你会看到sampledb在数据库列表中列出:

?PostgreSQL ALTER DATABASE 语句简介

ALTER DATABASE的允许我们对数据库执行以下操作:

• 更改数据库的属性
• 重命名数据库
• 更改数据库的所有者
• 更改数据库的默认表空间
• 更改数据库运行时配置变量的会话默认值

1) 更改数据库的属性

要更改数据库的属性，请使用以下形式的ALTER DATABASE声明:

 ALTER DATABASE name WITH option;

选项可以是:

• IS_TEMPLATE
• CONNECTION LIMIT
• ALLOW_CONNECTIONS

⛔ 注意，只有超级用户或数据库所有者可以更改这些设置。

2) 重命名数据库

以下ALTER DATABASE rename语句重命名数据库:

 ALTER DATABASE database_name
 RENAME TO new_name;

如果你在当前数据库执行语句，无法重命名当前数据库。因此，我们需要连接到另一个数据库并从该数据库重命名它。

只有具有CREATEDB特权的超级用户和数据库所有者才能重命名数据库。

3) 更改数据库的所有者

以下ALTER DATABASE语句将数据库的所有者更改为新的:

 ALTER DATABASE database_name
 OWNER TO new_owner | current_user | session_user;

⛔以下用户可以更改数据库的onwer:

• 数据库所有者，是新create database拥有角色的直接或间接成员。
• 超级管理员

4) 更改数据库的默认表空间

以下语句更改数据库表空间的默认值:

 ALTER DATABASE database_name
 SET TABLESPACE new_tablespace;

该语句将表和索引从旧表空间物理移动到新表空间。

要设置新的表空间，表空间需要为空，并且与数据库有连接。

⛔注意：超级管理员和数据库所有者可以更改数据库的默认表空间

5) 更改运行时配置变量的默认值

每当我们连接到数据库时，PostgreSQL都会从postgresql.conf文件读取配置变量，并在默认情况下使用这些变量。

要覆盖特定数据库的这些设置，请使用ALTER DATABASE SET声明如下:

 ALTER DATABASE database_name
 SET configuration_parameter = value;

在后续会话中，PostgreSQL将覆盖postgresql.conf文件。

⛔注意：只有超级用户或数据库所有者才能更改数据库运行时配置的会话默认值。

?PostgreSQL ALTER DATABASE 示例

首先，登录到PostgreSQL，为了演示，利用postgres用户和CREATE DATABASE命名testdb2。

 CREATE DATABASE testdb2;

第二，重命名testdb2到testhrdb使用以下语句:

 ALTER DATABASE testdb2 
 RENAME TO testhrdb;

第三，执行以下语句以更改testhrdb数据库从postgres到hr,假设hrrole 已存在。

 ALTER DATABASE testhrdb 
 OWNER TO hr;

如果hrrole 不存在，我们可以使用CREATE ROLE声明:

 CREATE ROLE hr
 LOGIN 
 CREATEDB
 PASSWORD 'securePa$$1';

第四，更改的默认表空间testhrdb从pg_default到hr_default,假设hr_default表空间已存在。

 ALTER DATABASE testhrdb
 SET TABLESPACE hr_default;

如果hr_default表空间不存在，我们可以使用以下语句创建它:

 CREATE TABLESPACE hr_default
 OWNER hr
 LOCATION 'C:sampledbhr';

第五，设置escape_string_warning配置变量为off通过使用以下语句:

 ALTER DATABASE testhrdb 
 SET escape_string_warning = off;

?PostgreSQL rename database 步骤

要重命名PostgreSQL数据库，请使用以下步骤:

让我们看一下重命名数据库的示例。

以下语句创建一个名为db:

 CREATE DATABASE db;

重命名db数据库到newdb,我们可以按照以下步骤操作:

首先，断开与要重命名的数据库的连接，然后连接到另一个数据库，例如，postgres。如果使用psql工具，则可以使用以下命令连接到postgres数据库:

 db=# connect postgres

通过连接到postgres数据库，我们将自动与db数据库连接。

接下来，通过使用以下查询 ，检查db数据库所有活动连接:

 SELECT  *
 FROM pg_stat_activity
 WHERE datname = 'db';

查询返回以下输出:

 -[ RECORD 1 ]----+------------------------------
 datid            | 35918
 datname          | db
 pid              | 6904
 usesysid         | 10
 usename          | postgres
 application_name | psql
 client_addr      | ::1
 client_hostname  |
 client_port      | 56412
 backend_start    | 2022-10-20 08:25:05.083705+07
 xact_start       |
 query_start      |
 state_change     | 2022-10-20 08:25:05.092168+07
 waiting          | f
 state            | idle
 backend_xid      |
 backend_xmin     |
 query            |

从输出中可以清楚地看到，只有一个连接到db数据库。

我们可能会发现要重命名的数据库具有许多活动连接。在这种情况下，我们需要在终止连接之前通知相应的用户以及应用程序所有者，以避免数据丢失。

然后，终止与db连接，使用以下语句:

 SELECT
     pg_terminate_backend (pid)
 FROM
     pg_stat_activity
 WHERE
     datname = 'db';

之后，重命名db数据库到newdb使用ALTER DATABASE ... RENAME声明如下:

 ALTER DATABASE db RENAME TO newdb;

最后但并非最不重要的一点是，如果应用程序正在使用数据库，则应修改连接字符串。

在本文中，我们已经学习了如何使用ALTER DATABASE ... RENAME声明。

?PostgreSQL DROP DATABASE 语句简介

一旦不再需要数据库，我们可以使用DROP DATABASE声明。

以下说明的语法DROP DATABASE声明:

 DROP DATABASE [IF EXISTS] database_name;

? Note:

• DROP DATABASE删除指定数据库。
• 使用如果存在防止错误删除不存在的数据库。PostgreSQL将发出通知。

DROP DATABASE 永久删除目录条目和数据目录。此操作无法撤消，因此我们必须谨慎使用。

只有超级用户和数据库所有者可以执行DROP DATABASE声明。此外，如果数据库仍然具有活动连接,我们不能执行DROP DATABASE语句。在这种情况下，我们需要断开与数据库的连接并连接到另一个数据库，例如，postgres执行DROP DATABASE声明。

PostgreSQL还提供了一个名为dropdb这允许我们删除数据库。

1) 删除具有活动连接的数据库

要删除具有活动连接的数据库，可以按照以下步骤操作:

首先，找到活动数据库查询pg_stat_activity视图:

 SELECT *
 FROM pg_stat_activity
 WHERE datname = '';

第二，通过发出以下查询来终止活动连接:

 SELECT  pg_terminate_backend (pid)
 FROM    pg_stat_activity
 WHERE   pg_stat_activity.datname = '';

请注意，如果我们使用PostgreSQL 9.1或更早版本，请使用procpid列而不是pid列，因为从9.2版开始PostgreSQL已更改procid至pid

第三，执行DROP DATABASE声明:

 DROP DATABASE ;

?PostgreSQL DROP DATABASE示例

如果我们尚未创建此数据库，可以使用以下CREATE DATABASE的语句创建它们:

 CREATE DATABASE hrdb;
 CREATE DATABASE testdb1;

1) 删除没有活动连接的数据库示例

删除hrdb数据库，使用hrdb所有者连接到数据库，而不是hrdb数据库 。例如，postgres并发布以下声明:

 DROP DATABASE hrdb;

PostgreSQL删除了hrdb数据库。

2) 删除具有活动连接的数据库示例

以下语句删除testdb1数据库:

 DROP DATABASE testdb1;

但是，PostgreSQL发出了如下错误:

 ERROR: database "testdb1" is being accessed by other users
 SQL state: 55006
 Detail: There is 1 other session using the database.

testdb1数据库，我们需要终止连接并删除数据库。

首先，查询pg_stat_activity连接到testdb1数据库的进程:

 SELECT *
 FROM pg_stat_activity
 WHERE datname = 'testdb1';

testdb1数据库有一个来自localhost因此，终止此连接并删除数据库是安全的。

第二，使用以下语句 终止与testdb1数据库的访问:

 SELECT
     pg_terminate_backend (pg_stat_activity.pid)
 FROM
     pg_stat_activity
 WHERE
     pg_stat_activity.datname = 'testdb1';

第三，运行DROP DATABASE命令删除testdb1数据库:

 DROP DATABASE testdb1;

PostgreSQL永久地删除testdb1。

?PostgreSQL复制同一服务器中的数据库

有时，我们希望在数据库服务器中复制PostgreSQL数据库以进行测试。

PostgreSQL使用 CREATE DATABASE，可以很容易实现这个操作:

 CREATE DATABASE targetdb 
 WITH TEMPLATE sourcedb;

此语句复制sourcedb到targetdb。例如，复制dvdrental到dvdrental_test数据库，我们使用以下语句:

 CREATE DATABASE dvdrental_test 
 WITH TEMPLATE dvdrental;

根据源数据库的大小,可能需要一段时间才能完成复制。

如果dvdrental数据库具有活动连接，我们将收到以下错误:

 ERROR:  source database "dvdrental" is being accessed by other users
 DETAIL:  There is 1 other session using the database.

以下查询返回活跃的连接:

 SELECT pid, usename, client_addr 
 FROM pg_stat_activity 
 WHERE datname ='dvdrental';

终止dvdrental数据库活跃连接，我们使用以下查询:

 SELECT pg_terminate_backend (pid)
 FROM pg_stat_activity
 WHERE datname = 'dvdrental';

之后，我们可以执行CREATE TABLE WITH TEMPLATE再次执行语句，将dvdrental数据库复制到dvdrental_test数据库。

?PostgreSQL将数据库从一台服务器复制到另一台服务器

在PostgreSQL数据库服务器之间复制数据库有几种方法。

如果源数据库很大，并且数据库服务器之间的连接很慢，我们可以将源数据库转储到文件中，将文件复制到远程服务器，并恢复它:

首先，将源数据库转储到文件中。

 pg_dump -U postgres -d sourcedb -f sourcedb.sql

第二，将转储文件复制到远程服务器。

第三，在远程服务器中创建新数据库:

 CREATE DATABASE targetdb;ql)

最后，还原远程服务器上的转储文件:

 psql -U postgres -d targetdb -f sourcedb.sql

示例：复制dvdrental数据库

以下步骤说明如何从本地服务器到远程服务器复制dvdrental数据库。

首先，将dvdrental数据库到转储文件中，例如，dvdrental.sql:

 pg_dump -U postgres -O dvdrental -f dvdrental.sql

第二，将转储文件复制到远程服务器

第三，在远程服务器创建dvdrental数据库

 CREATE DATABASE dvdrental;

第四，在远程服务器恢复dvdrental.sql转储文件:

 psql -U postgres -d dvdrental -f dvdrental.sql

如果服务器之间的连接速度很快并且数据库大小不大，可以使用以下命令:

 pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb

例如，复制dvdrental数据库来自本地主机到远程服务器，我们执行如下操作:

 pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental

在上面的示例中，我们已经学习了如何在数据库服务器中复制PostgreSQL数据库，或从数据库服务器复制到另一个数据库服务器。

?PostgreSQL 获取大小函数

?PostgreSQL table 大小

要获取特定表的大小，请使用pg_relation_size()功能。例如，我们可以获取user表如下所示:

 select pg_relation_size('user');

pg_relation_size()函数以字节为单位返回特定表的大小:

 pg_relation_size
 ------------------
             16384

为了使结果更易于阅读，我们可以使用pg_size_pretty()功能。pg_size_pretty()函数获取另一个函数的结果，并根据需要使用字节、kB、MB、GB或TB对其进行格式化。例如:

 SELECT
     pg_size_pretty (pg_relation_size('user'));

以下是以kB为单位的输出

  pg_size_pretty
     ----------------
      16 kB
     (1 row)

pg_relation_size()函数仅返回表的大小，不包括索引或其他对象。

要获取表的总大小，请使用pg_total_relation_size()功能。例如，要获取user表的总大小，请使用以下语句:

 SELECT
     pg_size_pretty (
         pg_total_relation_size ('user')
     );

下面显示输出:

  pg_size_pretty
 ----------------
  72 kB
 (1 row)

我们可以使用pg_total_relation_size()函数查找最大表 (包括索引) 的大小。

例如，以下查询返回dvdrental数据库最大的5张表:

 SELECT
     relname AS "relation",
     pg_size_pretty (
         pg_total_relation_size (C .oid)
     ) AS "total_size"
 FROM
     pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
 WHERE
     nspname NOT IN (
         'pg_catalog',
         'information_schema'
     )
 AND C .relkind  'i'
 AND nspname !~ '^pg_toast'
 ORDER BY
     pg_total_relation_size (C .oid) DESC
 LIMIT 5;

Output

   relation  | total_size
 ------------+------------
  user     | 2472 kB
  post    | 2232 kB
  dept       | 688 kB
  company | 536 kB
  tenant  | 464 kB
 (5 rows)

?PostgreSQL database 大小

要获取整个数据库的大小，请使用pg_database_size()功能。例如，以下语句返回dvdrental数据库大小:

 SELECT
     pg_size_pretty (
         pg_database_size ('dvdrental')
     );

该语句返回以下结果:

 pg_size_pretty
 ----------------
  15 MB
 (1 row)

要获取当前数据库服务器中每个数据库的大小，请使用以下语句:

 SELECT
     pg_database.datname,
     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
     FROM pg_database;
     
 Output:
     datname     |  size
 ----------------+---------
  postgres       | 7055 kB
  template1      | 7055 kB
  template0      | 6945 kB
  dvdrental      | 15 MB

?PostgreSQL index 大小

要获取附加到表的所有索引的总大小，请使用pg_indexes_size()功能。

pg_indexes_size()函数接受OID或表名称作为参数，并返回该表附加的所有索引使用的总磁盘空间。

例如，要获取user表index 总大小，我们使用以下语句:

 SELECT
     pg_size_pretty (pg_indexes_size('user'));

Output:

  pg_size_pretty
 ----------------
  32 kB
 (1 row)

?PostgreSQL tablespace 大小

要获取表空间的大小，请使用pg_tablespace_size()功能。pg_tablespace_size()函数接受表空间名称并返回以字节为单位的大小。

以下语句返回pg_default表空间大小:

 SELECT
     pg_size_pretty (
         pg_tablespace_size ('pg_default')
     );

该语句返回以下输出:

  pg_size_pretty
 ----------------
  43 MB
 (1 row)

?PostgreSQL value 大小

要查找需要存储特定值的空间，请使用pg_column_size()函数，例如:

 select pg_column_size(5::smallint);
  pg_column_size
 ----------------
               2
 (1 row)
 ​
 ​
 select pg_column_size(5::int);
  pg_column_size
 ----------------
               4
 (1 row)
 ​
 ​
 select pg_column_size(5::bigint);
  pg_column_size
 ----------------
               8
 (1 row)

在上面的示例中，我们学习了各种方便的函数来获取数据库、表、索引、表空间和值的大小。