Postgresql 如何创建数据库

开源无限 2024-11-07

创建数据库

前言

postgresql 数据库是一款通用的关系型数据，在开源数据库中能与商业数据媲美，在业界也越来越流行。

因为是开源数据库，不仅公开源码，还有很多使用案例，好用的插件，所以它的慢慢变成了数据库的先驱和标准，通过postgresql可以很好从使用到原理，彻底搞懂；

如果是学习编程，也可以学到丰富的编程知识，数据结构，编程技巧，它里面还有很多精妙的架构设计，分层思想，可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解postgresql是什么样的数据库，能做那些事情，以及如何做好服务，最关键的是这些知识都是面试的必备项。

概述

使用数据库服务的前提是，需要指定一个database对象，这个database是其它数据库对象的载体，或者说是上级名命空间。本文来分享一下创建数据库的几种方法，给数据库赋予用户使用的权限；

原理介绍

大家在想，创建数据库很简单，一条SQL语句就可以了，还需要讲原理吗？

那我们来看这几个问题？

•数据库存储的位置在那里;•数据库的所有者是谁；•其它用户如何来访问这个数据库；

看起来还是不简单，数据库也是有存储位置的，它其实也是一个目录，它下面可以存储其它的数据库对象，如该数据库中的表对象；

当然，数据库作为一个各类数据库汇集存储的管理系统，不是所有用户都可以任意访问和操作的，这就有了数据库的所有者，所有者具有该数据库的所有权限，类似于这个数据库的一个管理员；

同样，数据库中有很多用户，它们也是不可以随意访问和操作的，需要管理员给他们分配权限，不符合权限的访问会被拒绝，就如同宿合管理，女生宿舍是不允许男生进入的。

现在通过演示来解答上述问题, 大概流程是这样的：

1.登录到数据库；2.执行创建数据库的SQL；3.创建新的用户；4.将新数据库的使用权限授予新用户；5.新用户登录到新数据库；

创建数据库

这一步的前提是，数据库服务已经启动，当前用户具有创建数据库的权限，一般刚开始我们都使用超级管理员来创建第一个自定义的数据库；

新数据库将通过复制模版数据库template1来创建，也就是说创建数据库，其实就是复制了一份template1模版库，而模版库是在集群初始化时就预定义好的；在postgresql 中每个数据库对象都有一个唯一的标识，叫做OID，新数据库也会分配一个OID；

创建数据库常见方法：

•在图形化客户端创建，通过菜单创建数据库；•通过命令或SQL来创建，本文也是分享这种方式，因为它简单直接，而且不会随着UI变动而发生不同；

本文介绍通过命令和SQL来创建，图形化相对简单；

在通过创建之前，我们需要准备登录信息，因为不管是那种创建方式，都是需要能够登录到数据库系统中，最终还是转化为SQL的执行。通过上篇分享，在刚初始化的集群中，默认有一个数据库 postgres
，有一个超级管理员用户，默为当前系统的用户 senllang
(根据自已的情况修改) ，监听IP为 localhost
, 端口5432
；

有了这些信息，我们就可以开始创建新的数据库了；

SQL创建

•登录数据库系统

[senllang@hatch bin]$ ./psql -d postgres
psql (16beta1)
Type "help" for help.


postgres=#

这里的用户名，IP，端口都采用默认值，只指定了数据库名；

•创建数据库在命令行中输入以下命令：

postgres=# CREATE DATABASE db_factory;
CREATE DATABASE

在上述命令中，将 db_factory
替换为你希望创建的数据库名称。

通过 \l
命令就可以看到数据库的列表

postgres=# \l
                                                        List of databases
    Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 db_factory | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 postgres   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 template0  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
            |          |          |                 |             |             |            |           | senllang=CTc/senllang
 template1  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
            |          |          |                 |             |             |            |           | senllang=CTc/senllang
(4 rows)

刚才创建的数据库 db_factory
的 Owner就是创建者用户，默认owner拥有该数据库的所有权限，所有Access privileges 一栏没有信息；

客户端命令创建

在安装目录下的bin目录中，有一个工具 createdb
，看名称我们就知道它就是创建数据库专用工具了；先来看下它的帮助信息

