This blog has been around since September 2007. It has lived through a move to a new host around 2012, a couple of reinstalls since, and upgrading my personal laptop (actually I think this would be my 3rd upgrade since I lost my backups). By this fall the server had a fresh WordPress install and none of the old posts.
The Internet Archive had kept most of it anyway. This is how I got 101 posts back out of the Wayback Machine and into a fresh WordPress install, and the handful of things that went wrong along the way.
The short version: download the archived pages, parse the post content out of the HTML, and generate SQL inserts for wp_posts and the category tables. The long version is mostly about the ways fifteen years of archived HTML disagree with each other.
Finding every page
I started by saving the archived home page by hand. That gave me ten posts and a link to page 2. Clicking through captures one at a time was not going to work for a site with archives going back to 2007.
The Wayback Machine has a CDX API that lists every capture it has for a URL pattern. One request returns all of them:
curl 'https://web.archive.org/cdx/search/cdx?url=mengfung.com/*&output=json&fl=timestamp,original,statuscode,mimetype&filter=statuscode:200&filter=mimetype:text/html&collapse=urlkey'That came back with about 170 HTML pages. After dropping feeds, tag pages and comment pages, I had 151 URLs, and I picked the newest good capture of each one.
Downloading them taught me the first lesson: the Wayback Machine rate limits. About fifty requests in, it started refusing connections. The retry loop that worked waited four seconds between pages and backed off for twenty, forty, sixty seconds on failure. Slow, but it finished.
It also helps to know about the id_ flag. Adding it after the timestamp returns the page as it was archived, without the Wayback toolbar and without every link rewritten to point back into the archive:
https://web.archive.org/web/20210515035955id_/https://mengfung.com/2020/09/baby-name-rails-app-part-1-import-data/Two themes, two layouts
The site used one theme from 2007 to about 2012 and Writr after that. Each wraps a post differently. The old theme:
<div class="post" id="post-92">
<h2>Ruby Job Interview Question</h2>
<div class="postmetadata">Posted Monday, December 22, 2008 9:57 am in ...</div>
<div class="entry">...</div>Writr:
<article id="post-528" class="post-528 post type-post ... category-programming">
<h1 class="entry-title">...</h1>
<div class="entry-content">...</div>The parser looks for either opening tag, then walks forward counting nested <div> tags to find where the element ends. Regex alone does not work here, because post content is full of its own divs. The date, title, categories and slug each come from a different place depending on the layout.
Everything is Python standard library. No BeautifulSoup, because it was not installed and I did not need it badly enough to install it.
The post IDs changed
My first plan was to treat the WordPress post ID as the key, so a post seen on the home page, on a category page and on its own page would only be imported once.
That plan lasted until I compared captures from before and after the 2012 move. “Making a Remote Git Repo on Media Temple (DV)” was post 115 in 2010 and post 207 in 2021. Same post, new ID. The move must have been an export and import, which renumbers everything.
So captures get matched on content instead. Same title within a month is the same post. Different titles on the same day get compared on their text:
def same_post(a, b):
if norm(a["title"]) == norm(b["title"]):
return abs((a["date"] - b["date"]).days) <= 31
if a["date"].date() != b["date"].date():
return False
titles = SequenceMatcher(None, norm(a["title"]), norm(b["title"])).ratio()
text = SequenceMatcher(None, opening(a), opening(b)).ratio()
return titles > 0.85 or text > 0.9Those thresholds started at 0.6, which was too loose. “My First Merb App Part I” and “Part II” were posted on the same day with similar titles, and they got merged into one post. So did two of the Spark posts, whose code samples look nearly identical. Raising the bar fixed both without un-merging the post I actually wanted merged: “Home made Poo Pourri” in 2014 became “DIY – Homemade Poo Pourri” on the newer site.
Posts that exist on the newer site keep their Writr-era ID. Posts that only exist on the original site, 37 of them, get new IDs above the rest.
Excerpts, teasers and noteaser
A post on the home page is usually cut off at the “Continue reading” link, so the parser marks those as excerpts and prefers the capture from the post’s own page.
That broke on exactly one post. “Hello Apache Spark” had WordPress’s <!--noteaser--> option on, which hides the part above the more tag on the post’s own page. The home page had the first half and the post page had the second half, and neither had the whole thing. The fix stitches them back together: if the full capture starts at the more tag, borrow the teaser from a listing page capture of the same post.
Five posts with broken captures
Five posts only had one capture each, and all five returned 404 when I tried to download them, even though the index lists them as good captures. A later retry got two of them, so I think the archive was just under load. At the time it looked like they were gone.
The old theme saved me here. It printed full posts on the home page and on archive pages, not excerpts. So I pulled every capture of the home page, the month archives and the category pages from 2009 to 2013. About ninety listing pages later, all five posts were back, along with a few I did not know were missing.
Cleaning the content
The goal was content only. The new site has a new theme, so nothing from the old ones should survive.
- Syntax highlighting. Code was highlighted by two different plugins over the years, and both baked their output into the page as hundreds of colored
<span>tags. Crayon kept a plain copy of the code in a hidden<textarea>, which made it easy. WP-Syntax did not, so its spans get stripped back to text. Every code sample is now a plain<pre><code>block. - Wayback links. Rewritten captures prefix every link with
web.archive.org/web/<timestamp>/. Stripping that is easy. The catch is that a few of my own posts already linked to the Wayback Machine on purpose, to replace links that had died. To tell them apart, a capture only counts as rewritten if links to mengfung.com itself carry the prefix, since I never linked my own site through the archive. - Plugin leftovers. Share buttons, a reading-time label, and a keyword plugin that quietly turned words like “ruby” and “Capistrano” into links.
- Curly quotes. WordPress converts quotes and dashes to HTML entities on output. The database stores the plain characters, so the entities get turned back.
Generating the SQL
The output is a plain SQL file: one INSERT per post into wp_posts, the categories and tags into wp_terms and wp_term_taxonomy, and the links between them in wp_term_relationships. A final update recounts how many posts each category has. The whole thing runs in one transaction, so a failure leaves nothing half imported.
Two details mattered. Strings are escaped for MySQL, including backslashes, because a Dockerfile post with line continuations would otherwise lose them. And every post gets an explicit ID. My first version used LAST_INSERT_ID() for the posts that needed new IDs, which is fine in MySQL but did not survive WordPress Playground’s SQLite translation when I tested the import locally. Explicit IDs work everywhere and are easier to read.
Images went the same way. The archive had most of the files the posts referenced, I regenerated a few missing thumbnail sizes from full-size originals I did have, and two screenshots from a 2014 Vagrant post are gone for good, along with a few logos.
What I got back
101 posts from September 2007 to February 2021, all with their full text, original dates, categories and slugs. The oldest is the first post on the site. Some I had forgotten writing: a Ruby job interview question from 2008, MediaTemple setup notes, a post about the 2009 Lakers.
The scripts are on GitHub: mfung/wayback-to-wordpress. fetch.sh downloads every capture of a site, and wayback_to_sql.py turns them into SQL. It was written for this site, so expect to adjust it for your theme.
If you want to try this on your own site, the steps are: list your captures with the CDX API, download them slowly, find the element that wraps a post in each theme you have used, and match posts on their content rather than their IDs. Expect at least one capture that lies to you.