Migrating Plausible Data Between Instances (Servers)
Tags: webdev, plausible, docker, caprover
Published: 2024-03-24 20:51:05

### **Overview**
Over the weekend, our Caprover Instance (open-source heroku alternative) crashed inexplicably. After lots of digging, we found out that there was a corrupted file which prevented the swarm from starting. The error we got was: `can't initialize raft node: irreparable WAL error: wal: max entry size limit exceeded`. This is the equivalent to the Windows Blue Screen of Death... This docker instance was beyond repair. We started to investigate and found out that we had a 40gb Click House volume from Plausible. 
It turned out that query logging had inflated our database size to 40GB, whereas the actual data was just 1GB. 
Important Note: This was fixed by the Plausible team a long time ago, however we supported (sponsored) and used the project right from the start, so our instance had the logging issue. To be able to save our data, we needed to move to a new Plausible install on a new server. (Migrating to Hetzner along the way).

We determined that these were the needed steps: export data from ClickHouse and PostgreSQL, transfer it securely, and import it into a new environment. 

Easier said than done.... A weekend later, with  lots of Stack Overflow and GPT-4 help, we managed to migrate over without any data loss. The below is a tutorial we wrote for our internal knowledge base. 

### **Step 1: Exporting Data**

#### **A. Export from ClickHouse**

1. **Access ClickHouse Container:**
   ```bash
   docker exec -it <clickhouse-container-id> clickhouse-client
   ```

2. **Check Database Size:**
   - To list all databases and their sizes:
     ```sql
     SELECT database, formatReadableSize(sum(bytes_on_disk)) as total_size_on_disk FROM system.parts GROUP BY database ORDER BY sum(bytes_on_disk) DESC;
     ```
   - For detailed table sizes within a specific database:
     ```sql
     SELECT table AS table_name, formatReadableSize(sum(bytes_on_disk)) AS size_on_disk FROM system.parts WHERE database = 'your_database_name' GROUP BY table_name ORDER BY size_on_disk DESC;
     ```

==Note: Here we found that the clickhouse database was the culprit and had over 30gb in query logs.== 

3. **Export Individual Tables:**
   For each table in your database, export it as a CSV file. Repeat this process for every table you wish to export:
   ```bash
   clickhouse-client --query="SELECT * FROM your_database_name.your_table_name FORMAT CSV" > your_table_name.csv
   ```
   Optionally, compress the CSV files into a single archive for convenience using `tar`:
   ```bash
   tar -czvf your_database_backup.tar.gz *.csv
   ```

#### **B. Export from PostgreSQL**

1. **Direct Export to Host:**
   Use `docker exec` to run `pg_dump` within the PostgreSQL container, saving the output directly to the host machine:
   ```bash
   docker exec <postgres_container_id> pg_dump -U postgres your_database_name > /path/on/host/backupfile.sql
   ```


## **Step 2:  Download via SSH :** 
For us, the easiest way to grab the CSV files from ClickHouse and the sql file, was to copy them over using `scp`. 

CSV Folder (Recursive):
   ```bash
   scp -r your_username@remote_host:/path/to/csv_folder /local/directory
   ```

Single File:
   ```bash
   scp your_username@remote_host:/path/to/backupfile.sql /local/directory
   ```

### **Step 3: Creating A New Plausible Instance**

With our data safely stored on our local machine, it was time to spin up a new instance of Plausible. For this, I recommend the official [Community Edition Guide from GitHub.](https://github.com/plausible/community-edition/)

> Important Note: Do not create a new account. Once you're on the registration page, it's time to import our data. 

### **Step 3: Importing Data**

### **A. Stop Plausible Docker Service**

It's a good idea to pause the main Plausible container here in order to avoid any type of data corruption. 

#### B. Importing the Postgres DB

1. **Access Postgres**:
   First, access the PostgreSQL command line interface within your Docker container. Replace `<container_name_or_id>` with the name or ID of your PostgreSQL container:
   
```bash
docker exec -it <container_name_or_id> psql -U postgres
```

2. **Drop the Existing Database**
   **Warning:** Dropping a database will permanently delete it and all data contained within. Ensure you have backups if necessary.

From within the PostgreSQL CLI, run:
```sql
DROP DATABASE plausible_db;
```

If the database is being accessed by other users, you might encounter an error. To force the database to drop, you can disconnect all connected users by running:

```sql
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'plausible_db';
DROP DATABASE plausible_db;
```

Then, exit the PostgreSQL CLI:
```sql
\q
```

3. **Create a New Database:**
   Still, within the Docker container's shell, create a new database with the same name:
```bash
docker exec -it <container_name_or_id> createdb -U postgres plausible_db
```

4. **Import the SQL File:**
   
   Now, let's import the SQL file into the newly created database. On your host machine, run:
   
```bash
cat /path/to/your/plausible_backup.sql | docker exec -i <container_name_or_id> psql -U postgres -d plausible_db
```

Replace `/path/to/your/plausible_backup.sql` with the actual path to your SQL file. This command streams the SQL file into the `psql` command running inside your Docker container, importing the data into your `plausible_db` database.

### Additional Notes &  Handling Issues :
- Ensure that the SQL file contains the necessary commands to create tables and insert data. If it was generated by `pg_dump`, it should be fine.
- If your SQL file is particularly large, the import process might take some time. Monitor the process and check for any errors in the output.

- **Role Does Not Exist Error:** When importing into PostgreSQL we faced a "role does not exist" error. This was because the version we used had the user plausible, while the new one used postgres. Modify the SQL dump file (in any text editor) to replace `OWNER TO plausible` with `OWNER TO postgres`. 

#### **C. Check ClickHouse DB and Structure**

 1. **Access the ClickHouse Client**
 
Initiate an interactive session with your ClickHouse container to access the ClickHouse client. Replace `<container_name_or_id>` with your container's actual name or ID:

```bash
docker exec -it <container_name_or_id> clickhouse-client
```

 2. **Select Your Database**
Switch to your target database to ensure subsequent commands apply to it:
```sql
USE plausible_events_db;
```

3. ** List All Tables**
Display all tables within your selected database:
```sql
SHOW TABLES;
```

4. **Examine Table Structure**
For details and to check that nothing changed on a specific table's structure, use the `DESCRIBE TABLE` command:
```sql
DESCRIBE TABLE events;
```
Or more succinctly:
```sql
DESC events;
```

5. **Query Table Data (Optional)**
To query data from a particular table, execute a `SELECT` statement:
```sql
SELECT * FROM events LIMIT 10;
```

#### **D. Importing CSV Data into ClickHouse**

Importing CSV into Clickhouse is done by one simple command per CSV. 

You need to repeat this for every csv that you want to import. For us, these were the non-empty csv files from the export:

- events_v2
- events
- sessions_v2
- sessions
- ingest_counters
- schema_migrations

```bash
docker exec -i <container_name_or_id> bash -c "clickhouse-client --query=\"INSERT INTO plausible_events_db.TABLENAME FORMAT CSV\" < /path/in/container/CSVFILE.csv"
```
Replace `<container_name_or_id>` and `TABLENAME` and `CSVFILE` with your container's name or ID and the CSV file's name and table. 

#### **Verifying Data Import**
Ensure your data was accurately imported by executing a few checks:

1. **Count Imported Rows**
   Verify the total row count in the `events_v2` table:
   ```bash
   docker exec -it <container_name_or_id> clickhouse-client --query="SELECT COUNT(*) FROM plausible_events_db.events_v2;"
   ```

2. **Inspect Initial Rows**
   Look at the first few rows to confirm the data appears as expected:
   ```bash
   docker exec -it <container_name_or_id> clickhouse-client --query="SELECT * FROM plausible_events_db.events_v2 LIMIT 10;"
   ```

3. **Check for Specific Data**
   If looking for particular data, such as a specific `event_id`, tailor a query to verify its presence:
   ```bash
   docker exec -it <container_name_or_id> clickhouse-client --query="SELECT * FROM plausible_events_db.events_v2 WHERE event_id = 'expected_event_id' LIMIT 1;"
   ```


### **Final Steps**

- **Restart Plausible Container:** After completing the imports, restart the Plausible container to initiate database connections and migrations. This can take 10-15 seconds.
- **Verification:** Log in with your previous credentials to verify that all data has been successfully migrated.

### **Conclusion**

I hope that this guide provided a structured approach to exporting, transferring, and importing database data for Plausible analytics, including troubleshooting common issues. Ensure you have backups and verify data integrity at each step to ensure a smooth transition.