Windows: 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.

Use the following steps if you do not intend to use connection strings, but instead create ODBC data sources to access region and cross-region databases.

Open the ODBC Data Source Administrator.
Tip: On most newer Windows versions, click the Start Menu button, type ODBC, and then select the appropriate desktop app.
On the ODBC Data Source Administrator dialog box, select the System DSN tab.
Add the mandatory data sources:
Note: In order to create or query databases, you must have an active data source connection to the postgres database - one of the default databases created with any PostgreSQL server. If this connection already exists, you can skip this step:
1. Click Add.
2. Select the appropriate ODBC driver for your database (for example, PostgreSQL ODBC Driver (ANSI) is a common one for PostgreSQL), and click Finish.
  Note: If you are planning to run both 32-bit and 64-bit applications, you are required to install an ODBC driver for each bitism.
  
  The PostgreSQL setup dialog box appears.
3. In the Data Source and Description fields, enter the name as used for dsn:<name> when updating the database configuration file for this database, and a description for the data source.
  The data source name should be the name as specified by dsn:<name> when updating the database configuration file for this database.
4. In the Database field, type postgres.
5. In the Server and Port fields, enter the server name and port.
6. In the User Name and Password fields, enter a set of valid user credentials.
7. Click Datasource, and configure the Advanced Options:
  1. Click Page 2.
  2. In the idle time and interval fields, enter a value (in seconds) in which to maintain the connection when it is idle - you may need to experiment with this value, depending on the database workload.
  3. Click OK.
8. Click Test.
  The connection should be successful; if not, it indicates that you have a problem with your PostgreSQL installation, and should consult the vendor instructions to troubleshoot before proceeding any further.
9. Click Save.
Add a new data source for a region database:
1. Click Add.
2. Select the appropriate ODBC driver for your database (for example, PostgreSQL ODBC Driver (ANSI) is a common one for PostgreSQL), and click Finish.
  Note: The bitism of your driver must match the bitism of the application that will use it.
  
  The PostgreSQL setup dialog box appears.
3. In the Data Source and Description fields, enter the name as used for dsn:<name> when updating the database configuration file for this region database, and a description for the data source.
4. In the Database field, if your region database is to be housed in a single physical database containing other datastores/region databases, enter the name of that database as specified by -db|existdb:<db-name> when updating the database configuration file, or if the region database is to be housed in its own database, enter the name of the database as specified by -name:<dsname> when updating the database configuration file.
5. In the Server and Port fields, enter the server name and port.
6. In the User Name and Password fields, enter a set of valid user credentials.
7. Click Datasource, and configure the Advanced Options:
  1. Click Page 2.
  2. In the idle time and interval fields, enter a value (in seconds) in which to maintain the connection when it is idle - you may need to experiment with this value, depending on the database workload.
  3. Click OK.
  The database has yet to be created, and so do not click Test.
8. Click Save. The ODBC Data Source Administrator appears again.
9. Enter a name and description for the data source, and the name of the server on which the database will reside.
10. Click OK.
  The ODBC Data Source Administrator dialog box appears again.
Add a new data source for a cross-region database:
1. Click Add.
2. Select the appropriate ODBC driver for your database (for example, PostgreSQL ODBC Driver (ANSI) is a common one for PostgreSQL), and click Finish.
  Note: The bitism of your driver must match the bitism of the application that will use it.
  
  The PostgreSQL setup dialog box appears.
3. In the Data Source and Description fields, enter the name as used for dsn:<name> when updating the database configuration file for this region database, and a description for the data source.
4. In the Database field, if your cross-region database is to be housed in one physical database, with other datastores/region databases, enter the name of that database as specified by -db|existdb:<db-name> when updating the database configuration file, or if the cross-region database is to be housed in its own database, type $XREGN$ .
5. In the Server and Port fields, enter the server name and port.
6. In the User Name and Password fields, enter a set of valid user credentials.
7. Click Datasource, and configure the Advanced Options:
  1. Click Page 2.
  2. In the idle time and interval fields, enter a value (in seconds) in which to maintain the connection when it is idle - you may need to experiment with this value, depending on the database workload.
  3. Click OK.
  The database has yet to be created, and so do not click Test.
8. Click Save. The ODBC Data Source Administrator appears again.
Click OK.

The data sources for the region and cross-region databases are configured. You can now configure your enterprise server regions to use them.