Cheatsheat

导出数据库到 .sql 文件

psql 使用指南

参考：

表格
\dtlist tables列出所有表格
\dtable schema
\d+table schema detail
数据库
\l\list databases列举数据库
\l+list databases detail
\h CREATE DATABASEhelp on create database创建帮助
CREATE DATABASE创建
\c <my-db>\connect database连接，切换 db

术语

meta-commands

形如 \dt, \l+ 等的命令就是 meta-commands

FAQ

list tables 所有表格信息列举

\dt 可以和通配符一起使用

  1. \dt ***.* : 所有 schema 下的表

  2. \dt <name-of-schema>.* : 当前指定 schema 下的表格

    • eg: \dt public.*; public schema 下的所有表格

导出成 csv

\COPY SELECT * FROM <my-table> TO "/path/to/file.csv" WITH CSV

拼接 Group by 字符串结果

  • 使用 string_agg

    1
2
3

    SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;
  • 参考

  • 例子

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

    select
tab.pdf_name
, tab.pdf_id
, tab.paragraph_id
, string_agg(tab.sentence, ' ')
from
(select
pdf_metadata.pdf_name
, pdf_tei.pdf_id
, pdf_tei.paragraph_id
, pdf_tei.sentence_id
, pdf_tei.sentence
from pdf_tei inner join pdf_metadata  on pdf_tei.pdf_id = pdf_metadata.id  where pdf_metadata.pdf_name ~ '^2009.*$'
order by pdf_tei.pdf_id, pdf_tei.paragraph_id, pdf_tei.sentence_id) as tab
group by
tab.pdf_name
, tab.pdf_id
, tab.paragraph_id

docker compose

实现一

参考：

功能：

  1. postgres
  2. adminer
  3. pgadmin4
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# Use postgres/example user/password credentials
version: '3.1'

services:

  postgres:
    image: postgres
    restart: always
    volumes:
      - ./pg-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=12345678
      - POSTGRES_USER=matgene
      - POSTGRES_DB=postgres
    ports:
      - "15432:5432"
      - '172.17.0.1:5432:5432'

  adminer:
    image: adminer
    restart: always
    ports:
      - 10180:8080


  pgadmin4:
    image: elestio/pgadmin:latest
    restart: always
    environment:
      - PGADMIN_DEFAULT_EMAIL=${ADMIN_EMAIL:-js@js.com}
      - PGADMIN_DEFAULT_PASSWORD=${ADMIN_PASSWORD:-js123}
      - PGADMIN_LISTEN_PORT=8080
    ports:
      - "10181:8080"
      - '172.17.0.1:8080:8080'
    volumes:
      - ./servers.json:/pgadmin4/servers.json

备份和重建

数据库整体备份

使用 pg_dump

注意：

  1. 可能会遇到 pg_dump client version < pg_dump server version 的问题

    • 解决方法： 使用 docker 临时调用一个 版本高的 postgresql 镜像中的 pg_dump 命令
1

pg_dump  -U postgres -d knowledge_db_v3 -h 172.16.10.83 -p 5432 > /tmp/dump_knowledge_db_v3.sql