How to Connect to PostgreSQL

The connection dialog is explained here.

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 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
• Change User Password
• Start Postgres Service
• Allow Connectivity Through Firewall
• SSL Connections
• Guided Postgres Installation

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
   
   
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.
   
   

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':

How to Install PostgreSQL

Download Postgres from: http://www.postgresql.org/download/

Start the setup...

Choose the Data Directory:

Choose your password:

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

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;