How to ingest RDS PostgreSQL using S3 as log source

[owenCCY]

We do not support RDS PostgreSQL log ingestion officially in CLO, but customer wants to create the ingestion pipeline from S3 bucket.

[owenCCY]

Step1. Create log config for RDS PostgreSQL in CLO console

1. Create a new Log Config in CLO console
2. Choose Multi-line Text as the Log Type
3. Choose Custom as the Parser
4. Paste the following content into the config:

First Line Regular Expression:

(?<time>\d+-\d+-\d+\s\d+:\d+:.+)\s(?<timezone>\w+):(?<ip>.+\))?:(?<host>.+]):(?<type>\w+):\s(?<log>.+)

Example PostgreSQL logs:

2024-01-15 03:18:29 UTC:10.141.17.91(53898):crmuser@crm:[29427]:LOG:  execute <unnamed>: UPDATE public.wechat_user_tags
	SET interaction_tag = '{"last_time":"2024-01-15 11:18:28","count":6}', qrcode_tag = '{"count":2,"detail":{"xxxxxxx":2}}', update_date = to_char(current_timestamp, 'yyyyMMdd'), update_time = to_char(current_timestamp, 'HH24MISS'), update_user_id = 99
	WHERE openid = $1
	AND publicaccountid = $2

Time Format:

%Y-%m-%d %H:%M:%S

Example:

[owenCCY]

Step 2. Create an application pipeline