PostgreSQL 数据库与模式(一)

基本概念

数据库管理系统（DBMS）是用于管理数据库的软件系统。常见的关系型DBMS有Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2等。常见的NoSQLDBMS有Redis、MongoDB、Cassandra、Neo4j等。

数据库系统由实例（Instance）和物理数据库集群（Database Cluster）组成。通常所说的数据库管理系统也就是指数据库系统。

 实例（Instance）由PostgreSQL后台进程和相关的内存组成。启动服务器进程时创建一个实例，关闭服务器进程时实例随之关闭。启动PostgreSQL服务器进程之后，可以通过操作系统的ps命令查询相关的后台进程

[hui@hadoop201 ~]$ ps -ef | grep postgres
postgres   2643      1  0 09:41 ?        00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
postgres   2645   2643  0 09:41 ?        00:00:00 postgres: logger   
postgres   2647   2643  0 09:41 ?        00:00:00 postgres: checkpointer   
postgres   2648   2643  0 09:41 ?        00:00:00 postgres: background writer   
postgres   2649   2643  0 09:41 ?        00:00:00 postgres: walwriter   
postgres   2650   2643  0 09:41 ?        00:00:00 postgres: autovacuum launcher   
postgres   2651   2643  0 09:41 ?        00:00:00 postgres: stats collector   
postgres   2652   2643  0 09:41 ?        00:00:00 postgres: logical replication launcher   
root       2657   2298  0 09:41 pts/3    00:00:00 su postgres
postgres   2658   2657  0 09:41 pts/3    00:00:00 bash
postgres   2664   2658  0 09:44 pts/3    00:00:00 psql -U postgres
postgres   2665   2643  0 09:44 ?        00:00:00 postgres: postgres postgres [local] idle
postgres   2685   2643  0 09:56 ?        00:00:00 postgres: postgres postgres 192.168.124.1(62215) idle
root       2964   2436  0 13:00 pts/2    00:00:00 psql -h hadoop201 -p 5432 -U wdh postgres
postgres   2966   2643  0 13:00 ?        00:00:00 postgres: wdh postgres 192.168.124.141(44540) idle
hui        3208   3141  0 15:01 pts/0    00:00:00 grep --color=auto postgres
[hui@hadoop201 ~]$ 

数据库集群，每个PostgreSQL实例管理的都是一个数据库集群，它可以包含多个数据库。需要注意，这里的集群不是多台服务器组成的集群。

 

数据库（Database），一个数据库由一组相关的对象组成，例如表、索引、视图、存储过程等等。

模式（Schema），数据库中的对象使用模式进行组织。准确地说，一个数据库由多个模式组成，模式由许多对象组成。

表空间（Tablespace），在PostgreSQL中，数据库对象（例如表）在文件系统中对应的是文件，表空间指定了这些文件存储的目录。创建数据库对象时，只需要指定存储对象的表空间的名称（或者使用默认值），而不需要指定磁盘上的物理路径。

创建数据库

创建数据库之前，先看下默认的数据库有哪些

方式1：postgres用户登录，通过psql工具连接，使用  \l 查看

[hui@hadoop201 preserve]$ su postgres
Password: 
bash-4.2$ psql
psql (12.13)
Type "help" for help.

postgres=# \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

方式2：sql查询

select * from pg_database ;

系统默认创建了3个数据库，其中template0和template1是模板数据库，创建新的数据库时默认基于template1进行复制；postgres数据库是为postgres用户创建的默认数据库。

使用SQL语CREATE DATABASE创建一个数据库：

CREATE DATABASE name;

其中的name指定了数据库的名称。举个例子创建一个名为testdb的数据库

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \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
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

创建数据库时还可以指定许多选项，例如字符集编码、拥有者、默认表空间、最大连接数等等。具体参考官方文档中完整的CREATE DATABASE语句。

修改数据库

创建之后，可以通过ALTER DATABASE语句修改数据库的属性和配置：

ALTER DATABASE name action;

其中，action指定了要执行的修改操作，例如修改数据库的名称、所有者、默认表空间、数据库会话变量的默认值等等。以下语句修改testdb的名称：

postgres=# ALTER DATABASE testdb RENAME TO test;
ALTER DATABASE
postgres=# \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
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

然后将数据库test的拥有者修改为wdh：

postgres=# alter database test owner to wdh;
ALTER DATABASE
postgres=# \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
 test      | wdh      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

除了修改常见的属性之外，ALTER DATABASE语句还可以用于修改运行时配置变量的会话默认值。当用户连接数据库时，PostgreSQL使用配置文件postgresql.conf或者启动命令postgres中设置的变量值作为默认值。使用ALTER DATABASE语句可以设置指定数据库的这些配置：

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }

对于随后连接的会话，PostgreSQL将会使用该命令设置的值覆盖postgresql.conf文件或者命令行参数的值。注意，只有超级用户或者数据库的拥有者才能修改数据库的默认会话变量。例如，以下语句将会默认禁用数据库test中的索引扫描：

postgres=# ALTER DATABASE test SET enable_indexscan TO off;
ALTER DATABASE

使用以下命令可以还原默认配置：

ALTER DATABASE newdb RESET enable_indexscan;