构建PostgreSQL工作环境

最新推荐文章于 2025-06-02 20:38:29 发布

jack2tom

最新推荐文章于 2025-06-02 20:38:29 发布

阅读量668

点赞数

分类专栏： PostgreSQL

PostgreSQL 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍如何从零开始构建PostgreSQL的工作环境，包括创建用户、设置权限、创建表空间、数据库和表等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

构建PostgreSQL工作环境

jieshiyeskey@gmail.com

1.创建用户并附权限及设置密码

postgres=# create role markgeng password 'Jieshi11gR2' login superuser createdb createrole;

CREATE ROLE

postgres=# \dg+

List of roles

Role name | Attributes | Member of | Description

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

markgeng | Superuser, Create role, Create DB | {} |

postgres | Superuser, Create role, Create DB, Replication | {} |

2.创建表空间

postgres=# create tablespace tsp_users owner markgeng location '/Library/PostgreSQL/9.2/data/tsp_users';

postgres=# \db+

List of tablespaces

Name | Owner | Location | Access privileges | Description

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

pg_default | postgres | | |

pg_global | postgres | | |

tsp_users | markgeng | /Library/PostgreSQL/9.2/data/tsp_users | |

3.创建数据库

postgres=# create database orcl owner=markgeng tablespace=tsp_users;

CREATE DATABASE

postgres=# \l+

List of databases

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

home_markgeng | postgres | UTF8 | C | C | | 6233 kB | pg_default |

orcl | markgeng | UTF8 | C | C | | 6293 kB | tsp_users |

postgres | postgres | UTF8 | C | C | | 6797 kB | pg_default | default administrative connection database

template0 | postgres | UTF8 | C | C | =c/postgres +| 6177 kB | pg_default | unmodifiable empty database

| | | | | postgres=CTc/postgres | | |

template1 | postgres | UTF8 | C | C | =c/postgres +| 6185 kB | pg_default | default template for new databases

| | | | | postgres=CTc/postgres |

4.创建schema

postgres=# \c orcl markgeng

Password for user markgeng:

You are now connected to database "orcl" as user "markgeng".

orcl=# create schema authorization markgeng;

CREATE SCHEMA

orcl=# \dn+

List of schemas

Name | Owner | Access privileges | Description

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

markgeng | markgeng | |

public | postgres | postgres=UC/postgres+| standard public schema

| | =UC/postgres |

(2 rows)

5.创建表

orcl=# create table t1(id int);

CREATE TABLE

orcl=# \dt+

List of relations

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

(1 row)

orcl=# \d t1

Table "markgeng.t1"

Column | Type | Modifiers

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

id | integer |

orcl=# insert into t1 values(1);

INSERT 0 1

orcl=# select * from t1;

----

(1 row)