postgresql常用命令

最新推荐文章于 2023-01-27 18:44:51 发布

S1156578253

最新推荐文章于 2023-01-27 18:44:51 发布

阅读量217

点赞数

文章标签： postgresql

本文链接：https://blog.csdn.net/S1156578253/article/details/120707229

版权

1、创建数据库

create database test;

postgres=# create database test;
CREATE DATABASE
postgres=#

2、列出所有的数据库

\l 或者\list

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=#

通过SQL查询所有数据库：

select datname from pg_database;

postgres=# select datname from pg_database;
  datname  
-----------
 postgres
 test
 template1
 template0
(4 rows)

postgres=#

3、列出数据库更详细的大小，包括数据库大小：

\l+

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Table
space |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------
------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7953 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7809 kB | pg_default | 
(4 rows)

postgres=#

4、查看数据库版本：
select version();

postgres=# select version();
                                                 version                                              
   
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=#

5、查看某个数据库的大小：
select pg_size_pretty(pg_database_size('test'));

查看某个数据库的大小：
postgres=# select pg_size_pretty(pg_database_size('test'));
 pg_size_pretty 
----------------
 7809 kB
(1 row)

postgres=#

6、查看所有数据库的大小：
select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) from pg_database;

查看所有数据库的大小：
postgres=# select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) from pg_database;
  datname  | pg_size_pretty 
-----------+----------------
 postgres  | 7953 kB
 test      | 7809 kB
 template1 | 7809 kB
 template0 | 7809 kB
(4 rows)

postgres=#

7、连接到test数据库
\c test

连接到test数据库
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#

8、切换到dbmonitor用户

\c - dbmonitor

test=# \c - dbmonitor
You are now connected to database "test" as user "dbmonitor".
test=>

9、列出当前数据库的连接信息：
\conninfo

列出当前数据库的连接信息：
test=# \conninfo
You are connected to database "test" as user "postgres" via socket in "/tmp" at port "5432".
test=#

10、查看当前连接的用户：
select user;或者 select * from current_user;1

1、查看当前连接的用户：
test=# select user;
   user   
----------
 postgres
(1 row)


2、test=# select * from current_user;
 current_user 
--------------
 postgres
(1 row)

test=#