On Fri, Dec 20, 2013 at 7:59 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Dave Johansen escribió: >> On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe >> <scott.marl...@gmail.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...@gmail.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
