Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3E93C6325BD for ; Thu, 1 Apr 2010 04:54:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 49399-03 for ; Thu, 1 Apr 2010 07:54:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from megafon.hr (megafon.hr [213.202.104.5]) by mail.postgresql.org (Postfix) with ESMTP id 9D2A9633DDE for ; Thu, 1 Apr 2010 04:54:44 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by megafon.hr (Postfix) with ESMTP id F233225006C; Thu, 1 Apr 2010 09:54:42 +0200 (CEST) X-Virus-Scanned: ClamAV at megafon.hr Received: from megafon.hr ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id lvpSWwqHFgF3; Thu, 1 Apr 2010 09:54:37 +0200 (CEST) Received: from [192.168.1.94] (localhost.localdomain [127.0.0.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: mike) by megafon.hr (Postfix) with ESMTPSA id C413A25006A; Thu, 1 Apr 2010 09:54:37 +0200 (CEST) Message-ID: <4BB4513D.7000705@megafon.hr> Date: Thu, 01 Apr 2010 09:54:37 +0200 From: Mario Splivalo User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Igor Neyman CC: pgsql-admin@postgresql.org Subject: Re: Live sort-of-'warehousing' database how-to? References: <4BB35A02.5060100@megafon.hr> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=0.000, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201004/1 X-Sequence-Number: 34095 Igor Neyman wrote: > > Partition your MESSAGES table by week or month (read on table > partitioning in PG docs). > > Pg_dump "old" purtitions from "current" server, when they are not needed > any more. > Move backups of dumped partitions to your "auxilary" server, and > pg_restore them there. Hm. I never actually wanted to use partitioning (and I always thought my databases are small in size and don't need partitioning) because of what seems to be awful overhead of maintaining insert rules or insert triggers. But your idea sound plausible for exactly what i need. The only problem is that on the "auxiliary" server I wouldn't have the last month/week or so, and I'd love to so every day or maybe every hour. Will see, thank you! :) Mike