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
\?