Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHRYn-007Wxi-6o for pgsql-admin@arkaria.postgresql.org; Wed, 12 Jun 2024 17:14:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sHRYl-003G1m-L3 for pgsql-admin@arkaria.postgresql.org; Wed, 12 Jun 2024 17:14:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHRYl-003G1e-5V for pgsql-admin@lists.postgresql.org; Wed, 12 Jun 2024 17:14:56 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHRYi-000zHG-N8 for pgsql-admin@lists.postgresql.org; Wed, 12 Jun 2024 17:14:54 +0000 Content-Type: multipart/alternative; boundary="------------x0MgSF5Nsw6wi0uJWmomFOt0" Message-ID: <12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com> Date: Wed, 12 Jun 2024 20:14:47 +0300 MIME-Version: 1.0 Content-Language: en-US To: pgsql-admin@lists.postgresql.org From: Achilleas Mantzios Subject: Logical replication from PostgreSQL 10.23 to 16.3, last two big tables (getting synced) just keep growing List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------x0MgSF5Nsw6wi0uJWmomFOt0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hello as the subject says, we are trying to test a migration plan from 10.23 to 16.3, using logical replication to new OS, + checksums  + ICU + SQL_ASCII -> UTF-8, (some of which proved to be a challenge).  After solving all of the problems, and repeating the process, now it seems the system got into a weird state, in which all of the ~ 930 tables got synced correctly , except the two largest ones. The DB in total (in its 10.23 version) takes up 4.6 TB. In 16.3 it has already taken 4.8 and keep growing. No ERRORs or ROLLBACKS that would justify the growth (to my limited knowledge). Those two tables keep growing and growing in the 16.3 (subscriber) DB. In 10.23 (publisher) postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where  oid in ('marin erpapers_atts'::regclass,'marinerpapers_atts_tmp'::regclass); pg_total_relation_size | pg_size_pretty |          oid ------------------------+----------------+------------------------          1082956537856 | 1009 GB        | marinerpapers_atts           973440409600 | 907 GB         | marinerpapers_atts_tmp (2 rows) postgres@[local]/dynacom=# In 16.3 (subscriber) postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where  oid in ('marin erpapers_atts'::regclass,'marinerpapers_atts_tmp'::regclass); pg_total_relation_size | pg_size_pretty |          oid ------------------------+----------------+------------------------          1301943779328 | 1213 GB        | marinerpapers_atts          1295188762624 | 1206 GB        | marinerpapers_atts_tmp (2 rows)  The weird thing is that for every single table of the rest of the big ones, the size on the new machine seems smaller : 10.23 : postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where relkind in ('r' ,'p','m') order by 1 DESC LIMIT 10 OFFSET 2; pg_total_relation_size | pg_size_pretty |          oid ------------------------+----------------+-----------------------           634434142208 | 591 GB         | mail_entity           568166334464 | 529 GB         | fb_reports_files           538761527296 | 502 GB         | vmanews           115932397568 | 108 GB         | approval_request_docs           107413241856 | 100 GB         | status            86767763456 | 81 GB          | fb_reports_dets            82120941568 | 76 GB          | items_atts            81043136512 | 75 GB          | items_atts_tmp            59618181120 | 56 GB          | logging            58586382336 | 55 GB          | apm_evidence (10 rows) 16.3 postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where relkind in ('r' ,'p','m') order by 1 DESC LIMIT 10 OFFSET 2; pg_total_relation_size | pg_size_pretty |          oid ------------------------+----------------+-----------------------           542910595072 | 506 GB         | fb_reports_files           515205160960 | 480 GB         | vmanews           491888680960 | 458 GB         | mail_entity           115919945728 | 108 GB         | approval_request_docs            82050457600 | 76 GB          | items_atts            80889028608 | 75 GB          | items_atts_tmp            64453533696 | 60 GB          | fb_reports_dets            57055133696 | 53 GB          | apm_evidence            55286161408 | 51 GB          | logging            52352860160 | 49 GB          | status (10 rows) For what it's worth, I restarted the target (subscriber) 16.3 DB today after compiling for JIT.  I know 10.23 is dead but anyways, any clues or any advice would be great. -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only) --------------x0MgSF5Nsw6wi0uJWmomFOt0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hello

as the subject says, we are trying to test a migration plan from 10.23 to 16.3, using logical replication to new OS, + checksums  + ICU + SQL_ASCII -> UTF-8, (some of which proved to be a challenge).  After solving all of the problems, and repeating the process, now it seems the system got into a weird state, in which all of the ~ 930 tables got synced correctly , except the two largest ones. The DB in total (in its 10.23 version) takes up 4.6 TB. In 16.3 it has already taken 4.8 and keep growing. No ERRORs or ROLLBACKS that would justify the growth (to my limited knowledge).

Those two tables keep growing and growing in the 16.3 (subscriber) DB.

In 10.23 (publisher)

postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where  oid in ('marin
erpapers_atts'::regclass,'marinerpapers_atts_tmp'::regclass);
pg_total_relation_size | pg_size_pretty |          oid            
------------------------+----------------+------------------------
         1082956537856 | 1009 GB        | marinerpapers_atts
          973440409600 | 907 GB         | marinerpapers_atts_tmp
(2 rows)

postgres@[local]/dynacom=#

In 16.3 (subscriber)

postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where  oid in ('marin
erpapers_atts'::regclass,'marinerpapers_atts_tmp'::regclass);
pg_total_relation_size | pg_size_pretty |          oid            
------------------------+----------------+------------------------
         1301943779328 | 1213 GB        | marinerpapers_atts
         1295188762624 | 1206 GB        | marinerpapers_atts_tmp
(2 rows)

 The weird thing is that for every single table of the rest of the big ones, the size on the new machine seems smaller :

10.23 :

postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where relkind in ('r'
,'p','m') order by 1 DESC LIMIT 10 OFFSET 2;  
pg_total_relation_size | pg_size_pretty |          oid           
------------------------+----------------+-----------------------
          634434142208 | 591 GB         | mail_entity
          568166334464 | 529 GB         | fb_reports_files
          538761527296 | 502 GB         | vmanews
          115932397568 | 108 GB         | approval_request_docs
          107413241856 | 100 GB         | status
           86767763456 | 81 GB          | fb_reports_dets
           82120941568 | 76 GB          | items_atts
           81043136512 | 75 GB          | items_atts_tmp
           59618181120 | 56 GB          | logging
           58586382336 | 55 GB          | apm_evidence
(10 rows)

16.3

postgres@[local]/dynacom=# select pg_total_relation_size(oid), pg_size_pretty(pg_total_relation_size(oid)), oid::regclass from pg_class where relkind in ('r'
,'p','m') order by 1 DESC LIMIT 10 OFFSET 2;  
pg_total_relation_size | pg_size_pretty |          oid           
------------------------+----------------+-----------------------
          542910595072 | 506 GB         | fb_reports_files
          515205160960 | 480 GB         | vmanews
          491888680960 | 458 GB         | mail_entity
          115919945728 | 108 GB         | approval_request_docs
           82050457600 | 76 GB          | items_atts
           80889028608 | 75 GB          | items_atts_tmp
           64453533696 | 60 GB          | fb_reports_dets
           57055133696 | 53 GB          | apm_evidence
           55286161408 | 51 GB          | logging
           52352860160 | 49 GB          | status
(10 rows)

For what it's worth, I restarted the target (subscriber) 16.3 DB today after compiling for JIT.  I know 10.23 is dead but anyways,  any clues or any advice would be great. 

-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)
--------------x0MgSF5Nsw6wi0uJWmomFOt0--