Re: Recommendations for partitioning?

On Fri, Dec 20, 2013 at 7:59 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Dave Johansen escribió:
>> On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:
>
>> > That's pretty much it. What I did was to create the new month table
>> > and day tables, alter my triggers to reflect this, then move the data
>> > with insert into / select from query for each old day partition. Then
>> > once their data is moved you can just drop them. Since you changed the
>> > triggers first those tables are no long taking input so it's usually
>> > safe to drop them now.
>>
>> It would be nice if there was just a "move command", but that seems like
>> the type of model that we want and we'll probably move to that.
>
> Eh. Why can't you just do something like
>
> WITH moved AS (
> DELETE FROM src WHERE ..
> RETURNING *
> ) INSERT INTO dst SELECT * FROM moved;

Avero, I think it could be cheaper to do this like it is shown below, correct?

psql dbname -c 'copy src to stdout' | \
psql dbname -c 'copy dst from stdin; truncate src;'

Dave, in case if you need to archive old partitions to compressed
files out of your database you can use this tool [1]. Consult with the
configuration example [2], look at the ARCHIVE_* parameters.

[1] https://github.com/grayhemp/pgcookbook/blob/master/bin/archive_tables.sh
[2] https://github.com/grayhemp/pgcookbook/blob/master/bin/config.sh.example

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com