Как установить драйвер postgresql на windows

PostgreSQL is an Object-Relational Database Management System that focuses on SQL compliance and extensibility. This Open-Source Relational Database supports both JSON & SQL querying and serves as the primary data source for numerous mobile, web, geospatial, and analytics applications. Oracle has established itself as one of the largest vendors of RDBMS (Relational Database Management System) in the IT market since its inception. The query language that can be used to access data in Oracle’s relational databases is SQL.

This blog talks about the steps you can follow to configure the PostgreSQL ODBC Driver connection seamlessly. It also gives a brief introduction to ODBC Drivers and PostgreSQL before diving into the steps involved in PostgreSQL ODBC Driver Configuration.

Table of Contents

• What is PostgreSQL?
  • Key Features of PostgreSQL
• What are ODBC Drivers?
• What is the Difference between ODBC & JDBC Drivers?
• What is the PostgreSQL PSQL ODBC Driver?
• How to Connect with PostgreSQL using ODBC Drivers?
  • Step 1: Download the PostgreSQL ODBC Driver for PostgreSQL
  • Step 2: Install the PostgreSQL ODBC Driver
  • Step 3: Use System DSN to Setup the psqlODBCx64 Driver
  • Step 4: Connect and Verify PostgreSQL ODBC Connection
• How to Fix Common Errors with PostgreSQL ODBC Connections?
  • Working with Integrated Security
  • Fixing Connection Configurations
  • Fixing Wrong Port Number Issue
• Conclusion

What is PostgreSQL?

PostgreSQL is widely known for its Open-Source platform that supports all features of RDBMS. It also provides an array of features on top of RDBMS features like indexes, stored procedures, views, triggers, and atomicity features to name a few.

PostgreSQL runs on various major platforms like Windows, Linux, macOS, and UNIX to name a few. Concurrency is managed by PostgreSQL through Multi-Version Concurrency Control (MVCC). MVCC provides each transaction a “Database Snapshot” that allows changes to be made without impacting other transactions. This ensures the maintenance of ACID principles while doing away with the need to read locks.

Key Features of PostgreSQL

• Customizable: PostgreSQL can be customized by developing plugins to make the Database Management System fit your requirements. PostgreSQL also allows you to incorporate custom functions that are made with other programming languages like Java, C, C++, etc.
• Long History: PostgreSQL has been around for more than 30 years, having been initially released in 1988.
• Frequent Updates: The most recent PostgreSQL update was Version 13.1 on 12 November 2020.  
• MVCC Features: PostgreSQL happens to be the first Database Management System to implement Multi-Version Concurrency Control (MVCC) features.
• A Supportive Community: A dedicated community is always at your disposal. Private, third-party support services are available as well. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.
• Open-Source: This is an Object-Relational Database Management System(ORDBMS). This allows PostgreSQL to provide Object-Oriented and Relational Database functionality. PostgreSQL is a free and Open-Source ORDBMS.
• Users: PostgreSQL users include Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, Yahoo to name a few.

What are ODBC Drivers?

ODBC Drivers leverage the Open Database Connectivity Interface offered by Microsoft. Subsequently, it allows applications to extract data in Database Management Systems (DBMS) by utilizing SQL as a standard for extracting the data. ODBC offers maximum compatibility, which means a single application can link to various DBMS. After this, application users can add ODBC Database drivers to connect to the application of their choice of DBMS.

Here is what the ODBC Driver Interface describes:

• A customary way to link and log in to a DBMS.
• A customary set of error codes.
• SQL syntax based on the SQL Access Group SQL CAE Specification and X/Open.
• A customary way representation of data types.
• There are various types of ODBC function calls in its library:
  • Call Level Interface Specification.
  • Core functions based on the SQL Access Group and the X/Open.
  • Extended functions that provide support for supplementary functionalities, including scrollable cursors.

Thus ODBC supplies a customary medium to allow vendors of database drivers and application developers to switch data between data sources and applications.

What is the Difference between ODBC & JDBC Drivers?

You can only leverage JDBC Drivers to work with Java languages whereas ODBC Drivers can be used for any language like Java, C, C++, etc. JDBC Drivers are typically object-oriented whereas ODBC Drivers are procedural. It is not suggested to leverage ODBC Drivers for Java applications since it can end up hindering the performance due to internal conversions. This would lead to the applications becoming platform dependent which hurts the efficiency. So, it is suggested that you leverage a JDBC Driver for Java applications since these issues aren’t encountered while utilizing a JDBC Driver.

Apart from this, the code for JDBC is fairly simple to learn and can be run easily. In contrast, the code for ODBC Drivers is pretty hard to master and is complex to run as well.  

What is the PostgreSQL PSQL ODBC Driver?

PostgreSQL PSQL ODBC Driver is the official PostgreSQL ODBC Driver that allows data access similar to other vendor-specific drivers. Once you’ve written an ODBC application, you can easily connect to any specified back-end database, irrespective of the vendor, as long as the database schema remains intact with PostgreSQL ODBC Driver.

For instance, you could have PostgreSQL and MS SQL Servers that store the same data. By leveraging PostgreSQL ODBC Driver, you empower your Windows to make the same calls while the back-end data source resembles the one you have on hand, from the viewpoint of the Windows application.   

How to Connect with PostgreSQL using ODBC Drivers?

Here are the steps involved in configuring PostgreSQL ODBC Driver connection:

• Step 1: Download the PostgreSQL ODBC Driver for PostgreSQL
• Step 2: Install the PostgreSQL ODBC Driver
• Step 3: Use System DSN to Setup the psqlODBCx64 Driver
• Step 4: Connect and Verify PostgreSQL ODBC Connection

Step 1: Download the PostgreSQL ODBC Driver for PostgreSQL

• In this example, it is recommended that you use connection objects to connect to databases. First, you need to create a new channel to try out the PostgreSQL ODBC Driver code with the following credentials:
  • Source: From Translator
  • Name: DB PostgreSQL Connection
  • Destination: To Channel
• You can even leverage an existing channel that would require a Filter component or a From/To translator.
• Next, you need to connect to a database through ODBC. To do this, you can paste the following code snippet in the Translator:

function main()
     local conn = db.connect{  
     api=db.POSTGRES,
     name='your_odbc_server_name',
     user='your_login',
     password='secret',
     use_unicode = true,
     live = true
  }
 
  conn:execute{sql='SELECT * FROM <your table>', live=true}
end

• Here, the Name refers to the name of an ODBC source that can be created via the ODBC Administrator. You can finally adapt the code to your specific requirements.

Step 2: Install the PostgreSQL ODBC Driver

• To set up a new ODBC data source for PostgreSQL, you first need to install the latest PostgreSQL ODBC Drivers. You can start by downloading the latest 64-bit driver installer from the psqlodbc download site.
• Next, you can unzip the file and run the MSI installer to accept the default conditions.

• Next, double-click on the MSI file to install the driver. When you run the MSI file, the psqlODBC_x64 driver setup wizard kicks into motion. On the first screen, you can view the details as follows:

• When you reach the End-User Agreement window, you can look at the license details and agreement. Review the license terms and click on I accept the terms within the License Agreement. You can then print the agreement by clicking on the Print button.

• On the Custom Setup screen, you can choose the feature of the drivers. Within the ODBC Driver Setup, you can view the driver under the psqlODBC_x64 tree. You can take a look at the disk usage of the driver. If you wish to install the documentation, click on the button under the psqlODBC_x64 tree and choose the entire feature installed on the disk option as follows:

• When you’ve reached the ready to install screen, click on the Install button. If you wish to change or review the installation settings, click on the Back button.

• When you’ve completed the installation, click on Finish to close the Wizard.

Step 3: Use System DSN to Setup the psqlODBCx64 Driver

• Now, to configure the ODBC Data Source for the PostgreSQL database, open ODBC Data Source (64-bit) followed by clicking on the System DSN tab. You can click on Add button to move on to the next segment.

