https://www.postgresql.org/download/linux/debian/
apt update && apt install -y gnupg1 echo "deb https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt $(lsb_release -cs)-pgdg main" >/etc/apt/sources.list.d/pgdg.list curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - apt update && apt install -y postgresql-plpython3-14
Python:
yum install postgresql-server # Python 的包 psycopg2 要 devel yum install postgresql-devel # 不装 devel 也可以，pip install psycopg2-binary
启动服务
initdb systemctl enable postgresql systemctl start postgresql
安装好后, 先切换到 postgres 用户, 用来创建其他用户的库，并创建同名的数据库
su postgres cd createuser q createdb q
/var/lib/pgsql/data/
/var/lib/pgsql/<VERSION>/data/
postgresql.conf 基础配置
listen_addresses = '*' fsync = off # 日志相关配置，可选 log_destination = 'csvlog' log_filename = '%m-%d' log_statement = 'mod' log_min_duration_statement = 0 log_connections = on log_disconnections = on
pg_hba.conf 登录配置
local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 172.16.16.0/24 trust host replication all 172.16.16.0/24 trust host all all 0.0.0.0/0 md5
CREATE USER foo WITH PASSWORD 'bar'; ALTER USER q WITH PASSWORD 'xxxx';
最直观的就是用 COPY 命令了
https://www.postgresql.org/docs/current/sql-copy.html
有时候为了避免 postgres 用户权限不够的问题，可以用 STDIN/STDOUT
psql -c 'COPY "xxx" TO STDOUT' >db_backup/$(date +"%Y-%m-%dT%H:%M:%S").txt
psql -c 'COPY "xxx" FROM STDIN' <xxx.txt
假设我们用 ts 命名时间戳
create time：
create table TTTT {
...
ts timestamptz default now()
}
update time:
create or replace function update_column_ts() returns trigger
language 'plpgsql'
as $$
begin
new.ts = now();
return new;
end
$$;
create trigger update_TTTT_ts
before update on TTTT
for each row
execute procedure
update_column_ts();
参考 https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/
记录增删改
create table history (
id bigserial primary key,
tb varchar(64),
op varchar(16),
ref integer,
ts timestamptz default now()
);
create or replace function history() returns trigger
language 'plpgsql'
as $$
declare
row record;
begin
if (TG_OP = 'DELETE') then
row := OLD;
else
row := NEW;
end if;
insert into history (tb, op, ref) values (TG_TABLE_NAME, TG_OP, row.id);
return row;
end
$$;
create trigger history_t
after insert or update or delete
on t
for each row
execute procedure
history();
针对某表的某列专门做记录, 如记录 q.gold 的变化, 并且变化时必须带上 tip
drop table if exists history_gold;
drop table if exists q;
create table q (
id serial primary key,
name varchar(32),
gold bigint default 0,
tip varchar(256)
);
create table history_gold (
q integer references q (id),
before bigint,
after bigint,
tip varchar(256),
ts timestamptz default now()
);
-- https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html
-- https://www.postgresql.org/docs/current/sql-createtrigger.html
-- https://www.postgresql.org/docs/current/plpgsql-trigger.html
create or replace function fn_history_gold() returns trigger
language 'plpgsql'
as $$
begin
if (new.tip is null) then
raise 'tip is required';
end if;
insert into history_gold (q, before, after, tip) values (new.id, old.gold, new.gold, new.tip);
new.tip = null; -- reset
return new;
end
$$;
create trigger tg_history_gold
create trigger tg_history_gold
before update of gold on q
for each row
execute procedure
fn_history_gold();
-- strict permission, only read & append
REVOKE ALL ON TABLE history_gold FROM "you";
GRANT SELECT ON TABLE history_gold TO "you";
GRANT INSERT ON TABLE history_gold TO "you";
insert into q (name) values('q');
insert into q (name) values('q');
insert into q (name) values('q');
update q set gold = gold + 1, tip='test';
update q set tip='test';
update q set gold = gold + 1;
select * from history_gold;
select * from q;
如果需要定期清理并归档，参考以下代码(已通过高并发情况下数据完整性测试)
begin transaction; lock table history_gold in exclusive mode; copy history_gold to stdout; truncate history_gold; commit;
psql -q <xxx.sql >>$(date +"%Y-%m-%d").history
参考：https://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
CREATE OR REPLACE FUNCTION dynamic_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
s TEXT;
BEGIN
RAISE NOTICE E'\n Operation: %\n Schema: %\n Table: %',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;
FOR r IN
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = TG_TABLE_SCHEMA
AND table_name = TG_TABLE_NAME
ORDER BY ordinal_position
LOOP
EXECUTE 'SELECT ($1).' || r.column_name || '::text' INTO s USING NEW;
RAISE NOTICE E'Column\n number: %\n name: %\n type: %\n value: %',
r.ordinal_position,
r.column_name,
r.data_type,
s;
END LOOP;
RETURN NEW;
END;
$$;
主要是用到了 EXECUTE，下面来看高级用法：适配绝大部分表的历史记录触发器
drop table if exists q;
create table q (
id serial primary key,
name varchar(32),
gold bigint default 0,
tip varchar(256)
);
drop table if exists history_update;
create table history_update (
tab varchar(64), -- table
ref varchar(64), -- foreign primary key
col varchar(64), -- column target
old text,
new text,
ts timestamptz default now()
);
create or replace function history_update() returns trigger
language 'plpgsql'
as $$
declare
ref text;
col text;
begin
ref := TG_ARGV[0];
col := TG_ARGV[1];
execute
format('insert into history_update (tab, ref, col, old, new) values ($1, $4."%s", $2, $3."%s", $4."%s")', ref, col, col)
using TG_TABLE_NAME, col, OLD, NEW;
return NEW;
end
$$;
create trigger tg_
after update of gold on q
for each row
execute procedure
history_update(id, gold);
insert into q (name) values('q');
insert into q (name) values('q');
insert into q (name) values('q');
update q set gold = gold + 1;
update q set gold = gold + 5;
select * from history_update;
-- useful diff function
CREATE OR REPLACE FUNCTION json_diff(l JSON, r JSON) RETURNS JSON AS
$$
SELECT json_object_agg(a.key, a.value) FROM
(SELECT key, value FROM jsonb_each(l::jsonb)) a LEFT OUTER JOIN
(SELECT key, value FROM jsonb_each(r::jsonb)) b ON a.key = b.key
WHERE a.value != b.value OR b.key IS NULL;
$$ LANGUAGE sql;
create table history_update (
tb varchar(32),
pk varchar(64), -- just record id or ids, as string
before jsonb,
after jsonb,
ts timestamptz default now()
);
create or replace function history_update() returns trigger
language 'plpgsql'
as $$
declare
x json;
y json;
pk text;
arg text;
tmp text;
begin
x := row_to_json(old);
y := row_to_json(new);
pk := '';
FOREACH arg IN ARRAY TG_ARGV LOOP
EXECUTE 'SELECT ($1).' || arg || '::text' INTO tmp USING old;
pk := pk || ',' || tmp;
END LOOP;
pk := trim(',' from pk);
insert into history_update (tb, pk, before, after)
values (TG_TABLE_NAME, pk, json_diff(x, y), json_diff(y, x));
return new;
end
$$;
drop trigger history_update on t;
create trigger history_update
after update
on t
for each row
execute procedure
history_update(id); -- or multi primary keys: history_update(id, s);
这就可以非常灵活了
https://www.postgresql.org/docs/current/plpython.html
https://www.postgresql.org/docs/current/plpython-trigger.html
数据类型，共享数据，触发器，表数据增删改等等都有文档。
启用了 plpython3u 的 postgresql 服务，可以参考 docker: lwzm/postgres
函数例子：
do language plpython3u $$ import sys import os plpy.info(os.getpid(), os.getcwd(), sys.path) plpy.info(SD, GD) plpy.info(dir(__builtins__)) plpy.info(globals()) $$;
触发器例子：
-- init:
-- CREATE EXTENSION plpython3u;
create or replace function t() returns trigger language plpython3u as $$
import time
import requests
import os
1/1
time.sleep(1)
with open("/tmp/debug", "a") as f:
print(dir(), file=f)
print(globals(), file=f)
print(TD, file=f)
print(requests, file=f)
print(os.getcwd(), file=f)
TD['new']['state'] = "xx"
return 'MODIFY'
$$;
pip install pgxnclient pgxn install safeupdate vi /var/lib/pgsql/9.6/data/postgresql.conf shared_preload_libraries=safeupdate
file_fdw https://www.postgresql.org/docs/current/file-fdw.html 了解一下
CREATE EXTENSION file_fdw;
CREATE SERVER fdw FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE t (
name text,
age integer
) SERVER fdw
OPTIONS (
filename '/home/q/test/t.txt'
);
ALTER TABLE t OWNER TO q;
echo 'foo\t10' >> t.txt
与 nginx 输出日志配合的例子，记录字段（ ts ip request status length sent time upstream referer uid ua ）
CREATE FOREIGN TABLE day27 (
ts timestamptz,
ip text,
request text,
status integer,
length integer,
sent integer,
time real,
upstream text,
referer text,
uid text,
ua text
)
SERVER fdw
OPTIONS (
filename '/var/log/nginx/days/27'
);
log_format
tsv
escape=json
'$time_iso8601\t'
'$remote_addr\t'
'$request\t'
'$status\t'
'$request_length\t'
'$body_bytes_sent\t'
'$request_time\t'
'$upstream_addr\t'
'$http_referer\t'
'$cookie_uid\t'
'$http_user_agent'
;
server {
...
if ($time_iso8601 ~ "^.{8}(\d{2})") {
set $day $1;
}
access_log /var/log/nginx/days/$day tsv;
...
}
# 日志权限开放 usermod -a -G nginx postgres chmode 750 /var/log/nginx/
批量创建表，31 天的表
function tpl() {
echo "
DROP FOREIGN TABLE IF EXISTS day$1;
CREATE FOREIGN TABLE day$1 (
ts timestamptz,
ip text,
request text,
status integer,
length integer,
sent integer,
time real,
upstream text,
referer text,
uid text,
ua text
)
SERVER fdw
OPTIONS (
filename '/var/log/nginx/days/$1'
);
ALTER TABLE day$1 OWNER TO q;
GRANT SELECT ON TABLE day$1 TO web_anon;
"
}
for i in $(seq -f "%02g" 31); do
tpl $i
done
https://www.postgresql.org/docs/current/sql-listen.html
仅做参考，具体问题具体学习分析。
master 的 postgresql.conf 不需要特殊配置
默认 wal_level = replica
信任网络中，不想要密码，pg_hba.conf 不要漏了
host replication all 172.16.16.0/24 trust
准备好初始文件：
pg_basebackup -D bak
把初始文件放在 standby 的 $PGDATA
切换到 standby，编辑 postgresql.conf
primary_conninfo = 'postgresql://postgres:****@<MASTER_HOST>' # connection string to sending server
然后在当前目录 touch standby.signal，有这文件即激活。
关键就是有初始数据，有文件 standby.signal，有 master 的连接配置。
更方便的做法，直接在 standby 上操作：
pg_basebackup -v -N -R -D bak -h POSTGRES-HOST # or pg_basebackup -v -N -R -D bak -d 'postgresql://postgres:x@postgres' # or pg_basebackup -v -N -R -D bak -T /some/dir=$PWD/dir -d 'postgresql://postgres:x@postgres'
上面说的几条要素都为你准备好了，直接就能跑服务了：postgres -D bak
pg_tblspc 里面存放的是表空间的软连接，虽然官方说只能用绝对路径，这里可以手动魔改为相对路径。
tips:
podman 跑 standby:
# 这里 postgres 程序使用了镜像的默认环境变量 PGDATA=/var/lib/postgresql/data ，也可以自己加参数指定：postgres -D xxx # podman run --name bak -it -u postgres postgres:15 sh -c 'cd; postgres -D data || bash' podman run --name bak -it -u postgres postgres:15 sh -c 'postgres --check && exec postgres || bash' # 使用 postgres 用户，进入 $HOME，第一次跑 postgres 肯定会出错，然后自动转入 shell，这时候可以手动初始化数据库， # 比如： initdb # 初始化新库 # 再比如 pg_basebackup -v -N -R -D $PGDATA -h 10.1.0.94 && chmod 700 $PGDATA # 创建一个热备份
以后，只需要 podman start bak
虽说 select now() 遵循系统的 /etc/localtime 但是服务的日志打印不遵循，所以主动设置吧：
postgresql.conf
timezone = 'Asia/Shanghai' log_timezone = 'Asia/Shanghai'
参考
select * from pg_timezone_names where utc_offset = '08:00:00';
drop schema t cascade; create schema t; set search_path to t; create table z ( id serial primary key, key text unique not null, note text ); create table "group" ( -- z text references z on update cascade, z int references z, id text, primary key (z, id), note text ); create table "user" ( z int references z, id text, primary key (z, id), phone text ); create table group_user ( z int references z, "group" text, "user" text, primary key (z, "group", "user"), foreign key (z, "group") references "group" on update cascade, foreign key (z, "user") references "user" on update cascade, ts timestamptz default now() );
以上 DDL 可以根据自定义的配置生成：
- table: group
columns:
- id text
- note text
- ts timestamptz default now()
primary_key: id
indexes:
- ts
- ts,note
- table: user
columns:
- id text
- phone text
- ts timestamptz default now()
primary_key: id
- table: group_user
columns:
- group text
- user text
- ts timestamptz default now()
primary_key: group,user
foreign_keys:
- key: group
reference: group
#- group
- key: user
reference: user
#- user
转换程序 TODO 。
测试 partition + postgraphile, 目前需要创建 view 来达到效果，社区支持表继承后就不用 view 了。
create table group_user (
z text,
"group" text,
"user" text,
foreign key (z, "group") references "group" on update cascade,
foreign key (z, "user") references "user" on update cascade,
primary key (z, "group", "user")
) partition by hash (z);
create table ps.group_user_0 partition of group_user for values with (modulus 2, remainder 0) tablespace _0;
create table ps.group_user_1 partition of group_user for values with (modulus 2, remainder 1) tablespace _1;
alter index ps.group_user_0_pkey set tablespace _0;
alter index ps.group_user_1_pkey set tablespace _1;
create view gu as select * from group_user;
-- postgraphile smart tags
comment on view gu is '
@primaryKey z,group,user
@foreignKey (z,group) references group
@foreignKey (z,user) references user
';
insert into z(id) values ('q'), ('w');
insert into "group"(z, id) values ('q','g1'), ('q','g2'), ('w','g1'), ('w','g2');
insert into "user"(z, id) values ('q','u1'), ('q','u2'), ('q','u3'), ('w','u1'), ('w','u2'), ('w','u3'), ('w','u4');
insert into gu(z, "group", "user") values ('q','g1','u1'),('q','g1','u2'),('q','g1','u3'),('q','g2','u1');
select * from gu
left join "group" as g on gu.z=g.z and gu."group"=g.id
left join "user" as u on gu.z=u.z and gu."user"=u.id
where u.z = 'q';
另，可以配合 postgraphile 的 jwt 来实现约束命名空间的功能：
set search_path to api;
drop view gu;
drop view g;
drop view u;
create view gu as select "group" as g, "user" as u from public.group_user where z = current_setting('jwt.claims.z');
create view g as select id, note from public."group" where z = current_setting('jwt.claims.z');
create view u as select id, phone from public."user" where z = current_setting('jwt.claims.z');
comment on view gu is '
@primaryKey g,u
@foreignKey (g) references g
@foreignKey (u) references u
';
comment on view g is '
@primaryKey id
';
comment on view u is '
@primaryKey id
';
查询就可简化一个维度，这样：
begin; set local jwt.claims.z to 'q'; select * from gu left join g on gu."group"=g.id left join u on gu."user"=u.id ; commit;
性能存疑，推测可以用到索引，简单实践符合预期。
还能利用 simple view 的特性，无需主动指定 z，自动拥有 update, insert, delete 特性，实现 insert 操作需要做一点调整，如：
create table "group" (
z text default current_setting('jwt.claims.z'),
...
-- or
alter table "group" alter z set default current_setting('jwt.claims.z');
可以参考
按天分区，适用于日志：
create table log (
dt timestamp default now(),
x jsonb
) partition by list (date_part('day', dt));
create index idx_log_dt on log(dt);
create table _log partition of log default;
create table _log01 partition of log for values in (1);
create table _log02 partition of log for values in (2);
create table _log03 partition of log for values in (3);
create table _log04 partition of log for values in (4);
create table _log05 partition of log for values in (5);
create table _log06 partition of log for values in (6);
create table _log07 partition of log for values in (7);
create table _log08 partition of log for values in (8);
create table _log09 partition of log for values in (9);
create table _log10 partition of log for values in (10);
create table _log11 partition of log for values in (11);
create table _log12 partition of log for values in (12);
create table _log13 partition of log for values in (13);
create table _log14 partition of log for values in (14);
create table _log15 partition of log for values in (15);
create table _log16 partition of log for values in (16);
create table _log17 partition of log for values in (17);
create table _log18 partition of log for values in (18);
create table _log19 partition of log for values in (19);
create table _log20 partition of log for values in (20);
create table _log21 partition of log for values in (21);
create table _log22 partition of log for values in (22);
create table _log23 partition of log for values in (23);
create table _log24 partition of log for values in (24);
create table _log25 partition of log for values in (25);
create table _log26 partition of log for values in (26);
create table _log27 partition of log for values in (27);
create table _log28 partition of log for values in (28);
create table _log29 partition of log for values in (29);
create table _log30 partition of log for values in (30);
create table _log31 partition of log for values in (31);
create table _log32 partition of log for values in (32);
create table _log33 partition of log for values in (33);
hash partition
注意，索引也可以选择 tablespace，要使用 alter 语句来调整。
create table hp ( ns text, foo text, primary key(ns, foo) ) partition by hash (ns, foo); create table hp_0 partition of hp for values with (modulus 3, remainder 0); create table hp_1 partition of hp for values with (modulus 3, remainder 1); create table hp_2 partition of hp for values with (modulus 3, remainder 2); -- or create tablespace _0 location '/0'; create tablespace _1 location '/1'; create tablespace _2 location '/2'; create tablespace _3 location '/3'; -- select spcname from pg_tablespace; --SET default_tablespace = xxx; create table hp ( z text, id text, primary key(z, id) ) partition by hash (z); create table tmp._0 partition of hp for values with (modulus 4, remainder 0) tablespace _0; create table tmp._1 partition of hp for values with (modulus 4, remainder 1) tablespace _1; create table tmp._2 partition of hp for values with (modulus 4, remainder 2) tablespace _2; create table tmp._3 partition of hp for values with (modulus 4, remainder 3) tablespace _3; alter index tmp._0_pkey set tablespace _0; alter index tmp._1_pkey set tablespace _1; alter index tmp._2_pkey set tablespace _2; alter index tmp._3_pkey set tablespace _3; insert into hp select '1', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '2', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '3', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '4', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '5', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '6', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '7', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '8', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '9', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '10', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '11', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '12', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '13', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '14', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '15', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '16', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '17', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '18', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '19', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '20', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '21', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '22', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '23', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '24', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '25', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '26', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '27', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '28', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '29', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '30', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '31', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '32', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '33', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '34', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '35', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '36', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '37', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '38', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '39', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '40', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '41', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '42', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '43', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '44', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '45', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '46', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '47', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '48', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '49', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '50', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '51', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '52', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '53', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '54', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '55', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '56', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '57', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '58', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '59', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '60', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '61', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '62', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '63', md5(now()::text||v::text) from generate_series(1, 100000) v; insert into hp select '64', md5(now()::text||v::text) from generate_series(1, 100000) v; --- count partitions select tableoid::regclass, count(*) from hp group by 1 order by 1 --- insert into test select md5(now()::text||v::text) from generate_series(1, 100000) v;
e.g.
alter role postgres set statement_timeout = '10s';