Transferring cleaned up articles

Published on 2026-04-15 21:57

Since I've started blogging on my own CMS called Sloth, I've had bunch of articles that were not properly processed. At this point, it was embarrassing. So let's look at how I've cleaned them up.

The plan was to take data from local machine and then upload it to the server. As there's no UI for this I had to make two scripts. One to download the data and one to upload the data.

The common part for both files are getting the configuration data. In Sloth they are not stored in .env files but in the config folder and are named as [environment].py. This has advantages and disadvantages. Main advantage why there's no plan to move to .env is regular expression for CORS.

Back to the common part, as the first step it's necessary to get database credentials stored in the file:

config_filename = os.path.join(os.getcwd(), '..', 'config', f'{os.environ["SLOTH_ENV"]}.py')
try:
    with open(config_filename, mode="rb") as config_file:
       exec(compile(config_file.read(), config_filename, "exec"))
except IOError as e:
    print(e)

When we have credentials, we can start accessing the database. As Sloth is using psycopg, it is used in these two scripts as well:

with psycopg.connect(
          f"postgresql://{DATABASE_USER}:{DATABASE_PASSWORD}@{DATABASE_URL}:{DATABASE_PORT}/{DATABASE_NAME}"
    ) as con:
    with con.cursor() as cur:

After getting the data from the database, it was time to decide how to store it for the transfer between two machines. In this case it was a choice between SQL, JSON, JSON with content encoded as Base64 and pickle. Plain text JSON and SQL were two things I wanted to avoid because of how they store data and my experience with deserialising JSONs. At the end pickle won because dealing with Base64 strings felt like an overkill. Also it's trusted data, so pickle was a safe option.

with open('sections.pickle', 'wb') as f:
    pickle.dump(results, f)

Uploading the data into the database was the harder part. Unfortunately I haven't worked with that part of the codebase for a while. So my mental model was that sections get updated. Instead those sections get dropped and new ones with the correct texts are inserted.

Because of that I've had create a backup table with same columns and insert all post sections in there if they had post's UUID in the list of affected posts.

INSERT INTO sloth_post_sections_imported_backup SELECT * FROM sloth_post_sections WHERE post in (...)

After that it was easy to delete the affected sections in sloth_post_sections and insert in there the un-pickled sections.

In the end transfer was an evening project but fixing all the posts took quite a few evenings over several weeks.