DbSchema for PostgreSQL Database

DbSchema is a powerful database management and design tool for PostgreSQL. It offers features such as visual schema design, team collaboration with GIT, schema deployment and HTML schema documentation.

How to Connect to PostgreSQL Database

  1. Access the Connection Dialog

    Choose "Connect to the database" or "New Model Connected to the Database" will let you select your database and open the Connection Dialog.

    DbSchema automatically downloads the JDBC driver to connect to your database.

  2. Configure the Connection

    3. In the Connection Dialog window, select the JDBC URL you want to connect with. If your database is running in the cloud, choose Edit Manually and insert the JDBC URL from the cloud console.

    Enter the host name, the authentication details, select your database as described in the Connection Dialog page.

  3. Troubleshoot Firewall Issues
    4. If you encounter connection issues, it may be due to firewall settings. Read how to enable Firewall Connections.
Remember to enable Windows Firewalls Firewalls & anti-viruses may block database connections.

Connect to a Cloud Database

If the database is running in the cloud (AWS, Azure, Google, etc. ) choose 'Manually Edit JDBC URL' and copy the JDBC URL from the cloud console.

Connect to PostgreSQL in the Cloud

Connect to a PostgreSQL Server

You can connect to Postgres using the user 'postgres'. The default port is 5432. If you connect from a remote computer you have to enable the access from remote computers.

Check the following if you cannot connect to the database:

Enable Remote Connections

Step 1: Edit pg_hba.conf

On Postgres server there is a file called pg_hba.conf. On Windows it is located in the installation folder /data, on linux in /var/lib/pgsql/data/. Edit the pg_hba.conf file and append the following configuration line, replacing 10.10.29.0 with your client machine IP : 

host all all 10.10.29.0/24 trust
More simple, you can allow all hosts to connect by adding this line: 
    host  all  all 0.0.0.0/0 md5

Then restart the server. For this you may use the command below (replace the path to data folder).

pg_ctl.exe -D <path_to_data_folder> restart

Step 2 : Edit postgres.conf

To enable listening for all hosts, edit the file postgresql.conf ( on Windows located in Postgres installation folder /data, on linux in /var/lib/pgsql/data/ ), find configuration line that read as follows: listen_addresses='localhost'. Change it to listen_addresses='*' Eventually you can change this to your IP address. For example listen_addresses='202.54.1.3'

Search on google for 'postgresql enable remote access' to find further documentation.

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

Open Windows Firewall by clicking the Start button Picture of the Start button, and then clicking Control Panel. In the search box, type firewall, and then click Windows Firewall.
In the left pane, click Advanced settings. Administrator permission required If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
In the Windows Firewall with Advanced Security dialog box, in the left pane, click Inbound Rules, and then, in the right pane, click New Rule.
Follow the instructions in the New Inbound Rule wizard. Make sure to select Add Port.
For the Port name use Postgres.
For the specific local port use 5432
Make sure to check off Allow the Connection
For When does this rule apply?
Put a check in Domain, private, and public.
Make sure to follow the directions above for creating an Outbound rule...basically the same way except you click Outbound Rules on the left pane.

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 from: http://www.postgresql.org/download/

Start the setup...

Choose the Data Directory:

Choose PostgreSQL Data Directory

Choose your password:

Choose the Postgres password

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

Enter the Postgres port

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;