How to move partitions to other pg server?

Question

I have a table with 1b rows and it continues growing. Currently, I use pg_partman for partitioning by month. There are a lot of select queries only for current or previous months. Is it possible to move "old" partitions to another server? So ideally I want to have 1 server (powerful with fast disks) with recent partitions only and a second server (cheap) with other partitions. Should I use postgres_fdw extension to link partitions? What is good practice to achieve this kind of scheme?

Answer 1

If it's just about the (expensive) fast disk, and you are at liberty to connect multiple disks to your system, then add a big, cheap disk and create a TABLESPACE there. Then relocate old partitions with a simple ALTER TABLE statement:

ALTER TABLE my_partition SET TABLESPACE slow_tablespace;

Indexes on the table, if any, have to be moved separately.

Objects are moved physically. That acquires an ACCESS EXCLUSIVE lock for the duration. If that's not an option (and you know there are no concurrent writes on the partition!), you can alternatively copy the table in the background, and recreate indexes - if you still want them for old partitions? (You might set the index FILLFACTOR to 100 while recreating.) Then ALTER TABLE DETACH PARTITION / ALTER TABLE ATTACH PARTITION. Read the fine print in the manual to streamline this process. Slower, but without exclusive locks on the live partition. And all bloat is removed from table and indexes when rewritten this way.

Still a single server, the partitioned table remains complete, only partitions of your choice get the fast disk.

It's possible to attach foreign tables as partitions. But that's a lot more fragile than adding a TABLESPACE.