POSTGRES queries logging

Decription

To provide security standart compliance like PCI DSS 10.2.2 (All actions taken by any
individual with root or administrative privilege), HIPAA and others all administrators actions must be logged.

To provide this requirement Postgres has mutliple options. Most company will be satisfacted with the folowing solution

Solution

This methon will sufficient if your application communicates with PostgreSQL  with dedicated user and you don’t need to log such requests.

Add the folowing parameters to your postgres.conf

log_statement = 'all'  #Log any requests
log_destination = 'stderr,syslog'  #log to file(if you need) and log to syslog
syslog_facility = 'LOCAL0' #default value

Edit your rsyslog configuration:

$EscapeControlCharactersOnReceive off # Remove special characters from log #011
*.*   @192.168.0.10:514  #send to remote rsyslog server

#### RULES ####
local0.* ~ #Don't log local, we'll send it to remote server. Should be the first rule

Alter appplication postgres user to ignore it all queries log:

Alter role APPUSER set log_statement to 'none';

 

Read log

Multiple line entries may be read with my script

cat /var/log/sql/requests.log|awk 'BEGIN{sqlid=""};{idcur=substr($6,2,match($6,"-")-2)};sqlid==idcur{for(i=7;i<=NF;++i)buf=buf " " $i};sqlid!=idcur{buf=buf "\n\n";print buf;sqlid=idcur;buf=$0;}'|grep --color user

It groups line with the same connection ID.

 

 

XID wraparound. Overflow fix manual

Intro

ERROR: database is not accepting commands to avoid wraparound data loss in database «my_database»
Hint: Stop the postmaster and use a standalone backend to vacuum that database.
You might also need to commit or roll back old prepared transactions.

If you see that message you are screwed. Your sequental transaction id (XID) growed to MAXINT.

To find out the problem table run

SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') order by age desc;

Maximum XID is 2147483648. The closer value table have, the close database stuck moment.

If you don’t know (OMG) which database caused an error run the following script

SELECT datname, age(datfrozenxid) FROM pg_database;

Fixing

The only way to fix it is to run VACUUM on the problem table. You have some tweaks to speed up the process:

1. Remove delays in vacuum process by running before vacuum:

   set autovacuum_vacuum_cost_delay to 0;

2. Run postgres single mode

   postgres --single -D /usr/local/pgsql/data -S 10GB my_database

   Key -S specifies work-mem size.

3. Set in postgres.conf parameter vacuum_freeze_min_age to 1000000000 (1 billion). It tells postgres to reduce XID during vacuum to 1 billion, not to zero.

WARNING. VACUUM without FREEZE do not recalc indexes, so work faster.

 

Prevention

1. First of all enable autovacuum or schedule manual vacuum procedures.
2. Set parameter vacuum_freeze_table_age to less value: 10-100 millions. Posgres forces freeze recalc, when table XID grows greater than vacuum_freeze_table_age.

 

List all schema’s function code:

 psql -U postgres my_db  -c '\df+ partman.*'| \
cut -d '|' -f2,10|sed -s 's/                               //g'|less

View global avtovacuum settings:

select name, setting, unit
from pg_settings 
where name like 'autovacuum%';

Show single table avtovacuum configuration

select relname, reloptions
from pg_class where reloptions::TEXT like '%autovacuum%';

Set vacuum and analyse scale factor and threshold for a table:

ALTER TABLE piblic.log SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE piblic.log SET (autovacuum_vacuum_threshold = 1000000);
ALTER TABLE piblic.log SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE piblic.log SET (autovacuum_analyze_threshold = 1000000);

List Postgres tables and schemas in tablespaces

select relname, nspname as schema, spcname as tablespace from 
  pg_class cl, 
  pg_namespace ns, 
  pg_tablespace ts 
where 
  cl.relnamespace=ns.oid and 
  cl.reltablespace=ts.oid and 
  relkind='r'; 

Example output:

      relname       |   schema   | tablespace 
--------------------+------------+------------
 pg_authid          | pg_catalog | pg_global
 pg_database        | pg_catalog | pg_global
 pg_db_role_setting | pg_catalog | pg_global
 pg_tablespace      | pg_catalog | pg_global
 pg_pltemplate      | pg_catalog | pg_global
 pg_auth_members    | pg_catalog | pg_global
 pg_shdepend        | pg_catalog | pg_global
 pg_shdescription   | pg_catalog | pg_global
 pg_shseclabel      | pg_catalog | pg_global