کانال انجمن کلاد و دوآپس تبریز

no-pty,no-X11-forwarding,permitopen="localhost:3306",command="/bin/echo do-not-send-commands"

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_script.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.

docker -H ssh://user@remote:port ps