用 heredoc 在 PGSQL single-user mode 跑指令

 這只是一個刷存在感的雜記

很偶爾的時候，非得把 PGSQL 關掉，用 single-user mode 打開（請參考手冊）。

然後就想到，在這個狀態底下或許也可以用 pipe 吃外面指令～

先用 SELECT 1 試試看：

[enterprisedb@edb15 ~]$ /usr/edb/as15/bin/edb-postgres --single -D ~/as15/data/ edb << EOF
> select 1;
> EOF

PostgreSQL stand-alone backend 15.2.0
backend>         1: ?column?    (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ?column? = "1"      (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
backend> 2023-09-27 03:24:34 UTC LOG:  checkpoint starting: shutdown immediate
2023-09-27 03:24:34 UTC LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.022 s, total=0.114 s; sync files=2, longest=0.014 s, average=0.011 s; distance=0 kB, estimate=0 kB
[enterprisedb@edb15 ~]$

跑系統表查詢看看

[enterprisedb@edb15 ~]$ /usr/edb/as15/bin/edb-postgres --single -D ~/as15/data/ edb << EOF
select * from pg_stat_activity;
EOF

PostgreSQL stand-alone backend 15.2.0
backend>         1: datid       (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
         3: pid (typeid = 23, len = 4, typmod = -1, byval = t)
         4: leader_pid  (typeid = 23, len = 4, typmod = -1, byval = t)
         5: usesysid    (typeid = 26, len = 4, typmod = -1, byval = t)
         6: usename     (typeid = 19, len = 64, typmod = -1, byval = f)
         7: application_name    (typeid = 25, len = -1, typmod = -1, byval = f)
         8: client_addr (typeid = 869, len = -1, typmod = -1, byval = f)
         9: client_hostname     (typeid = 25, len = -1, typmod = -1, byval = f)
        10: client_port (typeid = 23, len = 4, typmod = -1, byval = t)
        11: backend_start       (typeid = 1184, len = 8, typmod = -1, byval = t)
        12: xact_start  (typeid = 1184, len = 8, typmod = -1, byval = t)
        13: query_start (typeid = 1184, len = 8, typmod = -1, byval = t)
        14: state_change        (typeid = 1184, len = 8, typmod = -1, byval = t)
        15: wait_event_type     (typeid = 25, len = -1, typmod = -1, byval = f)
        16: wait_event  (typeid = 25, len = -1, typmod = -1, byval = f)
        17: state       (typeid = 25, len = -1, typmod = -1, byval = f)
        18: backend_xid (typeid = 28, len = 4, typmod = -1, byval = t)
        19: backend_xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
        20: query_id    (typeid = 20, len = 8, typmod = -1, byval = t)
        21: query       (typeid = 25, len = -1, typmod = -1, byval = f)
        22: backend_type        (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
         1: datid = "15313"     (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname = "edb"     (typeid = 19, len = 64, typmod = -1, byval = f)
         3: pid = "836" (typeid = 23, len = 4, typmod = -1, byval = t)
         7: application_name = ""       (typeid = 25, len = -1, typmod = -1, byval = f)
        11: backend_start = "27-SEP-23 03:24:52.737088 +00:00"  (typeid = 1184, len = 8, typmod = -1, byval = t)
        12: xact_start = "27-SEP-23 03:24:52.840326 +00:00"     (typeid = 1184, len = 8, typmod = -1, byval = t)
        13: query_start = "27-SEP-23 03:24:52.840326 +00:00"    (typeid = 1184, len = 8, typmod = -1, byval = t)
        14: state_change = "27-SEP-23 03:24:52.840328 +00:00"   (typeid = 1184, len = 8, typmod = -1, byval = t)
        17: state = "active"    (typeid = 25, len = -1, typmod = -1, byval = f)
        19: backend_xmin = "881"        (typeid = 28, len = 4, typmod = -1, byval = t)
        21: query = "select * from pg_stat_activity;
"       (typeid = 25, len = -1, typmod = -1, byval = f)
        22: backend_type = "not initialized"    (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
backend> 2023-09-27 03:24:53 UTC LOG:  checkpoint starting: shutdown immediate
2023-09-27 03:24:53 UTC LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.021 s, total=0.114 s; sync files=2, longest=0.013 s, average=0.011 s; distance=0 kB, estimate=0 kB
[enterprisedb@edb15 ~]$

試試看查系統表

[enterprisedb@edb15 ~]$ /usr/edb/as15/bin/edb-postgres --single -D ~/as15/data/ edb << EOF
begin; select * from pg_stat_activity; rollback;
EOF


PostgreSQL stand-alone backend 15.2.0
backend>         1: datid       (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
         3: pid (typeid = 23, len = 4, typmod = -1, byval = t)
         4: leader_pid  (typeid = 23, len = 4, typmod = -1, byval = t)
         5: usesysid    (typeid = 26, len = 4, typmod = -1, byval = t)
         6: usename     (typeid = 19, len = 64, typmod = -1, byval = f)
         7: application_name    (typeid = 25, len = -1, typmod = -1, byval = f)
         8: client_addr (typeid = 869, len = -1, typmod = -1, byval = f)
         9: client_hostname     (typeid = 25, len = -1, typmod = -1, byval = f)
        10: client_port (typeid = 23, len = 4, typmod = -1, byval = t)
        11: backend_start       (typeid = 1184, len = 8, typmod = -1, byval = t)
        12: xact_start  (typeid = 1184, len = 8, typmod = -1, byval = t)
        13: query_start (typeid = 1184, len = 8, typmod = -1, byval = t)
        14: state_change        (typeid = 1184, len = 8, typmod = -1, byval = t)
        15: wait_event_type     (typeid = 25, len = -1, typmod = -1, byval = f)
        16: wait_event  (typeid = 25, len = -1, typmod = -1, byval = f)
        17: state       (typeid = 25, len = -1, typmod = -1, byval = f)
        18: backend_xid (typeid = 28, len = 4, typmod = -1, byval = t)
        19: backend_xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
        20: query_id    (typeid = 20, len = 8, typmod = -1, byval = t)
        21: query       (typeid = 25, len = -1, typmod = -1, byval = f)
        22: backend_type        (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
         1: datid = "15313"     (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname = "edb"     (typeid = 19, len = 64, typmod = -1, byval = f)
         3: pid = "838" (typeid = 23, len = 4, typmod = -1, byval = t)
         7: application_name = ""       (typeid = 25, len = -1, typmod = -1, byval = f)
        11: backend_start = "27-SEP-23 03:27:07.504369 +00:00"  (typeid = 1184, len = 8, typmod = -1, byval = t)
        12: xact_start = "27-SEP-23 03:27:07.601863 +00:00"     (typeid = 1184, len = 8, typmod = -1, byval = t)
        13: query_start = "27-SEP-23 03:27:07.601863 +00:00"    (typeid = 1184, len = 8, typmod = -1, byval = t)
        14: state_change = "27-SEP-23 03:27:07.601865 +00:00"   (typeid = 1184, len = 8, typmod = -1, byval = t)
        17: state = "active"    (typeid = 25, len = -1, typmod = -1, byval = f)
        19: backend_xmin = "881"        (typeid = 28, len = 4, typmod = -1, byval = t)
        21: query = "begin; select * from pg_stat_activity; rollback;
"       (typeid = 25, len = -1, typmod = -1, byval = f)
        22: backend_type = "not initialized"    (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
backend> 2023-09-27 03:27:07 UTC LOG:  checkpoint starting: shutdown immediate
2023-09-27 03:27:07 UTC LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.026 s, total=0.119 s; sync files=2, longest=0.016 s, average=0.013 s; distance=0 kB, estimate=0 kB
[enterprisedb@edb15 ~]$

不過要注意的是，如果是處理 wraparound 狀況，在 single-user mode 底下也要小心節約使用 TxID，不要用上面的手法把剩下的 TxID 吃光。

參考資料

自己去找 shell script 的 heredoc 與 pipe~