postgreSQL操作

valisweet

已于 2024-08-27 23:50:02 修改

阅读量145

点赞数

分类专栏： MySQL 文章标签： postgresql 数据库

于 2023-10-12 21:51:41 首次发布

本文链接：https://blog.youkuaiyun.com/javahuazaili/article/details/133800032

版权

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了如何在PostgreSQL中进行数据导出（如pg_dump）、备份与删除重复数据，处理模糊查询，管理索引，监控锁定状态，以及在Kubernetes中执行数据库操作，包括GolangGorm的使用和批量数据插入示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://www.postgresql.org/docs/current/explicit-locking.htmls

在线psql执行

https://pgplayground.com/

修改最大连接数

查看最大连接数 SHOW max_connections;

查看当前连接数 SELECT count(*)FROM pg_stat_activity;

修改最大连接数

方式一 ALTER SYSTEM SET max_connections TO '1024'; 然后重启psql的pod

方式二，修改配置文件（不知道什么原因这个方式没生效，方式一可以）：

vim postgresql.conf

max_connections=1024

导出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;

参考自： PostgreSQL - 使用generate_series函数生成大量测试数据（附样例）