Dealing with partitioned tables in Postgresql

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

But as I did the tests, I see COPY table TO 'file' creates dump of the whole table (both table and all of table160101, 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 COPY or TRUNCATE.

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.

Let's quickly check the COPY documentation:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' ...}
    ...

Your friend will be the query part. It means that you cannot only copy from a table, but also an arbitrary query. And example can be:

COPY (SELECT * FROM ONLY your_table) TO '/path/to/dump.sql';

Notes:

• you have to specify an absolute path with COPY

• it is usually handier to use \copy from psql as it copies the dump to the client machine, not the server. In your case this may be irrelevant.

• in practice, keeping daily tables is usually too fine-grained. If all your queries can use constraint exclusion, then it might be all good, otherwise you might notice some planning overhead.

StackExchange Database Administrators Q#126857, answer score: 5