How can I find "Source change position" of PostgreSQL

[khchoy]

Hi,

PostgreQSL source end point  dose not support timestamp option.

But I don't know How can I find start point from postgreSQL and what value insert into SCN field ?

Could help this ?

Regards,

Kwangho

[john_wang · Accepted solution]

Hello KwangHo,

Happy New Year!

You may start task from given hex LSN value, the format can be like:

0/06CC8908
or
00000000/06CC8908

the current LSN can be get by

select pg_current_wal_lsn();

Let me know if you need any additional information.

Regards,

John.

[khchoy]

Thanks John,

The recovery point may be earlier due to the occurrence of a failure, etc. Is there any way to know the LSN of the past point in time? Should PostgreSQL be archived for point-in-time recovery?

[john_wang]

Hello KwangHo,

I'm not sure I got your exact meaning. however you may get LSN from task log file. or do you want to get the walfile information? like:

sourcedb=# select * from pg_ls_waldir() order by modification desc limit 5;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000006 | 16777216 | 2021-10-31 10:51:59+08
 000000010000000000000007 | 16777216 | 2021-09-30 20:16:01+08
 000000010000000000000005 | 16777216 | 2021-09-30 20:13:18+08
 000000010000000000000004 | 16777216 | 2021-08-21 21:51:06+08
 000000010000000000000003 | 16777216 | 2021-08-21 21:48:40+08
(5 rows)

just like you wan to query the given SCN was recorded in which archived redo log file in Oracle world.

Regards,

John.

[khchoy]

Thanks John.

Other question , Is it possible to use archives in wal directory in postgresql environment just like using archive log in Oracle environment?

Regards,

KwzngHo

[john_wang]

Hello KwangHo,

No, I do not think so.

Regards,

John.

[khchoy]

Hello John,

If a capture error occurs, I wonder how far in the past Qlik Replicate can recover from. In the case of Oracle or Mysql, Oracle is available from the oldest past time of the existing archive file, and in the case of MySQl, it is possible from the oldest past time of the existing binlog. Is it only usable?

Regards,

KwangHo

[john_wang]

Hello KwangHo,

Well, for Oracle/MySQL it's possible to parse the transaction logs to get changes if they are still accessible however it's different for PostgreSQL: it's possible only from the point where the slot was created. it's PostgreSQL design rather than Replicate or other 3rd party tools limitation.

Regards,

John.