Using rclone (cloning database tables)

Rclone Quick Reference

I finally had to bite the bullet and install rclone, I might use it quite a lot. Essentially, I was trying to transfer some large files off my (productive) Debian Trixie machine onto my (productivity killer) Work Issues Windows Laptop. It turns out you can’t upload large files using OneDrive, so here we go. rclone.

rclone is a command-line tool for copying and synchronising files between a local machine and cloud storage providers such as OneDrive, Google Drive, Dropbox, S3, and many others. The Debian Dropbox tool seems to work fine for me, but nothing else.

For my workflow, the main use is moving large files (e.g. PostgreSQL dumps, data extracts, archives) between Linux and OneDrive.

Checking the Configuration

I meant to write this all down earlier, but forgot. The configuration is menu driven, and I managed to get the wrong config, and spent a lot of time looking at some weird text files that were obviously mine but not what I wanted. Other than restarting the configuration from scratch I don’t really remember how I fixed it. One to watch next time.

Anyway, I can list configured remotes:

rclone listremotes

I can check they have a configuration file:

rclone config file

I can even display the configuration:

rclone config show

Exploring OneDrive

Assuming the remote is called uni:

List top-level folders:

rclone lsd uni:

List files recursively:

rclone ls onedrive:

Navigate to a specific folder:

rclone lsd onedrive:backups

Uploading Files

Copy a single file:

rclone copy mydump.sql onedrive:postgres-dumps

Copy multiple files:

rclone copy dumps/ onedrive:postgres-dumps

Copy a directory and all subdirectories:

rclone copy project-data/ onedrive:project-data

Downloading Files

Download a single file:

rclone copy onedrive:postgres-dumps/mydump.sql .

Download an entire directory:

rclone copy onedrive:postgres-dumps ./postgres-dumps

Synchronisation

I haven’t tried syncronising yet, it might be my next trick.

rclone sync dumps/ onedrive:postgres-dumps

Synchronise OneDrive to local:

rclone sync onedrive:postgres-dumps dumps/

Warning: sync deletes files in the destination that do not exist in the source.

Useful for Large PostgreSQL Dumps

Create a dump:

pg_dump -Fc mydb > mydb.dump

Upload it:

rclone copy mydb.dump onedrive:postgres-dumps

Later, on another machine:

rclone copy onedrive:postgres-dumps/mydb.dump .

Restore:

pg_restore -d mydb mydb.dump

Progress Indicators

Show transfer progress:

rclone copy mydb.dump onedrive:postgres-dumps --progress

Short form:

rclone copy mydb.dump onedrive:postgres-dumps -P

Dry Runs

See what would happen without copying anything:

rclone copy dumps/ onedrive:postgres-dumps --dry-run

or

rclone sync dumps/ onedrive:postgres-dumps --dry-run

Checking What Was Uploaded

Compare source and destination:

rclone check dumps/ onedrive:postgres-dumps

Rules of Thumb

  • Use copy to upload/download without deleting anything.
  • Use sync only when you want the destination to exactly match the source.
  • Add -P for long-running transfers.
  • Use --dry-run before any large sync.

There’s a much longer story here, I can’t run sqitch and I can’t pipe bash importer scripts to psql on a windows machine because it’s configured for unproductivity. Nevertheless, I can do a psql -f dumped_table.sql routine, which I suppose means the tables exactly match what I have on my desktop. Just not the way I wanted to do it.