Create a PostgreSQL Database as a Stand-Alone Installation
Beginning in version 2021.2, you can host the Tableau Server repository separately as a stand-alone installation. This is different to using a managed cloud service such as AWS RDS, or Azure Database. This configuration can be done on-premises, on AWS EC2 or on an Azure VM. Such an installation of the Tableau Server Repository will be to referred to as stand-alone External Repository.
To see a full list of hosts that you can use for the your external repository, see Tableau Server External Repository.
This topic provides guidance on the requirements and configurations that are necessary for Tableau Server to connect to a PostgreSQL installation and use it as the Tableau Server External Repository. This topic does not provide you with the detailed instructions on how to install PostgreSQL. We recommend that you follow the documentation on the PostgreSQL site for this information.
Requirements and Recommendations
Hardware Recommendations: CPU and storage depend on your requirements. For smaller installations, you should have at least 50 GB of disk storage and a quad processor (or 4 virtual cores) system with 32 gig of RAM. Review the guidance in this topic to calculate the disk space requirements for backup and restore. The general recommendation is to start with more hardware resources and scale back after monitoring.
Networking: The PostgreSQL database instance must be reachable from all nodes in the Tableau Server cluster. One of the ways you can do this is by making the PostgreSQL database instance a member of a security group that has the necessary permissions to be accessed by all the nodes in the Tableau Server cluster.
- Version Compatibility: The version of PostgreSQL should match the version of the Tableau Server Repository when installed locally. For more information about compatibility, see Product Compatibility.
Security: Secure connections between Tableau Server and the External Repository using SSL are not required, but recommended.
But if you do not want to set up secure connections between Tableau Server and External Repository, you should configure the stand-alone PostgreSQL Database to allow unencrypted connections.
Create a stand-alone PostgreSQL Database Instance
Step 1: Install and initialise PostgreSQL
- Use the PostgreSQL documentation to install PostgreSQL database instance to serve as the External Repository for Tableau Server. You may want to set up a PostgreSQL database cluster to meet any high availability requirements you may have.
Install the contrib package that includes the uuid-ossp extension. This module is used to generate the UUIDS that Tableau Server uses for keys in the database.
- Initialise the PostgreSQL instance.
Step 1: Configure your PostgreSQL Instance
You will be using two configuration files to configure your PostgreSQL instance:
pg_hba
: This is the configuration file for host-based authentication.postgresql.conf
: This is the general server configuration file.
By default these files are located here:
/var/lib/pgsql/12/data
(This may be different depending on the distribution)
Super User Settings
Choose a username that meets your requirements. We recommend using postgres as the Administrator username. If you choose to use a different username, make sure that the username does not start with pg. The username also cannot be rails, tblwgadmin, tableau, readonly or tbladminviews.
This is a requirement for the external repository to work properly with Tableau Server.
Network and Security
Make sure that the database instance can be reached by all the Tableau Server nodes. This most often involves creating a security group that allows access from the nodes.
Database Options
The port can be anything, but we recommend leaving it as the default 5432
.
Update Parameters
From the Tableau Server perspective, most of the parameter values for the instance can be set to defaults. You can modify the parameter values if you have specific performance or logging requirements, but we strongly recommend that the following parameters be set to default values and not be changed:
standard_conforming_strings
escape_string_warning
We also recommend the value for the work_mem
be set to at least 16384
to help avoid performance issues.
Configure remote connections
Use the following steps to make updates to the configuration files:
By default, the configuration in the
postgresql.conf
is configured to only listen to local connections. Enable remote connections by making the following changes in the connections and authentication section of thepostgresql.conf
file:Add this line to allow remote connections:
listen_addresses = '*'
- Restart the PostgreSQL instance.
Configure SSL
Secure connections between Tableau Server and the External Repository are not required, but recommended.
To configure encrypted connections between Tableau Server and the External Repository, follow the guidance and the detailed steps described below:
When configuring Tableau Server to use the stand-alone PostgreSQL database instance, you will need to provide a trusted root certificate authority (CA), which is used to verify the connection to the server. Ideally, the stand-alone PostgreSQL instance's server certificate should specify a resolvable hostname so Tableau Server can use sslmode, verify-full. This mode verifies that the PostgreSQL server's certificate was signed by a trusted CA and that the hostname in the PostgresSQL Server's certificate matches the hostname used to connect to the PostgreSQL instance. However, if that is not possible, sslmode, verify-ca will just verify that the Postgres server's certificate was signed by a trusted CA.
The following procedure provides the general steps to generating a root CA certificate on the PostgreSQL Server. For more detailed information, read the SSL documentation on the PostgreSQL website (the link points to version 12):
Generate signing root certificate authority (CA) key.
- Create the root CA certificate.
Create the certificate and related key (for example,
server.csr
andserver.key
) for the PostgreSQL Server. The subject name for the certificate must match the DNS name of the PostgreSQL Server. The subject name is set with the -subj option with the format "/CN=<private DNS name>
".- Sign the new certificate with the CA certificate that you created in step 2.
Copy the crt and key files to the data directory (
/pgsql/<version>/data
).The
pg_hba.conf
file controls the connections to the database. Add the following line to allow remote connections. For example:host all all 10.0.0.0/8 md5
To enable SSL, add or update the
postgresql.conf
file with:ssl = on
To restrict connections to SSL only, use hostssl instead of host.
High Availability and Disaster Recovery
Tableau Server does not manage or set up high availability for the external repository. PostgreSQL database supports several solutions for these purposes including replication and log-shipping. For more information, see the high availability documentation on the PostgreSQL website.
In the event of a disaster, if you need to set up a new PostgreSQL instance, make sure you follow these steps to configure Tableau Server to use the new instance.
Create a new JSON settings file containing connection information for the new RDS instance. For more information on creating a JSON settings file, see Step 1 in Install Tableau Server with External PostgreSQL Repository.
Use the
tsm topology external-services repository replace-host
command to point your Tableau Server to the new PostgreSQL instance.For more information on the
tsm topology external-services repository replace-host
command, see tsm topology.
Who can do this
Only Tableau Server Administrators can configure Tableau Server to use the external repository. If you are using AWS EC2 or Azure VM to set up a stand-alone External Repository, you need to have accounts to access these platforms.