如何管理PostgreSQL的库?

PostgreSQL

1 基础知识

1.1 数据仓库的概念

– 数据库是SQL对象的命名集合,即数据对象
– 通常每个数据对象(表、函数等)都属于一个数据库
– 集群环境下,数据库目录pg_database属于整个集群即模式集合

1.2 连接的管理

– 客户端与服务器连接时,客户端必须连接请求中指定的数据库名称,每个连接不能访问多个数据库
– 应用程序可使用多个连接请求同时连接到不同的数据库,连接相互独立

1.3 表空间

1.3.1 表空间概念

Tablespaces(即表空间),
– PostgreSQL使用Tablespaces来管理数据库磁盘布局
– PostgreSQL的Tablespaces是的数据库可以分布在不同的磁盘分区

1.3.2 表空间用途

– 扩展分区,用于解决分区或卷空间不足
– 优化性能,数据分布与不同的物理存储设备

1.3.3 表空间的使用

– 可分配到特定的数据库
– 可分配到特定的数据表
– 可分配到特定的索引

2 最佳实践

2.1 系统环境

如果你是初次接触PostgreSQL,建议你先阅读此章节,了解基本的操作后在阅读后面的章节,
https://www.cmdschool.org/archives/3803

2.2 数据库的操作

2.2.1 列出数据库

\l

数据库显示如下,

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

2.2.2 创建数据库

create database mydb;

另外,更加方便的是可以使用linux shell创建数据库,

createdb mydb;

注:一般情况下,以上创建库的过程实际上是复制template1的模板库

2.2.3 切换数据库

\c mydb;

注:你将看到如下提示并发现提示符切换,

You are now connected to database "mydb" as user "postgres".
mydb=#

2.2.4 删除数据库

drop database mydb;

2.2.5 查看当前库的表

\d

由于我们还没有创建表,所以你会收到如下信息,

Did not find any relations.

2.3 表空间的操作

2.3.1 查看表空间

\db

命令显示如下,

       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

如果需要更加详细的信息,请使用“+”号

\db+

命令显示如下,

                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 31 MB  |
 pg_global  | postgres |          |                   |         | 573 kB |
(2 rows)

另外,我们也可以通过如下方法查询,

SELECT spcname FROM pg_tablespace;

2.3.2 创建新的表空间

创建表空间所需的目录,

mkdir /pgdata
chown postgres:postgres /pgdata/
chmod 700 /pgdata/

以特权身份进入psql的shell,

su - postgres
psql

创建表空间,

create tablespace pgdata location '/pgdata';

2.3.3 创建数据库到特定的表空间

删除已有并不需要的mydb库,

drop database mydb;

重新建立mydb库并指定到特定的表空间pgdata

create database mydb tablespace pgdata;

检验执行的结果,

\l+

可见如下显示,

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                D
escription
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7745 kB | pgdata     |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7877 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7745 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7745 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

2.3.4 创建数据表到特定的表空间

删除之前不需要的mydb库,同时占用的表空间pgdata也会释放,

drop database mydb;

重新创建mydb库后再创建foo表并指定到特定的表空间pgdata

create database mydb;
\c mydb;
create table foo (i int) tablespace pgdata;

检查执行的结果,

\d

命令显示如下,

       List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | foo  | table | postgres
(1 row)

如需方向操作删除表空间,可执行以下指令,

drop tablespace pgdata;

参阅文档
===========================

创建数据库
—————-
https://www.postgresql.org/docs/10/manage-ag-createdb.html

管理员使用教程
https://www.postgresql.org/docs/10/admin.html

数据库使用教程
—————-
https://www.postgresql.org/docs/10/index.html

没有评论

发表评论

PostgreSQL
如何使用yum部署PostgreSQL 9?

1 基础知识 1.1 PostgreSQL简介 PostgreSQL是一个功能强大的开源对象关系数据 …

PostgreSQL
如何管理PostgreSQL的角色?

1 基础知识 1.1 角色的概念 – PostgreSQL使用角色概念管理数据库访问权限 …

PostgreSQL
如何熟悉PostgreSQL的基本操作?

1 前言 以下是笔者初学PostgreSQL的笔记,此章节适合初学者熟悉PostgreSQL的基本操 …