Copy Druid segments from AWS to S3¶

Imported from Confluence

Content may be outdated. Verify before following any procedures. View original | Last updated: April 2023

115004960053 Migrate Existing Druid Cluster To A New Imply Cluster

Migration of Offerwall Druid to GCP

We need to migrate segment from MySQL in AWS (RDS) to PostgreSQL in GCP (Cloud SQL):

All segments were copied from s3 to gcs with flexify
Create new mysql rds from druid mysql db snapshot
Create mysqldump

mysqldump -h imply-db-to-psql-gcp-2.c7bparwcbb52.eu-west-1.rds.amazonaws.com -u imply -p --set-gtid-purged=OFF imply druid_dataSource druid_segments > imply-mysql-mod.sql

Updated segments location

sed -i 's/\\"type\\":\\"s3_zip\\"/\\"type\\":\\"google\\"/g' imply-mysql-mod.sql
sed -i 's/\\"bucket\\":\\"imply-data-production-1\\"/\\"bucket\\":\\"gcs-druid-old-deep-storage-offerwall-prod-useast1\\"/g' imply-mysql-mod.sql
sed -i 's/\\"key\\":\\"segments/\\"path\\":\\"segments/g' imply-mysql-mod.sql
sed -i 's/,\\"S3Schema\\":\\"s3n\\"//g' imply-mysql-mod.sql

Uploaded modified dump

mysql -h imply-db-to-psql-gcp-2.c7bparwcbb52.eu-west-1.rds.amazonaws.com -u imply -p imply < imply-mysql-mod.sql

From instances in gcp ran docker with pgloader and started migration

docker run -d --rm dimitri/pgloader:latest sleep infinity

root@vm-devops-offerwall-dev-useast1-01-0:~/druid-migration# docker ps
CONTAINER ID   IMAGE                     COMMAND            CREATED         STATUS         PORTS     NAMES
acebd09893a3   dimitri/pgloader:latest   "sleep infinity"   3 seconds ago   Up 2 seconds             condescending_gauss

docker cp pgloader.sql condescending_gauss:/root

screen 

docker exec -it condescending_gauss -- bash

root@acebd09893a3:~# cat pgloader.sql
LOAD DATABASE
     FROM     mysql://imply:password@imply-db-to-psql-gcp-2.c7bparwcbb52.eu-west-1.rds.amazonaws.com/imply
     INTO     postgresql://druid:password@druid-metadata.db-offerwall-dev-useast1.fyber.com/druid
     WITH     prefetch rows = 1000, batch rows = 1000, concurrency = 1

      SET MySQL PARAMETERS
        net_read_timeout  = '1200',
        net_write_timeout = '1200'

     -- INCLUDING ONLY TABLE NAMES MATCHING 'druid_segments'

   alter schema 'imply' rename to 'public';  

root@acebd09893a3:~# pgloader pgloader.sql

....
2023-04-03T12:24:28.584863Z LOG report summary reset
table name errors rows bytes total time
---------------------------- --------- --------- --------- --------------
fetch meta data 0 33 1.330s
Create Schemas 0 0 0.001s
Create SQL Types 0 0 0.001s
Create tables 0 20 0.128s
Set Table OIDs 0 10 0.001s
---------------------------- --------- --------- --------- --------------
public.druid_tasks 0 9027334 103.1 GB 2h52m21.154s
public.druid_segments 0 46991 167.9 MB 16.141s
public.druid_audit 0 34642 61.0 GB 1h44m43.461s
public.druid_tasklocks 0 149 84.0 kB 1.521s
public.druid_pendingsegments 0 105 71.9 kB 3.123s
public.druid_supervisors 0 63 507.2 kB 3.842s
public.druid_rules 0 25 7.2 kB 2.928s
public.druid_config 0 7 1.6 MB 4.153s
public.druid_datasource 0 1 0.5 kB 2.532s
public.druid_tasklogs 0 0 1.439s
---------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 2h52m21.153s
Create Indexes 0 23 5m29.156s
Index Build Completion 0 23 2m44.527s
Reset Sequences 0 4 0.436s
Primary Keys 0 10 0.032s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.001s
Install Comments 0 0 0.000s
---------------------------- --------- --------- --------- --------------
Total import time ✓ 9109317 164.3 GB 3h35.306

Issues:

The default docker image (version 3.6.9) didn't work with Postgresql 14 and failed with HEAP EXHAUSTED error. Adding more memory to the instance, using smaller batches also didn't help
Manually compiled pgloader with default parameters didn't work
According to pgloader (Github) it's recommended to try dimitri/pgloader:ccl.latest docker image
dimitri/pgloader:ccl.latest works!

Post cleaning temp mysql db before running pgloader:

SELECT id, dataSource, created_date, `start`, `end`, partitioned, version, used, payload
FROM imply.druid_segments
WHERE end > '2023-04-01T00:00:00.000Z' ORDER BY `end`;

DELETE FROM imply.druid_segments WHERE end > '2023-04-01T00:00:00.000Z';

SELECT id, dataSource, created_date, `start`, `end`, partitioned, version, used, payload
FROM imply.druid_segments
WHERE dataSource NOT IN ('calzone-creation-timestamp', 'calzone')

DELETE FROM imply.druid_segments WHERE dataSource NOT IN ('calzone-creation-timestamp', 'calzone')

UPDATE imply.druid_config
    SET payload=0x7B7D
    WHERE name='coordinator.compaction.config';
UPDATE imply.druid_config
    SET payload=0x7B7D
    WHERE name='lookupsConfig';

Copying segments from one dev to prod

We need to copy segments from one CloudSQL PostgreSQL in dev to another in prod.

Install pg_dump and pg_restore version 14

  echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
  apt -y update
  apt -y install postgresql-14

Create dump with segments table

pg_dump --host druid-metadata.db-offerwall-dev-useast1.fyber.com --port 5432 --username druid --verbose --column-inserts --format plain --data-only --file "druid_dev_pgsql_inserts.dump" --table public.druid_segments druid

Stop ingestion on Druid prod
Stop all ingestion tasks on the Druid prod cluster to prevent new data from being written during the migration.
Stop Druid
Stop the Druid production services so the database can be safely modified.
Make CloudSQL backup
Create a CloudSQL backup of the production database as a safety measure before restoring.
Restore dump to prod db

psql -h druid-metadata.db-offerwall-prod-useast1.fyber.com -U druid --port 5432 -d druid < druid_dev_pgsql_inserts.dump

Check that old and new segments are present in db
Verify that both the existing production segments and the newly restored dev segments are present in the database.
Start ingestion
Resume ingestion on the Druid prod cluster.