How to access postgres
sudo -u <username> psql
Disconnect from postgres (psql)
\q
List Connection Information (psql)
\conninfo
Create a new User/Role
sudo -u postgres createuser --interactive
List all databases (psql)
\l
List all users in postgres (psql)
\du
Create a Database (psql)
create database <database name>
Steps to add a new user (psql)
sudo -u postgres createuser --interactive
sudo -u postgres createdb <username>
sudo adduser <username>
Set user's password
ALTER USER <username> PASSWORD 'newPassword';
OR
\password <username>
ACCESS PROVILEGES
GRANT CONNECT ON DATABASE my_db TO my_user;
GRANT USAGE ON SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;
Get the version of Postgresql (psql)
SELECT version();
Get the size of a single database (psql)
SELECT pg_database_size(current_database());
Get the size of all databases (psql)
SELECT sum(pg_database_size(datname)) from pg_database;
Get the uptime of the server (psql)
SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
Get total size of a database
SELECT pg_database_size(current_database());
Get total size of all Databases
SELECT sum(pg_database_size(datname)) from pg_database;
Restart Postgres server
sudo systemctl restart postgresql
