Useful Postgres tables queries

03/26/2020 1047 11274

DESCRIPTION:

It is useful to see if summarized data is written in the Postgres database. Below are some of the example query which may be useful in some circumstances. 

RESOLUTION:

1. Connect to Postgres database using KB: 200356
   </br>
2. To see if summarized data currently being written in the table:
   
   
   syslogs=# select count(*) from raw_201607;
     count
   ---------
    6329367 --------> make a note of the figure
   (1 row)
   
   - We can use the same command few times to see if the raw is increasing in numbers. If the number goes up this indicates data is being currently written in the reporting database. 
   </br>
3. To see when the last time data being written in the table:
   
   syslogs=# select max(rcvd_time) from raw_201607;
            max
   ---------------------
    2016-07-19 10:11:55
   (1 row)
   </br>

4. To see the number of raws of data for a specific month

   syslogs=# select count(*) from raw_201607;

   count --------- 1090081 (1 row)
   </br>
5. To see the list of Firewalls serials with number of rows:
   
   syslogs=# select serial, count(1) from raw_201607 group by serial;
   serial                    | count
   ---------------------------+--------------
   C0EAE4819A66    | 240490
   C0EAE4AF3D7C  | 305249
   C0EAE4744004    |   6378
   </br>
6. To see the list of tables in the database:
   
   
   syslogs=# dt
                           List of relations
    Schema |      Name      | Type  |  Owner   |       Options
   --------+----------------+-------+----------+---------------------
    public | raw_201512     | table | postgres | {engine=infobright}
    public | raw_201601     | table | postgres | {engine=infobright}
    public | summary_status | table | mydbuser | {engine=postgres}
   </br>
7. To see the list of users:
   
   
   syslogs=# du  
                                  List of roles
      Role name   |                   Attributes                                    | Member of
   ---------------+--------------------------------------------------------------------------+-----------
    administrator | Superuser, Create role, Create DB                   | {}
    postgres       | Superuser, Create role, Create DB, Replication | {}
   </br>
8. To exit from the database
   
   
   syslogs=# q + Enter