Since early 2024, I’ve used the hobby cloud plan for Umami for simple and privacy-friendly web analytics for my personal website, as well as for the BikeSpace project.

It’s a really fantastic tool, but I noticed recently that the hobby plan started only showing me the last six months of data from my site. I’ve had some success starting to self-host some things using Coolify in the last couple of months, so I started working on switching to self-hosted Umami so that I could see (and keep) the full history of my web analytics.

I found a couple guides and resources out there for doing this, but many of them were a little out of date. Here are the steps I took to migrate the analytics for my personal site, including an updated python script for formatting the import data.

Exporting your data from Umami cloud

When I first signed up for the Umami cloud hobby plan, it advertised “1 year data retention”. It looks like they updated this to “6 month data retention” around October 2024. I had actually never noticed either retention period, since it continued to show me my full history up until recently.

Fortunately, when I exported my data, it still had the full history available, so I was able to port everything over to the self-hosted instance and didn’t lose anything.

To download the data for your site(s):

  • Log in to Umami cloud
  • Select your account in the main menu (top-left “hamburger” icon), then “settings”
  • Open the main menu again, and select “data”
  • Open “export”, select the site you want to download the data for, and enter the date range to download. If you have more than 12 months of data, you can use the “Custom Range” option.

For actually migrating, we’ll do the data export after we’ve already switched our website to using our self-hosted Umami instance, but if you’re worried your data might be lost, there’s no harm in exporting a copy of the data now.

After requesting the data export, you’ll receive an email with a link to download the data for your site. It will give you a zip file with a random UUID name (will look like some random characters) — to help yourself later, I would suggest re-naming the zipped or un-zipped folder with the name of the site you downloaded and the download date.

Setting up self-hosted Umami

There are many good options for self-hosting Umami, and there are several helpful hosting guides on the Umami docs. As mentioned above, I deployed my instance on Coolify, which was very easy since it’s one of their one-click deploy services.

However you self-host, for the steps below, you will need to make sure that you have SSH and SFTP access for the machine or container that the Umami database is running on.

Once your self-hosted instance is working, go ahead and switch over to using it for your website. When your site is no longer logging its analytics to Umami cloud, we’ll download that data (again) and back-fill the self-hosted database.

Preparing your export files

Follow the export steps noted above to download the data for your site. When I did my exports, it gave me the following files:

  • website_event.csv includes data on sessions and pageviews/events
  • session_data.csv has always been blank for me (see note below)
  • event_data.csv includes event properties

I’m not sure why session_data.csv has been blank for all of the sites I’ve exported, but we can recreate the session database table using website_event.csv, so it’s not really a problem.

For many people, event_data.csv may also be blank, unless you use the “data” argument for tracker functions to log custom data along with certain events.

If you downloaded a copy before, you can check to see if any old data has been lost in the meantime by opening website_event.csv in something like Excel, sorting on the “created_at” column, and comparing the oldest dates in each file.

Once you have your data downloaded, set up a working folder to process it into files that you can import into your self-hosted instance. It helps for the following steps if the folder has a relatively short filepath, e.g. ~/umami_import/ will be easier to use than something like /Users/ben/Library/CloudStorage/OneDrive-Personal/Documents/Programming_Projects/umami_import/.

Into your folder, put your exported files as well as the umami_import.py processing script.

If you’re just migrating one site, you can just put everything in the same folder, like this:

umami_import/
├── umami_import.py
├── website_event.csv
├── session.csv
└── event_data.csv

If you have multiple sites, I would recommend setting up one sub-folder per site:

umami_import/
├── umami_import.py
├── my_website_1/
│   ├── website_event.csv
│   ├── session.csv
│   └── event_data.csv
└── my_website_2/
    ├── website_event.csv
    ├── session.csv
    └── event_data.csv

Processing your export files

To generate your import files, open umami_import.py and configure these variables at the top:

# Enter the website ID from your new self-hosted instance
NEW_WEBSITE_ID = ""
# Folder where the export files from your cloud instance are saved
EXPORT_FOLDER = ""
# Folder where you want to save your generated import files
IMPORT_FOLDER = ""

For NEW_WEBSITE_ID, go to your site in your self-hosted Umami instance, click “edit”, and then the website ID should be the first item listed. Copy it into the script between the quotes next to this variable.

If you are using the “everything in one folder” method, you can leave EXPORT_FOLDER and IMPORT_FOLDER blank and it will both read and save files in the same folder as your script.

If you are using specific folders for each site, put in the name of the folder that you want to process between the quotes next to EXPORT_FOLDER and IMPORT_FOLDER.

If you want to put your generated files in a different folder than your cloud export files, you can do that by specifying different folders for each variable. You don’t need to make the IMPORT_FOLDER in advance; if it doesn’t exist, the script will make it for you.

To run the script, I would recommend installing uv and then you can run it with:

$ uv run umami_import.py

Other options would include pipx run or just installing pandas (ideally into a virtual environment) and then running python umami_import.py.

Once the script has run, you should have two or three new files:

  • website_event_new.csv
  • session_new.csv
  • event_data_new.csv (only if your event_data.csv file was not blank)

If you run into any issues with running the script, feel free to leave a comment and I’ll see if I can help.

Thank you to this blog post with older instructions on how to migrate and the accompanying github repo for helping me get started on figuring this step out!

Loading the data into your self-hosted instance

The specific steps at this stage might vary depending on how you’ve deployed your self-hosted instance. These are the steps I took for Coolify.

The final database load in this process might be difficult to undo if it goes wrong, so if your method of self-hosting has a backup mechanism, I would recommend doing that first, if you can.

At a high level, Coolify lets you take a server and easily run and manage docker containers on it. When you one-click deploy Umami on Coolify, it will run two containers for you: one for the Umami web app, and one for the database it uses.

To get our old data into our self-hosted Umami instance, we have to:

  • Get our import files onto the server that Coolify is running on
  • Get the files now on the server into the docker container running the database
  • Go into the docker container and use psql to load the files into their corresponding database tables

Transferring the files from your computer to the server

To get the files onto the server, I used sftp like this:

# connect to the server using sftp
sftp username@remote_host

# if you need to specify the port
sftp -P 1234 username@remote_host

# copy the files over 
# first argument is the name/location on your computer
# second argument is the name/location it should be copied to on the server
put umami_import/session_new.csv session_new.csv
put umami_import/website_event_new.csv website_event_new.csv
put umami_import/event_data_new.csv event_data_new.csv

# end sftp session
exit

Notes:

  • In my case, I have public key authentication set up, but if you don’t, it will prompt you to enter your password.
  • While in an sftp session, commands like ls, pwd, and cd will give you results for the server. If you want to navigate the files on your computer, you can prefix them with ‘l’ for ‘local’, e.g. lls, lpwd, and lcd.

Transferring the files from the server to the docker container

First, ssh into your server:

ssh username@remote_host

# if you need to specify the port
ssh -p 1234 username@remote_host

Then, figure out the id of the docker container that has your umami database in it:

# my default ssh user is not root, so I'm using sudo for docker commands
# the format flag optionally makes things slightly easier to read
sudo docker ps --format "{{.ID}}: {{.Names}}"

# result should include something like this
# find the postgres with the id matching the umami container
e528dc1df8e0:      umami-d6eb7906b6a140f7823e9fb0
e954b58fabd8: postgresql-d6eb7906b6a140f7823e9fb0

Once you have found the container id (in this example case, e954b58fabd8), you can copy the files into the container with docker cp:

sudo docker cp session_new.csv e954b58fabd8:session_new.csv
sudo docker cp website_event_new.csv e954b58fabd8:website_event_new.csv
sudo docker cp event_data_new.csv e954b58fabd8:event_data_new.csv

Loading the files into your database

Now, connect to a shell session inside your docker container:

sudo docker exec -it e954b58fabd8 sh

