Postgres Commands

Access Postgres as SuperUser

Use this terminal command to access the local PostgreSQL instance as the postgres user:

sudo -u postgres psql postgres

To listList databaseDatabase privileges:
Privileges

SELECT * FROM information_schema. table_privileges
where grantee = 'icore_client';

Good reference on how to do admin tasks from the command line: https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

Basically, this:

// Open a command line session with the postgres engine:
sudo -u postgres psql
// Create a database:
create database mydb;
// Create a user:
create user myuser with encrypted password 'mypass';
// Give the user access to the created database:
grant all privileges on database mydb to myuser;

To give a user privilege to create databases, use this:

ALTER USER postgresclient CREATEDB;

To give a user Super User privileges:

ALTER USER librarian WITH SUPERUSER;

Restoring Database from Command Line

Taken from here: https://simplebackups.com/blog/postgresql-pgdump-and-pgrestore-guide-examples/

Here’s a quick command to restore a database from a backup file:

pg_restore -U postgresclient -Ft -C -d dbProjectControls < /path-to-file/backupfilename.tar

// Do this to open a "postgres-#" session...
sudo -u postgres psql

// List databases with...
\l

// Switch databases with...
\c databasename

Admin Tasks

Good list of admin tasks: https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/

Another good reference to digest: https://hasura.io/blog/top-psql-commands-and-flags-you-need-to-know-postgresql/