
Создадим БД и таблицу
~~~~~~~~~~~~~~~~~~~~~


=> create database db8;
CREATE DATABASE

=> \c db8
You are now connected to database "db8" as user "postgres".

=> create table t(n numeric, t text);
CREATE TABLE

=> insert into t values (1, repeat('A',1000));
INSERT 0 1

Параметры (а)
~~~~~~~~~~~~~


=> alter system reset all;
ALTER SYSTEM

=> alter system set full_page_writes = on;
ALTER SYSTEM

=> alter system set wal_compression = off;
ALTER SYSTEM

=> \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
psql 

=> \c db8
You are now connected to database "db8" as user "postgres".

=> checkpoint;
CHECKPOINT

Выполняем изменение и определяем размер журнала.

=> select pg_current_xlog_location() as start_lsn \gset

=> \setenv START_LSN :start_lsn

=> select pg_xlogfile_name(:'start_lsn') as start_seg \gset

=> \setenv START_SEG :start_seg

=> update t set n=n+1;
UPDATE 1

=> select pg_current_xlog_location() as end_lsn \gset

=> \setenv END_LSN :end_lsn

=> select :'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn;
 ?column? 
----------
     2208
(1 row)


Журнал включает запись об изменении строки с занятой частью страницы (FPW = Full Page Write) и запись о фиксации изменений.

=> \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN $START_SEG
rmgr: Heap        len (rec/tot):     14/  2169, tx:       4083, lsn: 0/4A4C8394, prev 0/4A4C832C, desc: HOT_UPDATE off 1 xmax 4083 ; new off 2 xmax 0, blkref #0: rel 1663/33466/33467 blk 0 FPW
rmgr: Transaction len (rec/tot):      8/    34, tx:       4083, lsn: 0/4A4C8C10, prev 0/4A4C8394, desc: COMMIT 2016-07-02 16:31:22.172545 MSK


Параметры (б)
~~~~~~~~~~~~~


=> alter system set full_page_writes = on;
ALTER SYSTEM

=> alter system set wal_compression = on;
ALTER SYSTEM

=> \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
psql 

=> \c db8
You are now connected to database "db8" as user "postgres".

=> checkpoint;
CHECKPOINT

Выполняем изменение и определяем размер журнала.

=> select pg_current_xlog_location() as start_lsn \gset

=> \setenv START_LSN :start_lsn

=> select pg_xlogfile_name(:'start_lsn') as start_seg \gset

=> \setenv START_SEG :start_seg

=> update t set n=n+1;
UPDATE 1

=> select pg_current_xlog_location() as end_lsn \gset

=> \setenv END_LSN :end_lsn

=> select :'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn;
 ?column? 
----------
      240
(1 row)


Журнал включает запись об изменении строки вместе со сжатой страницей и запись о фиксации изменений.

=> \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN $START_SEG
rmgr: Heap        len (rec/tot):     14/   204, tx:       4084, lsn: 0/4A4C8D04, prev 0/4A4C8C9C, desc: HOT_UPDATE off 2 xmax 4084 ; new off 3 xmax 0, blkref #0: rel 1663/33466/33467 blk 0 FPW
rmgr: Transaction len (rec/tot):      8/    34, tx:       4084, lsn: 0/4A4C8DD0, prev 0/4A4C8D04, desc: COMMIT 2016-07-02 16:31:24.363218 MSK


Параметры (в)
~~~~~~~~~~~~~


=> alter system set full_page_writes = off;
ALTER SYSTEM

=> \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
psql 

=> \c db8
You are now connected to database "db8" as user "postgres".

=> checkpoint;
CHECKPOINT

Выполняем изменение и определяем размер журнала.

=> select pg_current_xlog_location() as start_lsn \gset

=> \setenv START_LSN :start_lsn

=> select pg_xlogfile_name(:'start_lsn') as start_seg \gset

=> \setenv START_SEG :start_seg

=> update t set n=n+1;
UPDATE 1

=> select pg_current_xlog_location() as end_lsn \gset

=> \setenv END_LSN :end_lsn

=> select :'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn;
 ?column? 
----------
      108
(1 row)


Журнал включает только запись об изменении строки и фиксации.


=> \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN $START_SEG
rmgr: Heap        len (rec/tot):     14/    71, tx:       4085, lsn: 0/4A4C8EC4, prev 0/4A4C8E5C, desc: HOT_UPDATE off 3 xmax 4085 ; new off 4 xmax 0, blkref #0: rel 1663/33466/33467 blk 0
rmgr: Transaction len (rec/tot):      8/    34, tx:       4085, lsn: 0/4A4C8F0C, prev 0/4A4C8EC4, desc: COMMIT 2016-07-02 16:31:26.534272 MSK

Восстанавливаем настройки
~~~~~~~~~~~~~~~~~~~~~~~~~


=> alter system reset all;
ALTER SYSTEM

=> \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
