Connect to a PostgreSQL Data Source (SQL Server Import and Export Wizard)

Applies to: SQL Server (all supported versions) SSIS Integration Runtime in Azure Data Factory

This topic shows you how to connect to a PostgreSQL data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard.

The detailed requirements and prerequisites for connecting to a PostgreSQL database are beyond the scope of this Microsoft article. This article assumes that you already have PostgreSQL client software installed and that you can already connect successfully to the target PostgreSQL database. For more info, consult your PostgreSQL database administrator or the PostgreSQL documentation.

Get the PostgreSQL ODBC driver

Install the ODBC driver with Stack Builder

Run Stack Builder to add the PostgreSQL ODBC driver (psqlODBC) to your installation of PostgreSQL.

Or, download the latest ODBC driver

Or, download the Windows installer for the latest version of the PostgreSQL ODBC driver (psqlODBC) directly from this FTP site — https://www.postgresql.org/ftp/odbc/versions/msi/. Extract the files from the .zip file and run the .msi file.

Connect to PostgreSQL with the PostgreSQL ODBC driver (psqlODBC)

ODBC drivers aren’t listed in the drop-down list of data sources. To connect with an ODBC driver, start by selecting the .NET Framework Data Provider for ODBC as the data source on the Choose a Data Source or Choose a Destination page. This provider acts as a wrapper around the ODBC driver.

Here’s the generic screen that you see immediately after selecting the .NET Framework Data Provider for ODBC.

Options to specify (PostgreSQL ODBC driver)

The connection options for this data provider and ODBC driver are the same whether PostgreSQL is your source or your destination. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.

To connect to PostgreSQL with the PostgreSQL ODBC driver, assemble a connection string that includes the following settings and their values. The format of a complete connection string immediately follows the list of settings.

Get help assembling a connection string that’s just right. Or, instead of providing a connection string, provide an existing DSN (data source name) or create a new one. For more info about these options, see Connect to an ODBC Data Source.

Driver
The name of the ODBC driver — either PostgreSQL ODBC Driver(UNICODE) or PostgreSQL ODBC Driver(ANSI).

Server
The name of the PostgreSQL server.

Port
The port to use to connect to the PostgreSQL server.

Database
The name of the PostgreSQL database.

Uid and Pwd
The Uid (user id) and Pwd (password) to connect.

Connection string format

Here’s the format of a typical connection string.

Enter the connection string

Enter the connection string in the ConnectionString field, or enter the DSN name in the Dsn field, on the Choose a Data Source or Choose a Destination page. After you enter the connection string, the wizard parses the string and displays the individual properties and their values in the list.

The following example uses this connection string.

Here’s the screen that you see after entering the connection string.

Other data providers and more info

For info about how to connect to PostgreSQL with a data provider that’s not listed here, see PostgreSQL connection strings. This third-party site also contains more info about the data providers and the connection parameters described on this page.

Подключение к источнику данных PostgreSQL (мастер импорта и экспорта SQL Server) Connect to a PostgreSQL Data Source (SQL Server Import and Export Wizard)

Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Azure-SSIS Integration Runtime в Фабрике данных Azure SSIS Integration Runtime in Azure Data Factory Azure-SSIS Integration Runtime в Фабрике данных Azure SSIS Integration Runtime in Azure Data Factory Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Azure-SSIS Integration Runtime в Фабрике данных Azure SSIS Integration Runtime in Azure Data Factory Azure-SSIS Integration Runtime в Фабрике данных Azure SSIS Integration Runtime in Azure Data Factory

В этом разделе показано, как подключаться к источникам данных PostgreSQL со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server. This topic shows you how to connect to a PostgreSQL data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard.

Подробные требования и необходимые условия для подключения к базе данных PostgreSQL выходят за рамки этой статьи Майкрософт. The detailed requirements and prerequisites for connecting to a PostgreSQL database are beyond the scope of this Microsoft article. В ней предполагается, что у вас уже установлено клиентское программное обеспечение PostgreSQL и вы можете успешно подключиться к целевой базе данных PostgreSQL. This article assumes that you already have PostgreSQL client software installed and that you can already connect successfully to the target PostgreSQL database. Для получения дополнительных сведений обратитесь к администратору базы данных PostgreSQL или к документации по PostgreSQL. For more info, consult your PostgreSQL database administrator or the PostgreSQL documentation.

Получение драйвера ODBC для PostgreSQL Get the PostgreSQL ODBC driver

Установка драйвера ODBC с помощью построителя стека Install the ODBC driver with Stack Builder

Запустите построитель стека, чтобы добавить драйвер ODBC для PostgreSQL (psqlODBC) в вашу установку PostgreSQL. Run Stack Builder to add the PostgreSQL ODBC driver (psqlODBC) to your installation of PostgreSQL.

Скачивание последней версии драйвера ODBC Or, download the latest ODBC driver

Или скачайте установщик Windows для последней версии драйвера ODBC для PostgreSQL (psqlODBC) прямо с этого FTP-сайта — https://www.postgresql.org/ftp/odbc/versions/msi/. Or, download the Windows installer for the latest version of the PostgreSQL ODBC driver (psqlODBC) directly from this FTP site — https://www.postgresql.org/ftp/odbc/versions/msi/. Извлеките содержимое ZIP-файла и запустите файл MSI. Extract the files from the .zip file and run the .msi file.

Подключение к PostgreSQL с помощью драйвера ODBC для PostgreSQL (psqlODBC) Connect to PostgreSQL with the PostgreSQL ODBC driver (psqlODBC)

Драйверы ODBC не приводятся в раскрывающемся списке источников данных. ODBC drivers aren’t listed in the drop-down list of data sources. Чтобы подключиться с помощью драйвера ODBC, сначала выберите поставщик данных .NET Framework для ODBC в качестве источника данных на странице Выбор источника данных или Выбор назначения. To connect with an ODBC driver, start by selecting the .NET Framework Data Provider for ODBC as the data source on the Choose a Data Source or Choose a Destination page. Этот поставщик служит оболочкой для драйвера ODBC. This provider acts as a wrapper around the ODBC driver.

Ниже показан экран, который появляется сразу после выбора поставщика данных .NET Framework для ODBC. Here’s the generic screen that you see immediately after selecting the .NET Framework Data Provider for ODBC.

Указываемые параметры (драйвер ODBC для PostgreSQL) Options to specify (PostgreSQL ODBC driver)

Параметры подключения для этого поставщика данных и драйвера ODBC одинаковы независимо от того, является ли PostgreSQL источником или назначением. The connection options for this data provider and ODBC driver are the same whether PostgreSQL is your source or your destination. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.

Чтобы подключиться к PostgreSQL с помощью драйвера ODBC для PostgreSQL, соберите строку подключения, используя указанные ниже параметры и их значения. To connect to PostgreSQL with the PostgreSQL ODBC driver, assemble a connection string that includes the following settings and their values. Полный формат строки подключения приведен после списка параметров. The format of a complete connection string immediately follows the list of settings.

Вы можете получить помощь в построении строки подключения. Get help assembling a connection string that’s just right. Кроме того, вместо указания строки подключения вы можете предоставить существующее имя DSN (имя источника данных) или создать новое. Or, instead of providing a connection string, provide an existing DSN (data source name) or create a new one. Дополнительные сведения об этих возможностях см. в разделе Подключение к источнику данных ODBC. For more info about these options, see Connect to an ODBC Data Source.

Драйвер Driver
Имя драйвера ODBC — PostgreSQL ODBC Driver(UNICODE) или PostgreSQL ODBC Driver(ANSI). The name of the ODBC driver — either PostgreSQL ODBC Driver(UNICODE) or PostgreSQL ODBC Driver(ANSI).

Server Server
Имя сервера PostgreSQL. The name of the PostgreSQL server.

порт. Port
Порт, используемый для подключения к серверу PostgreSQL. The port to use to connect to the PostgreSQL server.

База данных Database
Имя базы данных PostgreSQL. The name of the PostgreSQL database.

Uid и Pwd Uid and Pwd
Uid (идентификатор пользователя) и Pwd (пароль) для подключения. The Uid (user id) and Pwd (password) to connect.

Формат строки подключения Connection string format

Ниже приведен формат типичной строки подключения. Here’s the format of a typical connection string.

Ввод строки подключения Enter the connection string

Введите строку подключения в поле ConnectionString либо введите имя DSN в поле Dsn на странице Выбор источника данных или Выбор назначения. Enter the connection string in the ConnectionString field, or enter the DSN name in the Dsn field, on the Choose a Data Source or Choose a Destination page. После того как вы введете строку подключения, мастер проанализирует ее и отобразит отдельные свойства и их значения в списке. After you enter the connection string, the wizard parses the string and displays the individual properties and their values in the list.

