psql — PostgreSQL interactive terminal

https://www.postgresql.org/docs/current/app-psql.html

常用命令

命令  含义
\d  列出当前数据库的所有表，不区分类型
\d tablename    查看表结构
\dt tablename   查看表信息
\d 索引   查看索引信息
\db 查看所有表空间
\dg 列出所有角色和用户
\dp tablename   查看表的权限分配情况

\l 列出所有用户
\i ~/data.sql 导入数据

导出

/Applications/pgAdmin 4.app/Contents/SharedSupport/pg_dump --file "/Users/songgs/Desktop/_projects/CRM-MKT/data/odoo_order.sql" --host "pgm-f.aliyuncs.com" --port "5432" --username "read_only" --no-password --verbose --format=c --blobs --no-owner --no-privileges --no-tablespaces --no-unlogged-table-data --inserts --column-inserts --table "public.sale_order" "db_name"

查询表体积

SELECT table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size,
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') as total_size
FROM information_schema.tables
ORDER by total_size DESC 
limit 50;
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 ;