agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Logical replication from PostgreSQL 10.23 to 16.3, last two big tables (getting synced) just keep growing
Date: Wed, 12 Jun 2024 20:14:47 +0300
Message-ID: <12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com> (raw)
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)
view thread (5+ messages) latest in thread
Message-ID: <12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com>
Permalink: ../12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com/
Also on: postgresql.org/message-id/12ccf551-b3a0-47e5-ada5-a997a21e396c@cloud.gatewaynet.com
reply
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: <12ccf551-b3a0-47e5-ada5-a997a21e396c@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox