8000 Document using pgcopydb for citus-to-citus migrations. by dimitri · Pull Request #671 · dimitri/pgcopydb · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Document using pgcopydb for citus-to-citus migrations. #671

New issue

Have a question about this project? Sign up for a free GitHub account to ope 8000 n an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
wants to merge 2 commits into
base: main
Choose a base branch
from
Open

Document using pgcopydb for citus-to-citus migrations. #671

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
2ad83de
Document using pgcopydb for citus-to-citus migrations.
dimitri Feb 5, 2024
33a6ae5
Update docs/tutorial.rst
dimitri Feb 6, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
70 changes: 69 additions & 1 deletion docs/tutorial.rst
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -98,6 +98,74 @@ before copying the data over:
Note that to ensure consistency of operations, the ``pgcopydb snapshot``
command has been used. See :ref:`resuming_operations` for details.

Use pgcopydb to migrate from a Citus database to a Citus database
-----------------------------------------------------------------

It is possible to use the previous tutorial to implement support for
migrating a Citus database to its new hosting environment. For that, the
``schema-changes.sql`` script from the previous section needs to be the
Citus script that calls into the `Citus DDL`__ functions.

__ https://docs.citusdata.com/en/latest/develop/reference_ddl.html

Here is an SQL query that exports the commands to use to reproduce a Citus
distribution scheme:

.. code-block:: sql
:linenos:

with citus_tables AS
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Suggested change
with citus_tables AS
WITH citus_tables AS

(
SELECT logicalrelid AS table_name,
CASE WHEN colocationid IN (SELECT colocationid FROM pg_dist_schema)
THEN 'schema'

WHEN partkey IS NOT NULL
THEN 'distributed'

WHEN repmodel = 't'
THEN 'reference'

ELSE 'distributed'
END AS citus_table_type,

coalesce(column_to_column_name(logicalrelid, partkey), '<none>')
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I am unsure if it looks better if we capitalize coalesce call here.

Feel free to ignore this suggestion.

Suggested change
coalesce(column_to_column_name(logicalrelid, partkey), '<none>')
COALESCE(column_to_column_name(logicalrelid, partkey), '<none>')

AS distribution_column,

colocationid AS colocation_id,

(select count(*) from pg_dist_shard where logicalrelid = p.logicalrelid)
AS shard_count,

rank() OVER (PARTITION BY colocationid ORDER BY logicalrelid DESC)
AS colo_rank

FROM
pg_dist_partition p

ORDER BY
logicalrelid::text
)
SELECT
CASE
WHEN citus_table_type = 'distributed' AND colo_rank = 1
THEN 'SELECT create_distributed_table(''' || table_name || ''', ''' || distribution_column || ''', colocate_with := ''none'', shard_count := ''' || shard_count || ''');'

WHEN citus_table_type = 'distributed'
THEN 'SELECT create_distributed_table(''' || table_name || ''', ''' || distribution_column || ''', colocate_with := ''' || lag(table_name) OVER (PARTITION BY colocation_id ORDER BY colo_rank) || ''' );'

7DC3 WHEN citus_table_type = 'reference'
THEN 'SELECT create_reference_table(''' || table_name || ''');'
END AS command
FROM
citus_tables
ORDER BY
colocation_id, colo_rank;

Store the output of that query in the ``schema-changes.sql`` script and
follow the previous section of the tutorial for a Citus-to-Citus migration
using pgcopydb.

Follow mode, or Change Data Capture
-----------------------------------

Expand All @@ -114,7 +182,7 @@ Start with the initial copy and the replication setup:
$ pgcopydb clone --follow

While the command is running, check the replication progress made by
pgcopydb with the Postgres `pg_stat_replication`_ view.
pgcopydb with the Postgres `pg_stat_replication`__ view.

__ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW

Expand Down
0