PostgreSQL Connection

Monday, 21 January 2013

"PostgreSQL ODBC Connection from Windows to Linux" is my very first blog. Hope you all will find it useful. 

This blog will help you to create remote connections to PostgreSQL, as you all might know the PostgreSQL does not allow remote connections by default so it has to be configured accordingly. Please follow the steps mentioned below.

Note: The below scenario we are trying to make a remote connection from Windows to the PostgreSQL server which is Linux based.

Client OS : Windows XP
Host OS(PostgreSQL Server) : Centos 6.3

Setting Up Client Authentication for PostgreSQL

In order to access the PostgreSQL Server through any Ipv4 or Ipv6 connections from the client, we have to disable the firewalls on PostgreSQL server.

service iptables stop

In order to check, if the firewalls are disabled

service iptables status

By default, PostgreSQL does not allow remote connections

To allow the Any Windows server to connect to the PostgreSQL Server remotely, the following steps are required.

The postgresql.conf file is read on by PostgreSQL Server startup daemon when the server receives a sighup, if you edit the file on running system, you have to start up the server for the changes to take place effectively, or use the “pg_ctl reload” option.

Locate the postgresql.conf file and edit it.

Scroll down to the Connection and Authentication section of file

Uncomment the listen_addresses parameter by removing the #

Replace the 'localhost' with the IP address of the Windows / Client Server (please note that the IP address must be encapsulated in single quotes as well)
Replace the 'localhost' with ‘*’ (please note the * denoted all means the PostgreSQL server should listen to any IP and all IP addresses)
After editing and saving the changes the postgresql.conf file (only connection and authentication section) should look like this:


# - Connection Settings –
listen_addresses = '*'          # what IP address(es) to listen on;
                                # comma-separated list of addresses;
                                # defaults to 'localhost', '*' = all
                                # (change requires restart)
port = 5432                     # (change requires restart)
max_connections = 100           # (change requires restart)

Client authentication is controlled by a Host based authentication configuration file, which is named pg_hba.conf and is stored in the PostgreSQL database cluster's data directory. A default pg_hba.conf file is installed when the data directory is initialized by initdb command. It is possible to place the authentication configuration file elsewhere.

Each record/row specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication.

Note : Before editing the pg_hba.conf file, please read it thoroughly, It is very important to understand the attribute values that we are going to place in this file.

The pg_hba.conf file is located on this default path.

Locate the pg_hba.conf file and edit it. 
Scroll down to Ipv4 connections section.

If you are accessing the PostgreSQL Server database from a system that is not running PostgreSQL Server, you will need to add your computer’s IP address to the pg_hba.conf.templ file on the PostgreSQL Server machine. The following procedure will allow you to make persisted configuration changes to PostgreSQL Server.

Add the IP address of the Windows Server under the IPv4 Local Connections section.
Follow the same pattern as the host with, use spaces to separate entries on the columns, with the same number of spaces matching each underlying row.  Enter the IP address as x.x.x.x/32.

Note :- If we are unsure of the IP address of the client, if the IP address is dynamic in nature, we can set one more record in pg_hba.conf file under the IPv4 section using as the IP address field value. This tells PostgreSQL server to accept connection from any of the IP addresses.

For instance, using a remote Windows Server with an IP address of as an example,
the IPv4 section should appear as follows once the changes to the pg_hba.conf have been completed and saved:

# IPv4 local connections:
host    all         all          md5
host    all         all          trust
host    all         all             trust

Restart the PostgreSQL Service

service PostgreSQL restart
Stopping PostgreSQL service:                                  [  OK  ]
Starting PostgreSQL service:                                  [  OK  ]

Now you are all set to integrate any Windows tool supporting ODBC to the PostgreSQL server side.

Windows 32-bit and 64-bit client drivers are installed as separate libraries. On a 32-bit Windows operating system, you can install the 32-bit client driver only. On a 64-bit Windows operating system, both the 32-bit and 64-bit client drivers can be installed.
 Link for the ODBC Driver.
Make sure you download the ODBC driver according to the Postgres Version you are using.
To install the ODBC client driver:
1.   Assuming that you have downloaded the package from the given link, the zipped installer consists of a zipped package containing a standalone installation application. To install from this package, unzip the installer, and then run the installation application (msi).
2.   Make proper choices when the installation asks you for the 64 bit / 32 bit installation options.
3.   Once finished, The PostgreSQL driver is installed on your windows system.

To set up your PostgreSQL ODBC driver, follow the steps below:
1.    Select the Windows Control Panel >> Administrative Tools >> Data Sources (ODBC) option. The ODBC Data Sources Administrator window displays.

2.   Click on the Add button. The Create New Data Source dialog displays, enabling you to add a new DSN.
3.    Select PostgreSQL (Postgres Unicode) Driver from the list.
4.    Click on the Finish button.
5.    Enter the following configuration details:

  • A name for the  PostgreSQL connection e.g. : “PostgreSQL-Connect”
  • The actual name of the PostgreSQL database you want to get connected.
  • Description (optional)
  • The host address of the PostgreSQL server.
  • User name and password for the PostgreSQL Database connected to.
6.    Once all the data is entered, click Test. You should be successful. Then click Save. You are now ready to use your ODBC connection!

No comments:

Post a Comment

Follow by Email