[I/O] Use cr8 for loading tables from PostgreSQL

[amotl]

About

@hlcianfagna elaborated about typical cr8 usage patterns, which did not make it into the ctk load table interface yet. Thanks!

Details

Regarding copying the content from one table to a new one with different settings/partitioning options/etc, you can use the cr8 insert-from-sql utility. It accepts a --fetch-size parameter which defaults at 100 records, and a --concurrency parameter which defaults at 25.

This tool reads through the PostgreSQL protocol connecting on port 5432, so username and passwords for the source need to be encoded in the connection string. Writing happens through the HTTP endpoint of CrateDB on port 4200, and it can go to a separate cluster.

If your passwords have special characters, you need to encode them properly.

CLI Example

cr8 insert-from-sql \
  --src-uri "postgresql://readuser:readpwd@localhost:5432/doc" --query "SELECT * FROM sourcetable;" \
  --hosts writeuser:writepassword@localhost:4200 --table doc.targettable