PostgreSQL

---

title: PostgreSQL date: 2018-12-16 17:27:21 tags: - PostgreSQL categories: - 数据库

目录 start

1. Postgresql
   1. 概述
   2. 和MySQL对比
   3. 安装
      1. 安装客户端
      2. 安装服务端
      3. Docker方式安装服务端
         1. pull完整版
         2. pull精简版
         3. Dockerfile构建
         4. 解释Dockerfile文件
   4. 使用
      1. Postgresql终端命令行使用
      2. 用户和角色权限
         1. 创建用户
         2. 修改权限
      3. Java使用
   5. 基础数据

目录 end|2020-04-27 23:42|

---

Postgresql

• 该公司对于PostgreSQL的缺点陈列是否属实

概述

PostgreSQL | wiki

• 严格SQL标准
• Schemas 和表，用户的关系：
  • Schemas相当于是一个数据库进行分类的文件夹

和MySQL对比

PostgreSQL 与 MySQL 相比，优势何在？ Converting MySQL to PostgreSQL

安装

安装客户端

sudo apt-get install postgresql-client

安装服务端

sudo apt install postgresql

Docker方式安装服务端

官方镜像

pull完整版

• 或者： docker pull postgres Docker Hub
  • 运行容器 docker run --name mypostgre -i -t -p 5432:5432 postgres
  • 客户端连接 psql -h localhost -p 5432 -U postgres

pull精简版

• 下拉镜像：docker pull postgres:alpine | 因为个人系统客户端是9.6, 所以用9.6-alpine镜像
• 构建容器：

    docker run -d --name postgre \
    -e POSTGRES_PASSWORD=jiushi \
    -v gitea-db-data:/var/lib/postgresql/data \
    -p 5432:5432 \
    postgres:9.6-alpine

• 容器中连接 进入postgresql终端 docker exec -it postgre psql -U postgres
  • 客户端连接 psql -h localhost -U postgres
• 连接后 输入\l 列出所有数据库 即可查看连接成功与否

Dockerfile构建

Dockerfile

    FROM ubuntu:16.04
    RUN apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8
    RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list
    RUN apt-get update && apt-get -y -q install python-software-properties software-properties-common \
        && apt-get -y -q install postgresql-9.4 postgresql-client-9.4 postgresql-contrib-9.4
    USER postgres
    RUN /etc/init.d/postgresql start \
        && psql --command "CREATE USER pger WITH SUPERUSER PASSWORD 'pger';" \
        && createdb -O pger pgerdb
    USER root
    RUN echo "host all  all    0.0.0.0/0  md5" >> /etc/postgresql/9.4/main/pg_hba.conf
    RUN echo "listen_addresses='*'" >> /etc/postgresql/9.4/main/postgresql.conf
    EXPOSE 5432
    RUN mkdir -p /var/run/postgresql && chown -R postgres /var/run/postgresql
    VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]
    USER postgres
    CMD ["/usr/lib/postgresql/9.4/bin/postgres", "-D", "/var/lib/postgresql/9.4/main", "-c", "config_file=/etc/postgresql/9.4/main/postgresql.conf"]

• 构建容器 docker build -t mypostgresql:9.4 .
  • 运行容器 docker run --name mypostgre -i -t -p 5432:5432 mypostgresql:9.4
  • 使用客户端连接psql -h localhost -p 5432 -U pger -W pgerdb 口令：pger

解释Dockerfile文件

待学习解释

---

使用

PostgreSQL 9.6.0 手册

Postgresql终端命令行使用

PostgreSQL新手入门 用熟悉的MySQL命令来解释

• \l show databases
• \c dbname [user] 切换数据库
• \dt show tables
• \d tablename desc tablename
• \di 查看索引
• \du 查看所有用户
• \dn 查看模式列表
• \copyright 显示版权信息
• \encoding 显示编码信息
• \h SQL命令语法上的说明，用*显示全部命令
• \prompt [文本]名称 提示用户设定内部变数
• \password [username] 改密码
• \q exit
• 可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：
  • pg_dump drupal>/opt/Postgresql/backup/1.bak

用户和角色权限

创建用户

• createuser -P -D -R -e playboy //创建一个用户,-P要设置密码，-R,不参创建其他用户，-D不能创建数据库

• create user myth 不带login属性

• create role myth 具有login属性

• psql -U playboy -d playboy 登录用户，一般默认是有用户同名数据库才能登录

• 修改默认登录不需要密码的配置

修改权限

参考博客 参考博客_角色 配置

• ALTER ROLE rolename LOGIN; 设置登录权限

• ALTER ROLE david WITH PASSWORD 'ufo456'; 设置密码登录权限

  • 但是，默认是不需要密码 查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码
  • 将local 的METHOD 更改为password，然后保存重启postgresql。
  • 博客

• ALTER ROLE sandy VALID UNTIL '2014-04-24'; 设置角色有效期

• SELECT * from pg_roles ; 查看所有角色

• CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';

  • 在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。

• GRANT CONNECT ON DATABASE test to father; 角色赋予数据库test 连接权限和相关表的查询权限。

Java使用

Postgresql JDBC Driver

• 官方：springboot使用
  • 参考博客

基础数据

PostgreSQL中的数据类型