Re: Live sort-of-'warehousing' database how-to?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




> -----Original Message-----
> From: Mario Splivalo [mailto:mario.splivalo@xxxxxxxxxx] 
> Sent: Wednesday, March 31, 2010 10:20 AM
> To: pgsql-admin@xxxxxxxxxxxxxx
> Subject: Live sort-of-'warehousing' database how-to?
> 
> Suppose I have 'stupid' database with just one tables, like this:
> 
> CREATE TABLE messages (
> 	message_id uuid NOT NULL PRIMARY KEY,
> 	message_time_created timestamp with time zone NOT NULL,
> 	message_phone_number character varying NOT NULL,
> 	message_state type_some_state_enum NOT NULL,
> 	message_value numeric(10,4)
> )
> 
> Now, let's say that I end up with around 1.000.000 records 
> each week. I actually need just last week or two worth of 
> data for the whole system to function normaly.
> 
> But, sometimes I do need to peek into 'messages' for some old 
> message, let's say a year old.
> 
> So I would like to keep 'running' messages on the 'main' 
> server, and keep there a month worth of data. On the 
> 'auxiliary' server I'd like to keep all the data. (Messages 
> on the 'auxiliary' server are in the final state, no change 
> to that data will ever be made).
> 
> Is there a solution to achieve something like that. It is 
> fairly easy to implement something like
> 
> INSERT INTO auxilary.database.messages
> SELECT * FROM main.database.messagaes
> 	WHERE message_id NOT IN (SELECT message_id FROM
> auxilary.database.messages....)
> 
> using python/dblink or something like that. But, is there 
> already a solution that would do something like that?
> 
> Or is there a better way to achieve desired functionality?
> 
> 	Mike
> 

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.

Igor Neyman

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux