持续创造，加快成长！这是我参与「日新计划 10 月更文挑战」的第23天，点击检查活动详情

---

简介:

往期精选文章，求赞求关注！

一文带你玩转Kubernetes

Proxy 署理的源码和原理解析

Spring + LDAP + JPA 实现业务管理器

容器服务Istio实践 Istio Sidecar 注入方式

Java EasyPoi之复杂多表头多sheet页excel模板导出实例

---

️ PG系列文章:

PG系列文章，想学习更多PG芝士，快来吧‍♂️！

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL （一）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL（二）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL （三）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL（四）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL✒️（五）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL（六）

[千字长文系列] 探秘PG之一文带你玩转PostgreSQL（七）

[千字长文系列] 探秘PG之一文带你玩转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:\sampledb\hr';

第五，设置escape_string_warning装备变量为off经过运用以下句子:

ALTER DATABASE testhrdb
SET escape_string_warning = off;

PostgreSQL rename database 过程

要重命名PostgreSQL数据库，请运用以下过程:

1. 断开与要重命名的数据库的衔接，然后衔接到其他数据库。
2. 检查并停止与要重命名的数据库的一切活动衔接。
3. 运用ALTER DATABASE句子将数据库重命名为新数据库。

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

以下句子创立一个名为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 = '<database_name>';

第二，经过发出以下查询来停止活动衔接:

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

请留意，假如咱们运用PostgreSQL 9.1或更早版本，请运用procpid列而不是pid列，由于从9.2版开始PostgreSQL已更改procid至pid

第三，履行DROP DATABASE声明:

DROP DATABASE <database_name>;

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)

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

未完待续..既然看到这里了，无妨来个大大的点赞吧