PostgreSQL wiki - User contributions [en]

RLS

2013-03-07T15:26:57Z

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

= Row-Level Security =

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

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

== Jobs of access control feature ==

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

== Design Target ==

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

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

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

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

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

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

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

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

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

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

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

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

If it was followed by:

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

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

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

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

== Issues & discussion ==

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

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

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

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

= Previous discussion in 2010 =

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

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

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

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

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

=== Discussion ===

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

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

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

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

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

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

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

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

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

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

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

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

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

[[Category:SELinux]]

What's new in PostgreSQL 9.2

2012-03-20T13:37:44Z

Robe: fix version numbers in headline

{{Languages}}

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

'''This page is incomplete!'''

=Major new features=

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

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

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

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

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

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

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

=Performance improvements=

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

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

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

[[Category:PostgreSQL 9.2]]

LDAP Authentication against AD

2012-01-30T15:37:13Z

Robe: drop ownership info, this is a wiki

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

The following instruction applies to Redhat RPM installation.

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

Streaming Replication

2011-09-28T09:58:09Z

Robe: reworded archive_mode necessity

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

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

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

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

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

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

listen_addresses = '192.168.0.10'

$ $EDITOR pg_hba.conf

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

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

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

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

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

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

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

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

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

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

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

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

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

= Todo =
== v9.0 ==

Moved to [[PostgreSQL_9.0_Open_Items]]

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

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

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

[[Category:Replication]]

PGUG EU Media Coordination

2010-06-26T16:59:02Z

Robe:

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

= Publications =

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

= Published articles =

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

PGUG EU Media Coordination

2010-06-26T16:58:39Z

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

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

= Publications =

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

= Published articles =

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

PGUG EU Conference Coordination

2010-06-26T16:27:22Z

Robe: /* Completed Events */

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-26T16:26:23Z

Robe: /* Completed Events */

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-26T16:25:00Z

Robe: /* Conferences */

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-25T12:05:11Z

Robe: /* Planning 2010 */

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-25T11:24:27Z

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

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-25T11:23:48Z

Robe: osdc done, added froscon

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

PGUG EU Conference Coordination

2010-06-07T06:25:52Z

Robe: amoocon done

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

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

User:Robe

2010-05-24T21:57:26Z

Robe:

= Ad personam =

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

= Scribbles =

== Logging ==

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

The main advantages are:

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

=== Standard Syslog Output ===

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

=== Standard Logging Output ===

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

=== Syslog-Emulation ===

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

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

Which is already much more deterministic than the defaults.

= Ideas =

* Fix Status Quo of VACUUM Full
* Fix Cluster
* Add more Aliases for different Encodings
* Ease Table Partitioning
* Revisit all non-transactional/WAL logged commands
* Key Compression for Btree indexes
* Revisit insert fill behavior in pgexerciser.pl

PGUG EU Conference Coordination

2010-04-30T17:35:22Z

Robe: /* Planning 2010 */ -> netways accepted

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|}

PGUG EU Conference Coordination

2010-04-13T08:40:48Z

Robe: /* Planning 2010 */ Added AMOOCON

= Conferences =

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

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

= Planning 2010 =

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

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|}

PGUG EU Conference Coordination

2010-04-13T08:39:07Z

Robe: /* Conferences */ added amoocon

= Conferences =

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

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

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| Netways OSDC||23rd & 24th June||Michael Renner||Talk||Talk submitted, waiting for affirmation
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|}

PGUG EU Conference Coordination

2010-03-22T13:48:01Z

Robe: dropped the google docs note.

= Conferences =

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

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

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| Netways OSDC||23rd & 24th June||Michael Renner||Talk||Talk submitted, waiting for affirmation
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|}

PGUG EU Conference Coordination

2010-03-22T08:32:37Z

Robe: /* Conferences */ added froscamp

Ohai! Work in progress.

All tables available as Google Spreadsheet (contact [[User:Robe|Michael Renner]]) and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

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

= Planning 2010 =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| Netways OSDC||23rd & 24th June||Michael Renner||Talk||Talk submitted, waiting for affirmation
|-
| FrOSCamp||17th & 18th Sept||Markus Wanner||Booth & Talks||Project submitted, waiting for affirmation
|}

= Completed Events =

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February 2010||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March 2010||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March 2010||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|}

PGUG EU Conference Coordination

2010-03-14T23:40:44Z

Robe: /* Conferences */ sort by date

Ohai! Work in progress.

All tables available as Google Spreadsheet (contact [[User:Robe|Michael Renner]]) and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

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

= Planning 2010 =

Scheduled and completed Events in 2010

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| Netways OSDC||23rd & 24th June||Michael Renner||Talk||Talk submitted, waiting for affirmation
|}

PGUG EU Conference Coordination

2010-03-14T23:20:41Z

Robe: /* Planning 2010 */ add osdc to 2010

Ohai! Work in progress.

All tables available as Google Spreadsheet (contact [[User:Robe|Michael Renner]]) and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

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

= Planning 2010 =

Scheduled and completed Events in 2010

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
| align="center" style="background:#f0f0f0;"|'''Comments'''
|-
| FOSDEM||6th & 7th February||Andreas Scherbaum, ..||Talks & Booth||
|-
| CeBIT||2nd - 6th March||Andreas Scherbaum, ..||Booth||
|-
| Chemnitzer Linuxtage||13th & 14th March||Andreas Scherbaum, Andreas Kretschmer, ...||Booth||
|-
| Netways OSDC||23rd & 24th June||Michael Renner||Talk||Talk submitted, waiting for affirmation
|}

PGUG EU Conference Coordination

2010-03-14T23:15:20Z

Robe: /* Conferences */ add a few ballpark figures wrt visitors

Ohai! Work in progress.

All tables available as Google Spreadsheet (contact [[User:Robe|Michael Renner]]) and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

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

= Planning 2010 =

Scheduled and completed Events in 2010

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
|-
| FOSDEM||6th & 7th February||Andreas Scherbaum, ..||Talks & Booth
|-
| CeBIT||2nd - 6th March||Andreas Scherbaum, ..||Booth
|-
| Chemnitzer Linuxtage||13th & 14th March||Andreas Scherbaum, Andreas Kretschmer, ...||Booth
|-
|
|}

PGUG EU Conference Coordination

2010-03-14T23:11:30Z

Robe:

Ohai! Work in progress.

All tables available as Google Spreadsheet (contact [[User:Robe|Michael Renner]]) and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||||Tuesday - Saturday||Early March||German
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
|
|}
= Planning 2010 =

Scheduled and completed Events in 2010

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
|-
| FOSDEM||6th & 7th February||Andreas Scherbaum, ..||Talks & Booth
|-
| CeBIT||2nd - 6th March||Andreas Scherbaum, ..||Booth
|-
| Chemnitzer Linuxtage||13th & 14th March||Andreas Scherbaum, Andreas Kretschmer, ...||Booth
|-
|
|}

PGUG EU Conference Coordination

2010-03-14T23:09:18Z

Robe:

Ohai! Work in progress.

