Postgres Commands

PostgreSQL is most advanced general purpose and object relational database management system. It is written in C' Language and first released on 8 July 1996.

Port Number: 5432

Installation:

• Ubuntu: sudo apt install postgresql postgresql-contrib
• CentOS7:sudo yum install postgresql-server postgresql-contrib
• sudo postgresql-setup initdb
• sudo systemctl start postgresql
• sudo systemctl enable postgresql

pg_hba.conf file

Connectivity and logins are controlled from this file. The path of the file is: /var/lib/postgresql/[version]/ and also we can ask postgres to show the file path using:

SHOW hba_file; [OR] SHOW config_file; [OR] using shell command: ps aux  | grep 'postgres *-D'

Login to the server and switch to postgres user and run psql to get into the postgres shell

Login methods: apart from above standard method, we can login to postgres using below methods locally or remotely.

Commands:

postgres=#\l will lists the databases

postgres=#\c <dbname> will switch to desired db

postgres=#\dt => list or display tables inside a db

postgres=#\q to quit from postgres

Other commands which can be run as above are:\

\!                             \dE                \drds                  \ev                        \password
\?                            \des               \dRp                   \f                         \prompt
\a                            \det               \dRs                   \g                         \pset
\C                            \deu              \ds                    \gexec                    \q
\cd                           \dew            \dS                    \gset                      \qecho
\connect                 \df                \dt                     \gx                          \r
\conninfo               \dF                \dT                    \h                            \s
\copy                     \dFd               \du                    \H                          \set
\copyright             \dFp               \dv                    \help                      \setenv
\crosstabview       \dFt               \dx                      \i                            \sf
\d                           \dg                \dy                    \if                            \sv
\da                         \di                \e                         \ir                            \t
\dA                        \dl                \echo                  \l                             \T
\db                        \dL                \ef                    \lo_export                \timing
\dc                        \dm              \elif                   \lo_import                 \unset
\dC                        \dn               \else                 \lo_list                       \w
\dd                        \do              \encoding        \lo_unlink                 \watch
\dD                       \dO              \endif                \o                             \x
\ddp                      \dp            \errverbose        \p                             \z

ACCESS METHOD                 GROUP                         SERVER
AGGREGATE                          INDEX                           STATISTICS
CAST                                      LANGUAGE                  SUBSCRIPTION
COLLATION                           MATERIALIZED VIEW  TABLE
CONVERSION                        OPERATOR                  TABLESPACE
DATABASE                             OWNED                       TEXT SEARCH
DOMAIN                                 POLICY                        TRANSFORM
EVENT TRIGGER                    PUBLICATION             TRIGGER
EXTENSION                            ROLE                           TYPE
FOREIGN DATA WRAPPER    RULE                           USER
FOREIGN TABLE                     SCHEMA                     USER MAPPING FOR
FUNCTION                              SEQUENCE                  VIEW

postgres=#CREATE USER testuser WITH PASSWORD 'testpasswd@202' NOCREATEDB;

postgres=#SELECT username from pg_user;
postgres=#DROP USER <username>

postgres=#CREATE ROLE <rolename>;
postgres=#SELECT rolename FROM pg_roles;
postgres=#DROP ROLE <rolename>

Schema

A postgreSQL cluster contains one more named databases. Users and Groups of users are shared across the entire cluster, but not other data is shared across the databases.

A database contains one or more named schemas, which in turn contain tables. Schema also contain other kinds of named objects, including data types, functions and operators.

postgres=#SELECT nspname FROM pg_catalog.pg_namespace;

SET a defaut database for a user:

Temp: export PGDATABASE=dbname and to permenantly set add a line in users home bash profile. ~/.bashrc

CREATE a readonly user:

postgres=#CREATE USER testuser WITH PASSWORD 'testpasswd@202' NOCREATEDB;

postgres=#GRANT CONNECT on DATABASE "TestDb1" TO username;

postgres=#GRANT SELECT on <tablename> TO username;