SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-09-2008, 07:19 PM
| ||||
| ||||
 Data Warehousing Hi, I've got a postgres database collected logged data. This data I have to keep for at least 3 years. The data in the first instance is being recorded in a postgres cluster. This then needs to be moved a reports database server for analysis. Therefore I'd like a job to dump data on the cluster say every hour and record this is in the reports database. The clustered database could be purged of say data more than a week old. So basically I need a dump/restore that only appends new data to the reports server database. I've googled but can't find anything, can anyone help? Thanks Rob      |
|
04-09-2008, 07:19 PM
| |||
| |||
| Re: Data Warehousing On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > Hi, > > I've got a postgres database collected logged data. This data I have to keep > for at least 3 years. The data in the first instance is being recorded in a > postgres cluster. This then needs to be moved a reports database server for > analysis. Therefore I'd like a job to dump data on the cluster say every > hour and record this is in the reports database. The clustered database > could be purged of say data more than a week old. > > So basically I need a dump/restore that only appends new data to the reports > server database. > > I've googled but can't find anything, can anyone help? You might find an answer in partitioning your data. There's a section in the docs on it. Then you can just dump the old data from the newest couple of partitions if you're partitioning by week, and dump anything older with a simple delete where date < now() - interval '1 week' or something like that. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match |
|
04-09-2008, 07:19 PM
| |||
| |||
| Re: Data Warehousing On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > So basically I need a dump/restore that only appends new > data to the reports server database. I guess that will all depend on whether or not your data has a record of the time it got stuck in the cluster or not ... if there's no concept of a time-stamp attached to the records as they get entered I don't think it can be done. > Thanks > > Rob Cheers, Andrej ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
|
04-09-2008, 07:19 PM
| |||
| |||
| Re: Data Warehousing On 03/09/07, Scott Marlowe <scott.marlowe@gmail.com> wrote: > > On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > > Hi, > > > > I've got a postgres database collected logged data. This data I have to > keep > > for at least 3 years. The data in the first instance is being recorded > in a > > postgres cluster. This then needs to be moved a reports database server > for > > analysis. Therefore I'd like a job to dump data on the cluster say every > > hour and record this is in the reports database. The clustered database > > could be purged of say data more than a week old. > > > > So basically I need a dump/restore that only appends new data to the > reports > > server database. > > > > I've googled but can't find anything, can anyone help? > > You might find an answer in partitioning your data. There's a section > in the docs on it. Then you can just dump the old data from the > newest couple of partitions if you're partitioning by week, and dump > anything older with a simple delete where date < now() - interval '1 > week' or something like that. We're using hibernate to write to the database. Partitioning looks like it will be too much of a re-architecture. In reply to Andrej we do have a logged_time entity in the required tables. That being the case how does that help me with the tools provided? Might I have to write a custom JDBC application to do the data migration? Rob |
|
04-09-2008, 07:19 PM
| |||
| |||
| Re: Data Warehousing On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > We're using hibernate to write to the database. Partitioning looks like it > will be too much of a re-architecture. In reply to Andrej we do have a > logged_time entity in the required tables. That being the case how does that > help me with the tools provided? > > Might I have to write a custom JDBC application to do the data migration? That would be one option :} If the server is on a Unix/Linux-platform you should be able to achieve the result with a reasonably simple shell-script and cron, I'd say. > Rob Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
|
04-09-2008, 07:19 PM
| |||
| |||
| Re: Data Warehousing Andrej Ricnik-Bay wrote: > On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > > >> We're using hibernate to write to the database. Partitioning looks like it >> will be too much of a re-architecture. In reply to Andrej we do have a >> logged_time entity in the required tables. That being the case how does that >> help me with the tools provided? >> >> Might I have to write a custom JDBC application to do the data migration? >> > That would be one option :} > > If the server is on a Unix/Linux-platform you should be able > to achieve the result with a reasonably simple shell-script > and cron, I'd say. > > I am on a Linux platform but I'm going to need some pointers regarding the cron job. Are you suggesting that I parse the dump file? I assume I would need to switch to using inserts and then parse the dump looking for where I need to start from? ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster |
|
04-09-2008, 07:20 PM
| ||||
| ||||
| Re: Data Warehousing On 9/3/07, Rob Kirkbride <rob.kirkbride@gmail.com> wrote: > I am on a Linux platform but I'm going to need some pointers regarding > the cron job. Are you suggesting that I parse the dump file? I assume I > would need to switch to using inserts and then parse the dump looking > for where I need to start from? The question is: how complex is the data you need to extract? I guess where I was heading was to run a select with the interval Scott described from psql into a file, and then copy-from that into the analysis database. However, if the structure is more complex, if you needed to join tables, the parsing of a dump-file may be an option, even though (always retaining a weeks worth) might make that into quite some overhead. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match |
Linear Mode
