denis256 notes and projects

Several essential commands to manage PostgreSQL databases effectively

1. \l or \list: Lists all databases in PostgreSQL.

2. \c [database_name]: Connects to a given database.

3. \dt: Lists all tables in the current database.

4. CREATE DATABASE [database_name];: Creates a new database.

5. DROP DATABASE [database_name];: Deletes a database.

6. CREATE USER [user_name] WITH PASSWORD '[password]';: Creates a new user.

7. ALTER USER [user_name] WITH PASSWORD '[new_password]';: Changes a user’s password.

8. GRANT ALL PRIVILEGES ON DATABASE [database_name] TO [user_name];: Grants all privileges of a specific database to a user.

9. REVOKE ALL PRIVILEGES ON DATABASE [database_name] FROM [user_name];: Revokes all privileges from a user on a specific database.

10. \du: Lists all users and their roles.

11. SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '[database_name]' AND pid <> pg_backend_pid();: Forcefully disconnects users from a database, useful for maintenance or before dropping a database.

12. VACUUM (FULL, ANALYZE, VERBOSE) [table_name];: Recovers space and updates statistics of the database or a specific table. Useful for improving performance and reclaiming disk space.

13. EXPLAIN [query];: Shows the execution plan of a query, useful for query optimization.

14. pg_dump [database_name] > [file_name].sql: Backs up a database to a SQL script file.

15. psql -f [file_name].sql [database_name]: Restores a database from a SQL script file.

16. ALTER TABLE [table_name] ADD COLUMN [column_name] [data_type];: Adds a new column to a table.

17. ALTER TABLE [table_name] DROP COLUMN [column_name];: Removes a column from a table.

18. SELECT * FROM pg_stat_activity;: Displays current activity in the database, such as active queries and connections.