https://www.postgresql.org/docs/current/explicit-locking.html
导出binary格式
copy tb_namespace to '/tmp/tb_namespace.binary' with binary
非登录导入
psql -d demodb -h localhost-p 5432 -U postgres -f /tmp/sql.dmp
导出
pg_dump -d tb -t tb_namespace > /tmp/tb_namespace.sql
导入
psql--host=192.168.11.81--port=5494 --username=admin --dbname=datas -f /tmp/namespace.dmp
————————————————————————————————
备份重复数据
copy (SELECT name,env,username,create_at,updated_at,deleted_at from namespace n where (n.id) not in (select min(id) from namespace group by(name,env))) TO /tmp/multi.csv WITH CSV HEADER;
删除重复数据
delete from namespace n where (n.id) not in (select min(id)from namespace group by (name,env));
异常回滚导入备份数据
COPY namespace(name,env,username,create_at,updated_at,deleted_at ) FROM '/tmp/namespace.csv' WITH CSV HEADER;
————————————————————————————————
连接数据库
psql --host=db-instance.111223.aws-region.rds.amazon.com --port=5432 --username=admin --password --dbname=studentinfo
导出
pg_dump -d 数据库名 -t 表名 -a -f 导出文件路径及文件
示例:
pg_dump -d registry -t notification -a -f /tmp/notification.bak.sql
postgresql模糊查询支持特殊字符(golang gorm)
query = query.Where("student.descrtiption like regexp_replace(concat('%','"+q.SearchDesc+"','%'),'\\\\','\\\\\\','g')")
找出wait_event_type='Lock'的pid
SELECT * FROM pg_stat_activity ;
KILL掉线程
select pg terminate backend(16809);
查看索引使用情况
explain analyze select * from student where name like '%tom%';
如果有index字样则表示走了索引
创建索引
create inx name on student(name);
创建联合索品
create inxdex name_uuid on studeint(name, uuid);
删除索引
drop index name;
查询当前系统中所有活动的锁信息
SELECT virtualtransaction, relation::regclass, locktype, page, tuple, mode, granted, transactionid
FROM pg_locks
ORDER BY granted, virtualtransaction;
kubectl -n pg exec -it postgrepod bash
导出整个数据库
pg_dump -d aws -f aws_back.slq
导入数据
1、创建数据表
postres=# create table public.chartdata();
2、导入binary数据
copy public.student from '/tmp/student.binary' with binary;
注意此处的/tmp/student.binary是在pod的目录,需将student.binary文件提前拷贝到pod的/tmp目录下,可参考我的博文
3、导出表数据到binary数据
copy public.student to '/tmp/student.binary' with binary;
导出某个表结构及数据
pq dump -d aws -t aws_resource > /tmp/resource.sql
pod之间拷贝文件
kubectl cp -n harbor aws-pg-postgresql-pod:tmp/chart.bak.sql /home/chart.bak.sql
从pod下载文件到本地
kubectl cp -n my-nat my-nat-harbor-database-0:tmp/por back.sql /home/back.sql
kubectl cp -n harbor aws-pg-postgresql--pod:/tmp/user.sql /home/user.sql
本地上传文件到pod
kubectl -n default cp /home/user.sql my-nat-harbor-database-0:/user.sql
kubectl -n default cp /home/resource.sql my-database-0:/tmp/resource.sql
kubectl -n my-nat-sh cp trivv.db harbor-trivy-0:/home/scanner/.cache/trivy/db/
批量插入数据
create table people
(
id integer,
name varchar(32),
age integer,
grade numeric(4, 2),
birthday date,
logintime timestamp
);
insert into people
select generate_series(1,10000) as id,
md5(random()::text) as name,
(random()*100)::integer as age,
(random()*99)::numeric(4,2) as grade,
now() - ((random()*1000)::integer||' day')::interval as birthday,
clock_timestamp() as logintime;