We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11. But we have a big problem. We have a table with more than 800M rows, for better performance we've decided to partition the table. We create a partition table with the same name on the origin database table but we got bellow error on subscription creation :
ERROR: logical replication target relation "public.MY_TABLE" is not a table
At first, do you suggest logical replication for this purpose? could you please give pros and cons on logical replication instead of other approaches.
Is there any solution to replicate a table in partition table or we have to replicate in a normal table and for example we write a job to move data from the table to partitioned table nightly!