需要管理身份运行powershell

# 初始化数据库
D:\server\PostgreSQL\15\bin\pg_ctl.exe init 
# 开启服务
D:\server\PostgreSQL\15\bin\pg_ctl.exe start -N "postgres_15" -D "D:\server\PostgreSQL\15\data"
# 关闭服务
D:\server\PostgreSQL\15\bin\pg_ctl.exe stop -N "postgres_15" -D "D:\server\PostgreSQL\15\data"
# 注册windows系统服务并设置自动启动
D:\server\PostgreSQL\15\bin\pg_ctl.exe register -N "postgres_15" -D "D:\server\PostgreSQL\15\data" -S "auto"

运行postgres_15服务，默认本地连接时不需要密码的。

密码等设置在D:\server\PostgreSQL\15\data\pg_hba.conf

以pg_stat_statements为例

编译扩展

cd /usr/local/pgsql/contrib/pg_stat_statements/
make && make install

启用扩展

切换为postgres用户sudo su - postgres，运行sql命令行psql，启用扩展

CREATE EXTENSION pg_stat_statements;

修改postgresql.conf

进入宝塔面板，修改postgres配置

shared_preload_libraries= 'pg_stat_statements'

重启postgresql

根据字段值查找

SELECT t.*
FROM table_name t
WHERE json_field->>'type'='5' and  json_field->>'value'!=''

查找并替换

update
table_name d
set cover_content=(cover_content::jsonb || jsonb_build_object('cover_img_id',((select f.id  from file f WHERE  f.path = SUBSTRING(cover_content->>'value',44))  limit 1)::text)::jsonb)
where cover_content->>'type'='5' and  cover_content->>'value'!='' and cover_content->>'cover_img_id' is null;

更新嵌套对象的键值：

如果 jsonb 字段包含嵌套对象，例如：

'{"user": {"name": "Alice", "age": 30}}'

你想更新 user 对象中的 age 为 35，可以使用：

UPDATE your_table
SET jsonb_column = jsonb_set(jsonb_column, '{user,age}', '"35"');

数组里面的字段查找

字段里面存的是数组，数组里面存的对象，可以通过以下语句直接查询对象属性的值

select *
    from cart,
jsonb_array_elements(data::jsonb) as elem where ( elem->>'cover_img_id' is null or  elem->>'cover_img_id'='') order by created_at desc;

数组字段查找并替换

update cart set data= (
  SELECT jsonb_agg(
    CASE
      WHEN element->>'cover_img_url' <> '' THEN jsonb_set(element, '{cover_img_id}',
          ((((select f.id  from file f WHERE  f.path = SUBSTRING(element->>'cover_img_url',44))  limit 1)::text)::jsonb)
          )
      ELSE element
    END
  )
  FROM jsonb_array_elements(data::jsonb) AS element
)
where id in (
    select id
    from cart,
jsonb_array_elements(data::jsonb) as elem where elem->>'cover_img_url' <> '' and ( elem->>'cover_img_id' is null or  elem->>'cover_img_id'='') order by created_at desc
    );

多级数组查找替换

select id                                as cart_id,
       cart_user_id                      as user_id,
       elem ->> 'id'           as file_id,
       (updated_at + interval '30 days') as expired_at
from cart,
     jsonb_array_elements(data::jsonb) as elem
limit 10;

update file f
set expired_at=t.expired_at
from (select id                                as cart_id,
       cart_user_id                      as user_id,
       (elem ->> 'id')::int           as file_id,
       (updated_at + interval '30 days') as expired_at
from cart,
     jsonb_array_elements(data::jsonb) as elem)
         as t
where f.id = t.file_id
  and f.expired_at < t.expired_at;

创建用户

CREATE USER davide WITH PASSWORD 'jw8s0F4';
CREATE ROLE admin WITH CREATEDB CREATEROLE;

修改用户密码

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
ALTER ROLE davide WITH PASSWORD NULL;

创建数据库

CREATE DATABASE sales OWNER salesapp

设置数据库只读权限

create user test_readonly with password 'password';
grant connect on DATABASE test to test_readonly;
grant select on all tables in schema public to test_readonly;

导出导入

pg_dump -F t -h 127.0.0.1 -U postgres dbname > dbname.tar
pg_restore -d dbname dbname.tar