PostgreSQL

\l 或 \list meta-command 列出所有数据库
sudo -u postgres psql -c "\l"
用 \c + 数据库名 来进入数据库：
\dt 列出所有数据库表：

# 查看表结构, 索引
\d table0

# 比上面多几个字段 Storage  | Stats target | Description
\d+ table0

# Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only.
\t tuples only on/off, tuples only on 的时候 select 语句的输出不带 header

\h
\?

\du 列出所有的用户

# 创建用户
CREATE USER user_0 WITH PASSWORD 'password_0';

# create database, 所有者 user_0
create database database_0 OWNER user_0;

psql -h 192.168.1.100 -p 5432 -U user_0 -d database_0

PGPASSWORD=password_0 psql -h 192.168.1.100 -p 5432 -U user_0 -d database_0 --command 'select version();'

#当表没有其他关系时
TRUNCATE TABLE tablename;

#当表中有外键时，要用级联方式删所有关联的数据
TRUNCATE TABLE tablename CASCADE;

# 查看 表大小
select pg_size_pretty(pg_relation_size('table0'));

# 查看配置文件路径, 切换到 postgres 用户执行
psql -c "show config_file"

# 查看版本
select version();

pacman -S postgresql
psql -h 127.0.0.1 -p 5432 -d database0 -U user0

# create table
create table test(id int, c1 int);
create table table0(field0 json);

# delete table
DROP TABLE table0;
# 查看字段类型
select column_name, data_type from information_schema.columns where table_name='table0';


select * length( "abc"::TEXT)
insert into test select generate_series(1,10000), random()*10;

# 复制表结构到另外一个数据库
pg_dump -U postgres --schema-only source_db | psql -U postgres target_db

# https://www.postgresql.org/download/linux/ubuntu/
# install pg_dump
sudo apt install -y postgresql-common

# This script will enable the PostgreSQL APT repository on apt.postgresql.org
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# ubuntu install pg_dump
sudo apt-get install postgresql-client-17

# -h, host 127.0.0.1
# -p, port 5432
# -t, table: table0, 不加 -t 参数时会导出所有表结构
# -s, 不导出数据
# database: database0
# -F : 指定输出文件的格式，它可以是以下格式之一： c: 自定义格式 d: 目录格式存档 t: tar 文件包 p: SQL 脚本文件
# -W 命令执行时提示输入用户密码（不会直接在命令中写密码）。
pg_dump -h 127.0.0.1 -U username -W -F t db_name > foo.tar

# 导入
# -c --clean	创建数据库对象前先清理（删除）它们。
pg_restore -h 127.0.0.1 -U username -W -d db_name -c foo.tar

pg_dump -h 127.0.0.1 -p 5432 -t table_0 -U postgres database0 > foo.sql
pg_dump -h 127.0.0.1 -p 5432 -s -t table_0 -U postgres database0 > foo.sql

# 导出并压缩
pg_dump -d db_name | gzip > db.gz

pg_dump -a -t table_0 "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=user_0 password=password_0 dbname=db_0"
# export insert sql
pg_dump -a -t table_0 "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=user_0 password=password_0 dbname=db_0" --inserts

# sql
psql -h 127.0.0.1 -p 5432 -t table0 -U postgres -d database0 -f foo.sql

# csv, https://stackoverflow.com/questions/26701735/extra-data-after-last-expected-column-while-trying-to-import-a-csv-file-into-p
\COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';

# 按过滤条件导出成 csv, 注意: copy 里面的 select SQL 结尾不能有分号
COPY (
SELECT * FROM table_0 WHERE id = '1'
) TO '/tmp/foo.csv';

# 导出 CSV 并自定义 NULL 值表示
# 默认情况下 NULL 值会导出为 \N
COPY (
SELECT * FROM table_0 WHERE id = '1'
) TO '/tmp/foo.csv' WITH (FORMAT CSV, HEADER, NULL 'NULL');

# 或者使用空字符串表示 NULL
COPY (
SELECT * FROM table_0 WHERE id = '1'
) TO '/tmp/foo.csv' WITH (FORMAT CSV, HEADER, NULL '');

