个人最常用的PostgreSQL命令整理

发表于 更新于 阅读次数： 本文字数： 604 阅读时长 ≈ 2 分钟

psql命令

列出所有数据库:

1
psql -l

进入指定数据库：

1
psql -d my_db_name

带用户名密码进入指定数据库:

1
psql -h your_host_name_or_ip -p your_port -U username -W

执行sql文件：

1
psql -d my_db_name -f a.sql

通过一条终端指令执行简单操作：

1
psql -d my_db_name -c "\dt"

上述指令可替代如下三条指令构成的操作序列：

1
2
3
psql -d my_db_name
\dt
\q

单步执行SQL指令，每一步都要敲回车确认:

1
psql -s

查看版本号：

1
psql -V

数据库信息查看

查看有哪些用户:

1
\du

切换数据库:

1
\c my_db_name

查看有哪些数据库：

1
\l

查看当前数据库下有哪些表:

1
\dt

查看某个表的信息:

1
\d my_table_name

查看有哪些索引:

1
\di

查看有哪些表空间：

1
\db

退出psql：

1
\q

执行外部SQL文件:

1
\i a.sql

把后续执行结果写入外部文件,直到退出psql:

1
\o output_file.txt

查看可用指令列表：

1
\?

查看某个命令的语法帮助:

1
\h create table

数据库使用状态查看

查看数据库占用空间大小:

1
select pg_database_size('my_test_db');

查看所有数据库占用空间的大小:

1
select datname, pg_database_size(datname) AS size from pg_database;

查看所有数据库占用空间大小，并以KB、MB等可读方式显示:

1
select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;

查看表或索引大小:

1
select pg_relation_size('my_table_or_index_name');

查看表的总大小，包括索引:

1
select pg_size_pretty(pg_total_relation_size(my_table));

查看表空间大小:

1
select pg_size_pretty(pg_tablespace_size('pg_default'));

数据库、用户设置

创建用户:

1
create user my_test_user with password 'my_password';

创建数据库:

1
create database my_test_db;

把数据库分配给用户:

1
alter database my_test_db owner my_test_user;

直接创建用户并设置属主：

1
create database my_test_db_2 owner my_test_user;

别忘了把权限赋予用户:

1
grant all privileges on database my_test_db to my_test_user;

如果用新创建的数据库用户登录报以下错误：
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "my_test_user"
这是因为，该机器上psql的连接建立于Unix Socket上默认使用peer authentication，必须要用和数据库用户相同的系统用户进行登录。
解决办法也简单，要么创建对应的操作系统用户my_test_user后sudo su - my_test_user再用psql -d my_test_db登录，要么把登录认证方式从peer authentiction改为md5。

如果要收回权限，使用下面语句:

1
revoke all on database my_test_db from my_test_user;

删除数据库用户，使用下面语句：

1
drop user my_test_user;