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

Посмотрим на содержимое журнала для одних и тех же действий
при разных уровнях, установленных в параметре 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 
        --------------
               557768
        (1 row)
        

        => select txid_current() as txid \gset

        => \setenv TXID :txid

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

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

        => select pg_current_xlog_location();
         pg_current_xlog_location 
        --------------------------
         3/B35D1DF0
        (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     
        --------------------------
         0000001100000003000000B3
        (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 
        --------------------------
         3/B35D2390
        (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:     557768, lsn: 3/B35D1DF0, prev 3/B35D1D88, desc: NEXTOID 135110
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557768, lsn: 3/B35D1E10, prev 3/B35D1DF0, desc: CREATE base/126911/126918
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557768, lsn: 3/B35D1E3C, prev 3/B35D1E10, desc: UPDATE off 9 xmax 557768 ; new off 10 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557768, lsn: 3/B35D1EB8, prev 3/B35D1E3C, desc: INSERT_LEAF off 315, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557768, lsn: 3/B35D1EF4, prev 3/B35D1EB8, desc: INSERT_LEAF off 42, blkref #0: rel 1663/126911/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557768, lsn: 3/B35D1F34, prev 3/B35D1EF4, desc: INSERT_LEAF off 283, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557768, lsn: 3/B35D1F74, prev 3/B35D1F34, desc: CREATE base/126911/126919
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557768, lsn: 3/B35D1FA0, prev 3/B35D1F74, desc: UPDATE off 7 xmax 557768 ; new off 11 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557768, lsn: 3/B35D2030, prev 3/B35D1FA0, desc: INSERT_LEAF off 317, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:     557768, lsn: 3/B35D206C, prev 3/B35D2030, desc: INSERT_LEAF off 101, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557768, lsn: 3/B35D20B8, prev 3/B35D206C, desc: INSERT_LEAF off 284, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557768, lsn: 3/B35D20F8, prev 3/B35D20B8, desc: CREATE base/126911/126920
        rmgr: Heap        len (rec/tot):     14/    87, tx:     557768, lsn: 3/B35D2124, prev 3/B35D20F8, desc: UPDATE off 8 xmax 557768 ; new off 12 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557768, lsn: 3/B35D217C, prev 3/B35D2124, desc: INSERT_LEAF off 319, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:     557768, lsn: 3/B35D21B8, prev 3/B35D217C, desc: INSERT_LEAF off 103, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557768, lsn: 3/B35D220C, prev 3/B35D21B8, desc: INSERT_LEAF off 285, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Heap        len (rec/tot):      2/   184, tx:     557768, lsn: 3/B35D224C, prev 3/B35D220C, desc: INPLACE off 12, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Heap        len (rec/tot):      3/    60, tx:     557768, lsn: 3/B35D2304, prev 3/B35D224C, desc: INSERT off 3, blkref #0: rel 1663/126911/126918 blk 0
        rmgr: Transaction len (rec/tot):     52/    78, tx:     557768, lsn: 3/B35D2340, prev 3/B35D2304, desc: COMMIT 2017-12-14 18:58:54.231567 MSK; rels: base/126911/126917 base/126911/126915 base/126911/126912

Сначала происходит работа со словарем данных: создается новый файл
при команде 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 
        --------------------------
         3/B35D2A88
        (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:     557769, lsn: 3/B35D242C, prev 3/B35D23F8, desc: NEXTOID 135113
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557769, lsn: 3/B35D244C, prev 3/B35D242C, desc: CREATE base/126911/126921
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557769, lsn: 3/B35D2478, prev 3/B35D244C, desc: UPDATE off 10 xmax 557769 ; new off 13 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557769, lsn: 3/B35D24F4, prev 3/B35D2478, desc: INSERT_LEAF off 315, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557769, lsn: 3/B35D2530, prev 3/B35D24F4, desc: INSERT_LEAF off 42, blkref #0: rel 1663/126911/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557769, lsn: 3/B35D2570, prev 3/B35D2530, desc: INSERT_LEAF off 286, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557769, lsn: 3/B35D25B0, prev 3/B35D2570, desc: CREATE base/126911/126922
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557769, lsn: 3/B35D25DC, prev 3/B35D25B0, desc: UPDATE off 11 xmax 557769 ; new off 14 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557769, lsn: 3/B35D2658, prev 3/B35D25DC, desc: INSERT_LEAF off 318, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:     557769, lsn: 3/B35D2694, prev 3/B35D2658, desc: INSERT_LEAF off 101, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557769, lsn: 3/B35D26E0, prev 3/B35D2694, desc: INSERT_LEAF off 287, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557769, lsn: 3/B35D2720, prev 3/B35D26E0, desc: CREATE base/126911/126923
        rmgr: Heap        len (rec/tot):     14/    87, tx:     557769, lsn: 3/B35D274C, prev 3/B35D2720, desc: UPDATE off 12 xmax 557769 ; new off 15 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557769, lsn: 3/B35D27A4, prev 3/B35D274C, desc: INSERT_LEAF off 321, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:     557769, lsn: 3/B35D27E0, prev 3/B35D27A4, desc: INSERT_LEAF off 104, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557769, lsn: 3/B35D2834, prev 3/B35D27E0, desc: INSERT_LEAF off 288, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: XLOG        len (rec/tot):      0/   113, tx:     557769, lsn: 3/B35D2874, prev 3/B35D2834, desc: FPI , blkref #0: rel 1663/126911/126923 blk 0 FPW
        rmgr: Heap        len (rec/tot):      2/   184, tx:     557769, lsn: 3/B35D28E8, prev 3/B35D2874, desc: INPLACE off 15, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Heap2       len (rec/tot):      4/    77, tx:     557769, lsn: 3/B35D29A0, prev 3/B35D28E8, desc: MULTI_INSERT+INIT 2 tuples, blkref #0: rel 1663/126911/126921 blk 0
        rmgr: Heap        len (rec/tot):     14/    70, tx:     557769, lsn: 3/B35D29F0, prev 3/B35D29A0, desc: HOT_UPDATE off 2 xmax 557769 ; new off 3 xmax 0, blkref #0: rel 1663/126911/126921 blk 0
        rmgr: Transaction len (rec/tot):     52/    78, tx:     557769, lsn: 3/B35D2A38, prev 3/B35D29F0, desc: COMMIT 2017-12-14 18:58:56.354692 MSK; rels: base/126911/126920 base/126911/126919 base/126911/126918

Здесь появляется запись 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 
        --------------------------
         3/B35D32E4
        (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:     557770, lsn: 3/B35D2B24, prev 3/B35D2AF0, desc: LOCK xid 557770 db 126911 rel 126912 
        rmgr: XLOG        len (rec/tot):      4/    30, tx:     557770, lsn: 3/B35D2B50, prev 3/B35D2B24, desc: NEXTOID 135116
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557770, lsn: 3/B35D2B70, prev 3/B35D2B50, desc: CREATE base/126911/126924
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557770, lsn: 3/B35D2B9C, prev 3/B35D2B70, desc: UPDATE off 13 xmax 557770 ; new off 16 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557770, lsn: 3/B35D2C18, prev 3/B35D2B9C, desc: INSERT_LEAF off 315, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557770, lsn: 3/B35D2C54, prev 3/B35D2C18, desc: INSERT_LEAF off 42, blkref #0: rel 1663/126911/2663 blk 2
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557770, lsn: 3/B35D2C94, prev 3/B35D2C54, desc: INSERT_LEAF off 289, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Standby     len (rec/tot):     16/    42, tx:     557770, lsn: 3/B35D2CD4, prev 3/B35D2C94, desc: LOCK xid 557770 db 126911 rel 126915 
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557770, lsn: 3/B35D2D00, prev 3/B35D2CD4, desc: CREATE base/126911/126925
        rmgr: Heap        len (rec/tot):     14/   123, tx:     557770, lsn: 3/B35D2D2C, prev 3/B35D2D00, desc: UPDATE off 14 xmax 557770 ; new off 17 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557770, lsn: 3/B35D2DA8, prev 3/B35D2D2C, desc: INSERT_LEAF off 319, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    76, tx:     557770, lsn: 3/B35D2DE4, prev 3/B35D2DA8, desc: INSERT_LEAF off 101, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557770, lsn: 3/B35D2E30, prev 3/B35D2DE4, desc: INSERT_LEAF off 290, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: Standby     len (rec/tot):     16/    42, tx:     557770, lsn: 3/B35D2E70, prev 3/B35D2E30, desc: LOCK xid 557770 db 126911 rel 126917 
        rmgr: Storage     len (rec/tot):     16/    42, tx:     557770, lsn: 3/B35D2E9C, prev 3/B35D2E70, desc: CREATE base/126911/126926
        rmgr: Heap        len (rec/tot):     14/    87, tx:     557770, lsn: 3/B35D2EC8, prev 3/B35D2E9C, desc: UPDATE off 15 xmax 557770 ; new off 18 xmax 0, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Btree       len (rec/tot):      2/    60, tx:     557770, lsn: 3/B35D2F20, prev 3/B35D2EC8, desc: INSERT_LEAF off 323, blkref #0: rel 1663/126911/2662 blk 1
        rmgr: Btree       len (rec/tot):      2/    84, tx:     557770, lsn: 3/B35D2F5C, prev 3/B35D2F20, desc: INSERT_LEAF off 105, blkref #0: rel 1663/126911/2663 blk 4
        rmgr: Btree       len (rec/tot):      2/    64, tx:     557770, lsn: 3/B35D2FB0, prev 3/B35D2F5C, desc: INSERT_LEAF off 291, blkref #0: rel 1663/126911/3455 blk 1
        rmgr: XLOG        len (rec/tot):      0/   113, tx:     557770, lsn: 3/B35D2FF0, prev 3/B35D2FB0, desc: FPI , blkref #0: rel 1663/126911/126926 blk 0 FPW
        rmgr: Heap        len (rec/tot):      2/   184, tx:     557770, lsn: 3/B35D3064, prev 3/B35D2FF0, desc: INPLACE off 18, blkref #0: rel 1663/126911/1259 blk 0
        rmgr: Heap2       len (rec/tot):      4/    77, tx:     557770, lsn: 3/B35D311C, prev 3/B35D3064, desc: MULTI_INSERT+INIT 2 tuples, blkref #0: rel 1663/126911/126924 blk 0
        rmgr: Heap        len (rec/tot):     14/    70, tx:     557770, lsn: 3/B35D316C, prev 3/B35D311C, desc: HOT_UPDATE off 2 xmax 557770 ; new off 3 xmax 0, blkref #0: rel 1663/126911/126924 blk 0
        rmgr: Transaction len (rec/tot):    272/   301, tx:     557770, lsn: 3/B35D31B4, prev 3/B35D316C, desc: COMMIT 2017-12-14 18:58:58.474887 MSK; rels: base/126911/126923 base/126911/126922 base/126911/126921; inval msgs: catcache 45 catcache 44 catcache 45 catcache 44 catcache 45 catcache 44 catcache 45 catcache 44 relcache 126915 relcache 126917 relcache 126917 relcache 126915 relcache 126912

Здесь появляются записи 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