В приведенном ниже примере используется следующая строка подключения: The following example uses this connection string.

Ниже показан экран, который появляется после ввода строки подключения. Here’s the screen that you see after entering the connection string.

Другие поставщики данных и дополнительные сведения Other data providers and more info

Сведения о подключении к PostgreSQL с помощью поставщика данных, не представленного в этом списке, см. в разделе Строки подключения PostgreSQL. For info about how to connect to PostgreSQL with a data provider that’s not listed here, see PostgreSQL connection strings. Этот сторонний сайт также содержит дополнительные сведения о поставщиках данных и параметрах подключения, описанных на этой странице. This third-party site also contains more info about the data providers and the connection parameters described on this page.

Connect to PostgreSQL Database on Linux, Windows

Connect to PostgreSQL from the command line

Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands. At the time of installing postgres to your operating system, it creates an «initial DB» and starts the postgres server domain running. Typically initdb creates a table named «postgres» owned by user «current logged in user name»

At the command line in your operating system, type the following command.

Debian based systems like Ubuntu :
Connect/login as root —

Redhat based systems like Centos / Fedora :
Connect/login as root —

Windows :
In windows, current user doesn’t matter

After accessing a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands

• To view help for psql commands, type \?.
• To view help for SQL commands, type \h.
• To view information about the current database connection, type \conninfo.
• To list the database’s tables and their respective owners, type \dt.
• To list all of the tables, views, and sequences in the database, type \z.
• To exit the psql program, type \q.

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, sent them to PostgreSQL, and see the query results.

Option	Description
-a —echo-all	Print all nonempty input lines to standard output as they are read. This is equivalent to setting the variable ECHO to all.
-A —no-align	Switches to unaligned output mode.
-c command —command=command	Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and

/.psqlrc) are ignored with this option. -d dbname
—dbname=dbname Secifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line. -e
—echo-queries Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries. -E
—echo-hidden Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the meta-command \i. -F separator
—field-separator=separator Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. -h hostname
—host=hostname Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket. -H
—html Turn on HTML tabular output. This is equivalent to \pset format html or the \H command. -l
—list List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list. -L filename
—log-file=filename Write all query output into file filename, in addition to the normal output destination. -n
—no-readline Do not use Readline for line editing and do not use the command history. This can be useful to turn off tab expansion when cutting and pasting. -o filename
—output=filename Put all query output into file filename. This is equivalent to the command \o. -p port
—port=port Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432. -P assignment
—pset=assignment Specifies printing options, in the style of \pset. Note that here you have to separate name and value with an equal sign instead of space. For example, to set the output format to LaTeX, you could write -P format=latex. -q
—quiet Specifies that psql should do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this happens. This is useful with the -c option. This is equivalent to setting the variable QUIET to on. -R separator
—record-separator=separator Use separator as the record separator for unaligned output. -S
—single-line Runs in single-line mode where a newline terminates an SQL command, as a semicolon does. -t
—tuples-only Turnoff printing of column names and result row count footers, etc. -T table_options
—table-attr=table_options Specifies options to be placed within the HTML table tag. See \pset for details. -U username
—username=username Connect to the database as the user username instead of the default. (You must have permission to do so, of course.) -v assignment
—set=assignment
—variable=assignment Perform a variable assignment, like the \set meta-command. Note that you must separate name and value if any, by an equal sign on the command line. -V
—version Print the psql version and exit. -w
—no-password Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. -W
—password Force psql to prompt for a password before connecting to a database. -x
—expanded Turn on the expanded table formatting mode. -X,
—no-psqlrc Do not read the start-up file. -z
—field-separator-zero Set the field separator for unaligned output to a zero byte. -0
—record-separator-zero Set the record separator for unaligned output to a zero byte. This is useful for interfacing, for example, with xargs -0. -1
—single-transaction When psql executes a script with the -f option, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied. -?
—help Show help about psql command line arguments and exit.

Connect to PostgreSQL database using pgAdmin GUI application

You can also connect to PostgreSQL database using pgAdmin GUI application. Connect to the database at localhost:5432 using the user name postgres and the password supplied.

Clicking on pgAdmin III following screen will come:

Now, double click on PostgreSQL 9.4 under the «Servers Groups». pgAdmin will ask you for a password. You have to supply the password for the postgres user for authentication.

Under the Database(s) on this server section, find the desired database and execute SQL queries: