—帮助命令
test=# \h
—查看表结构
postgres-# \d pg_database
Table “pg_catalog.pg_database”
Column | Type | Collation | Nullable | Default
---------------±----------±----------±---------±--------
datname | name | | not null |
datdba | oid | | not null |
encoding | integer | | not null |
datcollate | name | | not null |
datctype | name | | not null |
datistemplate | boolean | | not null |
datallowconn | boolean | | not null |
datconnlimit | integer | | not null |
datlastsysoid | oid | | not null |
datfrozenxid | xid | | not null |
datminmxid | xid | | not null |
dattablespace | oid | | not null |
datacl | aclitem[] | | |
–找出数据库对应的OID
postgres=# Select datname,oid from pg_database where datname=‘test’;
datname | oid
---------±------
test | 16384
—找出表对应的oid
test=# \d
List of relations
Schema | Name | Type | Owner
--------±------±------±---------
public | test1 | table | postgres
select relname,oid from pg_class where relname=‘test1’;
relname | oid
---------±------
test1 | 16385
—可以根据oid的关系找到相应的路径
test=# select pg_relation_filepath(‘test1’);
pg_relation_filepath
base/16384/24577
(1 row)
–创建表
test=# create table test1 (id int);
CREATE TABLE
–创建新的数据库执行:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ CONNECTION LIMIT [=] connlimit ] ]
ostgres=# CREATE DATABASE test WITH OWNER postgres ENCODING=‘UTF-8’;
—创建数据库并指定表空间
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
创建数据目录
/monchickey/pgsql/bin/nitdb -D /monchickey/data
或者
/monchickey/pgsql/bin/pg_ctl -D /monchickey/data initdb
创建表空间
postgres=# CREATE TABLESPACE indexspace OWNER postgres LOCATION ‘/monchickey’;
CREATE TABLESPACE
查看基础目录中的文件每个编号对应着一个数据库
[postgres@ora11r2 base]$ pwd
/monchickey/data/base
[postgres@ora11r2 base]$ ls -lrt
total 12
drwx------. 2 postgres postgres 4096 Jun 29 04:05 1
drwx------. 2 postgres postgres 4096 Jun 29 04:05 13801
drwx------. 2 postgres postgres 4096 Jun 29 04:05 13802
查看这些数字目录对应是是那几个库
test=# Select datname,oid from pg_database where oid=13801;
datname | oid
-----------±------
template0 | 13801
(1 row)
test=# Select datname,oid from pg_database where oid=13802;
datname | oid
----------±------
postgres | 13802
(1 row)