postgreSQL维护

# 创建用户

创建用户

create user yfk with password '123456';
create database testdb owner yfk;
grant all privileges on database testdb to yfk;

1
2
3

授权yfk用户超级权限

alter user yfk with superuser

去除：ALTER USER yfk WITH NOSUPERUSER;

授权yfk用户只读testdb

\c testdb
GRANT SELECT ON ALL TABLES IN SCHEMA public TO yfk;

1
2

修改密码

alter user yfk with password 'qWeR12345.com';

# postgreSql连接问题

查看连接数

show superuser_reserved_connections;
show max_connections;
SELECT COUNT(1) FROM pg_stat_activity;

1
2
3

删除用户连接test库pid

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='test' AND pid<>pg_backend_pid();

查看当前有哪些sql语句

SELECT pid, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start;

分析sql语句

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_condition;

查看锁表的pid

SELECT
    pid 
FROM
    pg_locks l
    JOIN pg_class t ON l.relation = t.oid 
WHERE
    t.relkind = 'r' 
    AND t.relname = 'lockedtable';

1
2
3
4
5
6
7
8

查看执行的sql

select
  l.pid,
  l.relation::regclass AS table_name,
  a.query
from
  pg_locks l
join
  pg_stat_activity a ON l.pid = a.pid
where
  l.mode = 'AccessExclusiveLock';

1
2
3
4
5
6
7
8
9
10

SELECT pid, 
       usename AS username, 
       datname AS database_name, 
       query, 
       state, 
       now() - query_start AS execution_time 
FROM pg_stat_activity 
WHERE datname = 'caiji';

1
2
3
4
5
6
7
8

psql -U postgres -d postgres -c "SELECT pid,        usename AS username,        datname AS database_name,        query,        state,        now() - query_start AS execution_time FROM pg_stat_activity WHERE datname = 'caiji';" >/tmp/caiji.exec_sql.txt

查看锁表的语句

SELECT
    pid,
    state,
    usename,
    QUERY,
    query_start 
FROM
    pg_stat_activity 
WHERE
    pid IN (
    SELECT
    pid 
FROM
    pg_locks l
    JOIN pg_class t ON l.relation = t.oid 
    AND t.relkind = 'r' 
WHERE
    t.relname = 'lockedtable' 
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查找所有活动的被锁的表

select 
    pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (
    select pid from pg_locks l 
    join pg_class t on l.relation = t.oid 
     and t.relkind = 'r' 
);

1
2
3
4
5
6
7
8

解锁

SELECT pg_cancel_backend(pid);

# 数据库及用户维护

# postgreSql创建库

创建数据库

create database testdb;

CREATE DATABASE "testdb" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8';

创建数据库GuassDB

CREATE DATABASE iot_monitor_service WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'  dbcompatibility = 'PG' ;

删除数据库

drop database testdb;

重命名数据库(该数据库必须没有活动的连接)

alter database testdb rename to newname;

以其他数据库为模板创建数据库（表结构、数据都会复制，模板库没有活动的连接）

create database newdb template testdb;

# postgreSql用户

建立新的数据库用户

create user yfk with password '123456';

为新用户建立数据库

create database testdb owner yfk;

把新建的数据库权限赋予新用户

grant all privileges on database testdb to yfk;

授权超级权限：alter user dimine with superuser ;

修改数据库用户

alter user replica with password 'replica@pgsql';

创建只读用户

schema_name 默认：public

--创建用户
create user [username] password '[password]';

--更新只读权限
alter user [username] set default_transaction_read_only = on; 

--设置可操作的数据库
grant all on database [database_name] to [username];

--设置可操作的模式和权限---注意---需进入对应的数据库
grant select on all tables in schema [schema_name] to [username];

1
2
3
4
5
6
7
8
9
10
11

# 数据库表数据查看

表数据大小

#仅表数据大小(不包括索引)

SELECT pg_size_pretty(pg_relation_size('table_name'));


#表的总大小(包括索引)

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

表的索引大小

SELECT pg_size_pretty(pg_indexes_size('table_name'));

1
2
3
4
5
6
7
8
9
10
11
12
13

查询指定表大小：table_name

SELECT 
    table_name,
    pg_size_pretty(pg_table_size(quote_ident(table_name)::regclass)) AS table_size,
    pg_size_pretty(pg_indexes_size(quote_ident(table_name)::regclass)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name)::regclass)) AS total_size
FROM 
    information_schema.tables
WHERE 
    table_schema = 'public' AND
    table_name = 'table_name';

1
2
3
4
5
6
7
8
9
10

查询最大的表

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9

查询当前数据库中所有表的大小

SELECT
    table_name,
    pg_size_pretty(pg_table_size(table_name)) AS table_size,
    pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(table_name)) AS total_size
FROM
    (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
ORDER BY total_size DESC;

1
2
3
4
5
6
7
8
9
10
11

查询特定模式中所有表的大小

SELECT
    table_schema || '.' || table_name AS TableName,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

1
2
3
4
5
6

# postgreSql日志参数

编辑 postgresql.conf 文件，设置以下参数

logging_collector = on
log_destination = 'stderr'  # 或 'csvlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 5000  #5000=5s

1
2
3
4
5

重启postgresql服务

重载配置文件以使更改生效

SELECT pg_reload_conf();

如果此文章对您有帮助，点击 -->> 请博主喝咖啡

上次更新: 2025/03/28, 13:42:54

← postgreSQL-12编译安装 Redis安装→