Re: Setting up a server with previous day data
On 8/24/22 01:42, Peter J. Holzer wrote: On 2022-08-23 19:15:58 -0500, Ron wrote: 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. Isn't that what logical replication basically does? In a more asynchronous manner. :D -- Angular momentum makes the world go 'round.
Re: Setting up a server with previous day data
On 2022-08-23 19:15:58 -0500, Ron wrote: > 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. Isn't that what logical replication basically does? I also think I've seen other tools parsing the WAL stream and doing something useful with the results. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
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. -- Angular momentum makes the world go 'round.
Re: Setting up a server with previous day data
Greetings, * Srinivasa T N (seen...@gmail.com) wrote: >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. You might consider checking out pgbackrest and the incremental backup and delta restore options that it has. Incremental backups will only copy files from the PG server that have changed since the last backup, and the delta restore option will only update the files that are different between the backup and the files that are in place. pgbackrest is also able to parallelize these operations. (this use-case, more-or-less, was part of the original reason pgbackrest was developed, btw) Thanks, Stephen signature.asc Description: PGP signature
Re: Setting up a server with previous day data
On 7/19/22 00: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. Use Postgres FDW: https://www.postgresql.org/docs/current/postgres-fdw.html to set up a link between the two? Regards, Seenu. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Setting up a server with previous day data
You could instead set up High Availability and use your secondary as actual streamed and backed up database. https://www.postgresql.org/docs/current/high-availability.html This way, you get up-to-date data that you can query/generate reports with. On Tue, Jul 19, 2022 at 3:22 PM 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. > > Regards, > Seenu. > -- Abdul Qoyyuum Bin Haji Abdul Kadir HP No: +673 720 8043
Setting up a server with previous day data
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. Regards, Seenu.
