Configure a foreign data wrapper to collect data from multiple PostgreSQL databases

Some PostgreSQL Administrative alerts can be configured to collect data from multiple databases. The default PostgreSQL implementation does not support cross-database queries. If you want an alert to be triggered based on data from multiple databases, you must configure a foreign data wrapper. A foreign data wrapper is an extension available in PostgreSQL that allows you to access data from multiple databases.

For more information about foreign data wrappers, see the PostgreSQL documentation.

DPA provides scripts to help you implement foreign data wrappers.

1. Download fdw_scripts.zip and extract the contents on the database server. This file contains the following scripts for Linux and Windows operating systems, respectively:

   • fdw_linux.sh
   • fdw_win.bat

2. Decide on the value of the parameters. The following parameters are required for both Linux and Windows:

   Parameter	Description
   v_dbcount	Use 1 as the value.
   v_dbname	Specify the name of the database for which the foreign data wrapper is being created.
   v_port	Specify the PostgreSQL server port. The default port is 5432.
   v_hostip	Specify the host IP address.
   v_username	Specify the PostgreSQL user for which the foreign data wrapper is mapped by default. The superuser postgres can be used.
   v_userpwd	Specify the password for the user in the previous parameter.
   v_schemaname	Specify the name of the schema to which foreign views will be imported. Use a unique schema for each foreign data wrapper.database.
   v_servername	Specify the server name where the server is created. Use a unique server name for each foreign data wrapper.database.
   v_psqlpath	Specify the PostgreSQL path.

3. Execute the appropriate script from a command prompt on the database server, specifying the values for the parameters above. For example:

   • For Linux:

     ./fdw_linux.sh 1 mytest 5432 10.199.8.32 postgres root123 foreignschema1 foreign_server1 /var/lib/bin

   • For Windows:

     fdw_win.bat 1 mytest 5432 10.199.8.32 postgres root123 foreignschema1 foreign_server1 "C:\Program Files\PostgreSQL\14\bin"

Specifying schemas in an alert definition

When you create an alert and you want to include data from multiple databases, you will specify which schemas to get the data from. The available schemas can be seen in the client console under Foreign Data Wrappers. For example, let's say the following command was used to create the foreign data wrapper, with foreign_pg_catalog1 specified as the schema name:

fdw_win.bat 1 foreign_db1 5432 10.199.8.22 postgres admin foreign_pg_catalog1 foreign_server1 "C:\Program Files\PostgreSQL\14\bin"

This schema can be seen in the console here:

If an alert can use data from multiple databases, the Alert Parameters section includes the Schema Name option. To define which schemas to use:

1. Select an option from the Schema Name drop-down menu:

   • Catalog: Uses data from a single database, and does not require a foreign data wrapper

   • All Schemas: Uses data from all existing schemas

   • Include Schemas: Uses data from the schemas you specify

   • Exclude Schemas: Uses data from all existing schemas except the ones you specify

   

2. If you selected Include Schemas or Exclude Schemas, enter the names of the schemas to include or exclude.

   

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.