2 of 2 people found this helpful
Yep, this is within the PG database. It's buried as XML named value pairs within a privileged system table named "pg_largeobject", and it's encrypted. So short answer is that there's no way. Longer answer is if you figure out how to gain access to this table and parse it out, let me know
1 of 1 people found this helpful
All I’ve seen in the Postgres DB is a list of the custom views and their names. Nothing on the configuration. I suspect that data isn’t stored in a relational table. Probably an XML data structure sitting in a subfolder on the server. What you are wanting to do probably requires writing code to hack these files.
The same export_workbooks.sql script that I attached to Re: View custom sql for workbooks on Tableau Server will work to get the text out of the PostgreSQL Repository for the customized view data. You'd just alter the query towards the end so that the customized_views.repository_data_id column is what would provide the data from the repository_data table. I just tested it and it worked like a charm. Note that you'd also want to change the extension, since the data you're extracting isn't a .twb anymore, it's a different XML file.
Hi Kirsty, Matt,
I'm trying to follow the steps described to retrieve a custom view. However from your explanations I'm missing a bit - sorry... I know exactly which view to get and I have an id.
However when I use the lo_export SQL that was in the script, I got an error message:
ERROR: must be superuser to use server-side lo_export()
HINT: Anyone can use the client-side lo_export() provided by libpq.
Am I running the SQL in the wrong place?
How do I connect? I do not have a superuser password. I only have the readonly user I think? I tried to look for more info on this function and it says that if you are running it client side you do not need to be a super user. So where/how do you run this?
1 of 1 people found this helpful
Hey Dana. Remote into the host (or one of the hosts) powering your Tableau Server instance. Install a PostgreSQL client like PGAdmin III. Create a new connection to the host running your Repository process, port 8060, user tblwgadmin, with the password being found in the workgroup.yml file at [Server install root]\Tableau Server\data\tabsvc\config (just search for that username). Then try your script again.
If you have Tableau Server installed, PGAdmin III is nested a bit under the bin directory so you can actually skip installing it, I just noticed this recently.
For the primary or single node Server: [Server install root]\Tableau Server\bin\pgsql\bin\pgAdmin3.exe
On workers: [Server install root]\worker\bin\pgsql\bin\pgAdmin3.exe
Note that on the workers, you may have a worker.1 or worker.2 folder, look for the latest modified time in case there are multiple worker folders. These occur sometimes when a worker is being upgraded and something is still holding onto an item in the currently active worker folder.
Thanks for the answers!
I did find the pgadmin application but when I logged in with the readonly user to run the SQL it gave me the error.
I did not have the tblwgadmin password - unless it is a very long alphanumeric jumble.
I did however find some further info on how to build something based on the sql provided into a C# app and then it is client-side export and it works even with the read-only user.
Thank goodness we can get the custom view back
Thanks for your help!
The tblwgadmin password is a long alphanumeric jumble. It is set randomly when you install Tableau Server.
Glad to hear you were able to get it working!
hahaha! I should have tried it.
Had fun with C# though, but thanks for that because it will at some point come in handy for other things as well.
I revisited this recently since we only had a couple of workbooks. The answer Matt gave was great, but still a bit over my head.
I opened pgAdmin III (postgres) and can actually query pg_largeobject, repository data, and custom views. I pasted my query below. I have been very pleased with this, but need to parse out the actual values now from the xml in the 'Details' column. The other thing that I would like is to pull in the captions instead of the parameter names. I thought when I was naming the parameters in the workbook, that would be preserved as the 'names', but apparently not.
distinct repository_data.name as "Repository Data Name",
pg_largeobject.data as "Details",
customized_views.name as "View Name",
_customized_views.workbook_name as "Workbook",
_customized_views.view_name as "View",
_customized_views.user_name as "User Name",
_customized_views.email as "Email",
_customized_views.repository_url as "Repository URL",
customized_views.public as "Public?",
repository_data.content as "Content",
repository_data.created_at as "Custom View Created At"
customized_views.id = _customized_views.id AND
repository_data.id = customized_views.repository_data_id AND
pg_largeobject.loid = repository_data.content;