Re: Setting up a server with previous day data

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.