postgresql架构使用方法（postgresql常用语法）

概述表空间是数据库实际存储在文件系统中的位置，我来为大家科普一下关于postgresql架构使用方法?以下内容希望对你有帮助!

postgresql架构使用方法

表空间

概述

表空间是数据库实际存储在文件系统中的位置。

通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。这么做至少有两个用处。

1. 如果初始化集群所在的分区或者卷空间不足，而又不能在逻辑上扩展，那么表空间可以被创建在一个不同的分区上，从而达到集群可用。
2. 根据数据库对象的使用需求来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

表空间与数据库的关系：

• 在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于一对多的关系
• 在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于多对多的关系

默认表空间

• 表空间pg_default是template1和template0数据库的默认表空间，也将其他所有数据库的默认表空间，除非被CREATE DATABASE中的TABLESPACE子句覆盖，用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表，对应存储目录$PADATA/base/（包含每个数据库对应的子目录的子目录）
• 表空间pg_global全局共享系统表空间，用来存放系统字典表，对应存储目录$PADATA/global/（包含集簇范围的表的子目录）

表空间管理

创建表空间

创建表空间语法

CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory' [ WITH ( tablespace_option = value [, ... ] ) ]

参数：

• tablespace_name 要创建的表空间的名字。这个名字不能以pg_开头，因为这些名字是保留给系统表空间使用的。
• user_name 表空间所有者的用户名。如果省略，缺省为执行此命令的用户名。只有超级用户可以创建表空间，但是他们可以把表空间的所有者授予非超级用户。
• directory 表空间的存储目录。目录必须是空的，并且所有权由PostgreSQL服务所属系统用户所有。
• tablespace_option 设置或者重设的表空间参数。当前，唯一可用的参数是seq_page_cost和random_page_cost。 为一个特定的表空间设定其中的一个值， 覆盖规划器读取该表空间中表页面的通常估计成本， 就像通过相同名字的配置参数（参阅seq_page_cost、 random_page_cost）确定的一样。 如果一个表空间位于磁盘上，比其他I/O子系统更快或者更慢时，这个可能是有用的。

创建表空间

首先创建存储目录

$ mkdir -p /var/lib/pgsql/12/dbdata/mytb $ chown -R postgres:postgres /var/lib/pgsql/12/dbdata/mytb

• 创建表空间
• 使用postgres用户登录，并连接数据库，使用下面语句创建表空间

CREATE TABLESPACE mytbspace LOCATION '/var/lib/pgsql/12/dbdata/mytb';

设置默认表空间

SET default_tablespace = mytbspace; # 查看默认表空间 SHOW default_tablespace;

查看表空间

查看所有的表空间

SELECT * FROM pg_tablespace; 或者 \db # 列出更详细的信息 \db

数据库创建数据库

PostgreSQL 创建数据库可以用以下三种方式：

• 使用CREATE DATABASE sql语句来创建
• 使用createdb命令来创建
• 使用 pgAdmin 工具

CREATE DATABASE SQL语句创建

语法：

CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ CONNECTION LIMIT [=] connlimit ] ]

• 缺省情况下，新数据库将通过复制标准系统数据库template1来创建。 可以通过TEMPLATE name指定不同的模板。 尤其是，用TEMPLATE template0创建一个很纯净的、 只包括PostgreSQL预定义的标准对象的数据库。 这个方法对于避免把任何已经加入到template1里的本地安装对象拷贝到新数据库是非常有用的。

参数

• name
• 要创建的数据库名字。
• user_name
• 数据库所有者的名字。DEFAULT表示使用当前执行命令的用户。
• template使用的模板，即从哪个模板创建新数据库，或者使用DEFAULT选项来指定缺省模板(template1)。
• encoding创建新数据库使用的字符编码，常用UTF8、GBK。 查看支持的编码
• lc_collate数据库的排序规则(LC_COLLATE)。
• lc_ctype数据库的字符分类(LC_CTYPE)。
• tablespace_name指定表空间，或者使用DEFAULT选项表示使用模版数据库的表空间。
• connlimit数据库并发的连接数。-1(缺省)意味着没有限制。

例子：

CREATE DATABASE test WITH OWNER test TEMPLATE=template0 ENCODING 'UTF8' TABLESPACE test CONNECTION LIMIT 100;

createdb命令创建

createdb是SQL命令CREATE DATABASE的封装。

语法：

createdb [connection-option...] [option...] [dbname [description]]

选项:

• dbname要创建的数据库名。
• description数据库相关的注释。
• -D tablespace或--tablespace=tablespace指定数据库默认表空间。
• -e或--echo显示createdb生成并发送到服务端的命令。
• -E encoding或--encoding=encoding指定数据库的字符编码。
• -l locale 或--locale=locale指定在此数据库中使用的语言环境。相当于同时指定--lc-collate和--lc-ctype选项。
• --lc-collate=locale指定数据库的LC_COLLATE设置。
• --lc-ctype=locale指定数据库的LC_CTYPE设置。
• -O owner或--owner=owner指定数据库的所有者。
• -T template或--template=template指定创建此数据库使用的模板数据库。
• -V或--version输出createdb命令的版本信息。
• -?或--help显示createdb命令的帮助信息。
• 选项 -D, -l, -E, -O, 和 -T 对应SQL命令CREATE DATABASE的选项；
• createdb还接受以下命令行选项用于连接参数：-h host或--host=host指定运行服务器的主机名。如果数值以斜杠开头则被用作到Unix域套接字的路径。-p port或--port=port指定服务器侦听的TCP端口或一个本地Unix域套接字文件的扩展(描述符)。-U username或--username=username进行连接的用户名。-w或--no-password不提示输入密码。此选项在不需要用户输入密码的批处理作业和脚本中非常有用。-W或--password强制createdb联接到数据库之前提示输入密码。--maintenance-db=dbname指定要创建新数据库时连接的数据库名称。 如果没有指定，将使用postgres数据库；如果不存在（或是正在创建的新数据库的名称），将使用template1数据库。

环境变量

• PGDATABASE如果设置了，那么就是要创建的新数据库的名称，除非在命令行上进行覆盖。
• PGHOST、PGPORT、PGUSER连接参数

示例:

$ createdb mytestdb

删除数据库

只有数据库所有者且当前数据库没有连接的情况下才能执行删除操作。

语法：

DROP DATABASE [ IF EXISTS ] name;

修改数据库

语法：

ALTER DATABASE name [ [ WITH ] option [ ... ] ] # option 可以是以下参数： CONNECTION LIMIT connlimit ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO new_owner ALTER DATABASE name SET TABLESPACE new_tablespace ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT } ALTER DATABASE name SET configuration_parameter FROM CURRENT ALTER DATABASE name RESET configuration_parameter ALTER DATABASE name RESET ALL

示例：

关闭数据库test上缺省的索引扫描：

ALTER DATABASE test SET enable_indexscan TO off;

schema创建schema

语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ] CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification # 其中 role_specification 可以是： user_name | CURRENT_USER | SESSION_USER

参数：

• schema_name要创建的一个模式名。如果省略， user_name将被用作模式名。 该名称不能以pg_开始，因为这样的名称是用作系统模式的。
• user_name将拥有新模式的用户的角色名。如果省略，默认为执行该命令的用户。要 创建由另一个角色拥有的角色，你必须是那个角色的一个直接或者间接成员， 或者是一个超级用户。
• schema_element要在该模式中创建的对象的定义 SQL 语句。当前，只有CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE SEQUENCE、CREATE TRIGGER以及GRANT被接受为 CREATE SCHEMA中的子句。其他类型的对象可以在模式被 创建之后用单独的命令创建。
• IF NOT EXISTS如果一个存在同名的模式，则不创建。 使用这个选项时不能包括schema_element子命令。

示例：

创建一个模式：

CREATE SCHEMA myschema;

为用户joe创建一个模式，该模式也将被命名为 joe：

CREATE SCHEMA AUTHORIZATION joe;

修改schema

语法：

ALTER SCHEMA name RENAME TO new_name ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

删除schema

语法：

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

参数：

• IF EXISTS如果该模式不存在则不要抛出一个错误，而是发出一个提示。
• name模式的名称。
• CASCADE删除包含在该模式中的对象（表、函数等），然后删除所有依赖于那些对象的对象。
• RESTRICT如果该模式含有任何对象，则拒绝删除它。这是默认值。

用户角色管理创建用户

语法：

创建角色使用CREATE ROLE

CREATE USER name [ [ WITH ] option [ ... ] ] 这里 option 可以是： SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

描述：

CREATE USER现在是CREATE ROLE的一个别名。唯一的区别是 CREATE USER中LOGIN 被作为默认值，而NOLOGIN是 CREATE ROLE的默认值。

参数：

• name新角色或用户名。
• SUPERUSER、NOSUPERUSER是否创建一个超级管理员。
• CREATEDB、NOCREATEDB创建的角色是否具有创建数据库的权限。
• CREATEROLE、NOCREATEROLE创建的角色是否能够创建或删除其他角色。
• INHERIT、NOINHERIT创建的角色，若属于其他角色，是否从其他角色继承数据库权限。
• LOGIN、NOLOGIN创建的角色是否允许登录。如果没有指定，默认值是NOLOGIN，不过当通过CREATE USER创建时默认值会是LOGIN。
• REPLICATION、NOREPLICATION创建的角色是否具有复制权限。复制角色具有很高的特权，一般只有创建主备服务器时创建。
• BYPASSRLS、NOBYPASSRLS角色是否可以绕过每一条行级安全性（RLS）策略。
• CONNECTION LIMIT connlimit如果角色允许登录，指定角色能建立的连接并发量。-1（默认值）表示无限制。注意这个限制仅针对于普通连接。预备事务和后台工作者连接都不受这一限制管辖。
• [ ENCRYPTED ] PASSWORD 'password'、PASSWORD NULL设置角色的密码，如果没有设置密码，密码将被设置为空并且该用户的口令认证总是会失败。也可以用PASSWORD NULL显式地写出一个空口令。
• VALID UNTIL 'timestamp'
• 设置一个日期和时间，在该时间点之后角色的口令将会失效。
• IN ROLE role_name
• 将创建的角色加入已存在的角色中。
• IN GROUP role_name
• IN GROUP是IN ROLE的一种已废弃的拼写方式。
• ROLE role_name
• 创建一个组，并加入。
• ADMIN role_name
• ADMIN子句与ROLE相似，但是被创建的角色被使用WITH ADMIN OPTION加入到新角色中，它能够管理角色组中的成员。
• USER role_name
• USER子句是ROLE子句的一个已废弃的拼写方式。
• SYSID uid
• SYSID子句会被忽略，但是会为了向后兼容，还是会接受它。

示例：

• 创建一个有口令的角色：

CREATE USER davide WITH PASSWORD 'jw8s0F4';

• 创建一个角色，它的口令有效期截止到 2004 年底。在进入 2005 年第一秒时，该口令会失效。

CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

• 创建一个能够创建数据库并且管理角色的角色：

CREATE ROLE admin WITH CREATEDB CREATEROLE;

删除用户

语法：

DROP USER等同于DROP ROLE

DROP ROLE [ IF EXISTS ] name [, ...]

授权管理

授权

权限回收

创建用户到授权过程

1. 创建表空间

$ mkdir -p /var/lib/pgsql/12/dbhome/test $ chown -R postgres:postgres /var/lib/pgsql/12/dbhome/ $ su - postgres $ psql postgres=# create tablespace test location '/var/lib/pgsql/12/dbhome/test'; CREATE TABLESPACE

1. 创建用户

postgres=# create user test with password 'test'; CREATE ROLE

1. 创建数据库

postgres=# CREATE DATABASE test WITH OWNER=test TEMPLATE=template0 ENCODING='UTF8' TABLESPACE=test CONNECTION LIMIT=100; postgres=# select oid,datname from pg_database; oid | datname ------- ----------- 14187 | postgres 1 | template1 14186 | template0 16390 | test (4 rows)

1. 配置权限

• # 回收schema的public权限，否则test用户对所有的数据的public schema具有写权限revoke all on schema public from public;
• 如果要禁用连接其他的数据库，则使用下面的sql
• revoke all on database <数据库名> from public;revoke connect on database <数据库名> from public;

1. 创建新的schema并设置为默认schema

# 切换数据库 \c test; # 查看当前的search_path SHOW search_path; # 创建一个新的schema CREATE SCHEMA <schema名称>; # 默认schema更改为新的schema，在连接级别 SET search_path TO <schema名称>; # 将数据库的默认schema更改为新的schema ALTER DATABASE <数据库名> SET search_path TO <schema名称>; # 将用户的默认schema更改为新的schema ALTER USER <用户名> SET search_path to <schema名称>; # 把适用于该对象的所有权限都赋予目标角色 GRANT ALL ON SCHEMA <schema名称> TO <用户名>; GRANT ALL ON ALL TABLES IN SCHEMA <schema名称> TO <用户名>;

其他一些操作查看版本

select version(); select PostGIS_Full_Version();

查看所有系统表

select * from pg_tables

查看当前用户

select * from current_user;

查看所有的角色用户

select * from pg_roles; select * from pg_user; # 或 \du

查看所有数据库

\l

,