Author: shekhargulati

To find out the location of PostgreSQL configuration files, just connect with your PostgreSQL database and run the following command.

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

To describe a table, you should use

\d+ table_name

To list all the databases,you should run following command.

\list
\l

To view all the tables in a schema

\dt+ pg_catalog.pg_t*

To view core settings of a PostgreSQL server, you should use

SHOW ALL;

To view value of a specific setting like effective_cache_size

SHOW effective_cache_size;

To reload the configuration changes

SELECT pg_reload_conf();

To create a new LOGIN user

CREATE ROLE shekhar LOGIN PASSWORD 'p@ssw0rd';

To create a new database

CREATE DATABASE myappdb;

Schema are logical compartments in a database. You can divide your database into small logical schemas.

CREATE SCHEMA etcs;

To view all the available extensions

SELECT * from pg_available_extensions;

To view details about an extension
To view all the available extensions

\dx+ plpgsql;

To install an extension and view its details

postgres=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
postgres=# \dx+ fuzzystrmatch;
                    Objects in extension "fuzzystrmatch"
                             Object Description
----------------------------------------------------------------------------
 function difference(text,text)
 function dmetaphone(text)
 function dmetaphone_alt(text)
 function levenshtein(text,text)
 function levenshtein(text,text,integer,integer,integer)
 function levenshtein_less_equal(text,text,integer)
 function levenshtein_less_equal(text,text,integer,integer,integer,integer)
 function metaphone(text,integer)
 function soundex(text)
 function text_soundex(text)
(10 rows)

To view the active running processes

postgres=# SELECT pid, usename from pg_stat_activity;
 pid  | usename
------+----------
 1982 | postgres
 2742 | postgres
 2236 | postgres
 2723 | shekhar
 2744 | postgres
(5 rows)

To kill a connection

postgres=# SELECT pg_terminate_backend(2723);
 pg_terminate_backend
----------------------
 t
(1 row)

To kill all the connection by user shekhar

SELECT pg_terminate_backend(pid) FROM pg_stat_activity where usename = 'shekhar';

To run a script file, run the following command.

psql -f <path_to_filefile>

You can also run commands with psql non-interactively

psql -d mydb -c "CREATE SCHEMA test;"

The \set command can be used to create user defined shortcuts like as shown below.

\set connections_check 'SELECT pid, usename from pg_stat_activity;'

The .psqlrc file can be used to define configurations for a session. You can use PSQLRC environment variable to control the location of the startup file.

\pset null 'Null'
\encoding latin1
\set PROMPT1 '%n@%M:%>%x %/#'
\set PROMPT2
\timing on
\pset pager always

To turn on timing for the queries execution time

\timing

You can call OS shell commands from within psql as shown below.

admin2pnvtk8:myapp#\! env|grep POSTGRES

To view uptime of your PostgreSQL database in minutes, run the following query.

myapp=# select date_trunc('minute',current_timestamp - pg_postmaster_start_time()) as "postgresql_uptime";
 postgresql_uptime 
-------------------
 00:12:00
(1 row)