1 数据库
#创建数据库
postgres=# create database baoywdb;
CREATE DATABASE
postgres=#
1.1 查看数据库
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
baoywdb | baoyiwen | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 892 MB | ts_baoyw |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8745 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8537 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8537 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=#
baoyiwen=# select oid,datname from pg_database;
oid | datname
-------+-----------
13892 | postgres
1 | template1
13891 | template0
16637 | baoyiwen
(4 rows)
baoyiwen=#
1.2 更改数据库定义
alter database baoywdb rename to baoywdb0;
alter database baoywdb0 owner to baoyiwen;
alter database baoyiwen0 set tablespace ts_baoyw;
drop database baoyiwen0;
2 用户
#创建用户
postgres=# create user baoyiwen;
CREATE ROLE
postgres=#
2.1 授权用户
postgres=# alter user baoyiwen with replication;
ALTER ROLE
postgres=# alter user baoyiwen createdb;
ALTER ROLE
postgres=#
#其他授权
alter user baoyiwen with SUPERUSER;
alter user baoyiwen createdb;
alter user baoyiwen createrole;
alter user baoyiwen replication;
alter user baoyiwen bypassrls;
2.2 查看用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
baoyiwen | Create DB, Replication | {}
ljfz | Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
3 连接到新的数据库
postgres=# \c baoywdb baoyiwen
You are now connected to database "baoywdb" as user "baoyiwen".
baoywdb=>
4 SCHEMA
# 在用户baoyiwen下创建，owner就是baoyiwen
create schema bao;
alter schema bao rename to baobao;
alter schema bao owner to baoywien;
drop schema bao;
4.1 回收模式权限
#第一个public是模式的名称（标识符）
#第二个PUBLIC是所有用户（关键字），关键字大小写无关。
revoke create on schema public from public;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
5 表空间
5.1 创建表空间
[postgres@pgccc01 pgdata]$ psql
psql (14.7)
Type "help" for help.
postgres=# create tablespace ts_baoyw location '/pgccc/ywdata';
2023-05-15 12:56:51.487 CST [3490] ERROR: directory "/pgccc/ywdata" does not exist
2023-05-15 12:56:51.487 CST [3490] STATEMENT: create tablespace ts_baoyw location '/pgccc/ywdata';
ERROR: directory "/pgccc/ywdata" does not exist
postgres=# \q
[postgres@pgccc01 pgdata]$ mkdir -p /pgccc/ywdata
[postgres@pgccc01 pgdata]$ psql
psql (14.7)
Type "help" for help.
postgres=# create tablespace ts_baoyw location '/pgccc/ywdata';
CREATE TABLESPACE
postgres=#
5.2 更改表空间定义
#更改表空间 ts_baoyw 的拥有者为 baoyiwen
postgres=# alter tablespace ts_baoyw owner to baoyiwen;
ALTER TABLESPACE
#将表空间 ts_baoyw 重命名为 ts_baoyw000
postgres-# \db
List of tablespaces
Name | Owner | Location
------------+----------+---------------
pg_default | postgres |
pg_global | postgres |
ts_baoyw | baoyiwen | /pgccc/ywdata
(3 rows)
postgres=# alter tablespace ts_baoyw rename to ts_baoyw000;
ALTER TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
--------------+----------+---------------
pg_default | postgres |
pg_global | postgres |
ts_baoyw000 | baoyiwen | /pgccc/ywdata
(3 rows)
postgres=#
6 表
#创建表
postgres=# create table t_hejp (id int,name varchar(20)) tablespace ts_baoyw;
CREATE TABLE
postgres=#
6.1 修改表的定义
alter table t_baoyw owner to baoyiwen;
alter table t_baoyw set schema bao;
alter table bao.t_baoyw set tablespace ts_baoyw;
6.2 向表中插入数据
6.3 操作表中数据
#插入数据
insert into t_baoyw values(1,'西西科技');
insert into bao.t_baoyw select generate_series(1,5000000),substring('abcdefghijklmnopqrstuvwxyz',(random()*5)::int,(random()*20)::int) as name;
#查看数据
postgres=# select * from t_baoyw;
id | name
----+----------
1 | 西西科技
1 | 西西科技
(2 rows)
#删除数据
postgres=# truncate table t_baoyw;
TRUNCATE TABLE
#删除表
postgres=# drop table t_baoyw;
DROP TABLE
6.4 主键约束
#添加主键约束
postgres=# alter table t_baoyw add constraint pk_id primary key(id);
ALTER TABLE
postgres=# insert into t_baoyw values(1,'AAAAA');
INSERT 0 1
postgres=# insert into t_baoyw values(1,'BBBBB');
2023-05-15 13:39:15.294 CST [3790] ERROR: duplicate key value violates unique constraint "pk_id"
2023-05-15 13:39:15.294 CST [3790] DETAIL: Key (id)=(1) already exists.
2023-05-15 13:39:15.294 CST [3790] STATEMENT: insert into t_baoyw values(1,'BBBBB');
ERROR: duplicate key value violates unique constraint "pk_id"
DETAIL: Key (id)=(1) already exists.
postgres=# insert into t_baoyw values(2,'BBBBB');
INSERT 0 1
postgres=# select * from t_baoyw;
id | name
----+-------
1 | AAAAA
2 | BBBBB
(2 rows)
postgres=#
6.5 查看表的所属关系
# 数据库是默认的表空间pg_default时，不显示（其他显示）
# 如果不指定表的SCHEMA\OWNER\TABLESPACE，则都是数据库在新建是默认的。
# 默认的表空间名称，在PG_TABLES中不显示。
postgres=# select * from pg_tables where schemaname in ('public','bao');
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | t_hejp | postgres | ts_baoyw | f | f | f | f
public | t_baoyw | postgres | | t | f | f | f
public | t_xxx | postgres | | f | f | f | f
public | t_text | postgres | | f | f | f | f
public | t_baobao | postgres | | f | f | f | f
(5 rows)
postgres=# alter table t_xxx set tablespace ts_baoyw;
ALTER TABLE
postgres=# select * from pg_tables where schemaname in ('public','bao');
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | t_hejp | postgres | ts_baoyw | f | f | f | f
public | t_baoyw | postgres | | t | f | f | f
public | t_text | postgres | | f | f | f | f
public | t_baobao | postgres | | f | f | f | f
public | t_xxx | postgres | ts_baoyw | f | f | f | f
(5 rows)
postgres=#
6.6 查看表结构
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | contacts | table | postgres
public | t_baobao | table | postgres
public | t_baoyw | table | postgres
public | t_hejp | table | postgres
public | t_text | table | postgres
public | t_xxx | table | postgres
public | users | table | postgres
(7 rows)
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | contacts | table | postgres
public | t_baobao | table | postgres
public | t_baoyw | table | postgres
public | t_hejp | table | postgres
public | t_text | table | postgres
public | t_xxx | table | postgres
public | users | table | postgres
(7 rows)
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+----------+-------------+---------------+------------+-------------
public | contacts | table | postgres | permanent | heap | 8192 bytes |
public | t_baobao | table | postgres | permanent | heap | 0 bytes |
public | t_baoyw | table | postgres | permanent | heap | 8192 bytes |
public | t_hejp | table | postgres | permanent | heap | 0 bytes |
public | t_text | table | postgres | permanent | heap | 0 bytes |
public | t_xxx | table | postgres | permanent | heap | 0 bytes |
public | users | table | postgres | permanent | heap | 16 kB |
(7 rows)
postgres=#
7 视图
# 创建表
postgres=# create table users(id int,name varchar(20),password varchar(256),email text);
CREATE TABLE
# 创建视图
postgres=# create view vw_users as select id,name,email from users;
CREATE VIEW
# 插入数据
postgres=# insert into users values(1,'天帝','123456','86771599@qq.com');
INSERT 0 1
# 更改视图名称
postgres=# alter view vw_users rename to v_users;
ALTER VIEW
# 修改表中数据
postgres=# update users set email='postgresql@qq.com' where id = 1;
UPDATE 1
# 删除视图
postgres=# drop view v_users;
DROP VIEW
postgres=#
8 索引
create table contacts(id int primary key,name varchar(20),phone varchar(32)[],address text);
create index idx_contacts_name on contacts(name);
create index idx_contacts_phone on contacts using gin(phone);
CREATE INDEX CONCURRENTLY idx_contacts_name_phone on contacts (name,phone);
drop index idx_contacts_name;
8.1 查看索引
postgres=# select * from pg_indexes where tablename = 'contacts';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-------------------------+------------+-----------------------------------------------------------------------------------
public | contacts | contacts_pkey | | CREATE UNIQUE INDEX contacts_pkey ON public.contacts USING btree (id)
public | contacts | idx_contacts_phone | | CREATE INDEX idx_contacts_phone ON public.contacts USING gin (phone)
public | contacts | idx_contacts_name_phone | | CREATE INDEX idx_contacts_name_phone ON public.contacts USING btree (name, phone)
public | contacts | idx_contacts_name | | CREATE INDEX idx_contacts_name ON public.contacts USING btree (name)
(4 rows)
postgres=#
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+--------------------+-------+----------+----------+-------------+---------------+------------+-------------
public | contacts_pkey | index | baoyiwen | contacts | permanent | btree | 8192 bytes |
public | idx_contacts_name | index | baoyiwen | contacts | permanent | btree | 8192 bytes |
public | idx_contacts_phone | index | baoyiwen | contacts | permanent | gin | 16 kB |
(3 rows)
9 查看PG归档
postgres=# show archive_mode;
archive_mode
--------------
off
(1 row)
postgres=# select * from pg_stat_archiver;
archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_reset
----------------+-------------------+--------------------+--------------+-----------------+------------------+-------------------------------
0 | | | 0 | | | 2023-05-12 16:45:36.443169+08
(1 row)
postgres=#
10 查看PG启动时间
postgres=# select * from pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2023-05-15 11:15:01.294831+08
(1 row)
11 查看PG创建时间
postgres=# select * from pg_control_system();
pg_control_version | catalog_version_no | system_identifier | pg_control_last_modified
--------------------+--------------------+---------------------+--------------------------
1300 | 202107181 | 7222954859684529381 | 2023-04-21 16:47:54+08
(1 row)
