
ЖУРНАЛ УПРЕЖДАЮЩЕЙ ЗАПИСИ
~~~~~~~~~~~~~~~~~~~~~~~~~

Посмотрим на содержимое журнала для одних и тех же действий
при разных уровнях, установленных в параметре wal_level.

Сначала создадим отдельную базу данных:

        => create database db8;
        CREATE DATABASE

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

.......................................................................

Создадим таблицу:

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

.......................................................................

MINIMAL
~~~~~~~

Установим уровень minimal.

        => alter system set wal_level = minimal;
        ALTER SYSTEM

А также выключим параметры full_page_writes и wal_log_hints,
чтобы не загромождать журнал.

        => alter system set full_page_writes = off;
        ALTER SYSTEM

        => alter system set wal_log_hints = 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".

.......................................................................

Начинаем транзакцию.

        => begin;
        BEGIN

        => select txid_current();
         txid_current 
        --------------
                 3763
        (1 row)
        

        => select txid_current() as txid \gset

        => \setenv TXID :txid

.......................................................................

Текущая позиция в журнале:

        => select pg_current_xlog_location();
         pg_current_xlog_location 
        --------------------------
         0/2ED7FA8
        (1 row)
        

LSN выводится как два 32-битных числа в шестнадцатиричной системе через косую черту.

        => select pg_current_xlog_location() as start_lsn \gset

        => \setenv START_LSN :start_lsn

Эта позиция соответствует файлу:

        => select pg_xlogfile_name(:'start_lsn');
             pg_xlogfile_name     
        --------------------------
         000000010000000000000002
        (1 row)
        

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

        => \setenv START_SEG :start_seg

.......................................................................

        => truncate table t;
        TRUNCATE TABLE

        => copy t from stdin;
        => 1
        => 2
        => \.
        COPY 2

        => insert into t values (3);
        INSERT 0 1

        => commit;
        COMMIT

.......................................................................

Теперь позиция в журнале:

        => select pg_current_xlog_location();
         pg_current_xlog_location 
        --------------------------
         0/2ED8548
        (1 row)
        

        => select pg_current_xlog_location() as end_lsn \gset

        => \setenv END_LSN :end_lsn

.......................................................................

Размер журнальных записей, соответствующих нашей транзакции:

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

Безусловно, в журнал попадает информация обо всех действиях во всем кластере,
но в данном случае мы рассчитываем на то, что в системе ничего не происходит.

.......................................................................

Теперь воспользуемся утилитой pg_xlogdump, чтобы посмотреть содержимое журнала.
Утилита может работать как с диапазоном lsn, так и выбрать записи для транзакции.

        => \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN -x $TXID $START_SEG
        rmgr: XLOG        len (rec/tot):      4/    30, tx:       3763, lsn: 0/02ED7FA8, prev 0/02ED7F40, desc: NEXTOID 24780
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3763, lsn: 0/02ED7FC8, prev 0/02ED7FA8, desc: CREATE base/16581/16588
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3763, lsn: 0/02ED7FF4, prev 0/02ED7FC8, desc: UPDATE off 9 xmax 3763 ; new off 10 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3763, lsn: 0/02ED8084, prev 0/02ED7FF4, desc: INSERT_LEAF off 315, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3763, lsn: 0/02ED80C0, prev 0/02ED8084, desc: INSERT_LEAF off 42, blkref #0: rel 1663/16581/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3763, lsn: 0/02ED8100, prev 0/02ED80C0, desc: INSERT_LEAF off 283, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3763, lsn: 0/02ED8140, prev 0/02ED8100, desc: CREATE base/16581/16589
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3763, lsn: 0/02ED816C, prev 0/02ED8140, desc: UPDATE off 7 xmax 3763 ; new off 11 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3763, lsn: 0/02ED81E8, prev 0/02ED816C, desc: INSERT_LEAF off 317, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:       3763, lsn: 0/02ED8224, prev 0/02ED81E8, desc: INSERT_LEAF off 101, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3763, lsn: 0/02ED8270, prev 0/02ED8224, desc: INSERT_LEAF off 284, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3763, lsn: 0/02ED82B0, prev 0/02ED8270, desc: CREATE base/16581/16590
        rmgr: Heap        len (rec/tot):     14/    87, tx:       3763, lsn: 0/02ED82DC, prev 0/02ED82B0, desc: UPDATE off 8 xmax 3763 ; new off 12 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3763, lsn: 0/02ED8334, prev 0/02ED82DC, desc: INSERT_LEAF off 319, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:       3763, lsn: 0/02ED8370, prev 0/02ED8334, desc: INSERT_LEAF off 103, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3763, lsn: 0/02ED83C4, prev 0/02ED8370, desc: INSERT_LEAF off 285, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Heap        len (rec/tot):      2/   184, tx:       3763, lsn: 0/02ED8404, prev 0/02ED83C4, desc: INPLACE off 12, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Heap        len (rec/tot):      3/    60, tx:       3763, lsn: 0/02ED84BC, prev 0/02ED8404, desc: INSERT off 3, blkref #0: rel 1663/16581/16588 blk 0
        rmgr: Transaction len (rec/tot):     52/    78, tx:       3763, lsn: 0/02ED84F8, prev 0/02ED84BC, desc: COMMIT 2016-07-02 16:26:50.582492 MSK; rels: base/16581/16587 base/16581/16585 base/16581/16582

