Skip to Content

PostgreSQL - gathering info about database

Cheat sheet with commands that will help get info about our database!

Share on:
postgresql database info

Check server version

postgres=## select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

Check client version

[postgres@postgres-lab ~]$ psql --version
psql (PostgreSQL) 12.3

Check database version from PGDATA

[postgres@postgres-lab ~]$ cat $PGDATA/PG_VERSION
12

PostgreSQL support table

PostgreSQL Versioning Policy

Check when database was started

postgres=## select pg_postmaster_start_time();
   pg_postmaster_start_time    
-------------------------------
 2020-07-02 09:05:37.153927+00
(1 row)

Check location of PDGATA - data files

From instance:

postgres=# show data_directory;
  data_directory  
------------------
 /postgresql/data
(1 row)

From OS - look at postmaster which is main PostgreSQL server process after -D parameter we have PGDATA location:

[postgres@postgres-lab ~]$ ps aux | grep postgre
root     15916  0.0  1.2 136144  7380 pts/0    S    19:47   0:00 sudo su - postgres
root     15918  0.0  1.0 124148  6036 pts/0    S    19:47   0:00 su - postgres
postgres 15919  0.0  0.6  23256  3856 pts/0    S    19:47   0:00 -bash
postgres 16007  0.0  0.6  57184  3896 pts/0    R+   19:49   0:00 ps aux
postgres 16008  0.0  0.1  12108   976 pts/0    R+   19:49   0:00 grep --color=auto postgre
postgres 30161  0.0  2.5 286388 14800 ?        Ss   Jul02   0:17 /usr/pgsql-12/bin/postmaster -D /postgresql/data
postgres 30163  0.0  0.2 138648  1368 ?        Ss   Jul02   0:00 postgres: logger   
postgres 30165  0.0  0.5 286504  3336 ?        Ss   Jul02   0:00 postgres: checkpointer   
postgres 30166  0.0  0.5 286508  3332 ?        Ss   Jul02   0:27 postgres: background writer   
postgres 30167  0.0  0.9 286388  5804 ?        Ss   Jul02   0:27 postgres: walwriter   
postgres 30168  0.0  0.4 287036  2656 ?        Ss   Jul02   0:13 postgres: autovacuum launcher   
postgres 30169  0.0  0.4 140884  2836 ?        Ss   Jul02   0:20 postgres: stats collector   
postgres 30170  0.0  0.3 286928  2228 ?        Ss   Jul02   0:03 postgres: logical replication launcher