LOG MANAGEMENT in PostgreSQL

 

bash-4.1$ cd /home/prod

bash-4.1$ vi postgresql.conf 

LAB ONE :-

log_destination='stderr'

logging_collector=on

log_directory='prod_log'

log_filename='prod-%Y-%m-%d_%H%M%S.log'

log_rotation_age='1d'

log_rotation_size='10MB'

pg_ctl -D /home/prod restart

LAB TWO :-

log_min_duration_statement=2000

pg_ctl -D /home/prod restart

psql -p 5433

/% CREATE TABLE AND CHECK FOR SLOW RUNNING QUERIES UNDER LOG DIRECTORY %/

LAB THREE :-

log_connections=on

log_duration =on

log_line_prefix='%a %t %u %d %r '

log_statement ='all' -----------------------all statement will be captured in log file

:wq

/% CREATE TABLE AND CHECK FOR QUERIES UNDER LOG DIRECTORY %/

pg_ctl -D /home/prod restart

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

bash-4.1$ psql -p 5433

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

  data_directory  

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

 /home/prod

(1 row)

postgres=# show log_destination;

 log_destination 

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

 stderr

(1 row)

postgres=# show log_filename;

         log_filename          

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

 capgemini-%Y-%m-%d_%H%M%S.log

(1 row)

postgres=# show log_rotation_age;

 log_rotation_age 

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

 1d

(1 row)

postgres=# show log_rotation_size;

 log_rotation_size 

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

 10MB

(1 row)

postgres=# show log_min_duration_statement;

 log_min_duration_statement 

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

 2s

(1 row)

postgres=# create table log(id int);

CREATE TABLE

postgres=# insert into log values(generate_Series(1,10000));

INSERT 0 10000

postgres=# insert into log values(generate_Series(1,100000));

INSERT 0 100000

postgres=# insert into log values(generate_Series(1,1000000));

INSERT 0 1000000

postgres=# commit;