Сначала происходит работа со словарем данных: создается новый файл
при команде truncate, изменяются данные в pg_class и других таблицах,
обновляются индексы на системных таблицах.
Вставка строк 1 и 2 не попадает в журнал. Это оптимизация: сохранность
данных обеспечивается их записью на диск.
Вставка строки отражена в журнале, это предпоследняя строка (INSERT).
Далее идет запись о фиксации изменений.

.......................................................................

ARCHIVE
~~~~~~~

Теперь посмотрим на ту же транзакцию при уровне журнала archive.

        => alter system set wal_level = archive;
        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".

.......................................................................

Начинаем транзакцию.

        => begin;
        BEGIN

        => select txid_current() as txid \gset

        => \setenv TXID :txid

        => 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

.......................................................................

        => truncate table t;
        TRUNCATE TABLE

        => copy t from stdin;
        => 1
        => 2
        => \.
        COPY 2

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

        => commit;
        COMMIT

.......................................................................

        => select pg_current_xlog_location();
         pg_current_xlog_location 
        --------------------------
         0/2ED8C40
        (1 row)
        

        => select pg_current_xlog_location() as end_lsn \gset

        => \setenv END_LSN :end_lsn

Размер журнальных записей:

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

.......................................................................

Сам журнал:

        => \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN -x $TXID $START_SEG
        rmgr: XLOG        len (rec/tot):      4/    30, tx:       3764, lsn: 0/02ED85E4, prev 0/02ED85B0, desc: NEXTOID 24783
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3764, lsn: 0/02ED8604, prev 0/02ED85E4, desc: CREATE base/16581/16591
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3764, lsn: 0/02ED8630, prev 0/02ED8604, desc: UPDATE off 10 xmax 3764 ; new off 13 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3764, lsn: 0/02ED86AC, prev 0/02ED8630, desc: INSERT_LEAF off 315, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3764, lsn: 0/02ED86E8, prev 0/02ED86AC, desc: INSERT_LEAF off 42, blkref #0: rel 1663/16581/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3764, lsn: 0/02ED8728, prev 0/02ED86E8, desc: INSERT_LEAF off 286, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3764, lsn: 0/02ED8768, prev 0/02ED8728, desc: CREATE base/16581/16592
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3764, lsn: 0/02ED8794, prev 0/02ED8768, desc: UPDATE off 11 xmax 3764 ; new off 14 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3764, lsn: 0/02ED8810, prev 0/02ED8794, desc: INSERT_LEAF off 318, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:       3764, lsn: 0/02ED884C, prev 0/02ED8810, desc: INSERT_LEAF off 101, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3764, lsn: 0/02ED8898, prev 0/02ED884C, desc: INSERT_LEAF off 287, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3764, lsn: 0/02ED88D8, prev 0/02ED8898, desc: CREATE base/16581/16593
        rmgr: Heap        len (rec/tot):     14/    87, tx:       3764, lsn: 0/02ED8904, prev 0/02ED88D8, desc: UPDATE off 12 xmax 3764 ; new off 15 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3764, lsn: 0/02ED895C, prev 0/02ED8904, desc: INSERT_LEAF off 321, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:       3764, lsn: 0/02ED8998, prev 0/02ED895C, desc: INSERT_LEAF off 104, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3764, lsn: 0/02ED89EC, prev 0/02ED8998, desc: INSERT_LEAF off 288, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: XLOG        len (rec/tot):      0/   113, tx:       3764, lsn: 0/02ED8A2C, prev 0/02ED89EC, desc: FPI , blkref #0: rel 1663/16581/16593 blk 0 FPW
        rmgr: Heap        len (rec/tot):      2/   184, tx:       3764, lsn: 0/02ED8AA0, prev 0/02ED8A2C, desc: INPLACE off 15, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Heap2       len (rec/tot):      4/    77, tx:       3764, lsn: 0/02ED8B58, prev 0/02ED8AA0, desc: MULTI_INSERT+INIT 2 tuples, blkref #0: rel 1663/16581/16591 blk 0
        rmgr: Heap        len (rec/tot):     14/    70, tx:       3764, lsn: 0/02ED8BA8, prev 0/02ED8B58, desc: HOT_UPDATE off 2 xmax 3764 ; new off 3 xmax 0, blkref #0: rel 1663/16581/16591 blk 0
        rmgr: Transaction len (rec/tot):     52/    78, tx:       3764, lsn: 0/02ED8BF0, prev 0/02ED8BA8, desc: COMMIT 2016-07-02 16:26:52.698096 MSK; rels: base/16581/16590 base/16581/16589 base/16581/16588

