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.
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:
Decide on the value of the parameters. The following parameters are required for both Linux and Windows:
Parameter Description v_dbcount Use
1as 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
postgrescan 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.
Execute the appropriate script from a command prompt on the database server, specifying the values for the parameters above. For example:
./fdw_linux.sh 1 mytest 5432 10.199.8.32 postgres root123 foreignschema1 foreign_server1 /var/lib/bin
fdw_win.bat 1 mytest 5432 10.199.8.32 postgres root123 foreignschema1 foreign_server1 "C:\Program Files\PostgreSQL\14\bin"
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:
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
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.