create databse test;
# 切换数据库
c test;

CREATE TABLE films (
     code        char(5) CONSTRAINT firstkey PRIMARY KEY,
     title       varchar(40) NOT NULL,
     did         integer NOT NULL,
     date_prod   date,
     kind        varchar(10),
     len         interval hour to minute
 );

insert into films values ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

test=# create unique index title_index on films (title);
CREATE INDEX

-----  查看数据库
test-# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)


-----  列出数据库中所有的表
test=# dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | films | table | postgres
(1 row)

-----  查看表结构
test=# d films
              Table "public.films"
  Column   |          Type           | Modifiers
-----------+-------------------------+-----------
 code      | character(5)            | not null
 title     | character varying(40)   | not null
 did       | integer                 | not null
 date_prod | date                    |
 kind      | character varying(10)   |
 len       | interval hour to minute |
Indexes:
    "firstkey" PRIMARY KEY, btree (code)
    "title_index" UNIQUE, btree (title)

test=# select * from films ;
 code  |  title  | did | date_prod  |  kind  |   len
-------+---------+-----+------------+--------+----------
 UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22:00
(1 row)

----- 直接在建表时使用serial类型，默认生成为tblname+colname+'seq'，如下的名字为 t2_id_seq
postgres=# create table t2(id serial ,name text);
CREATE TABLE
postgres=# d t2
                         Table "public.t2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t2_id_seq'::regclass)
 name   | text    |
----- 查看 t2_id_seq 的属性，max_value为最大值
postgres=# d t2_id_seq
          Sequence "public.t2_id_seq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | t2_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: public.t2.id
----- 这里加名字时，不能使用双引号，只能使用单引号
postgres=# insert into t2(name) values("aaa");
ERROR:  column "aaa" does not exist
LINE 1: insert into t2(name) values("aaa");
                                    ^
postgres=# insert into t2(name) values('aaa');
INSERT 0 1
postgres=# insert into t2(name) values('bbb');
INSERT 0 1
postgres=# select * from t2;
 id | name
----+------
  1 | aaa
  2 | bbb
(2 rows)

-----  查看schema，默认就有public
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

postgres=#
postgres=#
postgres=# create schema s01;
CREATE SCHEMA
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 s01    | postgres
(2 rows)

postgres=# create table s01.t1(id int);
CREATE TABLE
postgres=# insert into s01.t1 values (1) ;
INSERT 0 1
postgres=# select * from s01.t1 ;
 id
----
  1
(1 row)

postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values (88);
INSERT 0 1
----- 默认是schema为public
postgres=# select * from t1 ;
 id
----
 88
(1 row)
postgres=# select * from public.t1 ;
 id
----
 88
(1 row)

postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
 s01        | t1        | postgres   |            | f          | f        | f           | f
(2 rows)

postgres=# drop schema s01 ;
ERROR:  cannot drop schema s01 because other objects depend on it
DETAIL:  table s01.t1 depends on schema s01
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop schema s01 cascade;
NOTICE:  drop cascades to table s01.t1
DROP SCHEMA
postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f

----- 指定s01模式的所有者为fdm这个用户，但由于没有创建，所以会失败。
postgres=# create schema s01 authorization fdm;
ERROR:  role "fdm" does not exist
postgres=# create user fdm with password '123456';
CREATE ROLE
postgres=# create schema s01 authorization fdm;
CREATE SCHEMA
postgres=#
----- 也可以不写schema的名字，默认为owner的名称
postgres=# create schema authorization fdm;
CREATE SCHEMA
postgres=# dn
  List of schemas
  Name  |  Owner
--------+----------
 fdm    | fdm
 public | postgres
 s01    | fdm
(3 rows)

postgres=# select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
(1 row)
----- 切换到fdm这个用户，那这个用户的schema为fdm -----
postgres=# c - fdm
Password for user fdm:
You are now connected to database "postgres" as user "fdm".
----- 这个没有加schema，但由于fdm用户的schema为fdm，所以创建后数据库的schema就是fdm -----
postgres=> create table t1 (id int);
CREATE TABLE
postgres=> create table s01.t1(id int);
CREATE TABLE
----- 由于pg_tables表就可以看到表的相关的信息，注意schemaname与tableowner -----
postgres=> select * from pg_tables where tablename = 't1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   |            | f          | f        | f           | f
 s01        | t1        | fdm        |            | f          | f        | f           | f
 fdm        | t1        | fdm        |            | f          | f        | f           | f

----- 当前登陆用户为fdm，所以$user的值为fdm
postgres=> show search_path ;
   search_path
-----------------
 "$user", public
(1 row)
----- 当前登陆用户为fdm，那这个用户的schema为fdm -----
postgres=> insert into t1 values (333);
INSERT 0 1
postgres=> insert into s01.t1 values (555);
INSERT 0 1
postgres=> select * from t1;
 id
-----
 333
(1 row)

----- public.t1 是postgres这个用户创建的，所以fdm没有权限 -----
postgres=> select * from public.t1 ;
ERROR:  permission denied for relation t1
----- 将search_path切换到s01 -----==
postgres=> set search_path = 's01' ;
SET
postgres=> show search_path ;
 search_path
-------------
 s01
(1 row)
postgres=> select * from t1;
 id
-----
 555
(1 row)

----- 先用超管账号进行登陆
postgres=# conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.137.66" at port "5432".
----- 先创建schema以及User、table -----
postgres=# create schema s02;
CREATE SCHEMA
postgres=# create user u02 with password '123456';
CREATE ROLE
postgres=# create table s02.t1 (id int);
CREATE TABLE
----- 切换用u02来登陆 -----
postgres=# c - u02;
----- 没有权限查看 -----
postgres=> select * from s02.t1 ;
ERROR:  permission denied for schema s02
LINE 1: select * from s02.t1 ;
postgres=> dt s02.t1
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 s02    | t1   | table | postgres
(1 row)

----- 将s02授权给U02 -----==
postgres=> c - postgres
postgres=# grant all on SCHEMA s02 to u02;
GRANT
postgres=> c - u02;
----- 提示没有查询权限 -----==
postgres=> select * from s02.t1 ;
ERROR:  permission denied for relation t1
postgres=> c - postgres
----- 将所有表的schema为s02的owner修改为u02 -----==
postgres=# grant all on all tables in schema s02 to u02;
GRANT
postgres=> select * from s02.t1 ;
 id
----
(0 rows)

postgres=# create user dbuser with password 'dbuser';
CREATE ROLE
postgres=# create database exampledb OWNER dbuser;
postgres=# grant all on DATABASE exampledb to dbuser;
GRANT
postgres=# c exampledb
psql (9.2.24, server 9.6.15)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
You are now connected to database "exampledb" as user "postgres".
exampledb=# dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)
exampledb=# alter schema public owner to dbuser ;
ALTER SCHEMA
exampledb=# dn
 List of schemas
  Name  | Owner
--------+--------
 public | dbuser
(1 row)

exampledb=# grant ALL ON all tables in schema public to dbuser;
GRANT
exampledb=# grant ALL ON all sequences in schema public to dbuser;
GRANT

--- 在新建数据库时，就先创建一个schema
create database test_db;
create user test_user with password '123456';
create schema test_schema authorization test_user;
grant all on schema test_schema to test_user;
grant all privileges on database test_db to test_user;
alter database test_db set search_path to test_user;

--- 或者使用public，但是修改owner
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
c exampledb;
ALTER SCHEMA public OWNER to dbuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;

postgres=# create table foo(id integer);
CREATE TABLE
postgres=# insert into foo values(generate_series(1,100));
INSERT 0 100
postgres=# select pg_size_pretty(pg_database_size('postgres'));
 pg_size_pretty
----------------
 7359 kB
(1 row)

------或者先查出oid值，再看查数据库的大小
postgres=# select oid from pg_database where datname='postgres';
  oid
-------
 13325
(1 row)
postgres=# q
[root@localhost ~]# du -sh /data/PostgreSQL/data/base/13325
7.3M	/data/PostgreSQL/data/base/13325

postgres=# show archive_timeout;
 archive_timeout
-----------------
 0
(1 row)
postgres=# show wal_segment_size;
 wal_segment_size
------------------
 16MB
(1 row)

postgres=# CREATE USER repuser REPLICATION  LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';
CREATE ROLE

postgres=# select pg_create_restore_point('201911052224');
 pg_create_restore_point
-------------------------
 0/3000138
(1 row)

postgres=# delete from foo ;
DELETE 100
postgres=# select count(*) from foo;
 count
-------
     0
(1 row)

postgres=# select current_timestamp;
              now
-------------------------------
 2019-11-05 10:24:40.932538+08
(1 row)

postgres=# CREATE USER repuser REPLICATION  LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state
----------------+------------
 192.168.137.55 | async
(1 row)

--- 如果返回 f 说明是主库，返回 t 说明是备库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=# create table foo(id integer);
CREATE TABLE
postgres=# insert into foo values(generate_series(1,100));
INSERT 0 100