DbSchema for PostgreSQL Database

DbSchema is a comprehensive tool for designing and managing PostgreSQL databases. It provides powerful features, like visual schema design, team collaboration with Git, easy schema deployment, and interactive HTML5 documentation.

How to Connect to PostgreSQL Database

For written documentation, please continue reading...

Open DbSchema, then click "Connect to Database" on the Main Screen and choose your database system. DbSchema will automatically download the required JDBC driver.

Preparing for Connection in DbSchema

Before starting the connection process in DbSchema, ensure the following conditions are met:

1. The PostgreSQL Server is running: Verify that the database server is operational and ready to accept incoming connections.
2. Firewall Rules : Ensure that the firewall on the server permits traffic through port 5432, which is commonly used for database connections.

I. Configure the Local Connection in DbSchema

• Select 'Standard' from the JDBC URL
• Connecting to PostgreSQL requires entering the localhost port, along with the database username and password you configured during the PostgreSQL server installation.
• Select the database you want to access, then click 'Connect'.

II. Configure the Cloud Connection in DbSchema

• Select 'Edit Manually' from the JDBC URL
• In DbSchema, you can manually edit the JDBC connection for PostgreSQL, which is particularly useful for connecting to cloud databases.
• You can copy the connection URL from your cloud console and, if needed, adjust it to match your specific setup before testing the connection.

III. Configure the Remote Connection in DbSchema

For remote connections, everything starts with providing the correct combination of username and password.

• To allow remote connections, you need to enable TCP/IP connections in the PostgreSQL configuration.
• Select the Remote computer or custom port
• Here, you can enter the IP address or the Hostname (Windows Computer Name) of your remote PostgreSQL Server to connect from your local computer to another remote machine.
• Test the connection (Ping)
• Now, you have insert the database username and password, allowed to connect from any host.
• Select the database you want to access, then click 'Connect'.

If you fail connecting you may need to check one of the items below.

• Enable Remote Connections
• Change User Password
• Start Postgres Service
• Read the Connection Dialog for more details.
• SSL Connections
• Guided Postgres Installation
• Create Docker Image with System-Stats

Enable Remote Connections

Enable Remote Connections in PostgreSQL

Step 1: Edit pg_hba.conf

On the PostgreSQL server, there is a configuration file called pg_hba.conf. This file controls client authentication.

• On Windows, it is located in the installation folder, usually under /data.
• On Linux, it is typically located in /var/lib/pgsql/data/ or /etc/postgresql//main/.

Edit the pg_hba.conf file and append the following configuration line:

host    all             all             172.26.64.1/32          scram-sha-256
    

This allows remote connections from the IP address 172.26.64.1 using the scram-sha-256 authentication method.

Step 2: Restart PostgreSQL

After modifying the pg_hba.conf file, restart PostgreSQL if it's needed to apply the changes:

1. On Windows, use the following command:

pg_ctl.exe -D "C:\Program Files\PostgreSQL\\data" restart
        

2. On Linux, use the following command:

sudo systemctl restart postgresql
        

Ensure that PostgreSQL is restarted for the changes to take effect.

Important Security Note

Allowing connections from all hosts (with 0.0.0.0/0) can expose your server to security risks. It is recommended to specify only the IP addresses or subnets that need access.

Change User Password

ALTER USER <someUser> PASSWORD '<newPassword>';

Start Postgres Service

1. Go to Start -> Control Panel -> System and Security -> Administrative Tools -> Component Services
   
   
2. Open Service Local
3. Find your postgres service name setup during installation (For Example: postgresql-9.3)
4. Right click on the service name and click start.
   
   

Allow Postgres Connect Through Firewall

SSL Connections

    openssl x509 -outform der -in <mypemcertificate>.pem -out <newdercert>.der
    keytool -import -alias <anyname> -keystore <newcertfilename> -file <newdercert>.der
    # Keytool may ask for a password to generate the newcert. The new certificate will be available in c:\backup
    

    -Djavax.net.ssl.trustStore=<Full Path of the newcertfilename that you generated>
    -Djavax.net.ssl.trustStorePassword=<The password you provided to create the netcertfilename>
    -Djavax.net.debug=ssl
    

How to Install PostgreSQL

• Start the setup...
• Choose the Data Directory:
• Choose your password:

• Select the port number or leave it by default (5432):

• Select the components you want to install

Create new database

Create a Postgres Docker Container with System-Stats

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# Use the official PostgreSQL image from the Docker Hub
FROM postgres:latest

# Install dependencies for building the system_stats extension
RUN apt-get update && \
    apt-get install -y build-essential postgresql-server-dev-all

# Update the package list and install git
RUN apt-get update && apt-get install -y git

# Clone the system_stats repository
RUN git clone https://github.com/EnterpriseDB/system_stats /usr/src/system_stats

# Build and install the system_stats extension
RUN cd /usr/src/system_stats && \
    make && \
    make install

# Clean up
RUN apt-get remove -y build-essential postgresql-server-dev-all && \
    apt-get autoremove -y && \
    rm -rf /var/lib/apt/lists/* /usr/src/system_stats

# Set the default command to run PostgreSQL
CMD ["postgres"]
    

1
2

docker build -t postgres-with-system-stats .
docker run --name postgres-with-system-stats -e POSTGRES_PASSWORD=xxx -p 5432:5432 -it postgres-with-system-stats

1

CREATE EXTENSION system_stats;