postgresql工具

export PGPASSWORD='user123456'

# pg_dump 逻辑备份工具, 支持二进制导出

# 关键参数
-O      不指定对象的所有者
-c      在重新创建前, 先删除对象
-x      不转储权限信息
-F d    目录存储
-b      二进制
-f      指定位置

# 普通逻辑备份
pg_dump -h 172.27.0.50 -U user1 -d db1 > db1.sql

# 备份表结构
pg_dump -U user1 -s db1 > db1_table_info.sql

# 不包括权限和属主信息, 包括删除对象的操作
pg_dump -U user1 -d db1 -x -O -c -f db1.sql

# 二进制, 目录格式 - 推荐
pg_dump -U user1 -d db1 -F d -b -c -f /backup/db1-20250904-1431
pg_dump -U user1 -d db1 -F d -b -x -O -c -f /backup/db1-20250904-1431

# 备份时并忽略指定表
pg_dump -U user1 -d db1 -O -x -c \
    --exclude-table-data=pre_commit \
    --exclude-table-data=tm_validators -f pro_db1.sql

psql -U user1 -d db1 -f 08281925_data.sql

create user user1 with password 'passw0rd';

-- 创建库并赋权限
CREATE DATABASE db1;
GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;


-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('me_hub'));

DROP SCHEMA myschema CASCADE;   -- 删除模式和其下的对象

# 查看数据库列表
psql -Upostgres -c '\l'


# 主机上安装不了高版本 psql 时, 使用 docker  客户端
docker run --name rds_tmp_task1 --rm -it \
    -e "PGPASSWORD=user123456" \
    -v /backup:/backup \
    -v /root/tmp:/tmpdata postgres:17.4 \
    psql -U user1 -d db1 -h database-1.xxxxxx.ap-southeast-1.rds.amazonaws.com

# login_db_me_rds.sh
export PGPASSWORD=user123456
psql -U user1 -d db1 -h database-1.xxxxxx.ap-southeast-1.rds.amazonaws.com


# 非交互式执行 sql

export PGPASSWORD=user123456
psql -h 192.168.0.206 -p 15432 -U user1 -d db1 -f drop_table.sql

echo ${SQL_TXT} | psql -h 192.168.0.206 -p 15432 -U user1 -d db1 -f -

dsn: postgres://user1:password@192.168.0.26:5432/db1?sslmode=disable

-- 查看关键配置参数
select name,setting from pg_settings where name in ('max_wal_size','min_wal_size','wal_segment_size','wal_keep_size','wal_level','wal_buffers','fsync');

# 不换行输出
\pset pager off

# 不换行输出 方法2/3
export PAGER=cat
psql -c "SELECT * FROM your_table" -P pager=off

# 输出查询
\o output.txt

-- 清空全部表
DO
$$
DECLARE
    table_name text;
BEGIN
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public')
    LOOP
        EXECUTE 'TRUNCATE TABLE "' || table_name || '" CASCADE';
    END LOOP;
END
$$
;

-- 删除 all 表, 有删除依赖对象
DO
$$
DECLARE
    table_name text;
BEGIN
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public')
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS "' || table_name ||'" CASCADE';
    END LOOP;
END
$$
;

-- 清空表, 并重置自增序列
TRUNCATE TABLE audit_logs CASCADE;
ALTER SEQUENCE audit_logs_id_seq RESTART;


-- 重置序列
DO
$$
DECLARE
    seq_name text;
BEGIN
    FOR seq_name IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema='public')
    LOOP
        EXECUTE 'ALTER SEQUENCE ' || seq_name || ' RESTART';
    END LOOP;
END
$$
;

-- 查看视图的 创建语句
SELECT pg_get_viewdef('public.my_view'::regclass);

explain sql_line;               -- 执行计划, 但实际没有执行
explain analyze sql_line;       -- 执行计划, 实际有执行

-- 按 指定时区进行查询
SELECT COUNT(1) FROM kyc WHERE created_at < ('2024-08-20'::date AT TIME ZONE 'Asia/Shanghai');

-- 查询序列
SELECT nextval('addresses_address_id_seq');

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('db1'));


-- 查询所有数据文件对象的大小
SELECT object_type, object_name, schema_name, size FROM
(SELECT
    'Table' AS object_type,
    relname AS object_name,
    schemaname AS schema_name,
    pg_total_relation_size(relid) as size_bytes,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM
    pg_catalog.pg_statio_user_tables
UNION
SELECT
    'Index' AS object_type,
    indexrelname AS object_name,
    schemaname AS schema_name,
    pg_total_relation_size(indexrelid) as size_bytes,
    pg_size_pretty(pg_total_relation_size(indexrelid)) AS size
FROM
    pg_catalog.pg_stat_all_indexes
) as data
ORDER BY
    size_bytes DESC;


--- 只查看表大小
SELECT
    'Table' AS object_type,
    relname AS object_name,
    schemaname AS schema_name,
    pg_total_relation_size(relid) as size_bytes,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM
    pg_catalog.pg_statio_user_tables
ORDER BY
    size_bytes DESC;