Здесь появляется запись MULTI_INSERT+INIT, соответствующая команде
copy, вставившей две строки. Эти данные необходимы для того, чтобы
повторить команду при восстановлении из архива.

.......................................................................

HOT_STANDBY
~~~~~~~~~~~

Та же транзакция при уровне журнала hot_standby.

        => alter system set wal_level = hot_standby;
        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".

.......................................................................

Начинаем транзакцию.

        => begin;
        BEGIN

        => select txid_current() as txid \gset

        => \setenv TXID :txid

        => 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

.......................................................................

        => truncate table t;
        TRUNCATE TABLE

        => copy t from stdin;
        => 1
        => 2
        => \.
        COPY 2

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

        => commit;
        COMMIT

.......................................................................

        => select pg_current_xlog_location();
         pg_current_xlog_location 
        --------------------------
         0/2ED949C
        (1 row)
        

        => select pg_current_xlog_location() as end_lsn \gset

        => \setenv END_LSN :end_lsn

Размер журнальных записей:

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

.......................................................................

Сам журнал:

        => \! pg_xlogdump -p $PGDATA/pg_xlog -s $START_LSN -e $END_LSN -x $TXID $START_SEG
        rmgr: Standby     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED8CDC, prev 0/02ED8CA8, desc: LOCK xid 3765 db 16581 rel 16582 
        rmgr: XLOG        len (rec/tot):      4/    30, tx:       3765, lsn: 0/02ED8D08, prev 0/02ED8CDC, desc: NEXTOID 24786
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED8D28, prev 0/02ED8D08, desc: CREATE base/16581/16594
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3765, lsn: 0/02ED8D54, prev 0/02ED8D28, desc: UPDATE off 13 xmax 3765 ; new off 16 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3765, lsn: 0/02ED8DD0, prev 0/02ED8D54, desc: INSERT_LEAF off 315, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3765, lsn: 0/02ED8E0C, prev 0/02ED8DD0, desc: INSERT_LEAF off 42, blkref #0: rel 1663/16581/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3765, lsn: 0/02ED8E4C, prev 0/02ED8E0C, desc: INSERT_LEAF off 289, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Standby     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED8E8C, prev 0/02ED8E4C, desc: LOCK xid 3765 db 16581 rel 16585 
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED8EB8, prev 0/02ED8E8C, desc: CREATE base/16581/16595
        rmgr: Heap        len (rec/tot):     14/   123, tx:       3765, lsn: 0/02ED8EE4, prev 0/02ED8EB8, desc: UPDATE off 14 xmax 3765 ; new off 17 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3765, lsn: 0/02ED8F60, prev 0/02ED8EE4, desc: INSERT_LEAF off 319, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:       3765, lsn: 0/02ED8F9C, prev 0/02ED8F60, desc: INSERT_LEAF off 101, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3765, lsn: 0/02ED8FE8, prev 0/02ED8F9C, desc: INSERT_LEAF off 290, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: Standby     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED9028, prev 0/02ED8FE8, desc: LOCK xid 3765 db 16581 rel 16587 
        rmgr: Storage     len (rec/tot):     16/    42, tx:       3765, lsn: 0/02ED9054, prev 0/02ED9028, desc: CREATE base/16581/16596
        rmgr: Heap        len (rec/tot):     14/    87, tx:       3765, lsn: 0/02ED9080, prev 0/02ED9054, desc: UPDATE off 15 xmax 3765 ; new off 18 xmax 0, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:       3765, lsn: 0/02ED90D8, prev 0/02ED9080, desc: INSERT_LEAF off 323, blkref #0: rel 1663/16581/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:       3765, lsn: 0/02ED9114, prev 0/02ED90D8, desc: INSERT_LEAF off 105, blkref #0: rel 1663/16581/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:       3765, lsn: 0/02ED9168, prev 0/02ED9114, desc: INSERT_LEAF off 291, blkref #0: rel 1663/16581/3455 blk 1
        rmgr: XLOG        len (rec/tot):      0/   113, tx:       3765, lsn: 0/02ED91A8, prev 0/02ED9168, desc: FPI , blkref #0: rel 1663/16581/16596 blk 0 FPW
        rmgr: Heap        len (rec/tot):      2/   184, tx:       3765, lsn: 0/02ED921C, prev 0/02ED91A8, desc: INPLACE off 18, blkref #0: rel 1663/16581/1259 blk 0
        rmgr: Heap2       len (rec/tot):      4/    77, tx:       3765, lsn: 0/02ED92D4, prev 0/02ED921C, desc: MULTI_INSERT+INIT 2 tuples, blkref #0: rel 1663/16581/16594 blk 0
        rmgr: Heap        len (rec/tot):     14/    70, tx:       3765, lsn: 0/02ED9324, prev 0/02ED92D4, desc: HOT_UPDATE off 2 xmax 3765 ; new off 3 xmax 0, blkref #0: rel 1663/16581/16594 blk 0
        rmgr: Transaction len (rec/tot):    272/   301, tx:       3765, lsn: 0/02ED936C, prev 0/02ED9324, desc: COMMIT 2016-07-02 16:26:54.812758 MSK; rels: base/16581/16593 base/16581/16592 base/16581/16591; inval msgs: catcache 45 catcache 44 catcache 45 catcache 44 catcache 45 catcache 44 catcache 45 catcache 44 relcache 16585 relcache 16587 relcache 16587 relcache 16585 relcache 16582

Здесь появляются записи LOCK для отражения эксклюзивных блокировок.
Эта информация нужна для выполнения запросов на горячей резервной копии.

.......................................................................

Конец демонстрации.

.......................................................................

        => 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
