From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Setting up a server with previous day data |
Date: | 2022-08-24 00:15:58 |
Message-ID: | f44409a6-13f3-61ec-fb5a-df2bf6f65166@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/19/22 02:22, Srinivasa T N wrote:
> Hi All,
> I have a primary postgresql 12 server which is being continuously used
> for transaction processing. For reporting purposes, I want to set up a
> secondary server which has got previous day data. Everyday night, I want
> the data from primary to be shifted to secondary. I can achieve this
> manually using pg_basebackup on primary and pg_restore on secondary. Is
> there any other automated efficient way to achieve the same? Any relevant
> docs would be helpful.
We populated a "reporting" database from the OLTP database (not PostgreSQL)
by creating "/X/_log1" and "/X/_log2" tables which had the same columns as
relevant "main" tables, plus an ACTION_CODE with values 'I", "U" or "D", and
a datetime field which defaults to CURRENT_TIMESTAMP.
ON INSERT, ON UPDATE and ON DELETE triggers were added to the "main" tables
which inserted into X_log2 on even days, and into X_log1 on odd days.
Soon after midnight, a cron job dumped "yesterday's" _log table, loaded it
into the reporting table, and then truncated the _log table.
That was before someone developed a utility to convert the roll-forward logs
into INSERT, UPDATE and DELETE statements.
Such a utility for PostgreSQL that would convert yesterday's WAL files into
SQL would /really/ solve your problem.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-08-24 00:21:29 | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |
Previous Message | Bryn Llewellyn | 2022-08-24 00:12:20 | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |