[9.2] Non-guarantee recovery table using UNLOGGED TABLE

/****************************************************************************************************************
-- Title : [9.2] Non-guarantee recovery table using UNLOGGED TABLE
-- Reference : postgresql.org postgresdba.com
-- Key word : postgresql unlogged table nologging nolog 로깅 노로깅
****************************************************************************************************************/

-- Unlogged TABLE is not logging, that means it's not guarantee recovery table.

-- initialization

DROP TABLE ttt;

-- CREATE unlogged table

CREATE unlogged TABLE ttt

( a int

, b varchar(10)

);

-- INSERT data

WITH recursive tt(n)

as

( 

  VALUES (1)

  union all

  SELECT n + 1 FROM tt

  WHERE n < 1000

)

INSERT INTO ttt

SELECT n ,'aaaaaaaaaa' FROM tt;

SELECT * FROM ttt;

-- kill all connected sessions

=# SELECT pg_terminate_backend(pid) 

   FROM pg_stat_activity;

-- restart postgresql

$ pg_ctl restart

-- confirm data IN ttt

SELECT * FROM ttt; /* exist!! */

-- INSERT data more

INSERT INTO ttt

SELECT generate_series, 'bbbbbbbbbb'

FROM generate_series(1001,1100, 1);

SELECT * FROM ttt;

-- shutdown postgresql WITH immediate

$ pg_ctl -m immediate stop

-- start postgresql

$ pg_ctl start

-- confirm data IN ttt

SELECT * FROM ttt; /* not exist!! */