Fix migration failure of an empty database with --drop-if-exists. 8000 #152
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
It fixes a corner case when --drop-if-exists is used to migrate a database that contains no tables. Before this change, `copydb_target_drop_tables` generates an invalid SQL query`DROP TABLE IF EXISTSCASCADE`.
dimitri
approved these changes
Nov 21, 2022
|
Thanks @shubhamdhama ! |
arajkumar
added a commit
to arajkumar/pgcopydb
that referenced
this pull request
Jul 31, 2024
Prior to this fix, we run the extension migration in the following order, 1) Run `SELECT timescaledb_pre_restore()` 2) CREATE EXTENSION IF NOT EXISTS timescaledb; 3) COPY extension config tables 4) Run `SELECT timescaledb_post_restore()` With this procedure, we see the following error while copying _timescaledb_catalog.metadata config table, ``` 2024-05-14T10:24:50.281 ERROR: 2024-05-14 10:08:10.486 27254 ERROR pgsql.c:2985 [TARGET 1147] [23505] ERROR: duplicate key value violates unique constraint "metadata_pkey" 2024-05-14T10:24:50.281 ERROR: 2024-05-14 10:08:10.486 27254 ERROR pgsql.c:2993 [TARGET 1147] DETAIL: Key (key)=(exported_uuid) already exists. ``` The problem is, Step #1 renames the key "exported_uuid" from "_timescaledb_catalog.metadata" to "exported_uuid_bak", but #2 again creates the same key "exported_uuid leading to PK violation during the migration. *Solution*: Run timescaledb_pre_restore after CREATE EXTENSION step. i.e. Swap (1) and (2). In order to do this with pgcopydb, we need to split the `pgcopydb clone` into , 1) `pgcopydb dump pre-data` 2) `pgcopydb restore pre-data` - Which also creates extension if not exists 2) RUN `SELECT timescaledb_pre_restore()` 3) `pgcopydb clone` - Resumes automatically after pre-data *Why there is no integration test?* This behaviour is very specific to cloud and couldn't produce on self-hosted target. Reference: timescale/Support-Dev-Collab#1684 (comment) *How to test?* 1) Test migration with tsdb 2.13.0 extension while the target is on the Timescale cloud. Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>
arajkumar
added a commit
to arajkumar/pgcopydb
that referenced
this pull request
Jul 31, 2024
0b57967 Fix issues with tsdb 2.13.x migration dimitri#152 0b57967 Ask user not to close/interrupt snapshot terminal (dimitri#151) 26fa73a Implement cross version TS to TS migration (dimitri#147) 58ed2a6 Refactor matview rename handling (dimitri#145) ddb8d8e Support chunk mapping for hypertable with custom schema and table prefix (dimitri#149) f28665d Set application name to all psql sessions (dimitri#146) fc29786 Upgrade test-common and other dependencies (dimitri#148) 209812c Recommend using docker run with –user CLI flag (dimitri#144) 5395420 Fail fast when initial data copy fails (dimitri#143) f1f0398 feat: implement retry for initial data migration (dimitri#137) 10da671 Use pgcopydb for PG to TS initial data copy (dimitri#120) c9cfa08 Exit with error code on failure (dimitri#142) Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
It fixes a corner case when --drop-if-exists is used to migrate a database that contains no tables. Before this change,
copydb_target_drop_tablesgenerates an invalid SQL queryDROP TABLE IF EXISTSCASCADE.