If you run ls, you should see the files you just copied in. Then you’ll want to start a session with psql (essentially the command line interface for postgres).

To find the username and database name, go into the Coolify configuration page for your Umami instance, select Environment Variables, and find the values for SERVICE_USER_POSTGRES and POSTGRES_DB.

Then you can start your psql session like this:

psql -U $SERVICE_USER_POSTGRES -d $POSTGRES_DB

# e.g. for SERVICE_USER_POSTGRES=93d00085c8d4500b and POSTGRES_DB=umami
psql -U 93d00085c8d4500b -d umami

If you run \dt, you should see the list of database tables, including the session, website_event, and (optional) event_data tables we will backfill:

                   List of relations
 Schema |        Name        | Type  |      Owner       
--------+--------------------+-------+------------------
 public | _prisma_migrations | table | 93d00085c8d4500b
 public | event_data         | table | 93d00085c8d4500b
 public | link               | table | 93d00085c8d4500b
 public | pixel              | table | 93d00085c8d4500b
 public | report             | table | 93d00085c8d4500b
 public | revenue            | table | 93d00085c8d4500b
 public | segment            | table | 93d00085c8d4500b
 public | session            | table | 93d00085c8d4500b
 public | session_data       | table | 93d00085c8d4500b
 public | team               | table | 93d00085c8d4500b
 public | team_user          | table | 93d00085c8d4500b
 public | user               | table | 93d00085c8d4500b
 public | website            | table | 93d00085c8d4500b
 public | website_event      | table | 93d00085c8d4500b

Our import files are already named to match the table they need to be imported into. To load them, we’re going to use the \copy command like this:

--  command format
 \copy target_table FROM '/path/to/local/filename.csv' WITH DELIMITER ',' CSV HEADER;

--  for example
\copy session FROM 'session_new.csv' WITH DELIMITER ',' CSV HEADER;
\copy website_event FROM 'website_event_new.csv' WITH DELIMITER ',' CSV HEADER;
\copy event_data FROM 'event_data_new.csv' WITH DELIMITER ',' CSV HEADER;

-- if your import file has 123 rows, successful completion should give you a message like this:
COPY 123

If you didn’t get the expected response or something went wrong, keep reading on and take a look at the appendix, or leave a comment. If everything went smoothly, when you look at your stats on your self-hosted Umami instance, you should see the data from the time period you imported.

After this is done, you’re in an psql session inside a docker container inside your remote server, and probably wondering how to get out:

# exit psql
\q

# exit your docker container
exit

# exit your ssh session - either of these should work
exit
logout

Congratulations! You’ve migrated to self-hosting Umami.

Appendix: some troubleshooting ideas

If one or more of your import files didn’t load properly into your database, then there might be a mis-match between the database schema that Umami is using and your import file.

The \copy command expects the following from the input file:

  • A properly formatted CSV file with the first row being column headers
  • Columns should be in the same order as the database schema
  • Columns should match the types expected by the database
  • Additional details in the postgres documentation for \copy

To check that the processing script is outputting a file that matches the database schema, you can follow the instructions noted above to get into the container, start a psql session, and try commands like:

-- list tables
\dt

-- describe a table schema
-- this will show you the expected column order and data types
\d TABLE_NAME

-- show some real data from a table (the semicolon at the end is important!)
SELECT * FROM TABLE_NAME;
SELECT * FROM TABLE_NAME LIMIT 5;

-- expanded display will make things easier to read
\x on
\x off

-- this is an sql session, so run whatever queries you find helpful, e.g.
SELECT * FROM event_data ORDER BY created_at DESC LIMIT 3;

If you want to check to make sure the order of the columns in the database tables matches what the script is generating, go to umami_import.py and find the lines commented like this:

# Check in psql with `\d TABLE_NAME`
# Must be in exactly the same order as the table schema

If you’re reading this some amount of time after I wrote it, checking the history of changes to the Umami database schema migration files might also give you some clues as to changes that would require updating the script.