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)
Shekhar Gulati 