GotEth is migrating to Clickhouse and we have designed a migration process for those users who want to migrate their existing data in PSQL to Clickhouse.

Risks

While we want to provide the most useful and integral migration process, there are always certain conditions out of our hands. This is why several recommendations should be followed:

Always use a read-access user to connect to your PSQL database (source database)
Perform checks in the Clickhouse instance, like counting the number of rows in each table. See here for an example.

Notes

Keep in mind the largest tables in the database are t_validator_rewards_summary and t_transactions. These take most of the migration time, especially when having historical data.

Our experience

We host a database with 6750 epochs of data, which escalates to around 1TB of data. The migration of this database has taken almost 8 hours, While the rest of the tables take a couple of minutes to migrate, the above mentioned tables take 99% of the time.

This migration was done with a remote PSQL server and a local Clickhouse server.

Requirements

Version

Please be aware that the migration process needs to be done with v3.0.0. After that you can now upgrade to any above version and database updates will be done automatically.

Clickhouse installed

sudo apt-get install -y clickhouse-server clickhouse-client

OR

curl <https://clickhouse.com/> | sh && sudo ./clickhouse install

Old PSQL server

You will also need access to your old PSQL database. This is, at least being able to connect and read the tables you wish to migrate.