Important psql Commands for DevOps
Basic Commands
1. Connect to a Database:
psql -U username -h hostname -d database_name
2. List Databases:
\l
3. List Tables in the Current Database:
\dt
4. Describe a Table:
\d table_name
5. Show Current Database:
\c
6. Show Connections:
\conninfo
Database Management
7. Create a Database:
CREATE DATABASE database_name;
8. Drop a Database:
DROP DATABASE database_name;
9. Create a User:
CREATE USER username WITH PASSWORD 'password';
10. Grant Privileges to a User:
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
Backup and Restore
11. Dump a Database:
pg_dump -U username -h hostname -Fc database_name > database_dump.dump
12. Restore a Database:
pg_restore -U username -h hostname -d database_name database_dump.dump
Monitoring and Maintenance
13. Show Active Queries:
SELECT * FROM pg_stat_activity;
14. Show Database Size:
\l+
15. Show Table Size:
\dt+
16. Vacuum a Table:
VACUUM table_name;
17. Analyze a Table:
ANALYZE table_name;
Configuration and Settings
18. Show Configuration Settings:
SHOW ALL;
19. Set a Configuration Parameter:
ALTER SYSTEM SET parameter_name = 'value';
20. Reload Configuration:
SELECT pg_reload_conf();
Security and Access Control
21. Change User Password:
ALTER USER username WITH PASSWORD 'new_password';
22. Revoke Privileges from a User:
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;
23. Show User Roles:
\du
Advanced Commands
24. Execute a SQL Script:
\i path_to_noscript.sql
25. Copy Data to/from a File:
\copy table_name TO 'file_path' WITH CSV;
\copy table_name FROM 'file_path' WITH CSV;
26. Explain Query Plan:
EXPLAIN ANALYZE SELECT * FROM table_name;
Troubleshooting
27. Show Log Files:
SHOW log_directory;
28. Check for Locks:
SELECT * FROM pg_locks;
These commands cover a wide range of tasks that a DevOps engineer might need to perform when managing PostgreSQL databases. Familiarity with these commands can greatly enhance your ability to maintain, monitor, and troubleshoot PostgreSQL databases effectively.
🔥2
Forbidden PTY allocation request with ssh
no-pty,no-X11-forwarding,permitopen="localhost:3306",command="/bin/echo do-not-send-commands"
DB size
Table sizes
SELECT
table_schema AS 'DB Name',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'DB Size in MB'
FROM
information_schema.tables
GROUP BY
table_schema;
Table sizes
SELECT
table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM
information_schema.TABLES
WHERE
table_schema = "<your-database-name>"
ORDER BY
(data_length + index_length) DESC;
❤1
Processes Signals in Linux
kill -KILL pid
kill -HUP pid
kill -TERM pid
pkill name
killall name