How to Configure PostgreSQL Workbench, View, and Manage Data in PostgreSQL
Introduction
PostgreSQL Workbench is a robust tool for managing your database and viewing data mapped from your Podio apps. With thatapp.io’s seamless integration, you can configure PostgreSQL Workbench to manage and analyze your synced data effectively. This guide walks you through setting up PostgreSQL Workbench, viewing data, and managing database tables.
Step 1: Download and Install PostgreSQL Workbench
To begin, ensure PostgreSQL Workbench is installed on your computer. If you haven’t:
Visit the PostgreSQL official page to download the installer.
Follow the on-screen prompts to complete the installation.
During installation, ensure that pgAdmin (PostgreSQL’s management tool) is also installed, as it will serve as your workspace.
Step 2: Setup Connection and Mapper on Thatapp
Click on the "Create Connector" button to set up and create your connection. To do this, select the organization, tick the PostgreSQL box, then click next.
View your created connection on the connection table
Next, create the mapper within PostgreSQL by clicking the database icon to access the mappers' page.
Click on the "Create Mapper" to open up the modal. Fill it and hit the create mapper button on the modal to create and proceed to the next page.
Select all the app data fields you want to map to your database. and save.
Step 3: Create a Connection in PostgreSQL Workbench
Open pgAdmin (PostgreSQL’s Workbench equivalent) after installation.
In the left-hand navigation pane, right-click on Servers and select Add New Server.
fill the modal with the connection details on thatapp psotgreSQL connector table
Connection Setup Details:
Before setting up your database connection, click the view icon of the connection table to view the created connection as you will be using the details to configure your PostgreSQL database connection.
Below is an overview of the details:
Name: Enter a unique name for the connection (e.g., "Thatapp PostgreSQL Database").
Connection:
Host Name/Address: Copy the host IP address from your thatapp.io connector details.
Port: Use the default PostgreSQL port 5432, unless otherwise specified.
Maintenance database
Username: Enter the database username provided in your thatapp.io connector details.
Password: Click Save Password to securely store the password from thatapp.io.
Click Save to create the connection. The server will now appear under Servers in the navigation pane.
Step 3: Open Your Connection
Click on the newly created server under Servers to expand it.
Navigate to Databases > your database name to view its structure and contents.
Step 5: Managing Data in PostgreSQL Workbench
Edit Data:
Use the grid view to update data directly by double-clicking on the desired cell.
Press Save to commit your changes to the database.
Save Updates Locally:
After editing or querying data, export it for local storage by selecting Export Data in the context menu.
Save the file in a secure location with a descriptive name.
Inspect Deleted Data:
If "delete sync" was enabled in your mapper, confirm that deleted items in Podio no longer appear in the table.
Pro Tips for Efficient Management
Assign descriptive names to connections for easy identification, especially when managing multiple databases.
Back up your database periodically by selecting Backup in pgAdmin to protect against data loss.