Question: is there a way to use this software to backup single Citus shard data?

[lpdevit]

Hi Dimitri, thank you for your great job! I have a single postgresql node with many Citus shards; I need to dump (backup) the data from all tables for a single shard. Then, I will need to restore them in the future without altering other shards' data. Is there a way to do so? Thank you for your help!

[dimitri]

cc @hanefi

[hanefi]

Hi @lpdevit,

First, pgcopydb is meant to be used for migration when both the source and target databases are up. This project does not plan to create backups that can be used in the future.

I would recommend you use pg_dump and pg_restore here.

If you have a multitenant use case, a single shard may contain multiple tenants. I will assume you want to backup and restore data of all the tenants that are in the same shard. If this is not the case, you can consider using tenant isolation feature before moving forward.

You can create a list of all the shards that you want to backup by checking citus metadata catalogs, and supply this list to --table argument of pg_dump. You should connect to the correct worker node that holds those shards for the pg_dump command to work.

If you decide to change the distribution of your Citus tables, the backups may no longer work. Some operations such as changing the number of shards or updating table access methods may end up changing the shard ids, as well as the names of the shards.