PostgreSQL知识点汇总

原创已于 2025-11-23 21:29:48 修改 · 44 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #PG #pg数据库 #库 #表 #索引

于 2025-07-01 10:20:42 首次发布

1. 连接数据库

#连接数据库
psql -h hostname -p portnumber -U username -d databasename

psql -h  127.0.0.1  -p 5432  -U cloud_test   -d  cloud

成功连接并进入 psql交互界面后，以下是一些非常实用的元命令（以反斜杠 `\`开头）：
\l或 \list：列出所有可用的数据库
\c <database_name>：切换到另一个数据库，例如 \c mydb
\dt：列出当前数据库中的所有表
\d <table_name>：显示指定表的结构（字段名、类型等）
\q：退出 psql命令行工具


pg_dump --file "/root/pgsql-all20250701.backup" --host "127.0.0.1" --port "5432" --username "lmzf_pg_test" --dbname "cloud" --verbose --format=c --blobs --encoding "UTF8"


#创建数据库
createdb -h 127.0.0.1 -p 5432  -U postgres cloud

createdb -h 127.0.0.1 -p 5432 -U postgres database_name -E UTF8 --lc-collate=C --lc-ctype=C

createdb -h 127.0.0.1 -p 5432 -U postgres   cloud    -T template0 -E UTF8  --lc-collate=C  --lc-ctype=C


#导入数据库
pg_restore  --host "10.10.14.133" --port "5432" --username "postgres"   --dbname "cloud"  --verbose  /root/pgsql-all20250701.backup

2. 查询数据库

2.1 查看单个数据库大小（以易读格式显示）

SELECT pg_size_pretty(pg_database_size('数据库名'));

2.2 查看所有数据库大小（列表形式）

SELECT 
    datname AS "数据库名",
    pg_size_pretty(pg_database_size(datname)) AS "大小"
FROM pg_database;

2.3. 查看所有数据库总大小

SELECT pg_size_pretty(SUM(pg_database_size(datname))) AS "总空间"
FROM pg_database;

3. 查询表

3.1 查询当前库所有模式下所有表的数据大小，索引大小，表大小，并按表大小倒序排列

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

3.2 统计当前数据库中指定模式下所有表的大小

public根据实际情况修改

SELECT table_schema || '.' || table_name AS full_table_name,
          pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS total_size
   FROM information_schema.tables
   WHERE table_schema = 'public' -- 可修改为其他模式
   ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

3.3 当前模式中所有表的大小、表相关对象的大小以及总表大小

SELECT stats.relname
           AS table,
       pg_size_pretty(pg_relation_size(statsio.relid))
           AS table_size,
       pg_size_pretty(pg_total_relation_size(statsio.relid) 
           - pg_relation_size(statsio.relid))
           AS related_objects_size,
       pg_size_pretty(pg_total_relation_size(statsio.relid))
           AS total_table_size,
       stats.n_live_tup
           AS live_rows
  FROM pg_catalog.pg_statio_user_tables AS statsio
  JOIN pg_stat_user_tables AS stats
 USING (relname)
 WHERE stats.schemaname = current_schema  -- Replace with any schema name
 UNION ALL
SELECT 'TOTAL'
           AS table,
       pg_size_pretty(sum(pg_relation_size(statsio.relid)))
           AS table_size,
       pg_size_pretty(sum(pg_total_relation_size(statsio.relid) 
           - pg_relation_size(statsio.relid)))
           AS related_objects_size,
       pg_size_pretty(sum(pg_total_relation_size(statsio.relid)))
           AS total_table_size,
       sum(stats.n_live_tup)
           AS live_rows
  FROM pg_catalog.pg_statio_user_tables AS statsio
  JOIN pg_stat_user_tables AS stats
 USING (relname)
 WHERE stats.schemaname = current_schema  -- Replace with any schema name
 ORDER BY live_rows ASC;

4. SQL格式备份与恢复

#pg_dump备份
#pg_dump支持多种输出格式，通过 -F参数指定,纯文本格式 (-F p)
#语法格式
pg_dump -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${TARGET_DB_NAME}  -Fp 
-f  ${BACKUP_FILE}
pg_dump  -h  10.10.10.180   -p 32371  -U  postgres    -d   lmzf_db  -Fp  -f  lmzf_cloud_202511232052.sql

#psql恢复
#纯文本格式的恢复：如果您当时使用的是纯文本格式（-Fp或默认）备份，生成的是 .sql文件，则应使用 psql工具进行恢复
#语法格式
psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${TARGET_DB_NAME} -f ${BACKUP_FILE}