PostgreSQL wiki - User contributions [en]

TPC-H

2024-07-18T19:31:27Z

Gsmith: Update to cover progress in core and commercial work

The [http://www.tpc.org/tpch/ TPC-H Benchmark] is a popular one for comparing database vendors. It's possible to run the TPC-H data set on PostgreSQL without having a formal testing kit, as demonstrated by the [[DBT-3]] project's work to provide a full kit. Commercial database vendors are famously so focused on this benchmark they will "game" TPC-H runs--add optimizations specifically aimed at it--to make absolutely sure they do well.

PostgreSQL is still missing some features needed to seriously compete on this benchmark. The parallel query features added in PostgreSQL 9.6 and later helped pull down run times for several queries, see EDB's [https://wiki.postgresql.org/images/9/92/Parallel-query-in-postgresql.pdf Parallel Query in PostgreSQL] and [https://www.2ndquadrant.com/en/blog/tpc-h-performance-since-postgresql-8-3/ TPC-H performance 8.3-13].

Columnar storage with compression is a well known way to accelerate the TPC-H queries. As covered below, Greenplum's PostgreSQL fork claimed some improved results against PG 8.4. Crunchy claims large improvements over PG 16 with their commercial columnar [https://www.crunchydata.com/blog/running-tcp-h-queries-on-iceberg-tables-from-postgresql Iceberg based extensions].

----

Early reference notes about the benchmark:
* [http://dsl.serc.iisc.ernet.in/projects/PICASSO/picasso_download/doc/Installation/tpch.htm TPC-H data generation and loading]
* [https://web.archive.org/web/20110406233644/http://monetdb.cwi.nl/SQL/Benchmark/TPCH/ TPC-H Performance Study] using an old version of PostgreSQL
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01867.php Discussion of limitations] started by the above
* [http://m.tholis.webnode.com/news/tpc-h-fun-with-greenplum-single-node-edition-/ TPC-H fun with Greenplum (single node edition)] - leads into the below and discusses what's needed to do well on TPC-H
* [https://web.archive.org/web/20100212084437/http://community.greenplum.com/showthread.php?t=113 GP SNE versus Postgres 8.4.2 - 22 Query Benchmark Results] (similar to TPC-H queries, tweaked to run better)
[[Category:Benchmarking]]
* [http://www.slideshare.net/jkshah/postgresql-and-benchmarks PostgreSQL and Benchmarks] TPC-H, TPC-E, and EAStress 2004 review (2008)

BinaryFilesInDB

2020-11-20T01:43:31Z

Gsmith: Cleaned up intro. Added some pros and cons, mainly around filesystem access and WAL streaming. Put BLOB last as the text itself doesn't recommend it.

==Storing Binary files in the Database==

New users to PostgreSQL often ask on the PostgreSQL mailing lists how/if to store binary files like images, Word documents, spreadsheets, HTML, PDF, compressed archives, or even code in the database. There are two prevailing schools of thought on that, each having pluses and minus detailed below:

* Save the binary file to the regular filesystem, then write metadata and some sort of symbolic link in the database to its location.
* Store the large binary file of unstructured data directly in a database field.

Caveat: many people come to this page looking for PostgreSQL's equivalent of the MySLQ "BLOB*" data types. The equivalent in Postgres is BYTEA. For details on PostgreSQL's "binary large object" (which are quite different from MySQL BLOB's and provide random seeking, etc), see below.

==Methods:==

===Storing the large binary* file aka unstructured data streams in a database.===
Postgres along with other databases offer similar basic structures.
* Storing binary data using [https://www.postgresql.org/docs/current/datatype-binary.html bytea] or [https://www.postgresql.org/docs/current/datatype-character.html text] data types
** Pluses
*** Storing and Accessing entry utilizes the same interface when accessing any other data type or record.
*** No need to track OID of a "large object" you create
** Minus
*** bytea and text data type both use [https://www.postgresql.org/docs/current/storage-toast.html TOAST] (details [[TOAST|here]])
**** limited to 1G per entry
**** 4 Billion (> 2KB) entries per table [[TOAST|max]].
*** Need to escape/encode binary data before sending to DB then do the reverse after retrieving the data
*** Memory requirements on the server can be steep even on a small record set.
* BLOB binary large object see [http://www.postgresql.org/docs/current/static/largeobjects.html Large Object Support]
** Pluses
*** limited 4TB (PostgreSQL 9.3+) per entry, & 4 Billion per database
*** Can stream, and seek over entries ( can reduce memory requirements on DB server and client)
*** no encoding or escaping required.
** Minuses
*** must use different interface from what is normally used to access BLOBs.
*** Need to track OID. Normally a separate table with additional meta data is used to describe what each OID is.
*** (8.4 and <8.4) No access controls in database.
*** Sometimes [http://www.postgresql.org/message-id/20150129142450.b83ce5d0652c39791292d0d5@potentialtech.com advised against] (basically you only need them if your entry is so large you need/want to seek and read bits and pieces of it at a time).

Database storage has some common characteristics whichever specific storage you pick:
* Pluses overall
** Security and access control is simplified
** version controlling is easier.
** ACID
** Files saved into a master database will stream to standby replicas automatically.
** Backups are easier no need to track external files
* Minus overall
** Performance hit storing files in database.
** Memory requirements higher, for Database
** Backups can take significantly longer
** Access to files to external applications is complicated. Normally a temporary file is copied to the client to access and modify the file then needs to be copied back.

===Storing Meta data and symbolic link in the database to where a Binary file is located ===
* Pluses
** Performance accessing binary file skips DB access layer.
** Number of files limited only by file system
** Size limited by file system
* Minuses
** The database user needs permissions to write outside of the database. That's not normally an option in hosted PostgreSQL environments.
** Need to develop an interface to keep track of externally attached files
** external files and database can get out of sync
*** binary files can have missing entries in the database
*** database having entries to external files that's been deleted or moved by users or other events out of control of the database.
** Security settings between file system and database are independent from each other. The application may need to may need multiple security logins to access all resources. This is dependent on security needs of the application, and methods used for access control. There are several methods to create a single log on infrastructure.
** Multiple points of failure depending on complexity of the network.

==When should files be stored in the database?==

The common suggestion here is when the files have to be [http://en.wikipedia.org/wiki/ACID ACID].

Example: Photos taken during a Quality Inspection process:

A normal/ordinary record in a database would consist of a Inspector, time stamp of inspection, process used to inspect the part, was the part accepted or rejected, and the parts serial number. In this scenario photos are also taken showing the part inspected. The photos are a integral piece of the inspection record keeping process.

The photos being a critical piece making up the record/inspection report should not to be stored separately. Consider if the photos being stored on file sever are moved to new location as part of upgrade, the link to the record is bad if it was not updated. So now the inspection record by itself is worth less as it lost the piece it is describing and the photos are worthless without the record describing them. So the two pieces must stay together to have any value as a inspection report.

When storing the photos outside the database, there is no easy way to guarantee atomicity, consistency, isolation and durability of the photos. Storing the photos in the database is the only practical solution where the programmer can guarantee rules are followed in the future. The file data will automatically replicate and flow to database backups. And since writing to arbitrary files on the database server requires extra trust, saving to the database may be the only option available or considered safe by your PostgreSQL administrator.

=What is the best way to store the files in the Database?=

There are three methods to choose from in PostgreSQL BLOB, bytea or text data type.

The main author of this article has changed to using bytea exclusively now that Python and pl/Python make it easy to use bytea vs text type.

===bytea===
*data storage requirements are the same as the object being stored in the file system
*Standard SQL statements to insert, update, delete, and select the object
*How easy is it to get the data in and out depends on the programming tools. Python & .Net have the easiest automatic casting to and from languages type to postgres type (depends on db driver). Other languages database access layers require escaping or casting in a separate step before additional processing can be done.
*Server side tools tools to encode or decode into other [https://www.postgresql.org/docs/current/functions-string.html formats]

===text===
*data storage requirements can be 30% more required space than if the file stored in the file system
*Standard SQL statements to insert, update, delete, and select the object
*always has to be encoded and decoded into some format such as (not a complete list)
** [http://en.wikipedia.org/wiki/Base64 Base64] probably the most common encoding method for binary files due to email.
** [http://en.wikipedia.org/wiki/Uuencode uuencoding]
** [http://en.wikipedia.org/wiki/Quoted-printable Quoted Printable]
** [http://en.wikipedia.org/wiki/Ascii85 ASCII85]
this can add significant overhead.
*Server side tools to encode or decode into other [https://www.postgresql.org/docs/current/functions-string.html formats]

===BLOB aka Large Objects===
*Completely different interface has to be use, read through [https://www.postgresql.org/docs/current/largeobjects.html Large-Objects] documentation to have a full understanding.
*Does have advantage of being able to seek over data and stream data out.
*data storage requirements are the same as if saved in the file system
*limited [https://www.postgresql.org/docs/11/lo-funcs.html server side tools] to manipulate the objects.

===What Meta Data should be stored in a database?===
here is a basic list what should be recorded in the database along side the binary file
*original file name
*data type
**this is needed to tell us what kinda of binary file is stored in the database
**the author suggest following the [http://en.wikipedia.org/wiki/Internet_media_type Internet Media Type] it makes it easier to use the same information for a website or attaching to email.
*long description
*date added

==When is it bad idea to store binary files in the database?==

Very large files (100meg+), where performance is critical to the application. The database layer adds a lot of overhead and complexity that may not be required.

[[Category:Howto]]

Monitoring

2017-01-31T21:52:19Z

Gsmith: Shrink the 3 paragraphs of okmeter text to the normal size expected on this page. Remove the excited ad copy as part of that.

{{Languages}}

== 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.

==== pg_activity ====

<tt>[https://github.com/julmon/pg_activity pg_activity]</tt> is a htop like application for PostgreSQL server activity monitoring, written in Python.

==== pgstats ====

<tt>[https://github.com/gleu/pgstats pgstats]</tt> is a command line tool written in C which can sample various PostgreSQL informations. It also provides a tool to generate CSV files to graph the pgstats metrics.
=== 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 ===

====Datadog====
[http://www.datadoghq.com/ Datadog] is a commercial saas that collects postgres metrics on connections, transactions, row crud operations, locks, temp files, bgwriter, index usage, replication status, memory, disk, cpu and lets you visualize and alert on those metrics alongside your other system and application metrics.

==== 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 PostgreSQL.

[http://cavaliercoder.github.io/libzbxpgsql/ libzbxpgsql] is a [http://www.zabbix.com/ Zabbix] monitoring template and native agent module for PostgreSQL.

==== 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.

====Okmeter====
[http://okmeter.io/ Okmeter.io] is a commercial SaaS that collects a whole lot of PostgreSQL status and operational data: over 50 types of metrics on connections, transactions, table CRUD operations, locks, bgwriter, index usage and ops, replication, autovacuum. Also, query timings, disk and CPU usage by queries from pg_stat_statements, and system metrics — CPU, memory, fd and disk usage per process, socket connections per port and tcp status. Collecting the data requires minimal configuration, and it can be used to create visualization dashboards and alert on metrics.

=== Postgres-centric monitoring solutions ===

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

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] monitors, alerts, manages and tunes local and remote large scale Postgres deployments from a single graphical console. Out of the box features include: server auto-discovery, point and click management of database objects, 225+ pre-configured database alerts by SMTP/SNMP, custom alerts, global At-a-Glance monitoring dashboards, Performance monitoring dashboards, custom dashboards, an Audit Manager, Postgres Expert best practice configuration recommendations, a Log Manager, a Log Analyzer Expert, a SQL Profiler, and Index Advisor.

==== 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.

==== PoWA ====

[http://dalibo.github.io/powa/ PoWA] is a PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers. It relies on extensions such as pg_stat_statements, pg_qualstats, pg_stat_kcache, pg_track_settings and HypoPG, and can help you optimize you database easily.

==== OPM: Open PostgreSQL Monitoring ====

[http://opm.io Open PostgreSQL Monitoring (OPM)] is a free software suite designed to help you manage your PostgreSQL servers. It's a flexible tool that will follow the activity of each instance. It can gather stats, display dashboards and send warnings when something goes wrong. The long-term goal of the project is to provide similar features to those of Oracle Grid Control or SQL Server Management Studio.

==== pgAnalytics ====

[http://pganalytics.io pgAnalytics] is a comercial SaaS developed by Dextra. pgAnalytics collects snapshots from PostgreSQL statistics, log files and backups. Linux and Windows statistics are gathered to provide a better understand about whole PostgreSQL environment. Batch process are triggered to looking for errors or things that can be improved, sending alert emails. Try a [http://pganalytics.io/demo demo].

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

Monitoring

2017-01-31T21:46:19Z

Gsmith: Move okmeter to the bottom of its section, below the much more popular/mature tools there. Remove the second entry added to spam it across multiple sections.

{{Languages}}

== 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.

==== pg_activity ====

<tt>[https://github.com/julmon/pg_activity pg_activity]</tt> is a htop like application for PostgreSQL server activity monitoring, written in Python.

==== pgstats ====

<tt>[https://github.com/gleu/pgstats pgstats]</tt> is a command line tool written in C which can sample various PostgreSQL informations. It also provides a tool to generate CSV files to graph the pgstats metrics.
=== 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 ===

====Datadog====
[http://www.datadoghq.com/ Datadog] is a commercial saas that collects postgres metrics on connections, transactions, row crud operations, locks, temp files, bgwriter, index usage, replication status, memory, disk, cpu and lets you visualize and alert on those metrics alongside your other system and application metrics.

==== 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 PostgreSQL.

[http://cavaliercoder.github.io/libzbxpgsql/ libzbxpgsql] is a [http://www.zabbix.com/ Zabbix] monitoring template and native agent module for PostgreSQL.

==== 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.

====Okmeter====
[http://okmeter.io/ Okmeter.io] is a commercial SaaS that collects a whole lot of PostgreSQL status and operational data — over 50 types of metrics on connections, transactions, table crud operations, locks, bgwriter, index usage and ops, replication, autovacuum. Also, query timings, disk and CPU usage by queries from pg_stat_statements, and system metrics — CPU, memory, fd and disk usage per process, socket connections per port and tcp status.

Once installed okmeter agent will auto-detect and start to monitor every PG instance without much of configuration. It will automatically create visualization dashboards with charts for all these stats and metrics. It allow you to configure alerts based on these metrics — for example on current number of auto-vacuum workers or replication lag.

Although Okmeter is a generic monitoring service, Okmeter PostgreSQL plugin was developed with guidance and feedback from PostgreSQL-Consulting experts. It is very detailed yet meaningful with regard to PostgreSQL metrics it is as Postgres-aware as Postgres-centric monitoring solutions.

=== Postgres-centric monitoring solutions ===

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

[http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager Postgres Enterprise Manager] monitors, alerts, manages and tunes local and remote large scale Postgres deployments from a single graphical console. Out of the box features include: server auto-discovery, point and click management of database objects, 225+ pre-configured database alerts by SMTP/SNMP, custom alerts, global At-a-Glance monitoring dashboards, Performance monitoring dashboards, custom dashboards, an Audit Manager, Postgres Expert best practice configuration recommendations, a Log Manager, a Log Analyzer Expert, a SQL Profiler, and Index Advisor.

==== 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.

==== PoWA ====

[http://dalibo.github.io/powa/ PoWA] is a PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers. It relies on extensions such as pg_stat_statements, pg_qualstats, pg_stat_kcache, pg_track_settings and HypoPG, and can help you optimize you database easily.

==== OPM: Open PostgreSQL Monitoring ====

[http://opm.io Open PostgreSQL Monitoring (OPM)] is a free software suite designed to help you manage your PostgreSQL servers. It's a flexible tool that will follow the activity of each instance. It can gather stats, display dashboards and send warnings when something goes wrong. The long-term goal of the project is to provide similar features to those of Oracle Grid Control or SQL Server Management Studio.

==== pgAnalytics ====

[http://pganalytics.io pgAnalytics] is a comercial SaaS developed by Dextra. pgAnalytics collects snapshots from PostgreSQL statistics, log files and backups. Linux and Windows statistics are gathered to provide a better understand about whole PostgreSQL environment. Batch process are triggered to looking for errors or things that can be improved, sending alert emails. Try a [http://pganalytics.io/demo demo].

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

Reviewing a Patch

2016-12-21T18:26:47Z

Gsmith: Add quick intro. Link to new review talk from David Steele. Date the talks for context.

{{Languages}}

PostgreSQL development is organized into [[CommitFest]] periods that need plenty of reviewers to help with all the submissions. Many people feel that they're not qualified to do a full review of patches submitted to PostgreSQL. But review includes many different tasks, and even if you can't do all of them you can help the project by taking on the earlier phases. If you can apply a patch and you can use the new feature, you're already qualified to start reviewing it.

The eventual committer will do their own review before the patch goes into the codebase. The task of a reviewer is to take off load from committers by catching simple problems. The reviewer's job is not necessarily to guarantee some level of quality, but just to report any problems they are able to find. That task is done if you think the patch is ready for in-depth review from a committer. See [http://archives.postgresql.org/pgsql-hackers/2009-07/msg01103.php this patch review] as one example of the output a thorough review might produce. Reviews for other patches might, of course, contain different sections or for that matter, look completely different.

See also [[Media:11_eggyknap-patch-review.pdf|Josh Tolley's presentation]] (2009) for a tongue-in-cheek but not inaccurate description of who is welcome, and [http://www.pgcon.org/2011/schedule/events/368.en.html Review of Patch Reviewing] (2011) or [https://github.com/dwsteele/conference/blob/release/PostgresPatchReview-PGConfEU-2016/slides/slides.pdf Reviewing PostgreSQL Patches for Fun and Profit] (2016) for more serious looks at the material that's covered here.

Tactical points:

* If a commitfest is currently in progress, it is available [https://commitfest.postgresql.org/action/commitfest_view/inprogress here].
* Every commitfest ought to have a commitfest manager that you can ask for help picking a patch or on procedural matters.
** The current commitfest manager is: (vacant)
* You don't have to ask for permission to sign yourself up.
* Please sign up as soon as you know that you plan to do the review. (Some people hold back on signing up until they have actually done the review. Please don't do that; it doesn't help us plan our reviewing resources.)
* On the other hand, we ask that initial reviews are sent in within about five days, to avoid "blocking" reviewing spots. But it's OK to send in a partial review or ask for more time. Just keep communicating.
* Reviews and other development communications should generally be done via the [http://www.postgresql.org/list/pgsql-hackers/ pgsql-hackers] mailing list.
* Send reviews as replies to the email containing the patch. Try to keep the email threading intact. Don't start a new thread for the review, or chances are the original author won't see it.

Phases a patch review generally goes through include:

== Submission review (skills needed: patch, English comprehension) ==

* Is the patch in a patch format which has context? (eg: [http://en.wikipedia.org/wiki/Diff#Context_format context diff format])
** Patches in 'normal' or 'plain' diff formats, which only show the lines changed and no context, are not acceptable.
** Ideally, submitters should choose either context (diff -c) format or unified (diff -u) format based on which makes the submitter's patch easier to read.
* Does it apply cleanly to the current git master?
* Does it include reasonable tests, necessary doc patches, etc?

== Usability review (skills needed: test-fu, ability to find and read spec) ==

Read what the patch is supposed to do, and consider:

* Does the patch actually implement that?
* Do we want that?
* Do we already have it?
* Does it follow SQL spec, or the community-agreed behavior?
* Does it include pg_dump support (if applicable)?
* Are there dangers?
* Have all the bases been covered?

== Feature test (skills needed: patch, configure, make, pipe errors to log) ==

Apply the patch, compile it and test:

* Does the feature work as advertised?
* Are there corner cases the author has failed to consider?
* Are there any assertion failures or crashes?
**Reviews should be done with the ''configure'' options
''--enable-cassert'' and ''--enable-debug'' turned on; see [[Working with git]] for a full example. These will help find issues with the code that might otherwise be missed. But note a copy of PostgreSQL built using these parameters will be substantially slower than one built without them. If you're working on something performance-related, such as testing whether a patch slows anything down, be sure to build without these flags before testing execution speed. Some, but not all, of the penalty of ''--enable-cassert'' can be turned off at server start time by putting ''debug_assertions = false'' in your postgresql.conf. See [http://www.postgresql.org/docs/current/static/runtime-config-developer.html Developer Options] for more details about that setting; it defaults to true in builds done with ''--enable-cassert''. Also note that while ''--enable-debug'' shouldn't have any performance penalty when building with ''gcc'', it definitely does with most other compilers.

== Performance review (skills needed: ability to time performance) ==

* Does the patch slow down simple tests?
* If it claims to improve performance, does it?
* Does it slow down other things?

== Coding review (skills needed: guideline comparison, experience with portability issues, minor C-reading skills) ==

Read the changes to the code in detail and consider:

* Does it follow the project [http://developer.postgresql.org/pgdocs/postgres/source.html coding guidelines]?
* Are there portability issues?
* Will it work on Windows/BSD etc?
* Are the comments sufficient and accurate?
* Does it do what it says, correctly?
* Does it produce compiler warnings?
* Can you make it crash?

== Architecture review (skills needed: experience with whole-PostgreSQL-project architecture) ==

Consider the changes to the code in the context of the project as a whole:

* Is everything done in a way that fits together coherently with other features/modules?
* Are there interdependencies that can cause problems?

== Review review ==

* Did the reviewer cover all the things that kind of reviewer is supposed to do?

[[Category:Development]]

TPC-H

2016-04-15T21:56:14Z

Gsmith: Change broken links to archive.org, add Jignesh's talk

The [http://www.tpc.org/tpch/ TPC-H Benchmark] is a popular one for comparing database vendors. It's possible to run the TPC-H data set on PostgreSQL without having a formal testing kit (although there is [[DBT-3]], a work in progress to provide a full kit). The results have generally been disappointing, for reasons that aren't necessarily relevant in the real world. PostgreSQL is missing some of the things needed to do well on this benchmark, whereas proprietary database vendors are so focused on it they will "game" TPC-H runs (add optimizations specifically aimed at it) to make absolutely sure they do well.

Notes:
* [http://dsl.serc.iisc.ernet.in/projects/PICASSO/picasso_download/doc/Installation/tpch.htm TPC-H data generation and loading]
* [https://web.archive.org/web/20110406233644/http://monetdb.cwi.nl/SQL/Benchmark/TPCH/ TPC-H Performance Study] using an old version of PostgreSQL
* [http://archives.postgresql.org/pgsql-hackers/2010-01/msg01867.php Discussion of limitations] started by the above
* [http://m.tholis.webnode.com/news/tpc-h-fun-with-greenplum-single-node-edition-/ TPC-H fun with Greenplum (single node edition)] - leads into the below and discusses what's needed to do well on TPC-H
* [https://web.archive.org/web/20100212084437/http://community.greenplum.com/showthread.php?t=113 GP SNE versus Postgres 8.4.2 - 22 Query Benchmark Results] (similar to TPC-H queries, tweaked to run better)
[[Category:Benchmarking]]
* [http://www.slideshare.net/jkshah/postgresql-and-benchmarks PostgreSQL and Benchmarks] TPC-H, TPC-E, and EAStress 2004 review (2008)

May 2015 Fsync Permissions Bug

2015-07-13T16:15:13Z

Gsmith: Add warning to check symlinks and make a backup before erasing anything

=May 2015 fsync Permissions Bug FAQ=

On May 22, 2015, the PostgreSQL project [http://www.postgresql.org/about/news/1587/ released a set of updates] to all supported versions of PostgreSQL. One of the fixes included in this batch of updates forced fsyncing of all PostgreSQL files on a restart after a crash. This fix was added to prevent certain kinds of data corruption which can occur if a system hosting a database has several failures in a row.

Unfortunately, this fix causes issues with some users' PostgreSQL setups due to file permissions issues, which can cause PostgreSQL to refuse to restart after an unexpected shutdown, or when restoring from a binary backup (PITR).

''This issue is slated to be fixed in the 2015-06-04 Update Release.''

==Who is affected by this bug?==

Users who:

# applied the 9.4.2, 9.3.7, 9.2.11, 9.1.16 and/or 9.0.20 PostgreSQL updates
# have one or more files or directories, or symlinks to one or more files or directories, not owned or writeable by the "postgres" user (or other installation owner) under the postgres data directory (PGDATA).

Note that condition 2 is common to SSL-enabled Debian and Ubuntu installations of PostgreSQL 9.1, 9.0, and earlier, but may affect other users as well. Most users on other platforms are not affected, as all files and links under PGDATA are owned by the "postgres" user by default.

==What are the symptoms?==

If you experience the bug, PostgreSQL will refuse to restart after a crash, or a restore from binary backup, with an error message similar to the following:

<code>
* Starting PostgreSQL 9.1 database server
* The PostgreSQL server failed to start. Please check the log output:
2015-05-26 03:27:20 UTC [331-1] LOG: database system was interrupted; last known up at 2015-05-21 19:56:58 UTC
2015-05-26 03:27:20 UTC [331-2] FATAL: could not open file "/etc/ssl/certs/ssl-cert-snakeoil.pem": Permission denied
2015-05-26 03:27:20 UTC [330-1] LOG: startup process (PID 331) exited with exit code 1
2015-05-26 03:27:20 UTC [330-2] LOG: aborting startup due to startup process failure
</code>

For more information, see [http://www.postgresql.org/message-id/20150525142657.4686.35151@wrigleys.postgresql.org the original bug report].

==I've hit this bug and I can't restart Postgres. What do I do?==

As a temporary workaround, change the permissions on any symlinked files to being writable by the Postgres user. For example, on Ubuntu, with PostgreSQL 9.1, the following should work:

''WARNING: Make sure these configuration files are symbolic links before executing these commands! If someone has customized the server.crt or server.key file, you can erase them by following these steps. It's a good idea to make a backup of the server.crt and server.key files before removing them.''

<code>
(as root)
# go to PGDATA directory
cd /var/lib/postgresql/9.1/main
ls -l server.crt server.key

# confirm both of those files are symbolic links
# to files in /etc/ssl before going further

# remove symlinks to SSL certs
rm server.crt
rm server.key

# copy the SSL certs to the local directory
cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
cp /etc/ssl/private/ssl-cert-snakeoil.key server.key

# set permissions on ssl certs
# and postgres ownership on everything else
# just in case
chown postgres *
chmod 640 server.crt server.key

service postgresql start
</code>

You will need to adapt the above example to your specific circumstances, but that should give you a general idea of what to do. The requirement is that the postgres user must have write access to everything in PGDATA or symlinked from PGDATA.

==Should I not apply the updates?==

The 9.4.2 and 9.3.7 updates fix a serious bug which causes unrecoverable data loss under some circumstances. As such, the PostgreSQL project considers a temporary workaround involving file permissions to be a less serious risk than the fixed bugs, and recommends applying the updates once you've verified and changed file permissions, if required.

Other users who are not at risk for the fsync issue are also recommended to apply the update at the next downtime.

==Will you be fixing it soon?==

The PostgreSQL project expects to release another update very soon which addresses the file permissions issue. The expected release date for this update is June 4.

PgCon 2015 Developer Unconference

2015-05-27T06:20:07Z

Gsmith: Add gsmith to the event

An Unconference-style multi-track (three tracks are currently planned) event for active PostgreSQL developers will be held from the afternoon of Tuesday 16 June, 2015 through Wednesday 17 June 2015 at the University of Ottawa, as part of PGCon 2015. This Unconference will be focused on technical PostgreSQL development discussions ranging from Clustering and replication to the infrastructure which runs postgresql.org.

'''Please add your name under RSVPs if you plan to attend.'''

== Topics ==

Developers are asked to propose topics which they wish to either present on or which they would like another individual to present on. All topics should be clearly related to PostgreSQL development. The topic should be added to the table below and any required attendees (presumably at least the presenter, and the requester if different) listed. Other attendees of the Unconference who are interested should list themselves as Optional. Note that non-technical topics related to PostgreSQL development will be addressed during the invite-only Developer meeting, being held in advance of the Unconference. Further, the Developer Unconference is for developers of PostgreSQL and user-oriented topics are not appropriate for this venue.

== Slot assignment ==

Slots will be assigned based on the topic's interest among the attendees of the Unconference (the number of individuals who listed themselves as attendees). Final determination on any particular topic will be made by the Unconference organizers. Please only participate if you are confident of your attendance at the Unconference.

== Venue ==

These meetings will be held at the University of Ottawa. The topics selected, the schedule and the specific room assignments will be published closer to the event and will be based on the information provided here. Please direct any questions to Dave Page (dpage@pgadmin.org).

== Sponsorship ==

The Developer Unconference will be sponsored by Salesforce.com, and by NTT Open Source for the Clustering Track.

== Attendees ==

While the Unconference is open to all attendees of PGCon, formal invitations will be sent to specific PostgreSQL developers, including the Core team, Major Contributors, Committers, and other developers who have been involved in the 9.4 release. These invitations are intended to encourage developers to attend the Unconference but we are unable to guarantee every invitee a speaking slot.

== RSVPs ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Josh Berkus
* Stephen Frost
* Stefan Kaltenbrunner
* Amit Kapila
* Dave Page
* Magnus Hagander
* Kumar Rajeev Rastogi
* Greg Smith

=Topics=

'''Please add any topics you wish covered to the table.'''

'''For any topics you are requesting or presenting on, please add your name in the Required column.'''

'''For any topics you would like to attend, please add your name in the Interested column.'''

{| border="1" cellpadding="4" cellspacing="0"
!Topic
!Policy
!Taker of Notes
!Required Attendees
!Interested Attendees

|- style="background-color:lightgray;"
|Clustering
|Open
|
|Josh Berkus
|

|- style="background-color:lightgray;"
|pgAdmin4
|Open
|
|Dave Page, Stephen Frost
|Magnus Hagander

|- style="background-color:lightgray;"
|Infrastructure Q&A
|Open
|
|Dave Page, Stephen Frost, Stefan Kaltenbrunner, Magnus Hagander
|

|- style="background-color:lightgray;"
|WWW Team Meeting
|Open
|
|Dave Page, Stephen Frost, Stefan Kaltenbrunner, Magnus Hagander
|

|- style="background-color:lightgray;"
|Advocacy Team Meeting
|Open
|
|Stephen Frost
|Magnus Hagander, Greg Smith

|- style="background-color:lightgray;"
|Vertical Scalability w.r.t Writes
|Open
|
|Amit Kapila
|Greg Smith

|- style="background-color:lightgray;"
|Security Team Meeting
|Closed
|
|Heikki Linnakangas, Stephen Frost, Magnus Hagander
|

|- style="background-color:lightgray;"
|Native Compilation + LLVM
|Open
|
|Kumar Rajeev Rastogi
|
|}

== Clustering ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

== pgAdmin4 ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

== Infrastructure Q&A ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

== WWW Team Meeting ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

== Advocacy Team Meeting ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

== Security Team Meeting ==

=== Meeting Notes ===
* To be filled in

=== Attendees ===
* To be filled in

PGConf NYC 2014 Talks

2014-04-08T14:57:10Z

Gsmith: Add lightning talk section

= PGConf NYC 2014 Talks =

== Conference Website ==

http://nyc.pgconf.us/2014

== Talks: Thursday April 3, 2014 ==

=== Keynotes ===

* [https://wiki.postgresql.org/images/9/9f/PostgreSQL_Keynote_-_GS.pdf Increase Enterprise PostgreSQL Adoption at Firms Like Goldman Sachs in Financial Services]

=== Grand Ballroom Salon A&B ===

* [http://tapoueh.org/images/confs/PGDAY_NYC_2014.pdf PostgreSQL for Developers]
* [http://excoventures.com/talks/misc/arin-ha-pgsql.pptx.pdf Building a High-Availability PostgreSQL Cluster]
* [http://nbviewer.ipython.org/gist/nyamada/9946705 Using Postgres for append-only immutable data]
* [https://wiki.postgresql.org/wiki/File:FlexiblePostgreSQLConfiguration.pdf Flexible PostgreSQL Configuration]

=== Grand Ballroom Salon C ===
* [http://snowman.net/slides/pgsecurity-pgconfnyc14_20140403.pdf Intro to PostgreSQL Security]
* [https://wiki.postgresql.org/wiki/File:Achieving_PCI_Compliace_PgConfNYC2014.pdf Achieving PCI compliance with Postgres]
* [http://www.slideshare.net/mason_s/geographically-distributed-postgresql Geographically Distributed PostgreSQL]

=== Empire Room ===
* [https://wiki.postgresql.org/wiki/File:Pgconfnyc2014_whenpgcantyoucan.pdf When PostgreSQL Can't, You Can]

== Talks: Friday April 4, 2014 ==

=== Keynotes ===

=== Grand Ballroom Salon A&B ===
* [http://momjian.us/main/writings/pgsql/central.pdf Making Postgres Central in Your Data Center]

=== Grand Ballroom Salon C ===

* [http://www.slideshare.net/PGExperts/fail-over-failback Fail Over, Fail Back]

=== Empire Room ===

* [https://github.com/dwsteele/conference/releases/tag/release%2FDatabaseValidationAndVersioning-PGConfNYC2014 Database Validation and Versioning]
* [http://www.joeconway.com/presentations/plr-PGConfNYC2014.pdf Easy Statistical Analysis in PostgreSQL with PL/R]
* [http://www.slideshare.net/gavinmroy/integrating-postgresql-with-rabbitmq Integrating PostgreSQL with RabbitMQ]
* [https://wiki.postgresql.org/wiki/File:Eav-pgconfnyc2014.pdf An Adventure in Data Modeling: The Entity-Attribute-Value Data Model]

=== Lightning talks ===
* Coming soon!

PGConf NYC 2014 Talks

2014-04-08T14:54:28Z

Gsmith: Add Flexible postgresql.conf slides

File:FlexiblePostgreSQLConfiguration.pdf

2014-04-08T14:52:08Z

Gsmith: Flexible PostgreSQL Configuration - Greg Smith - Crunchy Data Solutions

== Summary ==
Flexible PostgreSQL Configuration - Greg Smith - Crunchy Data Solutions
== Licensing ==
{{The PostgreSQL Licence}}

Monitoring

2013-09-13T13:03:05Z

Gsmith: Link to new Munin setup article

== Statistics collector ==

* 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]

== Monitoring PostgreSQL with [http://munin-monitoring.org Munin] ==

* PostgreSQL Plugins developed in Perl are included in the Core Munin Distribution. The following plugins are included by default: 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_.
* [http://aouyar.github.com/PyMunin/ PyMunin - Multigraph Munin Plugins in Python] - PyMunin includes a Multigraph Munin Plugin for PostgreSQL that implements the following graphs: 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.
* [http://highperfpostgres.com/guides/postgresql-monitoring-with-munin/ PostgreSQL Monitoring With Munin]. Detailed setup instructions for common Linux platforms.

== Cacti ==

* [[Cacti]]

== SNMP agent ==

Useful for network management systems which are limited to SNMP protocol.

* [http://pgsnmpd.projects.postgresql.org/ pgsnmpd] implements [http://www.faqs.org/rfcs/rfc1697.html RFC 1697 MIB] which is generic RDBMS MIB

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

Submitting a Patch

2013-07-22T22:30:30Z

Gsmith: Update "Mutual Review Obligations" with more details about a properly offsetting review

== Initial patch design ==

If you have a trivial patch that serves an obvious need, you may be able to write the patch and submit it directly to the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] mailing list without having its design reviewed first. But in general, a non-trivial change should be discussed (potentially before the code is even written) on the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] list before being submitted as a patch.

For general coding style guidelines, see the [[Developer FAQ]] and the [http://developer.postgresql.org/pgdocs/postgres/source.html PostgreSQL Coding Conventions].

=== Design your interface first ===

Ask yourself these questions:

* Will the user interact with this new feature? if so, how?
* What is the syntax? Have ideas, and the ability to defend technical decisions you believe strongly in.
* What are the exact semantics/behaviors?
* Are there any backward compatibility issues?
* Get community buy-in at this level of detail before you start coding. But not necessarily consensus.
* Write an opening paragraph to your email to the -hackers list that answers these questions:
**This is the kind of problem I'm trying to solve
**This is what it is doing right now
**This is what it will do.

Mostly, get someone from the community involved in your ideas as early as possible so that you can even get half-baked ideas vetted early, rather than creating something in a vacuum. Similarly, it's easier to make progress and keep patches focused if you concentrate on the smallest portion of the idea you can execute perfectly. Resist the temptation to build a giant patch all at once, as those are much less likely to be reviewed usefully and therefore committed. You should take a look at how your patch will eventually be [[Reviewing a Patch|reviewed]], so you can make sure that review is likely to succeed.

=== Save us the trouble of reformatting your code ===

Please read [http://developer.postgresql.org/pgdocs/postgres/source.html PostgreSQL Coding Conventions]. Also, follow the style of the adjacent code! [http://archives.postgresql.org/pgsql-hackers/2009-08/msg01001.php Suggestions from Tom] clarify some of the trickier situations you might run into. [[Creating Clean Patches]] provides a walkthrough of how to do self-review and improvement of your patches to fix code formatting issues.

Naming things is important, and when in doubt, ask someone else to help you with names. We tend to use [http://en.wikipedia.org/wiki/CamelCase CamelCase] or underscores: thisStyleIsOkay or this_is_okay_too.

Generally, try to blend in with the surrounding code. Do not use #ifdef's to enable your changes. Comments are for clarification and to explain the ''why'' question, not for delineating your code from the surroundings nor to re-state what the code is doing.

Please remove any spurious whitespace. ''git diff --color'' makes them stand out like a sore thumb, in red.

== Patch submission==

Once you believe your patch is complete, please read through the complete patch that you will be sending to the mailing list. Consider what format the patch is in (should be either context or unified) and if it's easy to read. Ensure that the patch didn't mistakenly include unintended changes (such as whitespace changes, test code, #ifdef 0'd out blocks, etc). The patch should then be submitted via e-mail to the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] mailing list. At that time, or after you wait for initial feedback, you should also add it to the page for the next [https://commitfest.postgresql.org/ CommitFest].

Normally changes should be submitted as a single patch that includes every file touched. If the patch is large and can be logically separated into distinct and separately commit-able sections for easier review, with a clear order they get applied in described when applicable, that can be more straightforward for reviewers to work with for more complicated patches. Patches must be in a format which provides context (eg: [http://en.wikipedia.org/wiki/Diff#Context_format Context Diff]); 'normal' or 'plain' diff formats are not acceptable. Ideally, patch authors should choose the context (diff -c) format or unified (diff -u) format based on which format makes their patch easier to read. See [[Working_with_Git#Context_diffs_with_Git|Working with git]] and [[Creating Clean Patches]] for ways to do this well. [http://petereisentraut.blogspot.com/2009/09/how-to-submit-patch-by-email.html How to submit a patch by email] for more details about mailing in patches. If you're a new submitter, the suggestion there about using your judgment on patch formatting is not a recommended practice however--you should be using the standard context diff format.

Please note that PostgreSQL uses a BSD/MIT-style license for its code. By posting a patch to the public PostgreSQL mailing lists, you are giving the PostgreSQL Global Development Group the non-revocable right to distribute your patch under the [http://www.postgresql.org/about/licence/ PostgreSQL license].

Please include all of the following information with each patch submitted

* Project name.
* Uniquely identifiable file name, so we can tell difference between your v1 and v24.
* What the patch does in a short paragraph.
* Whether the patch is for discussion or for application (see WIP notes below)
* Which branch the patch is against (ordinarily this will be ''master''). For more on branches in PostgreSQL, see [[Working_with_Git#Using_Back_Branches|Using Back Branches]].
* Whether it compiles and tests successfully, so we know nothing obvious is broken.
* Whether it contains any platform-specific items and if so, has it been tested on other platforms.
* Confirm that the patch includes [[Regression test authoring|regression tests]] to check the new feature actually works as described.
* Include documentation on how to use the new feature, including examples. See the [http://www.postgresql.org/docs/current/static/docguide.html documentation documentation] for more information.
* Describe the effect your patch has on performance, if any.
* Try to include a few lines about why you chose to do things particular ways, rather than let your reviewer guess what was happening. This can be done as code comments, but it might also be an additional reviewers' guide, or additions to a README file in one of the code directories.
* If your patch addresses a [[Todo]] item, please give the name of the Todo item in your email. This is so that the reviewers will know that the item needs to be marked as done if your patch is applied.

It is helpful for early patches, ones not intended to be of commit quality, to be labeled clearly as such so that the appropriate style of review is done. The abbreviation WIP ("Work in Progress") is the standard shorthand to attach to patches intended for review not as a commit candidate, but for design feedback. Labeling your patch as a WIP on your e-mail subject line and on the matching description in the CF application will advise reviewers to focus more on the general approach, rather than on things like coding style that can normally be ignored in the early portion of a patch's lifecycle.

=== Reasons your patch might be returned ===

Submitting the patch is just the first step towards getting it committed. Very few patches are committed exactly as originally submitted, even those submitted by experienced professional developers. For any non-trivial patch you should plan for at least 3 versions before final acceptance.

There are a few common reasons patches are returned without getting the review consideration the submitter was hoping for:

* '''The fastest way to get your patch rejected is to make unrelated changes'''. Reformatting lines that haven't changed, changing unrelated comments you felt were poorly worded, touching code not necessary to your change, etc. Each patch should have the minimum set of changes required to work robustly. If you do not follow the code formatting suggestions above, expect your patch to be returned to you with the feedback of "follow the code conventions", quite likely without any other review.

* '''Consider how the patch would be reviewed'''. The things a reviewer will evaluate are listed in [[Reviewing a Patch]]. You should look at this list and consider if you will pass it before submission. We recommend that new code contributors first spend time doing review, so that you will already be familiar with this list and process. The review guidelines are used because they work; doing your own self-review before submitting your patch is recommended.

* '''Performance gain is claimed without test case'''. Performance patches are fun to write but hard to validate. If the patch is intended to improve performance, it's a good idea to include some reproducible tests to demonstrate the improvement. If a reviewer cannot duplicate your claimed performance improvement in a short period of time, it's very likely your patch will be returned. Do not expect that a reviewer is going to find your performance feature so interesting that they will build an entire test suite to prove it works. You should have done that as part of patch validation, and included the necessary framework for testing with the submission.

* '''Did not include documentation or regression tests'''. Any patch without these two items is automatically considered a WIP one--you might get review of your patch, but do not expect it to be committed. See [[Regression test authoring]] for more information about how to write these tests. Documenting the design at a high level in your submission e-mail is also recommended.

* '''Failed to address earlier criticisms of this design'''. Many patches try to do things that have been tried or considered before, where the earlier discussion found a problem with the obvious approach. Not saying how your new submission addresses those issues suggests you have the same problems. The [[Todo|todo list]] is a good source for finding past discussion of ideas for patches.

The objective of all of these suggested items is to ensure that the reviewer's time is not wasted. You spent time writing the code, but that does '''not''' mean you can demand time, energy and interest from a reviewer. Follow these guidelines and become familiar with the review process. Make it easy on yourself and others so that your patch is accepted quickly, easily and with good humor on all sides.

The presentation [http://www.pgcon.org/2011/schedule/events/303.en.html How To Get Your PostgreSQL Patch Accepted] provides some additional suggestions for how to submit a patch that will be considered more seriously.

=== Submission timing ===

To improve the odds of the right discussion of your patch or idea happening, pay attention to what the community work cycle is. If for example you send in a brand new idea in the beta phase, don't be surprised if no one is paying attention because they are focused on release work. Come back when the beta is done, please!

PostgreSQL development is organized with periodic [[CommitFest|CommitFests]], which are periods where new development halts in order to focus on patch review and committing. It's best if you can avoid sending in a new patch during the occasional weeks when there is an active CommitFest; you can check the schedule via the [https://commitfest.postgresql.org/ CommitFest application]. If your schedule doesn't allow waiting until an active CommitFest is over, you should explicitly label your submission as intended for the next CommitFest, not the current one, so that it's clear it's not intended to be part of the active review process.

== Patch review and commit ==

There's basically three different workflows a patch can follow after it's been submitted that lead to it being commited:

Workflow A:
# You post patch to pgsql-hackers
# A committer picks it up immediately and commits it.

Workflow B:
# You post a patch to pgsql-hackers
# You add the patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue
# A committer picks up the patch from the queue, and commits it

Workflow C:
# You post a patch to pgsql-hackers
# Bruce adds the patch to a list of unapplied patches
# At the beginning of the next commit fest, Alvaro (with the help from others, I hope) goes through the list, and adds the patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue
# A [[Committers|committer]] picks up the patch from the queue, and commits it

At any of these stages, your patch might instead be rejected for technical, style, or other reasons. These rejections will normally come with feedback on whether an improved version of that patch would be more acceptable. In those cases, you should consider updating your patch based on that feedback and re-submit.

== Mutual Review Offset Obligations ==

PostgreSQL is a community project which relies on peer review. Each patch submitter to a CommitFest is expected to review at least one other patch. Ideally the patch selected for mutual review will be of similar size and/or scope to the one submitted. Someone who submits a feature that's thousands of lines long can't just review a tiny submission by someone else and expect the community to be satisfied. The idea is similar to the "offset" concept used for balancing gas emissions.

This policy permits the project to review, accept, reject, or return feedback on each patch in a timely fashion. Few other open source projects provide such a guarantee for processing submissions. Failure to participate in the review process means the PostgreSQL community can't promise you review and feedback of your own patches. Actively reviewing another patch during the CommitFest also helps keeps contributors engaged, so that they can process feedback on their submission. Very few submissions are committed without first going through feedback and revisions during a CommitFest.

For paid contributors to PostgreSQL, this policy means that you should make it clear to your sponsor that doing patch review is an obligation. When time is budgeted and the schedule laid out for developing a new feature for submission to the PostgreSQL project, that should include review resources for another feature as a necessary part of its overhead.

== Followup on submissions ==

=== How do you get someone to respond to you? ===

You've sent an email to -hackers and no one has responded. What do you do?

* Make sure you've added your patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue.
* Start out by reviewing a patch or responding to email on the lists. Even if it is unrelated to what you're doing.
* Start with submitting a patch that is small and uncontroversial to help them understand you, and to get you familiar with the overall process.
* People are more willing to listen and work with someone who is already contributing.
* Also, in our community -- if no one objects, then there is implicit approval. Within reason!

Participating in community is a process, not a single event.

=== Submitting patch updates ===

When submitting a new version of a previously submitted patch, you should do a few additional things:

* Uniquely identify the new version, usually done via an incremented suffix on the name of the patch. First review would be ''mypatch-v1.patch'', second ''mypatch-v2.patch'', etc. Using the ".patch" extension allows some reviewers to more easily read it in their code editor.

* Make sure it's easy to find any earlier discussion of the patch. Don't expect that everyone will still be able to find previous submissions on their own. Either fully duplicate the information about the patch from your original messages, or provide a clear link to the earlier message via the [http://archives.postgresql.org/pgsql-hackers/ archives]. Note that you can link to your earlier post using the e-mail message ID of what you sent earlier, perhaps by checking your sent e-mail for it. That type of link is preferred because links to the archive by message number might sometimes get renumbered. See [[Template:MessageLink]] for more details.

[[Category:Community]]

Reliable Writes

2013-06-09T02:45:55Z

Gsmith: Link to plug pull test page

Databases have many levels of read and write caches they use. There's the database's own dedicated memory (shared_buffers), the operating system cache, potentially a disk controller cache (typical on a RAID card), and caches on each individual hard drive. PostgreSQL expects some of these caches to work effectively in order to obtain good performance. But periodically, the database issues a synchronous write request: requesting that something must be committed to disk before the database can proceed. If any of those writes say they are successful to the database, but the data is not really safe, database corruption can be expected if there is a server crash for any reason. Software bug, power outage, UPS failure, accidentally unplugging the server, any of these things can lead to a corrupted database that will not start normally if your system has unsafe writes.

There is a good diagram of how all these levels of caching fit together at [http://momjian.us/main/writings/pgsql/hw_selection.pdf Database Hardware Selection Guidelines] (PDF, Bruce Momjian)

The recommended way to test reliability of all the caches on a server is with [http://highperfpostgres.com/disk-plug-pull-testing/ Disk Plug Pull Testing]. A plug pull test that fails is always accurate, but passing such a test cannot guarantee unsafe writes are impossible.

== References ==

This page lists articles about this topic that include a mix of theory and practical recommendations about how to setup a reliable system that doesn't have this sort of problem after a crash:

* [http://www.postgresql.org/docs/current/static/wal-reliability.html Reliability]: from the official documentation
* [https://www.packtpub.com/sites/default/files/0301OS-Chapter-2-Database-Hardware.pdf Database Hardware] (PDF): Chapter on general hardware selection, introduces write-back vs. write-through caching terminology and notes about issues with SSD writes. (Greg Smith)
* [https://www.packtpub.com/article/server-configuration-tuning-postgresql Server Configuration Tuning]: lists things to be aware of when setting wal_sync_method, as well as warnings about settings changes that can make your server unsafe--fsync, full_page_writes, and synchronous_commit (Greg Smith)
* [http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm Tuning PostgreSQL WAL Synchronization]: background about the topic of drives that handle writes unsafely and how this impacts PostgreSQL writes. (Greg Smith)

== Known problem hardware ==

* The first and second generation Intel SSD drives such as the Intel X25-E will write unsafely, and this behavior is impossible to fix without serious impact to both the speed and longevity of the drive. See [http://www.mysqlperformanceblog.com/2009/03/02/ssd-xfs-lvm-fsync-write-cache-barrier-and-lost-transactions/ SSD, XFS, LVM, fsync, write cache, barrier and lost transactions] (Vadim Tkachenko) for an introduction. Their consumer drives such as the X-25-M G2 have been reported to be [http://evanjones.ca/intel-ssd-durability.html even worse] (Evan Jones). The 3rd generation Intel 320 and 710 series drives do not have any of these problems, having added a capacitor to provide battery-backup for flushing the drive cache when power drops.

[[Category:Documentation]][[Category:Development]]

Planet PostgreSQL

2013-06-06T18:37:51Z

Gsmith: Add "Can I offer free or discounted products to people?"

[http://planet.postgresql.org/ Planet PostgreSQL] is a blog aggregation service run by the PostgreSQL community. In addition to the main Planet site, entries are published ("syndicated") to the [http://www.postgresql.org/ postgresql.org] site as well.

== Planet PostgreSQL Policy ==

The following simple rules cover the blogs being listed on Planet PostgreSQL:

* All blogs should be about PostgreSQL or closely related technologies. If you want to blog about other things as well, please put your PostgreSQL specific posts in a separate category/tag/label, and use the feed for this category only for Planet PostgreSQL.
* All topics related to PostgreSQL are of course appreciated - both technical and non-technical.
* All posts should be in English. If you want to blog in other languages, please put your PostgreSQL specific posts in a separate category/tag/label, and use the feed for this category only for Planet PostgreSQL.
* All blogs should be identified by the posters name. A nickname is allowed to be used in combination with this, but blogs without names are not allowed.
* Avoid inflammatory comments in syndicated posts. Personal attacks are not permitted.
* Publishing of advertising in the syndicated part of your blog is not permitted

Violating these rules will cause your blog to be removed from Planet PostgreSQL.

== Planet PostgreSQL FAQ ==

=== How can I remove a bad entry from the feed? ===

It's possible to login to the administration interface and hide an individual entry. If you realize you've made a mistake and done something like accidentally posting incorrectly tagged content, removing it on your own is recommended.

=== What are the right URLs to use for a labeled feed from [http://www.blogger.com blogger.com]? ===

Assuming your blog is named "blogname" and you've tagged a subset of your posts with the "postgresql" label, the correct URLs to input to the Planet code are:

* Feedurl: http://blogname.blogspot.com/search/label/postgresql
* Blogurl: http://blogname.blogspot.com/feeds/posts/default/-/postgresql

You can confirm those URLs display a suitable copy of your feed via a web browser before submitting them.

=== Are commercial posts allowed? ===

Informative entries that mention proprietary products are normally permissible, as long as the main entry is relevant to the PostgreSQL community. But the policy specifically prohibits publication of advertising in the portion of a blog that is syndicated to the Planet feed. It's recommended that blogs that may contain ads tag them appropriately, to provide a feed to the Planet without those entries included.

=== What constitutes an advertisement? ===

The primary test here is whether the information provided would be of some use even to people who have no interest in the commercial product mentioned. Consider what your entry would look like if all references to the product were removed. If there's no useful PostgreSQL content left after doing that, that post is an ad.

You can find a blog entry giving a more detailed opinion from one of the moderators about this subject at [http://blog.2ndquadrant.com/en/2011/06/planetary-alignment.html Planetary alignment]. That commentary is not part of the official policy.

=== Can I offer free or discounted products to people? ===

Not as your only post content. An article that otherwise meets the standards here, by providing useful information to readers with no interest in the commercial product mentioned, might also include some commercial offer like a free product in them. But promotions like product giveaways or providing a discount to blog readers are not acceptable on their own. You might consider the offer itself informative, but the moderators of the Planet feed will usually judge it to be advertisement. Blog entries offering to give away something for free or at a reduced cost are very likely to be flagged as a violation of policy.

=== What happens to blogs that violate the site policies? ===

A first violation of the advertising policy will result in the blog entry with the ad being removed, and a warning e-mail sent to the listed owner of the blog.

A second violation will resulting in the blog being removed from the Planet PostgreSQL feed. After a two month suspension period, the blog owner can submit their blog address--or a new feed URL better tagged to filter out advertisements--for reconsideration.

A third violation in any year will result in a full 12 month suspension of the blog from Planet PostgreSQL.

=== Who makes the moderation decisions about the Planet content allowed? ===

Site maintenance and moderation duty is done by a small team of volunteer moderators, primarily made up of [http://www.postgresql.org/community/contributors/ PostgreSQL contributors] from various companies who are familiar with the [[Policies|policies]] of the PostgreSQL community.

PgCon 2013 Developer Meeting

2013-05-22T22:06:45Z

Gsmith: Moar goals and resources!

A meeting of the most active PostgreSQL developers is being planned for Wednesday 22nd May, 2013 near the University of Ottawa, prior to pgCon 2013. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org).

Please note that this year the attendee numbers have been kept low in order to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.3 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.

This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.

== Time & Location ==

The meeting will be from 8:30AM to 5PM, and will be in the "Red Experience" room at:

Novotel Ottawa
33 Nicholas Street
Ottawa
Ontario
K1N 9M7

Food and drink will be provided throughout the day, including breakfast from 8AM.

[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]

== Attendees ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Josh Berkus (secretary)
* Jeff Davis
* Andrew Dunstan
* Peter Eisentraut
* Dimitri Fontaine
* Andres Freund
* Stephen Frost
* Peter Geoghegan
* Kevin Grittner
* Robert Haas
* Magnus Hagander
* KaiGai Kohei
* Alexander Korotkov
* Tom Lane
* Fujii Masao
* Noah Misch
* Bruce Momjian
* Dave Page (chair)
* Simon Riggs

== Proposed Agenda Items ==

Please list proposed agenda items here:

* 9.4 Commitfest schedule and Commitfest tools.
* [http://wiki.postgresql.org/wiki/Parallel_Query_Execution Parallel Query Execution] (Bruce, Noah)
* logical changeset generation review & integration (Andres)
* utilization of upcoming non-volatile RAM device (Kaigai)
* pluggable plan/exec nodes (Kaigai)
** to offload targetlist calculation, sorting, aggregates, ...
* [[GIN generalization]] (Alexander)
* An Extensibility Roadmap (dim) (http://pgsql.tapoueh.org/temp/extensibility.pdf) (15 min)
* Representing severity - derive severity from SQLSTATE (Peter Geoghegan - see http://www.postgresql.org/message-id/CA+TgmoZEjq7va+SfDZQwk6E4emEWThENNyxfqEGhB3iuoT1OJw@mail.gmail.com) (10 min)
* Error logging infrastructure - store normalized statistics about errors in a circular buffer (Peter Geoghegan). Arguably this could be discussed alongside SQLSTATE item. (10 min)
* Failback with backup (Fujii Masao - related discussion is: http://www.postgresql.org/message-id/CAF8Q-Gxg3PQTf71NVECe-6OzRaew5pWhk7yQtbJgWrFu513s+Q@mail.gmail.com)
* Volume Management (Stephen Frost - wiki page will be forthcoming before the meeting)
* AXLE Project - Big data analytics for Postgres (Simon Riggs) - an overview of the feature plan, how project works and what community can expect (15 min)
* Incremental maintenance of materialized views (Kevin) - differential REFRESH and infrastructure for ''counting'' algorithm (30 min)

== Agenda ==

{| border="1" cellpadding="4" cellspacing="0"
!Time
!Item
!Presenter
|- style="font-style:italic;background-color:lightgray;"
|08:00
|Breakfast
|

|- style="font-style:italic;background-color:lightgray;"
|08:30 - 08:45
|Welcome and introductions
|Dave Page

|-
|08:45 - 09:45
|Parallel Query Execution
|Bruce/Noah

|-
|09:45 - 10:15
|Pluggable plan/exec nodes
|KaiGai

|-
|10:15 - 10:30
|Volume Management
|Stephen Frost

|- style="font-style:italic;background-color:lightgray;"
|10:30 - 10:45
|Coffee break
|

|-
|10:45 - 11:00
|Utilization of upcoming non-volatile RAM devices
|KaiGai

|-
|11:00 - 11:30
|Logical changeset generation review & integration
|Andres

|-
|11:30 - 11:40
|Representing severity
|Peter G.

|-
|11:40 - 11:50
|Error logging infrastructure
|Peter G.

|-
|11:50 - 12:30
|Incremental maintenance of materialized views
|Kevin

|- style="font-style:italic;background-color:lightgray;"
|12:30 - 13:30
|Lunch
|

|-
|13:30 - 14:15
|GIN generalization
|Alexander

|-
|14:15 - 14:30
|An Extensibility Roadmap
|Dimitri

|-
|14:30 - 15:00
|Failback with backup
|Fujii

|- style="font-style:italic;background-color:lightgray;"
|15:00 - 15:15
|Tea break
|

|-
|15:15 - 15:45
|9.4 Commitfest schedule and tools
|Josh

|-
|15:45 - 16:45
|Goals, priorities, and resources for 9.4
|All

|- style="font-style:italic;background-color:lightgray;"
|16:45 - 17:00
|Any other business/group photo
|Dave Page

|- style="font-style:italic;background-color:lightgray;"
|17:00
|Finish
|
|}

== Notes ==

Attending:

* Dave Page, EnterpriseDB
* Andres Freund, 2ndQuadrant
* Kevin Grittner, EnterpriseDB
* Dimitri Fontaine, 2ndQuadrant
* Andrew Dunstan, PostgreSQL Experts
* Noah Misch, EnterpriseDB
* Bruce Momjian, EnterpriseDB
* Fujii Masao, NTT Data
* Tom Lane, Salesforce
* Magnus Hagander, Redpill Linpro
* Robert Haas, EnterpriseDB
* Josh Berkus, PostgreSQL Experts
* Kaigai Kohei, NEC
* Jeff Davis, Teradata
* Alexander Korotkov
* Peter Geoghegan, Heroku
* Peter Eisentraut, Meetme
* Stephen Frost

=== Parallelism ===

Bruce Momjian is looking at where Postgres is and hardware changes, and it's time to look at parallelism. Unlike the Windows port and pgUpgrade, there's no clear "done" with Parallelism. We're going to have to do a lot of small things, but not one big feature. Concern anout code cleanliness and stability. What is going to have to happen is that we'll attack one small thing, and build the infrastructure for parallelism.

Robert Haas is talking about EnterpriseDB's commitment to parallelism. The two things EDB wants is materialized views and parallel query. The way we're approaching this is the same way as 2Q approached logical replication for the last release cycle. We're doing this as a company, and we have buy-in from our management. So far there's a wiki page on parallel sort and Noah's posted some stuff to pgsql-hackers. The first part is to get a credible worker system in place, and then we can tackle parallelising particular things.

Stephen Frost pointed out that users are currently ad-hoc implementing parallelism in their middleware code. Bruce said that there was a basic set of steps for all parallel tasks. There's a false sense that threads automatically give you infrastructure for parallelism. Bruce doesn't think that's true. Having the worker/marshalling stuff sprinkles all over the code would be really bad, so we want central infrastructure.

Jeff Davis pointed out that there were different approaches to parallelism. One is "cluster parallelism". Do we know what approaches were taking? Cluster parallelism involves making the parallel tasks according to data partitions. It's popular in data warehousing. Robert Haas doesn't expect to get that far in one release cycle.

Haas: People come up with great ideas for PostgreSQL, and they do two things: either they figure out how to do it without modifing the query planner, or they fail. So we looked at index building, which wouldn't require dealing with the query planner. But the general problem of parallel query planning, we have to solve harder problems. I don't want to get bogged down in those sorts of questions at the outset, because there's a bunch of stuff to get done to execute parallel jobs in general.

Josh Berkus suggested implementing a framework for parallel function execution because then users could implement parallel code for themselves. It would help the Geo folks. Noah thinks this is possible today, but isn't specific how. Tom argues against exposing it to users in early iterations because the API will change.

There's a few things you need:
* and efficient way for passing data to the parallel backends, probably using a shared memory facility, because sockets are too slow.
* some logic for starting and stopping worker processes. Custom background workers aren't quite what we need for this. Also different from Autovacuum, which is a bit kludgy.
* you need to be able to do stuff in the worker processes as if they were the parent process. They need to share the parent worker's state, and there are a lot of state things which are not shared. If the master takes new snapshots or acquires extra XIDs, not sure how to share that. Some things will need to be prohibited in parallel mode. Threads don't solve this. Syscache lookups are also a problem, but we need them.

Noah wants to target parallel sort, specifically parallel memory sort. This hits a lot of the areas we need to tackle to make parallelism work in general. We need a cost model as well. How are we going to mark the functions which are safe to run in a parallel worker. We don't want to just call functions *_parallel because that will change. Maybe there will be an internal column in pgproc, as a short-term solution.

Peter E. asked about timeline. For 9.4, we want to at least have an index build which runs a user-specified amount of parallelism. It needs to be reasonably fast.

Peter G. asked about having a cost model for parallelism. Right now we don't have costing for how long it takes to sort things based on the number of rows. Sorting a text column in bad collation can be 1000X as expensive as sorting integers, for example. We might pick a single operator and make that the cost reference operator. Perfect costing isn't possible, but we can do some approximates. The initial operations we choose for parallelism will be very long operations. Startup costs are too high otherwise. We're not going to parallelize something that's 200ms. Something that takes 10s or a minute or a couple minutes.

Haas thinks that a lot of people will be appalled for starting up a parallel worker. That can be optimized later. It's OK for the initial version to be unoptimized. Even if it takes a full second to start up a new backend, there are sorting tasks which take large numbers of seconds. Those are existing issues which we'll hammer on as we get into this space; we may fix starting up a new connection speed in the process.

Josh pointed out that taking a hour to build an index, it's probably an external sort. Noah posted a patch to allow larger internal sorts, over 1GB. Andrew pointed out that a process model would tie us to certain large operations. Threads would add a lot of overhead to everything, though. We'd have to rewrite palloc. Haas things we can get the minimum unit down to something fairly small. Andrew pointed out that on windows process creation is very expensive. Haas doesn't want rewrite the entire internal infrastructure.

With Threads, everything is shared by default, with processes, everything is unshared by default. The process model and explicit sharing is a shorter path from where we are currently. Parallelism helps with CPU-bound processes, but not IO. Josh argued with Kevin that there are some types of storage where this isn't true. Kevin just pointed out that if the resource you're using the most of isn't bottlenecked, then it's not helpful to parallelize. Haas pointed out that parallelizing seq scan on a single rotating disk won't help, as opposed to parallelizing scan from memory, which would be much faster. Our cost model isn't up to this; we might even have to have a recheck model where the executor notices things are slow and switches approaches.

Bruce pointed out how Informix switched to threads between 5 and 7 and it killed the database. Parallelism will take Postgres into new markets.

Andrew pointed out that prefork backends will help us form new connections if we can get it to work. Haas pointed out that we're going to have to cut nonessential issues to avoid taking forever.

=== Pluggable plan/exec nodes ===

Kaigai is working on GPU execution. When he worked on writable FDW, pseudo-column approach for foreign scan node returning an already computed value, but that was rejected, because the scan plan needs to return the data structure as its definition. So Kaigai wants to add an API to add a plan node to the exeuction node, allowing executor to run extension code during query execution. When plan tree tries to scan large table with sequential scan, and the target list has a complex calculation, we can have a pseudo-column which does this calculation on a GPU.

Kaigai is talking about planner and executor. Haas doesn't understand how we would have pluggable planner nodes, as opposed to executor nodes. How would you allow it generate completely new types of plan nodes? We can replace existing plan nodes, but new types of nodes would require a new extensibility infrastructure. To do this, we need two new infrastructures to inject plan nodes and executor nodes. But Kaigai is mainly focused on is replacing existing scans and sort nodes. He didn't investigate the difficulty on planner extension yet.

Peter E. pointed out that 65% of the work will be the ability to add new nodes at all. Replacement will be MUCH easier. However, the ability to add new nodes would be very useful to PostgreSQL in general. Tom thinks that it could be done. Haas pointed out that we have a lot of wierd optimizations about what plan node connects to which other plan node. Tom doesn't think that we have that many. Noah says we'll probably use a hook.

For a new executor node we have a dispatch table, it's easy. Plan nodes could use a jump table too. Right now we have function volatility markers; for nodes we'll need the same thing. But that's a problem only for expression nodes.

This was discussed in the cluster meeting. PostgresXC wanted pluggable nodes for cluster scan, as do some other people. So a general pluggability infrastructure would be good. If we have pluggable scan nodes, we can plug in cluster scan as well as GPU scan.

Jeff Davis pointed out that range join could be our first pluggable node. Haas pointed out that opclass support requirements might make it difficult; there are easier cases. Range join might need to be hardcoded. Pluggable planner stuff is hard.

This would also maybe get people who fork Postgres to stay closer to the core project and implement extensions instead of having an incompatible fork which then doesn't work with others.

=== Volume Management ===

Right now we have tablespaces. Having some more automation around using them would help. Like we want the indexes on a separate tablespace from the heap; there ought to be automation for this. Somebody hacked up something like this ... maybe Depesz, in 2007.

Haas asked if having indexes on a separate volume was actually faster. Frost asserted that it was. Josh brought up that with new small fast storage there's reasons to want stuff to move around again. Also, index-only scans. If I only have one column, then I can do index-only scans, so I want to put the index on faster storage. Josh pointed out that indexes-separate worked back when at OSDL.

Stephen Frost pointed out that they have pairs of drives, with a whole lot of pairs. Stephen asked about whether or not we'll ever have things like Oracle Volumes. Kevin said that that configuration works on raw devices, but not so much on complex filesystems. FRost says that for specific workloads, it really works to parallelize everything for massive joins.

Several people asserted that modern RAID is fairly efficient. Josh asked if any default automated rules would work for a general class.

Frost explained Oracle Volumes. They can be used to go to raw drives. Volumes are disks or drives or files. You can have multiple volumes under a single tablespace, and Oracle will manage them together. Do we want to do that? Maybe we should just use LVM.

There's also some other things we could do with volumes, like compressed volumes. Noah has seen tablespaces abused 5 times as much as used properly. We should be careful that what we're adding is really useful. People want things like encrypted & compressed tablespaces. Every time something like this comes up, Tom or someone says "use a filesystem which provides that." There are some advantages to having the database do that, but there's a lot of development effort.

Noah suggested that event triggers would do this. Frost says that they already have code, they want to simplify it. Josh points out that there aren't simple rules for this; most DWs don't have rules which are as simple as "indexes go here, tables go there". A lot of this is misplaced Oracle knowledge. Josh brought up the backup issue again.

=== Utilization of upcoming non-volatile RAM devices ===

Kagai is just presenting an idea and asking for opinions. Wants to consider the impact of these new devices. Wants to boost the performance to write the transaction logs. Once we have mmaped to the nvram device, then we can consider that completion of log writing. Non-volatile ram will be exposed as main memory. There are Linux patches which allows mmaping to these devices. Once we have these devices, it dramatically reduces the overhead to write the transaction log.

Kaigai said that this topic comes from the forks on ram storage devices. We wanted improvement of row-writing performance. Also appliable for general fast SSD storage. Wants to add fork around row-writing and create extensions where we maybe have nvram and mmapped region for the xlog.

Josh points out that this dovetails with transactional memory, which allows lock exchange for multiple K of data and multiple instruction. Like we might not need as much from the WAL. Others disputed that.

Kaigai wants to implement this for the write-ahead log. Then later we can look at the heap. NVRam has limited write cycles, so we don't want to use it for main storage.

=== Logical changeset generation review & integration ===

Andres gave a progress report on current status of the changeset generation patch. It's mostly done, but we need to integrate it and it needs substantial review. He needs someone to help, not from 2Q -- someone independant. How can he proceed to get the pieces into core?

Haas suggested that they could swap for review on parallel query. The patch is currently about 12,000 lines. Peter G says he can take a shot at it, but he wants someone else.

Frost asked how much was additional stuff there is vs. changes to existing behavior. There's new kinds of TOAST tuples. There's mapping relfilenode back to the OID, which is controversial. Andres says that it's safe the way they do it, and if it's broken in logical replication then it's already broken in Hot Standby, so we should fix it. There's some changes to heapam.c, like preventing full page writes from removing the tuple contents.

Haas points out that Heikki objects to most of the functionality in core. Andres says that he can reduce complexity in some areas. Noah says that having zero implications for nonusers it can't possibly work. If it's a good thing, we're going to have to accept some code complexity cost to do it.

The write-ahead log currently is designed to do crash recovery. We've pushed that for hot standby and PITR, but this is pushing it even further, we're adding another level of pain. TOAST tuples are a good example of this; how we crash recover them doesn't work for logical replication. To what extent do we use the WAL format we have now, and how much do we modify it? What's the performance cost?

Some things don't need much intervention. But we have to look up stuff in the catalogs, and do catalog time travel to figure out what the object references mean. Peter E asked about simply creating a completely separate log for logical replication. Andres says that the tried this, but it had a huge extra cost, because you have to add a whole bunch of extra data to it. That would double synchronous writes, at least. So are we not going to investigate this? The logical replication stream would need to include catalog updates.

Haas points out that the kludges for this aren't as bad as hot standby. Magnus points out that MySQL has to do 2PC between the binlog and the innodb log. But a lot of people are going to want this feature.

Steve Singer has written 80% of the code for basing Slony on LCR. 2Q is also working on their own replicated solution for this.

So the other modification we need to deal with xmin for pg_catalog and user tables differently. But this had some benefits for general Postgres efficiency. Can we split this up at all? The earlier patches can be reviewed separately.

One other thing we devised for this is logical replication slots. You need to know where readers last left off. We could maybe use this for binary replication as well. You can decode on a hot standby, not just on a master. Primary key updates are handled. We only have the TOAST tuples if they change, though. We have before images, but they have some limitations. Kevin asked about rolling backwards to an earlier state.

Haas will review some of the patch, but not the whole thing. We had the same issue with streaming replication, not enough reviewers. Haas would like to get it done early in the first commitfest. Andres will submit but is not sure we can commit it in the first round.

=== Representing Severity ===

Peter G. agitated about that there's no principled way to discover whether a "severe" error occurs, such as corruption from errors in xlog.c. There's no way that DBAs can confirm that an error of particular severity has occured, except grepping for particular strings. There's multiple distinctions here. Such as there are various "can't happen" errors which our developers want to see as soon as possible. Heroku also wants to bring errors to the attention of the lists automatically. The tail-n-mail approach is not scalable.

Haas's object is whether it's possible to agree on categorization about whether or not something is severe. We have hundreds of ereports, so we need a very simple categorization scheme which everyone can agree on. Tom points out that "can't happen" and "severe" aren't necessarily the same thing. Maybe we could use SQLSTATE and tweak some things. Everyone thinks this is the way to go.

It would be very useful to be able to filter the volume of logging too. We need to log sqlstate though. We might in addition put the severity concept into the log. We'd have to clean up how we use SQLSTATE too. We could supply some default filters, but users would need to tweak them. Filtering the log is a separate feature from fixing the SQLSTATE. That can be done later.

=== Error Logging Infrastructure ===

Peter G: As a complimentary feature, it would be great to be able to store errors in a circular buffer available, like pg_stat_statements. Peter E is doing a presentation related to this tommorrow using logging hooks. You can already do this.

Peter E argues against doing more aggregated views like pg_stat_statements, because it doesn't provide historical or granular data. With logging hooks, you can build this more granularly. It already exists in 9.2. The average DBA can't write code in C, logging hooks allow you to work around this, you can get log data in JSON. Peter E argued against having a hardcoded system for this.

Josh pointed out the efficiency argument. Robert pointed out the usefulness of "last message repeated 351 times". Peter G envisaged doing pg_stat_errors as a contrib module. This could be build on logging hooks. Dimitri pointed out that you can also do this with FDWs and CSV logs. Some discussion about how does Oracle Enterprise Manager do this? PEM uses CSV logs.

Discussion about alternative approaches ensued.

Heroku can feed back some information about errors using log aggregation. Frost would love to have performance information from Heroku.

=== Incremental Maintenance of Matviews ===

Kevin: Matching other DBs who have matviews will require a 5 year plan. We have a foundation in 9.3, you can declare a view, and it's materialized. It's just a foundation at this point. For 9.4, we want the first level of incremental maintenance. We can't get all the optimizations in, we just want to make the infractructure for the simplest cases in there.

For the first commitfest, I want a transactional REFRESH using the approach on the lists. There will always be cases where incremental maintenance won't work. So we want transactional refres first, so that we can analyze whether it pays to update using the delta. If every row is touched, that's a loser. So we want to fall back to the full refresh if the delta is too large.

He won't speculate on what we can get into each subsequent release. Planning to use the counting algorithm, it's very well established. Will only handle the simple cases at first, and will work through complex cases and see how far they get.

Need a new system column, count_t, which counts the number of ways a give tuple can be derived from source data. It's a kind of reference count. There's a completely different algo for recursive views. Handling NOT EXISTS is a separate implementation too.

What he wants to get in these first releases is how to implement the count_t column, how to implement other stuff in the first commitfest. Then the other CFs can be refinements. Peter E points out that count could overflow. The new column will be added only for new matviews which are specified as "incremental". We'll probably have other stratgies for incremental update, and it'll only be used by the counting strategy. You'd have to do an ALTER table to change that.

Thinking of doing the incremental change as synchronous in the transaction. Initially will not work for async maintenance. You need a delta with a count which can be plus or minus, you need a snapshot for before and after the change. You need to generate the deltas with each change to the base table, but you don't need to update the matview immediately.

There was discussion about how this applies to expensive, infrequently updated materialized views.

The extra column would only be present in incremental materialized views and the deltas. There would also need conditional code in execution nodes which know how to handle the column. Haas questioned if there was some way we could add a non-system column. How do the executor nodes know not to display the system column. It's not like SELinux because it wouldn't be present in every table.

Maybe we need a general notion of invisible columns. That would be generally useful. Then we could have extra state information. Bucardo could use it. A lot of things want to hide columns from "select *". We should add this concept.

There was discussion about having stable system views. This change will break things like the ODBC driver, because it gets the list of columns from pg_attribute. We can start with just matviews. But right now drivers assume that anything with a positive attnum is visible. But there's no patch for logical column numbers now. Alvaro has a patch for it, it's complete but it's bitrotted. This is just like attisdropped; it broke a lot of things. Alvaro might update it, he's not sure he'll have time. But we have to get this done in CF1.

If the next version is 10.0, though, we could break things. So maybe it's a good time.

=== Lunch Notes ===

Robert suggested getting rid of the v2 protocol in the next version. He thinks there's hacks in COPY which slow it down and are required for support of v2. But people want to see numbers on that. v2 is also used for JDBC, but that's to fix the plan cache issue which is fixed in 9.2. We need to see concrete improvements, though, and then there'd be an easy switch. We also should know what drivers are using v2, like pygresql.

=== GIN generalization ===

Alexander talked about general advances in GIN. Many people use external full text search because it's faster. One of the reasons GIN is slower is that it's used only for filtering, forcing the executor to page all of the results, and ranking. And it doesn't do LIMIT.

So how the extended indexing solution works: there's positional information, so that we can calculate relevance. So there's now a structure to add extra infromation to the vector for any additional information. We can extract positions of words. To keep the index small we're using run-length compression. The index is then about the same size as the uncompressed index, with a lot of extra information.

The problem is that we use the infrastructure from KNN. GIN declares that it can do ORDER BY with an operator, so you can get ORDER BY and filtering from the same index. Noah asked what ordering operator he's talking about. GIN calculates distance on item vectors, and then sorts by an array, and then executes gin_get_tuple(), which is new. It can then return them one-by-one for sorting. It's kind of like KNN, but it works differently, but it's much cheaper than getting the tuple from the heap. We need to tell the planner that we can return the original tuple from the index.

This will be a new operator class. And we modify the operator classes for tsvector.

Peter E asked for applications outside tsearch. Alexander said that it would also work for similarity searches. Can also be used for regex index. You can build a regex, index it, and then search for text strings which match that regex.

Andres pointed out an alternate method for doing this, but it requires improvements to the planner. Haas encouraged maybe taking this approach. If we assume that the planner can get an expression from the index, then we need sorting before we go into the heap. That's going to be hard. That's a real issue for any steps which happen between checking the index and the heap. You could see tuples which aren't visible. This might not be a problem for the GIN case, but it's liable to be a problem for the general case.

Maybe this is related to bitmap indexes. Btree operators need to be able to deal with invisible tuples, so this isn't new.

Discussion of implementation methods ensued. There was question about what doesn't work in KNN-Gist infrastructure. Like it doesn't work without a WHERE clause. But this could be generalized. This will break amproc, but it's not like anyone makes access methods outside of core. Maybe we should get rid of pg_am and just have a jump table, pg_am just isn't really useful. We can't drop it because the oids there are used as keys elsewhere, and there's really not much cost.

Another infrastructure question is that currently GIN only has to know one data type. Now when we introduce additional information, we need to know datatypes. SP-GIst needs 3 datatypes. That's why we have spgist_config. For GIN we'll need a new configuration method to return this datatype. So there's a question of breaking older stuff. Can you support both opclasses? Storage parameter would define it, so it would appear in conflict.

This should probably be GIN2 with new access methods, etc. We'll be changing the storage format of GIN indexes. But that would require supporting the original GIN indefinitely. We could bump the version number on the GIN node page. Two choices: write the code so that it supports the old format, or add a whole new access method. Are we changing the operators or the access methods? Changes are mostly in GIN, a lot less in the operator class. Peter E pointed out that the compression could be a separate feature. Alexander says that the the change is not easily separable because of some of the performance optimizations.

There was more implementation discussion about the methods for doing online upgrade if we change GIN. Rewriting on selects is out. In general we want to deprecate the old GIN, but we'll need a couple versions to do it.

Frost raised the idea of general infrastructure to support upgrade-in-place. Like we could have a flag in pg_class. But several people dismissed this idea as not really helpful.

=== An Extensibility Roadmap ===

See slides linked from this page.

We've already did some extensibility with create extension and create event trigger. Now I need the extension templates. I want reviewers and committers to understand where I want to go.

DDL Execution Plans is after that ... like EXPLAIN ALTER TABLE. Dimitri wants CREATE EXTENSION to fetch code from the internet. But core doesn't want that, so Dimitri wants to do that using extension templates and event triggers. Then he wants a PL/C language which compiles on-the-fly. This would allow creating extensions from source dynamically. But the source is in a place only root has access to.

Dimitri wants extensions to follow replication. There's some security issues with that. There's issue with having such things like downloading in core and having it not suck. There's a lot of reliability issues. Haas isn't keen on the whole idea. But Dimitri doesn't want to code all of the extension features in C. But this is already a problem with external PLs.

Dimitri wants to be able to build full set of extensions using only a PostgreSQL connection and superuser access. Josh talked about the developer-push issue. But there's security issues with arbitrary C code, so a lot of people have issues with it. Peter G suggested we could somehow sandbox the C code. Haas says that this is possible, but you need kernel-level help.

Josh says 3 problems: developer push, relication, and backup. But what if we solved it for non-C cases, but not C cases? Some people thing it would be useful. Extension Templates for packaged user code would be very useful for in-house code, so it can be packaged as an extension.

=== Failback with Backup ===

Fujii hears complaints from users: why do we need to take a full snapshot for failback? If the database is very large, this can take a long time. Three problems: timeline ID mismatch, WAL record mismatch, database inconsistency.

Timeline ID: we can't start the old master as a replica because of the timeline ID. This is resolved now in 9.3.

WAL Record Inconsistency: if the master crashes after writing a WAL record but not sending it to the standby, then it has WAL records the standby doesn't have. We can resolve this by removing all WAL records in the old master before starting it up in standby mode. Old master will then try to start recovery, and them start replication to retrieve the WAL records. One problem: last checkpoint record may not be replicated to the standby. Could we make the checkpointer wait for replication? If the Standby goes offline checkpoint would hang.

Frost asked how large of a user problem this is? People cited examples.

Haas suggested a holdback for datafile sync. Magnus pointed out that if we distinguish between failover and switchover. So we could have failback only for switchover circumstances. Jeff pointed out that you have to break replication and resync standbys after upgrade. He also thinks that we can use the WAL to help a diff between datafiles. Controlled switchover would fix the upgrade case too maybe.

We could create a list of dirty blocks from the WAL. But there are a lot of special cases which would prevent rollback.

Database inconsistency can happen if the master crashes. There can be some missing database changes. One solution is implementing undo logic, but that's very very difficult. Fujii's approach would be to add some write points to the master. Before the master writes the database to the disk, it writes the WAL to disk. It could wait for replication of WAL before writing to the LSN. Except for hint bits. And what if the replica goes away?

Fujii wants to solve both controlled switchover case as well as the crash & failover case. The controlled swithover case is a lot easier to solve. You could freeze out existing connections, finish writes, and then switch over. There's a lot of similar ideas in Oracle QS.

Josh suggested that the crash case isn't any different if we're already in synch rep. Others disagreed. Haas suggested that we could use Andres' infrastructure of slots for replication. Noah suggested that we could delay hint bit application until we're after walflushlocation. Maybe we should only solve this for controlled switchover.

Jeff: for checksums, we already store the first modified record after the checkpoint. We could store very abbreviated information the WAL for checkpoints. There would be some significant cost for hint bits on a seq scan. If you want to be able to turn the WAL log into an undo log. You only have to copy certain pages. You could log only the first change since the last checkpoint.

=== 9.4 Commitfest schedule and tools ===

The CF schedule will be the same as last year. This time do what we said we'd do, not what we actually did. So include the triage periods etc. Josh will run first CF.

Discussion about patch development during commitfest. Ending a commitfest takes a lot of work. There's a lot of work for moving stuff along. We could make a commitment that -hackers will not argue with the CF manager. Josh also wants to have a commitfest assistant. He called for volunteers. The author ought to be the one to take the patch out, it shouldn't wait on the CFM. Each patch deserves one solid review per CF, but not more than one.

Kevin points out that the biggest issue is finding reviewers and getting the reviewers to do the review. Josh asked if having reviewers put their name down is actually helpful. Maybe we need to be really aggressive about taking names off. There's a big difference between Noah doing a review and some new reviewer doing a review. Josh suggested clearing reviewer after 5 days. Frost suggested removing them earlier. Maybe more tightly tying them to the archives would be good.

Lots of discussion ensued.

We should tell people to not put their names down until they are ready to start reviewing. The 10K line patches are not the problem, the 10 line patches are.

Josh discussed replacing the CF tool, either with Gerrit or Reviewboard or something new. Frost suggested Github. Josh discussed some issues for reviewers. Discussion ensued, but not recorded because the secretary was speaking. Josh also wants automated patch build. Other people suggested automated apply.

Josh will develop a spec for a new tool. He will run it past the CF managers for comment. Then post it to -hackers for comment. Haas is skeptical about 3rd-party tools. He hates git stuff. Frost discussed using forks in github. We should be able to track a git branch in the tool.

=== Goals, priorities, and resources for 9.4 ===

* Dave hopes to start writing pgAdmin4 this year.
* Kevin will work on materialized views.
* Dimitri: Extension templates and event triggers.
* Andrew: most JSON work is complete, the rest can be extensions. Wants to work on grouping sets.
* Noah working on EDB features (see above), and bug fixes.
* Bruce is working on pgPool to make it better and more usable, maintainable and debugged.
* Fujii is working on PostgreSQL replication. Would also like to work on pg_trigram for Japanese.
* Simon (via Skype) Tablesample patch, COPY tuning, and security reviews (SEPostres).
* Tom will take an interest in pluggable storage. Maybe also Autonomous transactions.
* Magnus will work on a new CF tool. Also more improvements to pgbasebackup.
* Haas will be working on side issues on matviews and parallel query as they come up.
* Josh will be working on new CF tool. With time/funding will work on automated testing.
* Kaigai will work on row-level security. Also GPU stuff.
* Jeff will be working on convincing Robert to remove pdallvisible. Also corruption detection. And Range JOIN, or inclusion constriants.
* Alexander will work on new GIN.
* Peter G will be working on error stuff (per above). Also wants to work on fixing archiving falling behind and panic shutdown. Also doing a little bit of work on upsert.
* Andres will be doing logical replication.
* Peter E has the transforms feature outstanding. And wants to move the documentation to XML. Wants to improve test coverage.
* Frost will be doing pg infrastructure. Wants to help with CFs. Also fix some stuff with optimizer.
* Greg Smith is working on autovacuum style I/O limits for other statements.

[[Category:PostgreSQL Events]]

Development information

2013-05-22T16:07:02Z

Gsmith: Link to 2013 developer meeting page

__NOTOC__
This area includes developer-targeted documentation regarding aspects of PostgreSQL development. Please visit the [http://www.postgresql.org/developer developer area] of the PostgreSQL website for more general information about the development of PostgreSQL. You can find most developers in [irc://irc.freenode.net/postgresql #postgresql on freenode]. A list of IRC nick names with their respective real world names can be found [[IRC2RWNames | here]].

==PostgreSQL 9.3 - Active Development==
* [[PostgreSQL 9.3 Development Plan]]
* [[PostgreSQL 9.3 Open Items]]

==Development Process==
* [[Todo|Todo list]]
* [[Todo:Contents|Unofficial Todo Detail]]
* [[Submitting a Patch]]
* [[Reviewing a Patch]]
* [[RRReviewers|Round-robin Patch Review]]
* [[Running a CommitFest]]
* [[Committing with Git]]

== Developer Resources ==
* [[Developer FAQ]]
* [[Regression test authoring]]
* [[HowToBetaTest|HOWTO Alpha and Beta Test PostgreSQL]]
* [[Working with Git]]
* [[Working with CVS]] (obsolete)
* [[Working with Eclipse]]
* [[Fixing shift/reduce conflicts in Bison]]
* [[PL Matrix|Procedural Language Matrix]]
* [http://www.postgresql.org/about/featurematrix Feature Matrix]
* [http://www.postgresql.org/developer/coding PostgreSQL Coding]
* [http://developer.postgresql.org/pgdocs/postgres/index.html Development docs] (updated every 5 minutes)
* [[Project Hosting]]
* [http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf Exposing PostgreSQL Internals with UDFs (2010)]

== Projects and Planning ==
* [http://commitfest.postgresql.org/action/commitfest_view/open Open CommitFest] - New patch submissions for 9.3
* [https://commitfest.postgresql.org/ CommitFest]
* [[PostgreSQL 8.4]]
* [[PgCon 2013 Developer Meeting]]
* [[PgCon 2012 Developer Meeting]]
* [[PgCon 2011 Developer Meeting]]
* [[PgCon 2010 Developer Meeting]]
* [[PgCon 2009 Developer Meeting]]
* [[PgCon 2008 Developer Meeting]]
* [[Development projects]] - links to individual projects

==PostgreSQL Past Development==
* [[PostgreSQL 9.2 Open Items]]
* [[PostgreSQL 9.2 Development Plan]]
* [[PostgreSQL 9.1 Open Items]]
* [[PostgreSQL 9.1 Development Plan]]
* [[PostgreSQL 9.0 Open Items]]
* [[85AlphaFeatures|PostgreSQL 9.0 Alpha Release Feature List]]

[[Category:CommitFest]]

Development information

2013-05-19T05:35:45Z

Gsmith: Eclipse documentation doesn't just cover cvs now

__NOTOC__
This area includes developer-targeted documentation regarding aspects of PostgreSQL development. Please visit the [http://www.postgresql.org/developer developer area] of the PostgreSQL website for more general information about the development of PostgreSQL. You can find most developers in [irc://irc.freenode.net/postgresql #postgresql on freenode]. A list of IRC nick names with their respective real world names can be found [[IRC2RWNames | here]].

==PostgreSQL 9.3 - Active Development==
* [[PostgreSQL 9.3 Development Plan]]
* [[PostgreSQL 9.3 Open Items]]

==Development Process==
* [[Todo|Todo list]]
* [[Todo:Contents|Unofficial Todo Detail]]
* [[Submitting a Patch]]
* [[Reviewing a Patch]]
* [[RRReviewers|Round-robin Patch Review]]
* [[Running a CommitFest]]
* [[Committing with Git]]

== Developer Resources ==
* [[Developer FAQ]]
* [[Regression test authoring]]
* [[HowToBetaTest|HOWTO Alpha and Beta Test PostgreSQL]]
* [[Working with Git]]
* [[Working with CVS]] (obsolete)
* [[Working with Eclipse]]
* [[Fixing shift/reduce conflicts in Bison]]
* [[PL Matrix|Procedural Language Matrix]]
* [http://www.postgresql.org/about/featurematrix Feature Matrix]
* [http://www.postgresql.org/developer/coding PostgreSQL Coding]
* [http://developer.postgresql.org/pgdocs/postgres/index.html Development docs] (updated every 5 minutes)
* [[Project Hosting]]
* [http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf Exposing PostgreSQL Internals with UDFs (2010)]

== Projects and Planning ==
* [http://commitfest.postgresql.org/action/commitfest_view/open Open CommitFest] - New patch submissions for 9.3
* [https://commitfest.postgresql.org/ CommitFest]
* [[PostgreSQL 8.4]]
* [[PgCon 2012 Developer Meeting]]
* [[PgCon 2011 Developer Meeting]]
* [[PgCon 2010 Developer Meeting]]
* [[PgCon 2009 Developer Meeting]]
* [[PgCon 2008 Developer Meeting]]
* [[Development projects]] - links to individual projects

==PostgreSQL Past Development==
* [[PostgreSQL 9.2 Open Items]]
* [[PostgreSQL 9.2 Development Plan]]
* [[PostgreSQL 9.1 Open Items]]
* [[PostgreSQL 9.1 Development Plan]]
* [[PostgreSQL 9.0 Open Items]]
* [[85AlphaFeatures|PostgreSQL 9.0 Alpha Release Feature List]]

[[Category:CommitFest]]

Working with Eclipse

2013-05-19T05:34:55Z

Gsmith: Intro to adding include files

== Developing with Eclipse ==

This page explains how to get quickly started if you want to use Eclipse to develop PostgreSQL.

Used software for this manual: 
''OS: Ubuntu 8.4 
Editor: Eclipse Helios 
Versioning: CVS''

The manual has been tested on an Ubuntu Live Version to make sure that no required Software is already installed. 
'''!!Important: An internet connection is required''' 
(Remark: If you haven’t installed Ubuntu yet, we recommend the following Link: http://www.ubuntu.com/desktop/get-ubuntu/download )

== Install version control software ==

The preferred way to get PostgreSQL source code is to use the git version control software. This tutorial will also cover using the older cvs program. You only need one program, git or cvs, not both.

=== Install git ===

Start Ubuntu Software Center: 
''Applications » Ubuntu Software Center''
 
 
To install git you should go to the magnifier on the right top side of the application, and enter '''"git"'''. From the results choose '''"fast, scalable, distributed revision control"''' and click on the '''Install''' button (if git is not already installed).
 
 

=== Install CVS ===

Start Ubuntu Software Center: 
''Applications » Ubuntu Software Center''
 
 
[[Image:03_Open_SW_Center.png]]
 
 
To install CVS you should go to the magnifier on the right top side of the application, and enter '''"cvs"'''. From the results choose '''"Concurrent Versions System"''' and click on the '''Install''' button (if CVS is not already installed).
 
 
[[Image:04_Install_CVS.png]]
 
 

== Open terminal ==

Through ''Applications » Accessories » Terminal'' you can open a new terminal: 
[[Image:01_Open_Terminal.png]]

== Install PostgreSQL source code ===

Now you can download the latest version of the source code, so you can edit it locally in your computer. Again you need to follow one of the cvs or git instruction sets, but not both.

=== Download with git ===

In the previous terminal, if you haven’t closed it (otherwise repeat step '''1. Open terminal''', and don’t close it until the end of the installation), you can make a copy with the following commands:

mkdir project
cd project
clone git://git.postgresql.org/git/postgresql.git
mv postgres pgsql

You can find more detailed information about setting up your own repository at [[Working with git]]. The cloned directory here is renamed to "pgsql" to match the cvs examples below. You can leave it as postgres instead, and just modify the rest of the examples below to use that name.

=== Download PostgreSQL source code - CVS ===

In our example the CVSROOT is '''''pgrepo''''' (you can choose also an other name), and is located under Home

The command mkdir creates a new folder ('''pgrepo''') and makes a new environment variable that links to this folder:
mkdir pgrepo
export CVSROOT=$HOME/pgrepo

The next command downloads with ''rsync'' the source code. This can take several minutes:
rsync --progress -avzCH --delete anoncvs.postgresql.org::pgsql-cvs $CVSROOT

[[Image:02_Download_repository.png]]
 
 

=== Copy with CVS ===

With '''CVS''' (Concurrent Version Control) you can make a versioned copy of the folder '''pgrepo'''.

=== Use CVS ===

In the previous terminal, if you haven’t closed it (otherwise repeat step '''1. Open terminal''', and don’t close it until the end of the installation), you can make a copy with the following commands:

mkdir project
cd project
cvs co pgsql

'''Warning:''' Execute these commands one layer above pgrepo, for instance in Home in our example!
 
 
[[Image:05_checkout_project.png]]
 
 
You can find more detailed information about setting up your own repository here [[Working with CVS]].

== Configure source code ==

=== Install components with Software-Center ===

You need the following Software, and you can download them through Software Center (''Applications » Ubuntu Software Center''):

''libreadline5-dev'' 
[[Image:06_Download_readline.png]]
 
 
''zlib1g-dev'' 
[[Image:07_Dowload_zlib.png]]
 
 
''bison (YACC)'' 
[[Image:08_Dowload_bison.png]]
 
 
''flex'' 
[[Image:09_Download_flex.png]]
 
 
You simply have to type the names of the software in the search area, and install them. This is the list of the required Software: 
1. ''libreadline5-dev'', 2. ''zlib1g-dev'', 3. ''bison (YACC)'', 4. ''flex''

=== Configure in the terminal ===

Switch back to the terminal and execute the following commands:

cd pgsql
./configure --prefix=$HOME/project --enable-depend --enable-cassert --enable-debug

[[Image:10_Configure.png]]
 
 
== Download Eclipse ==

=== Install JDK for Eclipse ===

To see the PostgreSQL source code in an organized manner we will install Eclipse on the computer. 
(Remark: You can use any other development tool also, but this manual explains the usage with Eclipse).

First of all you need the ''"OpenJDK Java 6 Runtime"'' for Eclipse, which you can also download in the Software-Center. 
Start Software Center Center (''Applications » Ubuntu Software Center'') or switch to the opened window and search for java. Choose ''"OpenJDK Java 6 Runtime”'' and click '''Install'''.
 
 
[[Image:11_Download_JDK.png]]
 
 

=== Download Eclipse ===

You can download Eclipse from the following link: http://www.eclipse.org/downloads/packages/eclipse-ide-cc-developers/heliosr

It’s important that you choose a '''Helios Version''', which supports''' C/C++'''. Open the Folder in your file browser, where the tar file is. In this example: ''ubuntu » Downloads''.

[[Image:12_Downloaded_eclispe.png]]
 
 

=== Extract and start ===

Right click on the Package and choose ''"Extract Here"'' from the menu.

[[Image:13_Extract_eclipse.png]]
 
 
Now enter the extracted Folder and double-click the file "eclipse".

[[Image:14_Launch_eclispe.png]]
 
 

=== Set up Workspace ===

You should set up a workspace, where the software stores your projects:

After starting the program you will be prompted to select a folder for your workspace. We will choose the recommended path, but you can change it if you browse for an other folder. If you have chosen your Folder click '''''"OK"'''''.

[[Image:15_Worspace.png]]
 
 

=== The program ===

Now you see the welcome-page of Eclipse, where you can choose tutorials, if you are not familiar with Eclipse. To continue the manual choose the arrow on the right to open your Workbench (the defaults layout of the program).

[[Image:16_Workbech.png]]
 
 

We will import the PostgreSQL code in this workbench.
 
 
[[Image:17_Layout.png]]
 
 

== Import ==

In this step we will import the project. Select in the left top of the program ''"File » Import "''

[[Image:18_Import.png]]
 
 

In the new window select ''"C/C++ » Existing Code as Makefile Project"'' and click the '''''"next"''''' button:

[[Image:19_Makefileproject.png]]
 
 
In the next window click browse, and choose the '''pgsql''' folder.
''"Language"'' is '''"C"''' and ''"Toolchain for Indexer Settings"'' select '''"Linux GCC"'''. At the end click '''finish'''.

[[Image:20_Import_finish.png]]
 
 

''"Make"'' will start automatically (this can take several minutes). If every components work well you should have the following message in you console: 
'''''"All of PostgreSQL successfully made, Ready to install"''''' 
Furthermore you can see the Project '''"pgsql"''' on the left side of your workbench in the project explorer view.

[[Image:21_Make.png]]
 
 

== Adding make targets ==

=== Open the Make Target view ===

If this you can’t find this view, simply click on the Button in the left bottom to add the.

[[Image:35_Add_view.png]]
 
 

[[Image:22_Make_target.png]]
 
 

=== Add Target ===

In the ''Make-Target-view'' right click on the project name and select ''"New"''. In the new window ''"Create Make Target"'' type at ''"Target name:"'' '''"install”''' and click '''"OK'''".

[[Image:23_Make_install.png]]
 
 

If this was successful in the ''Make-Target-view'' at the end of the list a green dot appears with the label install. Double-click this, so you start the installation of PostgreSQL.

[[Image:24_Launch_install.png]]
 
 

If the installation was successful in the console you can read: 
'''''“PostgreSQL installation complete".'''''

[[Image:25_Installed.png]]
 
 

== Launch initdb in the terminal ==

Switch back to the terminal and change to the path ''"ubuntu/project/pgsql"''. Before starting initdb you should set some environment variables.

export PATH=$HOME/project/bin:$PATH
export PGDATA=DemoDir
initdb

[[Image:26_initdb.png]]
 
 

== Run Configurations in Eclipse ==

Switch back to Eclipse. 
 
Select the Project Explorer view. If you can’t see it, add it with the button in the left bottom. 
 
In the Project Explorer view right click on the Projektname (pgsql), and select "Run As » Run Configurations".

[[Image:27_Run_as.png]]
 
 
In this Window you can add a configuration:

[[Image:28_new_launch_configuration.png]]
 
 

Double-click ''"C/C++ Application"'' so a subfolder is created with the name ''"pgsql-Default"''. Now fill in the form on the right side. At ''"C/C++ Application:"'' the path: '''"src/backend/postgres"''' and project should be: '''"pgsql"'''.

[[Image:29_main.png]]
 
 

Switch to the tab ''"Arguments"''. At ''"Program arguments"'' type the arguments '''"-D DemoDir"''' and click the '''"Apply"''' button. Click '''"Run"''' to start the program.

[[Image:30_arguments.png]]
 
 

You should see following logs in your console: 
'''LOG: database system was shut down at (current date and time) UTC '''
'''LOG: database system is ready to accept connections '''
'''LOG: autovacuum launcher started 
'''
[[Image:31_DB_feedback.png]]
 
 

That means, the installation was successful and the server is running!

== Add include files ==

When looking at source code files, you will see compiler errors saying that include files were not found. To fix this, you need to add and include directory to properties. From the menus, choose Project/Properties/ C/C++ General / Paths and Symbols/Includes. Add an Include directory. Click on Workspace. Choose <project>/src/include and hit enter. Allow an index rebuild.

== Debugging PostgreSQL from Eclipse ==
# Make sure gdb is installed on your machine
# Right click on the project in the Project Explorer window and select 'Debug As -> C/C++ Application' [[Image:elipse_pg_debug1.png|Debug in Eclipse]]
# Select 'postgres' as the binary to execute [[Image:elipse_pg_debug2.png|Debug in Eclipse]]
# If you don't have PGDATA set in the environment Eclipse is running in, you can tune the command line arguments in Run/Debug Configurations so that Postgres can start properly [[Image:elipse_pg_debug3.png|Debug in Eclipse]]

Tip: You can do a 'make install' and make your arguments points to that installed directory.

== Debugging with child processes ==

When you start debugging process by using the project default, it can only debug the postmaster process. When clients connect to the database, the postmaster process forks child processes, each of which is actually postgres backend process responsible for the response to the client. You might want gdb to be able to access the forked processes too. Here's how to set that up:

# Debug Perspective => Run => Debug Configuration
# On left side of menu you can see "C/C++ Attach to Application"
# Right click on "C/C++ Attach to Application" and create new debug configuration,
# Set Project, Build Configuration as "Use Active" , C/C++ Application as "postgres" executable path,
# Start postmaster & one instant of postgresql client (for creating one new postgres),
# Click on Debug which will show current process list,
# Select forked "postgres" process you want to debug
# Put breakpoint in your function,

== Create and open Database ==

To see, that the installation is completely working you can do the followings to test the database. 
 
Switch to the terminal, and change to the path ''"ubuntu/project/pgsql"''. Execute the command:
psql -l
This will list you all available database. 
 
To create your own database simply type the following:
createdb DemoDB

If you execute the list-database again (psql -l) you can see your database, "DemoDB":

[[Image:32_List_DBs.png]]
 
 
You can login to your database with:
psql DemoDB

[[Image:33_enter_DB.png]]
 
 
Now you can create tables, relationships and much more. You can fully access your database. To exit your database type:
/q

== Terminate the program ==
The server is available until you click on the red rectangle in the console of Eclipse. 
If the stop was successful you will see the following log in the console: 
'''LOG: received smart shutdown request'''

[[Image:34_DB_terminate.png]]
[[Category:Howto]]

Working with Eclipse

2013-05-19T05:31:53Z

Gsmith: Add in a basic git workflow

== Developing with Eclipse ==

This page explains how to get quickly started if you want to use Eclipse to develop PostgreSQL.

Used software for this manual: 
''OS: Ubuntu 8.4 
Editor: Eclipse Helios 
Versioning: CVS''

The manual has been tested on an Ubuntu Live Version to make sure that no required Software is already installed. 
'''!!Important: An internet connection is required''' 
(Remark: If you haven’t installed Ubuntu yet, we recommend the following Link: http://www.ubuntu.com/desktop/get-ubuntu/download )

== Install version control software ==

The preferred way to get PostgreSQL source code is to use the git version control software. This tutorial will also cover using the older cvs program. You only need one program, git or cvs, not both.

=== Install git ===

Start Ubuntu Software Center: 
''Applications » Ubuntu Software Center''
 
 
To install git you should go to the magnifier on the right top side of the application, and enter '''"git"'''. From the results choose '''"fast, scalable, distributed revision control"''' and click on the '''Install''' button (if git is not already installed).
 
 

=== Install CVS ===

Start Ubuntu Software Center: 
''Applications » Ubuntu Software Center''
 
 
[[Image:03_Open_SW_Center.png]]
 
 
To install CVS you should go to the magnifier on the right top side of the application, and enter '''"cvs"'''. From the results choose '''"Concurrent Versions System"''' and click on the '''Install''' button (if CVS is not already installed).
 
 
[[Image:04_Install_CVS.png]]
 
 

== Open terminal ==

Through ''Applications » Accessories » Terminal'' you can open a new terminal: 
[[Image:01_Open_Terminal.png]]

== Install PostgreSQL source code ===

Now you can download the latest version of the source code, so you can edit it locally in your computer. Again you need to follow one of the cvs or git instruction sets, but not both.

=== Download with git ===

In the previous terminal, if you haven’t closed it (otherwise repeat step '''1. Open terminal''', and don’t close it until the end of the installation), you can make a copy with the following commands:

mkdir project
cd project
clone git://git.postgresql.org/git/postgresql.git
mv postgres pgsql

You can find more detailed information about setting up your own repository at [[Working with git]]. The cloned directory here is renamed to "pgsql" to match the cvs examples below. You can leave it as postgres instead, and just modify the rest of the examples below to use that name.

=== Download PostgreSQL source code - cvs ===

In our example the CVSROOT is '''''pgrepo''''' (you can choose also an other name), and is located under Home

The command mkdir creates a new folder ('''pgrepo''') and makes a new environment variable that links to this folder:
mkdir pgrepo
export CVSROOT=$HOME/pgrepo

The next command downloads with ''rsync'' the source code. This can take several minutes:
rsync --progress -avzCH --delete anoncvs.postgresql.org::pgsql-cvs $CVSROOT

[[Image:02_Download_repository.png]]
 
 

=== Copy with CVS ===

With '''CVS''' (Concurrent Version Control) you can make a versioned copy of the folder '''pgrepo'''.

=== Use CVS ===

In the previous terminal, if you haven’t closed it (otherwise repeat step '''1. Open terminal''', and don’t close it until the end of the installation), you can make a copy with the following commands:

mkdir project
cd project
cvs co pgsql

'''Warning:''' Execute these commands one layer above pgrepo, for instance in Home in our example!
 
 
[[Image:05_checkout_project.png]]
 
 
You can find more detailed information about setting up your own repository here [[Working with CVS]].

== Configure source code ==

=== Install components with Software-Center ===

You need the following Software, and you can download them through Software Center (''Applications » Ubuntu Software Center''):

''libreadline5-dev'' 
[[Image:06_Download_readline.png]]
 
 
''zlib1g-dev'' 
[[Image:07_Dowload_zlib.png]]
 
 
''bison (YACC)'' 
[[Image:08_Dowload_bison.png]]
 
 
''flex'' 
[[Image:09_Download_flex.png]]
 
 
You simply have to type the names of the software in the search area, and install them. This is the list of the required Software: 
1. ''libreadline5-dev'', 2. ''zlib1g-dev'', 3. ''bison (YACC)'', 4. ''flex''

=== Configure in the terminal ===

Switch back to the terminal and execute the following commands:

cd pgsql
./configure --prefix=$HOME/project --enable-depend --enable-cassert --enable-debug

[[Image:10_Configure.png]]
 
 
== Download Eclipse ==

=== Install JDK for Eclipse ===

To see the PostgreSQL source code in an organized manner we will install Eclipse on the computer. 
(Remark: You can use any other development tool also, but this manual explains the usage with Eclipse).

First of all you need the ''"OpenJDK Java 6 Runtime"'' for Eclipse, which you can also download in the Software-Center. 
Start Software Center Center (''Applications » Ubuntu Software Center'') or switch to the opened window and search for java. Choose ''"OpenJDK Java 6 Runtime”'' and click '''Install'''.
 
 
[[Image:11_Download_JDK.png]]
 
 

=== Download Eclipse ===

You can download Eclipse from the following link: http://www.eclipse.org/downloads/packages/eclipse-ide-cc-developers/heliosr

It’s important that you choose a '''Helios Version''', which supports''' C/C++'''. Open the Folder in your file browser, where the tar file is. In this example: ''ubuntu » Downloads''.

[[Image:12_Downloaded_eclispe.png]]
 
 

=== Extract and start ===

Right click on the Package and choose ''"Extract Here"'' from the menu.

[[Image:13_Extract_eclipse.png]]
 
 
Now enter the extracted Folder and double-click the file "eclipse".

[[Image:14_Launch_eclispe.png]]
 
 

=== Set up Workspace ===

You should set up a workspace, where the software stores your projects:

After starting the program you will be prompted to select a folder for your workspace. We will choose the recommended path, but you can change it if you browse for an other folder. If you have chosen your Folder click '''''"OK"'''''.

[[Image:15_Worspace.png]]
 
 

=== The program ===

Now you see the welcome-page of Eclipse, where you can choose tutorials, if you are not familiar with Eclipse. To continue the manual choose the arrow on the right to open your Workbench (the defaults layout of the program).

[[Image:16_Workbech.png]]
 
 

We will import the PostgreSQL code in this workbench.
 
 
[[Image:17_Layout.png]]
 
 

== Import ==

In this step we will import the project. Select in the left top of the program ''"File » Import "''

[[Image:18_Import.png]]
 
 

In the new window select ''"C/C++ » Existing Code as Makefile Project"'' and click the '''''"next"''''' button:

[[Image:19_Makefileproject.png]]
 
 
In the next window click browse, and choose the '''pgsql''' folder.
''"Language"'' is '''"C"''' and ''"Toolchain for Indexer Settings"'' select '''"Linux GCC"'''. At the end click '''finish'''.

[[Image:20_Import_finish.png]]
 
 

''"Make"'' will start automatically (this can take several minutes). If every components work well you should have the following message in you console: 
'''''"All of PostgreSQL successfully made, Ready to install"''''' 
Furthermore you can see the Project '''"pgsql"''' on the left side of your workbench in the project explorer view.

[[Image:21_Make.png]]
 
 

== Adding make targets ==

=== Open the Make Target view ===

If this you can’t find this view, simply click on the Button in the left bottom to add the.

[[Image:35_Add_view.png]]
 
 

[[Image:22_Make_target.png]]
 
 

=== Add Target ===

In the ''Make-Target-view'' right click on the project name and select ''"New"''. In the new window ''"Create Make Target"'' type at ''"Target name:"'' '''"install”''' and click '''"OK'''".

[[Image:23_Make_install.png]]
 
 

If this was successful in the ''Make-Target-view'' at the end of the list a green dot appears with the label install. Double-click this, so you start the installation of PostgreSQL.

[[Image:24_Launch_install.png]]
 
 

If the installation was successful in the console you can read: 
'''''“PostgreSQL installation complete".'''''

[[Image:25_Installed.png]]
 
 

== Launch initdb in the terminal ==

Switch back to the terminal and change to the path ''"ubuntu/project/pgsql"''. Before starting initdb you should set some environment variables.

export PATH=$HOME/project/bin:$PATH
export PGDATA=DemoDir
initdb

[[Image:26_initdb.png]]
 
 

== Run Configurations in Eclipse ==

Switch back to Eclipse. 
 
Select the Project Explorer view. If you can’t see it, add it with the button in the left bottom. 
 
In the Project Explorer view right click on the Projektname (pgsql), and select "Run As » Run Configurations".

[[Image:27_Run_as.png]]
 
 
In this Window you can add a configuration:

[[Image:28_new_launch_configuration.png]]
 
 

Double-click ''"C/C++ Application"'' so a subfolder is created with the name ''"pgsql-Default"''. Now fill in the form on the right side. At ''"C/C++ Application:"'' the path: '''"src/backend/postgres"''' and project should be: '''"pgsql"'''.

[[Image:29_main.png]]
 
 

Switch to the tab ''"Arguments"''. At ''"Program arguments"'' type the arguments '''"-D DemoDir"''' and click the '''"Apply"''' button. Click '''"Run"''' to start the program.

[[Image:30_arguments.png]]
 
 

You should see following logs in your console: 
'''LOG: database system was shut down at (current date and time) UTC '''
'''LOG: database system is ready to accept connections '''
'''LOG: autovacuum launcher started 
'''
[[Image:31_DB_feedback.png]]
 
 

That means, the installation was successful and the server is running!

== Debugging PostgreSQL from Eclipse ==
# Make sure gdb is installed on your machine
# Right click on the project in the Project Explorer window and select 'Debug As -> C/C++ Application' [[Image:elipse_pg_debug1.png|Debug in Eclipse]]
# Select 'postgres' as the binary to execute [[Image:elipse_pg_debug2.png|Debug in Eclipse]]
# If you don't have PGDATA set in the environment Eclipse is running in, you can tune the command line arguments in Run/Debug Configurations so that Postgres can start properly [[Image:elipse_pg_debug3.png|Debug in Eclipse]]

Tip: You can do a 'make install' and make your arguments points to that installed directory.

== Debugging with child processes ==

When you start debugging process by using the project default, it can only debug the postmaster process. When clients connect to the database, the postmaster process forks child processes, each of which is actually postgres backend process responsible for the response to the client. You might want gdb to be able to access the forked processes too. Here's how to set that up:

# Debug Perspective => Run => Debug Configuration
# On left side of menu you can see "C/C++ Attach to Application"
# Right click on "C/C++ Attach to Application" and create new debug configuration,
# Set Project, Build Configuration as "Use Active" , C/C++ Application as "postgres" executable path,
# Start postmaster & one instant of postgresql client (for creating one new postgres),
# Click on Debug which will show current process list,
# Select forked "postgres" process you want to debug
# Put breakpoint in your function,

== Create and open Database ==

To see, that the installation is completely working you can do the followings to test the database. 
 
Switch to the terminal, and change to the path ''"ubuntu/project/pgsql"''. Execute the command:
psql -l
This will list you all available database. 
 
To create your own database simply type the following:
createdb DemoDB

If you execute the list-database again (psql -l) you can see your database, "DemoDB":

[[Image:32_List_DBs.png]]
 
 
You can login to your database with:
psql DemoDB

[[Image:33_enter_DB.png]]
 
 
Now you can create tables, relationships and much more. You can fully access your database. To exit your database type:
/q

== Terminate the program ==
The server is available until you click on the red rectangle in the console of Eclipse. 
If the stop was successful you will see the following log in the console: 
'''LOG: received smart shutdown request'''

[[Image:34_DB_terminate.png]]
[[Category:Howto]]

User:Gsmith

2013-04-30T21:11:44Z

Gsmith: Update my profile

Greg Smith is a consultant for for [http://www.2ndquadrant.com 2ndQuadrant], regular hacker of new features starting with [http://www.postgresql.org/docs/8.3/static/release-8-3.html PostgreSQL 8.3], and the author of [http://www.packtpub.com/postgresql-90-high-performance/book PostgreSQL 9.0 High Performance]. His blog and current articles can be found at [http://highperfpostgres.com/ High Performance PostgreSQL].

Submitting a Patch

2013-04-30T20:51:22Z

Gsmith: Link to the PostgreSQL license and clarify it's not quite standard BSD or MIT.

== Initial patch design ==

If you have a trivial patch that serves an obvious need, you may be able to write the patch and submit it directly to the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] mailing list without having its design reviewed first. But in general, a non-trivial change should be discussed (potentially before the code is even written) on the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] list before being submitted as a patch.

For general coding style guidelines, see the [[Developer FAQ]] and the [http://developer.postgresql.org/pgdocs/postgres/source.html PostgreSQL Coding Conventions].

=== Design your interface first ===

Ask yourself these questions:

* Will the user interact with this new feature? if so, how?
* What is the syntax? Have ideas, and the ability to defend technical decisions you believe strongly in.
* What are the exact semantics/behaviors?
* Are there any backward compatibility issues?
* Get community buy-in at this level of detail before you start coding. But not necessarily consensus.
* Write an opening paragraph to your email to the -hackers list that answers these questions:
**This is the kind of problem I'm trying to solve
**This is what it is doing right now
**This is what it will do.

Mostly, get someone from the community involved in your ideas as early as possible so that you can even get half-baked ideas vetted early, rather than creating something in a vacuum. Similarly, it's easier to make progress and keep patches focused if you concentrate on the smallest portion of the idea you can execute perfectly. Resist the temptation to build a giant patch all at once, as those are much less likely to be reviewed usefully and therefore committed. You should take a look at how your patch will eventually be [[Reviewing a Patch|reviewed]], so you can make sure that review is likely to succeed.

=== Save us the trouble of reformatting your code ===

Please read [http://developer.postgresql.org/pgdocs/postgres/source.html PostgreSQL Coding Conventions]. Also, follow the style of the adjacent code! [http://archives.postgresql.org/pgsql-hackers/2009-08/msg01001.php Suggestions from Tom] clarify some of the trickier situations you might run into. [[Creating Clean Patches]] provides a walkthrough of how to do self-review and improvement of your patches to fix code formatting issues.

Naming things is important, and when in doubt, ask someone else to help you with names. We tend to use [http://en.wikipedia.org/wiki/CamelCase CamelCase] or underscores: thisStyleIsOkay or this_is_okay_too.

Generally, try to blend in with the surrounding code. Do not use #ifdef's to enable your changes. Comments are for clarification and to explain the ''why'' question, not for delineating your code from the surroundings nor to re-state what the code is doing.

Please remove any spurious whitespace. ''git diff --color'' makes them stand out like a sore thumb, in red.

== Patch submission==

Once you believe your patch is complete, please read through the complete patch that you will be sending to the mailing list. Consider what format the patch is in (should be either context or unified) and if it's easy to read. Ensure that the patch didn't mistakenly include unintended changes (such as whitespace changes, test code, #ifdef 0'd out blocks, etc). The patch should then be submitted via e-mail to the [http://archives.postgresql.org/pgsql-hackers/ pgsql-hackers] mailing list. At that time, or after you wait for initial feedback, you should also add it to the page for the next [https://commitfest.postgresql.org/ CommitFest].

Normally changes should be submitted as a single patch that includes every file touched. If the patch is large and can be logically separated into distinct and separately commit-able sections for easier review, with a clear order they get applied in described when applicable, that can be more straightforward for reviewers to work with for more complicated patches. Patches must be in a format which provides context (eg: [http://en.wikipedia.org/wiki/Diff#Context_format Context Diff]); 'normal' or 'plain' diff formats are not acceptable. Ideally, patch authors should choose the context (diff -c) format or unified (diff -u) format based on which format makes their patch easier to read. See [[Working_with_Git#Context_diffs_with_Git|Working with git]] and [[Creating Clean Patches]] for ways to do this well. [http://petereisentraut.blogspot.com/2009/09/how-to-submit-patch-by-email.html How to submit a patch by email] for more details about mailing in patches. If you're a new submitter, the suggestion there about using your judgment on patch formatting is not a recommended practice however--you should be using the standard context diff format.

Please note that PostgreSQL uses a BSD/MIT-style license for its code. By posting a patch to the public PostgreSQL mailing lists, you are giving the PostgreSQL Global Development Group the non-revocable right to distribute your patch under the [http://www.postgresql.org/about/licence/ PostgreSQL license].

Please include all of the following information with each patch submitted

* Project name.
* Uniquely identifiable file name, so we can tell difference between your v1 and v24.
* What the patch does in a short paragraph.
* Whether the patch is for discussion or for application (see WIP notes below)
* Which branch the patch is against (ordinarily this will be ''master''). For more on branches in PostgreSQL, see [[Working_with_Git#Using_Back_Branches|Using Back Branches]].
* Whether it compiles and tests successfully, so we know nothing obvious is broken.
* Whether it contains any platform-specific items and if so, has it been tested on other platforms.
* Confirm that the patch includes [[Regression test authoring|regression tests]] to check the new feature actually works as described.
* Include documentation on how to use the new feature, including examples. See the [http://www.postgresql.org/docs/current/static/docguide.html documentation documentation] for more information.
* Describe the effect your patch has on performance, if any.
* Try to include a few lines about why you chose to do things particular ways, rather than let your reviewer guess what was happening. This can be done as code comments, but it might also be an additional reviewers' guide, or additions to a README file in one of the code directories.
* If your patch addresses a [[Todo]] item, please give the name of the Todo item in your email. This is so that the reviewers will know that the item needs to be marked as done if your patch is applied.

It is helpful for early patches, ones not intended to be of commit quality, to be labeled clearly as such so that the appropriate style of review is done. The abbreviation WIP ("Work in Progress") is the standard shorthand to attach to patches intended for review not as a commit candidate, but for design feedback. Labeling your patch as a WIP on your e-mail subject line and on the matching description in the CF application will advise reviewers to focus more on the general approach, rather than on things like coding style that can normally be ignored in the early portion of a patch's lifecycle.

=== Reasons your patch might be returned ===

Submitting the patch is just the first step towards getting it committed. Very few patches are committed exactly as originally submitted, even those submitted by experienced professional developers. For any non-trivial patch you should plan for at least 3 versions before final acceptance.

There are a few common reasons patches are returned without getting the review consideration the submitter was hoping for:

* '''The fastest way to get your patch rejected is to make unrelated changes'''. Reformatting lines that haven't changed, changing unrelated comments you felt were poorly worded, touching code not necessary to your change, etc. Each patch should have the minimum set of changes required to work robustly. If you do not follow the code formatting suggestions above, expect your patch to be returned to you with the feedback of "follow the code conventions", quite likely without any other review.

* '''Consider how the patch would be reviewed'''. The things a reviewer will evaluate are listed in [[Reviewing a Patch]]. You should look at this list and consider if you will pass it before submission. We recommend that new code contributors first spend time doing review, so that you will already be familiar with this list and process. The review guidelines are used because they work; doing your own self-review before submitting your patch is recommended.

* '''Performance gain is claimed without test case'''. Performance patches are fun to write but hard to validate. If the patch is intended to improve performance, it's a good idea to include some reproducible tests to demonstrate the improvement. If a reviewer cannot duplicate your claimed performance improvement in a short period of time, it's very likely your patch will be returned. Do not expect that a reviewer is going to find your performance feature so interesting that they will build an entire test suite to prove it works. You should have done that as part of patch validation, and included the necessary framework for testing with the submission.

* '''Did not include documentation or regression tests'''. Any patch without these two items is automatically considered a WIP one--you might get review of your patch, but do not expect it to be committed. See [[Regression test authoring]] for more information about how to write these tests. Documenting the design at a high level in your submission e-mail is also recommended.

* '''Failed to address earlier criticisms of this design'''. Many patches try to do things that have been tried or considered before, where the earlier discussion found a problem with the obvious approach. Not saying how your new submission addresses those issues suggests you have the same problems. The [[Todo|todo list]] is a good source for finding past discussion of ideas for patches.

The objective of all of these suggested items is to ensure that the reviewer's time is not wasted. You spent time writing the code, but that does '''not''' mean you can demand time, energy and interest from a reviewer. Follow these guidelines and become familiar with the review process. Make it easy on yourself and others so that your patch is accepted quickly, easily and with good humor on all sides.

The presentation [http://www.pgcon.org/2011/schedule/events/303.en.html How To Get Your PostgreSQL Patch Accepted] provides some additional suggestions for how to submit a patch that will be considered more seriously.

=== Submission timing ===

To improve the odds of the right discussion of your patch or idea happening, pay attention to what the community work cycle is. If for example you send in a brand new idea in the beta phase, don't be surprised if no one is paying attention because they are focused on release work. Come back when the beta is done, please!

PostgreSQL development is organized with periodic [[CommitFest|CommitFests]], which are periods where new development halts in order to focus on patch review and committing. It's best if you can avoid sending in a new patch during the occasional weeks when there is an active CommitFest; you can check the schedule via the [https://commitfest.postgresql.org/ CommitFest application]. If your schedule doesn't allow waiting until an active CommitFest is over, you should explicitly label your submission as intended for the next CommitFest, not the current one, so that it's clear it's not intended to be part of the active review process.

== Patch review and commit ==

There's basically three different workflows a patch can follow after it's been submitted that lead to it being commited:

Workflow A:
# You post patch to pgsql-hackers
# A committer picks it up immediately and commits it.

Workflow B:
# You post a patch to pgsql-hackers
# You add the patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue
# A committer picks up the patch from the queue, and commits it

Workflow C:
# You post a patch to pgsql-hackers
# Bruce adds the patch to a list of unapplied patches
# At the beginning of the next commit fest, Alvaro (with the help from others, I hope) goes through the list, and adds the patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue
# A [[Committers|committer]] picks up the patch from the queue, and commits it

At any of these stages, your patch might instead be rejected for technical, style, or other reasons. These rejections will normally come with feedback on whether an improved version of that patch would be more acceptable. In those cases, you should consider updating your patch based on that feedback and re-submit.

== Followup on submissions ==

=== How do you get someone to respond to you? ===

You've sent an email to -hackers and no one has responded. What do you do?

* Make sure you've added your patch to the [http://commitfest.postgresql.org/action/commitfest_view/open open commitfest] queue.
* Start out by reviewing a patch or responding to email on the lists. Even if it is unrelated to what you're doing.
* Start with submitting a patch that is small and uncontroversial to help them understand you, and to get you familiar with the overall process.
* People are more willing to listen and work with someone who is already contributing.
* Also, in our community -- if no one objects, then there is implicit approval. Within reason!

Participating in community is a process, not a single event.

=== Submitting patch updates ===

When submitting a new version of a previously submitted patch, you should do a few additional things:

* Uniquely identify the new version, usually done via an incremented suffix on the name of the patch. First review would be ''mypatch-v1.patch'', second ''mypatch-v2.patch'', etc. Using the ".patch" extension allows some reviewers to more easily read it in their code editor.

* Make sure it's easy to find any earlier discussion of the patch. Don't expect that everyone will still be able to find previous submissions on their own. Either fully duplicate the information about the patch from your original messages, or provide a clear link to the earlier message via the [http://archives.postgresql.org/pgsql-hackers/ archives]. Note that you can link to your earlier post using the e-mail message ID of what you sent earlier, perhaps by checking your sent e-mail for it. That type of link is preferred because links to the archive by message number might sometimes get renumbered. See [[Template:MessageLink]] for more details.

[[Category:Community]]

PostgreSQL 9.3 Development Plan

2012-11-16T07:13:32Z

Gsmith: Fix wording

The timeline for the PostgreSQL 9.3 development cycle was initially planned at the [[PgCon 2012 Developer Meeting]]. The schedule is based on the [[PostgreSQL 9.2 Development Plan|9.2 timeline]] moved forward one year, with some optimizations based on developer feedback.

* June 1, 2012: branch off 9.2, committing into head/trunk/master for 9.3 can begin.
* June 15, 2012 - July 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=14 commit fest 1]. Primarily for contributors who are not involved in preparing the 9.2 release.
* September 15, 2012 - October 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=15 commit fest 2]
* November 15, 2012 - December 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=16 commit fest 3]
** Dec 8-15 Final feature planning week
* January 15, 2013 - February 15, 2013: [https://commitfest.postgresql.org/action/commitfest_view?id=17 commit fest 4]
** Final Triage: Feb 1–7
* Around February 20, 2013: 9.3 alpha release
* Possibly more alpha releases
* Beta releases
* Release candidates
* Release

[[Category:PostgreSQL 9.3]]

PostgreSQL 9.3 Development Plan

2012-11-16T07:12:57Z

Gsmith: Update dates for 9.3

The timeline for the PostgreSQL 9.3 development cycle was initially planned at the [[PgCon 2012 Developer Meeting]]. The schedule is based on the [[PostgreSQL 9.2 Development Plan|9.2 timeline]] moved forward one year, with some optimizations from on developer feedback.

* June 1, 2012: branch off 9.2, committing into head/trunk/master for 9.3 can begin.
* June 15, 2012 - July 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=14 commit fest 1]. Primarily for contributors who are not involved in preparing the 9.2 release.
* September 15, 2012 - October 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=15 commit fest 2]
* November 15, 2012 - December 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=16 commit fest 3]
** Dec 8-15 Final feature planning week
* January 15, 2013 - February 15, 2013: [https://commitfest.postgresql.org/action/commitfest_view?id=17 commit fest 4]
** Final Triage: Feb 1–7
* Around February 20, 2013: 9.3 alpha release
* Possibly more alpha releases
* Beta releases
* Release candidates
* Release

[[Category:PostgreSQL 9.3]]

Development information

2012-11-16T06:28:36Z

Gsmith: Update to show 9.3 is active

__NOTOC__
This area includes developer-targeted documentation regarding aspects of PostgreSQL development. Please visit the [http://www.postgresql.org/developer developer area] of the PostgreSQL website for more general information about the development of PostgreSQL. You can find most developers in [irc://irc.freenode.net/postgresql #postgresql on freenode]. A list of IRC nick names with their respective real world names can be found [[IRC2RWNames | here]].

==PostgreSQL 9.3 - Active Development==
* [[PostgreSQL 9.3 Development Plan]]
* [[PostgreSQL 9.3 Open Items]]

==Development Process==
* [[Todo|Todo list]]
* [[Todo:Contents|Unofficial Todo Detail]]
* [[Submitting a Patch]]
* [[Reviewing a Patch]]
* [[RRReviewers|Round-robin Patch Review]]
* [[Running a CommitFest]]
* [[Committing with Git]]

== Developer Resources ==
* [[Developer FAQ]]
* [[Regression test authoring]]
* [[HowToBetaTest|HOWTO Alpha and Beta Test PostgreSQL]]
* [[Working with Git]]
* [[Working with CVS]] (obsolete)
* [[Working with Eclipse]] (using CVS)
* [[Fixing shift/reduce conflicts in Bison]]
* [[PL Matrix|Procedural Language Matrix]]
* [http://www.postgresql.org/about/featurematrix Feature Matrix]
* [http://www.postgresql.org/developer/coding PostgreSQL Coding]
* [http://developer.postgresql.org/pgdocs/postgres/index.html Development docs] (updated every 5 minutes)
* [[Project Hosting]]
* [http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf Exposing PostgreSQL Internals with UDFs (2010)]

== Projects and Planning ==
* [http://commitfest.postgresql.org/action/commitfest_view/open Open CommitFest] - New patch submissions for 9.3
* [https://commitfest.postgresql.org/ CommitFest]
* [[PostgreSQL 8.4]]
* [[PgCon 2012 Developer Meeting]]
* [[PgCon 2011 Developer Meeting]]
* [[PgCon 2010 Developer Meeting]]
* [[PgCon 2009 Developer Meeting]]
* [[PgCon 2008 Developer Meeting]]
* [[Development projects]] - links to individual projects

==PostgreSQL Past Development==
* [[PostgreSQL 9.2 Open Items]]
* [[PostgreSQL 9.2 Development Plan]]
* [[PostgreSQL 9.1 Open Items]]
* [[PostgreSQL 9.1 Development Plan]]
* [[PostgreSQL 9.0 Open Items]]
* [[85AlphaFeatures|PostgreSQL 9.0 Alpha Release Feature List]]

[[Category:CommitFest]]

PostgreSQL 9.3 Development Plan

2012-11-16T06:27:01Z

Gsmith: Create 9.3 plan based on 9.2 plan plus developer meeting notes

The timeline for the PostgreSQL 9.3 development cycle was initially planned at the [[PgCon 2012 Developer Meeting]]. The schedule is based on the [[PostgreSQL 9.2 Development Plan|9.2 timeline]] moved forward one year, with some optimizations from on developer feedback.

* June 1, 2012: branch off 9.2, committing into head/trunk/master for 9.3 can begin.
* June 15, 2011 - July 15, 2011: [https://commitfest.postgresql.org/action/commitfest_view?id=14 commit fest 1]. Primarily for contributors who are not involved in preparing the 9.2 release.
* September 15, 2011 - October 15, 2011: [https://commitfest.postgresql.org/action/commitfest_view?id=15 commit fest 2]
* November 15, 2011 - December 15, 2011: [https://commitfest.postgresql.org/action/commitfest_view?id=16 commit fest 3]
** Dec 8-15 Final feature planning week
* January 15, 2012 - February 15, 2012: [https://commitfest.postgresql.org/action/commitfest_view?id=17 commit fest 4]
** Final Triage: Feb 1–7
* Around February 20, 2012: 9.2alpha release
* Followed by possibly more alpha releases
* Beta
* Release candidates
* Release

[[Category:PostgreSQL 9.3]]

PostgreSQL 9.3 Open Items

2012-11-16T06:19:13Z

Gsmith: Created 9.3 open items page

== Project Planning ==
See the [[PostgreSQL 9.3 Development Plan]].

== Blockers for 9.3 ==

== Not Blockers for 9.3 ==

== Meta-Issues ==

== Resolved Issues ==

== Long-term Issues ==

[[Category:PostgreSQL 9.3]]

Postgres Open 2012

2012-11-01T16:29:54Z

Gsmith: Add link to "Query Logging and Workload Analysis" talk

Slides for talks will be linked here!

== Monday September 17 ==

* [https://wiki.postgresql.org/wiki/File:Pg_security_20120917.odp PostgreSQL AuthN / AuthZ]
* [https://wiki.postgresql.org/images/8/86/PostgreSQL_on_ZFS.pdf PostgreSQL on ZFS]

== Tuesday September 18 ==

* [https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same]
* [[Media:PGXC_Scalability_PGOpen2012.pdf|Scaling out by distributing and replicating data in Postgres-XC]]
* PostgreSQL When It's Not Your Job

* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL]
* Choosing a logical replication system: Slony vs Bucardo
* Temporal Database Demo

* [http://www.setspace.com/~jmscott/pgopen-2012-ams-talk.pdf 12 Calm Years of PostgreSQL in Critical Messaging]
* [https://speakerdeck.com/u/jacobian/p/the-first-postsql-database PostgreSQL in the cloud: Theory and Practice]
* [http://www.hagander.net/talks/Backup%20strategies.pdf PostgreSQL Backup Strategies]

* This Is PostGIS
* [http://plv8-pgopen.herokuapp.com Embracing the Web with JSON and PLV8]
* How Akiban Implemented a New Database Compatible with the PostgreSQL Protocol

* [[Media:Ha_postgres.pdf|High Availability with PostgreSQL and Pacemaker]]
* [[Media:Logging_pgopen_withnotes.pdf|Logging: Not Just for Lumberjacks]]
* [http://stuff.coffeecode.net/2012/pgopen_fulltext/pgsql-fulltext-intro.html Full-text search - seek and ye shall find]

=== Lightning Talks ===

[http://keithf4.com/sites/keithf4.com/files/pgjobmon_pgopen2012.pdf I wonder if my function works? (pg_jobmon)]

== Wednesday September 19 ==

* [http://pgexperts.com/document.html?id=54 Super Jumbo Deluxe], plus [http://pgexperts.com/presentations.html more presentations here]
* [[Media:OO_approach.pdf|An object oriented approach to data driven software development]]
* TransLattice Elastic Database Architecture Deep Dive

* [http://www.pateldenish.com/2012/04/deploying-maximum-ha-architecture-with-postgresql.html Deploying maximum HA architecture with Postgres] , for pg_reorg [http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=p90xyourdatabase-13010796913746-phpapp02&stripped_title=p90-x-your-database p90x talk]
* [https://docs.google.com/presentation/pub?id=1xKixOq3NP0OjQyXpL46woSDel-LzXxtbjDVOJX-holw&start=false&loop=false Scaling Postgres with some help from Redis]
* Retail DDL
* [http://2ndquadrant.com/media/cms_page_media/59/BeyondQueryLogging.pdf Query Logging and Workload Analysis] (link is to updated version titled "Beyond Query Logging")
* [[Media:Postgres-xc-sharednothing-pgopen2012.pdf|A Shared-nothing cluster system: Postgres-XC]]
* [http://jkshah.blogspot.com/2012/09/pgopen-2012-dvdstore-benchmark-and.html DVDStore Benchmark and PostgreSQL]

* Performance Improvements in PostgreSQL 9.2
* [http://2ndquadrant.com/media/cms_page_media/59/CommitBatching.pdf A Batch of Commit Batching]
* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions]

* [https://speakerdeck.com/u/randommood/p/postgres-is-the-new-default Postgres is the new default – how we transitioned our platform at Engine Yard and why you should too]
* Leveraging PLV8 in Javascript-heavy Web Applications
* [http://keithf4.com/sites/keithf4.com/files/pgextractor_pgopen2012.pdf PG Extractor - A smarter pg_dump]

Postgres Open 2012

2012-10-09T18:40:47Z

Gsmith: Add Commit Batching talk slides

Slides for talks will be linked here!

== Monday September 17 ==

* [https://wiki.postgresql.org/wiki/File:Pg_security_20120917.odp PostgreSQL AuthN / AuthZ]

== Tuesday September 18 ==

* [https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same]
* Scaling out by distributing and replicating data in Postgres-XC
* PostgreSQL When It's Not Your Job

* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL]
* Choosing a logical replication system: Slony vs Bucardo
* Temporal Database Demo

* [http://www.setspace.com/~jmscott/pgopen-2012-ams-talk.pdf 12 Calm Years of PostgreSQL in Critical Messaging]
* [https://speakerdeck.com/u/jacobian/p/the-first-postsql-database PostgreSQL in the cloud: Theory and Practice]
* [http://www.hagander.net/talks/Backup%20strategies.pdf PostgreSQL Backup Strategies]

* This Is PostGIS
* [http://plv8-pgopen.herokuapp.com Embracing the Web with JSON and PLV8]
* How Akiban Implemented a New Database Compatible with the PostgreSQL Protocol

* [[Media:Ha_postgres.pdf|High Availability with PostgreSQL and Pacemaker]]
* [[Media:Logging_pgopen_withnotes.pdf|Logging: Not Just for Lumberjacks]]
* [http://stuff.coffeecode.net/2012/pgopen_fulltext/pgsql-fulltext-intro.html Full-text search - seek and ye shall find]

=== Lightning Talks ===

[http://keithf4.com/sites/keithf4.com/files/pgjobmon_pgopen2012.pdf I wonder if my function works? (pg_jobmon)]

== Wednesday September 19 ==

* [http://pgexperts.com/document.html?id=54 Super Jumbo Deluxe], plus [http://pgexperts.com/presentations.html more presentations here]
* [[Media:OO_approach.pdf|An object oriented approach to data driven software development]]
* TransLattice Elastic Database Architecture Deep Dive

* [http://www.pateldenish.com/2012/04/deploying-maximum-ha-architecture-with-postgresql.html Deploying maximum HA architecture with Postgres] , for pg_reorg [http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=p90xyourdatabase-13010796913746-phpapp02&stripped_title=p90-x-your-database p90x talk]
* [https://docs.google.com/presentation/pub?id=1xKixOq3NP0OjQyXpL46woSDel-LzXxtbjDVOJX-holw&start=false&loop=false Scaling Postgres with some help from Redis]
* Retail DDL

* Query Logging and Workload Analysis
* A Shared-nothing cluster system: Postgres-XC
* [http://jkshah.blogspot.com/2012/09/pgopen-2012-dvdstore-benchmark-and.html DVDStore Benchmark and PostgreSQL]

* Performance Improvements in PostgreSQL 9.2
* [[Media:CommitBatching.pdf|A Batch of Commit Batching]]
* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions]

* [https://speakerdeck.com/u/randommood/p/postgres-is-the-new-default Postgres is the new default – how we transitioned our platform at Engine Yard and why you should too]
* Leveraging PLV8 in Javascript-heavy Web Applications
* [http://keithf4.com/sites/keithf4.com/files/pgextractor_pgopen2012.pdf PG Extractor - A smarter pg_dump]

PgCon 2012 Developer Meeting

2012-05-16T16:16:32Z

Gsmith: Add what developers want section

A meeting of the most active PostgreSQL developers is being planned for Wednesday 16th May, 2012 near the University of Ottawa, prior to pgCon 2012. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org).

Please note that this year the attendee numbers have been cut to try to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.2 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.

This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.

== Time & Location ==

The meeting will be from 8:30AM to 5PM, and will be in the "Red Experience" room at:

Novotel Ottawa
33 Nicholas Street
Ottawa
Ontario
K1N 9M7

Food and drink will be provided throughout the day, including breakfast from 8AM.

[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]

== Attendees ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Oleg Bartunov
* Josh Berkus (Secretary)
* Jeff Davis
* Andrew Dunstan
* Dimitri Fontaine
* Stephen Frost
* Peter Geoghegan
* Kevin Grittner
* Robert Haas
* Magnus Hagander
* Shigeru Hanada
* Hitoshi Harada
* KaiGai Kohei
* Tom Lane
* Noah Misch
* Bruce Momjian
* Dave Page (Chair)
* Simon Riggs
* Teodor Sigaev
* Greg Smith

== Proposed Agenda Items ==

Please list proposed agenda items here:

* Agree CommitFest schedule for 9.3 (Strawman from Simon)
** CF1 June 15, 2012 - 1 month
** CF2 Sep 15, 2012 - 1 month
** CF3 Nov 15, 2012 - 1 month
** CF4 Jan 15, 2013 - 2 months
* Queuing [Dimitri, Kevin]
** Description: efficient and transactional queuing is a very common need for application using databases, and could help implementing some internal features
** Goals: get an agreement that core is the right place where to solve that problem, and what parts of it we want in core exactly
* Materialized views [Kevin]
** Description: Declarative materialized views are a frequently requested feature, but means many things to many people. It's not likely that an initial implementation will address everything. We need a base set of functionality on which to build.
** Goals: Reach consensus on what a minimum feature set for commit would be.
* Partitioning and Segment Exclusion [Dimitri]
** Description: to solve partitioning, we need to agree on a global approach
** Goals: agreeing on SE as a basis for better partitioning, having a "GO" on working on SE
* MERGE: Challenges and priorities [Peter G]
** Description: Implementing the MERGE statement for 9.3. It is envisaged specifically as an atomic "upsert" operation.
** Goals: To get buy-in on various aspects of the feature's development, and, ideally, to secure reviewer resources or other support. Because of the complexity of the feature, early interest from reviewers is preferable.
* Row-level Access Control and SELinux [KaiGai]
** Security label on user tables
** Dynamic expandable enum data types
** Enforcement of triggers by extension
* Enhancement of FDW at v9.3 [KaiGai]
** Writable foreign tables
** Stuffs to be pushed down (Join, Aggregate, Sort, ...)
** Inheritance of foreign/regular tables
** Constraint (PK/FK) & Trigger support.
* Type registry [Andrew]
** Provide for known OIDs for non-builtin types, and possibly for their IO functions too
** Would make it possible to write code in core or in extension X that handles a type defined in extension Y.
* Ending CommitFests in a timely fashion, especially the last one. Avoiding a crush of massive feature patches at the end of the cycle. Handling big patches that aren't quite ready yet. Getting more people to help with patch review. [Robert]
* What Developers Want [Josh]
** Description: a top-5 list of features and obstacles to developer adoption of PostgreSQL (with slides)
** Goal: to set priorities for some features aimed at application users
* In-Place Upgrades & Checksums [Greg Smith, Simon]
** Description: Revisit in-place upgrades of the page format, now that pg_upgrade is available and multiple checksum implementations needing it have been proposed.
** Goal: Nail down some incremental milestones for 9.3 development to aim at.
* Autonomous Transactions [Simon]
** Overview of idea, relationship to stored procedures
** Feedback, buy-in and/or alternatives
* Parallel Query [Bruce Momjian]
** Hope to get buy-in for what parallel operations we are hoping to add in upcoming releases
* Report from Clustering Meeting [Josh] (10 min)
** Description: to summarize the discussions of the cluster-hackers meeting from the previous day
** Goal: inter-team synchronization. Possibly, decisions requested on specific in-core features.
* Double Write Buffers [Simon]
** Is anyone committing to do that for 9.3?

* Goals, priorities, and resources for 9.3 [All]
** For roadmap and planning purposes, set expectations and coordinate work schedules for 9.3. Confirm who is doing what, identify interested reviewers at start, and check for gaps.

== Agenda ==

{| border="1" cellpadding="4" cellspacing="0"
!Time
!Item
!Presenter
|- style="font-style:italic;background-color:lightgray;"
|08:00
|Breakfast
|

|- style="font-style:italic;background-color:lightgray;"
|08:30 - 08:45
|Welcome and introductions
|Dave Page

|-
|08:45 - 09:15
|Autonomous transactions
|Simon Riggs

|-
|09:15 - 09:40
|[[Queuing]]
|Dimitri Fontaine/Kevin Grittner

|-
|09:40 - 09:50
|Report from the Clustering Meeting
|Josh Berkus

|-
|09:50 - 10:10
|Type registry
|Andrew Dunstan

|-
|10:10 - 10:30
|Access control and SELinux
|KaiGai Kohei

|- style="font-style:italic;background-color:lightgray;"
|10:30 - 10:45
|Coffee break
|

|-
|10:45 - 11:15
|Enhancement of FDWs in 9.3
|KaiGai Kohei

|-
|11:15 - 11:30
|What developers want
|Josh Berkus

|-
|11:30 - 12:00
|Parallel Query
|Bruce Momjian

|-
|12:00 - 12:30
|MERGE: Challenges and priorities
|Peter Geoghegan

|- style="font-style:italic;background-color:lightgray;"
|12:30 - 13:30
|Lunch
|

|-
|13:30 - 14:00
|Materialised views
|Kevin Grittner

|-
|14:00 - 14:20
|In place upgrades and checksums
|Simon Riggs/Greg Smith

|-
|14:20 - 14:45
|Partitioning and segment exclusion
|Dimitri Fontaine

|-
|14:45 - 15:00
|Commitfest Schedule
|All

|- style="font-style:italic;background-color:lightgray;"
|15:00 - 15:15
|Tea break
|

|-
|15:15 - 15:40
|Commitfest management
|Robert Haas

|-
|15:40 - 16:45
|Goals, priorities, and resources for 9.3
|All

|- style="font-style:italic;background-color:lightgray;"
|16:45 - 17:00
|Any other business/group photo
|Dave Page

|- style="font-style:italic;background-color:lightgray;"
|17:00
|Finish
|
|}

==Minutes==

== 2012 Developer Meeting Minutes ==

Started with introductions.

=== Autonomous Transactions ===

Simon brought this to get some feedback on the idea. Autonomous transactions (ATX) are a transaction inside a transaction ... a new top-level transaction. In Oracle, it's not just one new transaction, it's a whole new context which can submit multiple new transactions. There is no connection between parent and child transactions, which can result in new types of deadlocks.

Each new transaction context would allocate a new pg_exec from a pg_proc call. Implementation is straightforwards, just have to handle locking. Allows us to implement stored procedures in an interesting way. If we treat a stored procedure as an autonomous transaction, then this solves some problems. We can put COMMIT< ROLLBACK, other things in stored procedures.

Tom suggested that ATX don't need to conflict with parent transaction locks. Noah pointed out some issues with that. We'd need to have a switch for Stored Procedures in order to indicate they are autonomous, like using CREATE STORED PROCEDURE. We'd be using an additional client slot for each ATX, which could be a problem. Oracle's limit on ATX is 70 per connection, which seems like a lot. Maybe we should try to hold them all to a single session like it was a subtransaction. Not sure if we can do this, Simons will need to take a look at is.

ATX also need to eventually be able to run utility commands, like VACUUM and CREATE INDEX CONCURRENTLY.

=== Queueing ===

Ultimately the materialized views will need some kind of queueing. Once we have queueing in core, it could be generally useful. CLUSTER CONCURRENTLY would need it, or application queues will need queueing structure. We might want to have it exposed at the SQL level. You put things in the queue, and at commit, others can see it. LISTEN/NOTIFY is sort of a queue, but is only one item and vanishes if you're not listening.

Like a table, but access semantics are different. Would need logged/unlogged queues. Some discussion about how queues are different from tables. Haas wondered about whether what we need for interal queues are the same as what users need for user-visible queues.

Queue-tables also need different performance characteristics. We don't need queues so much as we need deferred action. We also need background processes which wake up and check the queue. Queues could be built on top of tables. Discussion about uses, designs for queues ensued.

We need a really clear design spec for how queues would work. There are specific performance improvements we want for queueing, but they're likely to be just improvements on table performance. The idea is to have a generalized API instead of reinventing a bunch of times.

Next steps is to collect use cases. [[Queueing|Kevin & Dimitri will collect use cases on a wiki page]], to design an API. Performance optimization needs to look at access pattern. Simon pointed out that this works similar to fact tables where you want to move stuff forward constantly. Users might not use queues as pure FIFO.

Unlinking segments works for deleting from the beginning of a table but indexes could be a problem. Block numbers could be a practical problem, we might need wraparound, or reset-to-zero.

=== Report on Clustering Meeting ===

See [[PgCon2012CanadaClusterSummit|minutes]].

=== Type Registry ===

WIP idea. Hstores aren't build in, so they get an arbitrary OID, which causes issues with writing generic code. Looking up they type name is expensive. It would be nice to have a registry for types where people writing extensions are allocated an OID. Andrew gave example of hacking Postgres to support upgrading from the optional JSON type in 9.1 to the built-in type in 9.2.

We need to expose the pg_upgrade stuff as well, set_binary_upgrade. Should we use something other than and OID? We need the OIDs for upgrade and for drivers. Driver identicalness isn't the same as pg_upgradability, so we might want two different switches for that. Maybe we should have a new OID if you change the storage of a type?

What's the criterion for allocating an OID? We'll need some kind of judgement. We'll also need to block off the OID reserved space into sections. People generally found this to be a good idea. Andrew will create a wiki page and follow-up. We could just do this for contrib, but that's not really a good idea.

We could have CREATE TYPE ... WITH OID = ###, for base types only. The folks who want it for ENUM etc. are just replication/clustering authors. There was discussion of other approaches to handling these problems. Users will create types with OIDs which conflict.

=== Access Control and SE-Linux ===

Several components: to add security around user tables. Second, to add additional conditions around user queries. Third, a condition around new tuples which are inserted. Fourth, we should have ENUMs to represent user-defined security labels. Did some performance testing on the last part, having labels as OIDs was much faster and closer to non-SE performance.

There's concurrency issues around seeing new labels -- we'd have a huge issue with inserting the labels into the system table. Creating a new label could be a downtime event; we can have a utility command, and we can require users to create a new label first manually. But what happens if the new label isn't there? Should error just like a constraint.

Is there a way to query SE-linux to get all of the security labels? That's hard, because it's four fields. The last field is an issue for prediction. There's a lot of value in having row-level security be completely type-agnosic; we just have a string and we don't care what's in it.

An SE Label consists of: user, row field, type field, and (something inaudible). That last part is a kind of bitmap. Do we actually need that part, though? What's multi-category security, will we support that? How many different labels would you have on a specific table?

The idea of row-level security is to force quals on people. Currently it's not transparent. The discussion on labels needs to continue elsewhere.

Also we need to address FK and PK implementation for security labels.

=== What Developers want ===

PostgreSQL is becoming the default for many web applications like Ruby and Django. But there are plenty of users complaints. They don't show up on the PostgreSQL mailing lists. The developer complaints are on stackoverflow, forums for virtual hosting companies, and application specific lists like ORM/framework layers.

Two categories of developer comments: blockers that cause to use another tool, and enhancers that would expand the market into new areas. Many of these are available features, but they seem to hard to use.

==== Blockers ====

1. Installation onto developer laptops (Windows / OS X)
* Re-installs problematic in Windows
* Reinstall of Redis is the competitor here, it is a closer to a true one-click installer.
* People use Redis because it's "easy to install", while PostgreSQL ran into one of multiple problems (reported on lists like pgsql-general)
* postgres.app is aiming at simplifying things for Mac developers, is in beta
* Kevin: also seen issues with Rails + Rake, lots of questions on Stackoverflow.
2. Complexity of configuring PostgreSQL, i.e. postgresql.conf
* Shared memory issues on the Mac
** Could use POSIX shared memory instead Sys V
* Need a configuration generator and hints for settings that are set incorrectly
**Example: need to increase size of the transaction log with pg_xlog having X GB of space. Math to determine settings like checkpoint_segements given a GB target is complicated.
3. Better analysis and troubleshooting
* Expose everything via SQL, i.e. autovacuum ; no parsing logs.
* EXPLAIN needs to be easier to understand, suggest what needs to be done when planner mistakes are made.
* Freeze a stable query plan needed for some apps.
4. Easier to understand replication
* External projects that try to help are often less maintained/robust/documented than core
* Same thing is true for pooling projects
5. Better pg_upgrade
* More trustworthy
* Handle version upgrades across large clusters
* Deliver on <5 minutes promise. Can take a long time for statistics ANALYZE. Needs to save/restore that instead.
6. MERGE UPSERT

=== Enabling features to broaden userbase ===

1. Finish JSON support
* Most popular new feature on news sites LWN etc. since 9.0 replication
* Some people want simple document storage like NoSQL, but with PostgreSQl reliability
* Needs indexing performance improvements
* More extract from JSON features
* Schemaless PostgreSQL is possible with JSON or hstore, but it's not obvious that's true.
2. Better extensions
* Packaging for popular extensions on popular
* Extensions should follow replication; move .so to standby? Lots of resistance to that idea.
* Better visibility of extensions, and extension aggregators like PGXN.
3. Client language queries
* Straight from, say, Python to a parse tree
* SQL Server/.Net does move in this direction for C#
* Competition here is the non-relational databases
4. Built-in sharding
* PL/Proxy: must find it, minimal docs, questions around support situation
* Target user base here doesn't like SQL or functions much either
* Base on writable FDW?
* Borrow ideas from notable sharded PostgreSQL deployments?

PgCon 2012 Developer Meeting

2012-05-10T03:29:25Z

Gsmith: Rearrange to put longer "Goals.." section at end

PgCon 2012 Developer Meeting

2012-05-04T21:50:25Z

Gsmith: Add description/goal to upgrade discussion proposal

PgCon2012CanadaClusterSummit

2012-04-27T18:24:34Z

Gsmith: Added Greg Smith

= 3rd Postgres Cluster Hackers Summit, pgCon 2012 =

== Time and Place ==

Tuesday, May 15th, 10am to 5pm

Conference room at the University of Ottawa, room TBA

== Agenda ==

Draft agenda follows. Please let me know of any contributions/changes to the agenda you have:

=== Project Reports: 5 minutes from each project ===

* Hot Standby/Binary Replication -- Simon
* pgPoolII -- Tatsuo
* PostgresXC -- Koichi
* Slony -- Steve or Chris
* Stado -- Jim

=== Technical Issues of common interest ===
* SSI in cluster/replication - Koichi, Kevin
* Parser export - Koichi, Tatsuo
* Managing consistent views of data - Koichi
* Fault detection and handling - Koichi
* Node addition/removal - Koichi
* Configuration and operation - Koichi
* Cursor in replication/multi master - Koichi
* Command Triggers - Dimitri
* Detecting database change caused by triggers and cascade operations - Tatsuo
* Bi-Directional Replication - Simon

The Cluster Summit will be from 10am to 5pm, with a break for lunch, which will be provided, sponsored by NTT.

== Attendees ==

# Koichi Suzuki, Postgres-XC
# Álvaro Herrera, Mammoth, Postgres-XC
# Jim Mlodgenski, Stado
# Mason Sharp, Stado, Postgres-XC
# Dimitri Fontaine, Slony, Londiste, Bucardo
# Christopher Browne, Slony
# Steve Singer, Slony
# Kevin Grittner, SSI
# Simon Riggs, Binary Replication
# Michael Paquier, Postgres-XC
# Ashutosh Bapat, Postgres-XC
# Tatsuo Ishii, pgPool
# Kaori Inaba, pgPool
# Daichi Matsusaka, pgPool
# Guillaume Lelarge
# David Wheeler
# Josh Berkus, moderator
# Tetsuo Sakata, Postgres-XC
# Ahsan Hadi, Postgres-XC
# Andres Freund, Bi-Directional Replication
# Greg Smith, Bi-Directional Replication

=== Joining the Meeting ===

If you will be able to attend, please email Josh ([mailto:josh@agliodbs.com josh@agliodbs.com]) with the following:

* Your Name
* Project(s) you work on
* If you will be giving a Project Report
* If you have additions to the agenda
* Special dietary needs for lunch, if any
* If you need travel assistance

Note that the availability of travel funding is not guaranteed; I can just agree to request it.

PgCon2012CanadaInCoreReplicationMeeting

2012-04-27T13:33:12Z

Gsmith: Going to this meeting

= PostgreSQL In-Core Replication meeting, pgCon 2012 =

== Time and Place ==

Wednesday, May 16th, 6pm to 10pm

Ottawa somewhere, room TBA

== Agenda ==

Draft agenda follows. Please let me know of any contributions/changes to the agenda you have:

# Discussion of Multi-Master Theory (Simon)
# Demonstration of prototypes (Andres)
# Performance comparisons
# My use case (Keaton)
# Social Media use case (Simon)

Broad and general discussion throughout. Notes and actions will be taken. Volunteers for tasks welcome.

The meeting will be from 6pm to 10pm, with various forms of food and possibly a drink or two, sponsored by 2ndQuadrant.

== Attendees (alphabetical) ==

* Keaton Adams
* Josh Berkus (prefer vegetarian)
* David Fetter
* Dimitri Fontaine
* Andres Freund
* Jim Mlodgenski
* Jim Nasby (plus guest)
* Simon Riggs
* Greg Smith
...

Meeting limit about 20-25 people

=== Joining the Meeting ===

If you will be able to attend, please email Simon ([mailto:simon@2ndQuadrant.com simon@2ndQuadrant.com]) with the following:

* Your Name
* What pizza topping you like

and please come armed with detailed information about your future replication requirements.

PgCon 2012 Developer Meeting

2012-04-25T19:09:39Z

Gsmith: Topic suggestions from Greg and Simon

Streaming Replication

2012-04-03T23:12:32Z

Gsmith: Reroute users away from this developer page

'''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.

This feature was added to PostgreSQL 9.0. The discussion below is a developer oriented one that contains some out of data information. Users of this feature should use the documentation for the feature or a setup tutorial instead:

* [[Binary Replication Tutorial]] provides an introduction to using this replication feature.
* [http://www.postgresql.org/docs/9.1/static/warm-standby.html 9.0 Replication Documentation]
* [http://www.postgresql.org/docs/9.0/static/warm-standby.html 9.1 Replication Documentation]

= 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.

= 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]]

Binary Replication Tutorial

2012-04-03T23:11:21Z

Gsmith: Link to full documentation

Welcome to the new PostgreSQL 9 replication and standby databases guide. This new set of features implements possibly the longest awaited functionality in PostgreSQL's history. As a result, a lot of people are going to be trying to deploy standby databases for the first time, and find the process rather unintuitive. This guide is here to help.

'''Work in progress: only 40% complete'''

= 5 Minutes to Simple Replication =

This is the easiest way to set up replication between a master and standby. It requires shutting down the master; other methods are detailed later in this guide.

What we're going to do is shut down the master and copy the files we need over to the slave server, creating a cloned copy of the master. Because the master is shut down, we don't have to worry about changes being made to it.

== Prerequisites ==

You must have the right setup to make this work:

* 2 servers with similar operating systems (e.g both Linux 64-bit).
* The same release of PostgreSQL 9.0 installed on both servers.
* PostgreSQL superuser shell access on both servers.
* Knowledge of how to start, stop and reload Postgres.
* PostgreSQL 9.0 running on Server1.
* A database created and loaded on Server1.
* A postgres user or root user who has network

See the full documentation for more information:

* [http://www.postgresql.org/docs/9.1/static/warm-standby.html 9.0 Replication Documentation]
* [http://www.postgresql.org/docs/9.0/static/warm-standby.html 9.1 Replication Documentation]

== Binary Replication in 6 Steps ==

This 6-step guide, and all of the examples in this tutorial, assume that you have a master server at 192.168.0.1 and a standby server at 192.168.0.2 and that your database and its configuration files are installed at /var/lib/postgresql/data. Replace those with whatever your actual server addresses and directories are.

1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3

2. Edit pg_hba.conf on the master in order to let the standby connect.

host replication all 192.168.0.2/32 trust

3. Edit recovery.conf and postgresql.conf on the standby to start up replication and hot standby. First, in postgresql.conf, change this line:

hot_standby = on

Then create a file in the standby's '''data directory''' (which is often the same directory as postgresql.conf and pg_hba.conf, except on some Linux distributions such as Debian and Ubuntu), called recovery.conf, with the following lines:

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.1'

4. Shutdown the master and copy the files. You want to copy most but not all files between the two servers, excluding the configuration files and the pg_xlog directory. An example rsync script would be:

rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/

5. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connect to the primary server, that's OK.)

6. Start the master.

'''Is taking down the master for long enough to copy the files too long?''' Then you need the 10-minute version.

== Starting Replication with only a Quick Master Restart ==

What we're going to do this time is similar to what we did before, cloning the database by copying the files from the master to the slave server. However, because the database is only going to be shut down for a short period of time, long enough to activate the changes in the configuration file, after we've copied the data files we will need to copy additional files so that the slave will be an up-to-date copy of the master.

So, we will tell the master we're running a backup, copy the data files (not quite the same set of files as before), tell the master the backup is complete, then copy the WAL files in the pg_xlog directory so that when the slave comes up it can make all the changes that were committed to the master database after the backup was started.

First, start with the same prerequisites as above.

1. Set the postgresql.conf variables the same in step (1) as above.

2. Don't close the file yet. You'll need to set two other variables. The first is checkpoint_segments, which increases the size of your write-ahead-log (WAL). Unless you've already done so, you're going to need to increase this, which is usually a good idea for performance anyway. You want it to be big enough to not get used up in 15 or 20 minutes. If you don't have a clear idea of that, here's some reasonable values, based on how busy and how large your database is. Also, these logs will take up disk space, so make sure that you have enough available - space requirements are below.

checkpoint_segments = 8
wal_keep_segments = 8
# light load 500MB

checkpoint_segments = 16
wal_keep_segments = 32
# moderately busy 1.5GB

checkpoint_segments = 64
wal_keep_segments = 128
# busy server 5GB

You don't ''have'' to increase checkpoint_segments in order to increase wal_keep_segments, but it's generally a good idea. Now save the file.

3. Edit pg_hba.conf as in (2) in the "Six Steps" above.

4. Now you need to restart the master. Given the interruption in service, you should probably plan this ahead.

5. Edit postgresql.conf and recovery.conf on the standby as in (3) above.

6. Now, we're going to need to copy the files from the master and start the standby. Unlike in the 6-step version, this needs to be done quickly or the standby will fail to sync and you'll need to try again. First step, you need to tell the master you're starting a backup (see below for a more detailed explanation of this). Log in to psql as the database superuser.

psql -U postgres
# select pg_start_backup('clone',true);

Note that the string you use as a backup label doesn't matter; use any string you want.

7. Now, quickly copy all the database files. This rsync is slightly different from the 6-step version:

rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \
data/* 192.168.0.2:/var/lib/postgresql/data/

8. As soon as that's done you need to stop the backup on the master:

# select pg_stop_backup();

9. As soon as that completes, you need to quickly copy the WAL files from the master to the standby.

rsync -av data/pg_xlog 192.168.0.2:/var/lib/postgresql/data/

10. Now, start the standby.

If you've done this quickly enough, then the standby should catch up with the master and you should be replicating. If not, you'll get this message:

... which means you need to try again, possibly with checkpoint_segments and wal_keep_segments higher. If that still doesn't work, you're going to need to use the even more complex archiving method described below.

Now, the rest of the guide will explain how to deal with more complex situations, such as archive logs, handling security, and maintaining availability, failover and standby promotion.

= Introduction to Binary Replication =

Binary replication is also called "Hot Standby" and "Streaming Replication" which are two separate, but complimentary, features of PostgreSQL 9.0 and later. Here's some general information about how they work and what they are for.

== What Can You Do With Binary Replication? ==

* Have a simple and complete replica of your production database, preventing all but a couple seconds of data loss even under catastrophic circumstances.
* Load-balance between your read/write master server and multiple read-only servers.
* Run reporting or other long-running queries on a replica server, taking them off your main transaction-processing server.
* Replicate all DDL, including table and index changes, and even creating new databases.
* Replicate a hosted multi-tenant database, making no specific requirements for primary keys or database changes of your users.

== What Can't You Do With Binary Replication? ==

* Replicate a specific table, schema, or database. Binary replication is the entire Postgres instance (or "cluster").
* Multi-master replication. Multi-master binary replication is probably technically impossible.
* Replicate between different versions of PostgreSQL, or between different platforms.
* Set up replication without administration rights on the server. Sorry, working on it.
* Replicate data synchronously, guaranteeing zero data loss. But ... this is coming in PostgreSQL 9.1.

For the reasons above, we expect that Slony-I, Londiste, Bucardo, pgPool2 and other systems will continue to be used.

== Transaction Logs and Log Shipping ==

Users who are already familiar with the PostgreSQL transaction log and warm standby can skip this section.

An individual "instance", "server", or (confusingly) "cluster" of PostgreSQL (hereafter Server) consists of a single postmaster server process connected to a single initialized PostgreSQL data directory (PGDATA), which in turn contains several databases. Each running Server has a transaction log, located in the PGDATA/pg_xlog directory. This transaction log consists of binary snapshots of data, written to record synchronously each change to all databases' data, in case of unexpected shutdown of the database server (such as in a power failure). This ensures that data is not corrupted and no completed transaction is lost.

You can also use this log to allow a copy of the original database to replicate changes made to a master database. This was first implemented with the PITR feature in PostgreSQL 8.0, and is known as "log shipping". Log shipping is required for most forms of binary replication.

This log consists of 16MB segments full of new data pages (8K segments) of the database, and not of SQL statements. For this reason there is no before and after auditing possible via this log, as you cannot know exactly what has changed. Also, the log is treated as a buffer, being deleted as it is no longer needed for crash recovery. More importantly, the data page format of the log means that log segments can only be applied to a database which is binary-identical to the database which created the log.

== PITR, Warm Standby, Hot Standby, and Streaming Replication ==

For the rest of this tutorial, we will refer to the active read-write instance of the Server which generates transaction logs as the "Master" and the passive, read-only or offline instance (or instances) of the Server which receives transaction logs as the "Standby" (or "Standbys"). The term Master/Standby is equivalent to other terminology which may be used in the database industry, such as Master/Slave, Primary/Secondary or Primary/Replica.

=== PITR ===

In Point-In-Time Recovery (PITR), transaction logs are copied and saved to storage until needed. Then, when needed, the Standby server can be "brought up" (made active) and transaction logs applied, either stopping when they run out or at a prior point indicated by the administrator. PITR has been available since PostgreSQL version 8.0, and as such will not be documented here.

PITR is primarily used for database forensics and recovery. It is also useful when you need to back up a very large database, as it effectively supports incremental backups, which pg_dump does not.

=== Warm Standby ===

In Warm Standby, transaction logs are copied from the Master and applied to the Standby immediately after they are received, or at a short delay. The Standby is offline (in "recovery mode") and not available for any query workload. This allows the Standby to be brought up to full operation very quickly. Warm Standby has been available since version 8.3, and will not be fully documented here.

Warm Standby requires Log Shipping. It is primary used for database failover.

=== Hot Standby ===

Hot Standby is identical to Warm Standby, except that the Standby is available to run read-only queries. This offers all of the advantages of Warm Standby, plus the ability to distribute some business workload to the Standby server(s). Hot Standby by itself requires Log Shipping.

Hot Standby is used both for database failover, and can also be used for load-balancing. In contrast to Streaming Replication, it places no load on the master (except for disk space requirements) and is thus theoretically infinitely scalable. A WAL archive could be distributed to dozens or hundreds of servers via network storage. The WAL files could also easily be copied over a poor quality network connection, or by SFTP.

However, since Hot Standby replicates by shipping 16MB logs, it is at best minutes behind and sometimes more than that. This can be problematic both from a failover and a load-balancing perspective.

=== Streaming Replication ===

Streaming Replication improves either Warm Standby or Hot Standby by opening a network connection between the Standby and the Master database, instead of copying 16MB log files. This allows data changes to be copied over the network almost immediately on completion on the Master.

In Streaming Replication, the master and the standby have special processes called the walsender and walreceiver which transmit modified data pages over a network port. This requires one fairly busy connection per standby, imposing an incremental load on the master for each additional standby. Still, the load is quite low and a single master should be able to support multiple standbys easily.

Streaming replication does not require log shipping in normal operation. It may, however, require log shipping to start replication, and can utilize log shipping in order to catch up standbys which fall behind.

= How to Replicate =

== Cloning a Live Database ==

If your workload doesn't allow you to take the master down (and whose does?), things get a bit more complicated. You need to somehow take a "coherent snapshot" of the master, so that you don't have an inconsistent or corrupt database on the standby. Now, in some cases this can be done via filesystem snapshotting tools or similar tricks, but as that approach is tricky and platform-dependant, we're not going to cover it here.

Instead, we're going to cover the built-in method, which involves keeping a log of all changes applied to the database which happen during the copying process. The steps are essentially the same, regardless of whether you're planning to use just hot standby, streaming replication, or both. There are two parts:

* Cloning the database files
* Copying the archive logs

Unintuitive as it is, the latter needs to be set up first, so we're going to start with that.

== Setting Up Archiving On The Master ==

Archiving is the process of making an extra copy of each WAL file as it is completed. These log files then need to somehow be accessed by the standby. There are three basic ways to handle this, and you should decide in advance what method you're going to use:

# Manually
# Automatic file copying from master to standby using rsync or simiar
# Writing them to a common shared network file location

The first method is only appropriate if you're archiving logs only to jump-start streaming replication, and you have a fairly low-traffic database or the ability to stop all writes. The third method is probably the easiest to manage if you have an appropriate network share; it can even be used to support multiple standbys with some extra thought and scripting. All of these methods will be explained below.

This needs to be turned on on the master, which if it's never been done before may require a restart (sorry, working on it), and will certainly require a reload. You'll need to set the following parameters:

wal_level = hot_standby
archive_mode = on
archive_command = 'some command'

What archive command you use depends on which archiving approach you are taking, of course. Here are three examples of commands you might use. Note that you will need to create the "archive" directories.

# Manual: cp -f %p /var/lib/postgresql/data/archive/%f </dev/null
# Automatic Copy: rsync -a %p 192.168.0.2:/var/lib/pgsql/data/archive/%f
# Network Share: cp -f %p /shares/walarchive/archive/%f </dev/null

In these commands, %p is replace by postgres at invocation time with the full path and name of the WAL file, and %f with the name of the file alone. There are more escapes and parameters dealing with WAL archiving which will be detailed later in the tutorial. Note that, in real production, you are unlikely to want to use any commands as simple as the above. In general, you will want to have archive_command call an executable script which traps errors and can be disabled. Examples of such scripts are available in this tutorial.

Now, if archive_mode was originally "off" or if you had to change wal_level, you're going to need to restart the master (sorry, this will be fixed in a later version). If you just needed to change the archive_command, however, only a reload is required.

Once you've restarted or reloaded, check the master's logs to make sure archiving is working. If it's failing, the master will complain extensively. You might also check that archive log files are being created; run the command "SELECT pg_switch_xlog();" as the superuser to force a new log to be written.

== Setting Up Archiving on the Standby ==

The standby needs to be configured to consume logs. This is simpler than the master's setup, and doesn't really change no matter what archive copying strategy you're using.

== Recovery.conf ==

On the standby, replication configuration is controlled through a file called, for historical reasons, recovery.conf. If this file is present in PostgreSQL's data directory when PostgreSQL is started, that server will assume it is a standby and attempt to obey it. Generally, there is an example file installed with the other PostgreSQL shared docs. However, that example file covers all of the various replication options at once, so it's often simpler to write your own file, from scratch. Any change to recovery.conf requires a restart of the standby.

In recovery.conf, you need to add a command to copy the archived WAL files to the standby's on pg_xlog directory. This is the mirror image of the archive_command on the master. Generally, a simple cp command is sufficient:

restore_command = 'cp -f /var/lib/postgresql/data/archive/%f %p </dev/null'
restore_command = 'cp -f /shares/walarchive/%f %p </dev/null'

Again, you might want to use a simple shell script which traps error messages, and, importantly, deletes archive files which are no longer needed. If you will be doing only hot standby and not using streaming replication, you probably want to compile the pg_standby binary provided in PostgreSQL's additional modules or "contrib", and use it instead:

restore_command = 'pg_standby /shares/walarchive/%f %p %r'

More detail on pg_standby is in its documentation.

== Cloning a Snapshot of the Master ==

Once you have archiving working, you're ready to clone the master database. At this point, it's a simple process:

# As superuser, issue the command "SELECT pg_start_backup('backup');" on the master.
# Copy all of the database files to the standby.
# Start the standby database.
# Issue the command "SELECT pg_stop_backup();" on the master.

Of course, each of those steps deserves a little more elaboration. pg_start_backup and pg_stop_backup are special commands you issue on the master in order to create, hold open, and close, a "snapshot" which is how we make sure your copy of the database is not inconsistent. They also write special files to the archive log which tell the standby when it has a complete snapshot.

If you are using the "manual" method of synching the archive logs, immediately after step 4 you need to do one last rsync or copy of the archive logs to the standby.

When you're done with the cloning, you should see output similar to the below:

This means that you're up and replicating, and should now be able to run queries on the standby.

== Failing Over To The Standby ==

Of course, one of the major reasons to have a standby is in case something (planned or unplanned) causes the master server to shut down. Then you want to "fail over", or stop replication and change the standby to a full read-write master.

The recommended method is the same regardless of the type of replication or standby: via "trigger file". First, you need to set a configuration option in recovery.conf on the standby:

trigger_file = '/var/lib/postgresql/data/failover'

Then, when it's time to fail over, you just create an empty file with that name, such as by using the "touch" command. The standby will notice the file, attempt to apply any remaining WAL records or files it has received, and then switch to read-write or "master" mode. When this happens, you will see a message like this in the Postgres log:

PostgreSQL will also rename the recovery.conf file to recovery.done in order to prevent having the new master fail on restart. For this reason, the recovery.conf file should be owned by the same user which the server runs as (usually "postgres").

The alternative to using a trigger file is to failover manually, by deleting or renaming the recovery.conf file and restarting the standby. This method is inferior because it requires a restart which would interrupt any read-only connections to the standby currently in use.

In a high-availability system, the above activity should be managed automatically in order to avoid downtime. PostgreSQL itself supplies no tools to do this, but numerous third-party utilities such as "Linux heartbeat" are compatible with PostgreSQL replication.

It's important to prevent the original master from restarting after failover, lest you end up with a "split brain" problem and data loss. There is a substantial body of literature on this, and third-party tools, so we won't discuss them here at this time.

== Load Balancing ==

== Managing Archive Logs ==

== Tuning and Configuration of Binary Replication ==

== Monitoring Replication ==

[[Category:Replication]]

Homebrew

2012-04-03T22:35:46Z

Gsmith: Link to Lion issue fixing docs

[http://mxcl.github.com/homebrew/ Homebrew] is a package manager for Mac OS X that builds software from its source code. It includes a version of PostgreSQL packaged by what it refers to as a formula. This type of installation might be preferred by people who are comfortable using the command line to install programs, such as software developers.

Notes on Homebrew:

* [http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x Install PostgreSQL 9 on OS X] (2010-11-25) - Easy install using Homebrew package manager.
* [http://nextmarvel.net/blog/2011/09/brew-install-postgresql-on-os-x-lion/ Brew Install Postgresql on OS X Lion] - Resolving conflicts with the built-in PostgreSQL libraries shipping with OS X 10.7 'Lion'
* [http://archives.postgresql.org/pgsql-hackers/2012-04/msg00010.php Discussion comparing Homebrew and EDB OS X installers]

Homebrew

2012-04-03T22:18:45Z

Gsmith: Typo fix

[http://mxcl.github.com/homebrew/ Homebrew] is a package manager for Mac OS X that builds software from its source code. It includes a version of PostgreSQL packaged by what it refers to as a formula. This type of installation might be preferred by people who are comfortable using the command line to install programs, such as software developers.

Notes on Homebrew:

* [http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x Install PostgreSQL 9 on OS X] (2010-11-25) - Easy install using Homebrew package manager.
* [http://archives.postgresql.org/pgsql-hackers/2012-04/msg00010.php Discussion comparing Homebrew and EDB OS X installers]

Homebrew

2012-04-03T22:01:54Z

Gsmith: New page on Homebrew package manager

[http://mxcl.github.com/homebrew/ Homebrew] is a package manager for Mac OS X that builds software from its source code. It includes a version of PostgreSQL packaged by what it refers to as a recipe. This type of installation might be preferred by people who are comfortable using the command line to install programs, such as software developers.

Notes on Homebrew:

* [http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x Install PostgreSQL 9 on OS X] (2010-11-25) - Easy install using Homebrew package manager.
* [http://archives.postgresql.org/pgsql-hackers/2012-04/msg00010.php Discussion comparing Homebrew and EDB OS X installers]

PgCon2012CanadaClusterSummit

2012-02-29T06:04:14Z

Gsmith: Fixed bullet formatting, clickable e-mail address

= 3rd Postgres Cluster Hackers Summit, pgCon 2012 ==

== Time and Place ==

Tuesday, May 15th, 10am to 5pm

Conference room at the University of Ottawa, room TBA

== Agenda ==

Draft agenda follows. Please let me know of any contributions/changes to the agenda you have:

=== Project Reports: 5 minutes from each project ===
* Hot Standby/Binary Replication -- Peter or Simon
* pgPoolII -- Tatsuo
* PostgresXC -- Koichi
* Slony -- Steve or Jan
* Your Project Here

=== Technical Issues of common interest ===
* SSI in cluster/replication
* Parser export
* Managing consistent views of data
* Fault detection and handling
* Node addition/removal
* Configuration and operation
* Cursor in replication/multi master
* Your Issue Here

The Cluster Summit will be from 10am to 5pm, with a break for lunch, which will be provided, sponsored by NTT.

If you will be able to attend, please email Josh ([mailto:josh@agliodbs.com josh@agliodbs.com]) with the following:

* Your Name
* Project(s) you work on
* If you will be giving a Project Report
* If you have additions to the agenda
* Special dietary needs for lunch, if any
* If you need travel assistance

Note that the availability of travel funding is not guaranteed; I can just agree to request it.

PgCon 2012 Developer Meeting

2012-02-20T20:48:22Z

Gsmith: Add myself to the developer's meeting

Detailed installation guides

2012-01-10T22:18:32Z

Gsmith: Link to official Debian page

== Any UNIX-Like Platform ==

* [[Manual Setup at the Command Line]]

== General Linux ==

* Gentoo: [http://www.gentoo.org/doc/en/postgres-howto.xml PostgreSQL Guide] (2007-04-25)
* Linux: [http://www.designmagick.com/article/2/Starting-Out/Installing-PostgreSQL Installing Apache, PostgreSQL, and PHP on Linux] by Chris Smith (2006-02-07)
* [[PostgreSQL and SELinux]]
* [http://wiki.postgresql.org/wiki/PostgreSQL_Tutorials Installation of Postgres Plus Standard Server v8.3 Tutorial for Linux]

=== Debian/Ubuntu Linux ===

Debian based installs have a somewhat unique design that allows multiple database clusters to be managed independently. This allows running both multiple database instances as well as multiple versions more easily than other packaging schemes.

*[http://wiki.debian.org/PostgreSql Debian PostgreSQL Wiki]. As pointed out there, really detailed documentation is available on the server at ''/usr/share/doc/postgresql-common/README.Debian.gz'' ; you should copy this file to another directory and run ''gunzip README.Debian.gz'' if you'd like a readable version.
*[http://www.stuartellis.eu/articles/postgresql-setup/ Using PostgreSQL on Debian and Ubuntu] (2009)
*[http://russbrooks.com/2009/5/25/install-ubuntu-9-04-server-edition-rails-php-passenger-postgresql-and-mysql Install Ubuntu 9.04 Server Edition, Rails, PHP, Passenger, PostgreSQL, and MySQL] - the "database" section here is a concise guide to the standard PostgreSQL installation work most systems need to password-protected remote access, and it also covers installing the server side tools for pgAdmin (2009-05-25)
*[http://hocuspokus.net/2008/05/install-postgresql-on-ubuntu-804 Install PostgreSQL on Ubuntu 8.04] (2008-05-14)
*[http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html Howto setup Database Server With postgresql and pgadmin3] (has a nice intro to general postgresql.conf/pg_hba.conf info applicable to all distributions)
*[http://www.debianhelp.co.uk/postgresql.htm PostgreSQL Database Server Configuration in Debian]
*[http://www.elsn.org/main/postgresql/ Using the PostgreSQL Database Server]
*[http://www.progsoc.org/~wildfire/notes/postgresql-cluster.html postgresql clustering and Debian]
*[http://debian-user.blogspot.com/2008/02/upgrading-to-postgresql-83-in-debian.html Upgrading to PostgreSQL 8.3 in Debian from 7.4]

=== RedHat/Fedora/CentOS ===
* [[YUM Installation]]
* [[PostgreSQL on RedHat Linux]]

== FreeBSD ==
* FreeBSD: [http://www.b1c1l1.com/blog/2011/01/01/upgrading-postgresql-on-freebsd/ Upgrading PostgreSQL on FreeBSD] (2011-01-01)
* FreeBSD: [http://jasonk2600.wordpress.com/2010/01/11/installing-postgresql-on-freebsd/ Installing PostgreSQL on FreeBSD] (2010-01-11)
* FreeBSD: [http://www.freebsddiary.org/postgresql.php PostgreSQL Installation on FreeBSD] (2002-08-24)
* FreeBSD: [http://www.freebsddiary.org/postgresql-perl.php PostgreSQL and Perl on FreeBSD] (2000-12-26)

== MacOS ==

* MacOS X: [http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x Install PostgreSQL 9 on OS X] (2010-11-25) - Easy install using Homebrew package manager.
* MacOS X: [http://facility9.com/2010/02/15/installing-postgresql-on-mac-os-x Installing PostgreSQL on Mac OS X (an alternative guide using EnterpriseDB) by Jeremiah Peschka] (2010-02-15)
* MacOS X: [http://www.macdevcenter.com/pub/a/mac/2002/06/07/postgresql.html PostgreSQL on MacOS X (another alternative guide) by Michael Brewer] (2002-07-06)
* MacOS X: [http://developer.apple.com/internet/opensource/postgres.html PostgreSQL on Mac OS X]

== Solaris ==

* Solaris 10: [[:Image:pg_8.2.1_deployment_v1.0.pdf|PostgreSQL 8.2.1 on Solaris 10 – Deployment Guidelines]] by Chris Drawater (2007-01-15)
* Solaris 10: [http://www.holindis.co.uk/ Related documents for J2EE, Tomcat, and Oracle migrations] by Chris Drawater (2007-01-15)
* [[:Image:Pg81onsolaris10.pdf|PostgreSQL 8.1 on Solaris 10 - Deployment Guidelines]] (160KB - PDF)

== Windows ==

[[Running & Installing PostgreSQL On Native Windows]]

== Alternative to manual installation ==

Some users may prefer to skip manual installation by using a pre-integrated [[TurnKey PostgreSQL | PostgreSQL software appliance]].

[[Category:Administration]][[Category:Install]]

Project Hosting

2012-01-10T21:41:54Z

Gsmith: Added bitbucket to the options list

PostgreSQL provides several way to integrate with [http://www.postgresql.org/docs/9.0/interactive/external-projects.html External Projects], both inside and outside of the core database itself. The PostgreSQL Development Group (PGDG) provides some resources toward hosting PostgreSQL projects.

The current home for many PostgreSQL projects is [http://pgfoundry.org/ PgFoundry], a free resource provided by the PGDG. PgFoundry provides projects with a full set of resources, including version control, mailing lists with archives, and bug tracking. The only source code management (SCM) repository type supported is CVS. Because many projects want more modern SCM options, some existing and new projects have instead hosted their resources at other places. An upgrade to PgFoundry is not expected, and the PGDG is exploring a plan to eventually shut the site down. New projects should consider an alternative hosting site.

Some add-on projects are considered critical to PostgreSQL adoption. One such category are drivers for popular programming languages that are not shipped with the database itself (JDBC, ODBC, others). Critical projects can request space on [http://git.postgresql.org git.postgresql.org] along with a [http://www.postgresql.org/community/lists/ mailing list].

[https://github.com/ github] is a popular site for hosting projects using the git SCM. It provides some basic project tools such as an issue tracker. There is a [https://github.com/postgres-mirror PostgreSQL mirror at github] you can fork to easily create patches against the database source code. github doesn't provide a good way to provide project documentation, source releases (AKA tarballs), or any sort of mailing list for the project. Some projects have combined github for the main project hosting with [http://groups.google.com Google groups] to provide an archived mailing list.

Other sites that are possible places to host a PostgreSQL related project at include:
* [http://sourceforge.net/ sourceforge]
* [http://code.google.com/ Google code]
* [https://launchpad.net/ Launchpad]
* [https://bitbucket.org/ bitbucket]

Each of these sites has a different mix of features it supports. Support for the preferred SCM of the developer is often the first thing considered, since some of these sites only support one of them. For example, github only handles git, and Launchpad only supports Bazaar.

Pgfoundry

2012-01-10T21:33:59Z

Gsmith: Link to Project Hosting page

'''pgfoundry.org''' is a website hosting various Postgres-related software projects. It is the successor to [[gborg]], and runs using [http://gforge.org GForge], an open source collaborative software development tool.

Web site: [http://pgfoundry.org pgfoundry.org]

See [[Project Hosting]] for more information about what pgfoundry provides, and alternatives to it.

Events

2011-12-19T19:38:07Z

Gsmith: Added Postgres Open 2012

== PostgreSQL Events ==

Most PostgreSQL-specific events are tracked on the [http://www.postgresql.org/about/eventarchive PostgreSQL Events] page. This is a listing of events at which we expect, or would like to have, a PostgreSQL presence. Please keep the events in order by starting date and follow the existing examples. Please also tag the events with the MediaWiki "PostgreSQL Events" category. If you are going to be organizing a PostgreSQL booth, please adhere to [[BoothPolicies]]. PostgreSQL Europe conference coordination [[PGUG EU Conference Coordination|is here]]. Conference for China user group [[Pg_envent_cn| is here]].

{| border="1"
|+
|- style="background:Khaki;"
'''Upcoming PostgreSQL Events Listing'''
| '''Event''' || '''Web Page''' || '''Date''' || '''Country''' || '''City''' || '''Activities'''
|-
| [[FOSDEM 2012]] || [http://www.fosdem.org/2012/ FOSDEM '12] || February 04-05, 2011 || Belgium || Brussels || Booth, Devroom
|-
| PGWest 2012 || [https://www.postgresqlconference.org/ PostgreSQL West 2012] || June 26-29, 2012 || USA || Denver, CO || Training, Talks
|-
| PGOpen 2012 || [http://postgresopen.org Postgres Open 2012] || September 16-19, 2012 || USA || Chicago, IL || Training, Talks
|}

{| border="1"
|+
|- style="background:Khaki;"
'''Previous PostgreSQL Events Listing'''
| '''Event''' || '''Web Page''' || '''Date''' || '''Country''' || '''City''' || '''Activities'''
|-
| PostgreSQL Conference Germany 2011 || [http://2011.pgconf.de/ PGConf.DE 2011]|| Nov 11, 2011 || Germany || Oberhausen || [[German-Speaking PostgreSQL Conference 2011|Talks]]
|-
| PGBR2011 || [http://pgbr.postgresql.org.br/ PGBR2011] || Nov 3-4, 2011 || Brazil || São Paulo || Tutorials, Talks, Booth
|-
| PostgreSQL Conference Europe 2011 || [http://2011.pgconf.eu/ PGConf.EU 2011] || October 18-21, 2011 || The Netherlands || Amsterdam || Training, [[PostgreSQL Conference Europe Talks 2011|Talks]]
|-
| PostgreSQL Conference West 2011 || [http://www.postgresqlconference.org/ #PgWest 2011] || September 27-30, 2011 || San Jose || California || Training, Talks, Booth
|-
| Postgres Open || [http://postgresopen.org/2011/home/ Postgres Open 2011] || Sep 14-16, 2011 || USA || Chicago || [[Postgres Open Talks 2011]]
|-
| PGDay Porto Alegre || [http://www.postgresql.org.br/eventos/2011/pgday/rs PGDay Porto Alegre] || August 19, 2011 || Brazil || Porto Alegre || Talks, Booths
|-
| FrOSCon || [http://www.froscon.de/ FrOSCon 2011]|| August 20-21, 2011 || Germany || St. Augustin || Talks, Booth
|-
| Pg Conf Colombia || [http://www.pgconf.org Pg Conf Colombia 2011]|| August 4-5, 2011 || Colombia || Bucaramanga ||
|-
| PgDay at OSCON 2011 || [http://pugs.postgresql.org/node/1663 PgDay at OSCON 2011] || July 24, 2011 || USA || Portland, OR || Talks, party
|-
| 2011 China PostgreSQL User conference ||[http://wiki.postgresql.org/wiki/Pgconchina2011 2011 China PostgreSQL User conference] || July 16-17, 2011 || China || GUANGZHOU ||Talks,Tutorial
|-
| PGCon 2011 || [http://www.pgcon.org/2011/ PGCon 2011] || May 17-20, 2011 || Canada || Ottawa || Talks, Training
|-
| PGEast 2011 || [https://www.postgresqlconference.org/ PGWest] || March 22-25, 2011 || USA || New York, NY || Talks, Training
|-
| PostgresSQL Conference 2011 Japan || || February 25-26, 2011 || Tokyo || Japan ||
|-
| [[FOSDEM, Brussels 2011]] || [http://www.fosdem.org/2011/ FOSDEM '11] || February 05-06, 2011 || Belgium || Brussels || Booth, Devroom
|-
| [[PGDAY-Latino, La Habana 2011]] || [http://postgresql.uci.cu/news/19 PGDAY-Latino '11] || February 01-05, 2011 || Cuba || La Habana || Talks, Workshop
|-
| colspan="6" | [[Events/2010 | 2010 events]]
|-
| colspan="6" | [[Events/2009 | 2009 events]]
|-
| colspan="6" | [[Events/2008 | 2008 events]]
|-
| colspan="6" | [[Events/2007 | 2007 events]]
|}

== External Links ==

* [http://conferences.oreillynet.com/ O'Reilly conferences]
* [http://opencheese.com/2007/10/14/open-source-events-2008/ "Open Source and Linux events in 2008"]

[[Category:PostgreSQL Events]]
[[Category:Advocacy]]

Events

2011-12-19T19:32:55Z

Gsmith: Add PGWest 2012

== PostgreSQL Events ==

Most PostgreSQL-specific events are tracked on the [http://www.postgresql.org/about/eventarchive PostgreSQL Events] page. This is a listing of events at which we expect, or would like to have, a PostgreSQL presence. Please keep the events in order by starting date and follow the existing examples. Please also tag the events with the MediaWiki "PostgreSQL Events" category. If you are going to be organizing a PostgreSQL booth, please adhere to [[BoothPolicies]]. PostgreSQL Europe conference coordination [[PGUG EU Conference Coordination|is here]]. Conference for China user group [[Pg_envent_cn| is here]].

{| border="1"
|+
|- style="background:Khaki;"
'''Upcoming PostgreSQL Events Listing'''
| '''Event''' || '''Web Page''' || '''Date''' || '''Country''' || '''City''' || '''Activities'''
|-
| [[FOSDEM 2012]] || [http://www.fosdem.org/2012/ FOSDEM '12] || February 04-05, 2011 || Belgium || Brussels || Booth, Devroom
|-
| PGWest 2012 || [https://www.postgresqlconference.org/ PostgreSQL West 2012] || June 26-29, 2012 || USA || Denver, CO || Training, Talks
|}

{| border="1"
|+
|- style="background:Khaki;"
'''Previous PostgreSQL Events Listing'''
| '''Event''' || '''Web Page''' || '''Date''' || '''Country''' || '''City''' || '''Activities'''
|-
| PostgreSQL Conference Germany 2011 || [http://2011.pgconf.de/ PGConf.DE 2011]|| Nov 11, 2011 || Germany || Oberhausen || [[German-Speaking PostgreSQL Conference 2011|Talks]]
|-
| PGBR2011 || [http://pgbr.postgresql.org.br/ PGBR2011] || Nov 3-4, 2011 || Brazil || São Paulo || Tutorials, Talks, Booth
|-
| PostgreSQL Conference Europe 2011 || [http://2011.pgconf.eu/ PGConf.EU 2011] || October 18-21, 2011 || The Netherlands || Amsterdam || Training, [[PostgreSQL Conference Europe Talks 2011|Talks]]
|-
| PostgreSQL Conference West 2011 || [http://www.postgresqlconference.org/ #PgWest 2011] || September 27-30, 2011 || San Jose || California || Training, Talks, Booth
|-
| Postgres Open || [http://postgresopen.org/2011/home/ Postgres Open 2011] || Sep 14-16, 2011 || USA || Chicago || [[Postgres Open Talks 2011]]
|-
| PGDay Porto Alegre || [http://www.postgresql.org.br/eventos/2011/pgday/rs PGDay Porto Alegre] || August 19, 2011 || Brazil || Porto Alegre || Talks, Booths
|-
| FrOSCon || [http://www.froscon.de/ FrOSCon 2011]|| August 20-21, 2011 || Germany || St. Augustin || Talks, Booth
|-
| Pg Conf Colombia || [http://www.pgconf.org Pg Conf Colombia 2011]|| August 4-5, 2011 || Colombia || Bucaramanga ||
|-
| PgDay at OSCON 2011 || [http://pugs.postgresql.org/node/1663 PgDay at OSCON 2011] || July 24, 2011 || USA || Portland, OR || Talks, party
|-
| 2011 China PostgreSQL User conference ||[http://wiki.postgresql.org/wiki/Pgconchina2011 2011 China PostgreSQL User conference] || July 16-17, 2011 || China || GUANGZHOU ||Talks,Tutorial
|-
| PGCon 2011 || [http://www.pgcon.org/2011/ PGCon 2011] || May 17-20, 2011 || Canada || Ottawa || Talks, Training
|-
| PGEast 2011 || [https://www.postgresqlconference.org/ PGWest] || March 22-25, 2011 || USA || New York, NY || Talks, Training
|-
| PostgresSQL Conference 2011 Japan || || February 25-26, 2011 || Tokyo || Japan ||
|-
| [[FOSDEM, Brussels 2011]] || [http://www.fosdem.org/2011/ FOSDEM '11] || February 05-06, 2011 || Belgium || Brussels || Booth, Devroom
|-
| [[PGDAY-Latino, La Habana 2011]] || [http://postgresql.uci.cu/news/19 PGDAY-Latino '11] || February 01-05, 2011 || Cuba || La Habana || Talks, Workshop
|-
| colspan="6" | [[Events/2010 | 2010 events]]
|-
| colspan="6" | [[Events/2009 | 2009 events]]
|-
| colspan="6" | [[Events/2008 | 2008 events]]
|-
| colspan="6" | [[Events/2007 | 2007 events]]
|}

== External Links ==

* [http://conferences.oreillynet.com/ O'Reilly conferences]
* [http://opencheese.com/2007/10/14/open-source-events-2008/ "Open Source and Linux events in 2008"]

[[Category:PostgreSQL Events]]
[[Category:Advocacy]]

Archives Policy

2011-12-16T14:11:04Z

Gsmith: Added "E-Mail Disclaimers and Licensing" section

The PostgreSQL [http://www.postgresql.org/community/lists mailing lists] are archived at [http://archives.postgresql.org archives.postgresql.org]. This site is intended to provide an accurate representation of the activity on the lists, and as such '''will not''' be modified.

== Why won't you remove my post? ==

There are a number of reasons why we will not remove or modify messages in the archives:

* We want the archives to accurately represent the history of our public mailing lists.
* Messages posted to our mailing lists are archived by other organisations outside of our control both directly, and via our Usenet gateway. Removing a message from our archives would only be the tip of the iceberg.
* Due to the way the archives are stored and occasionally regenerated from archived mailbox files, there is no way to permanently modify a message.
* Removing a message breaks the numbering system used to access subsequent messages which will break any links on websites or other list messages.
* We have limited volunteer resources to run the project (and no non-volunteer resources) and would prefer to use those resources to produce a great DBMS than rewrite history.

== E-Mail Disclaimers and Licensing ==

Some companies or organization add footers to outgoing e-mail messages with that group's e-mail policies, such as this simple example:

* This e-mail is confidential and only for use by the intended recipient.

These disclaimers are incompatible with sending messages to the PostgreSQL mailing lists. When the mailing list is the "intended recipient", that includes all of its current subscribers and the archives. These archives are public, archived, and searchable; they cannot be made confidential.

By choosing to send messages to a PostgreSQL mailing list, you are implicitly granting your permission for that message to be archived in that fashion, regardless of any e-mail disclaimer it might have. And source code submissions to mailing lists such as ''pgsql-hackers'' are considered submissions to the PostgreSQL project, falling under the project's [http://www.postgresql.org/about/licence/ PostgreSQL license]. Please make sure you have permission to share information with these lists before sending your message.

Reliable Writes

2011-12-01T09:59:09Z

Gsmith: Updated to mention Intel 710 drives

Databases have many levels of read and write caches they use. There's the database's own dedicated memory (shared_buffers), the operating system cache, potentially a disk controller cache (typical on a RAID card), and caches on each individual hard drive. PostgreSQL expects some of these caches to work effectively in order to obtain good performance. But periodically, the database issues a synchronous write request: requesting that something must be committed to disk before the database can proceed. If any of those writes say they are successful to the database, but the data is not really safe, database corruption can be expected if there is a server crash for any reason. Software bug, power outage, UPS failure, accidentally unplugging the server, any of these things can lead to a corrupted database that will not start normally if your system has unsafe writes.

There is a good diagram of how all these levels of caching fit together at [http://momjian.us/main/writings/pgsql/hw_selection.pdf Database Hardware Selection Guidelines] (PDF, Bruce Momjian)

== References ==

This page lists articles about this topic that include a mix of theory and practical recommendations about how to setup a reliable system that doesn't have this sort of problem after a crash:

* [http://www.postgresql.org/docs/current/static/wal-reliability.html Reliability]: from the official documentation
* [https://www.packtpub.com/sites/default/files/0301OS-Chapter-2-Database-Hardware.pdf Database Hardware] (PDF): Chapter on general hardware selection, introduces write-back vs. write-through caching terminology and notes about issues with SSD writes. (Greg Smith)
* [https://www.packtpub.com/article/server-configuration-tuning-postgresql Server Configuration Tuning]: lists things to be aware of when setting wal_sync_method, as well as warnings about settings changes that can make your server unsafe--fsync, full_page_writes, and synchronous_commit (Greg Smith)
* [http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm Tuning PostgreSQL WAL Synchronization]: background about the topic of drives that handle writes unsafely and how this impacts PostgreSQL writes. (Greg Smith)

== Known problem hardware ==

* The first and second generation Intel SSD drives such as the Intel X25-E will write unsafely, and this behavior is impossible to fix without serious impact to both the speed and longevity of the drive. See [http://www.mysqlperformanceblog.com/2009/03/02/ssd-xfs-lvm-fsync-write-cache-barrier-and-lost-transactions/ SSD, XFS, LVM, fsync, write cache, barrier and lost transactions] (Vadim Tkachenko) for an introduction. Their consumer drives such as the X-25-M G2 have been reported to be [http://evanjones.ca/intel-ssd-durability.html even worse] (Evan Jones). The 3rd generation Intel 320 and 710 series drives do not have any of these problems, having added a capacitor to provide battery-backup for flushing the drive cache when power drops.

Advocacy

2011-11-25T15:28:54Z

Gsmith: Link to Projects Using page

__NOTOC__* [[Events|Upcoming events & conferences]]
* [[AdvocacyGuides|Guides to promoting PostgreSQL]]
* [[:Category:Software Ports|Software Ports]]: Getting PostgreSQL support into applications
* [[Advocacy/Better|Things Advocacy could be doing better]]
* [[Postgres| Postgres - Changing the name]]: Requirements and issues

===PostgreSQL Booths - Conference/Event===
* [[BoothCheckList|Booth Preparation Checklist]]
* Organizing [[BoothVols|Booth Volunteers]]
* [[HowToBooth|How To Booth]] for both Booth Bunnies and Organizers
* [[BoothDocs|Booth Related Documents]] List

=== Branding Resources===
* [[Identity Guidelines]], [[Color Palette]]
* [[Logo]], [[Buttons]], [[Banners]]
* [[Posters]], [[Rollups]]
* [[Flyers]], [[Brochures]]
* [[Surveys]]
* [http://pgfoundry.org/docman/?group_id=1000089 PgFoundry's Graphic Project]
* [[Marketing Blurbs]]

===New release management===
* [[ReleasePrep|Tasks to do before announcing a release]]
* [[MajorReleaseTimeline|Major Release Timeline]]
* [[HowToTranslate|HOWTO Translate The Release]]
* [[PostgreSQL 8.4]] - History of last completed release
* [[PostgreSQL 9.0 Press Coverage]]

===Work in Progress===
* [[UserGroupOperatingManual|PUG Operating Manual]]
* [[:Category:Advocacy WIP|Advocacy WIP]]
* [[PgDayManual|Advice on running a PgDay]]
* [[Short Topic Books]]
* [[CodeSprint2008|Code Sprint Session at Pg Conference West 2008]]
* [[Open Source Projects Using PostgreSQL]]

===Other Resources===
* Education [[Curriculum Development]]
* [[YourPostgreSQLAddress|Your @postgresql.org e-mail address]]
* [[HowToRC|How to be a Regional Contact]]

[[Category:Advocacy]]