A PostgreSQL Interface for AlloyDB Data



Use the SQL Gateway and AlloyDB ODBC Driver to create a PostgreSQL entry-point for data access.

There are a vast number of PostgreSQL clients available on the Internet. From standard Drivers to BI and Analytics tools, PostgreSQL is a popular interface for data access. Using the SQL Gateway included in our ODBC Drivers, you can now create PostgreSQL entry-points that you can connect to from any standard client.

To access AlloyDB data as a PostgreSQL database on Windows, use the CData SQL Gateway, the ODBC Driver for AlloyDB, and the MySQL foreign data wrapper from EnterpriseDB. In this article, we compile the foreign data wrapper in Visual Studio, install it as an extension, and query AlloyDB data from PostgreSQL Server.

Configure the Connection to AlloyDB

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

The following connection properties are usually required in order to connect to AlloyDB.

  • Server: The host name or IP of the server hosting the AlloyDB database.
  • User: The user which will be used to authenticate with the AlloyDB server.
  • Password: The password which will be used to authenticate with the AlloyDB server.

You can also optionally set the following:

  • Database: The database to connect to when connecting to the AlloyDB Server. If this is not set, the user's default database will be used.
  • Port: The port of the server hosting the AlloyDB database. This property is set to 5432 by default.

Authenticating with Standard Authentication

Standard authentication (using the user/password combination supplied earlier) is the default form of authentication.

No further action is required to leverage Standard Authentication to connect.

Authenticating with pg_hba.conf Auth Schemes

There are additional methods of authentication available which must be enabled in the pg_hba.conf file on the AlloyDB server.

Find instructions about authentication setup on the AlloyDB Server here.

Authenticating with MD5 Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.

Authenticating with SASL Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.

Authenticating with Kerberos

The authentication with Kerberos is initiated by AlloyDB Server when the ∏ is trying to connect to it. You should set up Kerberos on the AlloyDB Server to activate this authentication method. Once you have Kerberos authentication set up on the AlloyDB Server, see the Kerberos section of the help documentation for details on how to authenticate with Kerberos.

Start the Remoting Service

The MySQL remoting service is a daemon process that listens for clients' incoming MySQL connections. See the setup guide in the SQL Gateway overview to configure the MySQL Remoting service in the CData SQL Gateway.

Build the MySQL Foreign Data Wrapper

The Foreign Data Wrapper can be installed as an extension to PostgreSQL, without recompiling PostgreSQL. If you are running PostgreSQL on a Unix-based system, you can use the PostgreSQL Extension Network (PGXN) to install the FDW, mysql_fdw. If you are running PostgreSQL on Windows, compile the extension to ensure that you are working with the latest version. Follow the steps below to make the necessary modifications to build the extension from Visual Studio:

Obtain Prerequisites

To build the foreign data wrapper, do the following:

  • Install PostgreSQL. This example uses an installation of PostgreSQL 9.4.
  • If you are using a 64-bit installation of PostgreSQL, obtain libintl.h from the PostgreSQL source. The 64-bit PostgreSQL installer does not currently include libintl.h.
  • Obtain the source for the mysql_fdw foreign data wrapper from EnterpriseDB.
  • Install MySQL Connector C. This example uses an installation of MySQL Connector C 6.1.

Configure a Project

After you have obtained the necessary software and source code, you are ready to compile the extension with Visual Studio. Follow the steps below to create a project using the mysql_fdw source:

  1. In Visual Studio, create a new empty C++ project.
  2. In Solution Explorer, right-click Source Files and click Add -> Existing Item. In the file explorer, select all of the .c and .h files from the mysql_fdw source.

Follow the steps below to configure your project:

  1. If you are building for a 64-bit system, click Build -> Configuration Manager and in Active Solution Platform select x64.
  2. Right-click your project and click Properties.
  3. In the Configuration menu, select All Configurations.
  4. In Configuration Properties -> General -> Configuration Type, select Dynamic Library.
  5. In Configuration Properties -> C/C++ -> Code Generation -> Enable C++ Exceptions, select No.
  6. In Configuration Properties -> C/C++ -> Advanced -> Compile As, select Compile as C Code.
  7. In Linker -> Manifest File -> Generate Manifest, select No.

Follow the steps below to add the required dependencies:

  1. In Linker -> Input -> Additional Dependencies, select Edit and enter the following: postgres.lib libmysql.lib WS2_32.lib Secur32.lib Additionally, ensure that Inherit From Parent or Project Defaults is checked.
  2. In Linker -> General -> Additional Library Directories, select Edit and add the path to the lib folder in your PostgreSQL installation.
  3. In Linker -> General -> Link Library Dependencies, select No.
  4. To complete the configuration of your project, add the necessary includes: In C/C++ -> General -> Additional Include Directories, add the following folders in the following order: MyMySQLConnectorCInstallation\include MyPostgreSQLInstallation\MyPostgreSQLVersion\include\server\port\win32_msvc MyPostgreSQLInstallation\MyPostgreSQLVersion\include\server\port\win32 MyPostgreSQLInstallation\MyPostgreSQLVersion\include\server MyPostgreSQLInstallation\MyPostgreSQLVersion\include

Configure mysql_fdw for Windows

After setting up a project, make the following changes to build mysql_fdw in Visual Studio:

  1. In mysql_fdw.c, add the following defines: #define dlsym(lib, name) (void*)GetProcAddress((HMODULE)lib, name) #define dlopen(libname, unused) LoadLibraryEx(libname, NULL, 0)
  2. In the mysql_load_library definition, delete the following line: mysql_dll_handle = dlopen(_MYSQL_LIBNAME, RTLD_LAZY | RTLD_DEEPBIND);
  3. Add the following line in the mysql_load_library definition to replace the assignment of mysql_dll_handle for a Windows build: mysql_dll_handle = dlopen("libmysql.dll", 0);
  4. Prepend the call to the mysql_fdw_handler function with the __declspec(dllexport) keyword to export the function from the DLL: __declspec(dllexport) extern Datum mysql_fdw_handler(PG_FUNCTION_ARGS);
  5. In option.c, prepend the declaration of the mysql_fdw_validator function with the __declspec(dllexport) keyword to export the function from the DLL: __declspec(dllexport) extern Datum mysql_fdw_validator(PG_FUNCTION_ARGS);

You can now select the Release configuration and build.

Install the Extension

After you have compiled the DLL, follow the steps below to install the extension:

  1. Add the path to the lib folder for MySQL Connector C to the PATH environment variable of the machine running PostgreSQL.
  2. Copy the DLL from the RElease folder for your project into the lib subfolder of your PostgreSQL installation.
  3. In the folder containing the mysql_fdw csource files, copy myswl_fdw--1.0.sql and mysql_fdw.control into the extension folder under the share folder of your PostgreSQL installation. For example: C:\Program Files\PostgreSQL\9.4\share\extension.

Query AlloyDB Data as a PostgreSQL Database

After you have installed the extension, follow the steps below to start executing queries to AlloyDB data:

  1. Log into your PostgreSQL database. For example: C:\> psql -U postgres
  2. Load the extension for the database: postgres=#CREATE EXTENSION mysql_fdw;
  3. Create a server object for AlloyDB data: postgres=# CREATE SERVER AlloyDB FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
  4. Create a user mapping for the username and password of a user known to the MySQL remoting service. Below are the credentials for the user in the sample configuration of the service: postgres=# CREATE USER MAPPING for postgres SERVER AlloyDB OPTIONS (username 'admin', password 'test');
  5. Create the local schema: postgres=# CREATE SCHEMA AlloyDB_db;
  6. Import all the tables in the AlloyDB database you defined: postgres=# IMPORT FOREIGN SCHEMA "CData AlloyDB Sys" FROM SERVER AlloyDB INTO AlloyDB_db;

You can now execute read/write commands to AlloyDB:

postgres=# SELECT * FROM AlloyDB_db."orders";

Ready to get started?

Download a free trial of the AlloyDB ODBC Driver to get started:

 Download Now

Learn more:

AlloyDB Icon AlloyDB ODBC Driver

The AlloyDB ODBC Driver is a powerful tool that allows you to connect with live data from AlloyDB, directly from any applications that support ODBC connectivity.

Access AlloyDB data like you would a database - read, write, and update AlloyDB 0, etc. through a standard ODBC Driver interface.