CODEWITHSUNDEEP
postgresqlgoogle-cloud-sqlbinary-log
Nipu
Nipu

Reputation: 673

View Binary Logs of GCP Postgres SQL instance

How to view Binary Logs(Transaction Logs) of a GCP Postgres SQL Instance? Can it be viewed in GCP Console?

I tried to use Stackdriver. But it only shows instance usage data.

Thanks

Upvotes: 0

Views: 2876

Answers (2)

Ancoron
Ancoron

Reputation: 2743

The transactions (among a log of other things) are kept in the WAL, which you can look at (partially) using the provided tool pg_waldump, which shows the contents in a more human readable way, e.g.:

rmgr: Heap        len (rec/tot):     54/    54, tx:    1123429, lsn: 28/0BC386A0, prev 28/0BC38580, desc: LOCK off 10: xid 1123429: flags 0 LOCK_ONLY EXCL_LOCK , blkref #0: rel 262581/261781/274824 blk 0
rmgr: Heap        len (rec/tot):    401/   401, tx:    1123429, lsn: 28/0BC386D8, prev 28/0BC386A0, desc: UPDATE off 10 xmax 1123429 ; new off 16 xmax 0, blkref #0: rel 262581/261781/274824 blk 1, blkref #1: rel 262581/261781/274824 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:    1123429, lsn: 28/0BC38870, prev 28/0BC386D8, desc: INSERT_LEAF off 19, blkref #0: rel 262581/261781/274826 blk 1
rmgr: Gin         len (rec/tot):    270/   270, tx:    1123429, lsn: 28/0BC388B0, prev 28/0BC38870, desc: UPDATE_META_PAGE , blkref #0: rel 262581/261781/274827 blk 0, blkref #1: rel 262581/261781/274827 blk 2
rmgr: Transaction len (rec/tot):     34/    34, tx:    1123429, lsn: 28/0BC389C0, prev 28/0BC388B0, desc: COMMIT 2019-03-08 16:31:54.832416 CET
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 28/0BC389E8, prev 28/0BC389C0, desc: RUNNING_XACTS nextXid 1123430 latestCompletedXid 1123429 oldestRunningXid 1123430
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 28/0BC38A20, prev 28/0BC389E8, desc: RUNNING_XACTS nextXid 1123430 latestCompletedXid 1123429 oldestRunningXid 1123430
rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 28/0BC38A58, prev 28/0BC38A20, desc: CHECKPOINT_ONLINE redo 28/BC38A20; tli 1; prev tli 1; fpw true; xid 0:1123430; oid 282777; multi 2; offset 3; oldest xid 561 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1123430; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 28/0BC38AC8, prev 28/0BC38A58, desc: RUNNING_XACTS nextXid 1123430 latestCompletedXid 1123429 oldestRunningXid 1123430
rmgr: Heap2       len (rec/tot):     59/  4643, tx:          0, lsn: 28/0BC38B00, prev 28/0BC38AC8, desc: CLEAN remxid 1123429, blkref #0: rel 262581/261781/274824 blk 0 FPW
rmgr: Heap        len (rec/tot):     65/  6525, tx:    1123430, lsn: 28/0BC39D28, prev 28/0BC38B00, desc: UPDATE off 7 xmax 1123430 ; new off 17 xmax 0, blkref #0: rel 262581/261781/274824 blk 1 FPW

For a better introduction into how to read the WAL contents, have a look it this neat little presentation: WAL: Everything You Want to Know

Upvotes: 2

Pablo Almécija Rodríguez
Pablo Almécija Rodríguez

Reputation: 1520

It is not possible to find binary logs in the GCP Console because Postgres itself does not use binary logs.

Perhaps this post in ServerFault may be useful for you to do some further reading.

Upvotes: 0

Related Questions