How to Connect PowerBi to Postgres Database [WikiElosys]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== How to Connect PowerBi to Postgres Database ======
Below is a step-by-step documentation on how to export the port of PostgreSQL in Docker Compose, configure IP tables to create an external connection, and then connect Power BI to the Postgres Database:

===== Step 1 : Expose the Port of Postgres =====

  * SSH into your VM where Docker is running.
  * Open your docker-compose.yml file for editing (you can use any text editor you are comfortable with).
  * Find the section for your PostgreSQL service in the docker-compose.yml file and add the ports. It should look something like this:
<code yaml>
db:
    image: postgres
    environment:
      POSTGRES_USER: my_user
      POSTGRES_PASSWORD: my_password
    ports:
      - "5432:5432"
</code>
  * The ports section maps the port 5432 of the container to the same port on the host (VM). By default, PostgreSQL listens on port 5432. Ensure that this port is not already in use on the host.
  * Save and close the docker-compose.yml file.
  * Now, run the following command to apply the changes
<cli>
$ docker-compose up -d
</cli>

===== Step 2: Configure IP Tables =====

To create an external connection, you need to allow traffic to the PostgreSQL port in the IP tables of your VM. Follow these steps:

  * SSH into your Proxmox server where the VM is running
  * Run the following command to allow incoming traffic to the PostgreSQL port (5432):
<cli>
$ iptables -t nat -A PREROUTING -i vmbr0 -p tcp --dport 7*** -j DNAT --to-destination <VM_IP>:5432
</cli>

==== Note ====
  * Replace the Asterisks (***) in 7*** with the last 3 digits of your Vm ip address for technicality 




===== Step 3: Connect Power BI to PostgreSQL =====

Now that you have exported the port and configured IP tables, you can connect Power BI to the PostgreSQL database:

  * Open Power BI on your local machine.
  * Click on "Get Data" from the Home tab.
  * In the "Get Data" window, select "Database" and then "PostgreSQL" from the list.
  * Enter the IP address of your Server in the "Server" field followed by :7* which is the port you chose earlier.It should look like this
   <code> Server_IP:7*** </code>
  * In the "Database" field, enter the name of the database you want to connect to (or leave it blank to connect to the default database).
  * Enter the PostgreSQL username and password you defined in the docker-compose.yml file.
  * Click on "Connect" to establish the connection.
  * Power BI will display a navigator window showing the available tables in the PostgreSQL database. Select the tables you want to import, and click on "Load" to import the data into Power BI.