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 from 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 PosgreSQL 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 is not required, 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 initialize 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.
- Initialize the PostgreSQL instance.
Step 1: Configure your PostgreSQL Instance
You will be using two configuration files to configure your PosgreSQL instance:
- pg_hba : This is the general server configuration file.
- postgresql.conf: This is the configuration file for host-based authentication.
By default they are located at:
/var/lib/pgsql/12/data (This may be different depending on the distribution)
Super User Settings
Choose a user name that meets your requirements. We recommend using postgres as the Administrator user name. If you choose to use a different user name, make sure that the user name does not start with pg. The user name 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.
The port can be anything, but we recommend leaving it as the default 5432.
Update the PostgreSQL configuration in the posgresql.conf file with Tableau recommended values shown below:
|Name||Data type||Parameter Type||Recommended Tableau Configuration Values||Description|
|autovacuum||boolean||Dynamic||1||Starts the autovacuum subprocess.|
|client_min_messages||string||Dynamic||error||Sets the message levels that are sent to the client.|
|escape_string_warning||boolean||Dynamic||0||Warns about backslash (\) escapes in ordinary string literals.|
|lc_monetary||string||Dynamic||en_US.UTF-8||Sets the locale for formatting monetary amounts.|
|lc_numeric||string||Dynamic||en_US.UTF-8||Sets the locale for formatting numbers.|
|log_autovacuum_min_duration||integer||Dynamic||100||Sets the minimum execution time above which autovacuum actions will be logged.|
|log_connections||boolean||Dynamic||1||Logs each successful connection.|
|log_lock_waits||boolean||Dynamic||1||Logs long lock waits.|
|log_min_duration_statement||integer||Dynamic||100||Sets the minimum execution time above which statements will be logged.|
|log_min_messages||string||Dynamic||error||Sets the message levels that are logged.|
|log_temp_files||integer||Dynamic||128||Logs the use of temporary files larger than this number of kilobytes.|
|standard_conforming_strings||boolean||Dynamic||0||Causes ... strings to treat backslashes literally.|
|work_mem||integer||Dynamic||16384||Sets the maximum memory to be used for query workspaces.|
Configure remote connections
Use the following steps to make updates to the configuration files:
By default, the configuration in the posgresql.conf is configured to only listen to local connections. Enable remote connections by making the following changes in the connections and authentication section of the posgresql.conf file:
Add this line to allow remote connections:
listen_addresses = '*'
- Restart the PosgreSQL instance.
Secure connections between Tableau Server and the External Repository is 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 PosgreSQL Server. For more detailed information, read the SSL documentation on the PosgreSQL 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 and server.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 (
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 posgresql.conf file with:
ssl = on
To restrict connections to only SSL only, use hostssl instead of host.
High Availability and Disaster Recovery
Tableau Server does not manage or setup high availability for the external repository. PosgreSQL 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 setup a new PostgreSQL instance, make sure to 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.
tsm topology external-services repository replace-hostcommand to point your Tableau Server to the new PosgreSQL instance.
For more information on the
tsm topology external-services repository replace-hostcommand, see tsm topology.
Only Tableau Server Administrators can configure Tableau Server to use the external repository. If you are using AWS EC2 or Azure VM to setup a stand-alone External Repository, you need to have accounts to access these platforms.