构建PostgreSQL工作环境
List of roles
Role name | Attributes | Member of | Description
markgeng | Superuser, Create role, Create DB | {} |
postgres | Superuser, Create role, Create DB, Replication | {} |
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 | |
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
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 |
List of schemas
Name | Owner | Access privileges | Description
markgeng | markgeng | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
List of relations
Schema | Name | Type | Owner | Size | Description
markgeng | t1 | table | markgeng | 0 bytes |
Table "markgeng.t1"
Column | Type | Modifiers
id | integer |
id
1
jieshiyeskey@gmail.com
1.创建用户并附权限及设置密码
postgres=# create role markgeng password 'Jieshi11gR2' login superuser createdb createrole;
CREATE ROLE
postgres=# \dg+
-----------+------------------------------------------------+-----------+-------------
2.创建表空间
postgres=# create tablespace tsp_users owner markgeng location '/Library/PostgreSQL/9.2/data/tsp_users';
postgres=# \db+
------------+----------+----------------------------------------+-------------------+-------------
3.创建数据库
postgres=# create database orcl owner=markgeng tablespace=tsp_users;
CREATE DATABASE
postgres=# \l+
---------------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
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+
----------+----------+----------------------+------------------------
(2 rows)
5.创建表
orcl=# create table t1(id int);
CREATE TABLE
orcl=# \dt+
----------+------+-------+----------+---------+-------------
(1 row)
orcl=# \d t1
--------+---------+-----------
orcl=# insert into t1 values(1);
INSERT 0 1
orcl=# select * from t1;
----
(1 row)