# 完整的 CSV 导出选项
COPY (
SELECT * FROM table_0 WHERE id = '1'
) TO '/tmp/foo.csv' WITH (
    FORMAT CSV,           -- CSV 格式
    HEADER true,          -- 包含表头
    DELIMITER ',',        -- 分隔符
    NULL 'NULL',          -- NULL 值表示为 'NULL' 而不是 \N
    QUOTE '"',            -- 引号字符
    ESCAPE '"',           -- 转义字符
    ENCODING 'UTF8'       -- 编码
);

# 导入 csv (默认 \N 为 NULL)
COPY table_0 FROM '/tmp/foo.csv';

# 导入 CSV 并指定 NULL 值表示
COPY table_0 FROM '/tmp/foo.csv' WITH (FORMAT CSV, HEADER, NULL 'NULL');

# 按过滤条件导出成 INSERT SQL 语句
# 方法1: 使用 psql 生成 INSERT 语句
psql -h 127.0.0.1 -p 5432 -U postgres -d database0 -t -A -c "
SELECT 'INSERT INTO table_0 VALUES (' || 
       quote_literal(field1) || ',' || 
       quote_literal(field2) || ');' 
FROM table_0 
WHERE city = 'tokio';" > foo.sql

# 方法2: 使用 pg_dump 配合临时表或视图
# 先创建临时视图
psql -h 127.0.0.1 -p 5432 -U postgres -d database0 -c "
CREATE TEMP VIEW temp_view AS 
SELECT * FROM table_0 WHERE city = 'tokio';"

# 然后导出视图数据为 INSERT 语句
pg_dump -h 127.0.0.1 -p 5432 -U postgres -d database0 -t temp_view -a --inserts > foo.sql

CREATE USER user_0 WITH PASSWORD 'password_0';
CREATE DATABASE db_0 OWNER user_0;
GRANT ALL PRIVILEGES ON DATABASE db_0 TO user_0;

# 查看配置文件路径, ubuntu 24.04 apt 安装的 postgresql
sudo -u postgres psql -c "SHOW config_file;"

# 默认值: off, 改成 on 之后重启 postgresql 就开始写日志了, 日志目录会是 /var/lib/postgresql/data/pgdata/log/
logging_collector = on

# none, ddl, mod, all ---- 控制记录哪些 SQL 语句。
# none 不记录，(默认值)
# ddl 记录所有数据定义命令，比如 CREATE, ALTER, 和 DROP 语句。
# mod 记录所有 ddl 语句, 加上数据修改语句 INSERT,UPDATE等,
# all 记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句。
log_statement = 'all'

# 日志目录名, 默认值: log, 使用默认值, 不需要修改
# log_directory = 'log'

# 默认文件名, 使用默认值, 不需要修改
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d ----  单个日志文件的生存期，默认 1 天，在日志文件大小没有达到 log_rotation_size 时，一天只生成一个日志文件
log_rotation_size = 10MB  ---- 单个日志文件的大小，如果时间没有超过 log_rotation_age，一个日志文件最大只能到 10M，否则将新生成一个日志文件。
log_truncate_on_rotation = off ---- 当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。
log_duration = off ---- 记录每条SQL语句执行完成消耗的时间，将此配置设置为 on, 用于统计哪些 SQL 语句耗时较长。

10.log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running at least this number of milliseconds

# -1表示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。个人更喜欢使用该配置来跟踪那些耗时较长，可能存在性能问题的SQL语句。虽然使用log_statement和log_duration也能够统计SQL语句及耗时，但是SQL语句和耗时统计结果可能相差很多行，或在不同的文件中，但是log_min_duration_statement会将SQL语句和耗时在同一行记录，更方便阅读。

11.log_connections = off ----是否记录连接日志
12.log_disconnections = off ---- 是否记录连接断开日志

13.log_line_prefix = '%m %p %u %d %r ' ---- 日志输出格式（%m,%p实际意义配置文件中有解释）,可根据自己需要设置（能够记录时间，用户名称，数据库名称，客户端IP和端口，方便定位问题）
14.log_timezone = 'Asia/Shanghai' ---- 日志时区，最好和服务器设置同一个时区，方便问题定位

timezone = 'Asia/Shanghai'

pg_ctl reload

-- TO_DATE(text,format);
SELECT TO_DATE('20170103','YYYYMMDD');

-- to_timestamp(text, text)
to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
SELECT to_timestamp('10:49 2023/01/20', 'HH24:MI YYYY/MM/DD');

