构建PostgreSQL工作环境

前端之家收集整理的这篇文章主要介绍了构建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@

猜你在找的Postgre SQL相关文章