public inbox for [email protected]
help / color / mirror / Atom feedFile_FDW with example
7+ messages / 6 participants
[nested] [flat]
* File_FDW with example
@ 2011-06-17 01:21 Joshua Berkus <[email protected]>
0 siblings, 4 replies; 7+ messages in thread
From: Joshua Berkus @ 2011-06-17 01:21 UTC (permalink / raw)
To: pgsql-docs
(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.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco
Attachments:
[text/sgml] file-fdw.sgml (5.2K, 2-file-fdw.sgml)
download | inline:
<!-- doc/src/sgml/file-fdw.sgml -->
<sect1 id="file-fdw">
<title>file_fdw</title>
<indexterm zone="file-fdw">
<primary>file_fdw</primary>
</indexterm>
<para>
The <filename>file_fdw</> module provides the foreign-data wrapper
<function>file_fdw</function>, which can be used to access data
files in the server's filesystem. Data files must be in a format
that can be read by <command>COPY FROM</command>;
see <xref linkend="sql-copy"> for details.
</para>
<para>
A foreign table created using this wrapper can have the following options:
</para>
<variablelist>
<varlistentry>
<term><literal>filename</literal></term>
<listitem>
<para>
Specifies the file to be read. Required. Must be an absolute path name.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>format</literal></term>
<listitem>
<para>
Specifies the file's format,
the same as <command>COPY</>'s <literal>FORMAT</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>header</literal></term>
<listitem>
<para>
Specifies whether the file has a header line,
the same as <command>COPY</>'s <literal>HEADER</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>delimiter</literal></term>
<listitem>
<para>
Specifies the file's delimiter character,
the same as <command>COPY</>'s <literal>DELIMITER</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>quote</literal></term>
<listitem>
<para>
Specifies the file's quote character,
the same as <command>COPY</>'s <literal>QUOTE</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>escape</literal></term>
<listitem>
<para>
Specifies the file's escape character,
the same as <command>COPY</>'s <literal>ESCAPE</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>null</literal></term>
<listitem>
<para>
Specifies the file's null string,
the same as <command>COPY</>'s <literal>NULL</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>encoding</literal></term>
<listitem>
<para>
Specifies the file's encoding.
the same as <command>COPY</>'s <literal>ENCODING</literal> option.
</para>
</listitem>
</varlistentry>
</variablelist>
<para>
<command>COPY</>'s <literal>OIDS</literal>, <literal>FORCE_QUOTE</literal>,
and <literal>FORCE_NOT_NULL</literal> options are currently not supported by
<literal>file_fdw</>.
</para>
<para>
These options can only be specified for a foreign table, not in the
options of the <literal>file_fdw</> foreign-data wrapper, nor in the
options of a server or user mapping using the wrapper.
</para>
<para>
Changing table-level options requires superuser privileges, for security
reasons: only a superuser should be able to determine which file is read.
In principle non-superusers could be allowed to change the other options,
but that's not supported at present.
</para>
<para>
For a foreign table using <literal>file_fdw</>, <command>EXPLAIN</> shows
the name of the file to be read. Unless <literal>COSTS OFF</> is
specified, the file size (in bytes) is shown as well.
</para>
<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 "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. In one command,
you will need to define both the columns for the table as well as
the filename for the file 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, not you can query your log directly. In production, of course,
you would need to define some way to keep up with log rotation.
</para>
</example>
</sect1>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-06-17 11:13 Shigeru Hanada <[email protected]>
parent: Joshua Berkus <[email protected]>
3 siblings, 1 reply; 7+ messages in thread
From: Shigeru Hanada @ 2011-06-17 11:13 UTC (permalink / raw)
To: Joshua Berkus <[email protected]>; +Cc: pgsql-docs
(2011/06/17 10:21), 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.
Such example would be useful for administrators who wants to manage
servers via SQL. :-)
I examined the example, and found some points which should be corrected:
- Some lines are over 80 columns.
- CREATE EXTENSION file_fdw also creates default FOREIGN DATA WRAPPER
file_fdw, so no need to create FOREIGN DATA WRAPPER explicitly.
BTW, filename option can be changed via SQL:
ALTER FOREIGN TABLE pglog OPTIONS ( SET filename '/path/to/new/file' );
I think it's worth to mention the way to switch to new log file after
log rotation.
Regards,
--
Shigeru Hanada
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-06-17 11:24 Shigeru Hanada <[email protected]>
parent: Shigeru Hanada <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Shigeru Hanada @ 2011-06-17 11:24 UTC (permalink / raw)
To: Joshua Berkus <[email protected]>; +Cc: pgsql-docs
(2011/06/17 20:13), Shigeru Hanada wrote:
> I examined the example, and found some points which should be corrected:
>
> - Some lines are over 80 columns.
> - CREATE EXTENSION file_fdw also creates default FOREIGN DATA WRAPPER
> file_fdw, so no need to create FOREIGN DATA WRAPPER explicitly.
Oops, I forgot an important one.
- </para> needs to be added after </example>
Regards,
--
Shigeru Hanada
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-06-17 14:00 Alvaro Herrera <[email protected]>
parent: Joshua Berkus <[email protected]>
3 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2011-06-17 14:00 UTC (permalink / raw)
To: Joshua Berkus <[email protected]>; +Cc: pgsql-docs
Excerpts from Joshua Berkus's message of jue jun 16 21:21:36 -0400 2011:
> (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.
Interesting ...
The final query is a bit unwieldy. I wonder if this would work
CREATE TABLE generic_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
);
CREATE FOREIGN TABLE pglog (LIKE generic_pglog) SERVER pglog
OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
Note that you have a "Not" instead of "now" in the last paragraph.
--
Álvaro Herrera <[email protected]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-06-17 14:51 Magnus Hagander <[email protected]>
parent: Joshua Berkus <[email protected]>
3 siblings, 1 reply; 7+ messages in thread
From: Magnus Hagander @ 2011-06-17 14:51 UTC (permalink / raw)
To: Joshua Berkus <[email protected]>; +Cc: pgsql-docs
On Fri, Jun 17, 2011 at 03:21, Joshua Berkus <[email protected]> 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.
Hey, did you steal this straight from my blog? ;)
No, clearly not, because really.. Because the commands you've
suggested don't work, do they?
STATEMENT: CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
ERROR: foreign-data wrapper "file_fdw" already exists
because CREATE EXTENSION creates that one for you...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-06-27 20:49 Josh Berkus <[email protected]>
parent: Magnus Hagander <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2011-06-27 20:49 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; +Cc: pgsql-docs
> No, clearly not, because really.. Because the commands you've
> suggested don't work, do they?
> STATEMENT: CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
> ERROR: foreign-data wrapper "file_fdw" already exists
>
>
> because CREATE EXTENSION creates that one for you...
Ah, I think I combined code from non-create extension version with the
create extension version. Need to fix that, with both examples.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: File_FDW with example
@ 2011-11-30 00:28 Bruce Momjian <[email protected]>
parent: Joshua Berkus <[email protected]>
3 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2011-11-30 00:28 UTC (permalink / raw)
To: Joshua Berkus <[email protected]>; +Cc: pgsql-docs
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. +
Attachments:
[text/x-diff] /rtmp/csv (2.3K, 2-%2Frtmp%2Fcsv)
download | inline diff:
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>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-11-30 00:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-17 01:21 File_FDW with example Joshua Berkus <[email protected]>
2011-06-17 11:13 ` Shigeru Hanada <[email protected]>
2011-06-17 11:24 ` Shigeru Hanada <[email protected]>
2011-06-17 14:00 ` Alvaro Herrera <[email protected]>
2011-06-17 14:51 ` Magnus Hagander <[email protected]>
2011-06-27 20:49 ` Josh Berkus <[email protected]>
2011-11-30 00:28 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox