Thread: 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.
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 <seenutn@gmail.com> 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
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.klaver@aklaver.com
Greetings, * Srinivasa T N (seenutn@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
Attachment
On 7/19/22 02:22, Srinivasa T N wrote:
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.
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.
Angular momentum makes the world go 'round.
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.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
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.