PostgreSQL 表空间与数据库

PostgreSQL中表空间的创建有点像Oracle在裸设备上创建表空间，也就是说它只有表空间的名称与数据库文件的目录，而没有具体的大小。在PostgeSQL中，它没有数据文件的概念，只是一个数据目录。

下面，我们来看例子。

postgres=# create tablespace ts_demo  location '/home/pgsqladmin/pgdata01';

CREATE TABLESPACE

postgres=#

创建语法非常简单。

我们可以在已有的表空间上创建表或是索引，如下：

postgres=# create table t(

postgres(# x int,

postgres(# y varchar(30),

postgres(# z date

postgres(# )

postgres-#  tablespace ts_demo

postgres-# ;

CREATE TABLE

postgres=# create index t_idx_x on t(x)  tablespace ts_demo;

CREATE INDEX

postgres=# 

与Oracle中相关对像的创建语法类似。

我们也可以在添加表的主键约束或是唯一键约束的时候指定表空间，如下：

postgres=# alter table t add constraint t_y_unique unique(y) using index tablespace ts_demo;

ALTER TABLE

postgres=# 

我们可以使用psql工具下的命令来查看表空间，如下：

postgres=#  \db

                 List of tablespaces

    Name    |   Owner    |         Location          

------------+------------+---------------------------

 pg_default | pgsqladmin | 

 pg_global  | pgsqladmin | 

  ts_demo    | pgsqladmin | /home/pgsqladmin/pgdata01

(3 rows)

postgres=#  alter tablespace ts_demo rename to ts_demo01;

ALTER TABLESPACE

postgres=# \db

                 List of tablespaces

    Name    |   Owner    |         Location          

------------+------------+---------------------------

 pg_default | pgsqladmin | 

 pg_global  | pgsqladmin | 

  ts_demo01  | pgsqladmin | /home/pgsqladmin/pgdata01

(3 rows)

postgres=# 

查看表T的相关变化：

postgres=# \d t

              Table "public.t"

 Column |         Type          | Modifiers 

--------+-----------------------+-----------

 x      | integer               | 

 y      | character varying(30) | 

 z      | date                  | 

Indexes:

    "t_y_unique" UNIQUE CONSTRAINT, btree (y), tablespace "ts_demo01"

    "t_idx_x" btree (x), tablespace "ts_demo01"

Tablespace: "ts_demo01"

postgres=# 

我们也可以把表进行在表空间上的移动，如下：

postgres=# create tablespace ts_demo02 location '/home/pgsqladmin/pgdata02';

CREATE TABLESPACE

postgres=# alter table t set tablespace ts_demo02;

ALTER TABLE

postgres=# \d t

              Table "public.t"

 Column |         Type          | Modifiers 

--------+-----------------------+-----------

 x      | integer               | 

 y      | character varying(30) | 

 z      | date                  | 

Indexes:

    "t_y_unique" UNIQUE CONSTRAINT, btree (y), tablespace "ts_demo01"

    "t_idx_x" btree (x), tablespace "ts_demo01"

Tablespace: "ts_demo02"

postgres=# 

在一个PostgreSQL数据库系统中，数据的组织结构可以分为如下三成：

数据库：一个PostgreSQL数据库下可以管理多个数据库，当应用连接的时候，只能连接到其中的一个数据库，而一个数据库只能属于一个实例。这与Oracle 12c中有Pluggable Database有点类似，但是一个PDB可以有多个实例，一个实例可以有多个PDB，它是用service name来加以区分。在Oracle 12c之前，一个实例只能有一个数据库，而一个数据库可以有多个实例（RAC）。

表、索引：一个数据库里可以有多个表与索引。一般，在PostgreSQL中表的术语为“Relation”。

数据行：在每张表中可以有很多数据行。一般，在PostgreSQL中数据行叫做“Tuple”。

下面，我们来看一下如何创建数据库。

我先给出数据库的创建语法：

create database db_name

[

[ WITH] 

[ OWNER [ = ] user_name ]

[ TEMPLDATE [ =] templdate ]

[ ENCODING [ = ] encoding ]

[ LC_COLLATE [ = ] lc_collate ]

[ LC_CTYPE [ = ] lc_ctype ]

[ TABLESPACE [ = ] tablespace ]

[ CONNECTION LIMIT [ = ] connection limit ]

]

说明：OWNER用来指定要创建的数据库属于那个用户，如果没有指定，则属于创建的用户。

TEMPLDATE：指定创建数据库需要的模板，如果不指定，则使用默认的模板template1，可以指定为template0。如果指定为template1，则创建的数据库的编码和区域的设置必须与模板数据库的相匹配，如果模板数据库中包含了与新建的数据库有编码不匹配的数据，或者包含了接受LC_COLLATE 与LC_CTYPE 影响的索引，那么复制这些数据将会导致数据库被新设置破坏。template0公认不包含任何会受字符编码或排序影响的数据的索引。

ENCODING：指定数据库使用的编码，比如为utf8。

LC_COLLATE ，LC_CTYPE ：指定排序类型。

TABLESPACE：指定数据库的默认表空间。

CONNECTION LIMIT：指定数据库的最大连接数，默认为-1，表示禁用连接。

下面，我们来看个例子。

postgres=# create database demodb

postgres-# template template0

postgres-# encoding 'UTF8'

postgres-# tablespace ts_demo01

postgres-# connection limit 200;

CREATE DATABASE

postgres=# \l

                                     List of databases

   Name    |   Owner    | Encoding |   Collate   |    Ctype    |     Access privileges     

-----------+------------+----------+-------------+-------------+---------------------------

 demodb    | pgsqladmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | pgsqladmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | pgsqladmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsqladmin            +

           |            |          |             |             | pgsqladmin=CTc/pgsqladmin

 template1 | pgsqladmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsqladmin            +

           |            |          |             |             | pgsqladmin=CTc/pgsqladmin

(4 rows)

postgres=# 

创建完成。

下面，我们来连接并进行访问。

postgres=# \q

[pgsqladmin@pgsql ~]$ 

[pgsqladmin@pgsql ~]$ psql -d demodb

psql (9.4.4)

Type "help" for help.

demodb=# create table t

demodb-# (

demodb(# x int,

demodb(# y varchar(30),

demodb(# z date

demodb(# );

CREATE TABLE

demodb=# insert into t(x,y,z) values(123,'abcd',date'2015-07-09');

INSERT 0 1

demodb=# commit;

WARNING:  there is no transaction in progress

COMMIT

demodb=# \d t

              Table "public.t"

 Column |         Type          | Modifiers 

--------+-----------------------+-----------

 x      | integer               | 

 y      | character varying(30) | 

 z      | date                  | 

demodb=# select * from t;

  x  |  y   |     z      

-----+------+------------

 123 | abcd | 2015-07-09

(1 row)

demodb=#