From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Logical replication from PostgreSQL 10.23 to 16.3, last two big tables (getting synced) just keep growing
Date: Wed, 12 Jun 2024 21:09:26 +0300
Message-ID: <389ff6ad-cae4-42c0-9925-4b562779b872@cloud.gatewaynet.com> (raw)
In-Reply-To: <12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com>
References: <12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com>
Στις 12/6/24 20:14, ο/η Achilleas Mantzios έγραψε:
>
> 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.
>
The two COPY processes on the publisher are CPU active since about
11:27:00 EEST in the morning.
> --
> Achilleas Mantzios
> IT DEV - HEAD
> IT DEPT
> Dynacom Tankers Mgmt (as agents only)
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: Logical replication from PostgreSQL 10.23 to 16.3, last two big tables (getting synced) just keep growing
In-Reply-To: <389ff6ad-cae4-42c0-9925-4b562779b872@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox