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!

  • Why did partitioning solve your performance problem in the first place? Can you explain? – Philᵀᴹ May 13 '19 at 13:13
  • This is the BI DB. because our queries are base on day, so we portion the table by day. we have a near 3M rows per day, so partitioning by day let us have better performance in – Behrooz May 13 '19 at 13:18
  • 2
    This is a limitation in PG 11, see stackoverflow.com/questions/53554727/… Can't you base your logical replication on the base table? – Colin 't Hart May 13 '19 at 13:25
  • Is the original table not partitioned at all? You say you want a copy "in our company", but where is the original? Is it also in your company, or is it someplace else? – jjanes May 13 '19 at 14:24
  • The original one is connected to an application and It is not partitioned. also, the origin is in somewhere else, in another country. – Behrooz May 14 '19 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.