docker安装
PG版本
13.2
安装
#1. 宿主机:创建mount目录
mkdir -p /custom/mount/mypg1
cd /custom/mount/mypg1
#2. 获取默认配置,并存放在宿主机目录
docker run -i --rm postgres:13.2 cat /usr/share/postgresql/postgresql.conf.sample > my-postgres.conf
#3. 创建容器
docker run -d \
--name mypg1 \ # 容器名称 mypg1
-p 5432:5432 \ # 端口映射
-e PGDATA=/var/lib/postgresql/data/pgdata \ # 设置 padata目录
-v /custom/mount/mypg1/data:/var/lib/postgresql/data \ # pgdata目录 挂载至宿主机目录
-v /custom/mount/mypg1/my-postgres.conf:/etc/postgresql/postgresql.conf \ # 配置文件 映射至 宿主机文件
-e POSTGRES_PASSWORD=admin \ # 设置postgres 密码
postgres:13.2 \ # 指定镜像版本: 13.2
-c 'config_file=/etc/postgresql/postgresql.conf' # 指定配置文件
容器创建结果
验证
#方法1
#进入容器
[root@localhost mount]# docker exec -it mypg1 /bin/bash
#使用psql终端命令连接
# -U 指定用户, -d 指定数据库
root@f4b1c4d15d5b:/# psql -U postgres -d postgres
# 方法2
[root@localhost mount]# docker exec -it mypg1 psql -U postgres -d postgres
# 检验
postgres=# select current_database();
current_database
------------------
postgres
(1 row)
简单命令
# 1. 创建数据库 db01
postgres=# create database db01;
# 2. 查看数据库清单
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
db01 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
....
(4 rows)
# 3. 选择数据库
postgres=# \c db01;
You are now connected to database "db01" as user "postgres".
db01=#
# 4. 创建表
db01=# CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
# 5. 查看表创建状态
db01=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | company | table | postgres
(1 row)
# 6. 查看表详情
db01=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
# 7. 创建schema
## PostgreSQL 模式(SCHEMA)可以看着是一个表的集合。
## 一个模式可以包含视图、索引、数据类型、函数和操作符等。
db01=# create schema myschema1;
CREATE SCHEMA
# 8. help
db01-# \?
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\di[S+] [PATTERN] list indexes
\dn[S+] [PATTERN] list schemas
\l[+] [PATTERN] list databases
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "db01")
# 9. 退出psql
db01-# \q
权限
创建用户
# 1. 创建一个用户
db01=# CREATE USER scott WITH PASSWORD 'tiger';
CREATE ROLE
# 2.修改密码
postgres=# ALTER USER scott WITH PASSWORD '123456';
ALTER ROLE
GRANT 语法
GRANT privilege [, ...] # 值可以为:SELECT,INSERT,UPDATE,DELETE, RULE,ALL。
ON object [, ...] # 可能的对象有: table, view,sequence。
TO { PUBLIC | GROUP group | username } # PUBLIC:表示所有用户, GROUP group − 为用户组授予权限, username:要授予权限的用户名
表权限
# 1. 选择数据库
postgres-# \c db01;
# 2. 将 COMPANY表 的权限 授予 scott
db01=# GRANT ALL ON COMPANY TO scott;
GRANT
# 3. 取消授权
runoobdb=# REVOKE ALL ON COMPANY FROM scott;
REVOKE
数据库权限
postgres=# CREATE DATABASE testdb OWNER scott;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO scott;