Postgresql数据库命令行、数据库、表空间操作

一、查看数据库和表

列出数据库：

您可以使用以下任何一个命令列出postgresql中的所有数据库。

postgres=# list
postgres=# l

列出数据库中所有表：

要列出任何数据库的表先连接到数据库，然后查看该数据库中的表。 （例如：mydb）要看到表和第二个命令将列出表首先命令将您连接数据库。

postgres=# connect mydb

mydb=# dt

二、创建数据库：

列出数据库名称

SELECT datname FROM pg_database;

在Postgresql服务器上执行下面的SQL语句可以创建数据库。

CREATE DATABASE db_name;

在数据库成功创建之后，当前登录角色将自动成为此新数据库的所有者。在删除该数据库时，也需要该用户的特权。如果你想让当前创建的数据库的所有者为其它角色，可以执行下面的SQL语句。

CREATE DATABASE db_name OWNER role_name;

三、修改数据库配置：

PostgreSQL服务器提供了大量的运行时配置变量，我们可以根据自己的实际情况为某一数据库的某一配置变量指定特殊值，通过执行下面的SQL命令可以使该数据库的某一配置被设置为指定值，而不再使用缺省值。

ALTER DATABASE db_name SET varname TO new_value;

这样在之后基于该数据库的会话中，被修改的配置值已经生效。如果要撤消这样的设置并恢复为原有的缺省值，可以执行下面的SQL命令。

ALTER DATABASE dbname RESET varname;

四、删除数据库：

只有数据库的所有者和超级用户可以删除数据库。删除数据库将会删除数据库中包括的所有对象，该操作是不可恢复的。见如下删除SQL命令：

DROP DATABASE db_name;

五、表空间：

在PostgreSQL中，表空间表示一组文件存放的目录位置。在创建之后，就可以在该表空间上创建数据库对象。通过使用表空间，管理员可以控制一个PostgreSQL服务器的磁盘布局。这样管理员就可以根据数据库对象的数据量和数据使用频度等参照来规划这些对象的存储位置，以便减少io等待，从而优化系统的整体运行性能。比如，将一个使用频繁的索引放在非常可靠、高效的磁盘设备上，如固态硬盘。而将很少使用的数据库对象存放在相对较慢的磁盘系统上。下面的SQL命令用于创建表空间。

CREATE TABLESPACE fastspace LOCATION "/mnt/sda1/postgresql/data";

需要说明的是，表空间指定的位置必须是一个现有的空目录，且属于PostgreSQL系统用户，如postgres。在成功创建之后，所有在该表空间上创建的对象都将被存放在这个目录下的文件里。

在PostgreSQL中只有超级用户可以创建表空间，但是在成功创建之后，就可以允许普通数据库用户在其上创建数据库对象了。要完成此操作，必须在表空间上给这些用户授予CREATE权限。表、索引和整个数据库都可以放在特定的表空间里。见如下SQL命令：

CREATE TABLE foo(i int) TABLESPACE space1;

此外，我们还可以通过修改default_tablespace配置变量，以使指定的表空间成为缺省表空间，这样在创建任何数据库对象时，如果没有显示指定表空间，那么该对象将被创建在缺省表空间中，如：

SET default_tablespace = space1;

CREATE TABLE foo(i int);

与数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。

要删除一个空的表空间，可以直接使用DROP TABLESPACE命令，然而要删除一个包含数据库对象的表空间，则需要先将该表空间上的所有对象全部删除后，才可以再在删除该表空间。

要检索当前系统中有哪些表空间，可以执行以下查询，其中pg_tablespace为PostgreSQL中的系统表。

SELECT spcname FROM pg_tablespace;

我们还可以通过psql程序的db元命令列出现有的表空间。