• A dialog box Create a New Data Source opens up. Choose the PostgreSQL Unicode (x64) driver and click on the Finish button to move ahead.

• Another dialog box PostgreSQL Unicode ODBC Driver Setup is opened. Within the dialog box, you need to specify various parameters like Description, Database, Data Source, Port, Server, SSL Mode, Username, and Password. After you’ve configured all the parameters, the configuration setup will resemble the following image:

Step 4: Connect and Verify PostgreSQL ODBC Connection

• After you’ve completed the PostgreSQL ODBC Driver setup, you can click on Test to verify the connectivity:

• As is evident from the previous window, the connection has been established successfully. Click on the Save button to create the system DSN. When you move back to the System DSN screen, you can view the pgadmin13 DSN has been generated as follows:

How to Fix Common Errors with PostgreSQL ODBC Connections?

Here are a few ways that can allow you to troubleshoot the common errors you might encounter when setting up a PostgreSQL ODBC Driver connection:

Working with Integrated Security

You can leverage integrated security to connect to a Microsoft SQL Server database. If this does not work, you can have a word with your DBA.

Fixing Connection Configurations

To fix connection configurations, you can have a word with your DBA (Database Administrator). These issues include problems with the username, password, Database Server Network Name, among others.

Fixing Wrong Port Number Issue

SQL Server generally works on Port 5432, but you are free to work on a different port of your choosing. You can speak with your Network Administrator or DBA for the same.

Conclusion

This blog discusses the steps you can follow to set up a PostgreSQL ODBC Driver connection seamlessly. It also covers the basics of PostgreSQL and ODBC Drivers and how they differ from JDBC Drivers. It also covers the different remedies to common errors that you can encounter while trying to establish PostgreSQL ODBC Connections.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like PostgreSQL into your Data Warehouse or a destination like PostgreSQL to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources such as PostgreSQL to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first handYou can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

This article will guide you to install PostgreSQL ODBC Driver 64bit on Windows 10. Some software rely on ODBC in order to communicate with the PostgreSQL server. The PostgreSQL driver does not available on Windows. So, we need to download and install it manually. There are few steps to do this.

Steps to Install PostgreSQL ODBC Driver 64 bit on Windows 10

First, download the driver from the link below. Choose the version and architecture you want to install. In this example, I am going to install the 64bit edition.

https://www.postgresql.org/ftp/odbc/versions/msi/

I choose to install the latest version 12.01. I got the following file: psqlodbc_12_01_0000-x64.zip.

Next, extract the file and then double click the .exe file. The installation wizard will open up. Follow the steps until finish.

Create a new Data Source

After we install the driver, we need to create a new data source. To create a new data source, follow these steps.

At this points, we have successfully installed the PostgreSQL ODBC Driver and create a new ODBC connection on Windows 10.

PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.

Установка PostgreSQL 11 в Windows 10

Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.

В процессе установки установите галочки на пунктах:

• PostgreSQL Server – сам сервер СУБД
• PgAdmin 4 – визуальный редактор SQL
• Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
• Command Line Tools – инструменты командной строки

Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).

По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.

Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.

Доступ к PostgreSQL по сети, правила файерволла

Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.

Запустите командную строку от имени администратора. Введите команду:

netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432

• Где rule name – имя правила
• Localport – разрешенный порт

Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:

New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.

Утилиты управления PostgreSQL через командную строку

Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.

• Запустите командную строку.

  Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
  chcp 1251

• Перейдите в каталог bin выполнив команду:
  CD C:Program FilesPostgreSQL11bin

Основные команды PostgreSQL:

PgAdmin: Визуальный редактор для PostgresSQL

Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.

По умолчанию все созданные базы хранятся в каталоге base по пути C:Program FilesPostgreSQL11database.

Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.

Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.

Query Tool: использование SQL запросов в PostgreSQL

Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.

• Выберите базу данных, в панели Tools откройте Query Tool
• Создадим таблицу сотрудников:

CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.

После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.

Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.

Здесь вы можете заполнить данные в таблице.

После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;