Joshua Berkus wrote:
> (apologies for prior incomplete post. Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of
> how to use it to read your postgresql csv logs. I think this does some neat
> tying together of how to use FDWs that the docs are currently lacking.
I have merged your file_fdw example into our docs --- patch attached.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
new file mode 100644
index dd712e9..900b055
*** a/doc/src/sgml/file-fdw.sgml
--- b/doc/src/sgml/file-fdw.sgml
***************
*** 158,161 ****
--- 158,233 ----
specified, the file size (in bytes) is shown as well.
</para>
+ <example>
+ <title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title>
+
+ <para>
+ One of the obvious uses for the <literal>file_fdw</> is to make
+ the PostgreSQL activity log available as a table for querying. To
+ do this, first you must be logging to a CSV file, which here we
+ will call <literal>pglog.csv</>. First, install <literal>file_fdw</>
+ as an extension:
+ </para>
+
+ <programlisting>
+ CREATE EXTENSION file_fdw;
+ </programlisting>
+
+ <para>
+ Next, create the foreign data wrapper:
+
+ <programlisting>
+ CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
+ </programlisting>
+ </para>
+
+ <para>
+ Then create a foreign data server:
+
+ <programlisting>
+ CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
+ </programlisting>
+ </para>
+
+ <para>
+ Now you are ready to create the foreign data table. Using the
+ <command>CREATE FOREIGN TABLE</> command, you will need to define
+ the columns for the table, the CSV filename, and its format:
+
+ <programlisting>
+ CREATE FOREIGN TABLE pglog (
+ log_time timestamp(3) with time zone,
+ user_name text,
+ database_name text,
+ process_id integer,
+ connection_from text,
+ session_id text,
+ session_line_num bigint,
+ command_tag text,
+ session_start_time timestamp with time zone,
+ virtual_transaction_id text,
+ transaction_id bigint,
+ error_severity text,
+ sql_state_code text,
+ message text,
+ detail text,
+ hint text,
+ internal_query text,
+ internal_query_pos integer,
+ context text,
+ query text,
+ query_pos integer,
+ location text,
+ application_name text
+ ) SERVER pglog
+ OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
+ </programlisting>
+ </para>
+
+ <para>
+ That's it — now you can query your log directly. In production, of course,
+ you would need to define some way to adjust to log rotation.
+ </para>
+ </example>
+
</sect1>
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
