File Foreign Data Wrappers

Postgres Foreign Data Wrappers are incredibly useful for many things, especially for monitoring system resources. This article explains what FDWs are, how to install them, and how to use FDWs to monitor server resources.

Foreign Data Wrappers (FDWs) are a powerful feature within PostgreSQL that allows you to access and query data residing in external data sources as if they were native PostgreSQL tables. You can use FDWs to connect to other databases like MySQL, Oracle, MongoDB, etc. You can also use them to access files or programs on your server.

Installing FDWs is pretty simple. First make sure that Postgres-contrib is installed.

sudo yum install postgresql-contrib
OR
sudo apt-get install postgresql-contrib  

Once that is installed enable the file FDW extension and create a server that will be used to access external files:

CREATE EXTENSION file_fdw;
CREATE SERVER "import" FOREIGN DATA WRAPPER file_fdw;

Now we are ready to create some really cool system tables… let’s go!

public.system_df - create this function to see drive space from a query. This is useful in monitoring hard drive space. Notice that we are calling the system command df and piping it to awk in order to get the results in a csv format.

CREATE FOREIGN TABLE IF NOT EXISTS public.system_df (
  filesystem text,
  size numeric,
  used numeric,
  available numeric,
  use_pcnt text,
  mount text
) 
SERVER "import" OPTIONS (
  PROGRAM 'df -B1 | tail -n+2 | awk ''{print "\\""$1"\\",\\""$2"\\",\\""$3"\\",\\""$4"\\",\\""$5"\\",\\""$6"\\""}''',
  FORMAT 'csv',
  HEADER 'off'
);

--example query
SELECT 
  filesystem,
  PG_SIZE_PRETTY(size) AS size,
  PG_SIZE_PRETTY(used) AS used,
  PG_SIZE_PRETTY(available) AS available,
  use_pcnt,
  mount
FROM public.system_df

public.system_cpu - this table shows results from lscpu to monitor cpus. Notice that we are calling the system command lscpu and piping it to sed in order to get the results in a csv format.

CREATE FOREIGN TABLE IF NOT EXISTS public.system_cpu (
  name text,
  value text
) 
SERVER "import" OPTIONS (
  PROGRAM 'lscpu|sed -E ''s/^/"/''|sed -E ''s/:/",/''|sed -E ''s/  +/ /g''|sed -E ''s/, /,"/''|sed -E ''s/$/"/''|sed -E ''s/\\\(s\\\)/s/''',
  FORMAT 'csv',
  HEADER 'off'
);
--example usage
SELECT * FROM public.system_cpu

public.system_loadavg - to get the system load average we will call “cat /proc/loadavg” and pipe it to sed to get the results in a CSV format.

CREATE FOREIGN TABLE IF NOT EXISTS public.system_loadavg (
  load_avg_1_min numeric,
  load_avg_5_min numeric,
  load_avg_15_min numeric,
  number_of_running_over_number_of_threads text,
  last_created_pid numeric
) 
SERVER "import" OPTIONS (
  PROGRAM 'cat /proc/loadavg|sed ''s/\\s/\\,/g''',
  FORMAT 'csv',
  HEADER 'off'
);
--example usage
SELECT * FROM public.system_loadavg

public.system_mem - lets use the free command to get memory stats and pipe that to awk to format the results to CSV. Then we can use Postgres’s PG_PRETTY_SIZE function to make it a bit more readable.

CREATE FOREIGN TABLE IF NOT EXISTS public.system_mem (
  total numeric,
  used numeric,
  free numeric,
  shared numeric,
  buffers numeric,
  cached numeric
) 
SERVER "import" OPTIONS (
  PROGRAM 'free -b | awk -v RS="" ''{print $8 "," $9 "," $10 "," $11 "," $12 "," $13}''',
  FORMAT 'csv',
  HEADER 'off'
);
--sample usage
SELECT 
  PG_SIZE_PRETTY(total) AS total_mem,
  PG_SIZE_PRETTY(used) AS used,
  PG_SIZE_PRETTY(free) AS free,
  PG_SIZE_PRETTY(shared) AS shared,
  PG_SIZE_PRETTY(buffers) AS buffers,
  PG_SIZE_PRETTY(cached) AS cached
FROM public.system_mem

public.system_ps - to get a list of processes running on your server lets take the ps command and output the results in CSV format.

CREATE FOREIGN TABLE IF NOT EXISTS public.system_ps (
  pid text,
  username text,
  cpu_pcnt text,
  mem_pcnt text,
  command text
) 
SERVER "import" OPTIONS (
  --FILENAME '/var/ddfa/system_ps.csv',
  PROGRAM 'ps --no-headers -e -o %p, -o %U -o ,%C, -o %mem -o ,"%c"',
  FORMAT 'csv',
  HEADER 'true'
);
--example usage
SELECT * FROM public.system_ps

public.system_mpstat - if you happen to have mpstat installed on your server you can use the following to capture it.

 CREATE FOREIGN TABLE IF NOT EXISTS public.system_mpstat (
  checktime text,
  cpu text,
  user_pcnt text,
  nice_pcnt text,
  sys_pcnt text,
  iowait_pcnt text,
  irq_pcnt text,
  soft_pcnt text,
  steal_pcnt text,
  guest_pcnt text,
  gnice_pcnt text,
  idle_pcnt text
) 
SERVER "import" OPTIONS (
  PROGRAM 'mpstat -P ALL | tail -n+5 | awk ''{print "\\""$1"\\",\\""$2"\\",\\""$3"\\",\\""$4"\\",\\""$5"\\",\\""$6"\\",\\""$7"\\",\\""$8"\\",\\""$9"\\",\\""$10"\\",\\""$11"\\",\\""$12"\\""}''',
  FORMAT 'csv',
  HEADER 'off'
);

select * from public.system_mpstat

Hopefully this gives you a new perspective on how useful Postgres File Foreign Data Wrappers can be. What other system commands would you add to this list?