All tables available as Google Spreadsheet and easily [http://excel2wiki.net/ convertible].

= Conferences =

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

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Location'''
| align="center" style="background:#f0f0f0;"|'''URL'''
| align="center" style="background:#f0f0f0;"|'''Contact Information'''
| align="center" style="background:#f0f0f0;"|'''Type'''
| align="center" style="background:#f0f0f0;"|'''Size'''
| align="center" style="background:#f0f0f0;"|'''Duration'''
| align="center" style="background:#f0f0f0;"|'''Approx. Time'''
| align="center" style="background:#f0f0f0;"|'''Language'''
|-
| Chemnitzer Linuxtage||Chemnitz, Germany||http://chemnitzer.linux-tage.de/2010/||||FOSS community conference||||Weekend||Mid March||German & English
|-
| Grazer Linuxtage||Graz, Austria||http://www.linuxtage.at/||||FOSS community conference||||Weekend||Late April||German
|-
| FrOSCon||St. Augustin, Germany||http://www.froscon.de/||||FOSS community conference||||Weekend||Late August||German & English
|-
| FOSDEM||Brussels, Belgium||http://fosdem.org/2010/||||FOSS community conference||||Weekend||Early February||English
|-
| CeBIT||Hanover, Germany||http://www.cebit.de/||||Trade fair||||Tuesday - Saturday||Early March||German
|-
| PGDay Europe||changing||http://2009.pgday.eu/||||PostgreSQL Community conference||||Friday & Saturday||November||English
|-
| Netways OSDC||Nuremberg, Germany||http://www.netways.de/en/osdc/||||Commercial conference||||Wednesday & Thursday||Early June||German & English
|-
| Linuxtag||Berlin, Germany||http://www.linuxtag.org/||||FOSS community conference||||Wednesday - Saturday||Mid June||German?
|-
| FOSSGIS||Osnabrück, Germany||http://www.fossgis.de/konferenz/||||FOSS GIS community conference||||Tuesday - Friday||Early March||German?
|-
| OpenRheinRuhr ||"Im Pott", Germany||http://www.openrheinruhr.de/||||FOSS community conference||||TBD||November||German?
|-
| Kieler Linuxtage||Kiel, Germany||http://www.kieler-linuxtage.de/||||FOSS community conference||||Friday & Saturday||Early October||German?
|-
| Brandenburger Linux Info-Tag||Potsdam, Germany||http://www.blit.org/||||FOSS community conference||||Saturday||Mid November||German?
|-
|
|}
= Planning 2010 =

Scheduled and completed Events in 2010

{|
| align="center" style="background:#f0f0f0;"|'''Name'''
| align="center" style="background:#f0f0f0;"|'''Date'''
| align="center" style="background:#f0f0f0;"|'''Attendees'''
| align="center" style="background:#f0f0f0;"|'''Type of attendance'''
|-
| FOSDEM||6th & 7th February||Andreas Scherbaum, ..||Talks & Booth
|-
| CeBIT||2nd - 6th March||Andreas Scherbaum, ..||Booth
|-
| Chemnitzer Linuxtage||13th & 14th March||Andreas Scherbaum, Andreas Kretschmer, ...||Booth
|-
|
|}

PGUG EU Conference Coordination

2010-03-14T23:03:19Z

Robe: initial commit

User:Robe

2010-03-03T00:37:18Z

Robe: /* For 8.5 */

User:Robe

2010-03-03T00:37:09Z

Robe: /* For 8.5 */

= Ad personam =

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

= Scribbles =

== Logging ==

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

The main advantages are:

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

=== Standard Syslog Output ===

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

=== Standard Logging Output ===

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

=== Syslog-Emulation ===

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

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

Which is already much more deterministic than the defaults.

= Ideas =

== For 8.5 ==

* Fix Status Quo of VACUUM Full
* Fix Cluster
* Add more Aliases for different Encodings
* Ease Table Partitioning
* Revisit all non-transactional/WAL logged commands
* Key Compression for Btree indexes

Hot Standby

2009-05-17T16:04:05Z

Robe: fixing second version mentioning

Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.

Hot Standby is being developed for inclusion in PostgreSQL 8.5 by 2ndQuadrant via public sponsorship.

This feature will augment the capabilities of streaming replication that have also been discussed for inclusion in r8.5, though does not rely on it and has minimal interaction with it.

Changes will appear on this page, as and when design changes or clarifications are required. Please reply to posts on pgsql-hackers, or email simon@2ndquadrant.com

= Project Status Tracking =

== Version History ==

Latest version is always at top of this section

* [[:Image:hs.v9g.20090123_3.tar.bz2|hs.v9g.20090123_3.tar.bz2]] - v9g - bug fixes
* [[:Image:hs.v9d.20090122_4.tar.bz2|hs.v9d.20090122_4.tar.bz2]] - v9 - new conflict resolution and various refactorings
* [[:Image:hs.v8.20090114_1.tar.bz2|hs.v8.20090114_1.tar.bz2]] - v8 - bug fixes and knock-on effects of refactoring
* [[:Image:hs.v7.20090112_1.tar.bz2|hs.v7.20090112_1.tar.bz2]] - v7 - includes slotId removal refactoring
* [[:Image:hs.v6b.20090107_1.tar.bz2|hs.v6b.20090107_1.tar.bz2]] - v6b
* [[:Image:hs.v6a.20090103_1.tar.bz2|hs.v6a.20090103_1.tar.bz2]] - v6a new test cycle commencing (replaces v6)
* [[:Image:hs.v5.20081218_1.tar.bz2|hs.v5.20081218_1.tar.bz2]] - v5 corrected patch application to CVS HEAD, doc typos fixed
* [[:Image:hs.v5.20081217_2.tar|hs.v5.20081217_2.tar]] - v5 broken down into smaller patches for review

== Remaining Work Items for Nov 2008 CommitFest ==

* Latest commit that is being tested: 4fe66...

Reported problems (Identified by) (Target release)
* (none)

Infrastructure changes
* (none)

Main work/required additional items (Proposed by) (Target release)
* (none)

Performance tuning opportunities (Proposed by) (Target release)
* (none)

Usability enhancements (Proposed by)
* max_standby_delay set as a normal GUC, so it can be changed during normal execution (Simon)
* more accurately determine users to disconnect if we drop a tablespace that contains temp files currently in use
* more work possible on btree deletes to reduce impact
* input datatypes for functions (Simon)
* main docs required (put Wiki into SGML) (Simon)

Items deferred until next release
* make pg_start_backup() work on standby - synchronisation is already possible using pg_latest_recovered_lsn()
* connection from standby to master to prevent xmin horizon moving forwards on master
* buffer manager optimization targeted to reduce I/O for replay of btree vacuum when scanning uncleaned blocks (Simon)
* Canceled queries when idle in transaction cause client-side mismatch [slightly unrelated) (Simon) This only effects transactions that are idle-in-transaction AND either hold locks, are serializable or have open cursors. Current solution is upgrade to a FATAL error. The same problem occurs if we have an error while processing invalidation messages.

Tests needed
* Is there a performance regression on normal running from any overheads introduced? Patch v HEAD comparison
* Is there a performance regression on recovery? Patch v HEAD comparison (4% degradation on simple test at v4, need retest for later versions)
* Usability tests: How does query conflict handling work in practice? User experience needed

Code has been reviewed in various parts by Pavan, Heikki and Tom. Further detailed review/ers are still required.

== Resolved Items/Issues ==

Reported problems (Reported by/Requested by) (Resolved in version...) (Most recent at top)

=== Bugs present on Nov 1 ===

* minSafeStart point reset to earlier in some cases (Heikki) (Infra)
* signal postmaster to refresh password cache if auth file changes (Simon) (v9g)
* fix query cancel because of locks so it includes AbortOutOfAnyTransaction(); (Simon) (v9e)
* fix memory leak in GetRunningXactData() (Simon) (v9)
* conflict resolution for redo of database drop/move/rename while users are connected to that database (Simon) (v9)
* conflict resolution for redo of drop tablespace while users are using temp files in that tablespace - correct but needs work (Simon) (v9)
* Handle out of procs state gracefully, prevent snapshots and throw user query errors (Heikki) (v8d)
* AccessExclusiveLock must be held by subtransaction, not top-level transaction (Heikki/Simon) (v8)

* Block unlocking required during VACUUM scan (Heikki) (v6b)
* XLOG_BTREE_DELETE records handled correctly (Heikki) (v6)
* btree VACUUM code - must scan every block of index (Heikki) (v6)
* BEGIN TRANSACTION READ WRITE (Pavan) (v6)
* Must ignore_killed_tuples and never kill_prior_tuple during index scans in recovery (Greg) (v6) same fix, subtlely different scope (v8)
* uint unsupported on Windows (Jaime) (v6b)
* SIGINT cancels idle-in-transaction sessions (Pavan) (v6b)
* WALInsertLock still held at failover (Pavan) (v5)
* flat file updates for DDL incorrect (Mark) (v5)

=== Refactoring, bugs, tuning, usability tweaks ===

Functionality changes since Nov 1

* Complete Prepared Transaction support (Simon)
* fix bugs with recovery_starts_paused(), allow shutdown while paused (Simon)
* Compile error with WAL_DEBUG set (Bernd)
* removed reliance on pg_subtrans marking optimisation (Heikki/Simon)
* merged changes with Heikki's most recent review of recoveryinfra changes (Simon)
* tuning of RecordKnownAssignedTransactionIds() to avoid taking ProcArrayLock in most common case (Simon) (v9p)
* tuning of RecordKnownAssignedTransactionIds() using hash table (Heikki) (v9m)
* deferred buffer recovery conflict cache (Simon) (v9k)
* rename parameter used to turn on/off hot standby (Merlin) (v9i)
* Allow option to start recovery in paused mode (Kevin) (v9i)
* startup process waits, even when issuing deferred query cancellation (Heikki/Gianni) (v9i)
* Workaround: Canceled queries when idle in transaction cause FATAL errors because of client-side mismatch bug (Simon) (v9i)
* XidCacheRemoveXid (Simon) (v9i)
* rare assertion failure in pg_subtrans - caused by invalid assumption about when to mark subtrans (Gianni)
* snapshot bug (v9h) (Gianni & Mark)
* using wrong LSN in conflict handling causing more cancels than required (Gianni) (v9f)
* add parameter to disable standby query capability ("hot_standby") (Heikki) (v9e)
* fix race condition for SetBufferRecoveryConflictLSN(), recheck MyProc->xmin (Simon) (v9e)
* streamline commit/abort processing to bare minimum (Simon) (v9d)
* improved debug error messages for unobserved xids (Gianni) (v9c)
* missed some calls to RecordKnownAssignedTransactions() during last refactoring (Simon) (v9c)
* pg_recovery_advance() regression - fixed (Simon) (v9b)
* alter location of debug messages for AssignTransactionId (Simon) (v9a)
* remove docs for pg_recovery_pause_cleanup() (Simon) (v9)
* reduce use of ProcArrayLock to once per record, plus one at commit/abort (Simon) (v9)
* RecoveryConflictLSN, deferred cancellation of xmin horizon conflicts and skip cancellation if idle-in-xact (Andreas) (v9)
* make btree delete watertight (can't use RecentGlobalXmin) - correct but needs work (Heikki) (v9)
* must not do conflict processing when snapshots disabled - need to refactor some more, groan (Simon) (v9)
* rearrange call to UnobservedTransactionsPruneXids() to speed up calls to UnobservedTransactionsRemoveXid() (Simon) (v9)
* remove pg_recovery_pause_cleanup() which stopped working in v8e because of refactoring (Simon) (v9)
* downgrade "WARNING: proc array entry was missing for transaction" to DEBUG2 (Happens because of the race condition between XidGenLock and WALInsertLock. Occurred 4 times in 10 hour bash test on dual CPU server, but all cases have been normal and explainable since the xid was the latestRunningXid in all cases.) (Gianni) (v9)
* refactor rmgr "isCleanupRecord" code (Heikki) (v8e)
* remove databaseId, roleId and vacFlags from RunningXactData (Simon) (v8d)
* complete refactoring required change of ProcArrayUpdateRecoveryTransactions() (Heikki), plus fix one stupid and one very subtle bug while doing so (Simon) (v8c)
* not updating pg_auth when appropriate (Gianni) (v8b) -- trivial logic error
* optimize UnobservedTransactionsPruneXids() and fix corner case bug (Gianni) (v8b)
* split recovery procs into different pools, allowing error messages when overflowed (Simon) (v8a)
* refactor pre-allocation of Recovery Procs, need to trace dependency that prevents removal (Simon) (v8a)
* refactor XactCompletionHasUnmarkedSubxids() (Simon) (v8a)
* fix bug caused by earlier refactoring in ProcArrayClearRecoveryProcs() (Simon) (v8a)
* UnobservedXids leak warning - trivial fix (Gianni) (v8a)
* avoid starting autovac launcher for wraparound avoidance during recovery (Simon) (v8a)
* clarify meaning of last_recovered... functions during/after recovery (Simon) (v8)
* add function last_recovered_xlog_location() to allow synchronisation between master and standby based on an LSN (Simon) (v8)
* Document that index locking is correct and why (Heikki) (v8)
* add README comments to access/transam/README (Simon) (v8)
* document need to set max_connections correctly on standby (Simon) (v8)
* refactor/remove special case logic in GetRunningTransactionData() (Heikki) (v8)
* pg_subtrans could not open file (Heikki) (v8)
* added pg_current_recovery_target() (Gianni) (v8)
* allow startup process to delay at start to allow debugger attach where pg_ctl -o "-W n" sets a delay of n secs (Simon) (v8)
* adjust all XXX comments related to Hot Standby to be XXXHS (Simon) (v8)
* renamed last_completed... functions to last_recovered... (Simon) (v8)
* slotid refactoring, comments changed (Heikki) (v7)
* Cannot Assert(pmState == PM_STARTUP) when postmaster catches PMSIGNAL_RECOVERY_START (regression from earlier report) (v6b)
* Use of #define may mask bugs in 1-2 locations in procarray.c (Pavan) (v6b)

Do we need/want? (Current answer)
* recovery_safe_start_location; I think we do (Not implemented)
* to automatically reset default_transaction_read_only following startup (No)
* Creating temp tables in recovery mode. Not sure if that is spec compliant? We may want to relax that restriction. If we do, we already have a design in place for changes to make this work: http://archives.postgresql.org/pgsql-hackers/2008-07/msg00616.php. (Not in 8,4)

Resolved Issues/Questions
* No need to send invalidations when index metapages change - already handled at xact commit
* Added further user/admin documentation via this Wiki

Excluded items
* hash index handling (or any index type that doesn't write WAL)
* Cancelled queries to show errcode(ERRCODE_T_R_SERIALIZATION_FAILURE) (cannot do, unable to identify source of cancellation)

= Usage =

== Users Overview ==

Users will be able to connect to the database while the postmaster is in recovery state and perform read-only queries. Read-only access to catalogs and views will also occur as normal.

When you connect in this mode, default_transaction_read_only will be forced to be true, whatever its setting in postgresql.conf. As a result, all transactions started during this time will be limited to read-only actions only. In all other ways, connected sessions will appear identical to sessions initiated during normal processing mode. There are no special commands required to initiate a connection at this time, so all interfaces will work normally without change.

Read-only here means "no writes to the permanent database tables". So temporary sort and work files will be used, just as with normal SQL execution. Temporary tables cannot be created and therefore cannot be used at all in recovery mode.

Actions that are allowed
* Transaction management commands: BEGIN, END, ABORT, START TRANSACTION
* SELECT, COPY TO
* Cursor commands: DECLARE, FETCH, CLOSE,
* Parameters: SHOW, SET, RESET, DISCARD
* LOAD
* Plans: PREPARE, EXECUTE, DEALLOCATE
* LOCK (see later)
* SAVEPOINT commands: SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
* EXCEPTION blocks in internal subtransactions
* Activation of Select RULEs

Actions not allowed on Standby are:
* Transaction management commands which explicitly set non-read only state: SET TRANSACTION, SET SESSION CHARACTERISTICS, BEGIN, START TRANSACTION, direct SET
* Two-phase commit commands (PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED) because even read-only transactions need to write WAL in the prepare (first phase of two phase commit).
* SELECT ... FOR SHARE | UPDATE which cause row locks to be written
* DML - Insert, Update, Delete, COPY FROM, Truncate which all write data
* DDL - Create, Drop, Alter, Comment (even for temporary tables because currently these cause writes to catalog tables)
* Access to nextval on sequences because nextval causes the value of the sequence to be increment
* LISTEN, UNLISTEN, NOTIFY since they currently write to system tables
* Remember: Triggers will never be executed!
All of these are allowed on primary, just not on standby in read-only mode. When failover, switchover occurs these commands will then be possible.

It is possible that the restrictions on LISTEN, UNLISTEN, NOTIFY and temporary tables may be lifted in a future release, if their internal implementation allows it.

Note that current behaviour of read only transactions is to allow these
* nextval on sequences
* LISTEN, UNLISTEN, NOTIFY
So there is a subtle difference in behaviour between standby read-only transactions and non-recovery read only transactions.

If failover or switchover occurs the database will switch to normal processing mode. Sessions will remain connected while the server changes mode. Current transactions will continue, though will remain read-only. After this, it will be possible to initiate read-write transactions, though users must *manually* reset their default_transaction_read_only setting first.

Users will be able to tell whether their session is read-only by issuing SHOW default_transaction_read_only, or by running an information function pg_is_in_recovery() returns bool. There is also be a function that allows you to read the last known transaction end timestamp (pg_last_completed_xact_timestamp) and the transactionid (pg_last_completed_xid).

In recovery, transactions will not be permitted to take any lock higher than AccessShareLock nor assign a TransactionId. This mostly prevents writing WAL, but we protect against that specifically as well, just in case some user code attempts it.

The LOCK TABLE command by default applies an AccessExclusiveLock. This will generate WAL messages when executed on the primary node. When executed on the standby node the default will be to issue an AccessShareLock. Any LOCK TABLE command that runs on the standby and requests a specific lock type other than AccessShareLock will be rejected.

During recovery database writes will only be performed by startup process as it processes WAL records and replays them (also known as WAL redo or simply "apply"). In general this means that queries will not experience lock conflicts with writes, just like normal Postgres concurrency control (MVCC).

=== Query Conflicts ===

However, there is some potential for conflict between standby queries and WAL redo from the primary node. The user is provided with a number of optional ways to handle these conflicts.

Conflicts can occur for four reasons
* Access Exclusive Locks from primary node (including both locks and various kinds of DDL action)
* Early cleanup of data still visible to the current query's snapshot
* Dropping tablespaces on the master while standby queries are using those tablespace for temporary work files (work_mem overflow)
* Dropping users, databases or tablespaces on the master while those objects are connected on standby
* Waiting to acquire buffer cleanup locks (for which there is no time out)
discussed in more detail below.

Some WAL redo actions will be for DDL actions. These DDL actions are repeating actions that have already committed on the primary node, so they must not fail on the standby node. These DDL locks take priority and will automatically *cancel* any read-only transactions that get in their way, after a grace period. This is similar to the possibility of being canceled by the deadlock detector, but in this case the standby process always wins, since the repeated actions must not fail. This also ensures that replication doesn't fall behind while we wait for a query to complete. Again, we assume that the standby is there for high availability purposes primarily.

An example of the above would be:
* User on Standby running a query against table X
* Administrator on Primary runs DROP TABLE
Clearly the query cannot continue if we let the DROP TABLE proceed. If this situation occurred on the primary, the DROP TABLE would wait until the query has finished. When the query is on the standby and the DROP TABLE is on the primary, the primary doesn't have information about what the standby is running and so cannot wait. In most cases this is desirable, so the workload on the standby has little or no effect on the primary and so we would say the two nodes are only loosely coupled.

The second reason for conflict between standby queries and WAL redo is "early cleanup". Normally, PostgreSQL allows cleanup of old row versions when there are no users who may need to see them to ensure correct visibility of data (known as MVCC). If there is a standby query that has been running for longer than any query on the primary then it is possible for old row versions to be removed by either VACUUM or HOT. This will then generate WAL records that, if applied, would remove data on the standby that might *potentially* be required by the standby query. In more technical language, the Primary's xmin hozrizon is later than the Standby's xmin horizon, allowing dead rows to be removed.

We have a number of choices for resolving query conflicts. Choice (1) is automatic, options (2) and (3) should be taken as remedial actions if an annoying number of cancelations occur.

1. We wait and hope the query completes. If the recovery is not paused, then we will wait automatically until the server is max_standby_delay behind. Once that grace period expires, we then take one of the following actions:

a) If the conflict is caused by a lock, we cancel the standby transaction immediately, even if it is idle-in-transaction.

b) If the conflict is not caused by a lock, we tell the standby query that a conflict has occurred and that it must cancel itself if there is a risk that it attempts to read data that has been cleaned up already. Specifically, if the standby query reads a data block that has been recently modified it will cancel itself. Note it only needs one modified data block to cause a cancel and that the data block does *not* need to have been modified by a cleanup record - any change is sufficient to create this risk. (This is very similar to the much feared Oracle error message "snapshot too old"). Note also that this means that idle-in-transaction sessions are never canceled except by locks. Users should be clear that tables that are regularly and heavily updated on primary server will quickly cause cancellation of any longer running queries made against those tables.

If cancellation does occur, the query and/or transaction is repeatable - i.e. the error is dynamic and will not necessarily occur the same way if the query is executed again.

2. We connect to Primary server from Standby server and keep a transaction open. This guarantees that a cleanup record is never generated and we don't ever get into situation 1(b). This option can be performed fairly simply using contrib/dblink functions.

3. We pause recovery by issuing a pg_recovery_pause() function. This halts application of WAL records completely and allows queries to proceed to completion without problem. We can issue pg_recovery_continue() at any time, so the pause can be held for long or short periods, as the administrator allows. This method of conflict resolution may mean that there is a build up of WAL records waiting to be applied and this will progressively increase the failover delay. If there is regular arrival of WAL records this would quickly prevent the use of the standby as a high availability failover target. Some users may wish to use multiple standby servers for various purposes. Pauses in recovery stay until explicitly released, i.e. max_standby_delay is overridden by the use of pauses.

An example of the above would be:
* User on Standby running a long query involving tables X and Y
* User on Primary repeatedly updates table Z
The user on the primary will be generating HOT updates on table Z and these will generate cleanup WAL records. In this case the rows cleaned up are very recent and on the standby node we are able to detect that removing those row versions could *potentially* prevent the Standby query from executing correctly and so there is a conflict. Initially, we will wait for max_standby_delay, but then afterwards we will give the standby query notice to quit. In this case, the standby query never does read data that has been cleaned, since it is looking at different tables, so the standby query runs to completion normally.

We keep track of the first 8 relations (that is, indexes and/or tables) that cause conflicts. When we have conflicts with more than 8 tables we apply the conflict handling to all relations. So in the above example if we had already recorded conflicts with eight other tables (plus Z means 9 tables, more than our cache) then we *would* cancel the query against tables X and Y, even though those tables have not been touched. Conflict processing increases quickly with number of tables in the conflict cache, so raising the number above 8 is not practical.

Note that max_standby_delay is set in recovery.conf. It applies to the server as a whole, so once used it may not be available for other users. They will have to wait for the server to catch up again before the grace period is available again. So max_standby_delay is a configuration parameter set by the administrator which controls the maximum acceptable failover delay, rather than a user parameter to specify how long their query needs to run in.

Waits for buffer cleanup locks do not currently result in query cancellation. Long waits are uncommon, though can happen in some cases with long running nested loop joins.

These two items are discussed in the administrator's section since they are not typical user situations
* Dropping tablespaces on the master while standby queries are using those tablespace for temporary work files (work_mem overflow)
* Dropping users, databases or tablespaces on the master while those objects are connected on standby

== Administrators Overview ==

Hot Standby is enabled by default, though can be disabled by setting "recovery_connections = off" in recovery.conf.

A second parameter, "max_standby_delay" should also be set by the administrator in recovery.conf. The default is 30 seconds. This should be set according to business priorities. For example if the server is primarily tasked as a High Availability server, then you may wish to lower max_standby_delay or even set it to zero. If the standby server is tasked as an additional server for decision support then it may be acceptable to set this to a value of many hours, e.g. max_standby_delay = 43200 (12 hours). It is also possible to set max_standby_delay to -1 which means "always wait" if there are conflicts, though if unmonitored this may not be a useful mode.

It is strongly recommended that the setting of max_connections on the standby should be equal to or greater than the setting of max_connections on the primary. This is to ensure that standby has sufficient resources to manage incoming transactions. If the standby is asked to manage too many concurrent *write* transactions it will run out of recovery session resources. Read only transactions have no effect on the standby. If the standby runs out of recovery session resources then recovery will continue normally, but it will prevent MVCC snapshots from being taken and all standby queries will fail with an ERROR. Snapshots will be re-enabled after the next checkpoint on the primary for which the number of write transactions has fallen back below the cutoff.

Users will be able to write large sort temp files and re-generate relcache info files, so there is no part of the database that is truly read-only during hot standby mode. There is no restriction on use of set returning functions, or other users of tuplestore/tuplesort code.

Statspack functions should work OK, so tools such as pgAdminIII should work. pgAgent will not, though the next version will allow execution of jobs on a read only node.

Failover can be initiated at any time by allowing the startup process to reach the end of WAL, or by issuing the function pg_recovery_stop() as superuser. (see next section).

Stats collector is active during recovery. All scans, reads, blocks, index usage etc will all be recorded normally on the standby. Replayed actions will not duplicate their effects on primary, so replaying an insert will not increment the Inserts column of pg_stat_user_tables. The stats file is deleted at start of recovery, so stats from master and standby will differ.

pg_cancel_backend() will work on user backends, but not Startup process. pg_locks will show locks held by backends and by startup process. pg_stat_activity will not show an entry for startup process.

check_pgsql will work, but its very simple. check_postgres will also work, though many some actions could give
different or confusing results. e.g. last vacuum time will not be
maintained for example, since no vacuum occurs on the standby.

Dynamically loadable modules work, including the new pg_stat_statements.

Advisory locks work normally in recovery, including deadlock detection. No other form of deadlock is possible in recovery because the only only lock type that can be taken on database objects is AccessShareLock. Please note that advisory locks are never WAL logged, so it is not possible for an advisory lock to conflict with WAL replay.

slony won't run on the standby either! The Standby is a Clone and not a Slave. A Slave is a separate database that is forced to duplicate the actions of the Master. The Standby is an exact copy, in every detail that matters.

New oids cannot be assigned, though various UUID generators may still work if required.

Currently, creating temp tables is not allowed during read only transactions, so in some cases existing scripts will not run correctly. It is possible we may relax that restriction in a later release. This is both a SQL Standard compliance issue and a technical issue, so will not be resolved in this release.

Running DROP TABLESPACE on master has no effect on users on standby, though can only succeed if the tablespace is empty. Some standby users may be actively using the tablespace via their temp_tablespaces parameter. If so, those users will be have their current queries cancelled, so that they remove their temp files and allow WAL replay to proceed.

Running DROP DATABASE, ALTER DATABASE SET TABLESPACE, or ALTER DATABASE RENAME on master will cause all users connected to that database on the standby to be forcibly disconnected, once max_standby_delay has been reached. Similarly, running DROP USER or DROP ROLE for a role with login capability will cause all users connected using that role to be forcibly disconnected, once max_standby_delay has been reached.

WAL file control commands will not work during recovery e.g. pg_start_backup(), pg_switch_xlog() etc..

The following administrator commands will not be accepted during recovery mode
* ANALYZE
* VACUUM
* CLUSTER
* REINDEX
* CHECKPOINT
* GRANT, REVOKE, REASSIGN
* DDL - e.g. CREATE INDEX
Note again that some of these commands are actually allowed during "read only" mode transactions on the master.

So you cannot create additional indexes on the standby node, nor can you collect statistics via ANALYZE in new/different ways. (EXPLAIN ANALYZE still works, it is a different command).

If you wish to perform maintenance on the standby, run commands on the primary and the standby will then also mimic those changes. e.g. if you need to ANALYZE a table, do so on the primary.

Autovacuum is *not* active during recovery, though will start normally if recovery ends. Bgwriter is active during recovery and will perform restartpoints (similar to checkpoints on primary) and normal block cleaning activities.

Please be reminded that hash indexes are unusable on hot standby, due to already existing and documented limitations for that index type.

=== Dynamic Control of Recovery ===

The functions shown in Table 9-56 assist in archive recovery. Except for the first three functions, these are restricted to superusers. All of these functions can only be executed during recovery.

Table 9-56. Recovery Control Functions
{| border="1"
|Name||Return Type||Description
|-
|pg_is_in_recovery||bool||True if recovery is still in progress.
|-
|pg_last_recovered_xact_timestamp() ||timestamp with time zone ||Returns the original completion timestamp with timezone of the last completed transaction in the current recovery.
|-
|pg_last_recovered_xid() ||integer ||Returns the transaction id (32-bit) of last completed transaction in the current recovery. Later numbered transaction ids may already have completed. This is unrelated to transactions on the source server.
|-
|pg_recovery_pause() ||void ||Pause recovery processing, unconditionally.
|-
|pg_recovery_continue() ||void ||If recovery is paused, continue processing.
|-
|pg_recovery_stop() ||void ||End recovery and begin normal processing.
|-
|pg_recovery_pause_xid(int) ||void ||Continue recovery until specified xid completes, if it is ever seen, then pause recovery.
|-
|pg_recovery_pause_time(timestamp)||void ||Continue recovery until a transaction with specified timestamp completes, if one is ever seen, then pause recovery.
|-
|pg_recovery_advance(int)||void ||Advance recovery specified number of records then pause.
|}

pg_recovery_pause and pg_recovery_continue allow a superuser to control the progress of recovery of the database server. This allows queries to be executed to determine how far recovery should progress. If the superuser wishes recovery to complete and normal processing mode to start, execute pg_recovery_stop.

Variations of the pause function exist, mainly to allow PITR to dynamically control where it should progress to. pg_recovery_pause_xid and pg_recovery_pause_time allow the specification of a trial recovery target. Recovery will then progress to that point, and then the database can be inspected to see if this is the correct stopping point.

pg_recovery_advance allows recovery to progress record by record, for very careful analysis or debugging. Step size can be 1 or more records. If recovery is not yet paused then pg_recovery_advance will process the specified number of records then pause. If recovery is already paused, recovery will continue for another N records before pausing again.

If you pause recovery while the server is waiting for a WAL file when operating in standby mode it will have apparently no effect until the file arrives. Once the server begins processing WAL records again it will notice the pause request and will act upon it. This is not a bug.

Once recovery is paused it will stay paused until you release it, even after the server falls further behind than max_standby_delay.

You can specify "recovery_starts_paused = true" in recovery.conf, though the default is not-paused.

== Diagnosing problems ==

First, check that your are connected to the correct server and that the server is in recovery. You can check this by looking at "ps" output and looking for a "startup process".

There are sometimes reasons why you cannot connect. Check the log for messages.

If you think you should be able to see an object or some data, check that the standby has processed all oustanding WAL. It may just be the standby is lagging behind.

Look at the current snapshot using "select txid_current_snapshot();".
Look at pg_locks and pg_stat_activity.

= Design Overview =

== Internals ==
Accuracy: Some changes since this was written, refer to main patch for current details

=== What already works ===

The most difficult part of running queries during recovery is creating a valid MVCC snapshot. The route we have chosen is to have WAL records emulate transaction entries in the procarray, thus allowing users to see entries for TransactionIds that have started writing WAL but not yet had their commit records applied.

The following items for prerequisites for emulating transactions during recovery.

* TransactionIds are densely allocated in increasing order. This means that there are no gaps and the assignment is monotonic. Read-only transactions do not create
TransactionIds so we can run read-only transactions without disrupting duplicating already used ids.

* Subtransaction commit does not write WAL, so it is important that it also does not write clog entries until commit. This then allows us to perform all the actions of subtransaction commit at the same time as we process the commit record.

As a result, we have the infrastructure on which to build to make Hot Standby work.

=== Transactions ===

In 8.3, recovery maintains the commit log (or clog) as transactions commit or abort. This alone is insufficient to allow user queries to have full visibility of MVCC data.

For Hot Standby we will maintain 3 data structures:
* ProcArray - records emulated "running" transactions
* Clog - required to identify visibility of transactions
* Subtrans - required to identify visibility of subtransactions

We do not need to maintain Multitrans during recovery. We will not be running HeapTupleSatisfiesVacuum() during recovery.

The events that maintain these structures are:
* new transaction - add a recovery proc with this top-level xid
* new subtransaction - new subxids are marked with top-level xid, so add new subxid onto recovery proc's subxidcache. We may also add entry to subtrans.
* commit/abort - remove recovery proc

To allow the above, each WAL record has both the xid and the top-level xid

We expect new xids to come in dense order, i.e. each new xid is 1 later than previous (allowing for wraparound). However, there is a race condition between xid assignment (XidGenLock) and xid use (WALInsertLock), so the xids can arrive with gaps in the sequence. We will generally see the first WAL records for those intermediate xids appearing shortly afterwards, but even so we must keep track of these as yet UnobservedXids.

=== Row Removal ===

Various type of block-level actions were performed on the primary with Super-Exclusive/BufferCleanupLocks held. This same level of lock must be held otherwise queries may attempt to re-access data blocks while they are being cleaned. We will need to change many redo actions so that they take BufferCleanup locks, using a new XLogReadBufferForCleanup(). Changes would be required to HEAP2 actions (freeze, clean and cleanmove) as well as to all index vacuuming actions.

We can't cancel a query that holds a pin on a particular block since we don't keep track of who holds a pin. We just know a backend has a pin and that the startup process must wait.

This may become a problem, or it may not. In most cases, backends hold 5-15 pins concurrently and pins are held for relatively short times. Startup process will provide debug information for when pin times are extended, and for monitoring total delay from pin waits.

Some strategies, if this becomes a problem:
* minimise pin hold times wherever this occurs
* deferring application of WAL for pinned blocks (requiring us to use conditional locking)
* making VACUUM FREEZE hold stronger locks on standby to prevent query access (but doesn't help HOT)
* ensuring that we perform read ahead I/O for WAL records that have not yet reached head of apply queue
* change some of the ways index AMs work with respect to row removal (see later)

Hot Standby may work best with full_page_writes = off.

In general, we must do one of these three things:
1) pass OldestXmin for read-only snapshots to the master
2) make WAL replay halt while queries complete
3) cancel queries that could see the data about to be removed

We will implement a combination of strategies 2 and 3, while allowing 1 to exist also. (It might be trivial to make (1) work using dblink).

Each WAL record that cleans tuples has a latestRemovedTransactionId on it. If latestRemovedTransactionId
is later than a running query on standby then we wait. When we stop waiting we tell all at-risk queries the LSN of the first WAL record that
has potentially removed tuples they can see. If they see a block with a higher LSN they cancel *themselves*.

This works OK, since SeqScans never read blocks at end of file that
didn't exist when they started, so long queries need not be cancelled
when they access growing tables.

The wait is controlled by a parameter called max_standby_delay. The standby delay is the time taken to complete all outstanding WAL activity. max_standby_delay is thus the additional time we think acceptable should we choose to perform failover from the primary to the standby. max_standby_delay = 0..1000000s with typical values being 0s for a very High Availability server, or 3600s for a typical reporting server.

When Startup waits, as described above, this causes the standby_delay for the whole server to increase. Probably more discussion required about how this would work.

Sounds like we need some a balancing system that has a target or optimal standby delay and an absolute max, so that if we are between optimal and max we try to return to optimal. Maybe this would be max_query_standby_delay and max_server_standby_delay. Individual queries can force the server to wait for up to max_query_standby_delay, though no wait occurs that would make the whole server get more behind than max_server_standby_delay. If max_query_standby_delay >= max_server_standby_delay the server delay takes precedence.

We would perform a planned switchover by first setting max_standby_delay=0 on standby, so it catches up. Then shutoff primary users, wait for all WAL to drain across to standby and let her start up.

We will maintain OldestXmin in shared memory to allow it to be accessed quickly.

Issues: Many different use cases here. Doubt we can provide for all of them in first release.

=== Transaction Snapshots ===

All queries will use MVCC Snapshot Isolation in either Read Committed or Serializable modes. These require snapshots, plus visibility information as described above.

Snapshots will be derived directly from shared memory on stanbdy, since data is maintained as transactions arrive/complete. We will also track latest_completed_xid.

=== Relcache ===

All read-only transactions will use and maintain a relcache, allowing them to plan and execute queries using a cached copy of the current database catalog tables. The relcache will be maintained on each backend normally, re-reading catalog tables when invalidation messages are received.

Invalidation messages will be sent by the Startup process, though the Startip process will not maintain its own copy of the relcache. Invalidation messages will be issued when new WAL messages are received. XLOG_RELCACHE_INVAL messages will be sent every time we make a change to a cached heap tuple within CacheInvalidateHeapTuple(). These always occur after a change and will include all required information to send a full invalidation message.

This means that Startup process will continue to use XLogReadBuffer(), minimising the changes required in current ResourceManager's _redo functions - there are still some, see later. It also means that read-only backends will use ReadBuffer() calls normally, so again, no changes required throughout the normal executor code.

=== Relation Locking ===

In recovery, transactions will not be permitted to take any lock higher than AccessShareLock nor assign a TransactionId. This should also prevent us from writing WAL, but we protect against that specifically as well, just in case. (Maybe we can relax that to Assert sometime later).
We can dirty data blocks but only to set hint bits. (That's another reason to differentiate between those two cases anyway).
Note that in recovery, we will always be allowed to set hint bits - no need to check for asynchronous commits.
All other actions which cause dirty data blocks should not be allowed, though this will be just an Assert. Specifically, HOT pruning will not be allowed in recovery mode.

Since read-only backends will only be allowed to take AccessShareLocks there will be no lock conflicts apart from with AccessExclusiveLocks. (If we allowed higher levels of lock we would then need to maintain Multitrans to examine lock details, which we would also rather avoid). So Startup process will not take, hold or release relation locks for any purpose, *apart* from when AccessExclusiveLocks are required.

The Startup process will emulate locking behaviour for transactions that require AccessExclusiveLocks (AELs). AELs will be held by first inserting a dummy TransactionLock entry into the lock table with the TransactionId of the transaction that requests the lock. Then the lock entry will be made. Locks will be released when processing a transaction commit, abort or shutdown checkpoint message, and the lock table entry for the transaction will be removed.

Locks will be taken by the Startup process when it receives a new WAL message. XLOG_LOCK_ACCESSEXCLUSIVE messages will be sent each time a backend *successfully* acquires an AccessExclusiveLock.

Any AEL request that conflicts with an existing lock will cause some action: if it conflicts with an existing AEL then an error is raised; if the AEL request conflicts with a read-only backend then we wait for a while (see Row Removal above) then the read-only backend will receive a cancel message to make it go away.

If Startup process crashes it is a PANIC anyway, so there is no difficulties in cleanup for the lock manager with this approach.

The LOCK TABLE command by default applies an AccessExclusiveLock. This will generate WAL messages when executed on the primary node. When executed on the standby node the default will be to issue an AccessShareLock. Any LOCK TABLE command that runs on the standby and requests a specific lock type will be rejected.

Issues: How will advisory locks work?

=== Index Locking ===

Recovery queries will also wish to use indexes, so all index structures are assumed to be uncorrupted and able to be used during recovery. (This is the same asumption we make immediately after recovery completes, but it is worth noting this explicitly).

An index type may not be usable during recovery if it's WAL _redo() function does not take and hold buffer locks in the same (or at least, an equally correct) manner during recovery. For example, btree indexes have strict rules on buffer locking that must be followed during block splits.

* btree - initial analysis shows locking is correct for btrees (other opinions welcome)
* GIST, GIN need some changes, but no problems foreseen (says Teodor)
* hash - doesn't generate WAL yet, so unusable during recovery
* bitmap - locking protocol works without needing cleanup locks, just exclusive (looking at patch)

Changes to index metapages must also generate cache invalidations by using XLOG_RELCACHE_INVAL messages in WAL. Metapage changes will require cleanup locks, though these are almost certain to be granted because of cacheing.

B-tree index tuples are removed by
* inserts when the page is full - require only an exclusive lock
* VACUUMs - require a cleanup lock
The same lock requirements apply to Hot Standby, so we will need to differentiate the two uses of XLOG_BTREE_DELETE so that we can re-apply the appropriate lock during btree_redo()

HOT imposes some tricky conditions as to when it is safe to use a new index. These conditions also apply to Hot Standby, so there is nothing we need do. (Note also that Hot Standby has got nothing to do with the HOT feature).

=== Summary of changes to WAL records ===

* New WAL record for successfully acquired AccessExclusiveLocks
* New WAL record for cache invalidation messages
* Two new fields on each XLogRecord - xid2, a TransactionId and info2, 2 bytes of flags/data

The last change uses up the 6 bytes that were being wasted on 64-bit servers, and adds 4 bytes on to the XLogRecord length for 32-bit servers.

I would like to use 3 of the flag bits for Hot Standby:

* flag1 to indicate the first WAL record for an xid. This tells us Hot
Standby to efficiently manage a recovery snapshot.

* flag2 to indicate the first WAL record for a subxid. That tells us to
maintain subtrans during Hot Standby.

* flag3 to indicate that this is a cleanup record. This tells us to use latestRemovedTransactionId to inform user queries of cleanup having taken place.

If flag2 is set then xid2 contains the xid of xl_xid's parent.
else if flag3 is set then xid2 contains the latestRemovedTransactionId

Note that flag2 and flag3 are mutually exclusive. cleanup records do not make transactional changes, they just cleanup what can no longer be seen. The first change made by a subtransaction is always on another WAL record from any cleanup associated with the block (HOT etc).

== Design Notes ==

Additional thinking behind the technical designs above, not necessarily fully coherent sets of thoughts.

=== Transaction Snapshots ===

Deriving transaction snapshots is probably the most difficult problem for Hot Standby to resolve. We *must* have a transaction snapshot to access table data in a consistent way. (See discussion later on Transaction Isolation).

We can derive transaction snapshots
* remotely from primary node
* locally on the standby node

If we derive a snapshot locally, then we will end up with a situation where the xmin of the local snapshot precedes the xmin of the primary node. When this occurs it will then be possible for WAL records to arrive on the standby that request removal of rows that a transaction might wish to see. Preventing that situation can be done by either deferring WAL apply or by cancelling queries.

We can defer WAL apply for particular tables only, but this could significantly complicate the apply process and is not a suggested option for the first release of this feature.

We might control query cancellation by tracking which tables have had rows removed that would have been visible to particular queries. Again, possible but suffers from the problem that tables on which HOT is frequently active would be almost unusable.

Simply ignoring WAL removal has been discussed and rejected (so far). http://archives.postgresql.org/pgsql-hackers/2008-05/msg00753.php
Explicitly defining the tables a transaction wishes to see has also been discussed and rejected (so far).
http://archives.postgresql.org/pgsql-hackers/2008-08/msg00268.php

So the most generally applicable and simplest initial solution is to take snapshots from the remote primary node. The proposal for this follows:

Transaction snapshots will be managed by a new process, Snapshot. Snapshot will start only in recovery mode and will exit when normal processing mode begins. Snapshot process will connect to the remote primary node and export snapshot data and copy this to shared memory on the standby node. When all standby backends have run UnregisterSnapshot() then the snapshot will then be unregistered on the remote primary node also.

The standby must not think a transaction is visible until all changes made by it are have been applied. So snapshots from the primary cannot be used on the standby until the LSN at which they were taken has been reached by WAL apply on the standby. Snapshots don't normally have an LSN, so we must derive that information as well when we take a Snapshot. With asynchronous commits a transaction can be committed and yet not yet written to disk, so we cannot rely on the current WAL write pointer. Taking the WAL insert pointer is the safest thing to do, though most heavily contended. We don't want to hold ProcArrayLock while requesting WALInsertLock, so we will discover the LSN of the WAL insert pointer *after* the Snapshot has been derived.

So snapshots from the primary take time to before they can be used. The delay is exactly the current processing delay from primary to standby, so another reason why we do not wish to fall behind.

Taking snapshots from primary has a few disadvantages
* snapshots take time before they are usable
* requesting new snapshots is via remote request
* snapshots on primary prevent row removal (but this was also an advantage of this technique!)

If primary and standby node are connected by private ethernet then the snapshot request time will be ~10ms, though that is probably 100 times slower than current snapshot access. If primary and standby are opposite sides of an ocean then times could be as high as 500ms. Taking snapshots directly could be annoyingly slow for small read-only statements, so we need to buffer this process in various ways. For larger queries, this may not be a problem at all, but we do not wish to limit or assume the uses of the standby node.

First of all, each backend will have the opportunity to reuse previous snapshots both within transactions and across them. A userset parameter snapshot_reuse_window = 0..60000ms will define the time window in which any request for a new snapshot will simply result in being fed the last snapshot again. When the window on a snapshot has expired a newer snapshot will be presented instead. This idea is similar to serializable transactions, which continually reuse the same snapshot. This is a useful parameter for normal processing as well, since it will reduce contention on the ProcArrayLock for many applications.

Snapshots can be reused across transactions in recovery mode, since they are held in shared memory. Amount of shared memory dedicated to storing snapshots will be max_connections * size of snapshots.

Since there is a delay between obtaining a new snapshot and it becoming usable the Snapshot process will buffer them until they become "mature", like a good Whiskey. Snapshot process will take regular snapshots and pre-age them so that when a backend requests a snapshot it will be given the most recently matured snapshot. Time between snapshots is set by snapshot_preread_timeout = 0..60000ms. If a request for a snapshot arrives and there are no snapshots waiting to mature then this will trigger snapshot process to request a new primary snapshot. (This parameter could be automatically set based upon the arrival rate of snapshot requests, but this is a something to consider later).

If snapshot_reuse_window = 0 then a backend will be presented with a freshly obtained snapshot and will then wait until the exact first moment it can be used before returning.

We can continue to reuse snapshots from the primary even if the primary crashes, becomes disconnected or is shutdown/restarted. New snapshots are obviously not possible until it appears again. It's not that common for us to lose contact with the primary *and* for it to *not* be a failover, so this seems like an acceptable restriction.

txid_current_snapshot() is sufficient for our needs. We only need to know about top-level xids. This means we will need a working subtrans data structure, which means we will need some way to update it. Proposal is for the first WAL record in any subtransaction to note the parentxid. WAL apply loop will update subtrans during recovery when a WAL record arrives with an augmented parentxid.

=== Performance ===

Query performance may slow down WAL apply if too many queries execute concurrently and use up CPU and I/O resources that would have been used by the Startup process. Queries that hold pins for long periods on tables with frequent updates will slow down WAL apply.

Performance is not expected to be impaired by these new WAL message types since they apply only to AccessExclusiveLocks, which are reasonably rare even in applications that issue many DDL requests.

== Correctness ==

=== Two Phase Commit ===

Will need to maintain locking for prepared transactions. So will need to read twophase state and re-create any AELs therein. No other actions foreseen.

=== Tuple Locking ===

Tuple level locks are WAL-logged, so WAL replay will make the changes on blocks to emulate locking. There is no required access to lock manager.

From correctness perspective, no changes to tuples that are locked should appear in WAL, so we ignore the possibility.

No changes required here. No users can wait on locks, since they all use AccessShareLocks. So they just ignore tuple locks.

=== Data structure Check ===

* Heap - discussed above
* Index - discussed above
* FSM - checked with Heikki and new FSM causes no issues for Hot Standby
* twophase - just files, so structure is OK
* pg_log - writable
* pg_xlog - not writable by WAL apply or users
* clog - data structure valid and usable
* subtrans - data structure is non transactional
* multixact - is not required

== Acknowledgements ==

Florian Pflug's contribution to making Hot Standby was an important one. The single hardest problem in 2007 was how we would get access to a TransactionId. Florian's careful analysis resulted in the idea that is now critical to the rest of this project: VirtualTransactionIds. In addition, various internal changes made by a range of developers for 8.3 have made this significantly easier than it was previously.

Florian also identified the problems of relcache handling and block cleanup locks. Unfortunately, none of Florian's earlier code can/has been reused.

This design includes technical feedback from many PostgreSQL hackers, all of whom I thank and acknowledge.

== Project Plans ==

Priorities:
*1 Correctness
*2 Performance

Phases

* Design (2008!)

* Connection during recovery (mid-July)

* Infrastructure changes (1 Sept)

* Queries work when no WAL changes arrive (snapshots)

* Queries work while WAL changes arrive (block locking)

* Queries work while DDL changes arrive (locking, relcache)

* Performance

* Queries work with two-phase commit

== Features not planned for this release ==
(Or at least not by us in this release.)

* Automatic offloading - run a query on primary node and have it offload processing of larger queries onto standby nodes.

* Different mechanisms for deriving standby snapshots

* LISTEN, NOTIFY - should be possible when this happens via shared memory rather than pg_listener

Hot Standby

2009-05-17T16:03:11Z

Robe: correcting targets

Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.

Hot Standby is being developed for inclusion in PostgreSQL 8.5 by 2ndQuadrant via public sponsorship.

This feature will augment the capabilities of streaming replication that have also been discussed for inclusion in r8.4, though does not rely on it and has minimal interaction with it.

Changes will appear on this page, as and when design changes or clarifications are required. Please reply to posts on pgsql-hackers, or email simon@2ndquadrant.com

= Project Status Tracking =

== Version History ==

Latest version is always at top of this section

* [[:Image:hs.v9g.20090123_3.tar.bz2|hs.v9g.20090123_3.tar.bz2]] - v9g - bug fixes
* [[:Image:hs.v9d.20090122_4.tar.bz2|hs.v9d.20090122_4.tar.bz2]] - v9 - new conflict resolution and various refactorings
* [[:Image:hs.v8.20090114_1.tar.bz2|hs.v8.20090114_1.tar.bz2]] - v8 - bug fixes and knock-on effects of refactoring
* [[:Image:hs.v7.20090112_1.tar.bz2|hs.v7.20090112_1.tar.bz2]] - v7 - includes slotId removal refactoring
* [[:Image:hs.v6b.20090107_1.tar.bz2|hs.v6b.20090107_1.tar.bz2]] - v6b
* [[:Image:hs.v6a.20090103_1.tar.bz2|hs.v6a.20090103_1.tar.bz2]] - v6a new test cycle commencing (replaces v6)
* [[:Image:hs.v5.20081218_1.tar.bz2|hs.v5.20081218_1.tar.bz2]] - v5 corrected patch application to CVS HEAD, doc typos fixed
* [[:Image:hs.v5.20081217_2.tar|hs.v5.20081217_2.tar]] - v5 broken down into smaller patches for review

== Remaining Work Items for Nov 2008 CommitFest ==

* Latest commit that is being tested: 4fe66...

Reported problems (Identified by) (Target release)
* (none)

Infrastructure changes
* (none)

Main work/required additional items (Proposed by) (Target release)
* (none)

Performance tuning opportunities (Proposed by) (Target release)
* (none)

Usability enhancements (Proposed by)
* max_standby_delay set as a normal GUC, so it can be changed during normal execution (Simon)
* more accurately determine users to disconnect if we drop a tablespace that contains temp files currently in use
* more work possible on btree deletes to reduce impact
* input datatypes for functions (Simon)
* main docs required (put Wiki into SGML) (Simon)

Items deferred until next release
* make pg_start_backup() work on standby - synchronisation is already possible using pg_latest_recovered_lsn()
* connection from standby to master to prevent xmin horizon moving forwards on master
* buffer manager optimization targeted to reduce I/O for replay of btree vacuum when scanning uncleaned blocks (Simon)
* Canceled queries when idle in transaction cause client-side mismatch [slightly unrelated) (Simon) This only effects transactions that are idle-in-transaction AND either hold locks, are serializable or have open cursors. Current solution is upgrade to a FATAL error. The same problem occurs if we have an error while processing invalidation messages.

Tests needed
* Is there a performance regression on normal running from any overheads introduced? Patch v HEAD comparison
* Is there a performance regression on recovery? Patch v HEAD comparison (4% degradation on simple test at v4, need retest for later versions)
* Usability tests: How does query conflict handling work in practice? User experience needed

Code has been reviewed in various parts by Pavan, Heikki and Tom. Further detailed review/ers are still required.

== Resolved Items/Issues ==

Reported problems (Reported by/Requested by) (Resolved in version...) (Most recent at top)

=== Bugs present on Nov 1 ===

* minSafeStart point reset to earlier in some cases (Heikki) (Infra)
* signal postmaster to refresh password cache if auth file changes (Simon) (v9g)
* fix query cancel because of locks so it includes AbortOutOfAnyTransaction(); (Simon) (v9e)
* fix memory leak in GetRunningXactData() (Simon) (v9)
* conflict resolution for redo of database drop/move/rename while users are connected to that database (Simon) (v9)
* conflict resolution for redo of drop tablespace while users are using temp files in that tablespace - correct but needs work (Simon) (v9)
* Handle out of procs state gracefully, prevent snapshots and throw user query errors (Heikki) (v8d)
* AccessExclusiveLock must be held by subtransaction, not top-level transaction (Heikki/Simon) (v8)

* Block unlocking required during VACUUM scan (Heikki) (v6b)
* XLOG_BTREE_DELETE records handled correctly (Heikki) (v6)
* btree VACUUM code - must scan every block of index (Heikki) (v6)
* BEGIN TRANSACTION READ WRITE (Pavan) (v6)
* Must ignore_killed_tuples and never kill_prior_tuple during index scans in recovery (Greg) (v6) same fix, subtlely different scope (v8)
* uint unsupported on Windows (Jaime) (v6b)
* SIGINT cancels idle-in-transaction sessions (Pavan) (v6b)
* WALInsertLock still held at failover (Pavan) (v5)
* flat file updates for DDL incorrect (Mark) (v5)

=== Refactoring, bugs, tuning, usability tweaks ===

Functionality changes since Nov 1

* Complete Prepared Transaction support (Simon)
* fix bugs with recovery_starts_paused(), allow shutdown while paused (Simon)
* Compile error with WAL_DEBUG set (Bernd)
* removed reliance on pg_subtrans marking optimisation (Heikki/Simon)
* merged changes with Heikki's most recent review of recoveryinfra changes (Simon)
* tuning of RecordKnownAssignedTransactionIds() to avoid taking ProcArrayLock in most common case (Simon) (v9p)
* tuning of RecordKnownAssignedTransactionIds() using hash table (Heikki) (v9m)
* deferred buffer recovery conflict cache (Simon) (v9k)
* rename parameter used to turn on/off hot standby (Merlin) (v9i)
* Allow option to start recovery in paused mode (Kevin) (v9i)
* startup process waits, even when issuing deferred query cancellation (Heikki/Gianni) (v9i)
* Workaround: Canceled queries when idle in transaction cause FATAL errors because of client-side mismatch bug (Simon) (v9i)
* XidCacheRemoveXid (Simon) (v9i)
* rare assertion failure in pg_subtrans - caused by invalid assumption about when to mark subtrans (Gianni)
* snapshot bug (v9h) (Gianni & Mark)
* using wrong LSN in conflict handling causing more cancels than required (Gianni) (v9f)
* add parameter to disable standby query capability ("hot_standby") (Heikki) (v9e)
* fix race condition for SetBufferRecoveryConflictLSN(), recheck MyProc->xmin (Simon) (v9e)
* streamline commit/abort processing to bare minimum (Simon) (v9d)
* improved debug error messages for unobserved xids (Gianni) (v9c)
* missed some calls to RecordKnownAssignedTransactions() during last refactoring (Simon) (v9c)
* pg_recovery_advance() regression - fixed (Simon) (v9b)
* alter location of debug messages for AssignTransactionId (Simon) (v9a)
* remove docs for pg_recovery_pause_cleanup() (Simon) (v9)
* reduce use of ProcArrayLock to once per record, plus one at commit/abort (Simon) (v9)
* RecoveryConflictLSN, deferred cancellation of xmin horizon conflicts and skip cancellation if idle-in-xact (Andreas) (v9)
* make btree delete watertight (can't use RecentGlobalXmin) - correct but needs work (Heikki) (v9)
* must not do conflict processing when snapshots disabled - need to refactor some more, groan (Simon) (v9)
* rearrange call to UnobservedTransactionsPruneXids() to speed up calls to UnobservedTransactionsRemoveXid() (Simon) (v9)
* remove pg_recovery_pause_cleanup() which stopped working in v8e because of refactoring (Simon) (v9)
* downgrade "WARNING: proc array entry was missing for transaction" to DEBUG2 (Happens because of the race condition between XidGenLock and WALInsertLock. Occurred 4 times in 10 hour bash test on dual CPU server, but all cases have been normal and explainable since the xid was the latestRunningXid in all cases.) (Gianni) (v9)
* refactor rmgr "isCleanupRecord" code (Heikki) (v8e)
* remove databaseId, roleId and vacFlags from RunningXactData (Simon) (v8d)
* complete refactoring required change of ProcArrayUpdateRecoveryTransactions() (Heikki), plus fix one stupid and one very subtle bug while doing so (Simon) (v8c)
* not updating pg_auth when appropriate (Gianni) (v8b) -- trivial logic error
* optimize UnobservedTransactionsPruneXids() and fix corner case bug (Gianni) (v8b)
* split recovery procs into different pools, allowing error messages when overflowed (Simon) (v8a)
* refactor pre-allocation of Recovery Procs, need to trace dependency that prevents removal (Simon) (v8a)
* refactor XactCompletionHasUnmarkedSubxids() (Simon) (v8a)
* fix bug caused by earlier refactoring in ProcArrayClearRecoveryProcs() (Simon) (v8a)
* UnobservedXids leak warning - trivial fix (Gianni) (v8a)
* avoid starting autovac launcher for wraparound avoidance during recovery (Simon) (v8a)
* clarify meaning of last_recovered... functions during/after recovery (Simon) (v8)
* add function last_recovered_xlog_location() to allow synchronisation between master and standby based on an LSN (Simon) (v8)
* Document that index locking is correct and why (Heikki) (v8)
* add README comments to access/transam/README (Simon) (v8)
* document need to set max_connections correctly on standby (Simon) (v8)
* refactor/remove special case logic in GetRunningTransactionData() (Heikki) (v8)
* pg_subtrans could not open file (Heikki) (v8)
* added pg_current_recovery_target() (Gianni) (v8)
* allow startup process to delay at start to allow debugger attach where pg_ctl -o "-W n" sets a delay of n secs (Simon) (v8)
* adjust all XXX comments related to Hot Standby to be XXXHS (Simon) (v8)
* renamed last_completed... functions to last_recovered... (Simon) (v8)
* slotid refactoring, comments changed (Heikki) (v7)
* Cannot Assert(pmState == PM_STARTUP) when postmaster catches PMSIGNAL_RECOVERY_START (regression from earlier report) (v6b)
* Use of #define may mask bugs in 1-2 locations in procarray.c (Pavan) (v6b)

Do we need/want? (Current answer)
* recovery_safe_start_location; I think we do (Not implemented)
* to automatically reset default_transaction_read_only following startup (No)
* Creating temp tables in recovery mode. Not sure if that is spec compliant? We may want to relax that restriction. If we do, we already have a design in place for changes to make this work: http://archives.postgresql.org/pgsql-hackers/2008-07/msg00616.php. (Not in 8,4)

Resolved Issues/Questions
* No need to send invalidations when index metapages change - already handled at xact commit
* Added further user/admin documentation via this Wiki

Excluded items
* hash index handling (or any index type that doesn't write WAL)
* Cancelled queries to show errcode(ERRCODE_T_R_SERIALIZATION_FAILURE) (cannot do, unable to identify source of cancellation)

= Usage =

== Users Overview ==

Users will be able to connect to the database while the postmaster is in recovery state and perform read-only queries. Read-only access to catalogs and views will also occur as normal.

When you connect in this mode, default_transaction_read_only will be forced to be true, whatever its setting in postgresql.conf. As a result, all transactions started during this time will be limited to read-only actions only. In all other ways, connected sessions will appear identical to sessions initiated during normal processing mode. There are no special commands required to initiate a connection at this time, so all interfaces will work normally without change.

Read-only here means "no writes to the permanent database tables". So temporary sort and work files will be used, just as with normal SQL execution. Temporary tables cannot be created and therefore cannot be used at all in recovery mode.

Actions that are allowed
* Transaction management commands: BEGIN, END, ABORT, START TRANSACTION
* SELECT, COPY TO
* Cursor commands: DECLARE, FETCH, CLOSE,
* Parameters: SHOW, SET, RESET, DISCARD
* LOAD
* Plans: PREPARE, EXECUTE, DEALLOCATE
* LOCK (see later)
* SAVEPOINT commands: SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
* EXCEPTION blocks in internal subtransactions
* Activation of Select RULEs

Actions not allowed on Standby are:
* Transaction management commands which explicitly set non-read only state: SET TRANSACTION, SET SESSION CHARACTERISTICS, BEGIN, START TRANSACTION, direct SET
* Two-phase commit commands (PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED) because even read-only transactions need to write WAL in the prepare (first phase of two phase commit).
* SELECT ... FOR SHARE | UPDATE which cause row locks to be written
* DML - Insert, Update, Delete, COPY FROM, Truncate which all write data
* DDL - Create, Drop, Alter, Comment (even for temporary tables because currently these cause writes to catalog tables)
* Access to nextval on sequences because nextval causes the value of the sequence to be increment
* LISTEN, UNLISTEN, NOTIFY since they currently write to system tables
* Remember: Triggers will never be executed!
All of these are allowed on primary, just not on standby in read-only mode. When failover, switchover occurs these commands will then be possible.

It is possible that the restrictions on LISTEN, UNLISTEN, NOTIFY and temporary tables may be lifted in a future release, if their internal implementation allows it.

Note that current behaviour of read only transactions is to allow these
* nextval on sequences
* LISTEN, UNLISTEN, NOTIFY
So there is a subtle difference in behaviour between standby read-only transactions and non-recovery read only transactions.

If failover or switchover occurs the database will switch to normal processing mode. Sessions will remain connected while the server changes mode. Current transactions will continue, though will remain read-only. After this, it will be possible to initiate read-write transactions, though users must *manually* reset their default_transaction_read_only setting first.

Users will be able to tell whether their session is read-only by issuing SHOW default_transaction_read_only, or by running an information function pg_is_in_recovery() returns bool. There is also be a function that allows you to read the last known transaction end timestamp (pg_last_completed_xact_timestamp) and the transactionid (pg_last_completed_xid).

In recovery, transactions will not be permitted to take any lock higher than AccessShareLock nor assign a TransactionId. This mostly prevents writing WAL, but we protect against that specifically as well, just in case some user code attempts it.

The LOCK TABLE command by default applies an AccessExclusiveLock. This will generate WAL messages when executed on the primary node. When executed on the standby node the default will be to issue an AccessShareLock. Any LOCK TABLE command that runs on the standby and requests a specific lock type other than AccessShareLock will be rejected.

During recovery database writes will only be performed by startup process as it processes WAL records and replays them (also known as WAL redo or simply "apply"). In general this means that queries will not experience lock conflicts with writes, just like normal Postgres concurrency control (MVCC).

=== Query Conflicts ===

However, there is some potential for conflict between standby queries and WAL redo from the primary node. The user is provided with a number of optional ways to handle these conflicts.

Conflicts can occur for four reasons
* Access Exclusive Locks from primary node (including both locks and various kinds of DDL action)
* Early cleanup of data still visible to the current query's snapshot
* Dropping tablespaces on the master while standby queries are using those tablespace for temporary work files (work_mem overflow)
* Dropping users, databases or tablespaces on the master while those objects are connected on standby
* Waiting to acquire buffer cleanup locks (for which there is no time out)
discussed in more detail below.

Some WAL redo actions will be for DDL actions. These DDL actions are repeating actions that have already committed on the primary node, so they must not fail on the standby node. These DDL locks take priority and will automatically *cancel* any read-only transactions that get in their way, after a grace period. This is similar to the possibility of being canceled by the deadlock detector, but in this case the standby process always wins, since the repeated actions must not fail. This also ensures that replication doesn't fall behind while we wait for a query to complete. Again, we assume that the standby is there for high availability purposes primarily.

An example of the above would be:
* User on Standby running a query against table X
* Administrator on Primary runs DROP TABLE
Clearly the query cannot continue if we let the DROP TABLE proceed. If this situation occurred on the primary, the DROP TABLE would wait until the query has finished. When the query is on the standby and the DROP TABLE is on the primary, the primary doesn't have information about what the standby is running and so cannot wait. In most cases this is desirable, so the workload on the standby has little or no effect on the primary and so we would say the two nodes are only loosely coupled.

The second reason for conflict between standby queries and WAL redo is "early cleanup". Normally, PostgreSQL allows cleanup of old row versions when there are no users who may need to see them to ensure correct visibility of data (known as MVCC). If there is a standby query that has been running for longer than any query on the primary then it is possible for old row versions to be removed by either VACUUM or HOT. This will then generate WAL records that, if applied, would remove data on the standby that might *potentially* be required by the standby query. In more technical language, the Primary's xmin hozrizon is later than the Standby's xmin horizon, allowing dead rows to be removed.

We have a number of choices for resolving query conflicts. Choice (1) is automatic, options (2) and (3) should be taken as remedial actions if an annoying number of cancelations occur.

1. We wait and hope the query completes. If the recovery is not paused, then we will wait automatically until the server is max_standby_delay behind. Once that grace period expires, we then take one of the following actions:

a) If the conflict is caused by a lock, we cancel the standby transaction immediately, even if it is idle-in-transaction.

b) If the conflict is not caused by a lock, we tell the standby query that a conflict has occurred and that it must cancel itself if there is a risk that it attempts to read data that has been cleaned up already. Specifically, if the standby query reads a data block that has been recently modified it will cancel itself. Note it only needs one modified data block to cause a cancel and that the data block does *not* need to have been modified by a cleanup record - any change is sufficient to create this risk. (This is very similar to the much feared Oracle error message "snapshot too old"). Note also that this means that idle-in-transaction sessions are never canceled except by locks. Users should be clear that tables that are regularly and heavily updated on primary server will quickly cause cancellation of any longer running queries made against those tables.

If cancellation does occur, the query and/or transaction is repeatable - i.e. the error is dynamic and will not necessarily occur the same way if the query is executed again.

2. We connect to Primary server from Standby server and keep a transaction open. This guarantees that a cleanup record is never generated and we don't ever get into situation 1(b). This option can be performed fairly simply using contrib/dblink functions.

3. We pause recovery by issuing a pg_recovery_pause() function. This halts application of WAL records completely and allows queries to proceed to completion without problem. We can issue pg_recovery_continue() at any time, so the pause can be held for long or short periods, as the administrator allows. This method of conflict resolution may mean that there is a build up of WAL records waiting to be applied and this will progressively increase the failover delay. If there is regular arrival of WAL records this would quickly prevent the use of the standby as a high availability failover target. Some users may wish to use multiple standby servers for various purposes. Pauses in recovery stay until explicitly released, i.e. max_standby_delay is overridden by the use of pauses.

An example of the above would be:
* User on Standby running a long query involving tables X and Y
* User on Primary repeatedly updates table Z
The user on the primary will be generating HOT updates on table Z and these will generate cleanup WAL records. In this case the rows cleaned up are very recent and on the standby node we are able to detect that removing those row versions could *potentially* prevent the Standby query from executing correctly and so there is a conflict. Initially, we will wait for max_standby_delay, but then afterwards we will give the standby query notice to quit. In this case, the standby query never does read data that has been cleaned, since it is looking at different tables, so the standby query runs to completion normally.

We keep track of the first 8 relations (that is, indexes and/or tables) that cause conflicts. When we have conflicts with more than 8 tables we apply the conflict handling to all relations. So in the above example if we had already recorded conflicts with eight other tables (plus Z means 9 tables, more than our cache) then we *would* cancel the query against tables X and Y, even though those tables have not been touched. Conflict processing increases quickly with number of tables in the conflict cache, so raising the number above 8 is not practical.

Note that max_standby_delay is set in recovery.conf. It applies to the server as a whole, so once used it may not be available for other users. They will have to wait for the server to catch up again before the grace period is available again. So max_standby_delay is a configuration parameter set by the administrator which controls the maximum acceptable failover delay, rather than a user parameter to specify how long their query needs to run in.

Waits for buffer cleanup locks do not currently result in query cancellation. Long waits are uncommon, though can happen in some cases with long running nested loop joins.

These two items are discussed in the administrator's section since they are not typical user situations
* Dropping tablespaces on the master while standby queries are using those tablespace for temporary work files (work_mem overflow)
* Dropping users, databases or tablespaces on the master while those objects are connected on standby

== Administrators Overview ==

Hot Standby is enabled by default, though can be disabled by setting "recovery_connections = off" in recovery.conf.

A second parameter, "max_standby_delay" should also be set by the administrator in recovery.conf. The default is 30 seconds. This should be set according to business priorities. For example if the server is primarily tasked as a High Availability server, then you may wish to lower max_standby_delay or even set it to zero. If the standby server is tasked as an additional server for decision support then it may be acceptable to set this to a value of many hours, e.g. max_standby_delay = 43200 (12 hours). It is also possible to set max_standby_delay to -1 which means "always wait" if there are conflicts, though if unmonitored this may not be a useful mode.

It is strongly recommended that the setting of max_connections on the standby should be equal to or greater than the setting of max_connections on the primary. This is to ensure that standby has sufficient resources to manage incoming transactions. If the standby is asked to manage too many concurrent *write* transactions it will run out of recovery session resources. Read only transactions have no effect on the standby. If the standby runs out of recovery session resources then recovery will continue normally, but it will prevent MVCC snapshots from being taken and all standby queries will fail with an ERROR. Snapshots will be re-enabled after the next checkpoint on the primary for which the number of write transactions has fallen back below the cutoff.

Users will be able to write large sort temp files and re-generate relcache info files, so there is no part of the database that is truly read-only during hot standby mode. There is no restriction on use of set returning functions, or other users of tuplestore/tuplesort code.

Statspack functions should work OK, so tools such as pgAdminIII should work. pgAgent will not, though the next version will allow execution of jobs on a read only node.

Failover can be initiated at any time by allowing the startup process to reach the end of WAL, or by issuing the function pg_recovery_stop() as superuser. (see next section).

Stats collector is active during recovery. All scans, reads, blocks, index usage etc will all be recorded normally on the standby. Replayed actions will not duplicate their effects on primary, so replaying an insert will not increment the Inserts column of pg_stat_user_tables. The stats file is deleted at start of recovery, so stats from master and standby will differ.

pg_cancel_backend() will work on user backends, but not Startup process. pg_locks will show locks held by backends and by startup process. pg_stat_activity will not show an entry for startup process.

check_pgsql will work, but its very simple. check_postgres will also work, though many some actions could give
different or confusing results. e.g. last vacuum time will not be
maintained for example, since no vacuum occurs on the standby.

Dynamically loadable modules work, including the new pg_stat_statements.

Advisory locks work normally in recovery, including deadlock detection. No other form of deadlock is possible in recovery because the only only lock type that can be taken on database objects is AccessShareLock. Please note that advisory locks are never WAL logged, so it is not possible for an advisory lock to conflict with WAL replay.

slony won't run on the standby either! The Standby is a Clone and not a Slave. A Slave is a separate database that is forced to duplicate the actions of the Master. The Standby is an exact copy, in every detail that matters.

New oids cannot be assigned, though various UUID generators may still work if required.

Currently, creating temp tables is not allowed during read only transactions, so in some cases existing scripts will not run correctly. It is possible we may relax that restriction in a later release. This is both a SQL Standard compliance issue and a technical issue, so will not be resolved in this release.

Running DROP TABLESPACE on master has no effect on users on standby, though can only succeed if the tablespace is empty. Some standby users may be actively using the tablespace via their temp_tablespaces parameter. If so, those users will be have their current queries cancelled, so that they remove their temp files and allow WAL replay to proceed.

Running DROP DATABASE, ALTER DATABASE SET TABLESPACE, or ALTER DATABASE RENAME on master will cause all users connected to that database on the standby to be forcibly disconnected, once max_standby_delay has been reached. Similarly, running DROP USER or DROP ROLE for a role with login capability will cause all users connected using that role to be forcibly disconnected, once max_standby_delay has been reached.

WAL file control commands will not work during recovery e.g. pg_start_backup(), pg_switch_xlog() etc..

The following administrator commands will not be accepted during recovery mode
* ANALYZE
* VACUUM
* CLUSTER
* REINDEX
* CHECKPOINT
* GRANT, REVOKE, REASSIGN
* DDL - e.g. CREATE INDEX
Note again that some of these commands are actually allowed during "read only" mode transactions on the master.

So you cannot create additional indexes on the standby node, nor can you collect statistics via ANALYZE in new/different ways. (EXPLAIN ANALYZE still works, it is a different command).

If you wish to perform maintenance on the standby, run commands on the primary and the standby will then also mimic those changes. e.g. if you need to ANALYZE a table, do so on the primary.

Autovacuum is *not* active during recovery, though will start normally if recovery ends. Bgwriter is active during recovery and will perform restartpoints (similar to checkpoints on primary) and normal block cleaning activities.

Please be reminded that hash indexes are unusable on hot standby, due to already existing and documented limitations for that index type.

=== Dynamic Control of Recovery ===

The functions shown in Table 9-56 assist in archive recovery. Except for the first three functions, these are restricted to superusers. All of these functions can only be executed during recovery.

Table 9-56. Recovery Control Functions
{| border="1"
|Name||Return Type||Description
|-
|pg_is_in_recovery||bool||True if recovery is still in progress.
|-
|pg_last_recovered_xact_timestamp() ||timestamp with time zone ||Returns the original completion timestamp with timezone of the last completed transaction in the current recovery.
|-
|pg_last_recovered_xid() ||integer ||Returns the transaction id (32-bit) of last completed transaction in the current recovery. Later numbered transaction ids may already have completed. This is unrelated to transactions on the source server.
|-
|pg_recovery_pause() ||void ||Pause recovery processing, unconditionally.
|-
|pg_recovery_continue() ||void ||If recovery is paused, continue processing.
|-
|pg_recovery_stop() ||void ||End recovery and begin normal processing.
|-
|pg_recovery_pause_xid(int) ||void ||Continue recovery until specified xid completes, if it is ever seen, then pause recovery.
|-
|pg_recovery_pause_time(timestamp)||void ||Continue recovery until a transaction with specified timestamp completes, if one is ever seen, then pause recovery.
|-
|pg_recovery_advance(int)||void ||Advance recovery specified number of records then pause.
|}

pg_recovery_pause and pg_recovery_continue allow a superuser to control the progress of recovery of the database server. This allows queries to be executed to determine how far recovery should progress. If the superuser wishes recovery to complete and normal processing mode to start, execute pg_recovery_stop.

Variations of the pause function exist, mainly to allow PITR to dynamically control where it should progress to. pg_recovery_pause_xid and pg_recovery_pause_time allow the specification of a trial recovery target. Recovery will then progress to that point, and then the database can be inspected to see if this is the correct stopping point.

pg_recovery_advance allows recovery to progress record by record, for very careful analysis or debugging. Step size can be 1 or more records. If recovery is not yet paused then pg_recovery_advance will process the specified number of records then pause. If recovery is already paused, recovery will continue for another N records before pausing again.

If you pause recovery while the server is waiting for a WAL file when operating in standby mode it will have apparently no effect until the file arrives. Once the server begins processing WAL records again it will notice the pause request and will act upon it. This is not a bug.

Once recovery is paused it will stay paused until you release it, even after the server falls further behind than max_standby_delay.

You can specify "recovery_starts_paused = true" in recovery.conf, though the default is not-paused.

== Diagnosing problems ==

First, check that your are connected to the correct server and that the server is in recovery. You can check this by looking at "ps" output and looking for a "startup process".

There are sometimes reasons why you cannot connect. Check the log for messages.

If you think you should be able to see an object or some data, check that the standby has processed all oustanding WAL. It may just be the standby is lagging behind.

Look at the current snapshot using "select txid_current_snapshot();".
Look at pg_locks and pg_stat_activity.

= Design Overview =

== Internals ==
Accuracy: Some changes since this was written, refer to main patch for current details

=== What already works ===

The most difficult part of running queries during recovery is creating a valid MVCC snapshot. The route we have chosen is to have WAL records emulate transaction entries in the procarray, thus allowing users to see entries for TransactionIds that have started writing WAL but not yet had their commit records applied.

The following items for prerequisites for emulating transactions during recovery.

* TransactionIds are densely allocated in increasing order. This means that there are no gaps and the assignment is monotonic. Read-only transactions do not create
TransactionIds so we can run read-only transactions without disrupting duplicating already used ids.

* Subtransaction commit does not write WAL, so it is important that it also does not write clog entries until commit. This then allows us to perform all the actions of subtransaction commit at the same time as we process the commit record.

As a result, we have the infrastructure on which to build to make Hot Standby work.

=== Transactions ===

In 8.3, recovery maintains the commit log (or clog) as transactions commit or abort. This alone is insufficient to allow user queries to have full visibility of MVCC data.

For Hot Standby we will maintain 3 data structures:
* ProcArray - records emulated "running" transactions
* Clog - required to identify visibility of transactions
* Subtrans - required to identify visibility of subtransactions

We do not need to maintain Multitrans during recovery. We will not be running HeapTupleSatisfiesVacuum() during recovery.

The events that maintain these structures are:
* new transaction - add a recovery proc with this top-level xid
* new subtransaction - new subxids are marked with top-level xid, so add new subxid onto recovery proc's subxidcache. We may also add entry to subtrans.
* commit/abort - remove recovery proc

To allow the above, each WAL record has both the xid and the top-level xid

We expect new xids to come in dense order, i.e. each new xid is 1 later than previous (allowing for wraparound). However, there is a race condition between xid assignment (XidGenLock) and xid use (WALInsertLock), so the xids can arrive with gaps in the sequence. We will generally see the first WAL records for those intermediate xids appearing shortly afterwards, but even so we must keep track of these as yet UnobservedXids.

=== Row Removal ===

Various type of block-level actions were performed on the primary with Super-Exclusive/BufferCleanupLocks held. This same level of lock must be held otherwise queries may attempt to re-access data blocks while they are being cleaned. We will need to change many redo actions so that they take BufferCleanup locks, using a new XLogReadBufferForCleanup(). Changes would be required to HEAP2 actions (freeze, clean and cleanmove) as well as to all index vacuuming actions.

We can't cancel a query that holds a pin on a particular block since we don't keep track of who holds a pin. We just know a backend has a pin and that the startup process must wait.

This may become a problem, or it may not. In most cases, backends hold 5-15 pins concurrently and pins are held for relatively short times. Startup process will provide debug information for when pin times are extended, and for monitoring total delay from pin waits.

Some strategies, if this becomes a problem:
* minimise pin hold times wherever this occurs
* deferring application of WAL for pinned blocks (requiring us to use conditional locking)
* making VACUUM FREEZE hold stronger locks on standby to prevent query access (but doesn't help HOT)
* ensuring that we perform read ahead I/O for WAL records that have not yet reached head of apply queue
* change some of the ways index AMs work with respect to row removal (see later)

Hot Standby may work best with full_page_writes = off.

In general, we must do one of these three things:
1) pass OldestXmin for read-only snapshots to the master
2) make WAL replay halt while queries complete
3) cancel queries that could see the data about to be removed

We will implement a combination of strategies 2 and 3, while allowing 1 to exist also. (It might be trivial to make (1) work using dblink).

Each WAL record that cleans tuples has a latestRemovedTransactionId on it. If latestRemovedTransactionId
is later than a running query on standby then we wait. When we stop waiting we tell all at-risk queries the LSN of the first WAL record that
has potentially removed tuples they can see. If they see a block with a higher LSN they cancel *themselves*.

This works OK, since SeqScans never read blocks at end of file that
didn't exist when they started, so long queries need not be cancelled
when they access growing tables.

The wait is controlled by a parameter called max_standby_delay. The standby delay is the time taken to complete all outstanding WAL activity. max_standby_delay is thus the additional time we think acceptable should we choose to perform failover from the primary to the standby. max_standby_delay = 0..1000000s with typical values being 0s for a very High Availability server, or 3600s for a typical reporting server.

When Startup waits, as described above, this causes the standby_delay for the whole server to increase. Probably more discussion required about how this would work.

Sounds like we need some a balancing system that has a target or optimal standby delay and an absolute max, so that if we are between optimal and max we try to return to optimal. Maybe this would be max_query_standby_delay and max_server_standby_delay. Individual queries can force the server to wait for up to max_query_standby_delay, though no wait occurs that would make the whole server get more behind than max_server_standby_delay. If max_query_standby_delay >= max_server_standby_delay the server delay takes precedence.

We would perform a planned switchover by first setting max_standby_delay=0 on standby, so it catches up. Then shutoff primary users, wait for all WAL to drain across to standby and let her start up.

We will maintain OldestXmin in shared memory to allow it to be accessed quickly.

Issues: Many different use cases here. Doubt we can provide for all of them in first release.

=== Transaction Snapshots ===

All queries will use MVCC Snapshot Isolation in either Read Committed or Serializable modes. These require snapshots, plus visibility information as described above.

Snapshots will be derived directly from shared memory on stanbdy, since data is maintained as transactions arrive/complete. We will also track latest_completed_xid.

=== Relcache ===

All read-only transactions will use and maintain a relcache, allowing them to plan and execute queries using a cached copy of the current database catalog tables. The relcache will be maintained on each backend normally, re-reading catalog tables when invalidation messages are received.

Invalidation messages will be sent by the Startup process, though the Startip process will not maintain its own copy of the relcache. Invalidation messages will be issued when new WAL messages are received. XLOG_RELCACHE_INVAL messages will be sent every time we make a change to a cached heap tuple within CacheInvalidateHeapTuple(). These always occur after a change and will include all required information to send a full invalidation message.

This means that Startup process will continue to use XLogReadBuffer(), minimising the changes required in current ResourceManager's _redo functions - there are still some, see later. It also means that read-only backends will use ReadBuffer() calls normally, so again, no changes required throughout the normal executor code.

=== Relation Locking ===

In recovery, transactions will not be permitted to take any lock higher than AccessShareLock nor assign a TransactionId. This should also prevent us from writing WAL, but we protect against that specifically as well, just in case. (Maybe we can relax that to Assert sometime later).
We can dirty data blocks but only to set hint bits. (That's another reason to differentiate between those two cases anyway).
Note that in recovery, we will always be allowed to set hint bits - no need to check for asynchronous commits.
All other actions which cause dirty data blocks should not be allowed, though this will be just an Assert. Specifically, HOT pruning will not be allowed in recovery mode.

Since read-only backends will only be allowed to take AccessShareLocks there will be no lock conflicts apart from with AccessExclusiveLocks. (If we allowed higher levels of lock we would then need to maintain Multitrans to examine lock details, which we would also rather avoid). So Startup process will not take, hold or release relation locks for any purpose, *apart* from when AccessExclusiveLocks are required.

The Startup process will emulate locking behaviour for transactions that require AccessExclusiveLocks (AELs). AELs will be held by first inserting a dummy TransactionLock entry into the lock table with the TransactionId of the transaction that requests the lock. Then the lock entry will be made. Locks will be released when processing a transaction commit, abort or shutdown checkpoint message, and the lock table entry for the transaction will be removed.

Locks will be taken by the Startup process when it receives a new WAL message. XLOG_LOCK_ACCESSEXCLUSIVE messages will be sent each time a backend *successfully* acquires an AccessExclusiveLock.

Any AEL request that conflicts with an existing lock will cause some action: if it conflicts with an existing AEL then an error is raised; if the AEL request conflicts with a read-only backend then we wait for a while (see Row Removal above) then the read-only backend will receive a cancel message to make it go away.

If Startup process crashes it is a PANIC anyway, so there is no difficulties in cleanup for the lock manager with this approach.

The LOCK TABLE command by default applies an AccessExclusiveLock. This will generate WAL messages when executed on the primary node. When executed on the standby node the default will be to issue an AccessShareLock. Any LOCK TABLE command that runs on the standby and requests a specific lock type will be rejected.

Issues: How will advisory locks work?

=== Index Locking ===

Recovery queries will also wish to use indexes, so all index structures are assumed to be uncorrupted and able to be used during recovery. (This is the same asumption we make immediately after recovery completes, but it is worth noting this explicitly).

An index type may not be usable during recovery if it's WAL _redo() function does not take and hold buffer locks in the same (or at least, an equally correct) manner during recovery. For example, btree indexes have strict rules on buffer locking that must be followed during block splits.

* btree - initial analysis shows locking is correct for btrees (other opinions welcome)
* GIST, GIN need some changes, but no problems foreseen (says Teodor)
* hash - doesn't generate WAL yet, so unusable during recovery
* bitmap - locking protocol works without needing cleanup locks, just exclusive (looking at patch)

Changes to index metapages must also generate cache invalidations by using XLOG_RELCACHE_INVAL messages in WAL. Metapage changes will require cleanup locks, though these are almost certain to be granted because of cacheing.

B-tree index tuples are removed by
* inserts when the page is full - require only an exclusive lock
* VACUUMs - require a cleanup lock
The same lock requirements apply to Hot Standby, so we will need to differentiate the two uses of XLOG_BTREE_DELETE so that we can re-apply the appropriate lock during btree_redo()

HOT imposes some tricky conditions as to when it is safe to use a new index. These conditions also apply to Hot Standby, so there is nothing we need do. (Note also that Hot Standby has got nothing to do with the HOT feature).

=== Summary of changes to WAL records ===

* New WAL record for successfully acquired AccessExclusiveLocks
* New WAL record for cache invalidation messages
* Two new fields on each XLogRecord - xid2, a TransactionId and info2, 2 bytes of flags/data

The last change uses up the 6 bytes that were being wasted on 64-bit servers, and adds 4 bytes on to the XLogRecord length for 32-bit servers.

I would like to use 3 of the flag bits for Hot Standby:

* flag1 to indicate the first WAL record for an xid. This tells us Hot
Standby to efficiently manage a recovery snapshot.

* flag2 to indicate the first WAL record for a subxid. That tells us to
maintain subtrans during Hot Standby.

* flag3 to indicate that this is a cleanup record. This tells us to use latestRemovedTransactionId to inform user queries of cleanup having taken place.

If flag2 is set then xid2 contains the xid of xl_xid's parent.
else if flag3 is set then xid2 contains the latestRemovedTransactionId

Note that flag2 and flag3 are mutually exclusive. cleanup records do not make transactional changes, they just cleanup what can no longer be seen. The first change made by a subtransaction is always on another WAL record from any cleanup associated with the block (HOT etc).

== Design Notes ==

Additional thinking behind the technical designs above, not necessarily fully coherent sets of thoughts.

=== Transaction Snapshots ===

Deriving transaction snapshots is probably the most difficult problem for Hot Standby to resolve. We *must* have a transaction snapshot to access table data in a consistent way. (See discussion later on Transaction Isolation).

We can derive transaction snapshots
* remotely from primary node
* locally on the standby node

If we derive a snapshot locally, then we will end up with a situation where the xmin of the local snapshot precedes the xmin of the primary node. When this occurs it will then be possible for WAL records to arrive on the standby that request removal of rows that a transaction might wish to see. Preventing that situation can be done by either deferring WAL apply or by cancelling queries.

We can defer WAL apply for particular tables only, but this could significantly complicate the apply process and is not a suggested option for the first release of this feature.

We might control query cancellation by tracking which tables have had rows removed that would have been visible to particular queries. Again, possible but suffers from the problem that tables on which HOT is frequently active would be almost unusable.

Simply ignoring WAL removal has been discussed and rejected (so far). http://archives.postgresql.org/pgsql-hackers/2008-05/msg00753.php
Explicitly defining the tables a transaction wishes to see has also been discussed and rejected (so far).
http://archives.postgresql.org/pgsql-hackers/2008-08/msg00268.php

So the most generally applicable and simplest initial solution is to take snapshots from the remote primary node. The proposal for this follows:

Transaction snapshots will be managed by a new process, Snapshot. Snapshot will start only in recovery mode and will exit when normal processing mode begins. Snapshot process will connect to the remote primary node and export snapshot data and copy this to shared memory on the standby node. When all standby backends have run UnregisterSnapshot() then the snapshot will then be unregistered on the remote primary node also.

The standby must not think a transaction is visible until all changes made by it are have been applied. So snapshots from the primary cannot be used on the standby until the LSN at which they were taken has been reached by WAL apply on the standby. Snapshots don't normally have an LSN, so we must derive that information as well when we take a Snapshot. With asynchronous commits a transaction can be committed and yet not yet written to disk, so we cannot rely on the current WAL write pointer. Taking the WAL insert pointer is the safest thing to do, though most heavily contended. We don't want to hold ProcArrayLock while requesting WALInsertLock, so we will discover the LSN of the WAL insert pointer *after* the Snapshot has been derived.

So snapshots from the primary take time to before they can be used. The delay is exactly the current processing delay from primary to standby, so another reason why we do not wish to fall behind.

Taking snapshots from primary has a few disadvantages
* snapshots take time before they are usable
* requesting new snapshots is via remote request
* snapshots on primary prevent row removal (but this was also an advantage of this technique!)

If primary and standby node are connected by private ethernet then the snapshot request time will be ~10ms, though that is probably 100 times slower than current snapshot access. If primary and standby are opposite sides of an ocean then times could be as high as 500ms. Taking snapshots directly could be annoyingly slow for small read-only statements, so we need to buffer this process in various ways. For larger queries, this may not be a problem at all, but we do not wish to limit or assume the uses of the standby node.

First of all, each backend will have the opportunity to reuse previous snapshots both within transactions and across them. A userset parameter snapshot_reuse_window = 0..60000ms will define the time window in which any request for a new snapshot will simply result in being fed the last snapshot again. When the window on a snapshot has expired a newer snapshot will be presented instead. This idea is similar to serializable transactions, which continually reuse the same snapshot. This is a useful parameter for normal processing as well, since it will reduce contention on the ProcArrayLock for many applications.

Snapshots can be reused across transactions in recovery mode, since they are held in shared memory. Amount of shared memory dedicated to storing snapshots will be max_connections * size of snapshots.

Since there is a delay between obtaining a new snapshot and it becoming usable the Snapshot process will buffer them until they become "mature", like a good Whiskey. Snapshot process will take regular snapshots and pre-age them so that when a backend requests a snapshot it will be given the most recently matured snapshot. Time between snapshots is set by snapshot_preread_timeout = 0..60000ms. If a request for a snapshot arrives and there are no snapshots waiting to mature then this will trigger snapshot process to request a new primary snapshot. (This parameter could be automatically set based upon the arrival rate of snapshot requests, but this is a something to consider later).

If snapshot_reuse_window = 0 then a backend will be presented with a freshly obtained snapshot and will then wait until the exact first moment it can be used before returning.

We can continue to reuse snapshots from the primary even if the primary crashes, becomes disconnected or is shutdown/restarted. New snapshots are obviously not possible until it appears again. It's not that common for us to lose contact with the primary *and* for it to *not* be a failover, so this seems like an acceptable restriction.

txid_current_snapshot() is sufficient for our needs. We only need to know about top-level xids. This means we will need a working subtrans data structure, which means we will need some way to update it. Proposal is for the first WAL record in any subtransaction to note the parentxid. WAL apply loop will update subtrans during recovery when a WAL record arrives with an augmented parentxid.

=== Performance ===

Query performance may slow down WAL apply if too many queries execute concurrently and use up CPU and I/O resources that would have been used by the Startup process. Queries that hold pins for long periods on tables with frequent updates will slow down WAL apply.

Performance is not expected to be impaired by these new WAL message types since they apply only to AccessExclusiveLocks, which are reasonably rare even in applications that issue many DDL requests.

== Correctness ==

=== Two Phase Commit ===

Will need to maintain locking for prepared transactions. So will need to read twophase state and re-create any AELs therein. No other actions foreseen.

=== Tuple Locking ===

Tuple level locks are WAL-logged, so WAL replay will make the changes on blocks to emulate locking. There is no required access to lock manager.

From correctness perspective, no changes to tuples that are locked should appear in WAL, so we ignore the possibility.

No changes required here. No users can wait on locks, since they all use AccessShareLocks. So they just ignore tuple locks.

=== Data structure Check ===

* Heap - discussed above
* Index - discussed above
* FSM - checked with Heikki and new FSM causes no issues for Hot Standby
* twophase - just files, so structure is OK
* pg_log - writable
* pg_xlog - not writable by WAL apply or users
* clog - data structure valid and usable
* subtrans - data structure is non transactional
* multixact - is not required

== Acknowledgements ==

Florian Pflug's contribution to making Hot Standby was an important one. The single hardest problem in 2007 was how we would get access to a TransactionId. Florian's careful analysis resulted in the idea that is now critical to the rest of this project: VirtualTransactionIds. In addition, various internal changes made by a range of developers for 8.3 have made this significantly easier than it was previously.

Florian also identified the problems of relcache handling and block cleanup locks. Unfortunately, none of Florian's earlier code can/has been reused.

This design includes technical feedback from many PostgreSQL hackers, all of whom I thank and acknowledge.

== Project Plans ==

Priorities:
*1 Correctness
*2 Performance

Phases

* Design (2008!)

* Connection during recovery (mid-July)

* Infrastructure changes (1 Sept)

* Queries work when no WAL changes arrive (snapshots)

* Queries work while WAL changes arrive (block locking)

* Queries work while DDL changes arrive (locking, relcache)

* Performance

* Queries work with two-phase commit

== Features not planned for this release ==
(Or at least not by us in this release.)

* Automatic offloading - run a query on primary node and have it offload processing of larger queries onto standby nodes.

* Different mechanisms for deriving standby snapshots

* LISTEN, NOTIFY - should be possible when this happens via shared memory rather than pg_listener

User:Robe

2009-04-24T17:08:31Z

Robe: /* For 8.5 */

= Ad personam =

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

= Scribbles =

== Logging ==

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

The main advantages are:

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

=== Standard Syslog Output ===

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

=== Standard Logging Output ===

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

=== Syslog-Emulation ===

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

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

Which is already much more deterministic than the defaults.

= Ideas =

== For 8.5 ==

* Fix Status Quo of VACUUM Full
* Fix Cluster
* Add more Aliases for different Encodings
* Ease Table Partitioning
* Revisit all non-transactional/WAL logged commands

Waiting for 8.4

2009-04-22T15:02:43Z

Robe: adding category

The [http://developer.postgresql.org/pgdocs/postgres/release-8-4.html 8.4 Release Notes] briefly mention all of the features in the new version.

== Waiting for 8.4 ==
The "Waiting for 8.4" series from Hubert Lubaczewski covers features added to 8.4 as they are committed. A similar listing to this one is presented in [http://www.depesz.com/index.php/2009/04/10/waiting-for-84-final-post/ "final post"] or you can just go directly to [http://www.depesz.com/index.php/tag/pg84/ series index]:

*[http://www.depesz.com/index.php/2009/04/10/waiting-for-84-final-post/ final post (?)]
*[http://www.depesz.com/index.php/2009/03/22/waiting-for-84-no-more-d-in-pg_dump/ no more -d in pg_dump!]
*[http://www.depesz.com/index.php/2009/02/09/waiting-for-84-parallel-restoration-of-dumps/ parallel restoration of dumps]
*[http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions/ window functions]
*[http://www.depesz.com/index.php/2009/01/13/waiting-for-84-pg_stat_statements/ pg_stat_statements]
*[http://www.depesz.com/index.php/2009/01/08/waiting-for-84-remove-system-objects-from-dx/ remove system objects from \dX]
*[http://www.depesz.com/index.php/2008/12/29/waiting-for-84-window-functions-teaser/ Window Functions - teaser]
*[http://www.depesz.com/index.php/2008/12/09/waiting-for-84-default-values-for-function-arguments-integer-in-any-base/ Default values for function arguments + integer in any base]
*[http://www.depesz.com/index.php/2008/12/08/waiting-for-84-visibility-maps/ Visibility maps]
*[http://www.depesz.com/index.php/2008/11/23/waiting-for-84-table/ TABLE]
*[http://www.depesz.com/index.php/2008/11/23/waiting-for-84-auto-explain/ auto-explain]
*[http://www.depesz.com/index.php/2008/11/14/waiting-for-84-array-aggregate-and-array-unpacker/ array aggregate and array unpacker]
*[http://www.depesz.com/index.php/2008/11/05/waiting-for-84-suppress_redundant_updates_trigger/ suppress_redundant_updates_trigger]
*[http://www.depesz.com/index.php/2008/11/03/waiting-for-84-sql-wrappable-returning/ Waiting for 8.4 - sql-wrappable RETURNING]
*[http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/ pl/* srf functions in selects]
*[http://www.depesz.com/index.php/2008/10/07/waiting-for-84-common-table-expressions-with-queries/ Common Table Expressions (WITH queries)]
*[http://www.depesz.com/index.php/2008/10/04/waiting-for-84-new-fsm-free-space-map/ new FSM (Free Space Map)]
*[http://www.depesz.com/index.php/2008/09/28/waiting-for-84-database-level-lc_collation-and-lc_ctype/ database-level lc_collation and lc_ctype]
*[http://www.depesz.com/index.php/2008/09/13/waiting-for-84-pgbench-with-timed-execution/ pgbench with timed execution]
*[http://www.depesz.com/index.php/2008/08/10/waiting-for-84-case-insensitive-text-citext/ case insensitive text ( citext )]
*[http://www.depesz.com/index.php/2008/08/10/waiting-for-84-union-intersect-except/ UNION / INTERSECT / EXCEPT]
*[http://www.depesz.com/index.php/2008/09/08/waiting-for-84-ordered-data-loading-in-pg_dump/ ordered data loading in pg_dump]
*[http://www.depesz.com/index.php/2008/08/05/waiting-for-84-hash-based-distinct/ hash based DISTINCT]
*[http://www.depesz.com/index.php/2008/08/04/waiting-for-84-returns-table/ RETURNS TABLE]
*[http://www.depesz.com/index.php/2008/07/31/waiting-for-84-variadic-functions/ variadic functions]
*[http://www.depesz.com/index.php/2008/07/30/waiting-for-84-storage-types-for-table-columns/ storage types for table columns]
*[http://www.depesz.com/index.php/2008/07/30/waiting-for-84-sequence-details/ sequence details]
*[http://www.depesz.com/index.php/2008/06/11/waiting-for-84-waiting-onoff/ timing on/off]
*[http://www.depesz.com/index.php/2008/05/17/waiting-for-84-partial-match-support-in-gin-and-sequence-restart/ partial-match support in GIN, and sequence restart]
*[http://www.depesz.com/index.php/2008/05/16/waiting-for-84-case-in-plpgsql/ CASE in pl/PgSQL]
*[http://www.depesz.com/index.php/2008/05/15/waiting-for-84-function-stats/ function stats]
*[http://www.depesz.com/index.php/2008/05/14/waiting-for-84-plpgsql-raise/ pl/PgSQL RAISE]
*[http://www.depesz.com/index.php/2008/05/09/waiting-for-84-psql-vs-tabs-wrapped-output/ psql vs. tabs + wrapped output]
*[http://www.depesz.com/index.php/2008/05/05/waiting-for-84-pg_conf_load_time-time-related-generate_series-and-enum-values-in-dt/ pg_conf_load_time, time-related generate_series and enum values in \dT+]
*[http://www.depesz.com/index.php/2008/05/03/waiting-for-84-return-query-execute-and-cursor_tuple_fraction/ RETURN QUERY EXECUTE and cursor_tuple_fraction]
*[http://www.depesz.com/index.php/2008/05/02/waiting-for-84-o-and-d-in-psql/ \o and \d+ in psql]
*[http://www.depesz.com/index.php/2008/04/29/waiting-for-84-generate_subscripts/ generate_subscripts]
*[http://www.depesz.com/index.php/2008/04/18/waiting-for-84-explain-verbose/ “EXPLAIN VERBOSE”]
*[http://www.depesz.com/index.php/2008/04/05/waiting-for-84-current_query/ current_query()]
*[http://www.depesz.com/index.php/2008/04/02/waiting-for-84-3/ EXECUTE USING in plpgsql]
*[http://www.depesz.com/index.php/2008/03/31/waiting-for-84-2/ \l+ and \d in psql]
*[http://www.depesz.com/index.php/2008/03/28/waiting-for-84/ Statement-level ON TRUNCATE triggers]
*[http://www.depesz.com/index.php/2008/03/24/waiting-for-pg-84-4/ quote_nullable()]
*[http://www.depesz.com/index.php/2008/03/22/waiting-for-pg-84-3/ Report queries in a deadlock]
*[http://www.depesz.com/index.php/2008/03/10/waiting-for-pg-84-2/ enum type for guc parameters]
*[http://www.depesz.com/index.php/2008/02/16/waiting-for-pg-84/ limit (select ) and optional as in select]

==8.4 Talks and Discussion==
* [http://www.slideshare.net/xzilla/postgres-84-tutorial Intro to Postgres 8.4 Tutorial] by Robert Treat
* [http://www.hagander.net/talks/ What's new in PostgreSQL 8.4] by Magnus Hagander
* [http://blog.hagander.net/archives/141-Whats-your-favorite-8.4-feature.html What's your favorite 8.4 feature?]
* [http://lwn.net/SubscriberLink/328591/3fdb051da4bfee26/ PostgreSQL 8.4 Beta: "We've got momentum"]
* [http://fetter.org/ David Fetter talks]: "Trees and More in SQL" and "Windowing Functions: Putting the TPS in TPS Reports" cover 8.4 features
* [http://momjian.us/main/writings/pgsql/features.pdf Upcoming PostgreSQL Features] by Bruce Momjian

[[Category: PostgreSQL 8.4]]

PostgreSQL 8.4 Development Plan

2009-04-22T15:02:25Z

Robe: adding category

This is a copy of the [http://archives.postgresql.org/pgsql-hackers/2008-02/msg00193.php email sent to -hackers on 2008-02-06] outlining the core team's plan for attacking the 8.4 development cycle.

----

Hackers,

As you know we've finally released PostgreSQL 8.3, after a development
cycle that lasted well over a year despite our original plans for a 6
month cycle. The core team are aware that there are a number of
factors that contributed to this slippage:

*Lack of prompt and early review of patches.
*A significant rise in the number and complexity of patches submitted.
*Prioritising completion of incomplete patches over meeting the timetable.

In the 8.4 development cycle we would like to try a new style of
development, designed to keep the patch queue to a limited size and to
provide timely feedback to developers on the work they submit. To do
this we will replace the traditional 'feature freeze' with a series of
'commit fests' throughout the development cycle. The idea of commit
fests was discussed last October in -hackers, and it seemed to meet
with general approval. Whenever a commit fest is in progress, the
focus will shift from development to review, feedback and commit of
patches. Each fest will continue until all patches in the queue have
either been committed to the CVS repository, returned to the author
for additional work, or rejected outright, and until that has
happened, no new patches will be considered. Of course, individual
developers are free to continue working on their
patches throughout the fest, but we encourage everyone to do what they
can to help work through the patch queue. We feel that this idea can
only be successful if the whole development community is willing to
focus on patch review during the commit fests, in the same way that
everyone is expected to focus on testing during beta period.

The proposed timetable for the cycle is as follows:

*'''[[CommitFest 2008-03|1st March 2008]]''' - commit fest begins
*'''[[CommitFest 2008-05|1st May 2008]]''' - commit fest begins
*'''[[CommitFest 2008-07|1st July 2008]]''' - commit fest begins
*'''[[CommitFest 2008-09|1st September 2008]]''' - commit fest begins
*'''[[CommitFest 2008-11|1st November 2008]]''' - final commit fest begins
*'''1st January 2009''' - beta 1
*'''1st March 2009''' - 8.4.0 release

Note the lack of any 'feature freeze' date as such. However, any
significant feature patches not submitted by 1st November will clearly
not make it into 8.4.

The hope here is that we will not have enormous, previously unreviewed
patches landing on us at the end of October --- if that happens, we'll
be back in the same position we were in at 8.3 feature freeze.
Although this schedule allows for the final commit fest to take a good
deal of time, we'll reserve the right to reject patches that are too
large to be reviewed in a timely fashion. We want to encourage people
to do development of large features in an incremental fashion, with a
new increment landing during each commit fest.

[[Category: PostgreSQL 8.4]]

Archives Policy

2009-04-16T08:23:10Z

Robe: typo

'''DRAFT'''

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 except to remove offensive, pornographic, defamatory or otherwise potentially illegal messages.

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

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

* We want the archives to accurately represent the history of our public mailing lists.
* Due to the way the archives are stored and occasionally regenerated from archived mailbox files modifying a message is extremely difficult, time consuming and error prone.
* Removing a message breaks the numbering system used to access subsequent messages which will break any links on websites or other list messages.
* 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 is only the tip of the iceberg.
* 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.

Category:PostgreSQL 8.4

2009-03-28T11:45:26Z

Robe: add filler text to quell permission warnings when not logged in

All things related to PG 8.4

PostgreSQL 8.4 Open Items

2009-03-27T19:20:32Z

Robe: /* Pre-existing Bugs */ start_backup checkpointing

__NOTOC__
This is a list of tasks that need to finish before 8.4 is complete. Current version at http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items

== Changes ==
* [http://archives.postgresql.org/pgsql-hackers/2008-11/msg01883.php Change behavior of statement-level triggers for inheritance cases?]
** gsmet says: no patch written yet and it's probably something we'll need to backpatch so I don't think is a beta blocker
** rhaas says: TODO
* [http://archives.postgresql.org/message-id/87eiynlhbt.fsf@oxford.xeocode.com finishing posix_fadvise patch]
** gsmet says: no patch yet and no consensus on the solution yet: should be bumped to 8.5
** rhaas says: I'm not convinced the original design was wrong, but there's probably no time to rehash it now.
* [http://archives.postgresql.org/message-id/1232124740.31669.58.camel@ebony.2ndQuadrant GetCurrentVirtualXIDs() (is this patch safe?)]
** gsmet says: should be bumped to 8.5 - submitted far too late and no real answer to tgl's objection at the moment.
* [http://archives.postgresql.org/message-id/b42b73150902100857l3377bf8geafccb6392f6d129@mail.gmail.com PQinitSSL broken in some use cases]
** gsmet says: not a beta blocker IMHO but would be nice to fix it
** mha says: isn't this a beta blocker because it would add a function to the shared libraries, thus requiring a version number bump?
** tgl says: in the past we have not considered that adding a function requires a soname bump. But in general feature additions should not happen after beta starts. So either this blocks beta or it goes to 8.5.
** rhaas says: mmconcure was one of the instigators of this IIRC and he's OK with postpoing to 8.5
* [http://archives.postgresql.org/message-id/13073.1235929216@sss.pgh.pa.us change cardinality() for multi-dim arrays?]
** gsmet says: not a beta blocker IMHO but it's a must fix for 8.4
** tgl says: well, we either fix it or decide it's okay as is. But we have to make a decision now; once 8.4 ships we'll be stuck with the behavior.
** rhaas says: since it's only a compatibility function, the right answer is arguable; I don't see much reason to second-guess Peter's decision
* [http://archives.postgresql.org/message-id/49B45460.6040108@endpoint.com postgresql.conf: patch to have ParseConfigFile report all parsing errors, then bail]
** gsmet says: might be a good idea to commit it before beta. People are going to fine tune their 8.4 installation at the start of the beta phase and it seems like a good opportunity to exercise this code
** rhaas says: new feature, bump to 8.5. tgl agreed on -hackers.
* [http://archives.postgresql.org/message-id/Pine.LNX.4.64.0903161857200.31919@sn.sai.msu.ru small but useful patches for text search]
** gsmet says: no strong opinion on these ones. They were submitted far too late but they are small, useful and not likely to have any side effect elsewhere.
** rhaas says: new feature, bump to 8.5
* [http://archives.postgresql.org/message-id/49B0A914.8070006@sun.com Additional DTrace Probes]
** gsmet says: not a beta blocker. Not sure we want to commit them though. The patch is pretty small. These probes were originally submitted in july 2008 as part of a biggest patch and discarded from the patch due to comments. They were fixed in march 2009.
** rhaas says: tgl points out that this has not been reviewed yet, either. i think we should bump to 8.5.
* [http://archives.postgresql.org/message-id/24639.1237315756@sss.pgh.pa.us what to do about expensive args to DTrace probes?]
** gsmet says: IMHO, if we plan to build more binaries with DTrace enabled by default (tgl thought about enabling them in the RH/Fedora RPM packages), it should be fixed before the beta. Otherwise, it doesn't sound like a beta blocker.
** tgl says: both the DTrace and systemtap experts say that manual protection of an expensive probe with an if (foo_ENABLED()) test is the preferred way. I'm not thrilled with that but should probably defer to their judgment.
* [http://archives.postgresql.org/message-id/3f0b79eb0903242329j12865d55s348f5c873a956e71@mail.gmail.com pg_standby trigger behavior is dangerous]
** gsmet says: not a beta blocker but should be fixed in 8.4 and perhaps backpatched.
** rhaas says: backpatching sounds like a bad idea to me, but I could see fixing it for 8.4 if we can get it done RSN.
* [http://archives.postgresql.org/message-id/485579F9.4020307@dunslane.net typedefs for pg_indent]
** tgl says: this isn't a beta blocker, though we'd like to have it done before we do the pg_indent run for 8.4.

== psql \d issues ==
* [http://archives.postgresql.org/message-id/49A51740.9050804@gmail.com psql \d commands and information_schema]
* [http://archives.postgresql.org/message-id/49A5D354.7000609@dalibo.info Have \d show child tables that inherit from the specified parent]
** gsmet says: submitted in February and wanted behavior still discussed. Definitely 8.5 material.
* [http://archives.postgresql.org/message-id/603c8f070901211210x1433b95di4dd356ebb69d4444@mail.gmail.com The whole user/system behavioral issue ...]
* [http://archives.postgresql.org/message-id/19562.1230691104@sss.pgh.pa.us change psql's \df output for window functions?]
** gsmet says: cosmetic issue, not a beta blocker

== 8.4 Bugs ==
* [http://archives.postgresql.org/message-id/12431.1236965004@sss.pgh.pa.us posix_fadvise doesn't work on Solaris?]
* [http://archives.postgresql.org/message-id/20643.1232666867@sss.pgh.pa.us pgsql: Explicitly bind gettext to the correct encoding on Windows.]
** gsmet says: AFAICS waiting for Magnus' feedback
* [http://archives.postgresql.org/message-id/9706.1238023904@sss.pgh.pa.us some issues in new GIN code]

== Pre-existing Bugs ==
These issues also affect existing branches, so strictly speaking they are not blockers for putting out an 8.4 beta.
* [http://archives.postgresql.org/message-id/8763hwl56e.fsf@news-spur.riddles.org.uk GiST picksplit (maybe GIN too?) can fail]
* [http://archives.postgresql.org//pgsql-hackers/2009-03/msg00704.php OK to use built-in getopt_long (not getopt) on Solaris?]
* [http://archives.postgresql.org/message-id/20080929162628.1a05852b@jd-laptop pg_start_backup() takes too long]
** tgl says: maybe a documentation update is sufficient to resolve this?
** mrenner says: For now documentation or logging should be sufficient, should be revisited in 8.5 though for larger-than-standard checkpoint_intervals & completion targets
* [http://archives.postgresql.org/message-id/4887.1228700773@sss.pgh.pa.us Polymorphic types vs. domains]
* [http://archives.postgresql.org/message-id/4965C992.9020408@gmx.net Python 3.0 does not work with PL/Python]
* [http://archives.postgresql.org/message-id/20090306191404.GK3901@alvh.no-ip.org Perl/libxml incompatibility]
** Possible patch [http://archives.postgresql.org//pgsql-hackers/2009-03/msg00854.php here], but it desperately needs testing
* [http://archives.postgresql.org/message-id/1233209252.18692.24.camel@dragflick possible bug in cover density ranking?]
* [http://archives.postgresql.org//pgsql-bugs/2009-03/msg00117.php BUG #4721: bad side-effects of limiting number of clauses that predtest will consider]
* [http://archives.postgresql.org/message-id/9ea8622b0902072142u76c86c30q8b433182e8cb0800@mail.gmail.com BUG #4622: xpath only work in utf-8 server encoding]
* [http://archives.postgresql.org/message-id/17021.1234474178@sss.pgh.pa.us contrib/intarray opclass definition needs updating]
* [http://archives.postgresql.org/message-id/200903050848.n258mVgm046178@wwwmaster.postgresql.org BUG #4694: uppercase path problem on Windows]
* [http://archives.postgresql.org/message-id/49C0BDC5.4010002@hagander.net Path separator consistency on Windows]
* [http://archives.postgresql.org/message-id/200902142251.n1EMpsFU006888@wwwmaster.postgresql.org BUG #4655: Spelling mistake in windows installer]
** tgl says: this is something for the one-click installer, not core Postgres.

== Questions ==
* {{messageLink|491862F7.1060501@enterprisedb.com|Should we have a LOCALE option in CREATE DATABASE?}}
* {{messageLink|20090213100104.201D.52131E4D@oss.ntt.co.jp|Should we reject toast.fillfactor in reloptions?}} Because toast tables are never updated.
* {{messageLink|Pine.BSO.4.64.0902201611540.28757@leary.csoft.net|Consider reverting preventing regular users from base type creation}}

== Documentation ==
* [http://archives.postgresql.org/message-id/4936884B.6050205@enterprisedb.com document visibility map], [http://archives.postgresql.org/message-id/4978C34D.3050208@enterprisedb.com proposed manual change]
* [http://archives.postgresql.org/message-id/20090218155841.7EFD37559ED@cvs.postgresql.org document removal of log_restartpoints, and the rest of the recovery infra patch]
* [http://archives.postgresql.org/message-id/49C532B7.5030608@amd.co.at Documentation Update: WAL and Checkpoints]

[[Category: PostgreSQL 8.4]]

PostgreSQL 8.4 Open Items

2009-03-13T17:39:14Z

Robe:

WhatsNew84

2009-03-13T17:38:32Z

Robe:

This page is designed as a resource for people writing articles, presentations, brochures, or other advocacy related materials, to give them an overview of some of the new features coming in PostgreSQL 8.4. The format of the entries is:

* Feature
** What does it do and/or what does it change.
** Who should care, who will benefit the most.

Furthermore, the items have been split into 4 sections:
* Performance, for performance-related features.
* Admin, for features mostly likely to help DBA's
* Developer, for features likely to help people developing applications for PostgreSQL.
* 3rd Party, for features that are not included with the core server, but which are new in the 8.4 cycle.

For more information, see the [http://momjian.us/main/writings/pgsql/sgml/release-8-4.html 8.4 Release Notes]

----

== Performance ==
*Hash Methods for DISTINCT/UNION/INTERSECT/EXCEPTION queries
** Previously, operations like distinct would require Postgres to sort and then eliminate data to achieve the distinct results. Now PostgreSQL can use hash based methods (similar to group by) to achieve these results. This should make many of these queries faster without needing any changes.
*Cursor_tuple_fraction GUC
** Tell the planner what fraction of a cursors results you expect to return for a given cursor query. This allows the planner to use plans that are more optimal based on estimated percentage of rows you expect to return.
*Visibility maps
*parallel pg_restore

== Admin ==
*New Free-Space-Map (fsm) implementation
*Database Level LC_Collate/LC_Type
*PgBench with timed execution
*Column storage type display in psql
*Improved sequence display in psql
*Better control of \timing in psql
*ALTER SEQUENCE RESTART command
*TRUNCATE TABLE RESTART IDENTITY
*Track usage statistics for functions
*Improved handling of long lines and tab characters in psql
*pg_conf_load_time() function
*display enum values in enum type display for psql
*add table size display in psql
*EXPLAIN VERBOSE column level output
*add display of referencing tables to \d output in psql
*add tablespace and database size information to \l in psql
*improved tab completion support for tables in multiple schemas
*report all queries involved in a deadlock error
*pg_settings now shows available options for guc with defined set
*auto-explain contrib module
** this contrib module allows the dumping of explain plans for slow queries into the log. this allows for better troubleshooting, as explain plans for queries can very widely for different query executions.
*remove system objects from \dX commands in psql
*pg_stat_statments contrib module
*column level privilege support
*improved SSL certificate handling
*add columns to views
** allow you to add columns to a view (at the end), without having to recompile the view / dependencies. still can't modify / remove columns though.
*multi-column GIN indexes
*\ef in psql
** \ef <functioname> will bring up a function definition in your editor, so you can edit functions on the server.
*pgstat temp file
** the pgstat file tracks usage statistics in postgresql. currently it can lead to very high i/o on the fs, which can be hard to track down. 8.4 reduced usage of file, but also made the file relocatable, so you can put it on ramdrive or similar.
*pg_hba.conf name=value syntax
*pg_hba.conf usermaps

== Developers ==
*Common Table Expression (aka CTE, WITH queries)
*Case Insensitive Text module
*RETURNS TABLE for plpgsql functions
*Variable Argument Functions for plpgsql
*Partial Match support for Full Text Search
*CASE control structure support in plpgsql
*Support HINT, DETAIL, and SQLSTATE in RAISE command for plpgsql
*Time based generate_series() functions
*RETURN QUERY EXECUTE support in plpgsql
*generate_subscripts() function
** Generate subscripts allows for easier array traversal. You used to be able to do this with generate_series and array_upper and array_lower, but this new version should be easier to use and faster as well.
*EXECUTE USING for plpgsql
*Support for statement level triggers for TRUNCATE command
*quote_nullable()
*allow limit based on subquery
*make column alias keyword "as" optional (per sql spec)
*allow srf functions to be called in select clause for plpgsql
*suppress_redundent_updates() trigger
*array_aggregate() function
*unnest() function
*add TABLE sql 2008 spec command
*default values for functions
*sql2008 window functions
*sql standard interval handling

== 3rd Party ==

== Put off to 8.5 ==

There is already an active "queue" of items that are being deferred for 8.4, in addition to the [[Todo:Contents|ToDo Lists]].

[[Category: PostgreSQL 8.4]]

User:Robe

2009-03-08T12:26:41Z

Robe:

= Ad personam =

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

= Scribbles =

== Logging ==

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

The main advantages are:

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

=== Standard Syslog Output ===

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

=== Standard Logging Output ===

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

=== Syslog-Emulation ===

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

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

Which is already much more deterministic than the defaults.

= Ideas =

== For 8.5 ==

* Fix Status Quo of VACUUM Full
* Add more Aliases for different Encodings
* Ease Table Partitioning
* Revisit all non-transactional/WAL logged commands

Talk:Londiste Tutorial (Skytools 2)

2009-03-04T15:25:29Z

Robe: New page: Thanks, this is highly appreciated. I also have some docs on slony and WAL shipping which I'll contribute soon. - ~~~~

Thanks, this is highly appreciated. I also have some docs on slony and WAL shipping which I'll contribute soon. - [[User:Robe|Robe]] 15:25, 4 March 2009 (UTC)

Replication, Clustering, and Connection Pooling

2009-01-31T18:08:43Z

Robe: /* Replication */ clarified londiste/skytools

==Introduction==
There are many approaches available to scale PostgreSQL beyond running on a single server. An outline of the terminology and basic technologies involved is at [http://www.postgresql.org/docs/current/interactive/high-availability.html High Availability and Load Balancing]. There is a [http://momjian.us/main/writings/pgsql/replication.pdf presentation] covering some of these solutions.

There is no one-size fits all replication software. You have to understand your requirements and how various approaches fit into that. For example, here are two extremes in the replication problem space:

* You have a few servers connected to a local network you want to always keep current for failover and load-balancing purposes. Here you would be considering solutions that are synchronous, eager, and therefore conflict-free.
* Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

These are both database replication problems, but the best way to solve them is very different. And as you can see from these examples, replication has a lot of specific terminology that you'll have to understand to figure out what class of solution makes sense for your requirements. A great source for this background is in the
[http://www.postgres-r.org/documentation/terms Postgres-R Terms and Definitions for Database Replication]. The main theoretical topic it doesn't mention is how to resolve conflict resolution in lazy replication cases like the laptop situation, which involves voting and similar schemes.

==Features in the Core of PostgreSQL==
The PostgreSQL core team considered replication and clustering technology outside the scope of the main project's focus but this changed in Spring 2008, see the [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php Core Teams statement].

*[[Warm Standby]]/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is the simplest and best solution if all you care about is continuous backup and short failover times.

*There's an ongoing project to integrate hot standby capabilites (read only queries on slave) into PostgreSQL...if/when complete, this would provide a replication mechanism similar to, but significantly better than mysql binary log replication, and would provide an excellent complement to slony. See [[Hot Standby]].

==Comparison matrix==

{| border="1" cellpadding="1" cellspacing="0" style="font-size: 85%; border: gray solid 1px; border-collapse: collapse; text-align: center; width: 100%; table-layout: fixed;"
|- style="background: #ececec"
! Program
! License
! Maturity
! Replication Method
! Sync
! Connection Pooling
! Load Balancing
! Query Partitioning
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.pgcluster.org/ PGCluster]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | See version details on site
| bgcolor="#ffffaa" | Master-Master
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/ pgpool-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/pgpool-II/en/ pgpool-II]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
|-
! style="text-align:block;" bgcolor="#ececec" | [http://slony.info/ slony-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://bucardo.org/ Bucardo]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Master, Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [https://developer.skype.com/SkypeGarage/DbProjects/SkyTools Londiste]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.commandprompt.com/products/mammothreplicator/ Mammoth]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|}

==Replication==

Aside from Warm Standby, mentioned above...

*Slony-I: Seems good, single master only, master is a single point of failure, no good failover system for electing a new master or having a failed master rejoin the cluster. Slave databases are mostly for safety or for parallelizing queries for performance. Suffers from O(N^2) communications (N = cluster size). with reasonable sysadmin you can implement failover system yourself. regarding communications, you can cascade the replication to reduce load on the master. If you were implementing a large replication cluster, this would probably be a good idea. Slony is powerful, trigger based, and highly configurable.

* PGCluster: PGCluster (which, incidentally, is not the same as PGCluster-II, a shared-disk solution), which does synchronous multimaster replication. Two single-points failure spots, load balancer and the data replicator. The project has historically looked a bit dead, but they just released a new version and moved to a Trac-based web site at http://www.pgcluster.org/ and http://pgfoundry.org/projects/pgcluster is up to date (at least downloads page) One major downside to PGCluster is that it uses a modified version of PostgreSQL, and it usually lags a few releases behind.

* http://pgpool.projects.postgresql.org/ pgpool 1/2 is a reasonable solution. it's statement level replication, which has some downsides, but is good for certain things. pgpool 2 has a neat distributed table mechanism which is interesting. You might want to be looking here if you have extremely high ratios of read to write but need to service a huge transaction volume. Supports load-balancing and replication by implementing a proxy that duplicates all updates to all slaves. It can partition data by doing this, and it can semi-intelligently route queries to the appropriate servers.

* [http://www.commandprompt.com/products/mammothreplicator/ Mammoth Replicator], commercial.

* [http://bucardo.org/ Bucardo]: Trigger-based, asynchronous, multi-master or master-slave.

* Cybertec, an Austrian company, offers a commercial packaging of PGCluster. They simply call it PostgreSQL Multimaster-Replication, see http://www.cybertec.at.

* Londiste, a part of Skytools (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools) which is a collection of replication tools from the Skype people. Purports to be simpler to use than Slony.

* [http://www.continuent.com/index.php?option=com_content&task=view&id=212&Itemid=169 Continuent uni/cluster], commercial and the related Sequoia (jdbc, formerly known as c-jdbc)

* [http://www.postgres-r.org Postgres-R] is still in development. It features eager and thus conflict-free, but async multi-master replication.

* DRBD (http://www.drbd.org/), a device driver that replicates disk blocks to other nodes. This works for failover only, not for scaling reads. Easy migration of devices if combined with an NFS export.

* "Daffodil Replicator" - GPL - http://sourceforge.net/projects/daffodilreplica/

===Inactive projects===
* [http://www.slony2.org/ Slony-II]
* PGReplication

==Clustering==

* [http://www.greenplum.com/index.php?page=greenplum-database Greenplum Database] (formerly Bizgres MPP), commercial. Not so much a replication solution as a way to parallelize queries, and targeted at the data warehousing crowd. Similar to ExtenDB, but tightly integrated with PostgreSQL.

*[http://www.enterprisedb.com/products/gridsql.do GridSQL for EnterpriseDB Advanced Server] (formerly ExtenDB)

*sequoia (jdbc, formerly known as c-jdbc)

==Connection Pooling and Acceleration==

Connection pooling programs let you reduce database-related overhead when it's the sheer number of physical connections dragging performance down. This is particularly important on Windows, where system limitations prevent large number of connections; see "I cannot run with more than about 125 connections at once" in the [http://www.postgresql.org/docs/faqs.FAQ_windows.html Windows FAQ]. It's also vital for web applications where the number of connections can get very large.

Some programs that implement connection pooling are:
* [https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer PgBouncer]
* [http://pgpool.projects.postgresql.org/ pgpool]
* [http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/ plproxy]

Some people also or alternately use [http://www.danga.com/memcached/ memcached] in various ways to reduce the work the database handles directly by caching popular data.

==Credits==

Sources for the initial information on this page include:
*[http://archives.postgresql.org/pgsql-performance/2007-06/msg00264.php replication thread]
*[http://archives.postgresql.org/pgsql-general/2007-08/msg00085.php pgpool2 vs sequoia]
*[http://archives.postgresql.org/pgsql-hackers/2006-10/msg00810.php Postgresql Caching]

A existing page covering this topic in German is at http://burger-ag.de/postgresql_replikation.whtml It translates pretty well through [http://babelfish.altavista.com/ Babelfish].

Sources for more information located but not yet integrated into here:
* [http://bristlecone.continuent.org/uploads/bristlecone/HomePage/PG_East-Scale-Out-Benchmarks_FINAL2.pdf Portable Scale-Out Benchmarks for PostgreSQL] by Robert Hodges
* [http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf High Availability and PostgreSQL] by Gavin Sherry

Replication, Clustering, and Connection Pooling

2009-01-31T18:03:48Z

Robe: /* Features in the Core of PostgreSQL */ updated other text in paragraph to reflect core teams changed stance on integrated replication solutions

==Introduction==
There are many approaches available to scale PostgreSQL beyond running on a single server. An outline of the terminology and basic technologies involved is at [http://www.postgresql.org/docs/current/interactive/high-availability.html High Availability and Load Balancing]. There is a [http://momjian.us/main/writings/pgsql/replication.pdf presentation] covering some of these solutions.

There is no one-size fits all replication software. You have to understand your requirements and how various approaches fit into that. For example, here are two extremes in the replication problem space:

* You have a few servers connected to a local network you want to always keep current for failover and load-balancing purposes. Here you would be considering solutions that are synchronous, eager, and therefore conflict-free.
* Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

These are both database replication problems, but the best way to solve them is very different. And as you can see from these examples, replication has a lot of specific terminology that you'll have to understand to figure out what class of solution makes sense for your requirements. A great source for this background is in the
[http://www.postgres-r.org/documentation/terms Postgres-R Terms and Definitions for Database Replication]. The main theoretical topic it doesn't mention is how to resolve conflict resolution in lazy replication cases like the laptop situation, which involves voting and similar schemes.

==Features in the Core of PostgreSQL==
The PostgreSQL core team considered replication and clustering technology outside the scope of the main project's focus but this changed in Spring 2008, see the [http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php Core Teams statement].

*[[Warm Standby]]/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is the simplest and best solution if all you care about is continuous backup and short failover times.

*There's an ongoing project to integrate hot standby capabilites (read only queries on slave) into PostgreSQL...if/when complete, this would provide a replication mechanism similar to, but significantly better than mysql binary log replication, and would provide an excellent complement to slony. See [[Hot Standby]].

==Comparison matrix==

{| border="1" cellpadding="1" cellspacing="0" style="font-size: 85%; border: gray solid 1px; border-collapse: collapse; text-align: center; width: 100%; table-layout: fixed;"
|- style="background: #ececec"
! Program
! License
! Maturity
! Replication Method
! Sync
! Connection Pooling
! Load Balancing
! Query Partitioning
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.pgcluster.org/ PGCluster]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | See version details on site
| bgcolor="#ffffaa" | Master-Master
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/ pgpool-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/pgpool-II/en/ pgpool-II]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
|-
! style="text-align:block;" bgcolor="#ececec" | [http://slony.info/ slony-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://bucardo.org/ Bucardo]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Master, Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [https://developer.skype.com/SkypeGarage/DbProjects/SkyTools Londiste]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.commandprompt.com/products/mammothreplicator/ Mammoth]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|}

==Replication==

Aside from Warm Standby, mentioned above...

*Slony-I: Seems good, single master only, master is a single point of failure, no good failover system for electing a new master or having a failed master rejoin the cluster. Slave databases are mostly for safety or for parallelizing queries for performance. Suffers from O(N^2) communications (N = cluster size). with reasonable sysadmin you can implement failover system yourself. regarding communications, you can cascade the replication to reduce load on the master. If you were implementing a large replication cluster, this would probably be a good idea. Slony is powerful, trigger based, and highly configurable.

* PGCluster: PGCluster (which, incidentally, is not the same as PGCluster-II, a shared-disk solution), which does synchronous multimaster replication. Two single-points failure spots, load balancer and the data replicator. The project has historically looked a bit dead, but they just released a new version and moved to a Trac-based web site at http://www.pgcluster.org/ and http://pgfoundry.org/projects/pgcluster is up to date (at least downloads page) One major downside to PGCluster is that it uses a modified version of PostgreSQL, and it usually lags a few releases behind.

* http://pgpool.projects.postgresql.org/ pgpool 1/2 is a reasonable solution. it's statement level replication, which has some downsides, but is good for certain things. pgpool 2 has a neat distributed table mechanism which is interesting. You might want to be looking here if you have extremely high ratios of read to write but need to service a huge transaction volume. Supports load-balancing and replication by implementing a proxy that duplicates all updates to all slaves. It can partition data by doing this, and it can semi-intelligently route queries to the appropriate servers.

* [http://www.commandprompt.com/products/mammothreplicator/ Mammoth Replicator], commercial.

* [http://bucardo.org/ Bucardo]: Trigger-based, asynchronous, multi-master or master-slave.

* Cybertec, an Austrian company, offers a commercial packaging of PGCluster. They simply call it PostgreSQL Multimaster-Replication, see http://www.cybertec.at.

* Skytools (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools), a collection of replication tools from the Skype people. Purports to be simpler to use than Slony.

* [http://www.continuent.com/index.php?option=com_content&task=view&id=212&Itemid=169 Continuent uni/cluster], commercial and the related Sequoia (jdbc, formerly known as c-jdbc)

* [http://www.postgres-r.org Postgres-R] is still in development. It features eager and thus conflict-free, but async multi-master replication.

* DRBD (http://www.drbd.org/), a device driver that replicates disk blocks to other nodes. This works for failover only, not for scaling reads. Easy migration of devices if combined with an NFS export.

* "Daffodil Replicator" - GPL - http://sourceforge.net/projects/daffodilreplica/

===Inactive projects===
* [http://www.slony2.org/ Slony-II]
* PGReplication

==Clustering==

* [http://www.greenplum.com/index.php?page=greenplum-database Greenplum Database] (formerly Bizgres MPP), commercial. Not so much a replication solution as a way to parallelize queries, and targeted at the data warehousing crowd. Similar to ExtenDB, but tightly integrated with PostgreSQL.

*[http://www.enterprisedb.com/products/gridsql.do GridSQL for EnterpriseDB Advanced Server] (formerly ExtenDB)

*sequoia (jdbc, formerly known as c-jdbc)

==Connection Pooling and Acceleration==

Connection pooling programs let you reduce database-related overhead when it's the sheer number of physical connections dragging performance down. This is particularly important on Windows, where system limitations prevent large number of connections; see "I cannot run with more than about 125 connections at once" in the [http://www.postgresql.org/docs/faqs.FAQ_windows.html Windows FAQ]. It's also vital for web applications where the number of connections can get very large.

Some programs that implement connection pooling are:
* [https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer PgBouncer]
* [http://pgpool.projects.postgresql.org/ pgpool]
* [http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/ plproxy]

Some people also or alternately use [http://www.danga.com/memcached/ memcached] in various ways to reduce the work the database handles directly by caching popular data.

==Credits==

Sources for the initial information on this page include:
*[http://archives.postgresql.org/pgsql-performance/2007-06/msg00264.php replication thread]
*[http://archives.postgresql.org/pgsql-general/2007-08/msg00085.php pgpool2 vs sequoia]
*[http://archives.postgresql.org/pgsql-hackers/2006-10/msg00810.php Postgresql Caching]

A existing page covering this topic in German is at http://burger-ag.de/postgresql_replikation.whtml It translates pretty well through [http://babelfish.altavista.com/ Babelfish].

Sources for more information located but not yet integrated into here:
* [http://bristlecone.continuent.org/uploads/bristlecone/HomePage/PG_East-Scale-Out-Benchmarks_FINAL2.pdf Portable Scale-Out Benchmarks for PostgreSQL] by Robert Hodges
* [http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf High Availability and PostgreSQL] by Gavin Sherry

Replication, Clustering, and Connection Pooling

2009-01-31T17:55:24Z

Robe: /* Features in the Core of PostgreSQL */ update Hot Standby comments

==Introduction==
There are many approaches available to scale PostgreSQL beyond running on a single server. An outline of the terminology and basic technologies involved is at [http://www.postgresql.org/docs/current/interactive/high-availability.html High Availability and Load Balancing]. There is a [http://momjian.us/main/writings/pgsql/replication.pdf presentation] covering some of these solutions.

There is no one-size fits all replication software. You have to understand your requirements and how various approaches fit into that. For example, here are two extremes in the replication problem space:

* You have a few servers connected to a local network you want to always keep current for failover and load-balancing purposes. Here you would be considering solutions that are synchronous, eager, and therefore conflict-free.
* Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

These are both database replication problems, but the best way to solve them is very different. And as you can see from these examples, replication has a lot of specific terminology that you'll have to understand to figure out what class of solution makes sense for your requirements. A great source for this background is in the
[http://www.postgres-r.org/documentation/terms Postgres-R Terms and Definitions for Database Replication]. The main theoretical topic it doesn't mention is how to resolve conflict resolution in lazy replication cases like the laptop situation, which involves voting and similar schemes.

==Features in the Core of PostgreSQL==
The PostgreSQL core team considers replication and clustering technology outside the scope of the main project's focus. Jan Wieck works almost exclusively on Slony, so there is much high level attention in this area. While some features to help support this area are available, actually implementing a full solution is considered best implemented by a layer of software built on top of the core, rather than including it in there directly.

*[[Warm Standby]]/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is the simplest and best solution if all you care about is continuous backup and short failover times.

*There's an ongoing project to integrate hot standby capabilites (read only queries on slave) into PostgreSQL...if/when complete, this would provide a replication mechanism similar to, but significantly better than mysql binary log replication, and would provide an excellent complement to slony. See [[Hot Standby]].

==Comparison matrix==

{| border="1" cellpadding="1" cellspacing="0" style="font-size: 85%; border: gray solid 1px; border-collapse: collapse; text-align: center; width: 100%; table-layout: fixed;"
|- style="background: #ececec"
! Program
! License
! Maturity
! Replication Method
! Sync
! Connection Pooling
! Load Balancing
! Query Partitioning
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.pgcluster.org/ PGCluster]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | See version details on site
| bgcolor="#ffffaa" | Master-Master
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/ pgpool-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/pgpool-II/en/ pgpool-II]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Statement-Based Middleware
| bgcolor="#ffffaa" | Synchronous
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
| bgcolor="#ddffdd" | Yes
|-
! style="text-align:block;" bgcolor="#ececec" | [http://slony.info/ slony-I]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://bucardo.org/ Bucardo]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Master, Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [https://developer.skype.com/SkypeGarage/DbProjects/SkyTools Londiste]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Recent release
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|-
! style="text-align:block;" bgcolor="#ececec" | [http://www.commandprompt.com/products/mammothreplicator/ Mammoth]
| bgcolor="#ffffaa" | BSD
| bgcolor="#ffffaa" | Stable
| bgcolor="#ffffaa" | Master-Slave
| bgcolor="#ffffaa" | Asynchronous
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
| bgcolor="#ffaaaa" | No
|}

==Replication==

Aside from Warm Standby, mentioned above...

*Slony-I: Seems good, single master only, master is a single point of failure, no good failover system for electing a new master or having a failed master rejoin the cluster. Slave databases are mostly for safety or for parallelizing queries for performance. Suffers from O(N^2) communications (N = cluster size). with reasonable sysadmin you can implement failover system yourself. regarding communications, you can cascade the replication to reduce load on the master. If you were implementing a large replication cluster, this would probably be a good idea. Slony is powerful, trigger based, and highly configurable.

* PGCluster: PGCluster (which, incidentally, is not the same as PGCluster-II, a shared-disk solution), which does synchronous multimaster replication. Two single-points failure spots, load balancer and the data replicator. The project has historically looked a bit dead, but they just released a new version and moved to a Trac-based web site at http://www.pgcluster.org/ and http://pgfoundry.org/projects/pgcluster is up to date (at least downloads page) One major downside to PGCluster is that it uses a modified version of PostgreSQL, and it usually lags a few releases behind.

* http://pgpool.projects.postgresql.org/ pgpool 1/2 is a reasonable solution. it's statement level replication, which has some downsides, but is good for certain things. pgpool 2 has a neat distributed table mechanism which is interesting. You might want to be looking here if you have extremely high ratios of read to write but need to service a huge transaction volume. Supports load-balancing and replication by implementing a proxy that duplicates all updates to all slaves. It can partition data by doing this, and it can semi-intelligently route queries to the appropriate servers.

* [http://www.commandprompt.com/products/mammothreplicator/ Mammoth Replicator], commercial.

* [http://bucardo.org/ Bucardo]: Trigger-based, asynchronous, multi-master or master-slave.

* Cybertec, an Austrian company, offers a commercial packaging of PGCluster. They simply call it PostgreSQL Multimaster-Replication, see http://www.cybertec.at.

* Skytools (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools), a collection of replication tools from the Skype people. Purports to be simpler to use than Slony.

* [http://www.continuent.com/index.php?option=com_content&task=view&id=212&Itemid=169 Continuent uni/cluster], commercial and the related Sequoia (jdbc, formerly known as c-jdbc)

* [http://www.postgres-r.org Postgres-R] is still in development. It features eager and thus conflict-free, but async multi-master replication.

* DRBD (http://www.drbd.org/), a device driver that replicates disk blocks to other nodes. This works for failover only, not for scaling reads. Easy migration of devices if combined with an NFS export.

* "Daffodil Replicator" - GPL - http://sourceforge.net/projects/daffodilreplica/

===Inactive projects===
* [http://www.slony2.org/ Slony-II]
* PGReplication

==Clustering==

* [http://www.greenplum.com/index.php?page=greenplum-database Greenplum Database] (formerly Bizgres MPP), commercial. Not so much a replication solution as a way to parallelize queries, and targeted at the data warehousing crowd. Similar to ExtenDB, but tightly integrated with PostgreSQL.

*[http://www.enterprisedb.com/products/gridsql.do GridSQL for EnterpriseDB Advanced Server] (formerly ExtenDB)

*sequoia (jdbc, formerly known as c-jdbc)

==Connection Pooling and Acceleration==

Connection pooling programs let you reduce database-related overhead when it's the sheer number of physical connections dragging performance down. This is particularly important on Windows, where system limitations prevent large number of connections; see "I cannot run with more than about 125 connections at once" in the [http://www.postgresql.org/docs/faqs.FAQ_windows.html Windows FAQ]. It's also vital for web applications where the number of connections can get very large.

Some programs that implement connection pooling are:
* [https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer PgBouncer]
* [http://pgpool.projects.postgresql.org/ pgpool]
* [http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/ plproxy]

Some people also or alternately use [http://www.danga.com/memcached/ memcached] in various ways to reduce the work the database handles directly by caching popular data.

==Credits==

Sources for the initial information on this page include:
*[http://archives.postgresql.org/pgsql-performance/2007-06/msg00264.php replication thread]
*[http://archives.postgresql.org/pgsql-general/2007-08/msg00085.php pgpool2 vs sequoia]
*[http://archives.postgresql.org/pgsql-hackers/2006-10/msg00810.php Postgresql Caching]

A existing page covering this topic in German is at http://burger-ag.de/postgresql_replikation.whtml It translates pretty well through [http://babelfish.altavista.com/ Babelfish].

Sources for more information located but not yet integrated into here:
* [http://bristlecone.continuent.org/uploads/bristlecone/HomePage/PG_East-Scale-Out-Benchmarks_FINAL2.pdf Portable Scale-Out Benchmarks for PostgreSQL] by Robert Hodges
* [http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf High Availability and PostgreSQL] by Gavin Sherry

IRC2RWNames

2008-10-10T15:33:08Z

Robe: added myself

=== List of IRC nicks with their respective real world names ===

{| border="1"
|-
!Nickname || Real Name
|-
|ads || Andreas Scherbaum
|-
|agliodbs, aglio2 (freenode), jberkus (oftc) || Josh Berkus
|-
|ahammond || Andrew Hammond
|-
|alvherre || Alvaro Herrera
|-
|RhodiumToad (formerly AndrewSN) || Andrew Gierth
|-
|aurynn || Aurynn Shaw
|-
|BlueAidan/BlueAidan_work || David Blewett
|-
|bmomjian || Bruce Momjian
|-
|cbbrowne || Christopher Browne
|-
|crab || Abhijit Menon-Sen
|-
|Crad || Gavin M. Roy
|-
|daamien || Damien Clochard
|-
|DarcyB || Darcy Buskermolen
|-
|davidfetter || David Fetter
|-
|dcramer || Dave Cramer
|-
|DeciBull, TheCougar || Jim C. Nasby
|-
|dennisb || Dennis Björklund
|-
|devrimgunduz || Devrim Gündüz
|-
|dim || Dimitri Fontaine
|-
|direvus || Brendan Jurd
|-
|dvl || Dan Langille
|-
|f3ew || Devdas Vasu Bhagat
|-
|feivel || Michael Meskes
|-
|elein || Elein Mustain
|-
|gleu || Guillaume Lelarge
|-
|grzm || Michael Glaesemann
|-
|gsmet || Guillaume Smet
|-
|G_SabinoMullane || Greg Sabino Mullane
|-
|HarrisonF || Harrison Fisk
|-
|indigo || Phil Frost
|-
|JanniCash || Jan Wieck
|-
|jdavis, jdavis_ || Jeff Davis
|-
|jurka || Kris Jurka
|-
|justatheory || David Wheeler
|-
|jpa || Jean-Paul Argudo
|-
|klando || Cédric Villemain
|-
|larryrtx || Larry Rosenman
|-
|linuxpoet, postgresman || Joshua D. Drake
|-
|lluad || Steve Atkins
|-
|lsmith || Lukas Smith
|-
|magnush || Magnus Hagander
|-
|markwkm || Mark Wong
|-
|mastermind || [[user:mastermind | Stefan Kaltenbrunner]]
|-
|merlinm || Merlin Moncure
|-
|miracee || Susanne Ebrecht
|-
|MojoWork, StuckMojo || Jon Erdman
|-
|Moosbert || Peter Eisentraut
|-
|neilc || Neil Conway
|-
|oicu || Andrew Dunstan
|-
|okbobcz || Pavel Stehule
|-
|pgSnake || Dave Page
|-
|PJMODOS || Petr Jelínek
|-
|postwait || Theo Schlossnagle
|-
|psoo || Bernd Helmle
|-
|pyarra || Philip Yarra
|-
|Robe || [[user:Robe | Michael Renner]]
|-
|rotellaro || Federico Campoli
|-
|rtfm_please || [[IRCBotSyntax]]
|-
|SAS || Stéphane Schildknecht
|-
|scrappy || Marc G. Fournier
|-
|selenamarie || Selena Deckelmann
|-
|Snow-Man || Stephen Frost
|-
|swm || Gavin Sherry
|-
|vy || Volkan YAZICI
|-
|xzilla, xzi11a || Robert Treat
|}

User:Robe

2008-10-09T10:49:11Z

Robe:

User:Robe

2008-10-08T22:25:46Z

Robe: this ain't dokuwiki

== Logging ==

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

The main advantages are:

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

=== Standard Syslog Output ===

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

=== Standard Logging Output ===

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

=== Syslog-Emulation ===

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

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

Which is already much more deterministic than the defaults.

User:Robe

2008-10-08T22:20:05Z

Robe: New page: == Logging == After looking into Syslog per suggestions of pgfouine it seems as if Syslog-Logs are much easier to parse and overall more deterministic than "Standard" Logs. The main adva...