Hello everyone.
I am working on a database migration from old post PostgreSQL 10 hosted in a
Stolon in Kubernetes (yes I know, it's looking for troubles) to a managed AWS
RDS Postgresql 15 database.
The only potential solution I found to do this migration is to use the logical
replication, as we can't have much downtime and the database is quite big
(around 2TB).
I made many tests on multiple non production environments and had quite strange
results, on our staging platform for example I could not, so far, achieve a
complete migration, I had many issues of EOF during wal streaming, I then
tweaked the wal_sender_timeout and wal_received timeout when I tested the
migration on the production database and it looks like this part of the issue
is solved. (I will have to retest it on staging, I tested on production as
maybe only staging had this issue)
2 others environment were able to do the migration correctly (same schema,
without the WAL timeout tweaks), one being around 800GB, like staging and the
other much smaller, around 80GB).
Apart from the timeout issue that looks solved so far, I have a problem with
our biggest table (which is a link table for a many 2 many relation), it's the
one that were usually failing on staging and now also failing on the production
test migration.
There you have the information about the table in question :
back=> \d+ diagnostics_episodes
Table "public.diagnostics_episodes"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
---------------+---------+-----------+----------+---------+---------+--------------+-------------
diagnostic_id | bigint | | | | plain |
|
episode_id | bigint | | | | plain |
|
is_deleted | boolean | | not null | false | plain |
|
Indexes:
"diagnostics_episodes_unique" UNIQUE, btree (diagnostic_id, episode_id)
"index_diagnostics_episodes_on_episode_id" btree (episode_id)
Check constraints:
"diagnostics_episodes_diagnostic_id_null" CHECK (diagnostic_id IS NOT NULL)
"diagnostics_episodes_episode_id_null" CHECK (episode_id IS NOT NULL)
Foreign-key constraints:
"fk_rails_11c88d28cf" FOREIGN KEY (episode_id) REFERENCES episodes(id) ON
UPDATE CASCADE ON DELETE CASCADE
"fk_rails_5857fe47a7" FOREIGN KEY (diagnostic_id) REFERENCES
diagnostics(id) ON UPDATE CASCADE ON DELETE CASCADE
Replica Identity: FULL
Access method: heap
The table size is around 279 GB (from \d)
We had to use replica identity full on this table as setting the replica
identity to the unique index would require downtime to add the NOT NULL
constraints on the 2 FK columns.
And this is the current state of the replication, all the tables are in R
state, except this single table still stuck in F state, it looks like it is
also blocking the main replication slot of the logical replication ?
(I restarted the postgresql not long ago to check if it would unblock it, no
luck)
SUBSCRIBER:
back=> SELECT
pss.relid, pss.relid::regclass AS obj,
pss.pid, pss.latest_end_time, pg_size_pretty(pspc.bytes_processed) as
data_copied,
pspc.tuples_processed,
NOW() - pss.last_msg_receipt_time as age
FROM
pg_stat_subscription AS pss
LEFT JOIN
pg_stat_progress_copy AS pspc ON pss.pid = pspc.pid;
relid | obj | pid | latest_end_time |
data_copied | tuples_processed | age
-------+----------------------+------+-------------------------------+-------------+------------------+-----------------
| | 6346 | 2023-06-09 08:39:23.523942+00 |
| | 00:09:57.941278
16923 | diagnostics_episodes | 6347 | 2023-06-09 08:39:23.87963+00 |
| | 00:08:31.553164
(2 rows)
PUBLISHER:
production=# SELECT
prs.slot_name, prs.slot_type, prs.database, psa.wait_event,
psr.state, prs.temporary, prs.active, prs.active_pid, prs.restart_lsn,
prs.confirmed_flush_lsn
FROM
pg_replication_slots AS prs
LEFT JOIN
pg_stat_activity AS psa ON prs.active_pid = psa.pid
LEFT JOIN
pg_stat_replication AS psr ON psa.pid = psr.pid
WHERE
prs.slot_type = 'logical';
slot_name | slot_type | database |
wait_event | state | temporary | active | active_pid | restart_lsn |
confirmed_flush_lsn
-----------------------------------------+-----------+------------+--------------------+---------+-----------+--------+------------+--------------+---------------------
azure_to_aws | logical | production |
WalSenderWriteData | catchup | f | t | 21534 | A6F/1EF5C210 |
A6F/1F48F728
pg_17845_sync_16923_7225826535892257639 | logical | production |
WalSenderWriteData | catchup | f | t | 21537 | A6D/22E49068 |
A6D/22EDF188
(2 rows)
(the restart_lsn and confirmed_flush_lsn are not changing over time)
production=# SELECT
prs.slot_name, prs.active, psr.state, prs.active_pid,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
prs.confirmed_flush_lsn)) AS diff_size
FROM
pg_replication_slots AS prs
LEFT JOIN pg_stat_replication AS psr ON prs.slot_name = psr.application_name
WHERE
prs.slot_type = 'logical';
slot_name | active | state | active_pid |
diff_size
-----------------------------------------+--------+---------+------------+-----------
azure_to_aws | t | catchup | 21534 | 2855
MB
pg_17845_sync_16923_7225826535892257639 | t | catchup | 21537 | 11 GB
(2 rows)
Something I can see on the monitoring of the subscriber is that it's doing a
lot of read activity (I can't get more info as it's on RDS so no strace,
etc..), I can see it's reading at around 400MBps non stop (and it stops if I
disable the subscription and terminate the process related to the replication),
but I don't see any write going thru.
On either side I don't see any error on the logs, on the subscriber I have the
debug5 level log and I don't see any transaction being applied either by the
replication worker.
On pg_stat_activity I see the logical replication worker of this table always
active, sometime doing IO:DateFileRead, the main replication worker however is
always "idle" waiting for IPC:LogicalFileSyncChange
I am not sure what do to next ? I have been trying to troubleshoot this for a
week but no luck, I also got some help on the PostgreSQL slack server but we
are not 100% sure of what could be the issue either (
https://postgresteam.slack.com/archives/C0FS3UTAP/p1686041198195869 )
On the loadtest environment I first migrated, I saw it had the NOT NULL
constraint set on the columns of the table (but still with the REPLICA IDENTITY
FULL) and it worked, I tried to remove the NOT NULL constraint and do again the
migration and it still worked, so I am not sure it is related to that on the
other environments?
What could be the other causes of this ?
Thank you.
