Postgres Commands

postgres.md

Install

apt-get install postgresql-12

Login to Postgres as the default user

sudo -u postgres psql

Change password for postgres user

Note: Command is to be run inside the psql shell

ALTER USER postgres PASSWORD 'myPassword';

Create superuser and assign a password immediately

Note: Command is to be run inside the psql shell

CREATE ROLE jasdeep WITH
	LOGIN
	SUPERUSER
	CREATEDB
	CREATEROLE
	INHERIT
	NOREPLICATION
	CONNECTION LIMIT -1
	PASSWORD 'xxxxxx';

Create database

CREATE DATABASE testdb
    WITH 
    OWNER = jasdeep
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Connection string format

postgres://{db_username}:{db_password}@{host}:{port}/{db_name}

e.g. postgres://jasdeep:password@localhost:5432/testdb

List Databases

sudo -u postgres psql -l

Or inside a psql prompt use \l+:

postgres=# \l+

List Tables and Rows in a Database

sudo -u postgres psql -d testdb
# Enter into psql with active database
testdb=# \d
testdb=# SELECT * FROM data;

Returns

             List of relations
 Schema |    Name     |   Type   |  Owner  
--------+-------------+----------+---------
 public | data        | table    | jasdeep
 public | data_id_seq | sequence | jasdeep
(2 rows)

Exit psql

Finally, exit the psql client by using the \q command.

postgres=# \q