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=#