postgresql常用操作

一边学习一边哭

已于 2024-08-01 04:35:55 修改

阅读量2.2k

点赞数

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

于 2022-10-13 11:40:30 首次发布

本文链接：https://blog.csdn.net/qq522044637/article/details/126923890

版权

PostgreSQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

常规操作

数据库连接

psql -U user_name -h ip -d database_name

#命令行输入密码
PGPASSWORD=$your_password psql -U user_name -h ip -d database_name

基础操作

\l                   #查看数据库
\c databanse_name    #切换数据库
\d                   #查看表
\d table_name        #查看表结构
\q                   #退出
\dx                  #查看已安装插件

增删改查

查看使用量

查看磁盘使用总量

select pg_size_pretty(pg_tablespace_size('pg_default'));

查看数据库大小

#1.查看指定数据库大小，单位Byte
select pg_database_size('database_name');

#2.查看指定数据库大小，单位经过优化显示
select pg_size_pretty (pg_database_size('database_name'));

#3.列出所有database的大小，单位经过优化显示
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

查看表大小

#1.查看指定表大小，单位Byte
select pg_relation_size('table_name');

#2.查看指定表大小，单位经过优化显示
select pg_size_pretty(pg_relation_size('table_name'));

#3.查看所有表大小，单位经过优化显示，显示前20行
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' 
    || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;

#4.查看所有表的大小，并分别显示data和index大小
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
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
) AS pretty_sizes;

查看表和索引占用物理空间

select pg_size_pretty(pg_relation_size('$tableName', 'main')) as main, \
       pg_size_pretty(pg_relation_size('$tableName', 'fsm')) as fsm, \
       pg_size_pretty(pg_relation_size('$tableName', 'vm')) as vm, \
       pg_size_pretty(pg_relation_size('$tableNamei', 'init')) as init, \
       pg_size_pretty(pg_relation_size('$tableName')) as pg_relation_size, \
       pg_size_pretty(pg_table_size('$tableNamei')) as pg_table_size, \
       pg_size_pretty(pg_indexes_size('$tableName')) as pg_indexes_size, \
       pg_size_pretty(pg_total_relation_size('$tableName')) as pg_total_size;

pg_relation_size 仅包括表占用的存储空间，不包括TOAST表或外部扩展数据
pg_table_size 表占用的磁盘空间大小，包括TOAST
pg_indexes_size 索引占用的磁盘空间大小
pg_total_relation_size 表和索引占用的磁盘大小总和

用户&权限

#新建用户user_name，密码password
create user user_name with password 'password';
 
#创建数据库database_name并指定owner为user_name
create database database_name with owner user_name;
 
#授权user_name用户在数据库database_name拥有所有权限
grant all on database database_name to user_name;

插件

查看、开启、关闭插件前，都需要先进入指定的数据库。

\dx                                #查看已安装的插件
CREATE EXTENSION plugins_name;     #开启插件
DROP EXTENSION plugins_name;       #关闭插件

数据库只读设置

#数据库设为只读
alter system set default_transaction_read_only to on;
select pg_reload_conf();
select pg_terminate_backend(pid) from pg_stat_activity where usename not in ('replicator', 'aurora', 'alicloud_rds_admin') and usename not like 'pg%' and pid <> pg_backend_pid();

#（回滚）数据库关闭只读
alter system set default_transaction_read_only to off;
select pg_reload_conf();
select pg_terminate_backend(pid) from pg_stat_activity where usename not in ('replicator', 'aurora', 'alicloud_rds_admin') and usename not like 'pg%' and pid <> pg_backend_pid();

#检查当前是否只读
show default_transaction_read_only;