UNIX: Create Region and Cross-Region Data Sources for PostgreSQL

Note: We recommend that you use a connection string to connect to PostgreSQL databases; that is, using the connect attribute within the database configuration file, which is covered in Update the Configuration File for PostgreSQL Region and Cross-Region Databases.

If you did not configure direct connection strings, use the following steps to create ODBC data sources to access the region and cross-region databases.

There are a number of different driver managers that you can use to configure an ODBC data source. These instructions are carried out using unixODBC. If you are using a different driver manager, consult the vendor instructions for creating data sources.

Run the command odbcinst -j command, and note down the location of the drivers configuration file (odbcinst.ini) and the user data sources configuration file (.odbc.ini).
Open the odbcinst.ini configuration file and ensure that you have something similar to the following:
(This may differ depending on the version of PostgreSQL you are running.)
```
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver64=/usr/pgsql-10/lib/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
Threading=2
```
Note: Ensuring that the Threading setting is specified avoids potential problems when performing database operations on multiple threads.
Open the .odbc.ini configuration file and add an entry for the mandatory data source:
```
[<datasource-name>]
Driver = <driver-name>
Servername = <server-name>
Port = <port-no>
Database = postgres
KeepaliveTime=<int>
KeepaliveInterval=<int>
BatchSize=100
```
where:
- <datasource-name> - a name for the data source.
- <driver-name> - the name of the driver as configured in the .odbcinst.ini file; in the example above, that would be PostgreSQL.
- <server-name> - the name of the server on which the database instance resides.
- <port-no> - the port on which the database is listening.
- postgres - one of the default databases supplied with a PostgreSQL server.
- <int> - the number of seconds in which the connection will remain active whilst being idle - you may need to experiment with this value, depending on the database's workload.
Note: The BatchSize parameter is a setting to aid I/O performance.

Add a new data source for a region database:

[<datasource-name>]
Driver = <driver-name>
Servername = <server-name>
Port = <port-no>
Database = <region-name>
KeepaliveTime=<int>
KeepaliveInterval=<int>
BatchSize=100

where:

<region-name> - the name of the enterprise server region that this database will serve.

Add a new data source for a cross-region database:

[<datasource-name>]
Driver = <driver-name>
Servername = <server-name>
Port = <port-no>
Database = $XREGN$
KeepaliveTime=<int>
KeepaliveInterval=<int>
BatchSize=100

Save the .odbc.ini configuration file.

Example .odbc.ini file

The following is an example of an .odbc.ini configuration file that contains data sources for a local PostgreSQL database instance that stores files from the ESDEMO enterprise server region in a region and cross-region database.

[PG.POSTGRES]
Driver = PostgreSQL
Servername = localhost
Port = 5432
Database = postgres
KeepaliveTime=120
KeepaliveInterval=120
BatchSize=100

[PG.CAS.ESDEMO]
Driver = PostgreSQL
Servername = localhost
Port = 5432
Database = ESDEMO
KeepaliveTime=120
KeepaliveInterval=120
BatchSize=100

[PG.CAS.CROSSREGION]
Driver = PostgreSQL
Servername = localhost
Port = 5432
Database = $XREGN$
KeepaliveTime=120
KeepaliveInterval=120
BatchSize=100

The data sources for the region and cross-region databases are configured. Now, you can configure your enterprise server regions to use the databases.