"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.
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)
Or
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:
#---------------------------------------------------------------------------
# CONNECTIONS AND
AUTHENTICATION
#---------------------------------------------------------------------------
# - 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 127.0.0.1/32, 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 0.0.0.0/0
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 10.1.2.28 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 127.0.0.1/32 md5
host all all 10.1.2.28/32 trust
host all all 0.0.0.0/0 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