SELECT round(cast(date_part('epoch', start_time - end_time)/60/60 as numeric ),1) as time_diff_hours
FROM table0

# archlinux, 没有把 服务端和客户端分成不同的包
sudo pacman -S postgresql

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Create the repository configuration file:
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

# Update the package lists:
sudo apt update

# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-17' or similar instead of 'postgresql'
sudo apt -y install postgresql
sudo -u postgres psql
CREATE USER user_0 WITH PASSWORD 'password_0';

# 用 systemctl 启动 postgresql 服务
sudo systemctl restart  postgresql

# 默认配置文件位置 
 /etc/postgresql/17/main/postgresql.conf

# 日志 路径 
/var/lib/postgresql/17/main/log

nerdctl pull postgres:18.0
sudo mkdir -p /var/lib/postgresql/18/data
sudo chown -R 999:999 /var/lib/postgresql/18/data

# 默认用户名 POSTGRES_USER=postgres
sudo nerdctl run -d --name postgres --network host --restart=always -e POSTGRES_PASSWORD=password_0 -e POSTGRES_USER=postgres -v /var/lib/postgresql/18/data:/var/lib/postgresql/18/docker postgres:18.0

###
docker pull postgres:16.4

# 修改配置文件
log_timezone = 'UTC'
timezone = 'Asia/Shanghai'

# docker
# 默认用户名 postgres
docker run --name postgres \
--restart=always \
-p 5432:5432 \
-e POSTGRES_PASSWORD=password0 \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v postgres-data:/var/lib/postgresql/data \
-d postgres:16.4

# podman
podman run --name postgres \
-p 5432:5432 \
-e POSTGRES_PASSWORD=password0 \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v postgres-data:/var/lib/postgresql/data \
-d postgres:14.5

psql -h 127.0.0.1 -p 5432 -U postgres
# password: password0

# 重启服务
su -l postgres -c '/opt/pg9.6/bin/pg_ctl -D /mnt/pgdata start'

# 重载配置
su -l postgres -c '/opt/pg9.6/bin/pg_ctl -D /mnt/pgdata reload'

docker run -i --rm postgres:16.4 cat /usr/share/postgresql/postgresql.conf.sample > postgresql.conf

alter table foo
    rename to bar;

alter table foo
    add time0 timestamptz;

-- create table
create table table0(
  field0 json,
  create_time             timestamp with time zone default now(),
  );
 
-- rename column
alter table table_0
    rename column column_foo to column_bar;

PGPASSWORD=postgres psql -h 127.0.0.1 -p 5432 -d database0 -U user0  --command 'select version();'

PGPASSWORD=postgres psql -h 127.0.0.1 -p 5432 -d database0 -U user0 -f /path/to/foo.sql

--查询主键名称
SELECT
    pg_constraint.conname AS pk_name
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
    pg_class.relname = 'table_name_0'
AND pg_constraint.contype = 'p';

--查询主键的详细信息
SELECT
    pg_constraint.conname AS pk_name,
    pg_attribute.attname AS colname,
    pg_type.typname AS typename
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
    pg_class.relname = 'table_name_0'
AND pg_constraint.contype = 'p';

-- 方法1: 查询表上所有唯一约束的名称和字段
SELECT
    pg_constraint.conname AS constraint_name,
    pg_attribute.attname AS column_name
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
    AND pg_attribute.attnum = ANY(pg_constraint.conkey)
WHERE
    pg_class.relname = 'table_name_0'
    AND pg_constraint.contype = 'u'  -- 'u' 表示 unique constraint
ORDER BY pg_constraint.conname, pg_attribute.attnum;

-- 方法2: 使用 information_schema 查询唯一约束
SELECT
    tc.constraint_name,
    kcu.column_name
FROM
    information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE
    tc.constraint_type = 'UNIQUE'
    AND tc.table_name = 'table_name_0'
ORDER BY tc.constraint_name, kcu.ordinal_position;

-- 方法3: 查看表结构时一起显示约束信息
\d table_name_0

-- 方法4: 查询唯一约束的详细信息（包括索引）
SELECT
    con.conname AS constraint_name,
    con.contype AS constraint_type,
    array_agg(att.attname ORDER BY array_position(con.conkey, att.attnum)) AS columns,
    pg_get_constraintdef(con.oid) AS constraint_definition
FROM
    pg_constraint con
JOIN pg_class rel ON con.conrelid = rel.oid
JOIN pg_attribute att ON att.attrelid = rel.oid AND att.attnum = ANY(con.conkey)
WHERE
    rel.relname = 'table_name_0'
    AND con.contype = 'u'
GROUP BY con.conname, con.contype, con.oid;

SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS')
-- 时区
to_char(NOW() AT TIME ZONE 'Asia/Dubai','YYYY-MM-DD HH24:MI:SS')

select now() + interval '1 days'; 
select now() + interval '1 month'; 
select now() + interval '1 years'; 
select NOW(), NOW() - interval '1 hours 4 minutes';

string||string

-- 单引号转义 ''
UPDATE user SET username = 'Peter''s Name' WHERE id = 1;

SELECT to_number('12345', '9999999999999999999')//12345
SELECT to_number('12345', '99999')//12345
SELECT to_number(''||12345, '9999')//1234，由于模式是4位，结果忽略最后一位；
SELECT to_number('    12345', '9999999999999999999')//12345
SELECT to_number('  ab  ,1,2a3,4b5', '9999999999999999999')//12345，会忽略所有字符串中非数字字符

CREATE SEQUENCE seq_0 START 1;
CREATE SEQUENCE seq_0 INCREMENT 1 MINVALUE 1 START 1 CACHE 1;  

-- 查看 seq 定义
\d+ seq_0

-- 查看 seq 的当前值, 查询不会导致 sequence 值变化 , 每次查都 是一样的, 如果 没有其它客户端调用 nextval()
SELECT last_value FROM sequence_name;

-- 查看 seq 的下一个值, 每查一次 seq 值 会加 1
SELECT nextval('seq_0')

-- 更新 seq 的值 
select setval('seq_0 ', 88);

\d [tablename]
"table_name_id_fkey" FOREIGN KEY (id) REFERENCES other_table(id) ....
-- 然后使用下面的命令删除外键：

ALTER TABLE [tablename] DROP CONSTRAINT table_name_id_fkey;

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
FROM
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'table0';

-- 插入 UTC+3 时间
INSERT INTO table_name (created_at) 
VALUES ('2025-12-15 15:00:00+03:00');

-- 数据库实际存储为: 2025-12-15 12:00:00+00 (UTC+0)

-- 查询时，如果客户端时区是 UTC+3
SELECT created_at FROM table_name;
-- 返回: 2025-12-15 15:00:00+03

-- 如果客户端时区是 UTC+8
SELECT created_at FROM table_name;
-- 返回: 2025-12-15 20:00:00+08

-- 不管服务器在哪个时区，存储都是 UTC+0
-- 场景1: PostgreSQL 服务器在中国（UTC+8）
INSERT INTO table_name (ts) VALUES ('2025-12-15 15:00:00+03:00');
-- 存储为: 2025-12-15 12:00:00+00 (UTC+0)

-- 场景2: PostgreSQL 服务器在美国（UTC-5）
INSERT INTO table_name (ts) VALUES ('2025-12-15 15:00:00+03:00');
-- 存储为: 2025-12-15 12:00:00+00 (UTC+0)
-- 结果完全相同！

from datetime import datetime, timezone
import pytz

# 创建 UTC+3 时间
tz = pytz.timezone('Europe/Moscow')  # UTC+3
dt = datetime.now(tz)

# 直接插入，PostgreSQL 会自动转换为 UTC 存储
cursor.execute(
    "INSERT INTO table_name (created_at) VALUES (%s)", 
    (dt,)
)

import java.time.ZonedDateTime;
import java.time.ZoneId;

// 创建带时区的时间对象
ZonedDateTime dt = ZonedDateTime.now(ZoneId.of("Europe/Moscow")); // UTC+3

// 直接插入，JDBC 会正确处理时区
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO table_name (created_at) VALUES (?)"
);
pstmt.setObject(1, dt);
pstmt.executeUpdate();

-- 查看当前时区
SHOW timezone;

-- 设置数据库级别时区（推荐）
ALTER DATABASE your_db SET timezone = 'UTC';

-- 或设置会话级别
SET timezone = 'UTC';

