PostgreSQL data into Azure and Power BI
Open-source PostgreSQL data
Yres connects directly to your PostgreSQL database with a host, port (5432 by default), database name and a service account with read access. The connection is always SSL-encrypted; Yres sets that up itself, so you never write a connection string. The connection currently runs on the Azure Data Factory cloud runtime, which means the PostgreSQL server has to be reachable from Azure. The tables you select land in an Azure SQL database in your own tenant, where every change is kept as a new version and Power BI connects.
At a glance
- Connection
- Directly on the database: host, database name, port, user name and password
- Default port
- 5432
- Encryption
- SSL is required; Yres configures this automatically
- Network
- The connection runs on the Azure Data Factory cloud runtime, so the server needs a public endpoint or an opened firewall
- Incremental loading
- Yes, on one or two change columns of the same data type
- History
- Six of the seven load types keep earlier versions of a row (SCD2)
- Connection details
- Server, database, port, user name and password are each held as a separate secret in the Azure Key Vault of your own environment; no credentials sit in pipelines or configuration
What you arrange yourself
- 1Get the host, port and database name from your database administrator; you can also list database names with \l in psql.
- 2Create a service account with read-only access to the database.
- 3Make sure Azure can reach the server on host and port: adjust the firewall or pg_hba.conf where needed so the service account is allowed to connect.
- 4Check that the server accepts SSL connections.
- 5Add the PostgreSQL source in Yres, refresh the metadata and select the tables.
When you need this — and when you don't
A standalone connector is enough if…
If PostgreSQL is the database behind a single application of your own and you want a few overviews on top of it, a direct connection from your reporting tool is usually enough.
Yres adds value if…
Yres earns its place once the data from your web application or SaaS product has to sit next to accounting, CRM or other sources, you want to keep the history the application overwrites, and you no longer want reporting to run on the production database.
Frequently asked questions: PostgreSQL
Does Yres work with a PostgreSQL server inside our own network, behind a closed firewall?
Not at the moment without manual work. The integration runtime you pick in Yres is not yet applied to the PostgreSQL connection: it always runs on the cloud runtime. So the server has to be reachable from Azure. A database behind a closed firewall requires a manual change to the connection in Azure Data Factory.
Is the connection to PostgreSQL encrypted?
Yes. Yres sets up the connection with SSL required. This happens automatically when the source is created, so the PostgreSQL server has to allow SSL connections.
What has to be open on the PostgreSQL side?
Network access from Azure to the host and port of the database, 5432 by default. Depending on your setup that means a firewall rule and a pg_hba.conf entry that lets the service account connect.
What happens when an incremental load fails halfway?
The load position Yres tracks per table does not move forward. That only happens after a load has completed in full. The next successful run automatically fetches the missed part again; there is nothing to repair.
Full technical description in the knowledge base →·Last reviewed:
Data warehouse automation that runs in your own Azure
Built for organisations on Azure and Power BI. Yres connects your sources, keeps the history and promotes changes in a controlled way, without manual work.