zPnCUNf3

1## Gain access to the PostgreSQL console (Linux) - ( using user postgres)
2
3- sudo -u postgres psql
4
5## List all databases
6
7- \l
8
9- \list or \l: list all databases
10
11## List all tables in the current (connected to) database
12
13- \dt: list all tables in the current database
14
15## Connect to a database
16
17- \c my_database_name
18
19## Shows detailed information about a table
20
21- \d table_name
22
23## See all available commands
24
25- \?
26
27## Quit postgres CLI
28
29- \q
30
31## Drop all columns in your db and re-create the DB (with no tables)
32
33- drop schema public cascade; create schema public;
34
35## Change a user's password
36
37- ALTER USER postgres PASSWORD 'newPassword';
38
39## start, stop and restart PostgreSQL database
40
41- service postgresql stop
42  Stopping PostgreSQL: server stopped
43  ok
44
45- service postgresql start
46  Starting PostgreSQL: ok
47
48- service postgresql restart
49  Restarting PostgreSQL: server stopped
50  ok
51
52## create a PostgreSQL user
53
54- CREATE USER john WITH password 'johndoe';
55
56## create a PostgreSQL Database
57
58- CREATE DATABASE mydb WITH OWNER john;
59
60## Delete/Drop an existing PostgreSQL database
61
62- DROP DATABASE mydb;
63- dropdb DATABASENAME;
64
65## Create Roles From Within PostgreSQL
66
67- CREATE ROLE new_role_name;
68
69## Delete Roles In PostgreSQL
70
71- DROP ROLE role_name;
72
73## To avoid this situation and make the drop command delete a user if present and quietly do nothing if the user does not exist, use the following syntax:
74
75- DROP ROLE IF EXISTS role_name;
76
77## Define Privileges Upon Role Creation
78
79- CREATE ROLE role_name WITH optional_permissions;
80
81## see full list of the options by typing:
82
83- \h CREATE ROLE
84
85## to give this user the ability to log in, so we will type:
86
87- CREATE ROLE demo_role WITH LOGIN;
88
89## How to Log In as a Different User in PostgreSQL
90
91- psql -U user_name -d database_name -h 127.0.0.1 -W
92
93## How to Grant Permissions in PostgreSQL
94
95- GRANT permission_type ON table_name TO role_name;
96
97## How to Remove Permissions in PostgreSQL
98
99- REVOKE permission_type ON table_name FROM user_name;
100- REVOKE INSERT ON demo FROM PUBLIC;
101
102## How to Use Group Roles in PostgreSQL
103
104- CREATE ROLE temporary_users;
105  GRANT temporary_users TO demo_role;
106  GRANT temporary_users TO test_user;
107
108## List all users
109
110- psql
111  \du
112
113## Remove a user
114
115- dropuser USERNAME
116
117## Update password of user
118
119- psql
120  alter user USERNAME with password 'NEWPASSWORD';
121
122## Create a database and user, assign user to database
123
124- createuser -P USERNAME
125  createdb -O USERNAME DATABASENAME
126
127## Remove all tables of a schema (if all tables in a single schema)
128
129- drop schema public cascade;
130  create schema public;
131
132## import sql from file into postgres server
133
134- sudo -u postgres psql DATABASENAME -f filename.sql