> -----Original Message----- > From: Mario Splivalo [mailto:mario.spliv...@megafon.hr] > Sent: Wednesday, March 31, 2010 10:20 AM > To: pgsql-admin@postgresql.org > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
