Tableau Server, for those not familiar with it, is a system for creating, managing, and sharing visual analytics and all the things that go into it. While users can create data sources and visualisation workbooks, just like with the O.G. Tableau Desktop application, Tableau Server does many things beyond that. It is a full-blown web application server that can be programmed and that collects and keeps a lot of data about how it is used.
If your job is to manage a Tableau Server installation, because you happen to be the server administrator, then this data can be super-useful. Finding out which users have not used the system for a long time? Easy. Understanding which Vizzes are popular and important? Not a problem. Getting a list of content candidates that are ready to be retired/archived? Yep. Getting all data source owners that connect to a specific database? Sure. You get the idea.
Any important data and metadata that Tableau Server uses is stored in a single PostgreSQL database that comes with Tableau Server. This database is called The Tableau Server Repository or workgroup DB after the name of the database in PostgreSQL. The repository schema is publicly documented and commonly used for all sorts of analysis.
Why do I go on about it? Because I have to work with copies of large Tableau Server repositories rather regularly. One part of my day job is to plan, execute, and support projects like moving workloads from Tableau Server to Tableau Cloud, a fully hosted SaaS offering of Tableau Server functionality. To do this, I need to know many details about how the existing Tableau Server system is used; which I can find out by querying the customers’ repository database.
In order to not disturb the ongoing use of the system, this analysis is done on a copy of the repository DB. This copy created via a built-in function of Tableau Server ( tsm ziplogs) which uses pg_dump in the background.
Once I have the workgroup.pg_dump file from my customer I can restore it to a local PostgreSQL instance and get to work.
So, what’s the problem? #
So far, this is all great and pretty straight forward. Except for the detail that Tableau Server not only stores usage and metadata in the repository but also binary large objects (aka BLOBs). Things like the XML-esque files that store data source and workbook definitions or the profile pictures of user accounts all get stored in corresponding PostgreSQL large object fields.
As the name implies, these objects tend to be comparatively large i.e., storing them requires a lot of storage space and having to restore them when recreating the database dumps is taking a long time, too.
Worst of all is: for my work, I really have no use for this data. If all the BLOBs were gone from the restored DB, I could still do my job just fine and have a lot more free space on my hard drive.
But I don’t want those BLOBS! #
This is where I get to the point: it is not only possible but rather easy to exclude the BLOBS from the database restoration.
The “trick” here is that the pg_restore program allows for selective restoration of database objects.
Instead of running something like
pg_restore --host… --port… --dbname … workgroup.pg_dump
I create the inventory of objects in the dump first:
pg_restore -l workgroup.pg_dump > workgroup.list
This creates a textfile workgroup.list with all objects stored in the dump file, including all the BLOBs.
To exclude those from the restore, we can comment out the lines that refer to blobs. This is done by putting a ; (semi-colon) at the beginning of the line.
Unfortunately, there can easily be tens of thousands of those BLOB objects in a .pg_dump file of a Tableau Server repository. Manual editing of the list file won’t do.
Fortunately, the IT forefathers at Bell created sed the “stream-editor” — what a cool name for a tool. Reminds me of
TRON: Elementary physics: a beam of energy can always be diverted.
Now, sed is not exactly what one would call “intuitive”. Luckily there are plenty of good online resources available that help with that. I used the GNU sed REPL when developing this.
Each line in the list file that refers to one of the BLOBS we don’t want conveniently has the word BLOB in it. This turns the task of commenting out into:
- in every line check if “BLOB” occurs and if it does
- put a
;at the beginning of that line.
In sed language this is what that looks like:
sed -i.bak -e '/BLOB/s/^/;/g' workgroup.list
This creates a backup copy of the file ( -i.bak ) and runs a sed script ( -e).
The script/expression
- matches lines that contain “BLOB” and then
- substitutes
/s - the beginning of the line
/^ - with a semicolon
/; - and does that “globally”
/g, which means this is done for all lines with BLOB in it, not just for the first one found.
Finally, the edited list can be used to tell pg_restore what to restore and what to ignore via the -L parameter:
pg_restore --host=... --port=... --dbname=workgroup --username=... --password -L workgroup.list workgroup.pg_dump
Success! #
For my current project a 56GB pg_dump file resulted in a 17GB database without the BLOBS.
If that is not a successful diet, I don’t know what is.
There you go, now you know!