跳到主要内容

PostgreSQL commands

· 阅读需 2 分钟

Database Management​

List All Databases​

\l

Connect to a Database​

\c database_name

Create a New Database​

CREATE DATABASE database_name;

Drop a Database​

DROP DATABASE database_name;

Check Database Size​

SELECT pg_size_pretty(pg_database_size('database_name'));

List All Users/Roles​

\du

Check Current Database​

SELECT current_database();

Table Management​

List All Tables in the Current Database​

\dt

List All Tables with Additional Details​

\dt+

Describe a Table (Columns, Types, etc.)​

\d table_name

Show Table Schema​

\d+ table_name

Check Table Size (Including Indexes)​

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Check Table Row Count​

SELECT count(*) FROM table_name;

Show Indexes of a Table​

\di table_name

Show Table Constraints​

\d table_name

Query Execution and Statistics​

Explain a Query (Execution Plan)​

EXPLAIN SELECT * FROM table_name;

Explain with Detailed Statistics​

EXPLAIN ANALYZE SELECT * FROM table_name;

Show Query Execution Time​

\timing

Show Active Queries​

SELECT * FROM pg_stat_activity;

Cancel a Running Query​

SELECT pg_cancel_backend(pid);

Terminate a Running Query​

SELECT pg_terminate_backend(pid);

User and Role Management​

Create a New User/Role​

CREATE ROLE role_name WITH LOGIN PASSWORD 'password';

Grant Privileges to a User​

GRANT ALL PRIVILEGES ON DATABASE database_name TO role_name;

Revoke Privileges from a User​

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM role_name;

Alter User Password​

ALTER USER role_name WITH PASSWORD 'new_password';

Delete a User/Role​

DROP ROLE role_name;

Backup and Restore​

Backup a Database​

pg_dump -U username -d database_name -f backup_file.sql

Restore a Database​

psql -U username -d database_name -f backup_file.sql

Backup All Databases​

pg_dumpall -U username -f backup_file.sql

Restore All Databases​

psql -U username -f backup_file.sql

Miscellaneous​

Show PostgreSQL Version​

SELECT version();

Show Current User​

SELECT current_user;

Show All Settings​

SHOW ALL;

Quit psql​

\q

Help for SQL Commands​

\h

Help for psql Commands​

\?