PostgreSQL wiki - User contributions [en]

Query Parsing

2014-07-25T16:19:58Z

Robe: /* pg_query */

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code. More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

<code>
#<PgQuery:0x000000009673a0
@query="SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;",
@parsetree=
[{"SELECT"=>
{"distinctClause"=>nil,
"intoClause"=>nil,
"targetList"=>
[{"RESTARGET"=>
{"name"=>nil,
"indirection"=>nil,
"val"=>{"COLUMNREF"=>{"fields"=>[{"A_STAR"=>{}}], "location"=>7}},
"location"=>7}}],
"fromClause"=>
[{"RANGEVAR"=>
{"schemaname"=>nil,
"relname"=>"foo",
"inhOpt"=>2,
"relpersistence"=>"p",
"alias"=>nil,
"location"=>14}}],
"whereClause"=>
{"AEXPR"=>
{"name"=>["="],
"lexpr"=>{"COLUMNREF"=>{"fields"=>["bar"], "location"=>24}},
"rexpr"=>{"A_CONST"=>{"val"=>42, "location"=>30}},
"location"=>28}},
"groupClause"=>nil,
"havingClause"=>nil,
"windowClause"=>nil,
"valuesLists"=>nil,
"sortClause"=>
[{"SORTBY"=>
{"node"=>{"COLUMNREF"=>{"fields"=>["id"], "location"=>42}},
"sortby_dir"=>2,
"sortby_nulls"=>0,
"useOp"=>nil,
"location"=>-1}}],
"limitOffset"=>nil,
"limitCount"=>{"A_CONST"=>{"val"=>23, "location"=>56}},
"lockingClause"=>nil,
"withClause"=>nil,
"op"=>0,
"all"=>false,
"larg"=>nil,
"rarg"=>nil}}],
@warnings=[]>
</code>

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

[[Category:Development]]

Query Parsing

2014-07-25T16:18:54Z

Robe:

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code.

<code>
#<PgQuery:0x000000009673a0
@query="SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;",
@parsetree=
[{"SELECT"=>
{"distinctClause"=>nil,
"intoClause"=>nil,
"targetList"=>
[{"RESTARGET"=>
{"name"=>nil,
"indirection"=>nil,
"val"=>{"COLUMNREF"=>{"fields"=>[{"A_STAR"=>{}}], "location"=>7}},
"location"=>7}}],
"fromClause"=>
[{"RANGEVAR"=>
{"schemaname"=>nil,
"relname"=>"foo",
"inhOpt"=>2,
"relpersistence"=>"p",
"alias"=>nil,
"location"=>14}}],
"whereClause"=>
{"AEXPR"=>
{"name"=>["="],
"lexpr"=>{"COLUMNREF"=>{"fields"=>["bar"], "location"=>24}},
"rexpr"=>{"A_CONST"=>{"val"=>42, "location"=>30}},
"location"=>28}},
"groupClause"=>nil,
"havingClause"=>nil,
"windowClause"=>nil,
"valuesLists"=>nil,
"sortClause"=>
[{"SORTBY"=>
{"node"=>{"COLUMNREF"=>{"fields"=>["id"], "location"=>42}},
"sortby_dir"=>2,
"sortby_nulls"=>0,
"useOp"=>nil,
"location"=>-1}}],
"limitOffset"=>nil,
"limitCount"=>{"A_CONST"=>{"val"=>23, "location"=>56}},
"lockingClause"=>nil,
"withClause"=>nil,
"op"=>0,
"all"=>false,
"larg"=>nil,
"rarg"=>nil}}],
@warnings=[]>
</code>

More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

[[Category:Development]]

Query Parsing

2014-07-25T16:18:37Z

Robe:

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code.

<code>
#<PgQuery:0x000000009673a0
@query="SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;",
@parsetree=
[{"SELECT"=>
{"distinctClause"=>nil,
"intoClause"=>nil,
"targetList"=>
[{"RESTARGET"=>
{"name"=>nil,
"indirection"=>nil,
"val"=>{"COLUMNREF"=>{"fields"=>[{"A_STAR"=>{}}], "location"=>7}},
"location"=>7}}],
"fromClause"=>
[{"RANGEVAR"=>
{"schemaname"=>nil,
"relname"=>"foo",
"inhOpt"=>2,
"relpersistence"=>"p",
"alias"=>nil,
"location"=>14}}],
"whereClause"=>
{"AEXPR"=>
{"name"=>["="],
"lexpr"=>{"COLUMNREF"=>{"fields"=>["bar"], "location"=>24}},
"rexpr"=>{"A_CONST"=>{"val"=>42, "location"=>30}},
"location"=>28}},
"groupClause"=>nil,
"havingClause"=>nil,
"windowClause"=>nil,
"valuesLists"=>nil,
"sortClause"=>
[{"SORTBY"=>
{"node"=>{"COLUMNREF"=>{"fields"=>["id"], "location"=>42}},
"sortby_dir"=>2,
"sortby_nulls"=>0,
"useOp"=>nil,
"location"=>-1}}],
"limitOffset"=>nil,
"limitCount"=>{"A_CONST"=>{"val"=>23, "location"=>56}},
"lockingClause"=>nil,
"withClause"=>nil,
"op"=>0,
"all"=>false,
"larg"=>nil,
"rarg"=>nil}}],
@warnings=[]>
</code>

More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

[[Category:Development]]

Query Parsing

2014-07-25T16:15:40Z

Robe: /* pg_query */

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code.

<code>
#<PgQuery:0x000000009673a0
@query="SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;",
@parsetree=
[{"SELECT"=>
{"distinctClause"=>nil,
"intoClause"=>nil,
"targetList"=>
[{"RESTARGET"=>
{"name"=>nil,
"indirection"=>nil,
"val"=>{"COLUMNREF"=>{"fields"=>[{"A_STAR"=>{}}], "location"=>7}},
"location"=>7}}],
"fromClause"=>
[{"RANGEVAR"=>
{"schemaname"=>nil,
"relname"=>"foo",
"inhOpt"=>2,
"relpersistence"=>"p",
"alias"=>nil,
"location"=>14}}],
"whereClause"=>
{"AEXPR"=>
{"name"=>["="],
"lexpr"=>{"COLUMNREF"=>{"fields"=>["bar"], "location"=>24}},
"rexpr"=>{"A_CONST"=>{"val"=>42, "location"=>30}},
"location"=>28}},
"groupClause"=>nil,
"havingClause"=>nil,
"windowClause"=>nil,
"valuesLists"=>nil,
"sortClause"=>
[{"SORTBY"=>
{"node"=>{"COLUMNREF"=>{"fields"=>["id"], "location"=>42}},
"sortby_dir"=>2,
"sortby_nulls"=>0,
"useOp"=>nil,
"location"=>-1}}],
"limitOffset"=>nil,
"limitCount"=>{"A_CONST"=>{"val"=>23, "location"=>56}},
"lockingClause"=>nil,
"withClause"=>nil,
"op"=>0,
"all"=>false,
"larg"=>nil,
"rarg"=>nil}}],
@warnings=[]>
</code>

More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

Query Parsing

2014-07-25T15:40:51Z

Robe:

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code.

<code>
[..example output..]
</code>

More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

Query Parsing

2014-07-25T15:37:07Z

Robe: /* Using Postgres internals */

== Query Parsing ==

There are quite a few use cases where it'd be of great help if you could access the query tree Postgres uses internally for processing queries.

For example, when you need to do non-trivial operations on queries, like:

* Filtering out queries containing specific tables
* Reformatting & pretty-printing query strings
* Modifying or removing parts of the query string, e.g. rename a column

The [http://www.postgresql.org/docs/current/static/overview.html Postgres documentation] gives a good overview how query execution is handled and what the intermediate tree representations of a query string are.

=== Approaches ===

==== Using Postgres internals ====

Postgres already offers tooling to create a query tree from a given query string.

<code>raw_parser</code> takes a query statement as input and will return a parse tree. This parse tree can then be pretty-printed by using <code>nodeToString</code>.

An example can be seen [https://github.com/pganalyze/queryparser/blob/master/queryparser.c here], this can be easily implemented in a [http://www.postgresql.org/docs/current/static/extend-extensions.html Postgres extension] as well as seen [https://github.com/pganalyze/queryparser/blob/master/extension/annotate_query.c here].

<code>
SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
{SELECT
:distinctClause <>
:intoClause <>
:targetList (
{RESTARGET
:name <>
:indirection <>
:val
{COLUMNREF
:fields (
{A_STAR
}
)
:location 7
}
:location 7
}
)
:fromClause (
{RANGEVAR
:schemaname <>
:relname foo
:inhOpt 2
:relpersistence p
:alias <>
:location 14
}
)
:whereClause
{AEXPR
:name ("=")
:lexpr
{COLUMNREF
:fields ("bar")
:location 24
}
:rexpr
{A_CONST
:val 42
:location 30
}
:location 28
}
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause (
{SORTBY
:node
{COLUMNREF
:fields ("id")
:location 42
}
:sortby_dir 2
:sortby_nulls 0
:useOp <>
:location -1
}
)
:limitOffset <>
:limitCount
{A_CONST
:val 23
:location 56
}
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
)
</code>

This approach isn't without caveats though, since the nodeToString code is used only for printing out planned query trees. These trees are rewritten to contain only contain nodes ultimately needed by the [http://www.postgresql.org/docs/current/static/executor.html Executor]. This means that all nodes not to expected in later stages of the query execution can't be printed, for example INSERT/UPDATE/DELETE - these are all aggregated in a <code>ModifyStmt</code> node.

==== pg_query ====

[https://github.com/pganalyze/pg_query pg_query] is a Ruby gem which can generate query trees in JSON representation. It accomplishes this by using a patched Postgres version which one hond adressess the shortcomings outlined above, on the other hand it provides a new function [https://github.com/pganalyze/postgres/blob/172e1dfc81705e9b14d81e8ef56c54b10ddfcd65/src/backend/nodes/outfuncs_json.c#L3502 <code>nodeToJSONString</code>] which turns a query tree into a JSON representation instead of the native Postgres format.

The Ruby bindings are just glue code around the Postgres code.

<code>
[..example output..]
</code>

More information can be found in this [https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html blog post]

==== pgpool-II ====

pgpool-II is a middleware for Postgresql which in it's simplest use-case is an connection broker between client and database servers.

To be able to do advanced query routing it needs to understand the queries it gets sent form the query. To be able to do that, they forked the parser and implemented their own node <code>outFuncs</code> to be able to recreated a query string from a given query tree.

If you want to create your own as correct as possible Postgres query pretty-printer this might be a good starting point.

==== sqlparse ====

[https://pypi.python.org/pypi/sqlparse sqlparse] is a Python module which implements a non-validating SQL parser suitable for parsing, splitting and formatting SQL statements. It provides the backbone for http://sqlformat.org/ - a SQL pretty printer.

Introspection into the queries is quite limited and since it's not specifically targed at Postgres it's capabilities are quite limited when it comes to drilling down into query trees.

== TODO ==

To make life easier for people using query trees the following things need to be addressed in Postgres core .

* Implement outFuncs for all node types and not only those seen in a post-planning state.
* Ship alternate outFuncs suitable for generating JSON trees instead of the native Postgres tree format.
* Support a placeholder character suitable for mimicking all constant expressions in query strings. This would be of great help when trying to parse normalized queries as produced by pg_stat_statements.

Query Parsing

2014-07-25T15:26:05Z

Robe: /* Query Parsing */

Query Parsing

2014-07-25T15:19:21Z

Robe: first version

Apt

2014-02-05T14:22:49Z

Robe: change default distro to wheezy.

==PostgreSQL packages for Debian and Ubuntu==

The PostgreSQL Global Development Group (PGDG) maintains an APT repository of PostgreSQL packages for Debian and Ubuntu located at http://apt.postgresql.org/pub/repos/apt/.
We aim at building PostgreSQL server packages as well as extensions and modules packages on several Debian/Ubuntu releases for all PostgreSQL versions supported.

Currently, we support

* '''Debian 6.0''' (squeeze), '''7.0''' (wheezy), and '''unstable''' (sid) 64/32 bit (amd64/i386)
* '''Ubuntu 10.04''' (lucid), '''12.04''' (precise), and '''14.04''' (trusty) 64/32 bit (amd64/i386) -- See the [[Apt/FAQ#I_am_using_a_non-LTS_release_of_Ubuntu|FAQ]] for other Ubuntu releases
* '''PostgreSQL 8.4, 9.0, 9.1, 9.2, 9.3'''
* Server extensions such as Slony-I, various PL languages, and datatypes
* Applications like pgadmin3, pgbouncer, and pgpool-II

Packages for older PostgreSQL versions and older Debian/Ubuntu distributions will continue to stay in the repository; updates for those will be provided on an ad-hoc basis.

==Quickstart==

Create '''/etc/apt/sources.list.d/pgdg.list'''. The distributions are called ''codename'''''-pgdg'''. In the example, replace ''wheezy'' with the actual distribution you are using:

<nowiki>deb http://apt.postgresql.org/pub/repos/apt/</nowiki> ''wheezy''-pgdg main

(You may determine the codename of your distribution by running '''lsb_release -c'''.)

Import the repository key from '''https://www.postgresql.org/media/keys/ACCC4CF8.asc''', update the package lists, and start installing packages:

<nowiki>wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -</nowiki>
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.3 pgadmin3

Alternately, [http://anonscm.debian.org/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh this shell script] will do the above steps for you.

Have a look at the '''[[Apt/FAQ|FAQ]]'''.

'''Note:''' This repository provides "postgresql", "postgresql-contrib", and "postgresql-client" ''meta-packages'' that depend on the latest postgresql-x.y, ... packages, similar to the ones present in Debian and Ubuntu. Once a new PostgreSQL version is released, these meta-packages will be updated to depend on the new version. If you rather want to stay with a particular PostgreSQL version, you should install specific packages like "postgresql-9.3" instead of "postgresql".

==News==

* 2014-01-09: Ubuntu trusty (14.04) packages available: http://www.postgresql.org/message-id/20140109124716.GA5659@msgid.credativ.de
* 2013-11-22: Users concerned about the [[Nov2013ReplicationIssue]] can find 9.2.4/9.1.9/9.0.13 packages at http://apt.postgresql.org/pub/repos/apt/nov2013replicationissue/
* 2013-10-10: New pgdg-keyring version extending the key expiration date. The old expiration date is 2013-10-13. Announcement: http://www.postgresql.org/message-id/20131010083115.GC24812@msgid.df7cb.de Additional blog post: http://blog.endpoint.com/2013/10/postgresql-debian-apt-repository-gnupg.html
* 2013-09-09: postgresql-9.3 released! (This also includes new postgresql-9.2 and postgresql-common packages.)
* 2013-05-12: postgresql-9.3 beta1 is now available, see [[Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release|FAQ entry]] for instructions
* 2013-05-11: Temporary problems with libpq5 packages: http://www.postgresql.org/message-id/20130511050816.GB8469@msgid.df7cb.de
* 2013-04-10: New postgresql-common version 141 introducing a dependency on the pgdg-keyring package. (If you were following the (old) installation instructions, you already have that package installed.)
* 2013-02-23: pgapt.debian.net is gone now. If you were using that, change to apt.postgresql.org using the instructions above.
* 2013-02-22: Default repository pin priority changed; see [[Apt/FAQ#I_want_only_specific_packages_from_this_repository|Apt pinning]] for details.
* 2012-12-18: Ubuntu Lucid (10.04) is now part of the repository; PostgreSQL and pgadmin3 packages are built.
* 2012-12-06: Official announcement: http://archives.postgresql.org/pgsql-announce/2012-12/msg00008.php, http://www.postgresql.org/about/news/1432/
* 2012-12-06: Minor releases 9.2.2, 9.1.7, 9.0.11, 8.4.15, 8.3.22 built.
* 2012-11-30: pgadmin3 added to the repository
* 2012-11-22: Ubuntu Precise (12.04) added to the repository, and built 9.2.1, 9.1.6, 9.0.10, 8.4.14, 8.3.21 for it.
* 2012-10-13: Repository key renewed with new expiry date.
* 2012-10-02: Minor releases 9.2.1, 9.1.6, 9.0.10, 8.4.14, 8.3.21 built
* 2012-09-15: 9.2.0 is in the repository
* 2012-08-22: PostgreSQL in Debian Hackathon: [http://www.df7cb.de/blog/2012/PostgreSQL_in_Debian_Hackathon.html Blog posting] and [https://github.com/dimitri/apt.postgresql.org/blob/master/hackaton-20120821.md notes]. The distribution names changed from *-pgapt to *-pgdg, please update your sources.list.
* 2012-06-07: 9.2 added, Lenny marked unsupported now
* 2012-06-07: separate components "9.0", "9.2" etc added for libpq5/libpg-dev/lib... packages that would otherwise overwrite packages from other versions in the same suite. (Usually, using the latest stable libpq5 version will be ok for users. The packages built from other PostgreSQL versions are provided only for completeness.)

==Resources==

* [[Apt/FAQ|FAQ]]
* [https://pgdgbuild.dus.dg-i.net/repos/apt/dists/ Overview of available packages and versions] (WIP)
* [http://apt.postgresql.org/pub/repos/apt/dists/ Dists directory]
* [http://apt.postgresql.org/pub/repos/apt/pool/ Packages pool directory]
* [[ExtensionPackaging]] (PostgreSQL Wiki)
* [http://wiki.debian.org/pkg-postgresql pkg-postgresql] (Debian Wiki)

===Contact===

* Mailing list: pgsql-pkg-debian@postgresql.org ([http://archives.postgresql.org/pgsql-pkg-debian/ Archives])
* IRC channel: #postgresql-apt @ irc.freenode.net
* People: Christoph Berg <myon@debian.org>, Dimitri Fontaine <dimitri@2ndquadrant.fr>, Magnus Hagander <magnus@hagander.net>

===Bugs===

Please report bugs on the pgsql-pkg-debian@postgresql.org mailing list, or open a bug in the [http://bugs.debian.org/ Debian BTS].

===Documentation===

* [[Apt/RepoDocs]]
* [[Apt/Jenkins]]
* [[Apt/NewPostgreSQLVersion]]

==Acknowledgements==

Work on setting up the archive was kindly supported by [http://www.credativ.de/ credativ], [http://www.2ndquadrant.com/ 2ndQuadrant], [http://redpill-linpro.com/ Redpill Linpro],
and funding from the European Union's Seventh Framework Programme (FP7/2007-2013) under grant agreement 258862.

The build server is kindly hosted by [https://www.dg-i.net/ DG-i]. The archive is hosted on postgresql.org hardware.

Monitoring

2013-11-26T09:50:15Z

Robe: /* Generic monitoring solutions with plugins */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== CLI tools ===

==== pg_view ====

<tt>[http://tech.zalando.com/getting-a-quick-view-of-your-postgresql-stats/ pg_view]</tt> is a Python-based tool to quickly get information about running databases and resources used by them as well as correlate running queries and why they might be slow.

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== Zabbix ====

[http://pg-monz.github.io/pg_monz/index-en.html pg_monz] is a [http://www.zabbix.com/ Zabbix] monitoring template for Postgres.

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

==== PGObserver ====

[http://zalando.github.io/PGObserver/ PGObserver] is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

==== pgCluu ====
[http://pgcluu.darold.net/ pgCluu] is a Perl-based monitoring solution which uses psql and [http://en.wikipedia.org/wiki/Sar_(Unix) sar] to collect information about Postgres servers and render comprehensive performance stats.

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-11-19T11:54:44Z

Robe: added pgcluu

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== CLI tools ===

==== pg_view ====

<tt>[http://tech.zalando.com/getting-a-quick-view-of-your-postgresql-stats/ pg_view]</tt> is a Python-based tool to quickly get information about running databases and resources used by them as well as correlate running queries and why they might be slow.

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

==== PGObserver ====

[http://zalando.github.io/PGObserver/ PGObserver] is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

==== pgCluu ====
[http://pgcluu.darold.net/ pgCluu] is a Perl-based monitoring solution which uses psql and [http://en.wikipedia.org/wiki/Sar_(Unix) sar] to collect information about Postgres servers and render comprehensive performance stats.

[[Category:Monitoring|!]]
[[Category:Performance]]

PostgreSQL Conference Europe Talks 2013

2013-11-01T14:19:09Z

Robe: /* Lightning talks */ i can't paranthese

= PostgreSQL Conference Europe 2013 Talks =

== Conference Website ==

http://2013.pgconf.eu/

== Trainings: Tuesday 29th October, 2013 ==

=== Dargle ===

* PostgreSQL & PostGIS (Gianni Ciolli)
* Writing & using Postgres Extensions (Dimitri Fontaine)

=== Dodder ===

* PostgreSQL indexing (Hans-Jürgen Schönig)

=== Liffey ===

* PostgreSQL Performance Tuning (Bruce Momjian)
* Data Processing Inside PostgreSQL (Bruce Momjian)

== Talks: Wednesday 30th October, 2013 ==

=== Earlsfort ===

* Keynote: Handling the Spotlight: How PostgreSQL can become the database of choice (Mark Taylor)
* What's New in PostgreSQL 9.3 (Robert Treat)
* PostgreSQL - One of the most important Open Source projects (Michael Meskes)
* Going spatial with PostGIS and more (Hugo Mercier)
* Nulls Make Things Easier? (Bruce Momjian)
* PostgreSQL Disaster Recovery with Barman (Gabriele Bartolini)

=== Leeson ===

* PostgreSQL for Rails (Roland Sonnenschein)
* Introduction of pg_statsinfo and pg_stats_reporter ~Statistics Reporting Tool for DBA~ (Mitsumasa KONDO)
* [http://de.slideshare.net/psoo1978/pg-fdw Writing a foreign data wrapper] (Bernd Helmle)
* Success stories on migrating from Sybase ASE to PostgreSQL (Achim Eisele, Jens Wilke)
* Multicorn: writing Foreign Data Wrappers in python (Ronan Dunklau)

=== Pembroke ===

* Writing a user-defined datatype (Heikki Linnakangas)
* LATERAL querying in 9.3; keep up with the new features! (Gianni Ciolli)
* Detecting performance bottlenecks (Hans-Jürgen Schönig)
* [http://www.slideshare.net/jkatz05/explain-the-index-of-postgresql-indexes Explaining the Index of PostgreSQL Indexes] (Jonathan S. Katz)
* Conduct change from Oracle to PostgreSQL (Jean-Paul Argudo)

== Talks: Thursday 31th October, 2013 ==

=== Earlsfort ===

* Taking advantage of custom background workers (Michael Paquier)
* Doing PITR Right - There's more than just replication (Stephen Frost)
* [https://speakerdeck.com/craigkerstiens/postgres-what-they-really-use slides Postgres what they really use] ([http://www.craigkerstiens.com Craig Kerstiens])
* Indexes: The neglected performance all-rounder (Markus Winand)
* Migrating from MySQL to PostgreSQL (Dimitri Fontaine)
* Lightning talks (Harald Armin Massa)

=== Leeson ===

* Gotcha! Finer points of the postgres query planner and how to use them in your queries (Atri Sharma)
* [https://wiki.postgresql.org/images/a/ad/Materialised_views_now_and_the_future-pgconfeu_2013.pdf Materialised views now and the future] (Thom Brown)
* [http://www.slideshare.net/davidfetter/federation-with-foreigndatawrapperspgconfeu20131031 Federation with Foreign Data Wrappers] (David Fetter)
* Switching Horses Overnight - a Software Manufacturer's View on Replacing an Existing Database Management System with PostgreSQL (Robert Lichtenberger, Stefan Andreatta)
* My experience with embedding PostgreSQL (Jignesh Shah)

=== Pembroke ===

* pgBadger v.4 (Jean-Paul Argudo)
* Business Intelligence & Performance (Simon Riggs)
* [https://speakerdeck.com/peterg/concurrency-in-postgres Concurrency in Postgres] (Peter Geoghegan)
* Next generation of GIN (Alexander Korotkov, Oleg Bartunov) [http://www.sai.msu.su/~megera/postgres/talks/Next%20generation%20of%20GIN.pdf Slides(pdf)]
* [https://speakerdeck.com/leinweber/visualizing-postgres Visualizing Postgres] ([http://bitfission.com Will Leinweber])

=== Lightning talks ===

* [http://www.slideshare.net/davidfetter/slides-pg-confeu20131031 Slides] (David Fetter)
* [https://wiki.postgresql.org/images/1/1c/Json-and-speed.pdf JSON & Speed] (Jonathan S. Katz)
* [https://wiki.postgresql.org/images/a/ab/Pganalyze_Lightning_talk.pdf pganalyze - Monitoring for Casual DBAs] (Michael Renner)

== Talks: Friday 1st November, 2013 ==

=== Earlsfort ===

* Epic fails in the RDBMS world (Willem Leenen)
* Using Postgres FDW w/ a sharded PostgreSQL Database (Stephen Frost)
* Postgres versus an anonymous database from Redwood (Gregory Stark)
* Hierarchies Without Masters (Gianni Ciolli)
* PostgreSQL Futures (Simon Riggs)
* PostgreSQL in 5 years - Expectations from the market place (Keith Alsheimer)
* Closing (Dave Page, Magnus Hagander)

=== Leeson ===

* Pagination done the PostgreSQL way (Markus Winand)
* Postgres from Vision to Reality, ABNAMRO Client case (Michel Sijmons)
* PostgreSQL Backups, the good the bad and the ugly (Joshua D. Drake)
* Elephants in fashion (Valentine Gogichashvili)

=== Pembroke ===

* Agile Oracle to PostgreSQL migrations (Gabriele Bartolini)
* Useful PostgreSQL Extensions -- the extensions behind the scenes (Devrim GÜNDÜZ)
* Binary storage for nested data structures and application to hstore data type. (Oleg Bartunov, Teodor Sigaev), [http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf Slides(pdf)]
* Wiretapping the Wire Protocol: automatic data visualization for psql with Cartographer and FEMEBE (Maciek Sakrejda)

PostgreSQL Conference Europe Talks 2013

2013-11-01T14:18:49Z

Robe: /* Lightning talks */

= PostgreSQL Conference Europe 2013 Talks =

== Conference Website ==

http://2013.pgconf.eu/

== Trainings: Tuesday 29th October, 2013 ==

=== Dargle ===

* PostgreSQL & PostGIS (Gianni Ciolli)
* Writing & using Postgres Extensions (Dimitri Fontaine)

=== Dodder ===

* PostgreSQL indexing (Hans-Jürgen Schönig)

=== Liffey ===

* PostgreSQL Performance Tuning (Bruce Momjian)
* Data Processing Inside PostgreSQL (Bruce Momjian)

== Talks: Wednesday 30th October, 2013 ==

=== Earlsfort ===

* Keynote: Handling the Spotlight: How PostgreSQL can become the database of choice (Mark Taylor)
* What's New in PostgreSQL 9.3 (Robert Treat)
* PostgreSQL - One of the most important Open Source projects (Michael Meskes)
* Going spatial with PostGIS and more (Hugo Mercier)
* Nulls Make Things Easier? (Bruce Momjian)
* PostgreSQL Disaster Recovery with Barman (Gabriele Bartolini)

=== Leeson ===

* PostgreSQL for Rails (Roland Sonnenschein)
* Introduction of pg_statsinfo and pg_stats_reporter ~Statistics Reporting Tool for DBA~ (Mitsumasa KONDO)
* [http://de.slideshare.net/psoo1978/pg-fdw Writing a foreign data wrapper] (Bernd Helmle)
* Success stories on migrating from Sybase ASE to PostgreSQL (Achim Eisele, Jens Wilke)
* Multicorn: writing Foreign Data Wrappers in python (Ronan Dunklau)

=== Pembroke ===

* Writing a user-defined datatype (Heikki Linnakangas)
* LATERAL querying in 9.3; keep up with the new features! (Gianni Ciolli)
* Detecting performance bottlenecks (Hans-Jürgen Schönig)
* [http://www.slideshare.net/jkatz05/explain-the-index-of-postgresql-indexes Explaining the Index of PostgreSQL Indexes] (Jonathan S. Katz)
* Conduct change from Oracle to PostgreSQL (Jean-Paul Argudo)

== Talks: Thursday 31th October, 2013 ==

=== Earlsfort ===

* Taking advantage of custom background workers (Michael Paquier)
* Doing PITR Right - There's more than just replication (Stephen Frost)
* [https://speakerdeck.com/craigkerstiens/postgres-what-they-really-use slides Postgres what they really use] ([http://www.craigkerstiens.com Craig Kerstiens])
* Indexes: The neglected performance all-rounder (Markus Winand)
* Migrating from MySQL to PostgreSQL (Dimitri Fontaine)
* Lightning talks (Harald Armin Massa)

=== Leeson ===

* Gotcha! Finer points of the postgres query planner and how to use them in your queries (Atri Sharma)
* [https://wiki.postgresql.org/images/a/ad/Materialised_views_now_and_the_future-pgconfeu_2013.pdf Materialised views now and the future] (Thom Brown)
* [http://www.slideshare.net/davidfetter/federation-with-foreigndatawrapperspgconfeu20131031 Federation with Foreign Data Wrappers] (David Fetter)
* Switching Horses Overnight - a Software Manufacturer's View on Replacing an Existing Database Management System with PostgreSQL (Robert Lichtenberger, Stefan Andreatta)
* My experience with embedding PostgreSQL (Jignesh Shah)

=== Pembroke ===

* pgBadger v.4 (Jean-Paul Argudo)
* Business Intelligence & Performance (Simon Riggs)
* [https://speakerdeck.com/peterg/concurrency-in-postgres Concurrency in Postgres] (Peter Geoghegan)
* Next generation of GIN (Alexander Korotkov, Oleg Bartunov) [http://www.sai.msu.su/~megera/postgres/talks/Next%20generation%20of%20GIN.pdf Slides(pdf)]
* [https://speakerdeck.com/leinweber/visualizing-postgres Visualizing Postgres] ([http://bitfission.com Will Leinweber])

=== Lightning talks ===

* [http://www.slideshare.net/davidfetter/slides-pg-confeu20131031 Slides] (David Fetter)
* [https://wiki.postgresql.org/images/1/1c/Json-and-speed.pdf JSON & Speed] (Jonathan S. Katz)
* [https://wiki.postgresql.org/images/a/ab/Pganalyze_Lightning_talk.pdf pganalyze - Monitoring for Casual DBAs] (Michael Renner

== Talks: Friday 1st November, 2013 ==

=== Earlsfort ===

* Epic fails in the RDBMS world (Willem Leenen)
* Using Postgres FDW w/ a sharded PostgreSQL Database (Stephen Frost)
* Postgres versus an anonymous database from Redwood (Gregory Stark)
* Hierarchies Without Masters (Gianni Ciolli)
* PostgreSQL Futures (Simon Riggs)
* PostgreSQL in 5 years - Expectations from the market place (Keith Alsheimer)
* Closing (Dave Page, Magnus Hagander)

=== Leeson ===

* Pagination done the PostgreSQL way (Markus Winand)
* Postgres from Vision to Reality, ABNAMRO Client case (Michel Sijmons)
* PostgreSQL Backups, the good the bad and the ugly (Joshua D. Drake)
* Elephants in fashion (Valentine Gogichashvili)

=== Pembroke ===

* Agile Oracle to PostgreSQL migrations (Gabriele Bartolini)
* Useful PostgreSQL Extensions -- the extensions behind the scenes (Devrim GÜNDÜZ)
* Binary storage for nested data structures and application to hstore data type. (Oleg Bartunov, Teodor Sigaev), [http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf Slides(pdf)]
* Wiretapping the Wire Protocol: automatic data visualization for psql with Cartographer and FEMEBE (Maciek Sakrejda)

File:Pganalyze Lightning talk.pdf

2013-11-01T14:17:40Z

Robe:

Monitoring

2013-10-31T16:47:54Z

Robe: /* CLI tools */ heading

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== CLI tools ===

==== pg_view ====

<tt>[http://tech.zalando.com/getting-a-quick-view-of-your-postgresql-stats/ pg_view]</tt> is a Python-based tool to quickly get information about running databases and resources used by them as well as correlate running queries and why they might be slow.

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

==== PGObserver ====

[http://zalando.github.io/PGObserver/ PGObserver] is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-31T16:47:29Z

Robe: pg_view

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== CLI tools ===

<tt>[http://tech.zalando.com/getting-a-quick-view-of-your-postgresql-stats/ pg_view]</tt> is a Python-based tool to quickly get information about running databases and resources used by them as well as correlate running queries and why they might be slow.

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

==== PGObserver ====

[http://zalando.github.io/PGObserver/ PGObserver] is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-31T16:41:06Z

Robe: /* Postgres-centric monitoring solutions */ pgobserver

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

==== PGObserver ====

[http://zalando.github.io/PGObserver/ PGObserver] is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-30T14:08:23Z

Robe: /* Postgres-centric monitoring solutions */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

==== pg_statsinfo & pg_stats_reporter ====

[http://pgstatsinfo.projects.pgfoundry.org/ pg_statsinfo] is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

[[Category:Monitoring|!]]
[[Category:Performance]]

User:Robe

2013-10-22T15:36:02Z

Robe: /* Ideas */

= Ad personam =

Michael Renner
http://amd.co.at/

= Scribbles =

== Logging ==

After looking into Syslog per suggestions of pgfouine it seems as if Syslog-Logs are much easier to parse and overall more deterministic than "Standard" Logs.

The main advantages are:

* PID and
* per-backend Linecounter allow clear identification of interleaved/mangled messages
* Per-"chunk" identifier allows to identify lines which belong to the same log event.

=== Standard Syslog Output ===

<pre>
Oct 8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-1] 2008-10-08 20:48:26 UTC ERROR: duplicate key value violates unique constraint "newsgroups_name_key"
Oct 8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-2] 2008-10-08 20:48:26 UTC STATEMENT: insert into newsgroups
Oct 8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-3] (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep)
Oct 8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-4] values($1,'--',$2,$3,0,$4,0,0,$5,$6)
</pre>

=== Standard Logging Output ===

<pre>
2008-10-05 05:29:20 UTC ERROR: duplicate key value violates unique constraint "newsgroups_name_key"
2008-10-05 05:29:20 UTC STATEMENT: insert into newsgroups (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep) values($1,'--',$2,$3,0,$4,0,0,$5,$6)
</pre>

=== Syslog-Emulation ===

With <tt>log_line_prefix = '%t postgres[%p]: [%l-1] '</tt> you can achieve the following output:

<pre>
2008-10-08 21:20:46 UTC postgres[30545]: [12911-1] ERROR: duplicate key value violates unique constraint "newsgroups_name_key"
2008-10-08 21:20:46 UTC postgres[30545]: [12912-1] STATEMENT: insert into newsgroups (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep) values($1
,'--',$2,$3,0,$4,0,0,$5,$6)
</pre>

Which is already much more deterministic than the defaults.

Monitoring

2013-10-22T15:23:28Z

Robe: /* pgwatch */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[http://www.cybertec.at/en/products/pgwatch-cybertec-enterprise-postgresql-monitor/ pgwatch] is a PHP web application which offers interactive graphs for relevant Postgres data.

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-22T15:20:52Z

Robe: /* pganalyze */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

[https://pganalyze.com/ pganalyze] is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

==== pgwatch ====

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-22T15:10:06Z

Robe: /* EnterpriseDB Postgres Enterprise Manager */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

==== pganalyze ====

==== pgwatch ====

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-22T15:00:57Z

Robe: /* Cacti */

== PostgreSQL builtin & contrib ==

=== Statistics collector ===

PostgreSQL collects lots of data on its own and offers it via the <tt>pg_stat(io)_</tt> system views

* Official documentation on the [http://www.postgresql.org/docs/current/static/monitoring-stats.html Statistics Collector]
* [http://www.varlena.com/GeneralBits/107.php Interpreting pg_stat Views]

=== contrib extensions ===

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the <tt>-contrib</tt> packages may need to be installed) and can be activated via the [http://www.postgresql.org/docs/current/static/sql-createextension.html extension interface].

==== pg_stat_statements ====

<tt>[http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements]</tt> tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

==== pg_stat_plans ====

<tt>[https://github.com/2ndQuadrant/pg_stat_plans pg_stat_plans]</tt> extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

==== pgstattuple ====

<tt>[http://www.postgresql.org/docs/current/static/pgstattuple.html pgstattuple]</tt> can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

==== pg_buffercache ====

<tt>[http://www.postgresql.org/docs/current/static/pgbuffercache.html pg_buffercache]</tt> gives you introspection into Postgres' [http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS shared buffers], showing how many pages of which relations are currently held in the cache.

== External projects ==

=== Checkers ===

==== check_postgres ====

<tt>[http://bucardo.org/wiki/Check_postgres check_postgres]</tt> is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

=== Interfaces & collectors ===

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

==== pgsnmpd ====

<tt>[http://pgsnmpd.projects.postgresql.org/ pgsnmpd]</tt> can run as a standalone SNMP server and implements the [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS [http://en.wikipedia.org/wiki/Management_information_base MIB]

This is useful for network management systems which are limited to SNMP protocol.

==== pganalyze-collector ====

<tt>[https://github.com/pganalyze/pganalyze-collector pganalyze-collector]</tt> is a tool which collects <tt>pg_stat_plans</tt> query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

=== Generic monitoring solutions with plugins ===

==== Munin ====

PostgreSQL Plugins developed in Perl are included in the Core [http://munin-monitoring.org/ Munin] Distribution. The following plugins are included by default: <tt>postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_</tt>

[http://aouyar.github.com/PyMunin/ PyMunin] includes a Multigraph Munin Plugin written in Python that implements the following graphs: <tt>pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail</tt>

Detailed setup instructions for common Linux platforms can be found at [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ highperfpostgres.com]

==== NewRelic ====

[http://newrelic.com/ NewRelic] is a commercial SaaS application monitoring solution which offers a [https://newrelic.com/plugins/enterprisedb-corporation/30 PostgreSQL plugin] maintained by EnterpriseDB.

==== Cacti ====

There has been work done on building a Postgres template for [http://www.cacti.net/ Cacti], Details can be found at the [[Cacti]] page.

=== Postgres-centric monitoring solutions ===

==== EnterpriseDB Postgres Enterprise Manager ====

==== pganalyze ====

==== pgwatch ====

[[Category:Monitoring|!]]
[[Category:Performance]]

Monitoring

2013-10-22T14:55:43Z

Robe: /* NewRelic */

Monitoring

2013-10-22T14:52:12Z

Robe: /* Munin */

Monitoring

2013-10-22T14:46:37Z

Robe: /* Interfaces & collectors */

Monitoring

2013-10-22T14:39:14Z

Robe: /* check_postgres */

Monitoring

2013-10-22T14:38:34Z

Robe: /* check_postgres */

Monitoring

2013-10-22T14:29:25Z

Robe: new outline

Monitoring

2013-10-22T14:10:44Z

Robe: /* pg_buffercache */ formatting fail

Monitoring

2013-10-22T14:10:24Z

Robe: /* pg_buffercache */

Monitoring

2013-10-22T14:06:23Z

Robe: /* pgstattuple */

Monitoring

2013-10-22T14:03:00Z

Robe: /* pg_stat_plans */

Monitoring

2013-10-22T14:01:16Z

Robe: /* pg_stat_statements */

Monitoring

2013-10-22T13:47:39Z

Robe: Start work on postgres contrib extensions

Shared Database Hosting

2013-06-26T10:08:35Z

Robe: /* PostgreSQL issues that affect shared hosting */ pg_terminate_backend implemented since 8.4, dropping caveat/problem

The objective of this page is to provide a place to exchange ideas on how
best to run PostgreSQL in a shared hosting environment as suggested [http://archives.postgresql.org/pgsql-admin/2008-08/msg00073.php here].
If you are interested in debating the intrinsics of running PostgreSQL under similiar preconditions, please contact me directly by email at '''jacob at internet24 dot de''', as I only check this site rather infrequently and the change notification system doesn't seem to work for me.

== Defining shared hosting ==

For the purpose
of this document, shared database hosting is the process of running PostgreSQL clusters for the benifit of third parties (henceforth called '''customers''') by a DBMS management authority (the '''hosting provider'''). Customers have no organizational relationship
to each other but each one has a relationship to the hosting provider. Customers typically require complete control over a small number of databases but do not want to manage their own PostgreSQL cluster. Sharing the resources of a single cluster system among many customers is economically attractive.

== Goals of a shared hosting setup ==

# Complete isolation of customers from each other
# Standardized procedures for creating and deleting databases and users

== Approaches ==

=== Approach 1 ===

* Manage cluster only via access to a super user account
** no permanent modification of pg_hba.conf etc. required
** central managing system can manage several clusters without the need for additional control channels
* Customers cannot directly create new roles and databases
* Each customer database is given an associated '''main user'''
** acting as a database local super user
** typically creates and owns all objects
* The can be a number of additional users per database
** Rights of these users are granted by the main user

==== pg_hba.conf ====

We allow TCP/IP access to all databases in
the cluster using md5 authentication from
the local hosting network. A role can only
login if it is member of a role that has the
same name as the database.

#host samerole all hosting_network md5
host samerole all 192.168.0.0/24 md5

==== template1 ====

We modify template1 to revoke all rights
from "PUBLIC" to the public schema, to prevent
access to the public schema of indiviudial customer
databases by other customers. Also we add support
for PL/PGSQL.

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

If you deem it acceptable to break certain PostgreSQL
management applications (pgadmin, phppgadmin, parts of psql's functionality),
then you could also revoke some more rights from pg_catalog to "resolve" problem
1 in the last section of this document:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;

REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

EOT

==== Creating a new database + main user ====

We create a role without any special access
rights named after our planned database name
and a login role for our main user
that becomes a member of the former role. Then
we create the database with the main user
as its owner. Finally we grant all rights
to the public schema in the new database
to the main user

psql -U postgres template1 -f - <<EOT

CREATE ROLE <DBNAME> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE <DBMAINUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBMAINUSERPASS>';
GRANT <DBNAME> TO <DBMAINUSER>;
CREATE DATABASE <DBNAME> WITH OWNER=<DBMAINUSER>;
REVOKE ALL ON DATABASE <DBNAME> FROM public;

EOT

psql -U postgres <DBNAME> -f - <<EOT

GRANT ALL ON SCHEMA public TO <DBMAINUSER> WITH GRANT OPTION;

EOT

==== Create an additional user ====

psql -U postgres <DBNAME> -f - <<EOT

CREATE ROLE <DBEXTRAUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBEXTRAUSERPASS>';
GRANT USAGE ON SCHEMA public TO <DBEXTRAUSER>;
GRANT CONNECT,TEMPORARY ON DATABASE <DBNAME> TO <DBEXTRAUSER>;
GRANT <DBNAME> TO <DBEXTRAUSER>;

EOT

==== Remove an additional user ====

psql -U postgres <DBNAME> -f - <<EOT

-- REMOVE ALL PERMISSIONS FROM ALL OBJECTS OWNED BY DBEXTRAUSER
-- TERMINATE CONNECTIONS OF DBEXTRAUSER
REASSIGN OWNED BY <DBEXTRAUSER> TO <DBMAINUSER>;
DROP ROLE <DBEXTRAUSER>

EOT

==== Remove a database + main user ====

psql -U postgres template1 -f - <<EOT

-- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
DROP DATABASE <DBNAME>;
DROP ROLE <DBMAINUSER>
DROP ROLE <DBNAME>

EOT

== PostgreSQL issues that affect shared hosting ==

This is meant to be a list of problem areas where the current
PostgreSQL version leaves something to be desired with respect to
shared hosting requirements.

# pg_catalog allows access to database and user names for everyone, however access to this schema cannot be disabled without breaking essential features
# no support for limiting database sizes
# database local usernames are not fully supported yet (md5 auth)
# removing all rights granted to user (prerequisite to deleting that user) must be done for each single object (no "REASSIGN" equivalent)
# No built-in support for setting [[Priorities|user, query or database priorities]]

[[Category:Administration]]

Row-security

2013-03-07T15:26:57Z

Robe: /* Row-Level Security */ reference commitfest entry

= Row-Level Security =

This page is for discussing the implementation of Row-Level Security (RLS) in PostgreSQL. You can find the current progress of this proposal at the [https://commitfest.postgresql.org/action/patch_view?id=874 commitfest page].

RLS is a security feature which allows users to give access to a sub-set of data in their table to others. Traditional RDBMS permission systems don't distinguish the individual rows in a table- GRANT SELECT on a table will allow a user to access all rows of that table. With RLS, requirements such as "a manager can only view sensetive employee information for those employees who report to them." can be specified and enforced by the database.

== Jobs of access control feature ==

According to the definition of ISO/IEC27001 (information security management), design target of information security feature is to ensure confidentiality, integrity and availability of information asset. In short, these are often called C.I.A.
Access control contributes towards both confidentiality and integrity. Access controls prevent unprivileged users from reading or writing information asset base on the rules which are configured in the access control system. Information or data itself does not have a particularly tangible form and therefore it must be stored in an object. Usually, access control features allow or prohibit users access to the object that contains the information or data. The intent of this feature (RLS) is to allow a more fine-grained control over the information inside of the objects.
For example, regular GRANT/REVOKE mechanisms control access on the specified database object according to the access control list, but they do not allow anything more granular.

== Design Target ==

The purpose of row-level security feature is to prevent users (not means database roles, just users) from accessing unprivileged rows. Please note that "access" means two different direction of information (1) data read (rows => users) for confidentiality, and (2) data write (users => rows) for data integrity.
Due to the nature of database access, it is the most straight-forwards way to describe the rule with regular qualifier style of WHERE clause; that is an expression returning a boolean value.

Overall, RLS prevents users from reading and writing rows that do not satisfy the rule (also know as a row-security policy). If we support per-command configuration, the row-security policy to be checked depends on the command. RLS design accepts individual row-security policy to be applied on SELECT, INSERT, UPDATE or DELETE. Relevant discussions are below.

We have some SQL commands that allow users to access database rows; SELECT, INSERT, UPDATE or DELETE. COPY TO/FROM is synonym of SELECT and INSERT from security perspective.
In case of SELECT (data read), what we should do is quite simple: any rows that don't match with the configured row-security policy shall be filtered out for unprivileged users.
In case of INSERT or UPDATE (data write), RLS prevents unprivileged rows from being written to the table, as if CHECK constraint performing.
In case of UPDATE or DELETE (data write), RLS prevents unprivileged rows from appearing as candidates of modification; that means row-security policy should be applied on the table scan stage. We also need to pay attention on potential information leaks using the leaky-view scenario below. So, UPDATE and DELETE shall also take row-security checks of SELECT command on table scanning stage.

TRUNCATE command performs as if DELETE, but much faster. It has its own permission separated from DELETE. So, we re-define meaning of TRUNCATE permission; that also implies to ignore row-security policy of DELETE.

Row-security policy is set by table owner, using the following syntax.
If no "ON <command>" given, a unique security policy shall be applied on all the supported privileges (SELECT, INSERT, UPDATE and DELETE).

For example, an UPDATE command requires SELECT privilege to read a row, yet UPDATE privilege to change a row.

ALTER TABLE <relname> SET ROW SECURITY FOR <privilege> TO (<expression>);

ALTER TABLE <relname> RESET ROW SECURITY FOR <privilege>;

<privilege> can be one of: ALL, SELECT, INSERT, UPDATE, DELETE. The initial implementation may only support 'ALL'.

'ALL' would simply define the same policy for all the commands. Later commands which update only a single command would replace the policy for that command only, leaving the policy for the other commands to what was set from the initial 'ALL'. The same goes for a RESET. Listing the policies in a table form would look like:

ALTER TABLE mytable SET ROW SECURITY FOR ALL TO (where user = 1);

{| class="wikitable" style="text-align:center; width:200px; height:200px;" border="1"
|+ Example
|-
! table
! command
! expression
|-
| mytable || select || user = 1
|-
| mytable || insert || user = 1
|-
| mytable || update || user = 1
|-
| mytable || delete || user = 1
|}

If it was followed by:

ALTER TABLE mytable SET ROW SECURITY FOR SELECT TO (where user = 2);
the result would be:

{| class="wikitable" style="text-align:center; width:200px; height:200px;" border="1"
|+ Example
|-
! table
! command
! expression
|-
| mytable || select || user = 2
|-
| mytable || insert || user = 1
|-
| mytable || update || user = 1
|-
| mytable || delete || user = 1
|}

and so on. If no row security policy has been set for a given command type (or the security policy has been reset for that command type), no row-level security would apply and regular GRANT permissions for the operation would be used. Note that row-level security does not override existing GRANT permissions but provide a more fine-grained control. For example, setting 'ROW SECURITY FOR SELECT' to allow a given user to give rows would only give that user access if the user ALSO has 'SELECT' privileges on the column or table in question.

Superuser bypass row-security policy (1) to avoid Trojan-horse attack by table owner (2) to ensure consistent view for database backup, however, row-security policy injected by extension (such as sepgsql) is exception.

== Issues & discussion ==

=== Per-command security policy ===
Asymmetric row-security policy may cause unexpected information leaks using UPDATE or DELETE command with RETURNING clause or leaky functions in WHERE clause.
It is an idea to append row-security policy of SELECT when executor scan the result relation. It ensures the rows to be modified are also visible to the user executing the UPDATE.
One other idea was to enforce a unique policy for all commands, however, it has a problematic scenario when user wants to define INSERT-only relation.

=== Writer-side checks ===
Now we can implement writer-side checks on INSERT or UPDATE command using before-row triggers.
On the other hand, it makes users to synchronize the configuration of RLS with triggers of this checks. One model case to solve this concern is implementation of FK constraints; that automatically defines before-row triggers that checks newer version of tuples to be inserted or updated.

=== Table statistics ===
pg_statistic hold some example of values on the table being analyzed.
Right now, we have no way to prevent users to see collected values on statistical board.
An idea is to mask the field if the relation has RLS policy.

=== Minimal core feature set ===
* Checks are only applied on table scanning. If writer-side checks are required, users '''can''' do that using triggers, even though it takes complex setting.
* A unique security policy can be configurable on a table. Even though RLS design allows per-command policy, we need to investigate whether asymmetric policy is harmless.

= Previous discussion in 2010 =

== Prerequisites ==
* Before we can try to tackle row-level security generally, using labels or not, we need to fix the issues with data leaks in views.

* Related topics in -hackers
** [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00014.php Thread to discuss the way to tackle this problem]
** [http://archives.postgresql.org/pgsql-hackers/2009-10/msg01346.php Thread on information leak due to views]
** [http://archives.postgresql.org/pgsql-hackers/2009-02/msg00861.php Earlier discussion of view leak]

=== Issue: A leaky VIEWs for RLS ===

* This issue is summarized as: an untrusted user can define a function which stores all information it is presented, then query a view using that function in a way which will convince the planner to send every row of the underlying table to the function, thus leaking data in the table which the view was intended to prevent.

In [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00014.php this message], KaiGai pointed out we have two different causes of the problem, but both of them can cause same information leaks.
* [1] Unexpected order to evaluate qualifiers on a certain scan plan
** When a scan-plan has multiple qualifiers to filter scanned tuples, the optimizer sort the qualifiers based on their estimated cost to execute. If an exogenetic function has smaller cost than other qualifiers come from view definition, the exogenetic function shall be evaluated earlier than others, then contents of invisible tuples may be provided to malicious user-defined functions.
** It is reordered at <tt>order_qual_clauses()</tt>.
* [2] Unexpected qualifier distributions into inside of join loop
** When planner makes a scan plan, it tries to distribute qualifiers of scans into smaller unit as possible as it can. For example, if a function takes arguments only come from a certain table, it will be distributed into scan plan of the table, not outside of the join.
** When a view contains JOIN clause between A and B, user can reference the view with his own WHERE clause. If a clause takes arguments depending on only A, the planner distribute the clause into the scan plan of A. In the reault, tuples to be filtered out may become visible to user defined functions.
** It is distributed at <tt>distribute_qual_to_rels()</tt>.

=== Discussion ===

* At the point [2], if we prevent all exogenetic functions to push down into join loops, it will make unignorable performance degradation, although the view might not be intended to security purpose.
* It needs a way to provide a hint whether the view is defined for security purpose, or not.
** Tom Lane [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00033.php suggested] <tt>CREATE SECURITY VIEW AS ...</tt> statement.
** It was not concluded which is the default. Security view? Regular view?
** How about a GUC option to specify the default? NACKed.
* In addition, Robert Haas [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00053.php suggested] to test privileges of users whether they have privileges to reference underlaying tables without vires, or not. If available, it is eventually harmless even if user defined functions are evaluated within join loop.
** Here was one opposition because this check will be applied on planner stage, not execution stage.
* KaiGai submitted a [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00150.php proof of concept patch] that prevents to push down exogenetic functions into security views.

* At the point [1], we don't have any active discussions yet.

== Discussions of RLS in PG ==
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg01095.php Current thread on -hackers]
* [http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732 Josh Berkus on RLS in PG, Part 1]
* [http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-2-30757 Josh Berkus on RLS in PG, Part 2]
* [[SEPostgreSQL_Specifications Specifications for SEPostgreSQL, includes RLS]]

== Articles/Documentation of existing RLS implementations ==
* [http://www.securityfocus.com/infocus/1743 Oracle RLS Article, Part 1]
* [http://www.securityfocus.com/infocus/1744 Oracle RLS Article, Part 2]
* [http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/ Oracle RLS and VPD Article]
* [http://msdn.microsoft.com/en-us/library/cc966395.aspx SQL Server RLS with Classified Systems]
* [http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.admin/db2z_implementmls4row.htm IBM/DB2 RLS Documentation]
* Other?

== Use Cases ==
* PCI Compliant implementations
* Classified Environments
* Other?

== Components of an implementation ==
* Overview
** Allow the query to be modified, prior to being passed to the planner, in such a way that the rows returned will be those the user is authorized for
** This depends on being able to tell the planner that this filter must be done, in some way, prior to user-defined functions being called
** This issue is related to the VIEW leak described above. Once that issue has been resolved, this should be pretty straight-forward to implement
* Grammar for catalog updates/changes; user-interface for specifying how RLS is to be done
* Catalog changes for storing RLS information
* Storage - Could this just be a regular column in the table? It would be good to avoid changing the header or creating a system column for this.
** We would need to track, in some fashion, the "security" column in the catalog, perhaps as a flag on pg_attribute, or a 'security_attnum' in pg_class, etc.
* Planner updates to enforce the filter based on RLS- this can't be done till after we deal with the issue with VIEWs
* Executor changes may not be required.. but how to deal with stored plans? Use invalidation if anything changes with regard to RLS?
* Other?

== Issues ==
* Covert Channel
** If we try to insert a value which violates a PK constraint, we can assume existence of invisible PK from the error.
** The same issue exists in a Foreign Key relationship situation
** Other databases with row-level security don't address this issue, so it's unclear if we really need to (reference?)
** In any case, this isn't something we need to address in our initial RLS implementation

* Order to evaluate row level policy
** Addressed above with regard to views, once we solve that, this will be handled

* TRUNCATE statement
** TRUNCATE is expected to be fast.
** If the user does not have rights to remove all rows from the table regardless of row-level policy, then any TRUNCATE must be denied.
** Turning TRUNCATE (a PostgreSQL extension which is not in the SQL spec anyway) into a DELETE FROM doesn't make sense.

* COPY TO statement
** COPY can just be reformed into a COPY statement with a query being used instead, eg:
** <nowiki>COPY tblname TO xxx;</nowiki> can be replaced by <nowiki>COPY (SELECT * FROM tblname) TO xxx;</nowiki>.

* Table inheritance
** Not something we really need to deal with in the initial version, so long as it doesn't completely break (or we make sure it does for inheritance)
** What policy should be applied on the parent and child relations.
** idea: Also copy row-level policies from the parent tables.

* Foreign Key constraint
** Adding multiple modes for FK should be a separate, follow-on patch, it doesn't need to be in the initial version.
** idea: We can provide two modes: The first is filter-mode, the second is abort-mode.
*** filter-mode: A normal mode. Row-level policy is evaluated earlier than user given condition, and returns false, if violated.
*** abort-mode: A special internal mode. Row-level policy is evaluated after all the condition, and raises an error, if violated. The condition shall be evaluated earlier than row-level policy, the query has to be trusted. Such as queries in FK constraint.

== Considerations ==
* Performance
** With RLS
** Without RLS
** The page.16 of [http://sepgsql.googlecode.com/files/JLS2009-KaiGai-LAPP_SELinux.pdf LAPP/SELinux slides] shows a pgbench comparison between pgsql-8.4.1 vs SE-PostgreSQL with RLS.
*** It has 2-4% of performance tradeoff depending on database size.
*** Note that SE-PostgreSQL implemented RLS in a different way than what is being proposed here; our goal is to get RLS support into core PG
* Integration with external security manager (eg: SELinux, SMACK)
** This will not be included in the initial support of RLS (unless we happen to get support for external security managers implemented first in PG)

[[Category:SELinux]]

What's new in PostgreSQL 9.2

2012-03-20T13:37:44Z

Robe: fix version numbers in headline

{{Languages}}

This document showcases many of the latest developments in PostgreSQL 9.2, compared to the last major release – PostgreSQL 9.1. There are many improvements in this release, so this wiki page covers many of the more important changes in detail. The full list of changes is itemised in ''Release Notes''.

'''This page is incomplete!'''

=Major new features=

==Index-only scans==
Index-only scans is a new performance feature whereby PostgreSQL can skip the heap visibility check if the index contains all necessary columns, for pages that are known to be all-visible. This feature is similar to '''covering indexes''' in other database systems, although the implementation is different. (More info: [http://www.depesz.com/2011/10/08/waiting-for-9-2-index-only-scans/ depesz blog])

In previous PostgreSQL versions, all matching index rows in an index scan had to consult the table heap for visibility information. In version 9.2, an index-only scan first checks the smaller [http://www.postgresql.org/docs/devel/static/storage-vm.html visibility map] to see whether all the rows on the particular page are visible. If true, the table heap fetch can be skipped. VACUUM is responsible for setting the visibility map bits.

This required making visibility map changes crash-safe, so visibility map bit changes are now WAL-logged.

==Cascading replication==
Streaming replication slaves can now serve as a source for other slaves. This can be used to reduce the impact of replication on the master server. (More info: [http://www.depesz.com/2011/07/26/waiting-for-9-2-cascading-streaming-replication/ depesz blog])

A related feature, the pg_basebackup command now also works from slaves (More info: [http://www.depesz.com/2012/02/03/waiting-for-9-2-pg_basebackup-from-slave/ depesz blog])

==Multi-processor scalability improvements==
The lock contention of several big locks has been significantly reduced, leading to better multi-processor scalability. (More info: [http://rhaas.blogspot.com/2011/09/scalability-in-graphical-form-analyzed.html Robert Haas blog])

==JSON datatype==
The JSON datatype is meant for storing JSON-structured data. (More info: [http://www.depesz.com/2012/02/12/waiting-for-9-2-json/ depesz blog])

=Performance improvements=

* The performance of in-memory sorts has been improved by up to 25% in some situations, with type-specific specializations. (More info: [http://momjian.us/main/blogs/pgblog/2012.html#February_16_2012 Bruce Momjian's blog])

* An idle PostgreSQL server now makes less wakeups, leading to lower power consumption ([http://pgeoghegan.blogspot.com/2012/01/power-consumption-in-postgres-92.html Peter Geoghegan's blog])

* Timing can now be disabled with EXPLAIN (analyze on, timing off), leading to lower overhead on platforms where getting the current time is expensive ([http://www.depesz.com/2012/02/13/waiting-for-9-2-explain-timing/ depesz blog])

[[Category:PostgreSQL 9.2]]

LDAP Authentication against AD

2012-01-30T15:37:13Z

Robe: drop ownership info, this is a wiki

== Howto SSL enable Postgresql LDAP Authentication against Active Directory ==

The following instruction applies to Redhat RPM installation.

# '''Download SRPMs from website and rebuild with --with-ldap flag.'''
#:The binary release from postgresql website does not include LDAP authentication support. If you want LDAP support, you will need build it from the source with a flag: --with-ldap. If you are deploying it to Redhat 4, you can download the source RPM from srpm folder of the release. You can simply add --with-ldap flag into the RPM specification file.
# '''Install all the needed rpms as root.'''
#:<pre>
#:postgresql-8.2.3-1PGDG.i386.rpm
#:postgresql-libs-8.2.3-1PGDG.i386.rpm
#:postgresql-server-8.2.3-1PGDG.i386.rpm
#:</pre>
# '''Fix a permission on directory:'''
#:<pre>
#:chmod 755 /usr/share/pgsql/timezonesets/
#:</pre>
#'''Initialize the database'''
#:<pre>
#:/sbin/service postgresql initdb
#:</pre>
#'''Modify client authentication configuration by editing /var/lib/pgsql/data/pg_hba.conf'''
#:<pre>
#: # For testing, allow any local user without a password
#: # Remote connection for admin and postgres users require a password. Everybody else use LDAP
#: # TYPE DATABASE USER CIDR-ADDRESS METHOD
#: # "local" is for Unix domain socket connections only
#: local all all trust sameuser
#: # IPv4 local connections:
#: host all all 127.0.0.1/32 trust sameuser
#: # IPv6 local connections:
#: host all all ::1/128 trust sameuser
#: # Remote TCP/IP connection
#: host all postgres,admin 172.20.0.0/16 password
#: host all all 172.20.0.0/16 ldap "ldaps://172.20.13.130/basedn;cn=;,cn=users,dc=concert,dc=music"
#:</pre>
#*In this configuration, we assume all users are under cn=users,dc=concert,dc=music. Otherwise, user Name defined inside postgresql needs to include ou:
#*:<pre>
#*:Jzw,ou=dev
#*:</pre>
#*Note that basedn is ignored in the Postgresql 8.2.3 build, but you will have to put it there in order for it to work. See bug report reference: 3095.
#*It is important to use double-quote around ldap url.
#*LDAP authentication only verifies user credentials from AD, but the user has to be pre-created inside Postgresql.
#'''Update /var/lib/pgsql/data/postgresql.conf'''
#* Listen to connections on all IP addresses
#*:<pre>
#*:listen_addresses ='*'
#*:</pre>
#*Enable SSL
#*:<pre>
#*:Ssl=on
#*:</pre>
#'''Generate self-signed key and certificate:'''
#*This is needed for SSL communication between client with DB. Follow the directions on the [http://www.postgresql.org/docs/current/static/ssl-tcp.html Postgresql official documentation.]
#'''Create database'''
#:<pre>
#:createdb mydb
#:</pre>
#'''Make LDAPs to work with AD'''
#*For the test, you may want to create your self-signed certificate on AD:
##Create Root Certificate Authority
##*If you are importing a certificate from Verisign, skip this part and go to part 2 (Import server cert into AD).
###Start the Control Panel '''Add/Remove Programs''' applet.
###Click '''Add/Remove Windows Components''' to start the Windows Components wizard.
###Click Next when the welcome screen appears.
###When the list of components displays, select the '''Certificate Services''' checkbox and click Next.
###Select type '''Enterprise root CA''', and click Next.
###Enter a CA name and other information about the organization, as the screen shows. Click Next.
###Accept the default location for the certificate database (i.e., %systemroot%\System32\CertLog), and click Next.
###If Microsoft IIS is running, the service will stop and a dialog box will display. Click OK.
###A list of files to copy will generate, and the files will install. Service and system configurations will also install. You might need to insert the Windows 2000 Server CD-ROM.
###When the wizard completes, click '''Finish'''.
##Import server certificate into Active Directory
###Open '''Default Group Policy''' editor. Navigate to Computer configuration->windows settings->security settings->Public key policies->Trusted root certificate authorities.
###Right click on '''Trusted root certificate authorities''' and choose import.
###Click on Next and browse to the certificate (.crt file) issued by CA. Click on open.
##Export CA from Active Directory
###Click on '''Trusted root certificate authorities''' to open the folder
###Right click on the CA you want to export and choose open
###Click on '''Details''' tab and click on copy to file button.
###You will see Certificate export wizard. Click on '''Next'''.
###Choose the format to export. For use with OpenSSL (OpenLDAP), choose '''base-64 encoded X.509''' format.
###Copy the file to appropriate location on Redhat 4 server: '''/etc/openldap/concerto.cer'''
##Configure LDAPS connection to Active Directory
##*Configure '''ldap.conf''' and add the following lines:
##*:<pre>
##*: TLS_CACERT /etc/openldap/concerto.cer
##*: TLS_CACERTDIR /etc/openldap/
##*: # TLS_REQCERT never
##*:</pre>
##Start DB
##:<pre>
##: service postgresql start
##:</pre>
#'''Try to connect DB remotely:'''
#* On Windows client, download win32 binary from http://www.postgresql.org. It also includes a .NET library for integrating with .NET client software. Install only psql package.
#* Login using password of AD user jzw:
#*:<pre>
#*:psql -d mydb -h 172.20.x.x. -U jzw -W
#*:</pre>
#*Make sure one see this line to verify that SSL connection is established between client and DB
#*:<pre>
#*:SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256).
#*:</pre>
#*To verify if SSL is working between Postgresql and AD, you can run tcpdump to see if content is encrypted. I found that psql client does not verify the certificate. Not sure if that is a bug or a default settings.

Streaming Replication

2011-09-28T09:58:09Z

Robe: reworded archive_mode necessity

'''Streaming Replication''' (SR) provides the capability to continuously ship and
apply the [http://www.postgresql.org/docs/current/static/wal.html WAL XLOG] records to some number of standby servers in order to keep them current.

= Project =
SR was developed for inclusion in PostgreSQL 9.0 by NTT OSS Center. The lead developer is [mailto:masao.fujii@gmail.com Masao Fujii]. [http://www.pgcon.org/2008/schedule/events/76.en.html Synchronous Log Shipping Replication Presentation] introduces the early design of the feature.

The feature is now committed to CVS and is included in PostgreSQL 9.0 Alpha4 and later.

= Usage =
== Users Overview ==
* '''Log-shipping'''
** XLOG records generated in the primary are periodically shipped to the standby via the network.
** In the existing warm standby, only records in a filled file are shipped, what's referred to as file-based log-shipping. In SR, XLOG records in partially-filled XLOG file are shipped too, implementing record-based log-shipping. This means the window for data loss in SR is usually smaller than in warm standby, unless the warm standby was also configured for record-based shipping (which is complicated to setup).
** The content of XLOG files written to the standby are exactly the same as those on the primary. XLOG files shipped can be used for a normal recovery and PITR.
* '''Multiple standbys'''
** More than one standby can establish a connection to the primary for SR. XLOG records are concurrently shipped to all these standbys. The delay/death of a standby does not harm log-shipping to other standbys.
** The maximum number of standbys can be specified as a GUC variable.
* '''Continuous recovery'''
** The standby continuously replays XLOG records shipped without using pg_standby.
** XLOG records shipped are replayed as soon as possible without waiting until XLOG file has been filled. The combination of [[Hot Standby]] and SR would make the latest data inserted into the primary visible in the standby almost immediately.
** The standby periodically removes old XLOG files which are no longer needed for recovery, to prevent excessive disk usage.
* '''Setup'''
** The start of log-shipping does not interfere with any query processing on the primary.
** The standby can be started in various conditions.
*** If there are XLOG files in archive directory and restore_command is supplied, at first those files are replayed. Then the standby requests XLOG records following the last applied one to the primary. This prevents XLOG files already present in the standby from being shipped again. Similarly, XLOG files in pg_xlog are also replayed before starting log-shipping.
*** If there is no XLOG files on the standby, the standby requests XLOG records following the starting XLOG location of recovery (the redo starting location).
* '''Connection settings and authentication'''
** A user can configure the same settings as a normal connection to a connection for SR (e.g., keepalive, pg_hba.conf).
* '''Activation'''
** The standby can keep waiting for activation as long as a user likes. This prevents the standby from being automatically brought up by failure of recovery or network outage.
* '''Progress report'''
** The primary and standby report the progress of log-shipping in PS display.
* '''Graceful shutdown'''
** When smart/fast shutdown is requested, the primary waits to exit until XLOG records have been sent to the standby, up to the shutdown checkpoint record.

== Restrictions ==
* '''Synchronous log-shipping'''
** Currently SR supports only asynchronous log-shipping. The commit command might return a "success" to a client before the corresponding XLOG records are shipped to the standby.
* '''Replication beyond timeline'''
** A user has to get a fresh backup whenever making the old standby catch up.
* '''Clustering'''
** Postgres doesn't provide any clustering feature.

== How to Use ==
* '''1.''' Install postgres in the primary and standby server as usual. This requires only ''configure'', ''make'' and ''make install''.
* '''2.''' Create the initial database cluster in the primary server as usual, using ''initdb''.
* '''3.''' Set up connections and authentication so that the standby server can successfully connect to the ''replication'' pseudo-database on the primary.
$ $EDITOR postgresql.conf

listen_addresses = '192.168.0.10'

$ $EDITOR pg_hba.conf

# The standby server must have superuser access privileges.
host replication postgres 192.168.0.20/22 trust
* '''4.''' Set up the streaming replication related parameters on the primary server.
$ $EDITOR postgresql.conf

# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5

# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.
wal_keep_segments = 32

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode = on
archive_command = 'cp %p /path_to/archive/%f'
* '''5.''' Start postgres on the primary server.
* '''6.''' Make a base backup by copying the primary server's data directory to the standby server.
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"
* '''7.''' Set up replication-related parameters, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover.
* '''8.''' Enable read-only queries on the standby server. But if wal_level is ''archive'' on the primary, leave hot_standby unchanged (i.e., off).
$ $EDITOR postgresql.conf

hot_standby = on
* '''9.''' Create a recovery command file in the standby server; the following parameters are required for streaming replication.
$ $EDITOR recovery.conf
# Note that recovery.conf must be in $PGDATA directory.

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=192.168.0.10 port=5432 user=postgres'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/path_to/trigger'

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /path_to/archive/%f "%p"'
* '''10.''' Start postgres in the standby server. It will start streaming replication.
* '''11.''' You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using ''pg_current_xlog_location'' on the primary and the ''pg_last_xlog_receive_location''/''pg_last_xlog_replay_location'' on the standby, respectively.
$ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)
pg_current_xlog_location
--------------------------
0/2000000
(1 row)

$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)
pg_last_xlog_receive_location
-------------------------------
0/2000000
(1 row)

$ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)
pg_last_xlog_replay_location
------------------------------
0/2000000
(1 row)
* '''12.''' You can also check the progress of streaming replication by using ''ps'' command.
# The displayed LSNs indicate the byte position that the standby server has
# written up to in the xlogs.
[primary] $ ps -ef | grep sender
postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

[standby] $ ps -ef | grep receiver
postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000
* How to do failover
** Create the trigger file in the standby after the primary fails.
* How to stop the primary or the standby server
** Shut down it as usual (''pg_ctl stop'').
* How to restart streaming replication after failover
** Repeat the operations from '''6th'''; making a fresh backup, some configurations and starting the original primary as the standby. The primary server doesn't need to be stopped during these operations.
* How to restart streaming replication after the standby fails
** Restart postgres in the standby server after eliminating the cause of failure.
* How to disconnect the standby from the primary
** Create the trigger file in the standby while the primary is running. Then the standby would be brought up.
* How to re-synchronize the stand-alone standby after isolation
** Shut down the standby as usual. And repeat the operations from '''6th'''.

= Todo =
== v9.0 ==

Moved to [[PostgreSQL_9.0_Open_Items]]

=== Committed ===
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01455.php Retrying from archive and some refactoring around Read/FetchRecord().] - [http://archives.postgresql.org/pgsql-committers/2010-01/msg00395.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg02601.php SR wrongly treats the WAL-boundary.] - [http://archives.postgresql.org/pgsql-committers/2010-01/msg00396.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01715.php Adjust SR for some later changes about wal-skipping.] - [http://archives.postgresql.org/pgsql-committers/2010-01/msg00399.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00024.php VACUUM FULL unexpectedly writes an XLOG UNLOGGED record.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00038.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01754.php Add a message type header.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00037.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01536.php Documentation: Add a new "Replication" chapter.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00115.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00350.php Failed assertion during recovery of partial WAL file.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00124.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00712.php A PANIC error might occur in the standby because of a partially-filled archived WAL file.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00137.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00330.php Improve the standby messages.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00140.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01672.php pq_getbyte_if_available() is not working because the win32 socket emulation layer simply wasn't designed to deal with non-blocking sockets.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00198.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01488.php Walsender might emit unfit messages.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00239.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01236.php Streaming replication on win32, still broken.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00270.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg00992.php Create new section for recovery.conf.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00295.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01824.php Assertion failure in walreceiver.] - [http://archives.postgresql.org/pgsql-committers/2010-02/msg00356.php commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01717.php Forbid a startup of walsender during recovery, and emit a suitable message? Or allow walsender to be started also during recovery?] - [http://archives.postgresql.org/message-id/20100316090955.9A5107541D0@cvs.postgresql.org commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01003.php How do we clean down the archive without using pg_standby?] - [http://archives.postgresql.org/message-id/20100318091718.BC14D7541D0@cvs.postgresql.org commit]
* [http://archives.postgresql.org/pgsql-hackers/2010-02/msg01510.php File-based log shipping without pg_standby doesn't replay the WAL files in pg_xlog.] - [http://archives.postgresql.org/pgsql-committers/2010-03/msg00356.php commit]

== v9.1 ==
=== Synchronization capability ===
* Introduce the replication mode which can control how long transaction commit waits for replication before the commit command returns a "success" to a client. The valid modes are ''async'', ''recv'' and ''fsync''.
** ''async'' doesn't make transaction commit wait for replication, i.e., asynchronous replication.
** ''recv'' or ''fsync'' makes transaction commit wait for XLOG to be received or fsynced by the standby, respectively.
** (''apply'' makes transaction commit wait for XLOG to be replayed by the standby. This mode will be supported in v9.2 or later)
** The replication mode is specified in recovery.conf of the standby as well as other parameters for replication.
*** The startup process reads the replication mode from recovery.conf and shares it to walreceiver via new shared-memory variable.
*** Walreceiver also shares it to walsender by using the replication handshake message (existing protocol needs to be extended).
** Based on the replication mode, walreceiver sends the reply meaning that replication is done up to the specified location to the primary.
*** In async, walreceiver doesn't need to send any reply other than end-of-replication message.
*** In recv or fsync, walreceiver sends the reply just after receiving or flushing XLOG, respectively.
*** New message type for the reply needs to be defined. The reply is sent as CopyData message.
** Walreceiver writes all the outstanding XLOG to disk before shutting down.
** Walsender receives the reply from the standby, updates the location of the last record replicated, and announces completion of replication.
*** New shared-memory variable to keep that location is required.
** When processing the commit command, backend waits for XLOG to be replicated to only the standbys which are in the recv or fsync replication mode.
*** Also smart shutdown waits for XLOG of shutdown checkpoint to be replicated.
* Required optimization
** Walsender should send outstanding XLOG without waiting wal_sender_delay.
*** When processing the commit command, backend signals walsender to send outstanding XLOG immediately.
** Backend should exit the wait loop as soon as the reply arrives at the primary.
*** When receiving the reply, walsender signals backends to get up from the sleep and determine whether to exit the wait loop by checking the location of the last XLOG replicated.
*** Only backends waiting for XLOG to be replicated up to the location contained in the reply are sent the signal.
** Walsender waits for the signal from backends and the reply from the standby at the same time, by using select/poll.
** Walsender reads XLOG from not only disk but also shared memory (wal buffers).
** Walreceiver should flush XLOG file only when XLOG file is switched or the related page is flushed.
*** When startup process or bgwriter flushes the buffer page, it checks whether the related XLOG has already been flushed via shared memory (location of the last XLOG flushed).
*** It flushes the buffer page, if XLOG file has already been flushed.
*** It signals walreceiver to flush XLOG file immediately and waits for the flush to complete, if XLOG file has not been flushed yet.
** While the standby is catching up with the primary, those servers should ignore the replication mode and perform asynchronous replication.
*** After those servers have almost gotten into synchronization, they perform replication based on the specified replication mode.
*** New replication states like 'catching-up', 'sync', etc need to be defined, and the state machine for them is required on both servers.
*** Current replication state can be monitored on both servers via SQL.
* Required timeout
** Add new parameter replication_timeout which is the maximum time to wait until XLOG is replicated to the standby.
** Add new parameter (replication_timeout_action) to specify the reaction to replication_timeout.

== Future release ==
* '''Synchronization capability'''
** Introduce the synchronization mode which can control how long transaction commit waits for replication before the commit command returns a "success" to a client. The valid modes are ''async'', ''recv'', ''fsync'' and ''apply''.
*** ''async'' doesn't make transaction commit wait for replication, i.e., asynchronous replication.
*** ''recv'', ''fsync'' and ''apply'' makes transaction commit wait for XLOG records to be received, fsynced and applied on the standby, respectively.
** Change walsender to be able to read XLOG from not only the disk but also shared memory.
** Add new parameter replication_timeout which is the maximum time to wait until XLOG records are replicated to the standby.
** Add new parameter (replication_timeout_action) to specify the reaction to replication_timeout.
* '''Monitoring'''
** Provide the capability to check the progress and gap of streaming replication via one query. A collaboration of HS and SR is necessary to provide that capability on the standby side.
** Provide the capability to check if the specified repliation is in progress via a query. Also more detailed status information might be necessary, e.g, the standby is catching up now, has already gotten into sync, and so on.
** Change the stats collector to collect the statistics information about replication, e.g., average delay of replication time.
** Develop the tool to calculate the latest XLOG position from XLOG files. This is necessary to check the gap of replication after the server fails.
** Also develop the tool to extract the user-readable contents from XLOG files. This is necessary to see the contents of the gap, and manually restore them.
* '''Easy to Use'''
** Introduce the parameters like:
*** replication_halt_timeout - replication will halt if no data has been sent for this much time.
*** replication_halt_segments - replication will halt if number of WAL files in pg_xlog exceeds this threshold.
*** These parameters allow us to avoid disk overflow.
** Add new feature which transfers also base backup via the direct connection between the primary and the standby.
** Add new hooks like walsender_hook and walreceiver_hook to cooperate with the add-on program for compression like pglesslog.
** Provide a graceful termination of replication via a query on the primary. On the standby, a trigger file mechanism already provides that capability.
** Support replication beyond timeline. The timeline history files need to be shipped from the primary to the standby.
* '''Robustness'''
** Support keepalive in libpq. This is useful for a client and the standby to detect a failure of the primary immediately.
** [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01536.php New privilege for replication.]
*** Currently superuser privilege is required when the standby connects to the primary. But there is the complaint that we should add new privilege for replication and use it instead of superuser because current approach is not good for security.
* '''Miscellaneous'''
** Standalone walreceiver tool, which connects to the primary, continuously receives and writes XLOG records, independently from postgres server.
** Cascade streaming replication. Allow walsender to send XLOG to another standby during recovery.
** WAL archiving during recovery.

[[Category:Replication]]

PGUG EU Media Coordination

2010-06-26T16:59:02Z

Robe:

This is an overview of publications where we want to have regular PostgreSQL exposure. Feel free to extend the list with interesting Publications in your area.

= Publications =

{|
| align="center" style="background:#f0f0f0;"|'''Publication'''
| align="center" style="background:#f0f0f0;"|'''Publisher'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Language'''
| align="center" style="background:#f0f0f0;"|'''Target audience'''
| align="center" style="background:#f0f0f0;"|'''Contact'''
|-
| [http://www.linux-magazin.de/ Linux Magazin] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Monthly Magazine || German || Professional/Commercial FOSS Users || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.admin-magazin.de/ Admin Magazin] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Monthly Magazine || German || IT-Administrators || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.linuxtechnicalreview.de/ Linux Technical Review] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Subscriber-only Online Publication || German || Architects & Decision Makers || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.heise.de/ix/ iX] || [http://www.heise-medien.de/default.php/mediengruppe,zeitschriften/11/2 Heise Zeitschriften Verlag GmbH & Co. KG] || Monthly Magazine || German || IT-Administrators || FIXME
|-
| [http://entwickler-magazin.de/ Entwickler Magazin] || [http://software-support.biz/ Software & Support Verlag GmbH] || Monthly Magazine || German || Developers? || FIXME
|-
|}

= Published articles =

{|
| align="center" style="background:#f0f0f0;"|'''Publication'''
| align="center" style="background:#f0f0f0;"|'''Title'''
| align="center" style="background:#f0f0f0;"|'''Autor'''
| align="center" style="background:#f0f0f0;"|'''Date/Issue'''
|-
|Linux Magazin || FIXME || Andreas Scherbaum || FIXME
|-
|}

PGUG EU Media Coordination

2010-06-26T16:58:39Z

Robe: New page: This is an overview of publications where we want to have regular PostgreSQL exposure. Feel free to extend the list with interesting Publications in your area. = Publications = {| | alig...

This is an overview of publications where we want to have regular PostgreSQL exposure. Feel free to extend the list with interesting Publications in your area.

= Publications =

{|
| align="center" style="background:#f0f0f0;"|'''Publication'''
| align="center" style="background:#f0f0f0;"|'''Publisher'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Language'''
| align="center" style="background:#f0f0f0;"|'''Target audience'''
| align="center" style="background:#f0f0f0;"|'''Contact'''
|-
| [http://www.linux-magazin.de/ Linux Magazin] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Monthly Magazine || German || Professional/Commercial FOSS Users || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.admin-magazin.de/ Admin Magazin] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Monthly Magazine || German || IT-Administrators || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.linuxtechnicalreview.de/ Linux Technical Review] || [http://www.linuxnewmedia.de/ Linux New Media AG] || Subscriber-only Online Publication || German || Architects & Decision Makers || [mailto:jbrendel@linuxnewmedia.de Jens-Christoph Brendel]
|-
| [http://www.heise.de/ix/ iX] || [http://www.heise-medien.de/default.php/mediengruppe,zeitschriften/11/2 Heise Zeitschriften Verlag GmbH & Co. KG] || Monthly Magazine || || German IT-Administrators || FIXME
|-
| [http://entwickler-magazin.de/ Entwickler Magazin] || [http://software-support.biz/ Software & Support Verlag GmbH] || Monthly Magazine || German || Developers? || FIXME
|-
|}

= Published articles =

{|
| align="center" style="background:#f0f0f0;"|'''Publication'''
| align="center" style="background:#f0f0f0;"|'''Title'''
| align="center" style="background:#f0f0f0;"|'''Autor'''
| align="center" style="background:#f0f0f0;"|'''Date/Issue'''
|-
|Linux Magazin || FIXME || Andreas Scherbaum || FIXME
|-
|}

PGUG EU Conference Coordination

2010-06-26T16:27:22Z

Robe: /* Completed Events */

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Wiener Linuxwochen||Wien, Austria||http://www.linuxwochen.at/||||FOSS community conference||||Weekend||Early May||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom with Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| Wiener Linuxwochen||6th - 8th May 2010||Hans-Jürgen Schöning||[http://www.linuxwochen.at/index.php?option=com_content&view=article&id=152&Itemid=69 Talk]||Talk PostgreSQL 9.0
|-
| AMOOCON||4th - 6th June 2010||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June 2010||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-26T16:26:23Z

Robe: /* Completed Events */

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Wiener Linuxwochen||Wien, Austria||http://www.linuxwochen.at/||||FOSS community conference||||Weekend||Early May||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom with Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| Wiener Linuxwochen||6th - 8th May 2010||Hans-Jürgen Schöning||Talk PostgreSQL 9.0
|-
| AMOOCON||4th - 6th June 2010||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June 2010||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-26T16:25:00Z

Robe: /* Conferences */

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Wiener Linuxwochen||Wien, Austria||http://www.linuxwochen.at/||||FOSS community conference||||Weekend||Early May||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom with Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| AMOOCON||4th - 6th June 2010||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June 2010||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-25T12:05:11Z

Robe: /* Planning 2010 */

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom with Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| AMOOCON||4th - 6th June 2010||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June 2010||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-25T11:24:27Z

Robe: /* Completed Events */ added year to completed events

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom & Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| AMOOCON||4th - 6th June 2010||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June 2010||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-25T11:23:48Z

Robe: osdc done, added froscon

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FrOSCon||21st & 22nd Aug||Michael Renner, Andreas Scherbaum||DevRoom & Talks||Project submitted, Talks TBD
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| AMOOCON||4th - 6th June||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|-
| Netways OSDC||23rd & 24th June||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk PostgreSQL Replication with 9.0
|}

PGUG EU Conference Coordination

2010-06-07T06:25:52Z

Robe: amoocon done

= Conferences =

Recurring events in the Euro zone where PostgreSQL activities might be beneficial

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| P2D2||Prague, Czech Rep ||http://www.p2d2.cz/||||PostgreSQL Czech Community conference||||One day||February||Czech
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||~300k Visitors||Tuesday - Saturday||Early March||German
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Solutions Linux||Paris, France||http://www.solutionslinux.fr/||||Trade fair||||Tuesday-Thursday||Mid March||French
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||~100 Attendees||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| AMOOCON||Rostock, Germany||http://www.amoocon.de/||||Commercial conference||~100 Attendees||Friday - Sunday||Early June||German & English
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FrOSCamp||Zurich, Switzerland||http://wiki.froscamp.org/||||FOSS community conference||||Friday & Saturday||Mid September||German? English? French?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| PGDay Fr||France ||http://www.pgday.fr/||||PostgreSQL French-speaking Community conference||||Friday & Saturday||October-November||French
|-
|
|}

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| Netways OSDC||23rd & 24th June||Michael Renner||[http://www.netways.de/osdc/y2010/programm/ Talk]||Talk accepted
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| AMOOCON||4th - 6th June||Michael Renner||[http://www.amoocon.de/speakers/214 Talk]||Talk PostgreSQL 9.0
|}

User:Robe

2010-05-24T21:57:26Z

Robe: