PostgreSQL
Connect Semaphor to your PostgreSQL database
Connecting to PostgreSQL
To connect Semaphor to your PostgreSQL database, you need to use a connection string in the following format:
Connection String Format
username– Your database username.password– The password for the specified user.server– The hostname or IP address of your PostgreSQL server.port– The port on which PostgreSQL is running (default: 5432).db– The name of the database you want to connect to.
Example Connection String
If your database username is admin, password is mypassword, hosted on db.example.com using the default PostgreSQL port (5432), and the database name is analytics_db, your connection string would be:
Prerequisites
To successfully connect Semaphor to your PostgreSQL database, ensure the following requirements are met:
Database Permissions
- The user account must have read-only access to the required database.
- The user must have the ability to create session-specific temp tables.
- The user must have access to query
INFORMATION_SCHEMAtables for metadata population.
Network Access
- If your database is private, whitelist the Semaphor IP:
44.199.53.142to allow connections. - Ensure that firewall rules and security group settings permit network access for Semaphor.
Verifying Information Schema Access
Semaphor requires access to INFORMATION_SCHEMA tables to populate database metadata. To verify that your PostgreSQL user has the necessary permissions, run the following diagnostic queries:
For Tables and Views:
For Schemas:
Replace the placeholders:
your-db-name– The name of your database (e.g.,analytics_db)your-schema-name– The schema you want to query (e.g.,reporting)
Expected Results:
- The tables query should return a list of tables and views in your specified database and schema
- The schemas query should return a list of available schemas in your database
- If either query fails or returns no results when you expect data to exist, the Semaphor user likely lacks the required
INFORMATION_SCHEMApermissions
Troubleshooting
If you encounter issues connecting to PostgreSQL, consider the following:
- Invalid Credentials: Verify your username and password.
- Network Restrictions: Ensure your firewall or security group allows connections to the PostgreSQL server.
- Incorrect Connection String: Double-check the format of your connection string.
- Database Permissions: Ensure your user has the required privileges to access the database.
- Metadata Population Issues: If Semaphor cannot display database tables or schemas, verify that your user can query
INFORMATION_SCHEMAtables using the diagnostic queries above.