-- 或设置为具体时区
SET timezone = 'Europe/Moscow';  -- UTC+3

-- ✅ 推荐：数据库设为 UTC
ALTER DATABASE your_db SET timezone = 'UTC';

-- 应用层使用带时区的时间
-- timestamptz 会自动转换为 UTC 存储
INSERT INTO table_name (created_at) 
VALUES ('2025-12-15 15:00:00+03:00');

-- timestamp without time zone
-- 不存储时区信息，不做任何转换
CREATE TABLE test1 (ts timestamp);
INSERT INTO test1 VALUES ('2025-12-15 15:00:00');
-- 存储什么就是什么，不关心时区

-- timestamp with time zone (timestamptz)
-- 存储 UTC+0，读取时转换
CREATE TABLE test2 (ts timestamptz);
INSERT INTO test2 VALUES ('2025-12-15 15:00:00+03:00');
-- 存储为 UTC+0，显示时根据客户端时区转换

# ❌ 错误：不带时区信息
from datetime import datetime
dt = datetime.now()  # naive datetime
cursor.execute("INSERT INTO table_name (ts) VALUES (%s)", (dt,))
# 会按数据库/服务器时区解释，可能不是你想要的

# ✅ 正确：带时区信息
from datetime import datetime, timezone
dt = datetime.now(timezone.utc)  # 或指定具体时区
cursor.execute("INSERT INTO table_name (ts) VALUES (%s)", (dt,))

# ❌ 不推荐：手动转换
import pytz
from datetime import datetime

tz_moscow = pytz.timezone('Europe/Moscow')
dt_moscow = datetime.now(tz_moscow)
# 手动转换为 UTC
dt_utc = dt_moscow.astimezone(pytz.UTC)
cursor.execute("INSERT INTO table_name (ts) VALUES (%s)", (dt_utc,))
# 多余的操作，PostgreSQL 会自动转换

# ✅ 推荐：直接传入
dt_moscow = datetime.now(tz_moscow)
cursor.execute("INSERT INTO table_name (ts) VALUES (%s)", (dt_moscow,))
# PostgreSQL 自动转换为 UTC 存储

-- ❌ 避免混用
CREATE TABLE bad_table (
    ts1 timestamp,      -- 不带时区
    ts2 timestamptz     -- 带时区
);
-- 容易造成混乱和错误

-- ✅ 统一使用 timestamptz
CREATE TABLE good_table (
    created_at timestamptz,
    updated_at timestamptz
);

名字                        别名             描述
character varying [(n)]  varchar [ (n) ]  可变长字符串
character [(n)]          char [ (n) ]     定长字符串
timestamp                                   SQL标准要求仅仅将timestamp类型等于timestamp without time zone 类型
timestamp with time zone   TIMESTAMPTZ       PostgreSQL遵守这个行为。timestamptz 作为 timestamp with time zone 的缩写被接受；这是PostgreSQL 的一个扩展。

show timezone;
select * from pg_timezone_names where abbrev='+04';

# 临时设置时区, 退出 psql 之后失效
set time zone "Asia/Shanghai";

select now();
# 查看数据库可供选择的时区:
select * from pg_timezone_names;

SUBSTRING(string, start_position, length)
-- str_pos 

select * from pg_tables where tablename = 'my_tbl';

-- 查看锁
SELECT locker.pid,
       pc.relname,
       locker.mode,
       locker_act.application_name,
       least(query_start, xact_start)                        start_time,
       locker_act.state,
       CASE
           WHEN granted = 'f' THEN
               'wait_lock'
           WHEN granted = 't' THEN
               'get_lock'
           END                                               lock_satus,
       current_timestamp - least(query_start, xact_start) AS runtime,
       locker_act.query
FROM pg_locks locker,
     pg_stat_activity locker_act,
     pg_class pc
WHERE locker.pid = locker_act.pid
  AND NOT locker.pid = pg_backend_pid()
  AND application_name <> 'pg_statsinfod'
  AND locker.relation = pc.oid
  AND pc.reltype <> 0 --and pc.relname='t'
ORDER BY runtime desc;

--查看 PostgreSQL 正在执行的SQL  
SELECT procpid,
       start,
       now() - start AS lap,
       current_query
