On Thu, Nov 25, 2021 at 8:00 PM Marcos Pegoraro <mar...@f10.com.br> wrote: >> >> Yes, the way you are doing I think it is bound to happen. There is >> some discussion about why this is happening in email [2]. AFAIK, it is >> not documented and if so, I think it will be a good idea to document >> > And my problem remains the same, how to solve it ? All records on > pg_subscription_rel are initialize with srsubstate null. How can I replay > only updates since yesterday. This replication is a auditing database, so I > cannot loose all things happened since that pg_upgrade. [1] points me how to > upgrade but if I did the wrong way, how to solve that ? >
AFAIU the main problem in your case is that you didn't block the write traffic on the publisher side. Let me try to understand the situation. After the upgrade is finished, there are some new tables with data on the publisher, and did old tables have any additional data? Are the contents in pg_replication_origin intact after the upgrade? So, in short, I think what we need to solve is to get the data from new tables and newly performed writes on old tables. I could think of the following two approaches: Approach-1: 1. Drop subscription and Truncate all tables corresponding to subscription. 2. Create a new subscription for the publication. I think this will be quite neat and there would be no risk of data loss but it could be time-consuming since all the data from previous tables needs to be synced again. Approach-2: Here, I am assuming pg_replication_origin is intact. 1. Block new writes on the publisher-side. 2. Disable the existing subscription (say the name of the subscription is old_sub). 3. Drop the existing all tables publication. 4. Create two new publications, one for old tables (old_pub), and one for new tables (new_pub). 5. Create a new subscription corresponding to new_pub. 6. Remove the existing publication from old_sub and add the old_pub. 7. Enable the subscription. 8. Now, perform a refresh on old_sub. The benefit of Approach-1 is that you don't need to change anything on the publisher-side and it has very few steps. OTOH, in Approach-2, we can save the effort/time to re-sync the initial data for old tables but as there are a lot of things to be taken care there is always a chance of mistake and if that happens you might lose some data. In any case, before following any of these, I suggest creating a dummy setup that mimics your original setup, perform the above steps and ensure everything is fine, then only try the same steps in your main setup. -- With Regards, Amit Kapila.
