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

Watch the step-by-step video tutorial below:

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. Connect to Database in DbSchema

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'.
Local Connection in PostgreSQL

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.
Cloud Connection in PostgreSQL

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'.
Remote Connection in PostgreSQL

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

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.

Remote Connection in PostgreSQL

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:
    2. pg_ctl.exe -D "C:\Program Files\PostgreSQL\\data" restart
  2. On Linux, use the following command:
    3. 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

You may change a user password from psql using the statement below. The default password is the one you set during Postgres installation. 
ALTER USER <someUser> PASSWORD '<newPassword>';

Start Postgres Service

If Postgres service is not running, you won't be able to connect.

Windows
To enable Postgres Service follow this steps:
  1. Go to Start -> Control Panel -> System and Security -> Administrative Tools -> Component Services
    How to configure Postgres 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.
    How to start PostgreSQL services

Linux
To start the postgres service on linux just type in the console: service postgres start

Allow Postgres Connect Through Firewall

Firewall Rules : Ensure that the firewall on the server permits traffic through port 5432, which is commonly used for database connections.

SSL Connections

The certificate you download from AWS is usually in PEM format. You can convert it using: 
    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
Edit the DbSchema.vmoptions (located in the same folder as DbSchema.exe or ./DbSchema.app/Contents/vmoptions.txt on Mac OS) and add this parameters: 
    -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
In the connection dialog choose 'Use SSL':
Connect to PostgreSQL using SSL

How to Install PostgreSQL

Download Postgres
  • Start the setup...
  • Choose the Data Directory:
  • Choose your password:
Choose the Postgres password
  • Select the port number or leave it by default (5432):
Enter the Postgres port
  • Select the components you want to install
Select the components

Create new database

su - postgres
// set all to trust
edit /etc/postgres/8.2/main/postgres.sql
pg_ctlcluster 8.2 main restart
or
/etc/init.d/postgres restart
psql -d template1
create database dbtst;
drop database dbtst;

Create a Postgres Docker Container with System-Stats

Use this code to create a docker container with Postgres and System Stats. Save this code snippet to a file Dockerfile without any extension. 
# 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"]
From command prompt or terminal, go into this directory and execute: 
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
Connect with DbSchema to the docker container as user postgres using the password from the command above, port 5432. Execute from DbSchema SQL Editor: 
CREATE EXTENSION system_stats;