管理数据库Database
--创建一个数据库
createdb gtlions;
create database gtlions;
createdb -T template0 databasename;
create database gtlions template template0 tablespace gtlions_ts1;
alter database gtlions rename to gtlions1;
alter database gtlions set default_tablespace=gtlions_ts1;
alter database gtlions reset default_tablespace;
alter database gtlions owner to gtlions;
创建一个数据库默认会使用模板库template1克隆一个新库，这个模板包含了一些内置的对象；如果想获得一个干净的数据库，使用template0模板。创建数据库之后我们可以修改数据库的属主。
postgres=# create database gtlions;
CREATE DATABASE
postgres=# create user gtlions superuser;
CREATE ROLE
postgres=# alter database gtlions owner to gtlions;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
gtlions | gtlions | UTF8 |
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(4 rows)
postgres=# \c gtlions
You are now connected to database "gtlions" as user "gpadmin".
postgres=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl
-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+----------------------------------
postgres | 10 | 6 | t | t | -1 | 10899 | 803 | 1663 | |
template1 | 10 | 6 | t | t | -1 | 10899 | 803 | 1663 | | {=c/gpadmin,gpadmin=CTc/gpadmin}
template0 | 10 | 6 | t | f | -1 | 10899 | 803 | 1663 | | {=c/gpadmin,gpadmin=CTc/gpadmin}
gtlions | 98690 | 6 | f | t | -1 | 10899 | 803 | 1663 | |
(4 rows)
gtlions=# select datname,datdba,usename,encoding,pg_encoding_to_char(encoding) from pg_database a,pg_user b where a.datname='gtlions' and a.datdba=b.usesysid;
datname | datdba | usename | encoding | pg_encoding_to_char
---------+--------+---------+----------+---------------------
gtlions | 98690 | gtlions | 6 | UTF8
(1 row)
-EOF-
