Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided we can clean it up a bit, say to keep only 90 days of data to speed things up a bit and also to save some disk space.
The problem is, doing
delete from ... where clock_column < ... won't reclaim any space on disk, so we needed both to have
VACUUM FULL on it after the delete.
Since we can't afford to stop the DB for such a long time, we decided to go with a partitioning approach and created per-day tables so we'll be able to delete them one by one as time expires. So now we have
table (the initial, huge one), and named after date
Now I'd like to do:
backup of initial table (only the initial table)
2.1 make some clearing in it (only in it) or
2.2 maybe simple truncate it (only it)
But as I did the tests, I see
COPY table TO 'file' creates dump of the whole table (both
table and all of
table160102 etc). The very same fashion, doing
TRUNCATE clears the whole table, not the initial one (tested on test server).
When I work with partitioned tables in
SELECT, I can simple state
ONLY to choose which table to use. But I can not find a way to do that in
So the question is: how can I archive my goals without sacrificing data in the table?
P.S. What I need to archive is when all 'per-day' data are in the 'per-date' tables I simple don't like to keep initial huge table with very old data. In fact the
TRUNCATE only on it would be just fine idea.