Last active
February 9, 2025 13:27
-
-
Save sreeharikodavalam/e46dca6f1cba93502cec32dc321a6dc3 to your computer and use it in GitHub Desktop.
PostgreSQL Essential Commands Guide
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #PostgreSQL Essential Commands Guide | |
| Quick Database Backup | |
| -------------------- | |
| sudo su - postgres | |
| pg_dump database_name > /tmp/backup.sql | |
| User Management | |
| -------------- | |
| // Create new user | |
| CREATE USER myuser WITH PASSWORD 'mypassword'; | |
| // Create superuser | |
| CREATE USER myuser WITH SUPERUSER PASSWORD 'mypassword'; | |
| // Change user password | |
| ALTER USER myuser WITH PASSWORD 'newpassword'; | |
| // Grant database access | |
| GRANT ALL PRIVILEGES ON DATABASE dbname TO myuser; | |
| // Remove privileges | |
| REVOKE ALL PRIVILEGES ON DATABASE dbname FROM myuser; | |
| // Delete user | |
| DROP USER myuser; | |
| Database Management | |
| ------------------ | |
| // Create database | |
| CREATE DATABASE dbname; | |
| CREATE DATABASE dbname OWNER myuser; | |
| // Grant permissions | |
| GRANT ALL PRIVILEGES ON DATABASE dbname TO myuser; | |
| GRANT CONNECT ON DATABASE dbname TO myuser; | |
| GRANT USAGE ON SCHEMA public TO myuser; | |
| GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser; | |
| // Change database owner | |
| ALTER DATABASE dbname OWNER TO myuser; | |
| Schema Permissions | |
| ---------------- | |
| // Grant schema permissions | |
| GRANT USAGE ON SCHEMA schema_name TO myuser; | |
| GRANT ALL ON SCHEMA schema_name TO myuser; | |
| // Grant table permissions in schema | |
| GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO myuser; | |
| GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO myuser; | |
| // Set default permissions for new tables | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name | |
| GRANT ALL ON TABLES TO myuser; | |
| Export/Import Commands | |
| -------------------- | |
| // Export database | |
| pg_dump database_name > output_file.sql | |
| pg_dump -U username database_name > output_file.sql | |
| pg_dump -Fc database_name > output_file.dump | |
| // Import database | |
| psql database_name < input_file.sql | |
| psql -U username database_name < input_file.sql | |
| pg_restore -d database_name input_file.dump | |
| Common Options | |
| ------------- | |
| -U : username | |
| -h : host | |
| -p : port | |
| -F : format (c=custom, t=tar, p=plain) | |
| -v : verbose | |
| Essential PSQL Commands | |
| --------------------- | |
| \l // List all databases | |
| \c dbname // Connect to database | |
| \dt // List tables | |
| \du // List users | |
| \dn // List schemas | |
| \dp // List table permissions | |
| \q // Quit psql | |
| Reset Public Schema | |
| ----------------- | |
| // Reset public schema | |
| DROP SCHEMA public CASCADE; | |
| CREATE SCHEMA public; | |
| GRANT ALL ON SCHEMA public TO postgres; | |
| GRANT ALL ON SCHEMA public TO public; | |
| Full Access Configuration | |
| ----------------------- | |
| // Grant full access to specific user | |
| GRANT ALL PRIVILEGES ON DATABASE dbname TO myuser; | |
| \c dbname | |
| GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser; | |
| GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser; | |
| GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO myuser; | |
| Advanced Backup & Restore | |
| ----------------------- | |
| // Full backup with options | |
| pg_dump -h localhost -p 5432 -U username -F c -b -v -f output_file.backup database_name | |
| // Restore with options | |
| pg_restore -h localhost -p 5432 -U username -d database_name -v output_file.backup |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment