PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.
Prerequisites
Step 1: Whitelist Locale’s IP Addresses
Step 2: Create a User and Grant Privileges
Step 3: Retrieve the Hostname and Port Number
Step 4: Configure Postgres as a Data Connection on Locale
1. Creating a Database User and Granting Privileges
Create a database user
If you want to use an existing user with Locale, you can skip these steps, However, It is recommended to create a new user for the locale with only the necessary permissions.
Perform the following steps to create the login and database users in your Postgres database:
Step 1: Log in to your Postgres instance using root or any other superuser.
sudo -u postgres psql
Step 2: Enter the following command to create a user, so that locale can authenticate using a password.
CREATE USER 'localeuser' WITH PASSWORD 'password';
2. Grant privileges to the user
Perform the following steps to grant the required privileges to the database user, to read the database objects and schema:
Step 1: Log in to your Postgres Server instance using an SQL client of your choice as a root user/user with grant privileges.
Step 2: Use the following SQL queries to grant privileges to the database
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
Alternatively, you can use this query to grant only SELECT privilege on all databases and tables
GRANT SELECT *.* ON DATABASE database_name TO username;
💡 This will create a read-only user which is ideal for Locale’s use case.
3. Configure Postgres as a Data Connection on Locale
Step 1: Log in to your locale command center and click on the Data Connections option under the ⚙️ Org Settings, on the left-hand side menu.
Step 2: Select Postgres from the available options.
Step 3: Enter your database details
Name the data source→ A unique name to identify the data source. You could have multiple Postgres databases/clusters connecting to Locale so you can use this name to uniquely differentiate between each of them within Locale’s platform.Host Address→ The Domain address/IP Address of your database.Port→ The port on which the database accepts connections (The default value for MySQL is 5432).Database Name→ Postgres connections are bound to the database. Enter the database name to which the connection needs to be made. As mentioned already, you can create new connections for different databases within the same cluster/new cluster.Username→ Database Username.Password→ Password for the mentioned database user.Use SSL→ Check this option if you want the connection to be encrypted over SSL.
Step 4: Once you have entered all of the details, click the Test Connection button to validate the details entered and to make a test database connection.
Note: Details are greyed out for security reasons.
⚠️ You will not be able to save the connection unless the connection test is successful.
Any error that comes while connecting with your database is displayed as an error text indicated above. Please contact Locale support to have one of our support administrators help you with setting this up. Alternatively, you can also reach out to your Database administrator for further troubleshooting.
If your database is behind a firewall, then you can whitelist the mentioned IP on the connections page for whitelisting. This will ensure that Locale can connect to your database seamlessly.
Step 5: Once you have saved the connection, it will be visible on the Data Connections page and can be used in setting up alerts.
Note : Locale will cancel queries that run for more than 90 seconds. This is to protect your database from running rogue queries and also to prevent a backlog of alerts in the Locale system. If you have a use case where this needs to be increased then please get in touch with our Support.
Happy Alerting 🙌🏻
Blazing-fast operations minus all the constant firefighting👨🚒Learn how to set up a control tower for your operations in under 15 minutes ️🔥
...with Locale for modern ops teams |