Basic commands - PostgreSQL

postgres=#  \set

AUTOCOMMIT = 'on'

COMP_KEYWORD_CASE = 'preserve-upper'

DBNAME = 'postgres'

ECHO = 'none'

ECHO_HIDDEN = 'off'

ENCODING = 'UTF8'

FETCH_COUNT = '0'

HISTCONTROL = 'none'

HISTSIZE = '500'

HOST = '/tmp'

IGNOREEOF = '0'

ON_ERROR_ROLLBACK = 'off'

ON_ERROR_STOP = 'off'

PORT = '5432'

PROMPT1 = '%/%R%# '

PROMPT2 = '%/%R%# '

PROMPT3 = '>> '

QUIET = 'off'

SERVER_VERSION_NAME = '10.9'

SERVER_VERSION_NUM = '100009'

SHOW_CONTEXT = 'errors'

SINGLELINE = 'off'

SINGLESTEP = 'off'

USER = 'postgres'

VERBOSITY = 'default'

VERSION = 'PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit'

VERSION_NAME = '10.9'

VERSION_NUM = '100009'

===========================================================

postgres=# select datname,pid,usename,wait_event_type,wait_event,state,backend_xid,query from pg_stat_activity;

 datname  |  pid  | usename  | wait_event_type |    wait_event    | state  | backend_xid |                                                query
----------+-------+----------+-----------------+------------------+--------+-------------+---------------------------------------------------------------------------------------------------
---
 postgres | 48887 | postgres |                 |                  | active |             | select datname,pid,usename,wait_event_type,wait_event,state,backend_xid,query from pg_stat_activit
y;
          | 12230 |          | Activity        | RecoveryWalAll   |        |             |
          | 12357 |          | Activity        | BgWriterMain     |        |             |
          | 12356 |          | Activity        | CheckpointerMain |        |             |
          | 45521 |          | Activity        | WalReceiverMain  |        |             |
(5 rows)

==========================================================================

postgres=# select version();

                                                version

--------------------------------------------------------------------------------------------------------

 PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

(1 row)

==========================================================================

postgres=#  select datname from pg_database;

  datname

-----------

 postgres

 samik

 template1

 template0

 amit

 priya

 samik1

 manoj

 samik2

 sandeep

 sandeep1

 samik3

 samik4

(13 rows)

==========================================================================

postgres=#  select pg_backend_pid();

 pg_backend_pid

----------------

          48887

(1 row)

==========================================================================

postgres=# select pg_terminate_backend(48887);

FATAL:  terminating connection due to administrator command

server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

==========================================================================

postgres=# select pid from pg_stat_activity;

  pid

-------

 50471

 12230

 12357

 12356

 45521

(5 rows)

==========================================================================

postgres=# show data_directory;

     data_directory

-------------------------

 /u02/PostgreSQL/10/data

(1 row)

=====================================================================

postgres=# select current_user;

 current_user

--------------

 postgres

(1 row)

====================================================================