[senllang@hatch bin]$ ./createdb --help
createdb creates a PostgreSQL database.


Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]


Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
      --icu-locale=LOCALE      ICU locale setting for the database
      --icu-rules=RULES        ICU rules setting for the database
      --locale-provider={libc|icu}
                               locale provider for the database's default collation
  -O, --owner=OWNER            database user to own the new database
  -S, --strategy=STRATEGY      database creation strategy wal_log or file_copy
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit


Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database


By default, a database with the same name as the current user is created.


Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

可以看到，使用时，如果是默认ip,port，用户时，直接创建新的数据库即可；我们再创建一个 db_factory1
的数据库

[senllang@hatch bin]$ ./createdb db_factory1

postgres=# \l
                                                        List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 db_factory  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 db_factory1 | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 postgres    | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 template0   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
 template1   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
(5 rows)

通过查询，可以看到创建成功了，其它信息与第一种方法创建的一样；

以上两种方法都能成功创建 PostgreSQL 数据库，你可以根据个人喜好和使用环境选择适合你的方法。

存储位置

数据库存储在那里呢，前面博客介绍了数据库的逻辑架构，其中有说存储层次和命名空间，默认情况下，存储在默认的表空间pg_default，它的位置就是当前集群目录下的base目录；如果指定了表空间，那就是表空间对应的目录下了；

我们来证实一下，在集群目录下存储的目录和文件都是以OID来命名的，所以需要查看当前数据库的OID；

postgres=# select * from pg_tablespace ;
  oid  |   spcname    | spcowner | spcacl | spcoptions
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        |
  1664 | pg_global    |       10 |        |
 16394 | tblspc_test1 |       10 |        |
(3 rows)
postgres=# select oid, datname, dattablespace from pg_database ;
  oid  |   datname   | dattablespace
-------+-------------+---------------
     5 | postgres    |          1663
     1 | template1   |          1663
     4 | template0   |          1663
 16445 | db_factory  |          1663
 16446 | db_factory1 |          1663
(5 rows)

在系统表中记录了数据的组织形式，也可以叫它们为数据字典；在pg_tablespace
中记录了表空间的信息，名称和对应的OID；在pg_database
中记录了数据库的信息，名称和对应的OID，还有存储对应的表空间的OID；可以看到我们创建的两个factory的数据库，OID分别是 16445和16446，对应的表空间为 1663，也就是pg_default，即默认表空间的存储位置；

在系统内部所有数据库对象的使用都是通过OID来标识和引用，很多时间我们需要通过OID来找到它对应的名称；

接下来找到默认表空间的位置，它位于集群目录testdemo1
下，对应的就是base目录；

[senllang@hatch bin]$ cd testdemo1/base/
[senllang@hatch base]$ ls -l
total 60
drwx------. 2 senllang develops 8192 Sep  3 09:40 1
drwx------. 2 senllang develops 8192 Sep  7 08:27 16445
drwx------. 2 senllang develops 8192 Sep  7 08:32 16446
drwx------. 2 senllang develops 8192 Sep  2 22:02 4
drwx------. 2 senllang develops 8192 Sep  6 13:57 5
-- 集群目录的路径，根据自己的实际位置查找 
[senllang@hatch base]$ pwd
/opt/postgres/bin/testdemo1/base

可以看到base 目录下有五个文件夹，都是以数字命名，正好对应了数据库列表中的OID；新创建的两个数据库 16445，16446也在这里，而且它们下面已经有很多文件了，是从模版数据库复制过来的；

所有者

默认的owner用户为创建者，也可以在创建时指定，当然也可以在创建后修改。

owner在权限管理中有非常关键的作用，它相当于该数据库对象的管理者；

在 PostgreSQL 中，数据库对象的 owner 具有以下作用：

1.权限控制：数据库对象的 owner 具有对对象的完全控制权限。他们可以执行任何与该对象相关的操作，包括读取、修改、删除等。其他用户则需要通过 owner 授予的权限才能对对象进行操作。2.对象的访问：数据库对象的 owner 可以决定哪些用户可以访问该对象。通过授予不同的权限级别，owner 可以控制用户对对象的读取、修改和删除等操作。3.对象的所有权：数据库对象的 owner 是该对象的所有者，他们可以对该对象进行任何操作。所有权可以转移给其他用户，但只有 owner 可以回收所有权。4.对象的依赖关系：有些数据库对象（如视图、触发器等）依赖于其他对象，当这些依赖对象被删除或修改时，owner 可以决定是否允许删除或修改操作。

访问权限

数据库系统平常使用时，一般不会用超级管理员用户，只有在特殊场景时才会用超级管理员。我们创建一个新用户 manager
成为数据库的db_factory
所有者，由它来全权负责，给其它使用者分配权限；再创建一个 hr
的用户，它可以使用数据库，比如创建表等，但不能对数据库进行修改；

创建普通用户

新建两个普通用户，指定密码为123456；

postgres=# create user manager with password '123456';
CREATE ROLE
postgres=# create user hr with password '123456';
CREATE ROLE

这里有个细节，最后输出是 CREATE ROLE ，明明创建的是 create user，这个到用户权限章节再给大家分享；

修改所有者

将超级管理员的职责归位，用户的数据库应该由各自的管理员负责，所以这里将数据库 db_factory
的管理权限交给 manager
用户。

当然这个命令是非常危险的，最好在一开始创建时就分配好owner。

postgres=# alter database db_factory owner to manager ;
ALTER DATABASE
db_factory=> \l
                                                        List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 db_factory  | manager  | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =Tc/manager          +
             |          |          |                 |             |             |            |           | manager=CTc/manager  +
             |          |          |                 |             |             |            |           | hr=CTc/manager
 db_factory1 | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 postgres    | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 template0   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
 template1   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
(5 rows)

通过查看，db_factory
的owner确实变为了 manager
用户，Access privileges字段中也有了权限信息；

有人要说了，为什么这么麻烦呢？举个例子，这个数据库系统被三个业务共同使用，它们对应三个数据库，如果都由超级管理员用户管理，那每天都会吵架 “谁又动了我的库“，哈哈~

授予访问权限

用普通用户 manager
登录到数据库 db_factory
；可以退出 \q
之后重新登录，也可以用之前分享的方法 \c
进行切换数据库和用户；

给 hr
用户授予在当前数据库中的create 权限；

postgres=# \c db_factory manager
You are now connected to database "db_factory" as user "manager".


db_factory=> grant ALL ON database db_factory TO hr ;
GRANT

用普通用户 hr
登录到数据库 db_factory
；尝试创建表

db_factory=> \c db_factory hr
You are now connected to database "db_factory" as user "hr".


db_factory=> CREATE TABLE employees (
db_factory(>   employee_id SERIAL PRIMARY KEY,
db_factory(>   name VARCHAR(100),
db_factory(>   department VARCHAR(100),
db_factory(>   position VARCHAR(100),
db_factory(>   hire_date DATE,
db_factory(>   salary NUMERIC(10, 2)
db_factory(> );
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE employees (

居然失败了，不过从报错信息，看出来对于默认的schema，也就是public没有权限；通过前面命名空间的介绍，我们知道有在database之下还有schema，表必须在某个schema中；

前面授予了database下的create权限，但是没有schema的权限；下面授予schema下的create权限；

postgres=# \c db_factory manager
You are now connected to database "db_factory" as user "manager".


db_factory=> grant create on schema public  TO hr;
GRANT

新数据库中创建表

用普通用户 hr
登录到数据库 db_factory
；

db_factory=> \c db_factory hr
You are now connected to database "db_factory" as user "hr".
db_factory=> CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100),
  position VARCHAR(100),
  hire_date DATE,
  salary NUMERIC(10, 2)
);
CREATE TABLE
db_factory=> \d
                   List of relations
 Schema |           Name            |   Type   | Owner
--------+---------------------------+----------+-------
 public | employees                 | table    | hr
 public | employees_employee_id_seq | sequence | hr
(2 rows)

创建雇员表成功，它的owner就是创建者hr，所以hr用户拥有该表的所有权限；这样就是一种分层管理的策略，符合现实中的人员管理层级关系。

postgresql 数据库对象存储

文章转载自开源无限，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Postgresql 如何创建数据库

创建数据库

前言

概述

原理介绍

创建数据库

SQL创建

客户端命令创建

存储位置

所有者

访问权限

创建普通用户

修改所有者

授予访问权限

新数据库中创建表

评论