FROM (SELECT backendid,
             pg_stat_get_backend_pid(S.backendid)            AS procpid,
             pg_stat_get_backend_activity_start(S.backendid) AS start,
             pg_stat_get_backend_activity(S.backendid)       AS current_query
      FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S) AS S,
     pg_stat_activity pa
WHERE current_query <> '<IDLE>'
  and procpid <> pg_backend_pid()
  and pa.pid = s.procpid
  and pa.state <> 'idle'
ORDER BY lap DESC;

# 查找是否有 waiting  
ps -ef|grep postgres | grep wait  

--查看当前库表和索引的的大小并排序显示前20条
SELECT nspname,
       relname,
       relkind                                       as "type",
       pg_size_pretty(pg_table_size(C.oid))          AS size,
       pg_size_pretty(pg_indexes_size(C.oid))        AS idxsize,
       pg_size_pretty(pg_total_relation_size(C.oid)) as "total"
FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND nspname !~ '^pg_toast'
  AND relkind IN ('r', 'i')
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;

-- 最大连接数
show max_connections;
-- 当前连接
select * from pg_stat_activity;
select datname from pg_stat_activity group by datname;
select state from pg_stat_activity group by state;
select * from pg_stat_activity where datname='database0' limit 1;
select pid, query_start,query from pg_stat_activity where datname='database0' and state='idle';
-- 释放空闲连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle';

COPY (
  SELECT foo, bar FROM table0
)
TO '/tmp/foo.csv'
WITH csv header;

psql
\c database0
\copy table0(field0,field1,field2,"field3") from '/home/wiloon/tmp/foo.csv' delimiter ',' csv header;

select array_length(regexp_split_to_array(config,'"id":'),1)-1 from xxx;

### 操作系统上查看 WAL 发送进程或 WAL 接收进程
ps -ef | grep "wal" | grep -v "grep"
# 主库会有 postgres: walwriter, postgres: walsender 进程
# 从库只有 postgres: walreceiver 进程

# 通过 pg_controldata 命令查看数据库控制信息，内容包含 WAL 日志信息、checkpoint、数据块等信息，通过 Databasecluster state 信息可判断是主库还是备库
pg_controldata | grep cluster

-- psql 连接到 DB 之后, 可以这样判断
SELECT pg_is_in_recovery();
-- You can use pg_is_in_recovery() which returns True if recovery is still in progress(so the server is running in standby mode). Check the System Administration Functions for further informations.
-- 如果返回 t 说明是备库，返回 f 是主库

-- 判断当前库是主库还是从库
SELECT pg_is_in_recovery();
-- 返回 f (false) = 主库
-- 返回 t (true) = 从库（处于恢复模式）

# 操作系统层面查看进程
ps -ef | grep "wal" | grep -v "grep"
# 主库会有: postgres: walwriter, postgres: walsender
# 从库会有: postgres: walreceiver

# 通过 pg_controldata 查看
pg_controldata | grep cluster

-- 快速查看所有从库 IP
SELECT client_addr FROM pg_stat_replication;
-- 返回 0 条数据 = 没有从库连接，或当前是从库

-- 查看从库 IP 和基本状态
SELECT 
    client_addr AS slave_ip,
    state,
    sync_state
FROM pg_stat_replication;

-- 查看完整的复制连接状态和延迟
SELECT 
    client_addr AS slave_ip,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 简化版本，查看基本复制状态
SELECT 
    client_addr,
    state,
    sync_state,
    replay_lag
FROM pg_stat_replication;

-- 检查复制槽状态（如果使用了复制槽）
SELECT 
    slot_name,
    slot_type,
    database,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

-- 查看从库接收状态
SELECT 
    status,
    receive_start_lsn,
    receive_start_tli,
    received_lsn,
    received_tli,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time,
    slot_name
FROM pg_stat_wal_receiver;

-- 在从库上执行：查看主从延迟
SELECT 
    now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- 查看 WAL 应用状态
SELECT 
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    pg_last_xact_replay_timestamp() AS last_replay_time;

-- 检查是否配置了复制槽（如果使用复制槽）
SELECT * FROM pg_replication_slots;
-- 返回 0 条 = 可能没配置主从

-- 检查主库日志
-- 查看是否有从库连接失败的错误信息

# 检查主库配置文件
grep -E "wal_level|max_wal_senders|wal_keep_size" $PGDATA/postgresql.conf
# wal_level = replica (或 logical) 才能做主从
# max_wal_senders > 0 才能发送 WAL

# 检查主库访问控制
cat $PGDATA/pg_hba.conf | grep replication
# 需要有类似这样的配置才允许从库连接：
# host    replication    all    从库IP/32    md5

create table test(id int, c1 int);  
insert into test select generate_series(1,10000), random()*10;
select * from test limit 10;
select c1,count(*)  from test group by c1;
select c1,count(*), count(*) filter (where id<1000) from test group by c1;

--把'1234'转成整数

select cast('1234' as integer ) ;

--用substring截取字符串，从第8个字符开始截取2个字符：结果是12

select cast(substring('1234abc12',8,2) as integer)

---使用to_number函数来转换成整数

---to_number(text, text)  返回的类型 numeric     把字串转换成numeric   to_number('12,454.8-', '99G999D9S')

select to_number('12121','999999999')
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/xingxiupaioxue/article/details/78295118

-- 模糊查询，并且不区分大小写
select * from student where name ilike '%tom%'

CREATE INDEX index_0 ON table_0 (column_0);

select * from pg_indexes where tablename='network_discovery_routing_data';
select * from pg_statio_all_indexes where relname='network_discovery_routing_data';

select pg_relation_size('network_discovery_routing_data_time_stamp_idx');
select pg_relation_size('constraint_routing_id_time_stamp');
-- 按名字删除索引
DROP INDEX index_0;

--查看索引占用的空间 
SELECT 
    indexrelname AS indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_relation_size(indexrelid) AS index_size_bytes
FROM pg_stat_user_indexes 
WHERE indexrelname = 'index_0';

set enable_seqscan=off;set enable_bitmapscan = off;set enable_tidscan = off;explain (analyze,buffers,verbose) select count(*) from aken01;

set enable_seqscan = off;

synchronous_commit = off | local | remote_write | remote_apply | on

客户端发出 COMMIT
    ↓
PostgreSQL 立即返回成功
    ↓  (后台异步)
WAL 写入操作系统缓冲区
WAL 刷盘
WAL 发送给 Standby
Standby 写入并应用

客户端发出 COMMIT
    ↓
等待 WAL 刷入本地磁盘 (fsync)
    ↓
返回成功
    ↓  (后台异步)
WAL 发送给 Standby

客户端发出 COMMIT
    ↓
等待 WAL 刷入本地磁盘
等待 Standby 确认已将 WAL 写入并刷盘 (flush)
    ↓
返回成功

等待 Standby 确认已将 WAL 写入操作系统缓冲区 (write, 未 fsync)

客户端发出 COMMIT
    ↓
等待 WAL 刷入本地磁盘
等待 Standby 确认已将 WAL replay (应用到数据文件)
    ↓
返回成功

-- 查看当前值
SHOW synchronous_commit;

-- 数据库级别设置
ALTER DATABASE mydb SET synchronous_commit = 'remote_apply';

-- 会话级别设置 (只影响当前连接)
SET synchronous_commit = 'remote_apply';

-- 事务级别设置 (只影响当前事务)
BEGIN;
SET LOCAL synchronous_commit = 'remote_apply';
-- ... 业务操作
COMMIT;

# postgresql.conf on Primary
synchronous_commit = remote_apply
# * 表示任意一个 Standby 确认即可
# 也可以指定名称: synchronous_standby_names = 'standby1'
synchronous_standby_names = '*'

# recovery.conf 或 postgresql.conf on Standby (PG 12+)
primary_conninfo = 'host=primary_ip port=5432 user=replicator application_name=standby1'

# 等待超时后降级为异步提交, 避免 Primary 完全阻塞
# 默认值: 1s
wal_sender_timeout = 60s    # Standby 超时踢出同步列表

-- 查看当前同步状态
SELECT application_name, sync_state FROM pg_stat_replication;
-- sync_state = 'sync'      当前同步 Standby
-- sync_state = 'async'     异步 Standby
-- sync_state = 'potential' 候补同步 Standby

-- 获取排他锁 (阻塞, 直到获取成功)
SELECT pg_advisory_lock(key bigint);

-- 尝试获取排他锁 (非阻塞, 成功返回 true, 失败返回 false)
SELECT pg_try_advisory_lock(key bigint);

-- 获取共享锁 (多个会话可同时持有共享锁)
SELECT pg_advisory_lock_shared(key bigint);

-- 尝试获取共享锁 (非阻塞)
SELECT pg_try_advisory_lock_shared(key bigint);

-- 释放排他锁
SELECT pg_advisory_unlock(key bigint);

-- 释放共享锁
SELECT pg_advisory_unlock_shared(key bigint);

-- 释放当前会话所有 Advisory Lock
SELECT pg_advisory_unlock_all();

-- 获取事务级排他锁 (事务结束时自动释放, 无法手动解锁)
SELECT pg_advisory_xact_lock(key bigint);

-- 尝试获取事务级排他锁
SELECT pg_try_advisory_xact_lock(key bigint);

-- 等效于 pg_advisory_lock((int8(class) << 32) | int8(id))
SELECT pg_advisory_lock(class int4, id int4);
SELECT pg_try_advisory_lock(class int4, id int4);

SELECT pg_advisory_lock(100);   -- count = 1
SELECT pg_advisory_lock(100);   -- count = 2
SELECT pg_advisory_unlock(100); -- count = 1, 锁仍持有
SELECT pg_advisory_unlock(100); -- count = 0, 锁释放

应用 A: 从连接池借到连接 conn-1
应用 A: pg_advisory_lock(100) → 加锁成功
应用 A: 完成业务, 将 conn-1 归还连接池  ← 忘记 unlock!

应用 B: 从连接池借到 conn-1
应用 B: 以为是干净连接, 实际上 conn-1 仍持有 key=100 的锁
应用 B: 对其他 key 加锁, 操作共享资源 → 产生意外的锁竞争

-- 在应用代码中 (伪代码)
connection.execute("SELECT pg_try_advisory_lock(hashtext('daily_report_job'))");
if result == true:
    run_daily_report()
    connection.execute("SELECT pg_advisory_unlock(hashtext('daily_report_job'))")
else:
    log("Another instance is running the job, skip")

-- 对 order_id=12345 加锁, 防止并发处理
SELECT pg_try_advisory_xact_lock(12345);
-- 返回 true: 获取成功, 处理订单
-- 返回 false: 已有其他事务在处理, 跳过或等待

-- 事务提交时自动释放, 无需手动 unlock
COMMIT;

BEGIN;
SELECT pg_advisory_xact_lock(order_id);
SELECT * FROM orders WHERE id = order_id FOR UPDATE;
-- 处理业务逻辑
COMMIT;

应用 A 持有 Advisory Lock(100) ← 存在共享内存中

数据库重启 (计划内 or 崩溃)
    ↓
共享内存清空
    ↓
Advisory Lock(100) 消失

数据库重启完成后:
- 应用 A 的连接已断开 (连接断开时锁本来就会自动释放)
- 即使应用 A 重新连接, 也需要重新申请锁
- 其他应用可以正常申请 Lock(100)

应用 A 连接 Primary, 持有 Advisory Lock(100)
Primary 宕机
    ↓
Standby 晋升为新 Primary
    ↓
新 Primary 共享内存中没有 Advisory Lock(100)
    ↓
应用 B 连接新 Primary, 申请 Advisory Lock(100) → 成功! ⚠️

同时:
应用 A 的连接已断开 (Primary 宕机)
应用 A 重连新 Primary, 发现自己没有锁了

-- 获得锁后, 先检查业务状态
SELECT pg_try_advisory_xact_lock(order_id);
IF acquired THEN
    -- 检查幂等: 是否已经处理过?
    IF NOT EXISTS (SELECT 1 FROM orders WHERE id = order_id AND status = 'processed') THEN
        -- 处理业务
        UPDATE orders SET status = 'processed' WHERE id = order_id;
    END IF;
END IF;

# postgresql.conf
synchronous_commit = remote_apply
synchronous_standby_names = 'standby1'

-- 查看当前所有 Advisory Lock
SELECT
    pid,
    locktype,
    classid,
    objid,
    mode,
    granted
FROM pg_locks
WHERE locktype = 'advisory';

-- 结合进程信息
SELECT
    l.pid,
    a.usename,
    a.application_name,
    l.classid,
    l.objid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'advisory';