零壹

01

PostgreSQL 笔记

发表于 更新于 分类于
本文字数： 1.5k 阅读时长 ≈ 1 分钟

简介

The World’s Most Advanced Open Source Relational Database. —— PostgreSQL

部署

Docker

…

示例，其中 /usr/local/nakadi4cbh/database/nakadi 路径包含了初始化 Nakadi 数据库脚本。

1
2
3
4
5
6
7
docker run --name eventbus_pg -d \
   -p 5432:5432 \
   -e POSTGRES_USER=nakadi \
   -e POSTGRES_PASSWORD=nakadi \
   -e POSTGRES_DB=local_nakadi_db \
   -v /usr/local/nakadi4cbh/database/nakadi:/docker-entrypoint-initdb.d \
   postgres:9.6.16

psql

  • psql

  • psql -U username

    1
    2
    3
    4
    5
    bash-4.2$ psql
    psql (12.2)
    Type "help" for help.

    postgres=#

    Getting information about databases

Command List

  • \h Help

  • \l List databases

  • \l+ List databases with size, tablespace, and description

  • \x Expand/narrow table lists

  • \c Connect to a database

  • \dt Display tables

  • \d and \d+ Display columns (field names) of a table

  • \du Display user roles

  • \q quit

Command Usage

  • \l 数据库列表

    1
    postgres=# \l

  • \c 连接数据库

    1
    2
    3
    postgres=# \c icosregistry_icos
    You are now connected to database "icosregistry_icos" as user "postgres".
    icosregistry_icos=#

    SQL

  • Create Database & User, Grant 

1
2
3
create database keycloak;
create user keycloak with password 'keycloak';
grant all privileges on database keycloak to keycloak;

  • Query Data From Table

    1
    2
    3
    4
    5
    6
    icosregistry_icos=# select * from asset_deploy;
     asset_code | handler_type | opr_type | opr_status | opr_message | deploy_url | created 
    ------------+--------------+----------+------------+-------------+------------+---------
    (0 rows)

    icosregistry_icos=#

pg_dump [OPTION]… [DBNAME]

Options

-s -s, –schema-only dump only the schema, no data

-t -t, –table=PATTERN dump the specified table(s) only

Examples

  • 导出表结构示例
1
pg_dump -s -t asset -t asset_group -t asset_group_membership -t asset_relationship -t asset_relationship_type -t asset_type -t asset_deploy icosregistry_icos

（待续）