构建PostgreSQL工作环境

构建Postgresql工作环境

jieshiyeskey@gmail.com

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

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

CREATE ROLE@H_404_9@

@H_404_9@

postgres=# \dg+@H_404_9@

List of roles@H_404_9@

Role name | Attributes | Member of | Description @H_404_9@

-----------+------------------------------------------------+-----------+-------------@H_404_9@

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

postgres | Superuser,Create DB,Replication | {} | @H_404_9@

@H_404_9@

2.创建表空间@H_404_9@

postgres=# create tablespace tsp_users owner markgeng location '/Library/Postgresql/9.2/data/tsp_users';@H_404_9@

postgres=# \db+@H_404_9@

List of tablespaces@H_404_9@

Name | Owner| Location | Access privileges | Description @H_404_9@

------------+----------+----------------------------------------+-------------------+-------------@H_404_9@

pg_default | postgres | | | @H_404_9@

pg_global | postgres | | | @H_404_9@

tsp_users | markgeng | /Library/Postgresql/9.2/data/tsp_users | | @H_404_9@

3.创建数据库@H_404_9@

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

CREATE DATABASE@H_404_9@

postgres=# \l+@H_404_9@

List of databases@H_404_9@

Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description @H_404_9@

---------------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------@H_404_9@

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

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

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

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

| | | | | postgres=CTc/postgres | | | @H_404_9@

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

| | | | | postgres=CTc/postgres |@H_404_9@

4.创建schema@H_404_9@

postgres=# \c orcl markgeng@H_404_9@

Password for user markgeng: @H_404_9@

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

orcl=# create schema authorization markgeng;@H_404_9@

CREATE SCHEMA@H_404_9@

orcl=# \dn+@H_404_9@

List of schemas@H_404_9@

Name | Owner | Access privileges | Description @H_404_9@

----------+----------+----------------------+------------------------@H_404_9@

markgeng | markgeng | | @H_404_9@

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

| | =UC/postgres | @H_404_9@

(2 rows)@H_404_9@

5.创建表@H_404_9@

orcl=# create table t1(id int);@H_404_9@

CREATE TABLE@H_404_9@

orcl=# \dt+@H_404_9@

List of relations@H_404_9@

Schema | Name | Type |Owner| Size | Description @H_404_9@

----------+------+-------+----------+---------+-------------@H_404_9@

markgeng | t1 | table | markgeng | 0 bytes |@H_404_9@

(1 row)@H_404_9@

orcl=# \d t1@H_404_9@

Table "markgeng.t1"@H_404_9@

Column | Type | Modifiers@H_404_9@

--------+---------+-----------@H_404_9@

id | integer | @H_404_9@

@H_404_9@

@H_404_9@

orcl=# insert into t1 values(1);@H_404_9@

INSERT 0 1@H_404_9@

orcl=# select * from t1;@H_404_9@

id @H_404_9@

----@H_404_9@

1@H_404_9@

(1 row)@H_404_9@

构建PostgreSQL工作环境

猜你在找的Postgre SQL相关文章