https://wiki.postgresql.org/api.php?action=feedcontributions&user=Mwtoews&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-28T20:27:51ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=YUM_Installation&diff=24006YUM Installation2014-12-19T03:04:05Z<p>Mwtoews: update example to use PostgreSQL 9.4</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms; for the current release and prior release or two):<br />
* Fedora<br />
* Red Hat Enterprise Linux<br />
* CentOS<br />
* Scientific Linux<br />
* Oracle Enterprise Linux<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/repopackages.php Repository packages for different version combinations]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the postgresql supplied by the base repository):<br />
exclude=postgresql*<br />
<br />
=== Install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.4 on CentOS 6 64-bit:<br />
yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.4 server:<br />
yum install postgresql94-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
In the commands below, the value of ''<name>'' will vary depending on the version of PostgreSQL used. <br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' includes the ''major.minor'' version of PostgreSQL, e.g., ''postgresql-9.4''<br />
<br />
For versions 8.x, the ''<name>'' is always ''postgresql'' (without the version signifier).<br />
<br />
==== Data Directory ====<br />
<br />
The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.<br />
<br />
For PostgreSQL version 9.0 and above, the default data directory is:<br />
/var/lib/pgsql/''<name>''/data<br />
<br />
For example: <br />
/var/lib/pgsql/9.4/data<br />
<br />
For versions 7.x and 8.x, default data directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA.<br />
service ''<name>'' initdb<br />
<br />
E.g. for version 9.4:<br />
service postgresql-9.4 initdb<br />
<br />
If the previous command did not work, try directly calling the setup binary, located in a similar naming scheme:<br />
/usr/pgsql-''y.x''/bin/postgresql''yx''-setup initdb<br />
<br />
E.g. for version 9.4:<br />
/usr/pgsql-9.4/bin/postgresql94-setup initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to start automatically when the OS starts:<br />
chkconfig ''<name>'' on<br />
<br />
E.g. for version 9.4:<br />
chkconfig postgresql-9.4 on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
<br />
E.g. to start version 9.4:<br />
service postgresql-9.4 start<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql94*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: maintainers@lists.pgrpms.org or pgsql-pkg-yum@postgresql.org (requires subscription)<br />
<br />
== After installation ==<br />
[[First steps]] in PostgreSQL<br />
<br />
<br />
[[Category:Install]]<br />
[[Category:Linux distribution]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=BDR_Project&diff=23255BDR Project2014-09-30T20:06:10Z<p>Mwtoews: typo</p>
<hr />
<div>BDR stands for '''B'''i'''D'''irectional '''R'''eplication. <br />
<br />
----<br />
This page discusses the BDR development project. If you're looking for the user/admin documentation, see [[BDR User Guide]].<br />
----<br />
<br />
Design work began in late 2011 to look at ways of adding new features to PostgreSQL core to support a flexible new infrastructure for replication that built upon and enhanced the existing streaming replication features added in 9.1-9.2. Initial design and project planning was by Simon Riggs; implementation lead is now Andres Freund, both from [http://www.2ndQuadrant.com/ 2ndQuadrant]. Various additional development contributions from the wider 2ndQuadrant team as well as reviews and input from other community devs.<br />
<br />
At the [[PgCon2012CanadaInCoreReplicationMeeting]] an inital version of the design was presented. A presentation containing reasons leading to the current design and a prototype of it, including preliminary performance results, is [[:File:BDR_Presentation_PGCon2012.pdf|available here]].<br />
<br />
= Project Overview and Plans =<br />
== Project Aims ==<br />
* To be included as part of the main PostgreSQL open source distribution<br />
* Fast<br />
* Re-usable individual parts (see below), usable by other projects (slony, ...)<br />
* Basis for easier sharding/write scalability<br />
* Wide geographic distribution of replicated nodes<br />
<br />
== High Level Planning ==<br />
=== 9.3 ===<br />
Fundamental changes have been made as part of 9.3 to support BDR; total of 16 separate commits on these and other smaller aspects<br />
<br />
* background workers<br />
* xlogreader implementation<br />
* pg_xlogdump<br />
<br />
Fully working implementation will be available for production use in 2013. At this stage, probably more than 50% of code exists out of core.<br />
<br />
Exact mechanism for dissemination is not yet announced; key objective is to develop code with the objective of being core/contrib modules. There is no long term plan for existence of code outside of core.<br />
<br />
=== 9.4 ===<br />
Objective to implement main BDR features into core Postgres.<br />
<br />
=== 9.5 ===<br />
Additional features based upon feedback<br />
<br />
== Aspects of BDR ==<br />
<br />
Bi-Directional Replication consists of a number of related features<br />
<br />
* Logical Log Streaming Replication - getting data from one master to another.<br />
* Global Sequences - ability to support sequences that work globally across a set of nodes<br />
* Conflict Detection & Resolution (options)<br />
* DDL Replication via Event Triggers<br />
<br />
Taken together these features will allow replication in both directions for any pair of servers. We could call this "multi-master replication", but the possibilities for constructing complex networks of servers allow much more than that, so we use the more general term bi-directional replication.<br />
<br />
Note that these features aren't "clustering" in the sense that Oracle RAC uses the term. There is no distributed lock manager, global transaction coordinator etc.. The vision here is interconnected yet still separate servers, allowing each server to have radically different workloads and yet still work together, even across global scale and large geographic separation.<br />
<br />
=== Other Terminology ===<br />
<br />
(Physical) Streaming replication talks about Master and Standby, so we could also talk about Master and Physical Standby, and then use Master and Logical Standby to describe LLSR. That terminology doesn't work when we consider that replication might be bi-directional, or at could be reconfigured that way in the future.<br />
<br />
Similarly, the terms Origin, Provider and Subcriber only work with one Origin.<br />
<br />
=== Tuning ===<br />
<br />
As a result of the architecture there are few physical tuning parameters. That may grow as the implementation matures, but not significantly.<br />
<br />
There are no parameters for tuning transfer latency.<br />
<br />
The only likely tunable is the amount of memory used to accumulate changes before we send them downstream. Similar in many ways to setting of <tt>shared_buffers</tt> and should be increased on larger machines.<br />
<br />
A variant of <tt>hot_standby_feedback</tt> could be implemented also, though would likely need renaming.<br />
<br />
The CRC check while reading WAL is not useful in this context and there will likely be an option to skip that for logical decoding since it can be a CPU bottleneck.<br />
<br />
== Operation ==<br />
<br />
BDR usage is described in [[BDR User Guide]].<br />
<br />
=== Selective Replication (Table/Row-level filtering) ===<br />
<br />
LLSR doesn't yet support selection of data at table or row level, only at database level. It is a design goal to be able to support this in the future.<br />
<br />
=== DDL replication ===<br />
<br />
DDL replication is supported using event triggers and DDL deparse, which is to be submitted to 9.5.<br />
<br />
BDR restricts some DDL. See [[BDR Command Restrictions]].<br />
<br />
= Core changes =<br />
<br />
== Logical changeset extraction ==<br />
<br />
Merged in 9.4.<br />
<br />
[[Category:Bi-Directional Replication]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=23080YUM Installation2014-09-03T12:08:14Z<p>Mwtoews: remove versions, since these are better obtained from repo page; add Oracle Enterprise Linux</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms; for the current release and prior release or two):<br />
* Fedora<br />
* Red Hat Enterprise Linux<br />
* CentOS<br />
* Scientific Linux<br />
* Oracle Enterprise Linux<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/repopackages.php Repository packages for different version combinations]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the postgresql supplied by the base repository):<br />
exclude=postgresql*<br />
<br />
=== Install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:<br />
yum localinstall http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.3 server:<br />
yum install postgresql93-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
In the commands below, the value of ''<name>'' will vary depending on the version of PostgreSQL used. <br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' includes the ''major.minor'' version of PostgreSQL, e.g., ''postgresql-9.3''<br />
<br />
For versions 8.x, the ''<name>'' is always ''postgresql'' (without the version signifier).<br />
<br />
==== Data Directory ====<br />
<br />
The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.<br />
<br />
For PostgreSQL version 9.0 and above, the default data directory is:<br />
/var/lib/pgsql/''<name>''/data<br />
<br />
For example: <br />
/var/lib/pgsql/9.3/data<br />
<br />
For versions 7.x and 8.x, default data directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA.<br />
service ''<name>'' initdb<br />
<br />
E.g. for version 9.3:<br />
service postgresql-9.3 initdb<br />
<br />
If the previous command did not work, try directly calling the setup binary, located in a similar naming scheme:<br />
/usr/pgsql-''y.x''/bin/postgresql''yx''-setup initdb<br />
<br />
E.g. for version 9.3:<br />
/usr/pgsql-9.3/bin/postgresql93-setup initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to start automatically when the OS starts:<br />
chkconfig ''<name>'' on<br />
<br />
E.g. for version 9.3:<br />
chkconfig postgresql-9.3 on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
<br />
E.g. to start version 9.3:<br />
service postgresql-9.3 start<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql93*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: maintainers@lists.pgrpms.org or pgsql-pkg-yum@postgresql.org (requires subscription)<br />
<br />
== After installation ==<br />
[[First steps]] in PostgreSQL<br />
<br />
<br />
[[Category:Install]]<br />
[[Category:Linux distribution]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=20873YUM Installation2013-09-28T09:03:32Z<p>Mwtoews: correction to restore "service postgresql-9.3 initdb" method</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms):<br />
* ''Fedora'' (versions 16 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 5 and up)<br />
* ''CentOS'' (versions 5 and up)<br />
* ''Scientific Linux'' (versions 5 and up)<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:<br />
curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.3 server:<br />
yum install postgresql93-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
In the commands below, the value of ''<name>'' will vary depending on the version of PostgreSQL used. <br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' includes the ''major.minor'' version of PostgreSQL, e.g., ''postgresql-9.3''<br />
<br />
For versions 8.x, the ''<name>'' is always ''postgresql'' (without the version signifier).<br />
<br />
==== Data Directory ====<br />
<br />
The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.<br />
<br />
For PostgreSQL version 9.0 and above, the default data directory is:<br />
/var/lib/pgsql/''<name>''/data<br />
<br />
For example: <br />
/var/lib/pgsql/9.3/data<br />
<br />
For versions 7.x and 8.x, default data directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA.<br />
service ''<name>'' initdb<br />
<br />
E.g. for version 9.3:<br />
service postgresql-9.3 initdb<br />
<br />
If the previous command did not work, try directly calling the setup binary, located in a similar naming scheme:<br />
/usr/pgsql-''y.x''/bin/postgresql''yx''-setup initdb<br />
<br />
E.g. for version 9.3:<br />
/usr/pgsql-9.3/bin/postgresql93-setup initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to start automatically when the OS starts:<br />
chkconfig ''<name>'' on<br />
<br />
E.g. for version 9.3:<br />
chkconfig postgresql-9.3 on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql93*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: maintainers@lists.pgrpms.org or pgsql-pkg-yum@postgresql.org (requires subscription)<br />
<br />
== After installation ==<br />
[[First steps]] in PostgreSQL<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]<br />
[[Category:Linux distribution]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=20867YUM Installation2013-09-26T11:40:39Z<p>Mwtoews: correction to new initdb method; use PostgreSQL 9.3 as example</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms):<br />
* ''Fedora'' (versions 16 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 5 and up)<br />
* ''CentOS'' (versions 5 and up)<br />
* ''Scientific Linux'' (versions 5 and up)<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:<br />
curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.3 server:<br />
yum install postgresql93-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
In the commands below, the value of ''<name>'' will vary depending on the version of PostgreSQL used. <br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' includes the ''major.minor'' version of PostgreSQL, e.g., ''postgresql-9.3''<br />
<br />
For versions 8.x, the ''<name>'' is always ''postgresql'' (without the version signifier).<br />
<br />
==== Data Directory ====<br />
<br />
The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.<br />
<br />
For PostgreSQL version 9.0 and above, the default data directory is:<br />
/var/lib/pgsql/''<name>''/data<br />
<br />
For example: <br />
/var/lib/pgsql/9.3/data<br />
<br />
For versions 7.x and 8.x, default data directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA. The setup binary is located in a similar naming scheme:<br />
/usr/pgsql-''y.x''/bin/postgresql''yx''-setup initdb<br />
<br />
E.g. for version 9.3:<br />
/usr/pgsql-9.3/bin/postgresql93-setup initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to start automatically when the OS starts:<br />
chkconfig ''<name>'' on<br />
<br />
E.g. for version 9.3:<br />
chkconfig postgresql-9.3 on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql93*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: maintainers@lists.pgrpms.org or pgsql-pkg-yum@postgresql.org (requires subscription)<br />
<br />
== After installation ==<br />
[[First steps]] in PostgreSQL<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]<br />
[[Category:Linux distribution]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=RPM_Installation&diff=20856RPM Installation2013-09-24T01:47:37Z<p>Mwtoews: see also YUM Installation</p>
<hr />
<div>== Installing PostgreSQL on Red Hat Enterprise Linux / Fedora Core ==<br />
<br />
Written by <br />
Devrim GÜNDÜZ (PGDG RPM Maintainer) devrim@PostgreSQL.org and<br />
Lamar Owen (PGDG RPM Maintainer) lowen@pari.edu<br />
<br />
(All contributions are welcome. Please feel free to drop us e-mail about your comments.)<br />
<br />
This article will talk about the details of installing PostgreSQL Database Server on Red Hat Enterprise Linux / Fedora Core. RPMs are ready-to-install binary files for many Linux distributions. Please note that this article is written for 8.1 and above, so some information may not fit your version.<br />
<br />
===General information===<br />
<br />
PostgreSQL Global Development Group (PGDG) builds RPMs for various Linux distributions. At the time of this writing, we have RPMs and SRPMs for the following platforms1:<br />
* Red Hat Linux 9,<br />
* Red Hat Enterprise Linux 2.1, 3.0 and 4 and<br />
* Fedora Core 1,2,3,4,5<br />
<br />
On PgFoundry, there is a project for building RPMs:<br />
http://pgfoundry.org/projects/pgsqlrpms<br />
<br />
Furthermore, there are -hackers, -general and -announce mailing lists. All discussions are open to everyone.<br />
<br />
===Obtaining the RPMs===<br />
<br />
The RPMs are available on PostgreSQL FTP site and all of its mirrors. For your convenience, you may try using web interface of PostgreSQL FTP site. That site will suggest you the suitable mirror(s) for you. The URL is:<br />
http://www.PostgreSQL.org/ftp<br />
<br />
===Which RPM for what purpose?===<br />
PGDG ships the following RPMs:<br />
<br />
* postgresql-libs : The postgresql-libs package provides the essential shared libraries for any PostgreSQL client program or interface. You will need to install this package to use any other PostgreSQL package or any clients that need to connect to a PostgreSQL server.<br />
<br />
* postgresql: If you want to manipulate a PostgreSQL database on a local or remote PostgreSQL server, you need this package. You also need to install this package if you're installing the postgresql-server package.<br />
<br />
* postgresql-contrib : The postgresql-contrib package contains contributed packages that are included in the PostgreSQL distribution.<br />
<br />
* postgresql-devel : The postgresql-devel package contains the header files and libraries needed to compile C or C++ applications which will directly interact with a PostgreSQL database management server and the ecpg Embedded C Postgres preprocessor. You need to install this package if you want to develop applications which will interact with a PostgreSQL server.<br />
<br />
* postgresql-docs : The postgresql-docs package includes the SGML source for the documentation as well as the documentation in PDF format and some extra documentation. Install this package if you want to help with the PostgreSQL documentation project, or if you want to generate printed documentation.<br />
<br />
* postgresql-server : The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create and maintain PostgreSQL databases. You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package and its requirements.<br />
<br />
* postgresql-tcl : The postgresql-tcl package contains the Pgtcl client library and its documentation.<br />
<br />
* postgresql-jdbc : The postgresql-jdbc package includes the .jar files needed for Java programs to access a PostgreSQL database.<br />
<br />
* postgresql-pl : The postgresql-pl package contains the the PL/Perl, and PL/Python procedural languages for the backend. PL/Pgsql is part of the core server package.<br />
<br />
* postgresql-python : The postgresql-python package includes a module for developers to use when writing Python code for accessing a PostgreSQL database.<br />
<br />
* postgresql-test : The postgresql-test package includes the sources and pre-built binaries of various tests for the PostgreSQL database management system, including regression tests and benchmarks.<br />
<br />
===Which packages should I use?===<br />
<br />
If you feel lazy about reading the descriptions above, here is a shortcut for minimal scenarios:<br />
<br />
* If you want to run a PostgreSQL server, install postgresql-libs, postgresql and postgresql-server.<br />
* If you want to run a client, install postgresql-libs and postgresql rpms.<br />
<br />
For some cases, you might just want to install postgresql-libs for some packages like PHP.<br />
<br />
Package names also include version and architecture information. Official PostgreSQL Global Development Group RPM's have a 'PGDG' after the release number. Other RPMset's as distributed with Linux distributions may have a different release number and initials. The version numbering is the same as PostgreSQL.<br />
<br />
It is preferable for the distribution-specific set to be the one used, as the PGDG set is intentionally generic. So, if your distro has a set of RPMs, use them in preference. If you want to stay up-to-date on the PostgreSQL core itself, use the PGDG generic set -- but understand that it is a GENERIC set.<br />
<br />
These RPMs no longer support any sort of upgrading process other than that documented in the regular documentation. That is, you must dump, upgrade, initdb, and restore your data. You must remove the old server subpackage, install the new package and restore the data from dump.<br />
<br />
===RPM File Locations===<br />
<br />
To be in compliance with the Linux FHS, the PostgreSQL PGDG RPMs install files in a manner not consistent with most of the PostgreSQL documentation. According to the standard PostgreSQL documentation, PostgreSQL is installed under the directory /usr/local/pgsql, with executables, source, and data existing in various subdirectories.<br />
<br />
Different distributions have different ideas of some of these file locations. In particular, the documentation directory can be /usr/doc, /usr/doc/packages, /usr/share/doc, /usr/share/doc/packages, or some other similar path. The Red Hat / Fedora Core locations are listed below:<br />
<br />
* Executables : /usr/bin<br />
* Libraries : /usr/lib<br />
* Documentation : /usr/share/doc/postgresql-x.y.z , /usr/share/doc/postgresql-x.y.z/contrib<br />
* Contrib : /usr/share/pgsql/contrib<br />
* Data : /var/lib/pgsql/data<br />
* Backup area : /var/lib/pgsql/backup<br />
* Templates : /usr/share/pgsql<br />
* Procedural Languages : /usr/lib/pgsql<br />
* Development Headers : /usr/include/pgsql<br />
* Other shared data : /usr/share/pgsql<br />
* Regression tests : /usr/lib/pgsql/test/regress (in the -test package)<br />
* Documentation SGML : /usr/share/doc/postgresql-docs-x.y.z<br />
<br />
The above list references the Red Hat / Fedora Core structure. These locations may change for other distributions. Use of 'rpm -ql' for each package is recommended as the 'Official' location source.<br />
<br />
These RPMs are designed to be LSB-compliant -- if you find this not to be the case, please let us know by way of the pgsqlrpms-hackers@PgFoundry.org mailing list.<br />
<br />
<br />
===Installing and Upgrading PostgreSQL RPMs===<br />
<br />
Installing PGDG RPMs are as easy as installing any RPMs<br />
rpm -ivh package_name.version.arch.rpm<br />
<br />
Unless specified, on minor release upgrades (i.e., upgrading from 8.1.0 to 8.1.1 or 8.1.3, etc1), you may use usual RPM upgrade process:<br />
rpm -Uvh package_name.version.arch.rpm<br />
<br />
Please note that on every new major version upgrade, you have to follow dump/reload sequence. However please don’t forget to read the Release Notes before upgrading because on some cases minor versions may need a dump/reload process. At those times if you use -U switch, then you will probably lose data! Please refer to “How do I perform a major upgrade?” section.<br />
<br />
Many of the RPMs are signed with the PGP key of the package builder. Directories contain a CURRENT_MAINTAINER file which includes the name/email of the package builder and link to their PGP key. You might want to import the signature before you’ll install the RPM:<br />
rpm –import http://link/to/the/pgp/key<br />
<br />
Unless this is not done, you can install/upgrade the RPMs but you’ll be thrown a warning.<br />
You may subscribe to pgsqlrpms-general@pgfoundry.org list for more details.<br />
<br />
===Removing RPMS===<br />
<br />
You might want to take a full dump (and possibly a filesystem level backup) before removing RPMs. Removing an PostgreSQL RPM is as easy as removing any RPM:<br />
rpm -e package_name<br />
<br />
Before the removal of server package, if there are any running processes, all are stopped. You don’t need to stop it.<br />
<br />
===Starting PostgreSQL for the first time and init script===<br />
<br />
Red Hat Linux uses the System V Init package. A startup script for PostgreSQL is provided in the server package, as <br />
/etc/rc.d/init.d/postgresql. To start the postmaster, with sanity checking, as root, run<br />
service postgresql start<br />
<br />
To shut the postmaster down,<br />
service postgresql stop<br />
<br />
There are other parameters to this script -- execute 'service postgresql' for a listing.<br />
<br />
On some cases you might want to edit this init file. For example, you might want to pass a –locale=... parameter to initdb, etc. <br />
<br />
This script does the following sets defaults for configuration variables and then performs the given action (stop, start, etc). During the startup, first the script checks whether the database cluster has been initialized or not. If not, the initialization is done. Then the service is started as usual. The initdb step is skipped if initialization has been performed before. Usually this is done in the fresh installs.<br />
<br />
There is also a “stop” parameter for stopping database server.<br />
<br />
You may also reload the server for some changes to take effect; but please look at the PostgreSQL documentation for the conditions that the database server needs a restart or a reload.<br />
<br />
===Starting PostgreSQL automatically at system startup===<br />
<br />
To get this script to run at system startup run:<br />
chkconfig postgresql on<br />
and the proper symlinks will be created. See the chkconfig man page for more information. Note that this is manual -- while the startup script can include tags to allow chkconfig to automatically perform the symlinking, this is not done at this time.<br />
<br />
===How do I perform a major upgrade?===<br />
<br />
Currently, PostgreSQL RPMs does not provide a data upgrade feature among major releases (or clearly, upgrades that require an initdb.) This work in under progress. In order to upgrade to a major version, you should follow the following steps:<br />
<br />
* Take a full dump using pg_dumpall<br />
<br />
* You might want to take a filesystem-level backup also. This is intentional.<br />
<br />
* Check the backups! (Do it again!)<br />
<br />
* Now, stop the database server:<br />
/sbin/service postgresql stop<br />
* Remove all postgresql rpms. Please note that you'll probably need a --nodeps switch at the end:<br />
/bin/rpm -e `/bin/rpm -qa|grep postgresql^`<br />
/bin/rpm -e `/bin/rpm -qa|grep postgresql^` --nodeps<br />
<br />
During the removal of packages, some scripts will be run to remove postgres user and to uninstall postgresql service, etc. <br />
<br />
Please note that removing of postgresql-server RPM will not also remove /var/lib/pgsql<br />
<br />
* Remove database cluster:<br />
/bin/rm -rf /var/lib/pgsql (Better: /bin/rm -rf ~postgres or best take a filesystem-level backup of this directory by using /bin/mv for example)<br />
<br />
* Install new RPM sets<br />
<br />
* Start database server<br />
/sbin/service postgresql start<br />
<br />
* Edit conf files, if you need.<br />
<br />
* Reload the data to the new server (You may need to edit your data).<br />
... and you're done!<br />
<br />
===Rebuilding from Source RPM===<br />
<br />
If your distribution is not supported by the binary RPM's from PostgreSQL.org, you will need to rebuild from the source RPM. Download the .src.rpm for this release. You will need to be root to rebuild, unless you have already set up a non-root build environment.<br />
<br />
Install the source RPM with rpm -i, then CD to the rpm building area (on Red Hat or Fedora Core this is /usr/src/redhat by default). You will have to have a full development environment to rebuild the full RPM set.<br />
<br />
<br />
<br />
* This release of the RPMset includes the ability to conditionally build sets of packages. The parameters, their defaults, and the meanings are:<br />
<br />
* beta : 0 - #build with cassert and do not strip the binaries<br />
* tcl : 1 - #build the postgresql-tcl package.<br />
* jdbc : 1- #build the postgresql-jdbc package.<br />
* pls : 1 - #build the postgresql-pl package.<br />
* test : 1 - #build the postgresql-test package.<br />
* python : 1 - #build the postgresql-python package.<br />
* pltcl : 1 - #build the pltcl portion of the postgresql-pl package.<br />
* plperl : 1 - #build the plperl portion of the postgresql-pl package.<br />
* ssl : 1 - #use OpenSSL support.<br />
* kerberos : 1 - #use Kerberos 5 support.<br />
* nls : 1 - #build with national language support.<br />
* pam : 1 : #build with PAM support.<br />
* runselftest : 1 - #do "make check" during the build.<br />
* xml : 1 - #build contrib/xml2<br />
* pgfts : 0 - #Build with –enable-thread-safety<br />
<br />
<br />
To use these defines, invoke a rebuild like this:<br />
rpm --rebuild --define 'perl 0' --define 'tcl 0' \ <br />
--define 'test 0' --define 'runselftest 1' \<br />
--define 'kerberos 0' postgresql-8.1.0-2PGDG.src.rpm<br />
<br />
This line would disable the perl, tcl, and test subpackages, enable the regression test run during build, and disable kerberos support. You might need to disable runselftest if there is an installed version of PostgreSQL that is a different major version from what you are trying to build. The self test tends to pick up the installed libpq.so shared library in place of the one being built :-(, so if that isn't compatible the test will fail. Also, you can't use runselftest when doing the build as root.<br />
<br />
More of these conditionals may be added/removed in the future.<br />
<br />
=== Contrib Files===<br />
<br />
The contents of the contrib tree are packaged into the -contrib subpackage and are processed with make and make install. There is documentation in /usr/share/doc/postgresql-contrib-VERSION for these modules. Most of the modules are in /usr/lib/pgsql for loadable modules, and binaries are in /usr/bin. In the future these files may be split out, depending upon function and dependencies.<br />
<br />
=== After Installation ===<br />
[[First steps]]<br />
<br />
===More Information===<br />
<br />
You can get more information at http://www.postgresql.org<br />
<br />
Please help make this packaging better -- let us know if you find problems, or better ways of doing things. You can reach us by e-mail at pgsqlrpms-hackers@PgFoundry.org.<br />
<br />
== See also ==<br />
* [[YUM Installation]]<br />
<br />
[[Category:Install]]<br />
[[Category:Linux distribution]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Fdw&diff=19453Fdw2013-04-27T03:35:09Z<p>Mwtoews: Redirected page to Foreign data wrappers</p>
<hr />
<div>#REDIRECT[[Foreign data wrappers]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=FDW&diff=19452FDW2013-04-27T03:34:56Z<p>Mwtoews: Redirected page to Foreign data wrappers</p>
<hr />
<div>#REDIRECT[[Foreign data wrappers]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Foreign_data_wrapper&diff=19451Foreign data wrapper2013-04-27T03:34:30Z<p>Mwtoews: Redirected page to Foreign data wrappers</p>
<hr />
<div>#REDIRECT[[Foreign data wrappers]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=What%27s_new_in_PostgreSQL_9.2&diff=18211What's new in PostgreSQL 9.22012-09-10T19:36:19Z<p>Mwtoews: /* Have EXTRACT of a non-timezone-aware value measure the epoch from local midnight, not UTC midnight */ With 9.2?</p>
<hr />
<div>{{Languages}}<br />
<br />
This document showcases many of the latest developments in PostgreSQL 9.2, compared to the last major release &ndash; 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''.<br />
<br />
<br />
=Major new features=<br />
<br />
==Index-only scans <!-- Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane -->==<br />
<br />
In PostgreSQL, indexes have no "visibility" information. It means that when you access a record by its index, PostgreSQL has to visit the real tuple in the table to be sure it is visible to you: the tuple the index points to may simply be an old version of the record you are looking for.<br />
<br />
It can be a very big performance problem: the index is mostly ordered, so accessing its records is quite efficient, while the records may be scattered all over the place (that's a reason why PostgreSQL has a cluster command, but that's another story). In 9.2, PostgreSQL will use an "Index Only Scan" when possible, and not access the record itself if it doesn't need to.<br />
<br />
There is still no visibility information in the index. So in order to do this, PostgreSQL uses the visibility map ([http://www.postgresql.org/docs/devel/static/storage-vm.html visibility map]) , which tells it whether the whole content of a (usually) 8K page is visible to all transactions or not. When the index record points to a tuple contained in an «all visible» page, PostgreSQL won't have to access the tuple, it will be able to build it directly from the index. Of course, all the columns requested by the query must be in the index.<br />
<br />
The visibility map is maintained by VACUUM (it sets the visible bit), and by the backends doing SQL work (they unset the visible bit).<br />
<br />
If the data has been read only since the last VACUUM then the data is All Visible and the index only scan feature can improve performance.<br />
<br />
Here is an example.<br />
<br />
CREATE TABLE demo_ios (col1 float, col2 float, col3 text);<br />
<br />
In this table, we'll put random data, in order to have "scattered" data. We'll put 100 million records, to have a big recordset, and have it not fit in memory (that's a 4GB-ram machine). This is an ideal case, made for this demo. The gains won't be that big in real life.<br />
<br />
INSERT INTO demo_ios SELECT generate_series(1,100000000),random(), 'mynotsolongstring';<br />
<br />
SELECT pg_size_pretty(pg_total_relation_size('demo_ios'));<br />
pg_size_pretty <br />
----------------<br />
6512 MB<br />
<br />
Let's pretend that the query is this:<br />
<br />
SELECT col1,col2 FROM demo_ios where col2 BETWEEN 0.01 AND 0.02<br />
<br />
In order to use an index only scan on this query, we need an index on col2,col1 (col2 first, as it is used in the WHERE clause).<br />
<br />
CREATE index idx_demo_ios on demo_ios(col2,col1);<br />
<br />
We vacuum the table, so that the visibility map to be up-to-date:<br />
<br />
VACUUM demo_ios;<br />
<br />
All the timing you'll see below are done on a cold OS and PostgreSQL cache (that's where the gains are, as the purpose on Index Only Scans is to reduce I/O).<br />
<br />
Let's first try without Index Only Scans:<br />
<br />
SET enable_indexonlyscan to off;<br />
<br />
EXPLAIN (analyze,buffers) select col1,col2 FROM demo_ios where col2 between 0.01 and 0.02;<br />
QUERY PLAN <br />
----------------------------------------------------------------------------------------------------------------------------------------<br />
Bitmap Heap Scan on demo_ios (cost=25643.01..916484.44 rows=993633 width=16) (actual time=763.391..362963.899 rows=1000392 loops=1)<br />
Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))<br />
Rows Removed by Index Recheck: 68098621<br />
Buffers: shared hit=2 read=587779<br />
-> Bitmap Index Scan on idx_demo_ios (cost=0.00..25394.60 rows=993633 width=0) (actual time=759.011..759.011 rows=1000392 loops=1)<br />
Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))<br />
Buffers: shared hit=2 read=3835<br />
Total runtime: 364390.127 ms<br />
<br />
<br />
With Index Only Scans:<br />
<br />
explain (analyze,buffers) select col1,col2 from demo_ios where col2 between 0.01 and 0.02;<br />
QUERY PLAN <br />
-----------------------------------------------------------------------------------------------------------------------------------------------<br />
Index Only Scan using idx_demo_ios on demo_ios (cost=0.00..35330.93 rows=993633 width=16) (actual time=58.100..3250.589 rows=1000392 loops=1)<br />
Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))<br />
Heap Fetches: 0<br />
Buffers: shared hit=923073 read=3848<br />
Total runtime: 4297.405 ms<br />
<br />
<br />
<br />
As nothing is free, there are a few things to keep in mind:<br />
<br />
* Adding indexes for index only scans obviously adds indexes to your table. So updates will be slower.<br />
* You will index columns that weren't indexed before. So there will be less opportunities for HOT updates.<br />
* Gains will probably be smaller in real life situations, especially when data is changed between VACUUMs<br />
<br />
This required making visibility map changes crash-safe, so visibility map bit changes are now WAL-logged.<br />
<br />
==Replication improvements <!-- Fujii Masao, Simon Riggs, Magnus Hagander, Jun Ishizuka -->==<br />
<br />
Streaming Replication becomes more polished with this release. <br />
<br />
One of the main remaining gripes about streaming replication is that all the slaves have to be connected to the same and unique master, consuming its resources. Moreover, in case of a failover, it could be complicated to reconnect all the remaining slaves to the newly promoted master, if one is not using a tool like repmgr. <br />
<br />
With 9.2, a standby can also send replication changes, allowing cascading replication.<br />
<br />
Let's build this. We start with an already working 9.2 database.<br />
<br />
We set it up for replication:<br />
<br />
postgresql.conf:<br />
wal_level=hot_standby #(could be archive too)<br />
max_wal_senders=5<br />
hot_standby=on<br />
<br />
You'll probably also want to activate archiving in production, it won't be done here.<br />
<br />
pg_hba.conf (do not use trust in production):<br />
host replication replication_user 0.0.0.0/0 md5<br />
<br />
Create the user:<br />
create user replication_user replication password 'secret';<br />
<br />
Clone the cluster:<br />
<br />
pg_basebackup -h localhost -U replication_user -D data2<br />
Password:<br />
<br />
We have a brand new cluster in the data2 directory. We'll change the port so that it can start (postgresql.conf), as both clusters are running on the same machine:<br />
port=5433<br />
<br />
We add a recovery.conf to tell it how to stream from the master database:<br />
standby_mode = on<br />
primary_conninfo = 'host=localhost port=5432 user=replication_user password=secret' <br />
<br />
pg_ctl -D data2 start<br />
server starting<br />
LOG: database system was interrupted; last known up at 2012-07-03 17:58:09 CEST<br />
LOG: creating missing WAL directory "pg_xlog/archive_status"<br />
LOG: entering standby mode<br />
LOG: streaming replication successfully connected to primary<br />
LOG: redo starts at 0/9D000020<br />
LOG: consistent recovery state reached at 0/9D0000B8<br />
LOG: database system is ready to accept read only connections<br />
<br />
Now, let's add a second slave, which will use this slave:<br />
<br />
pg_basebackup -h localhost -U replication_user -D data3 -p 5433<br />
Password: <br />
<br />
We edit data3's postgresql.conf to change the port:<br />
port=5434<br />
<br />
We modify the recovery.conf to stream from the slave:<br />
standby_mode = on<br />
primary_conninfo = 'host=localhost port=5433 user=replication_user password=secret' # e.g. 'host=localhost port=5432'<br />
<br />
We start the third cluster:<br />
pg_ctl -D data3 start<br />
server starting<br />
LOG: database system was interrupted while in recovery at log time 2012-07-03 17:58:09 CEST<br />
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.<br />
LOG: creating missing WAL directory "pg_xlog/archive_status"<br />
LOG: entering standby mode<br />
LOG: streaming replication successfully connected to primary<br />
LOG: redo starts at 0/9D000020<br />
LOG: consistent recovery state reached at 0/9E000000<br />
LOG: database system is ready to accept read only connections<br />
<br />
Now, everything modified on the master cluster get streamed to the first slave, and from there to the second slave. This second replication has to be monitored from the first slave (the master knows nothing about it).<br />
<br />
<br />
As you may have noticed from the example, pg_basebackup now works from slaves.<br />
<br />
There is another use case that wasn't covered: what if a user didn't care for having a full fledged slave, and only wanted to stream the WAL files to another location, to benefit from the reduced data loss without the burden of maintaining a slave ?<br />
<br />
pg_receivexlog is provided just for this purpose: it pretends to be a PostgreSQL slave, but only stores the log files as they are streamed, in a directory:<br />
pg_receivexlog -D /tmp/new_logs -h localhost -U replication_user<br />
<br />
will connect to the master (or a slave), and start creating files: <br />
ls /tmp/new_logs/<br />
00000001000000000000009E.partial<br />
<br />
Files are of the segment size, so they can be used for a normal recovery of the database. It's the same as an archive command, but with a much smaller granularity.<br />
<br />
Remember to rename the last segment to remove the .partial suffix before using it with a PITR restore or any other operation.<br />
<br />
The synchronous_commit parameter has a new value: remote_write. It can be used when there is a synchronous slave (synchronous_standby_names is set), meaning that the master doesn't have to wait for the slave to have written the data to disk, only for the slave to have acknowledged the data. With this set, data is protected from a crash on the master, but could still be lost if the slave crashed at the same time (i.e. before having written the in flight data to disk). As this is a quite remote possibility, and the performance improvement will be large, some people will be interested in this compromise.<br />
<br />
==JSON datatype==<br />
<br />
The JSON datatype is meant for storing JSON-structured data. It will validate that the input JSON string is correct JSON:<br />
<br />
=# SELECT '{"username":"john","posts":121,"emailaddress":"john@nowhere.com"}'::json;<br />
json <br />
-------------------------------------------------------------------<br />
{"username":"john","posts":121,"emailaddress":"john@nowhere.com"}<br />
(1 row)<br />
<br />
=# SELECT '{"username","posts":121,"emailaddress":"john@nowhere.com"}'::json;<br />
ERROR: invalid input syntax for type json at character 8<br />
DETAIL: Expected ":", but found ",".<br />
CONTEXT: JSON data, line 1: {"username",...<br />
STATEMENT: SELECT '{"username","posts":121,"emailaddress":"john@nowhere.com"}'::json;<br />
ERROR: invalid input syntax for type json<br />
LINE 1: SELECT '{"username","posts":121,"emailaddress":"john@nowhere...<br />
^<br />
DETAIL: Expected ":", but found ",".<br />
CONTEXT: JSON data, line 1: {"username",...<br />
<br />
You can also convert a row type to JSON:<br />
<br />
=#SELECT * FROM demo ;<br />
username | posts | emailaddress <br />
----------+-------+---------------------<br />
john | 121 | john@nowhere.com<br />
mickael | 215 | mickael@nowhere.com<br />
(2 rows)<br />
<br />
=# SELECT row_to_json(demo) FROM demo;<br />
row_to_json <br />
-------------------------------------------------------------------------<br />
{"username":"john","posts":121,"emailaddress":"john@nowhere.com"}<br />
{"username":"mickael","posts":215,"emailaddress":"mickael@nowhere.com"}<br />
(2 rows)<br />
<br />
Or an array type:<br />
<br />
<br />
=# select array_to_json(array_agg(demo)) from demo;<br />
array_to_json <br />
---------------------------------------------------------------------------------------------------------------------------------------------<br />
[{"username":"john","posts":121,"emailaddress":"john@nowhere.com"},{"username":"mickael","posts":215,"emailaddress":"mickael@nowhere.com"}]<br />
(1 row)<br />
<br />
== Range Types ==<br />
<br />
Range types are used to store a range of data of a given type. There are a few pre-defined types. They are integer (int4range), bigint (int8range), numeric (numrange), timestamp without timezone (tsrange), timestamp with timezone (tstzrange), and date (daterange).<br />
<br />
Ranges can be made of continuous (numeric, timestamp...) or discrete (integer, date...) data types. They can be open (the bound isn't part of the range) or closed (the bound is part of the range). A bound can also be infinite.<br />
<br />
Without these datatypes, most people solve the range problems by using two columns in a table. These range types are much more powerful, as you can use many operators on them.<br />
<br />
Here is the intersection between then 1000(open)-2000(closed) and 1000(closed)-1200(closed) numeric range:<br />
<br />
SELECT '(1000,2000]'::numrange * '[1000,1200]'::numrange;<br />
?column? <br />
-------------<br />
(1000,1200]<br />
(1 row)<br />
<br />
So you can query on things like: «give me all ranges that intersect this»:<br />
<br />
=# SELECT * from test_range ;<br />
period <br />
-----------------------------------------------------<br />
["2012-01-01 00:00:00+01","2012-01-02 12:00:00+01"]<br />
["2012-01-01 00:00:00+01","2012-03-01 00:00:00+01"]<br />
["2008-01-01 00:00:00+01","2015-01-01 00:00:00+01"]<br />
(3 rows)<br />
<br />
<br />
=# SELECT * FROM test_range WHERE period && '[2012-01-03 00:00:00,2012-01-03 12:00:00]'; <br />
period <br />
-----------------------------------------------------<br />
["2012-01-01 00:00:00+01","2012-03-01 00:00:00+01"]<br />
["2008-01-01 00:00:00+01","2015-01-01 00:00:00+01"]<br />
(2 rows)<br />
<br />
This query could use an index defined like this:<br />
<br />
=# CREATE INDEX idx_test_range on test_range USING gist (period);<br />
<br />
You can also use these range data types to define exclusion constraints:<br />
<br />
CREATE EXTENSION btree_gist ;<br />
CREATE TABLE reservation (room_id int, period tstzrange);<br />
ALTER TABLE reservation ADD EXCLUDE USING GIST (room_id WITH =, period WITH &&);<br />
<br />
This means that now it is forbidden to have two records in this table where room_id is equal and period overlaps. The extension btree_gist is required to create a GiST index on room_id (it's an integer, it is usually indexed with a btree index).<br />
<br />
=# INSERT INTO reservation VALUES (1,'(2012-08-23 14:00:00,2012-08-23 15:00:00)');<br />
INSERT 0 1<br />
=# INSERT INTO reservation VALUES (2,'(2012-08-23 14:00:00,2012-08-23 15:00:00)');<br />
INSERT 0 1<br />
=# INSERT INTO reservation VALUES (1,'(2012-08-23 14:45:00,2012-08-23 15:15:00)');<br />
ERROR: conflicting key value violates exclusion constraint "reservation_room_id_period_excl"<br />
DETAIL: Key (room_id, period)=(1, ("2012-08-23 14:45:00+02","2012-08-23 15:15:00+02")) <br />
conflicts with existing key (room_id, period)=(1, ("2012-08-23 14:00:00+02","2012-08-23 15:00:00+02")).<br />
STATEMENT: INSERT INTO reservation VALUES (1,'(2012-08-23 14:45:00,2012-08-23 15:15:00)');<br />
<br />
One can also declare new range types.<br />
<br />
=Performance improvements=<br />
<br />
This version has performance improvements on a very large range of domains (non-exaustive):<br />
<br />
* The most visible will probably be the Index Only Scans, which has already been introduced in this document.<br />
<br />
* The lock contention of several big locks has been significantly reduced, leading to better multi-processor scalability, for machines with over 32 cores mostly. <!-- Robert Haas --><br />
<br />
* The performance of in-memory sorts has been improved by up to 25% in some situations, with certain specialized sort functions introduced. <!-- Peter Geoghegan --><br />
<br />
* An idle PostgreSQL server now makes less wakeups, leading to lower power consumption<!--Peter Geoghegan-->. This is especially useful on virtualized and embedded environments.<br />
<br />
* COPY has been improved, it will generate less WAL volume and fewer locks of a table's pages. <!-- Heikki Linnakangas --><br />
<br />
* Statistics are collected on array contents<!-- Alexander Korotkov -->, allowing for better estimations of selectivity on array operations.<br />
<br />
* Text-to-anytype concatenation and quote_literal/quote_nullable functions are not volatile any more, enabling better optimization in some cases <!-- Marti Raudsepp --><br />
<br />
* The system can now track IO durations <!--Ants Aasma --><br />
<br />
This one deserves a little explanation, as it can be a little tricky. Tracking IO durations means asking repeatedly the time to the operating system. Depending on the operating system and the hardware, this can be quite cheap, or extremely costly. The most import factor here is where the system gets its time from. It could be directly retrieved from the processor (TSC), dedicated hardware such as HPET, or an ACPI call. What's most important is that the cost of getting time can vary from a factor of thousands.<br />
<br />
If you are interested in this timing data, it's better to first check if your system will support it without too much of a performance hit. PostgreSQL provides you with the pg_test_timing tool:<br />
<br />
<pre><br />
$ pg_test_timing <br />
Testing timing overhead for 3 seconds.<br />
Per loop time including overhead: 28.02 nsec<br />
Histogram of timing durations:<br />
< usec: count percent<br />
32: 41 0.00004%<br />
16: 1405 0.00131%<br />
8: 200 0.00019%<br />
4: 388 0.00036%<br />
2: 2982558 2.78523%<br />
1: 104100166 97.21287%<br />
</pre><br />
<br />
Here, everything is good: getting time costs around 28 nanoseconds, and has a very small variation. Anything under 100 nanoseconds should be good for production. If you get higher values, you may still find a way to tune your system. You'd better check on the [http://www.postgresql.org/docs/9.2/static/pgtesttiming.html documentation].<br />
<br />
Anyway, here is the data you'll be able to collect if your system is ready for this:<br />
<br />
First, you'll get per-database statistics, which will now give accurate informations about which database is doing most I/O:<br />
<br />
<pre><br />
=# SELECT * FROM pg_stat_database WHERE datname = 'mydb';<br />
-[ RECORD 1 ]--+------------------------------<br />
datid | 16384<br />
datname | mydb<br />
numbackends | 1<br />
xact_commit | 270<br />
xact_rollback | 2<br />
blks_read | 1961<br />
blks_hit | 17944<br />
tup_returned | 269035<br />
tup_fetched | 8850<br />
tup_inserted | 16<br />
tup_updated | 4<br />
tup_deleted | 45<br />
conflicts | 0<br />
temp_files | 0<br />
temp_bytes | 0<br />
deadlocks | 0<br />
blk_read_time | 583.774<br />
blk_write_time | 0<br />
stats_reset | 2012-07-03 17:18:54.796817+02<br />
</pre><br />
We see here that mydb has only consumed 583.774 milliseconds of read time.<br />
<br />
Explain will benefit from this too:<br />
<pre><br />
=# EXPLAIN (analyze,buffers) SELECT count(*) FROM mots ;<br />
QUERY PLAN<br />
----------------------------------------------------------------------------------------------------------------<br />
Aggregate (cost=1669.95..1669.96 rows=1 width=0) (actual time=21.943..21.943 rows=1 loops=1)<br />
Buffers: shared read=493<br />
I/O Timings: read=2.578<br />
-> Seq Scan on mots (cost=0.00..1434.56 rows=94156 width=0) (actual time=0.059..12.933 rows=94156 loops=1)<br />
Buffers: shared read=493<br />
I/O Timings: read=2.578<br />
Total runtime: 22.059 ms<br />
</pre><br />
We now have a separate information about the time taken to retrieve data from the operating system. Obviously, here, the data was in the operating system's cache (2 milliseconds to read 493 blocks).<br />
<br />
And last, if you have enabled pg_stat_statements:<br />
<pre><br />
select * from pg_stat_statements where query ~ 'words';<br />
-[ RECORD 1 ]-------+---------------------------<br />
userid | 10<br />
dbid | 16384<br />
query | select count(*) from words;<br />
calls | 2<br />
total_time | 78.332<br />
rows | 2<br />
shared_blks_hit | 0<br />
shared_blks_read | 986<br />
shared_blks_dirtied | 0<br />
shared_blks_written | 0<br />
local_blks_hit | 0<br />
local_blks_read | 0<br />
local_blks_dirtied | 0<br />
local_blks_written | 0<br />
temp_blks_read | 0<br />
temp_blks_written | 0<br />
blk_read_time | 58.427<br />
blk_write_time | 0<br />
</pre><br />
<br />
* As for every version, the optimizer has received its share of improvements <!-- Tom Lane--><br />
** Prepared statements used to be optimized once, without any knowledge of the parameters' values. With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.<br />
** A new feature has been added: parameterized paths. Simply put, it means that a sub-part of a query plan can use parameters it has got from a parent node. It fixes several bad plans that could occur, especially when the optimizer couldn't reorder joins to put nested loops where it would have been efficient.<br />
<br />
This example is straight from the developpers mailing lists <!-- Andres Freund -->:<br />
<br />
<pre><br />
CREATE TABLE a (<br />
a_id serial PRIMARY KEY NOT NULL,<br />
b_id integer<br />
);<br />
CREATE INDEX a__b_id ON a USING btree (b_id);<br />
<br />
<br />
CREATE TABLE b (<br />
b_id serial NOT NULL,<br />
c_id integer<br />
);<br />
CREATE INDEX b__c_id ON b USING btree (c_id);<br />
<br />
<br />
CREATE TABLE c (<br />
c_id serial PRIMARY KEY NOT NULL,<br />
value integer UNIQUE<br />
);<br />
<br />
INSERT INTO b (b_id, c_id)<br />
SELECT g.i, g.i FROM generate_series(1, 50000) g(i);<br />
<br />
INSERT INTO a(b_id)<br />
SELECT g.i FROM generate_series(1, 50000) g(i);<br />
<br />
INSERT INTO c(c_id,value)<br />
VALUES (1,1);<br />
</pre><br />
<br />
So we have a referencing b, b referencing c.<br />
<br />
Here is an example of a query working badly with PostgreSQL 9.1:<br />
<br />
<pre><br />
EXPLAIN ANALYZE SELECT 1<br />
FROM<br />
c<br />
WHERE<br />
EXISTS (<br />
SELECT *<br />
FROM a<br />
JOIN b USING (b_id)<br />
WHERE b.c_id = c.c_id)<br />
AND c.value = 1;<br />
QUERY PLAN<br />
-----------------------------------------------------------------------------------------------------------------------<br />
Nested Loop Semi Join (cost=1347.00..3702.27 rows=1 width=0) (actual time=13.799..13.802 rows=1 loops=1)<br />
Join Filter: (c.c_id = b.c_id)<br />
-> Index Scan using c_value_key on c (cost=0.00..8.27 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)<br />
Index Cond: (value = 1)<br />
-> Hash Join (cost=1347.00..3069.00 rows=50000 width=4) (actual time=13.788..13.788 rows=1 loops=1)<br />
Hash Cond: (a.b_id = b.b_id)<br />
-> Seq Scan on a (cost=0.00..722.00 rows=50000 width=4) (actual time=0.007..0.007 rows=1 loops=1)<br />
-> Hash (cost=722.00..722.00 rows=50000 width=8) (actual time=13.760..13.760 rows=50000 loops=1)<br />
Buckets: 8192 Batches: 1 Memory Usage: 1954kB<br />
-> Seq Scan on b (cost=0.00..722.00 rows=50000 width=8) (actual time=0.008..5.702 rows=50000 loops=1)<br />
Total runtime: 13.842 ms<br />
</pre><br />
<br />
Not that bad, 13 milliseconds. Still, we are doing sequential scans on a and b, when our common sense tells us that c.value=1 should be used to filter rows more aggressively.<br />
<br />
Here's what 9.2 does with this query:<br />
<br />
<pre><br />
QUERY PLAN<br />
----------------------------------------------------------------------------------------------------------------------------<br />
Nested Loop Semi Join (cost=0.00..16.97 rows=1 width=0) (actual time=0.035..0.037 rows=1 loops=1)<br />
-> Index Scan using c_value_key on c (cost=0.00..8.27 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)<br />
Index Cond: (value = 1)<br />
-> Nested Loop (cost=0.00..8.69 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)<br />
-> Index Scan using b__c_id on b (cost=0.00..8.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)<br />
Index Cond: (c_id = c.c_id)<br />
-> Index Only Scan using a__b_id on a (cost=0.00..0.35 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)<br />
Index Cond: (b_id = b.b_id)<br />
Total runtime: 0.089 ms<br />
</pre><br />
<br />
The «parameterized path» is:<br />
<pre><br />
-> Nested Loop (cost=0.00..8.69 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)<br />
-> Index Scan using b__c_id on b (cost=0.00..8.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)<br />
Index Cond: (c_id = c.c_id)<br />
-> Index Only Scan using a__b_id on a (cost=0.00..0.35 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)<br />
Index Cond: (b_id = b.b_id)<br />
Total runtime: 0.089 ms<br />
</pre><br />
<br />
This part of the plan depends on a parent node (c_id=c.c_id). This part of the plan is called each time with a different parameter coming from the parent node.<br />
<br />
This plan is of course much faster, as there is no need to fully scan a, and to fully scan AND hash b.<br />
<br />
=SP-GiST=<br />
<br />
SP-GiST stands for Space Partitionned GiST, GiST being Generalized Search Tree. GiST is an index type, and has been available for quite a while in PostgreSQL. GiST is already very efficient at indexing complex data types, but performance tends to suffer when the source data isn't uniformly distributed. SP-GiST tries to fix that.<br />
<br />
As all indexing methods available in PostgreSQL, SP-GiST is a generic indexing method, meaning its purpose is to index whatever you'll throw at it, using operators you'll provide. It means that if you want to create a new datatype, and make it indexable through SP-GiST, you'll have to follow the documented API.<br />
<br />
SP-GiST can be used to implement 3 type of indexes: trie (suffix) indexing, Quadtree (data is divided into quadrants), and k-d tree (k-dimensional tree).<br />
<br />
For now, SP-GiST is provided with operator families called "quad_point_ops", "kd_point_ops" and "text_ops".<br />
<br />
As their names indicate, the first one indexes point types, using a quadtree, the second one indexes point types using a k-d tree, and the third one indexes text, using suffix.<br />
<br />
=pg_stat_statements=<br />
<br />
This contrib module has received a lot of improvements in this version:<br />
<br />
* Queries are normalized: queries that are identical except for their constant values will be considered the same, as long as their post-parse analysis query tree (that is, the internal representation of the query before rule expansion) are the same. This also implies that differences that are not semantically essential to the query, such as variations in whitespace or alias names, or the use of one particular syntax over another equivalent one will not differentiate queries.<br />
<br />
<pre><br />
=#SELECT * FROM words WHERE word= 'foo';<br />
word <br />
------<br />
(0 ligne)<br />
<br />
=# SELECT * FROM words WHERE word= 'bar';<br />
word <br />
------<br />
bar<br />
<br />
=#select * from pg_stat_statements where query like '%words where%';<br />
-[ RECORD 1 ]-------+-----------------------------------<br />
userid | 10<br />
dbid | 16384<br />
query | SELECT * FROM words WHERE word= ?;<br />
calls | 2<br />
total_time | 142.314<br />
rows | 1<br />
shared_blks_hit | 3<br />
shared_blks_read | 5<br />
shared_blks_dirtied | 0<br />
shared_blks_written | 0<br />
local_blks_hit | 0<br />
local_blks_read | 0<br />
local_blks_dirtied | 0<br />
local_blks_written | 0<br />
temp_blks_read | 0<br />
temp_blks_written | 0<br />
blk_read_time | 142.165<br />
blk_write_time | 0<br />
<br />
</pre><br />
<br />
The two queries are shown as one in pg_stat_statements.<br />
<br />
* For prepared statements, the execution part (execute statement) is charged on the prepare statement. That makes it is easier to interpret, and avoids the double-counting there was with PostgreSQL 9.1.<br />
<br />
* pg_stat_statements displays timing in milliseconds, to be consistent with other system views.<br />
<br />
= Explain improvements=<br />
<br />
* Timing can now be disabled with EXPLAIN (analyze on, timing off), leading to lower overhead on platforms where getting the current time is expensive <!--Tomas Vondra--><br />
<br />
=# EXPLAIN (analyze on,timing off) SELECT * FROM reservation ;<br />
QUERY PLAN <br />
----------------------------------------------------------------------------------------<br />
Seq Scan on reservation (cost=0.00..22.30 rows=1230 width=36) (actual rows=2 loops=1)<br />
Total runtime: 0.045 ms<br />
<br />
<br />
<br />
* Have EXPLAIN ANALYZE report the number of rows rejected by filter steps <!--(Marko Tiikkaja)--><br />
<br />
This new feature makes it much easier to know how many rows are removed by a filter (and spot potential places to put indexes):<br />
<br />
=# EXPLAIN ANALYZE SELECT * FROM test WHERE a ~ 'tra';<br />
QUERY PLAN <br />
---------------------------------------------------------------------------------------------------------------<br />
Seq Scan on test (cost=0.00..106876.56 rows=2002 width=11) (actual time=2.914..8538.285 rows=120256 loops=1)<br />
Filter: (a ~ 'tra'::text)<br />
Rows Removed by Filter: 5905600<br />
Total runtime: 8549.539 ms<br />
(4 rows)<br />
<br />
=Backward compatibility=<br />
<br />
These changes may incur regressions in your applications.<br />
<br />
==Ensure that xpath() escapes special characters in string values <!-- (Florian Pflug)--> ==<br />
<br />
Before 9.2:<br />
<pre><br />
SELECT (XPATH('/*/text()', '<root>&amp;lt;</root>'))[1];<br />
xpath <br />
-------<br />
<<br />
<br />
'<' Isn't valid XML.<br />
</pre><br />
With 9.2:<br />
<pre><br />
SELECT (XPATH('/*/text()', '<root>&amp;lt;</root>'))[1];<br />
xpath <br />
-------<br />
&amp;lt;<br />
</pre><br />
<br />
==Remove hstore's => operator <!-- (Robert Haas)-->==<br />
Up to 9.1, one could use the => operator to create a hstore. Hstore is a contrib, used to store key/values pairs in a column.<br />
<br />
In 9.1:<br />
<pre><br />
=# SELECT 'a'=>'b';<br />
?column? <br />
----------<br />
"a"=>"b"<br />
(1 row)<br />
<br />
=# SELECT pg_typeof('a'=>'b');<br />
pg_typeof <br />
-----------<br />
hstore<br />
(1 row)<br />
</pre><br />
<br />
With 9.2:<br />
<pre><br />
SELECT 'a'=>'b';<br />
ERROR: operator does not exist: unknown => unknown at character 11<br />
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.<br />
STATEMENT: SELECT 'a'=>'b';<br />
ERROR: operator does not exist: unknown => unknown<br />
LINE 1: SELECT 'a'=>'b';<br />
^<br />
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.<br />
</pre><br />
<br />
It doesn't mean one cannot use '=>' in hstores, it just isn't an operator anymore:<br />
<br />
<pre><br />
=# select hstore('a=>b');<br />
hstore <br />
----------<br />
"a"=>"b"<br />
(1 row)<br />
<br />
=# select hstore('a','b');<br />
hstore <br />
----------<br />
"a"=>"b"<br />
(1 row)<br />
</pre><br />
are still two valid ways to input a hstore.<br />
<br />
"=>" is removed as an operator as it is a reserved keyword in SQL.<br />
<br />
<br />
==Have pg_relation_size() and friends return NULL if the object does not exist <!-- (Phil Sorber)-->==<br />
<br />
A relation could be dropped by a concurrent session, while one was doing a pg_relation_size on it, leading to a SQL exception. Now, it merely returns NULL for this record.<br />
<br />
<br />
==Remove the spclocation field from pg_tablespace <!-- (Magnus Hagander)-->==<br />
<br />
The spclocation field provided the real location of the tablespace. It was filled in during the CREATE or ALTER TABLESPACE command. So it could be wrong: somebody just had to shutdown the cluster, move the tablespace's directory, re-create the symlink in pg_tblspc, and forget to update the spclocation field. The cluster would still run, as the spclocation wasn't used.<br />
<br />
So this field has been removed. To get the tablespace's location, use pg_tablespace_location():<br />
<br />
<pre><br />
=# SELECT *, pg_tablespace_location(oid) AS spclocation FROM pg_tablespace;<br />
spcname | spcowner | spcacl | spcoptions | spclocation <br />
------------+----------+--------+------------+----------------<br />
pg_default | 10 | | | <br />
pg_global | 10 | | | <br />
tmptblspc | 10 | | | /tmp/tmptblspc<br />
</pre><br />
<br />
==Have EXTRACT of a non-timezone-aware value measure the epoch from local midnight, not UTC midnight <!-- (Tom Lane) -->==<br />
<br />
<br />
With PostgreSQL 9.1:<br />
<br />
<pre><br />
=#SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamp);<br />
date_part <br />
------------<br />
1341180000<br />
(1 row)<br />
<br />
=# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamptz);<br />
date_part <br />
------------<br />
1341180000<br />
(1 row)<br />
</pre><br />
<br />
There is no difference in behaviour between a timstamp with or without timezone.<br />
<br />
With 9.2:<br />
<pre><br />
=#SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamp);<br />
date_part <br />
------------<br />
1341187200<br />
(1 row)<br />
<br />
=# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamptz);<br />
date_part <br />
------------<br />
1341180000<br />
(1 row)<br />
</pre><br />
When the timestamp has no timezone, the epoch is calculated with the "local midnight", meaning the 1st january of 1970 at midnight, local-time.<br />
<br />
==Fix to_date() and to_timestamp() to wrap incomplete dates toward 2020 <!-- (Bruce Momjian)-->==<br />
<br />
The wrapping was not consistent between 2 digit dates and 3 digit dates: 2 digit dates always chose the date closest to 2020, 3 digit dates mapped dates from 100 to 999 on 1100 to 1999, and 000 to 099 on 2000 to 2099.<br />
<br />
Now PostgreSQL chooses the date closest to 2020, for 2 and 3 digit dates.<br />
<br />
With 9.1:<br />
<pre><br />
=# SELECT to_date('200-07-02','YYY-MM-DD');<br />
to_date <br />
------------<br />
1200-07-02<br />
</pre><br />
<br />
With 9.2:<br />
<pre><br />
SELECT to_date('200-07-02','YYY-MM-DD');<br />
to_date <br />
------------<br />
2200-07-02<br />
</pre><br />
<br />
<br />
==pg_stat_activity and pg_stat_replication's definitions have changed <!--Magnus Hagander -->==<br />
<br />
The view pg_stat_activity has changed. It's not backward compatible, but let's see what this new definition brings us:<br />
<br />
* current_query disappears and is replaced by two columns:<br />
** state: is the session running a query, waiting<br />
** query: what is the last run (or still running if stat is "active") query<br />
* The column procpid is renamed to pid, to be consistent with other system views<br />
<br />
The benefit is mostly for tracking «idle in transaction» sessions. Up until now, all we could know was that one of these sessions was idle in transaction, meaning it has started a transaction, maybe done some operations, but still not committed. If that session stayed in this state for a while, there was no way of knowing how it got in this state.<br />
<br />
Here is an example:<br />
<pre><br />
-[ RECORD 1 ]----+---------------------------------<br />
datid | 16384<br />
datname | postgres<br />
pid | 20804<br />
usesysid | 10<br />
usename | postgres<br />
application_name | psql<br />
client_addr | <br />
client_hostname | <br />
client_port | -1<br />
backend_start | 2012-07-02 15:02:51.146427+02<br />
xact_start | 2012-07-02 15:15:28.386865+02<br />
query_start | 2012-07-02 15:15:30.410834+02<br />
state_change | 2012-07-02 15:15:30.411287+02<br />
waiting | f<br />
state | idle in transaction<br />
query | DELETE FROM test;<br />
</pre><br />
<br />
With PostgreSQL 9.1, all we would have would be «idle in transaction».<br />
<br />
As this change was backward-incompatible, procpid was also renamed to pid, to be more consistent with other system views.<br />
The view pg_stat_replication has also changed. The column procpid is renamed to pid, to also be consistent with other system views.<br />
<br />
==Change all SQL-level statistics timing values to float8-stored milliseconds <!-- (Tom Lane) -->==<br />
<br />
pg_stat_user_functions.total_time, pg_stat_user_functions.self_time, pg_stat_xact_user_functions.total_time, pg_stat_xact_user_functions.self_time, and pg_stat_statements.total_time (contrib) are now in milliseconds, to be consistent with the rest of the timing values.<br />
<br />
==postgresql.conf parameters changes <!-- (Heikki Linnakangas, Tom Lane, Peter Eisentraut) -->==<br />
<br />
* silent_mode has been removed. Use pg_ctl -l postmaster.log<br />
* wal_sender_delay has been removed. It is no longer needed<br />
* custom_variable_classes has been removed. All «classes» are accepted without declaration now<br />
* ssl_ca_file, ssl_cert_file, ssl_crl_file, ssl_key_file have been added, meaning you can now specify the ssl files<br />
<br />
= Other new features =<br />
<br />
== DROP INDEX CONCURRENTLY ==<br />
<br />
The regular DROP INDEX command takes an exclusive lock on the table. Most of the time, this isn't a problem, because this lock is short-lived. The problem usually occurs when:<br />
<br />
* A long-running transaction is running, and has a (shared) lock on the table<br />
* A DROP INDEX is run on this table in another session, asking for an exclusive lock (and waiting for it, as it won't be granted until the long-running transaction ends)<br />
<br />
At this point, all other transactions needing to take a shared lock on the table (for a simple SELECT for instance) will have to wait too: their lock acquisition is queued after the DROP INDEX's one.<br />
<br />
<br />
DROP INDEX CONCURRENTLY works around this and won't lock normal DML statements, just as CREATE INDEX CONCURRENTLY. The limitations are also the same: Since you can only DROP one index with the CONCURRENTLY option, and the CASCADE option is not supported.<br />
<br />
== NOT VALID CHECK constraints ==<br />
<br />
PostgreSQL 9.1 introduced «NOT VALID» foreign keys. This has been extended to CHECK constraints. Adding a «NOT VALID» constraint on a table means that current data won't be validated, only new and updated rows.<br />
<br />
=# CREATE TABLE test (a int); <br />
CREATE TABLE<br />
=# INSERT INTO test SELECT generate_series(1,100);<br />
INSERT 0 100<br />
=# ALTER TABLE test ADD CHECK (a>100) NOT VALID;<br />
ALTER TABLE<br />
=# INSERT INTO test VALUES (99);<br />
ERROR: new row for relation "test" violates check constraint "test_a_check"<br />
DETAIL: Failing row contains (99).<br />
=# INSERT INTO test VALUES (101);<br />
INSERT 0 1<br />
<br />
Then, later, we can validate the whole table:<br />
<br />
=# ALTER TABLE test VALIDATE CONSTRAINT test_a_check ;<br />
ERROR: check constraint "test_a_check" is violated by some row<br />
<br />
Domains, which are types with added constraints, can also be declared as not valid, and validated later.<br />
<br />
Check constraints can also be renamed now:<br />
<br />
=# ALTER TABLE test RENAME CONSTRAINT test_a_check TO validate_a;<br />
ALTER TABLE<br />
<br />
== NO INHERIT constraints ==<br />
<br />
Here is another improvement about constraints: they can be declared as not inheritable, which will be useful in partitioned environments. Let's take PostgreSQL documentation example, and see how it improves the situation:<br />
<br />
CREATE TABLE measurement (<br />
city_id int not null,<br />
logdate date not null,<br />
peaktemp int,<br />
unitsales int,<br />
CHECK (logdate IS NULL) NO INHERIT<br />
);<br />
<br />
CREATE TABLE measurement_y2006m02 (<br />
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )<br />
) INHERITS (measurement);<br />
CREATE TABLE measurement_y2006m03 (<br />
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )<br />
) INHERITS (measurement);<br />
<br />
<br />
INSERT INTO measurement VALUES (1,'2006-02-20',1,1);<br />
ERROR: new row for relation "measurement" violates check constraint "measurement_logdate_check"<br />
DETAIL: Failing row contains (1, 2006-02-20, 1, 1).<br />
INSERT INTO measurement_y2006m02 VALUES (1,'2006-02-20',1,1);<br />
INSERT 0 1<br />
<br />
Until now, every check constraint created on measurement would have been inherited by children tables. So adding a constraint forbidding inserts, or allowing only some of them, on the parent table was impossible.<br />
<br />
== Reduce ALTER TABLE rewrites ==<br />
<br />
A table won't get rewritten anymore during an ALTER TABLE when changing the type of a column in the following cases:<br />
<br />
* varchar(x) to varchar(y) when y>=x. It works too if going from varchar(x) to varchar or text (no size limitation)<br />
* numeric(x,z) to numeric(y,z) when y>=x, or to numeric without specifier<br />
* varbit(x) to varbit(y) when y>=x, or to varbit without specifier<br />
* timestamp(x) to timestamp(y) when y>=x or timestamp without specifier<br />
* timestamptz(x) to timestamptz(y) when y>=x or timestamptz without specifier<br />
* interval(x) to interval(y) when y>=x or interval without specifier<br />
<br />
== Security barriers and Leakproof ==<br />
<br />
This new feature has to do with views security. First, let's explain the problem, with a very simplified example:<br />
<br />
=# CREATE TABLE all_data (company_id int, company_data varchar);<br />
CREATE TABLE<br />
# INSERT INTO all_data VALUES (1,'secret_data_for_company_1');<br />
INSERT 0 1<br />
=# INSERT INTO all_data VALUES (2,'secret_data_for_company_2');<br />
INSERT 0 1<br />
=# CREATE VIEW company1_data AS SELECT * FROM all_data WHERE company_id = 1;<br />
CREATE VIEW<br />
<br />
This is a quite classical way of giving access to only a part of a table to a user: we'll create a user for company_id 1, grant to him the right to access company1_data, and deny him the right to access all_data.<br />
<br />
The plan to this query is the following:<br />
<br />
=# explain SELECT * FROM company1_data ;<br />
QUERY PLAN <br />
----------------------------------------------------------<br />
Seq Scan on all_data (cost=0.00..25.38 rows=6 width=36)<br />
Filter: (company_id = 1)<br />
<br />
Even if there was more data, a sequential scan could still be forced: just "SET enable_indexscan to OFF" and the likes.<br />
<br />
So this query reads all the records from all_data, filters them, and returns to the user only the matching rows. There is a way to display scanned records before they are filtered: just create a function with a very low cost, and call it while doing the query:<br />
<br />
CREATE OR REPLACE FUNCTION peek(text) RETURNS boolean LANGUAGE plpgsql AS<br />
$$<br />
BEGIN<br />
RAISE NOTICE '%',$1;<br />
RETURN true;<br />
END<br />
$$<br />
COST 0.1;<br />
<br />
This function just has to cost less than the = operator, which costs 1, to be executed first.<br />
<br />
The result is this:<br />
<br />
<br />
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);<br />
NOTICE: secret_data_for_company_1<br />
NOTICE: secret_data_for_company_2<br />
company_id | company_data <br />
------------+---------------------------<br />
1 | secret_data_for_company_1<br />
(1 row)<br />
<br />
We got access to the record from the second company (in the NOTICE messages).<br />
<br />
So this is the first new feature: the view can be declared as implementing "security barriers":<br />
<br />
<br />
=# CREATE VIEW company1_data WITH (security_barrier) AS SELECT * FROM all_data WHERE company_id = 1;<br />
CREATE VIEW<br />
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);<br />
NOTICE: secret_data_for_company_1<br />
company_id | company_data <br />
------------+---------------------------<br />
1 | secret_data_for_company_1<br />
(1 row)<br />
<br />
The view is not leaking anymore. The problem, of course, is that there is a performance impact: maybe the "peek" function could have made the query faster, by filtering lots of rows early in the plan.<br />
<br />
This leads to the complementary feature: some function may be declared as "LEAKPROOF", meaning that they won't leak the data they are passed into error or notice messages.<br />
<br />
Declaring our peek function as LEAKPROOF is a very bad idea, but let's do it just to demonstrate how it's used:<br />
<br />
CREATE OR REPLACE FUNCTION peek(text) RETURNS boolean LEAKPROOF LANGUAGE plpgsql AS<br />
$$<br />
BEGIN<br />
RAISE NOTICE '%',$1;<br />
RETURN true;<br />
END<br />
$$<br />
COST 0.1;<br />
<br />
A LEAKPROOF function is executed «normally»:<br />
<br />
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);<br />
NOTICE: secret_data_for_company_1<br />
NOTICE: secret_data_for_company_2<br />
company_id | company_data <br />
------------+---------------------------<br />
1 | secret_data_for_company_1<br />
(1 row)<br />
<br />
Of course, in our case, peek isn't LEAKPROOF and shouldn't be declared as such. Only superuser have the permission to declare a LEAKPROOF function.<br />
<br />
== New options for pg_dump ==<br />
<br />
Until now, one could ask pg_dump to dump a table's data, or a table's meta-data (DDL statements for creating the table's structure, indexes, constraints). Some meta-data is better restored before the data (the table's structure, check constraints), some is better after the data (indexes, unique constraints, foreign keys…), for performance reasons mostly.<br />
<br />
So there are now a few more options:<br />
<br />
* --section=pre-data: dump what's needed before restoring the data. Of course, this can be combined with a -t for instance, to specify only one table<br />
* --section=post-data : dump what's needed after restoring the data.<br />
* --section=data: dump the data<br />
* --exclude-table-data: dump everything, except THIS table's data. It means pg_dump will still dump other tables' data.<br />
<br />
<br />
<br />
<br />
<br />
[[Category:PostgreSQL 9.2]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=PostgreSQL_9.2&diff=18210PostgreSQL 9.22012-09-10T19:19:28Z<p>Mwtoews: Redirected page to What's new in PostgreSQL 9.2</p>
<hr />
<div>#REDIRECT[[What's new in PostgreSQL 9.2]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=PostgreSQL_9.1&diff=18209PostgreSQL 9.12012-09-10T19:19:04Z<p>Mwtoews: Redirected page to What's new in PostgreSQL 9.1</p>
<hr />
<div>#REDIRECT[[What's new in PostgreSQL 9.1]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=16518YUM Installation2012-04-10T12:18:31Z<p>Mwtoews: don't assume wget is installed, since it isn't with minimal</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms):<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.1 on CentOS 6 32-bit:<br />
curl -O http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos91-9.1-4.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.1 server:<br />
yum install postgresql91-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' is ''postgresql-9.1'', and the PGDATA directory is:<br />
/var/lib/pgsql/9.1/data<br />
<br />
For versions 7.x and 8.x, the ''<name>'' is always ''postgresql'', and the PGDATA directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA:<br />
service ''<name>'' initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to startup automatically on reboot:<br />
chkconfig ''<name>'' on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql91*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=16495YUM Installation2012-04-05T08:11:40Z<p>Mwtoews: update URLs and example to PG91</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms):<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See links from the main repository, http://yum.postgresql.org:<br />
* [http://yum.postgresql.org/packages.php A list of available packages]<br />
* [http://yum.postgresql.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.1 on CentOS 6 32-bit:<br />
wget http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos91-9.1-4.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.1 server:<br />
yum install postgresql91-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' is ''postgresql-9.1'', and the PGDATA directory is:<br />
/var/lib/pgsql/9.1/data<br />
<br />
For versions 7.x and 8.x, the ''<name>'' is always ''postgresql'', and the PGDATA directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA:<br />
service ''<name>'' initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to startup automatically on reboot:<br />
chkconfig ''<name>'' on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql91*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12081YUM Installation2010-09-28T07:48:09Z<p>Mwtoews: simplify</p>
<hr />
<div>PostgreSQL can be installed using RPMs (binary) or SRPMs (source) managed by [http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified YUM]. This is available for the following Linux distributions (both 32- and 64-bit platforms):<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See links from the main repository, http://www.pgrpms.org:<br />
* [http://www.pgrpms.org/packages.php A list of available packages]<br />
* [http://www.pgrpms.org/rpmchart.php Status of RPM builds]<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' is ''postgresql-9.0'', and the PGDATA directory is:<br />
/var/lib/pgsql/9.0/data<br />
<br />
For versions 7.x and 8.x, the ''<name>'' is always ''postgresql'', and the PGDATA directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA:<br />
service ''<name>'' initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to startup automatically on reboot:<br />
chkconfig ''<name>'' on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql90*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Working_with_GIT&diff=12080Working with GIT2010-09-28T07:07:29Z<p>Mwtoews: Redirecting to Working with Git</p>
<hr />
<div>#REDIRECT[[Working with Git]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Working_with_git&diff=12079Working with git2010-09-28T07:07:10Z<p>Mwtoews: Redirecting to Working with Git</p>
<hr />
<div>#REDIRECT[[Working with Git]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12043YUM Installation2010-09-23T17:35:10Z<p>Mwtoews: /* Control service */</p>
<hr />
<div>PostgreSQL can be installed using ''PGDG'' (PostgreSQL Global Development Group) RPMs (binary) or SRPMs (source) managed by ''YUM'' ([http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified Yellowdog_Updater,_Modified]).<br />
<br />
RPMs are available for the following Linux distributions:<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See http://www.pgrpms.org/ for an overview, http://www.pgrpms.org/packages.php for a list of packages and http://www.pgrpms.org/rpmchart.php for status of RPM builds.<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' is ''postgresql-9.0'', and the PGDATA directory is:<br />
/var/lib/pgsql/9.0/data<br />
<br />
For versions 7.x and 8.x, the ''<name>'' is always ''postgresql'', and the PGDATA directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA:<br />
service ''<name>'' initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to startup automatically on reboot:<br />
chkconfig ''<name>'' on<br />
<br />
=== Control service ===<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql90*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12042YUM Installation2010-09-23T17:34:44Z<p>Mwtoews: edits</p>
<hr />
<div>PostgreSQL can be installed using ''PGDG'' (PostgreSQL Global Development Group) RPMs (binary) or SRPMs (source) managed by ''YUM'' ([http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified Yellowdog_Updater,_Modified]).<br />
<br />
RPMs are available for the following Linux distributions:<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See http://www.pgrpms.org/ for an overview, http://www.pgrpms.org/packages.php for a list of packages and http://www.pgrpms.org/rpmchart.php for status of RPM builds.<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-installation commands ===<br />
<br />
After installing the packages, a database needs to be initialized and configured.<br />
<br />
For PostgreSQL version 9.0 and above, the ''<name>'' is ''postgresql-9.0'', and the PGDATA directory is:<br />
/var/lib/pgsql/9.0/data<br />
<br />
For versions 7.x and 8.x, the ''<name>'' is always ''postgresql'', and the PGDATA directory is:<br />
/var/lib/pgsql/data/<br />
<br />
==== Initialize ====<br />
The first command (only needed once) is to initialize the database in PGDATA:<br />
service ''<name>'' initdb<br />
<br />
==== Startup ====<br />
If you want PostgreSQL to startup automatically on reboot:<br />
chkconfig ''<name>'' on<br />
<br />
==== Control service ====<br />
To control the database service, use:<br />
service ''<name>'' ''<command>''<br />
where ''<command>'' can be:<br />
* ''start'' : start the database<br />
* ''stop'' : stop the database<br />
* ''restart'' : stop/start the database; used to read changes to core configuration files<br />
* ''reload'' : reload pg_hba.conf file while keeping database running<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql90*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Detailed_installation_guides&diff=12025Detailed installation guides2010-09-23T06:52:12Z<p>Mwtoews: +YUM Installation</p>
<hr />
<div>== Any UNIX-Like Platform ==<br />
<br />
* [[Manual Setup at the Command Line]]<br />
<br />
== General Linux ==<br />
<br />
* Gentoo: [http://www.gentoo.org/doc/en/postgres-howto.xml PostgreSQL Guide] (2007-04-25)<br />
* Linux: [http://www.designmagick.com/article/2/Starting-Out/Installing-PostgreSQL Installing Apache, PostgreSQL, and PHP on Linux] by Chris Smith (2006-02-07)<br />
* [[PostgreSQL and SELinux]]<br />
* [http://wiki.postgresql.org/wiki/PostgreSQL_Tutorials Installation of Postgres Plus Standard Server v8.3 Tutorial for Linux]<br />
<br />
=== Debian/Ubuntu Linux ===<br />
<br />
Debian based installs have a somewhat unique design that allows multiple database clusters to be managed independently. This allows running both multiple database instances as well as multiple versions more easily than other packaging schemes.<br />
<br />
*[http://www.stuartellis.eu/articles/postgresql-setup/ Using PostgreSQL on Debian and Ubuntu] (2009)<br />
*[http://www2.russbrooks.com:8000/2009/5/25/install-ubuntu-9-04-server-edition-rails-php-passenger-postgresql-and-mysql Install Ubuntu 9.04 Server Edition, Rails, PHP, Passenger, PostgreSQL, and MySQL] - the "database" section here is a concise guide to the standard PostgreSQL installation work most systems need to password-protected remote access, and it also covers installing the server side tools for pgAdmin (2009-05-25)<br />
*[http://hocuspokus.net/2008/05/install-postgresql-on-ubuntu-804 Install PostgreSQL on Ubuntu 8.04] (2008-05-14)<br />
*[http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html Howto setup Database Server With postgresql and pgadmin3] (has a nice intro to general postgresql.conf/pg_hba.conf info applicable to all distributions)<br />
*[http://www.debianhelp.co.uk/postgresql.htm PostgreSQL Database Server Configuration in Debian]<br />
*[http://www.elsn.org/main/postgresql/ Using the PostgreSQL Database Server]<br />
*[http://www.progsoc.org/~wildfire/notes/postgresql-cluster.html postgresql clustering and Debian]<br />
*[http://debian-user.blogspot.com/2008/02/upgrading-to-postgresql-83-in-debian.html Upgrading to PostgreSQL 8.3 in Debian from 7.4]<br />
<br />
=== RedHat/Fedora/CentOS ===<br />
* [[YUM Installation]]<br />
* [[PostgreSQL on RedHat Linux]]<br />
<br />
== FreeBSD ==<br />
* FreeBSD: [http://jasonk2600.wordpress.com/2010/01/11/installing-postgresql-on-freebsd/ Installing PostgreSQL on FreeBSD] (2010-01-11)<br />
* FreeBSD: [http://www.freebsddiary.org/postgresql.php PostgreSQL Installation on FreeBSD] (2002-08-24)<br />
* FreeBSD: [http://www.freebsddiary.org/postgresql-perl.php PostgreSQL and Perl on FreeBSD] (2000-12-26)<br />
<br />
== MacOS ==<br />
<br />
* MacOS X: [http://developer.apple.com/internet/macosx/postgres.html PostgreSQL on Mac OS X]<br />
* MacOS X: [http://www.macdevcenter.com/pub/a/mac/2002/06/07/postgresql.html PostgreSQL On MacOS X (another alternative guide) by Michael Brewer] (2002-07-06)<br />
* MacOS X: [http://facility9.com/2010/02/15/installing-postgresql-on-mac-os-x Installing PostgreSQL On Mac OS X (an alternative guide using EnterpriseDB) by Jeremiah Peschka] (2010-02-15)<br />
<br />
== Solaris ==<br />
<br />
* [http://www.sun.com/software/solaris/howtoguides/postgresqlhowto.jsp How to Configure and Run PostgreSQL on Solaris 10]<br />
* Solaris 10: [[:Image:pg_8.2.1_deployment_v1.0.pdf|PostgreSQL 8.2.1 on Solaris 10 – Deployment Guidelines]] by Chris Drawater (2007-01-15)<br />
* Solaris 10: [http://www.holindis.co.uk/ Related documents for J2EE, Tomcat, and Oracle migrations] by Chris Drawater (2007-01-15)<br />
* [[:Image:Pg81onsolaris10.pdf|PostgreSQL 8.1 on Solaris 10 - Deployment Guidelines]] (160KB - PDF)<br />
<br />
== Windows ==<br />
<br />
[[Running & Installing PostgreSQL On Native Windows]]<br />
<br />
== Alternative to manual installation ==<br />
<br />
Some users may prefer to skip manual installation by using a pre-integrated [[TurnKey PostgreSQL | PostgreSQL software appliance]].<br />
<br />
[[Category:Administration]][[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12024YUM Installation2010-09-23T02:10:56Z<p>Mwtoews: more edits</p>
<hr />
<div>PostgreSQL can be installed using ''PGDG'' (PostgreSQL Global Development Group) RPMs (binary) or SRPMs (source) managed by ''YUM'' ([http://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified Yellowdog_Updater,_Modified]).<br />
<br />
RPMs are available for the following Linux distributions:<br />
* ''Fedora'' (versions 7 and up)<br />
* ''Red Hat'' Enterprise Linux (versions 4 and up)<br />
* ''CentOS'' (versions 4 and up)<br />
<br />
See http://www.pgrpms.org/ for an overview, http://www.pgrpms.org/packages.php for a list of packages and http://www.pgrpms.org/rpmchart.php for status of RPM builds.<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-YUM installation ===<br />
<br />
For PostgreSQL version 9.0 and above (replace version):<br />
chkconfig postgresql-9.0 on<br />
service postgresql-9.0 initdb<br />
service postgresql-9.0 start<br />
<br />
For versions 7.x and 8.x:<br />
chkconfig postgresql on<br />
service postgresql initdb<br />
service postgresql start<br />
<br />
== Removing ==<br />
To remove everything:<br />
yum erase postgresql90*<br />
<br />
Or remove individual packages as desired.<br />
<br />
== Support ==<br />
* Email: pgsqlrpms-hackers@PgFoundry.org<br />
<br />
== See also ==<br />
* [[RPM Installation]]<br />
<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12023YUM Installation2010-09-23T01:51:39Z<p>Mwtoews: typo</p>
<hr />
<div>PostgreSQL can be installed using PGDG (PostgreSQL Global Development Group) RPMs (binary) or SRPMs (source).<br />
<br />
The following Linux distributions are available:<br />
* Fedora (versions 7 and up)<br />
* Red Hat Enterprise Linux (versions 4 and up)<br />
* CentOS (versions 4 and up)<br />
<br />
See http://www.pgrpms.org/ for an overview, http://www.pgrpms.org/packages.php for a list of packages and http://www.pgrpms.org/rpmchart.php for status of RPM builds.<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-YUM installation ===<br />
<br />
For PostgreSQL version 9.0 and above (replace version):<br />
chkconfig postgresql-9.0 on<br />
service postgresql-9.0 initdb<br />
service postgresql-9.0 start<br />
<br />
For versions 7.x and 8.x:<br />
chkconfig postgresql on<br />
service postgresql initdb<br />
service postgresql start<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=YUM_Installation&diff=12022YUM Installation2010-09-23T01:48:49Z<p>Mwtoews: start</p>
<hr />
<div>PostgreSQL can be installed using PGDG (PostgreSQL Global Development Group) RPMs (binary) or SRPMs (source).<br />
<br />
The following Linux distributions are available:<br />
* Fedora (versions 7 and up)<br />
* Red Hat Enterprise Linux (version 4 and up)<br />
* CentOS (versino 4 and up)<br />
<br />
See http://www.pgrpms.org/ for an overview, http://www.pgrpms.org/packages.php for a list of packages and http://www.pgrpms.org/rpmchart.php for status of RPM builds.<br />
<br />
== Instructions ==<br />
=== Configure your YUM repository ===<br />
<br />
Locate and edit your distributions .repo file, located:<br />
* On Fedora: ''/etc/yum.repos.d/fedora.repo'' and ''/etc/yum.repos.d/fedora-updates.repo'', '''[fedora]''' sections<br />
* On CentOS: ''/etc/yum.repos.d/CentOS-Base.repo'', '''[base]''' and '''[updates]''' sections<br />
* On Red Hat: ''/etc/yum/pluginconf.d/rhnplugin.conf'' '''[main]''' section<br />
<br />
To the section(s) identified above, you need to append a line:<br />
exclude=postgresql*<br />
<br />
=== Download and install PGDG RPM file ===<br />
<br />
A PGDG file is available for each distribution/architecture/database version combination. Browse http://www.pgrpms.org and find your correct RPM. For example, to install PostgreSQL 9.0 on CentOS 5.5 32-bit:<br />
wget http://www.pgrpms.org/9.0/redhat/rhel-5-i386/pgdg-centos-9.0-2.noarch.rpm<br />
<br />
Now install RPM distribution:<br />
rpm -ivh pgdg-centos-9.0-2.noarch.rpm<br />
<br />
=== Install PostgreSQL ===<br />
<br />
To list available packages:<br />
yum list postgres*<br />
<br />
For example, to install a basic PostgreSQL 9.0 server:<br />
yum install postgresql90-server<br />
<br />
Other packages can be installed according to your needs.<br />
<br />
=== Post-YUM installation ===<br />
<br />
For PostgreSQL version 9.0 and above (replace version):<br />
chkconfig postgresql-9.0 on<br />
service postgresql-9.0 initdb<br />
service postgresql-9.0 start<br />
<br />
For versions 7.x and 8.x:<br />
chkconfig postgresql on<br />
service postgresql initdb<br />
service postgresql start<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=RPM_Installation&diff=12017RPM Installation2010-09-22T21:37:42Z<p>Mwtoews: +cat</p>
<hr />
<div>== Installing PostgreSQL on Red Hat Enterprise Linux / Fedora Core ==<br />
<br />
Written by <br />
Devrim GÜNDÜZ (PGDG RPM Maintainer) devrim@PostgreSQL.org and<br />
Lamar Owen (PGDG RPM Maintainer) lowen@pari.edu<br />
<br />
(All contributions are welcome. Please feel free to drop us e-mail about your comments.)<br />
<br />
This article will talk about the details of installing PostgreSQL Database Server on Red Hat Enterprise Linux / Fedora Core. RPMs are ready-to-install binary files for many Linux distributions. Please note that this article is written for 8.1 and above, so some information may not fit your version.<br />
<br />
===General information===<br />
<br />
PostgreSQL Global Development Group (PGDG) builds RPMs for various Linux distributions. At the time of this writing, we have RPMs and SRPMs for the following platforms1:<br />
* Red Hat Linux 9,<br />
* Red Hat Enterprise Linux 2.1, 3.0 and 4 and<br />
* Fedora Core 1,2,3,4,5<br />
<br />
On PgFoundry, there is a project for building RPMs:<br />
http://pgfoundry.org/projects/pgsqlrpms<br />
<br />
Furthermore, there are -hackers, -general and -announce mailing lists. All discussions are open to everyone.<br />
<br />
===Obtaining the RPMs===<br />
<br />
The RPMs are available on PostgreSQL FTP site and all of its mirrors. For your convenience, you may try using web interface of PostgreSQL FTP site. That site will suggest you the suitable mirror(s) for you. The URL is:<br />
http://www.PostgreSQL.org/ftp<br />
<br />
===Which RPM for what purpose?===<br />
PGDG ships the following RPMs:<br />
<br />
* postgresql-libs : The postgresql-libs package provides the essential shared libraries for any PostgreSQL client program or interface. You will need to install this package to use any other PostgreSQL package or any clients that need to connect to a PostgreSQL server.<br />
<br />
* postgresql: If you want to manipulate a PostgreSQL database on a local or remote PostgreSQL server, you need this package. You also need to install this package if you're installing the postgresql-server package.<br />
<br />
* postgresql-contrib : The postgresql-contrib package contains contributed packages that are included in the PostgreSQL distribution.<br />
<br />
* postgresql-devel : The postgresql-devel package contains the header files and libraries needed to compile C or C++ applications which will directly interact with a PostgreSQL database management server and the ecpg Embedded C Postgres preprocessor. You need to install this package if you want to develop applications which will interact with a PostgreSQL server.<br />
<br />
* postgresql-docs : The postgresql-docs package includes the SGML source for the documentation as well as the documentation in PDF format and some extra documentation. Install this package if you want to help with the PostgreSQL documentation project, or if you want to generate printed documentation.<br />
<br />
* postgresql-server : The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create and maintain PostgreSQL databases. You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package and its requirements.<br />
<br />
* postgresql-tcl : The postgresql-tcl package contains the Pgtcl client library and its documentation.<br />
<br />
* postgresql-jdbc : The postgresql-jdbc package includes the .jar files needed for Java programs to access a PostgreSQL database.<br />
<br />
* postgresql-pl : The postgresql-pl package contains the the PL/Perl, and PL/Python procedural languages for the backend. PL/Pgsql is part of the core server package.<br />
<br />
* postgresql-python : The postgresql-python package includes a module for developers to use when writing Python code for accessing a PostgreSQL database.<br />
<br />
* postgresql-test : The postgresql-test package includes the sources and pre-built binaries of various tests for the PostgreSQL database management system, including regression tests and benchmarks.<br />
<br />
===Which packages should I use?===<br />
<br />
If you feel lazy about reading the descriptions above, here is a shortcut for minimal scenarios:<br />
<br />
* If you want to run a PostgreSQL server, install postgresql-libs, postgresql and postgresql-server.<br />
* If you want to run a client, install postgresql-libs and postgresql rpms.<br />
<br />
For some cases, you might just want to install postgresql-libs for some packages like PHP.<br />
<br />
Package names also include version and architecture information. Official PostgreSQL Global Development Group RPM's have a 'PGDG' after the release number. Other RPMset's as distributed with Linux distributions may have a different release number and initials. The version numbering is the same as PostgreSQL.<br />
<br />
It is preferable for the distribution-specific set to be the one used, as the PGDG set is intentionally generic. So, if your distro has a set of RPMs, use them in preference. If you want to stay up-to-date on the PostgreSQL core itself, use the PGDG generic set -- but understand that it is a GENERIC set.<br />
<br />
These RPMs no longer support any sort of upgrading process other than that documented in the regular documentation. That is, you must dump, upgrade, initdb, and restore your data. You must remove the old server subpackage, install the new package and restore the data from dump.<br />
<br />
===RPM File Locations===<br />
<br />
To be in compliance with the Linux FHS, the PostgreSQL PGDG RPMs install files in a manner not consistent with most of the PostgreSQL documentation. According to the standard PostgreSQL documentation, PostgreSQL is installed under the directory /usr/local/pgsql, with executables, source, and data existing in various subdirectories.<br />
<br />
Different distributions have different ideas of some of these file locations. In particular, the documentation directory can be /usr/doc, /usr/doc/packages, /usr/share/doc, /usr/share/doc/packages, or some other similar path. The Red Hat / Fedora Core locations are listed below:<br />
<br />
* Executables : /usr/bin<br />
* Libraries : /usr/lib<br />
* Documentation : /usr/share/doc/postgresql-x.y.z , /usr/share/doc/postgresql-x.y.z/contrib<br />
* Contrib : /usr/share/pgsql/contrib<br />
* Data : /var/lib/pgsql/data<br />
* Backup area : /var/lib/pgsql/backup<br />
* Templates : /usr/share/pgsql<br />
* Procedural Languages : /usr/lib/pgsql<br />
* Development Headers : /usr/include/pgsql<br />
* Other shared data : /usr/share/pgsql<br />
* Regression tests : /usr/lib/pgsql/test/regress (in the -test package)<br />
* Documentation SGML : /usr/share/doc/postgresql-docs-x.y.z<br />
<br />
The above list references the Red Hat / Fedora Core structure. These locations may change for other distributions. Use of 'rpm -ql' for each package is recommended as the 'Official' location source.<br />
<br />
These RPMs are designed to be LSB-compliant -- if you find this not to be the case, please let us know by way of the pgsqlrpms-hackers@PgFoundry.org mailing list.<br />
<br />
<br />
===Installing and Upgrading PostgreSQL RPMs===<br />
<br />
Installing PGDG RPMs are as easy as installing any RPMs<br />
rpm -ivh package_name.version.arch.rpm<br />
<br />
Unless specified, on minor release upgrades (i.e., upgrading from 8.1.0 to 8.1.1 or 8.1.3, etc1), you may use usual RPM upgrade process:<br />
rpm -Uvh package_name.version.arch.rpm<br />
<br />
Please note that on every new major version upgrade, you have to follow dump/reload sequence. However please don’t forget to read the Release Notes before upgrading because on some cases minor versions may need a dump/reload process. At those times if you use -U switch, then you will probably lose data! Please refer to “How do I perform a major upgrade?” section.<br />
<br />
Many of the RPMs are signed with the PGP key of the package builder. Directories contain a CURRENT_MAINTAINER file which includes the name/email of the package builder and link to their PGP key. You might want to import the signature before you’ll install the RPM:<br />
rpm –import http://link/to/the/pgp/key<br />
<br />
Unless this is not done, you can install/upgrade the RPMs but you’ll be thrown a warning.<br />
You may subscribe to pgsqlrpms-general@pgfoundry.org list for more details.<br />
<br />
===Removing RPMS===<br />
<br />
You might want to take a full dump (and possibly a filesystem level backup) before removing RPMs. Removing an PostgreSQL RPM is as easy as removing any RPM:<br />
rpm -e package_name<br />
<br />
Before the removal of server package, if there are any running processes, all are stopped. You don’t need to stop it.<br />
<br />
===Starting PostgreSQL for the first time and init script===<br />
<br />
Red Hat Linux uses the System V Init package. A startup script for PostgreSQL is provided in the server package, as <br />
/etc/rc.d/init.d/postgresql. To start the postmaster, with sanity checking, as root, run<br />
service postgresql start<br />
<br />
To shut the postmaster down,<br />
service postgresql stop<br />
<br />
There are other parameters to this script -- execute 'service postgresql' for a listing.<br />
<br />
On some cases you might want to edit this init file. For example, you might want to pass a –locale=... parameter to initdb, etc. <br />
<br />
This script does the following sets defaults for configuration variables and then performs the given action (stop, start, etc). During the startup, first the script checks whether the database cluster has been initialized or not. If not, the initialization is done. Then the service is started as usual. The initdb step is skipped if initialization has been performed before. Usually this is done in the fresh installs.<br />
<br />
There is also a “stop” parameter for stopping database server.<br />
<br />
You may also reload the server for some changes to take effect; but please look at the PostgreSQL documentation for the conditions that the database server needs a restart or a reload.<br />
<br />
===Starting PostgreSQL automatically at system startup===<br />
<br />
To get this script to run at system startup run:<br />
chkconfig postgresql on<br />
and the proper symlinks will be created. See the chkconfig man page for more information. Note that this is manual -- while the startup script can include tags to allow chkconfig to automatically perform the symlinking, this is not done at this time.<br />
<br />
===How do I perform a major upgrade?===<br />
<br />
Currently, PostgreSQL RPMs does not provide a data upgrade feature among major releases (or clearly, upgrades that require an initdb.) This work in under progress. In order to upgrade to a major version, you should follow the following steps:<br />
<br />
* Take a full dump using pg_dumpall<br />
<br />
* You might want to take a filesystem-level backup also. This is intentional.<br />
<br />
* Check the backups! (Do it again!)<br />
<br />
* Now, stop the database server:<br />
/sbin/service postgresql stop<br />
* Remove all postgresql rpms. Please note that you'll probably need a --nodeps switch at the end:<br />
/bin/rpm -e `/bin/rpm -qa|grep postgresql^`<br />
/bin/rpm -e `/bin/rpm -qa|grep postgresql^` --nodeps<br />
<br />
During the removal of packages, some scripts will be run to remove postgres user and to uninstall postgresql service, etc. <br />
<br />
Please note that removing of postgresql-server RPM will not also remove /var/lib/pgsql<br />
<br />
* Remove database cluster:<br />
/bin/rm -rf /var/lib/pgsql (Better: /bin/rm -rf ~postgres or best take a filesystem-level backup of this directory by using /bin/mv for example)<br />
<br />
* Install new RPM sets<br />
<br />
* Start database server<br />
/sbin/service postgresql start<br />
<br />
* Edit conf files, if you need.<br />
<br />
* Reload the data to the new server (You may need to edit your data).<br />
... and you're done!<br />
<br />
===Rebuilding from Source RPM===<br />
<br />
If your distribution is not supported by the binary RPM's from PostgreSQL.org, you will need to rebuild from the source RPM. Download the .src.rpm for this release. You will need to be root to rebuild, unless you have already set up a non-root build environment.<br />
<br />
Install the source RPM with rpm -i, then CD to the rpm building area (on Red Hat or Fedora Core this is /usr/src/redhat by default). You will have to have a full development environment to rebuild the full RPM set.<br />
<br />
<br />
<br />
* This release of the RPMset includes the ability to conditionally build sets of packages. The parameters, their defaults, and the meanings are:<br />
<br />
* beta : 0 - #build with cassert and do not strip the binaries<br />
* tcl : 1 - #build the postgresql-tcl package.<br />
* jdbc : 1- #build the postgresql-jdbc package.<br />
* pls : 1 - #build the postgresql-pl package.<br />
* test : 1 - #build the postgresql-test package.<br />
* python : 1 - #build the postgresql-python package.<br />
* pltcl : 1 - #build the pltcl portion of the postgresql-pl package.<br />
* plperl : 1 - #build the plperl portion of the postgresql-pl package.<br />
* ssl : 1 - #use OpenSSL support.<br />
* kerberos : 1 - #use Kerberos 5 support.<br />
* nls : 1 - #build with national language support.<br />
* pam : 1 : #build with PAM support.<br />
* runselftest : 1 - #do "make check" during the build.<br />
* xml : 1 - #build contrib/xml2<br />
* pgfts : 0 - #Build with –enable-thread-safety<br />
<br />
<br />
To use these defines, invoke a rebuild like this:<br />
rpm --rebuild --define 'perl 0' --define 'tcl 0' \ <br />
--define 'test 0' --define 'runselftest 1' \<br />
--define 'kerberos 0' postgresql-8.1.0-2PGDG.src.rpm<br />
<br />
This line would disable the perl, tcl, and test subpackages, enable the regression test run during build, and disable kerberos support. You might need to disable runselftest if there is an installed version of PostgreSQL that is a different major version from what you are trying to build. The self test tends to pick up the installed libpq.so shared library in place of the one being built :-(, so if that isn't compatible the test will fail. Also, you can't use runselftest when doing the build as root.<br />
<br />
More of these conditionals may be added/removed in the future.<br />
<br />
=== Contrib Files===<br />
<br />
The contents of the contrib tree are packaged into the -contrib subpackage and are processed with make and make install. There is documentation in /usr/share/doc/postgresql-contrib-VERSION for these modules. Most of the modules are in /usr/lib/pgsql for loadable modules, and binaries are in /usr/bin. In the future these files may be split out, depending upon function and dependencies.<br />
<br />
===More Information===<br />
<br />
You can get more information at http://www.postgresql.org<br />
<br />
Please help make this packaging better -- let us know if you find problems, or better ways of doing things. You can reach us by e-mail at pgsqlrpms-hackers@PgFoundry.org.<br />
<br />
[[Category:Install]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Running_%26_Installing_PostgreSQL_On_Native_Windows&diff=12015Running & Installing PostgreSQL On Native Windows2010-09-22T13:44:56Z<p>Mwtoews: modernize and simplify</p>
<hr />
<div>{{Languages}}<br />
<br />
== Supported Platforms ==<br />
=== What versions of Windows does PostgreSQL run on?===<br />
PostgreSQL is supported on Windows 2000 and above. It will run on 32 and 64 bit systems. We aim to support new versions of Windows in the PostgreSQL major version following their release at the latest.<br />
<br />
=== I heard that NT4 is supported. Is that true? ===<br />
Although not officially supported, PostgreSQL will run on Windows NT4 <br />
with a few minor issues including:<br />
* The installer will not work correctly, thus you will need to compile and install the code manually.<br />
* PostgreSQL uses a feature in the NTFS filesystem called 'reparse points' to implement tablespaces. Reparse points are not available in NT4, hence tablespaces cannot be used.<br />
* There is no 'runas.exe' included in Windows NT4 as standard, making it difficult to start PostgreSQL from an administrative account.<br />
It should also be noted that very little testing has been done on NT4.<br />
<br />
=== What about Windows 95/98/ME? ===<br />
<br />
PostgreSQL requires functionality that is not available on these<br />
platforms and will not run on them. If you need to run PostgreSQL<br />
on these platforms, you can look at the <br />
[http://www.postgresql.org/files/documentation/faqs/text/FAQ_CYGWIN Cygwin]<br />
port, which has basic support for 9x platforms.<br />
<br />
=== Is there a 64-bit build of PostgreSQL for Windows? ===<br />
<br />
Yes. A [[64bit Windows port]] was released for [[PostgreSQL 9.0]].<br />
<br />
=== What about 64-bit ODBC drivers? ===<br />
<br />
At time of writing there was 64-bit support in the [http://psqlodbc.projects.postgresql.org/ psqlODBC] source code, but no official binary releases of 64-bit ODBC drivers. Check the psqlODBC website for details.<br />
<br />
== Installation ==<br />
<br />
=== What do I need to install PostgreSQL on Windows? ===<br />
<br />
The easiest way to install PostgreSQL on Windows is with the Windows<br />
Installer package available from the PostgreSQL FTP site and mirrors.<br />
This will install a precompiled version of PostgreSQL along with<br />
pgAdmin (a graphical administration and management program), a<br />
selection of 'contrib' modules to provide additional specialised<br />
functionality, and a choice of procedural languages.<br />
<br />
To use the installer, you need a computer running Windows 2000, XP,<br />
2003 or Vista with the Windows Installer service installed. The installer<br />
will create a service account if required, and initialise the database<br />
cluster.<br />
<br />
The Installer may be downloaded from <br />
[http://www.postgresql.org/ftp/binary/ here].<br />
<br />
=== What do I need to compile PostgreSQL from source code? ===<br />
<br />
The Windows compilation FAQ at <br />
[http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html FAQ_MINGW]<br />
contains full details of compilation of the PostgreSQL source code on <br />
Windows systems using MinGW. PostgreSQL 8.3 and above can be built using<br />
Microsoft Visual C++ 2005 (2008 should also work, but is currently <br />
untested). See the [http://www.postgresql.org/docs/8.3/static/install-win32-full.html documentation]<br />
for details.<br />
<br />
=== Why do I need a non-administrator account to run PostgreSQL under? ===<br />
<br />
When a hacker gains entry to a computer using a software bug in a<br />
package, she gains the permissions of the user account under which the<br />
service is run. Whilst we do not know of any such bugs in PostgreSQL,<br />
we enforce the use of a non-administrative service account to minimise<br />
the possible damage that a hacker could do should they find and utilise<br />
a bug in PostgreSQL to hack the system.<br />
<br />
This has long been common practice in the Unix world, and is starting to<br />
become standard practice in the Windows world as well as Microsoft and<br />
other vendors work to improve the security of their systems.<br />
<br />
Note, that with the release of PostgreSQL 8.2, it is possible to run under<br />
a administrative account. PostgreSQL 8.2 and above are able to irrevocably<br />
give up administrative rights at startup thus ensuring the rest of the <br />
system remains secure in the extremely unlikely event that PostgreSQL<br />
becomes compromised.<br />
<br />
=== Can I install PostgreSQL on a FAT partition? ===<br />
<br />
PostgreSQL's number one priority is the integrity of your data. FAT and<br />
FAT32 filesystems simply do not offer the reliability required to allow<br />
this. In addition, the lack of security features offered by FAT make it<br />
impossible to secure the raw data files from unauthorised modification.<br />
Finally, PostgreSQL utilises a feature called 'reparse points' to<br />
implement tablespaces. This feature is not available on FAT partitions.<br />
<br />
The NTFS file system is a journalled filesystem offering much better<br />
reliability and crash recovery. In addition, it has a comprehensive<br />
access control system and offers the reparse point functionality used<br />
by PostgreSQL.<br />
<br />
For this reason, the PostgreSQL installer package will not initialise<br />
a database cluster on anything but an NTFS partition. The server and<br />
utilities may be installed on any partition type.<br />
<br />
It is recognised however, that on some systems such as developer's<br />
PCs, FAT partitions may be the only choice. In such cases, you can<br />
simply install PostgreSQL as normal, but without initialising the<br />
database cluster. When the installation has finished, manually run<br />
the 'initdb.exe' program on the FAT partition. Security and<br />
reliability will be compromised however, and any attempts to create<br />
tablespaces will fail.<br />
<br />
=== What filesystem permissions does PostgreSQL require? ===<br />
The PostgreSQL service account needs ''read'' permissions on<br />
all directories leading up to the service directory. It needs ''write'' permissions ''only''<br />
on the data directory. Specifically, it should ''not'' be granted anything other than ''read''<br />
permissions on the directories containing binary files. (All directories below the installation<br />
directory are set by the installer, so unless you change something, there should be no problem with this).<br />
<br />
PostgreSQL also needs ''read'' permissions on system DLL files like kernel32.dll and user32.dll<br />
(among others), which is normally granted by default, and on the CMD.EXE binary, which may in some<br />
scenarios be locked down and need opening.<br />
<br />
If you are running PostgreSQL on a multi-user system, you should remove the permissions from all<br />
non-administrative users from the PostgreSQL directories. No user ''ever'' needs permissions on the<br />
PostgreSQL files - all communication is done through the libpq connection. Direct access to data files <br />
can lead to information disclosure or system instability!<br />
<br />
=== Why can't I select Unicode as an encoding? ===<br />
Starting with PostgreSQL 8.1, (UTF8) UNICODE encoding is fully supported on Windows. 8.0 is no longer supported.<br />
<br />
=== I installed in a non-english language, but all messages show up in english! ===<br />
The language choice made during the installation only selects which language is used by the installer. To<br />
change the language of the messages of the installed product, make sure you have installed the ''National language<br />
support'' feature. Then edit your postgresql.conf file and change the value of the ''lc_messages'' parameter<br />
to the language you want.<br />
<br />
== Common installation errors ==<br />
<br />
=== PostgreSQL and/or the installer crashes at startup, fails to start or hangs on start ===<br />
<br />
By far most common reasons for this are antivirus and firewalls. If<br />
you have any firewall software installed on your machine, try either<br />
disabling it or uninstalling it. If you have any antivirus software<br />
installed, you '''must''' disable it for the directories that are to<br />
be used by PostgreSQL. If that still does not help, it may be required<br />
to completely uninstall the software from the machine. See the next<br />
section for more information about specific products.<br />
<br />
There is also an issue if you have installed ''cygwin'', and the cygwin\bin directory is present<br />
in the system PATH variable. There are DLL files in the cygwin directory related to interpreted languages (TCL,<br />
perl, python) that contain bugs that can cause the installer or the installed version of PostgreSQL to either hang<br />
or crash. Remove the cygwin\bin directory from your path before running the installer!<br />
<br />
=== What Anti-Virus software is compatible? ===<br />
<br />
The systems used to build the Windows installers all run either Sophos AV or AVG Free Edition, and<br />
those systems pass a full set of PostgreSQL regression tests running those programs.<br />
<br />
Specific issues have been reported with the ''nod32'' antivirus<br />
product. If you are using this product, add "postmaster.exe" to the list<br />
of excluded processes (available under advanced options). This has been<br />
reported to fix the problem.<br />
<br />
Specific issues have also been reported with McAfee and Panda anti-virus <br />
software and NetLimiter network monitoring software. While some people do have <br />
PostgreSQL working with these software packages, there is no specific or even <br />
recommend solutions that have not worked in some cases, so the issues would appear<br />
to be installation specific, sometimes even requiring uninstallation.<br />
<br />
=== The installer claims the specified account is an administrator, but it isn't! ===<br />
<br />
Most likely, the specified account is an administrator or power user,<br />
even though you aren't aware of it. The check used by the installer<br />
specifically checks for membership in the Administrators or<br />
Power Users group. Work your way backwards using Local Users and Groups -<br />
open the Administrators group, see who's a member. Then check any<br />
groups (domain or local) that are a member of the Administrators group,<br />
and any groups members of that group etc. PostgreSQL checks any level<br />
of nested groups.<br />
<br />
=== I get the error "the user has not been granted the requested logon type at this computer" or similar ===<br />
Make sure the specified PostgreSQL account has the "Log on as a service"<br />
and "Log on locally" rights. The "Log on locally" is only required for<br />
the install part, and can be removed once the installation is completed<br />
if security policies require it. (Rights are granted and revoked using<br />
the "Local Security Policy" MMC snapin. "Log on locally" is default,<br />
and "Log on as a service" will normally be granted automatically by<br />
the installer).<br />
<br />
If you still have this problem, enable auditing (also using<br />
the "Local Security Policy" snapin) and let us know what other rights<br />
were required in your setup.<br />
<br />
Note that if your computer is a member of a domain, the settings of<br />
the security policies may be controlled at the domain level using<br />
Group Policy.<br />
<br />
=== I'm getting permissions errors when installing/running initdb ===<br />
<br />
Make sure the PostgreSQL service account has permissions on the directories leading up to the one<br />
you have installed into. The installer will set permissions on the install directory but not on<br />
parent directories of it.<br />
<br />
=== I'm getting an error message that says PostgreSQL cannot be installed from a Terminal Services session ===<br />
This is unfortunately so. The PostgreSQL backend won't run from a TS session, and in order to do initdb the installer<br />
has to start a standalone backend. Therefore, installation has to be performed from the console. Note that if you are<br />
using Windows Server 2003, you can get remote access to the actual console and not just an administrative session.<br />
To do this, start the Remote Desktop Connection by executing <I>mstsc /console</I>, and then connect as usual.<br />
This will lock the local console of the server and give you control over that session. In this scenario,<br />
PostgreSQL should install just fine.<br />
<br />
=== I changed the directory but PostgreSQL was still installed in the default directory ===<br />
<br />
Make sure you changed the directory of the ''root feature''. The PostgreSQL installer permits changing the directory<br />
of some individual features. If you change the root feature ("PostgreSQL"), any subfeatures (such as "Database Server")<br />
will automatically inherit this value as default, but if you only change a subfeature the rest of the installation<br />
will remain in the default location.<br />
<br />
=== When upgrading the installer says I don't have permissions to install a service, but I'm logged in as administrator ===<br />
The workaround for this issue is to first ''uninstall'' the previous version. Note that this will ''not'' remove<br />
your data!! Then reinstall the new version, and make sure you use the ''exact same directories''. That should solve the<br />
problem. Note that this is only possible in minor version upgrades (such as 8.0.1 to 8.0.2) that do not require a dump/reload.<br />
<br />
=== I get an error message saying that the installation package cannot be opened ===<br />
This can be caused by two things. The most likely one is that you double-clicked<br />
on the MSI file in the ZIP file. For the installation to work, you must uncompress the<br />
whole ZIP file to a temporary directory and then run it.<br />
The other reason is that your download is broken. Try to download again, using a different<br />
mirror.<br />
<br />
=== How do I delete the service account - it's not listed under users! ===<br />
Windows sometimes hide some accounts from the GUI tools, so they cannot be removed from there. This includes<br />
the auto-created service account for PostgreSQL (that may be left over from a previous installation). To delete this<br />
account, use the NET command on the commandline:<br />
NET USER <username> /DELETE<br />
Where <username> is the windows login name of the user, for example ''postgres''.<br />
<br />
== Common runtime issues ==<br />
<br />
=== I'm getting "dynamic load error" when installing a procedural language ===<br />
<br />
Most commonly this means that the actual language DLL for the procedural language is missing. The PostgreSQL<br />
DLLs only contains the language bindings, and require the language distribution DLLs to be present<br />
in the system PATH. For a list of the current required DLLs for the different procedural languages,<br />
please see [http://pginstaller.projects.postgresql.org the installation instructions].<br />
<br />
To find out exactly which DLL files are missing, you can use the ''depends'' tool from Microsoft.<br />
It is available in the Windows Support Tools, that are on the Windows CD as a separate install. Just run<br />
''depends plpython.dll'' (for PL/python) to show which imports are missing.<br />
<br />
=== I'm seeing a lot of postgres.exe processes even though I only started the server once ===<br />
<br />
This is normal. PostgreSQL uses a multi-process architecture. In an empty system you will see anything from two to<br />
five processes. Once clients start to connect, the number of processes will increase.<br />
<br />
=== How do I set an environment variable? ===<br />
PostgreSQL uses environment variables for several settings. To change an environment variable in most<br />
Windows versions, go into Properties for My Computer, then into Advanced. Note that there are two sets of <br />
environment variables - one for the system which applies to all users and one for the current user. If you <br />
want an environment variable to affect the PostgreSQL service, you must change the system variable. After <br />
changing a system variable, you must restart the service.<br />
<br />
=== I cannot run with more than about 125 connections at once, despite having capable hardware ===<br />
When running as a service, you may experience failures with more than approximately 125 simultaneous connections. <br />
This can happen because some of the libraries that PostgreSQL is dependent on are dependent on user32.dll which allocates<br />
memory from an area known as the Desktop Heap. The desktop heap is assigned to each logon session, with non-interactive<br />
sessions typically being assigned 512KB. Each postgres process that runs typically consumes approximately 3.2KB of desktop<br />
heap, which coupled with other overhead can exhaust the allocated heap at somewhere around 125 connections. This doesn't<br />
happen when running from the command line (or more precisely, happens with much higher numbers of connections) because the <br />
interactive logon session is typically allocated 3MB of Desktop Heap.<br />
<br />
You can increase the non-interactive Desktop Heap by modifying the third SharedSection value in the registry as described<br />
in this [http://support.microsoft.com/kb/184802 Microsoft Knowledgebase article]. Note that this should be done<br />
with great care as specifying a value that is excessively high may prevent your system from booting.<br />
<br />
[[Category:FAQ]] [[Category:Windows]]<br />
<br />
== Windows version-specific issues ==<br />
<br />
==== Can I install a 32-bit PostgreSQL on 64-bit Windows? ====<br />
<br />
Recent 32-bit versions of PostgreSQL (8.3 and newer) can be installed and used on 64-bit Windows XP and above,<br />
though they retain the 32-bit limits on maximum process address space (and thus shared memory).<br />
<br />
You can connect to the 32-bit postgresql server from 64-bit programs on the computer the server is running on <br />
or other computers if a 64-bit libpq or psqlODBC driver is installed where the program is running.<br />
<br />
Because 32-bit versions of the PostgreSQL server only install a 32-bit libpq and pgsqlODBC, only 32-bit programs <br />
on the computer the server was installed on can use the database unless a 64-bit ODBC driver or libpq<br />
is installed as an add-on.<br />
<br />
==== Where is the PostgreSQL ODBC driver? I'm running 32-bit PostgreSQL on 64-bit Windows ====<br />
<br />
You need to use the 32-bit ODBC administrator to set up data sources for 32-bit<br />
applications using 32-bit drivers.<br />
<br />
Unless you've also installed a [[#What about 64-bit ODBC drivers?|64-bit version]] of <br />
[http://psqlodbc.projects.postgresql.org psqlODBC], a 32-bit install of PostgreSQL will<br />
only have a 32-bit ODBC driver. The 32-bit ODBC driver may only be used by 32-bit programs,<br />
and ''will not show up in the 64-bit ODBC administrator''.<br />
<br />
This gets confusing because on 64-bit windows <code>c:\windows\system32\odbcad32</code> is the ''64-bit''<br />
ODBC driver administrator, despite the name. This is a historical artifact of Windows development. <br />
Apparently many apps and installers depend on odbcad32.exe having that name and path,<br />
so Microsoft landed up being stuck with it despite the now-stupid name, which made sense back<br />
in the 16- to 32-bit transition but makes none now. It's the same reason the<br />
"system32" directory is still called that on 64-bit Windows. PostgreSQL can't do anything about this.<br />
<br />
See: [http://support.microsoft.com/kb/942976 http://support.microsoft.com/kb/942976]<br />
<br />
You'll see in that article that the 32-bit ODBC administrator on 64-bit Windows is:<br />
<br />
<pre><br />
%systemdrive%\Windows\SysWoW64\odbcad32.exe<br />
</pre><br />
<br />
You can launch this from "Start->Run" using the path above. You will see the PostgreSQL ODBC drivers in the<br />
32-bit ODBC administrator.<br />
<br />
You 'can not' use a 32-bit ODBC driver in a 64-bit application. That means that you'll still only be<br />
able to use the PostgreSQL ODBC driver with 32-bit applications unless you install a 64-bit ODBC driver<br />
as well.<br />
<br />
==== Can I use a 64-bit ODBC program with a 32-bit PostgreSQL server? ====<br />
<br />
Only if you have a 64-bit [http://psqlodbc.projects.postgresql.org|psqlODBC] driver installed. See the installation section.</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=PostgreSQL_9.0&diff=12014PostgreSQL 9.02010-09-22T13:39:34Z<p>Mwtoews: Redirecting to What's new in PostgreSQL 9.0</p>
<hr />
<div>#REDIRECT[[What's new in PostgreSQL 9.0]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=What%27s_new_in_PostgreSQL_9.0&diff=11996What's new in PostgreSQL 9.02010-09-21T00:27:20Z<p>Mwtoews: /* Message passing in NOTIFY/pg_notify */</p>
<hr />
<div>This document showcases many of the latest developments in PostgreSQL 9.0, compared to the last major release &ndash; PostgreSQL 8.4. There are more than 200 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 [http://www.postgresql.org/docs/9.0/static/release-9-0 Release Notes].<br />
<br />
=The two features you can't ignore=<br />
<br />
Hot Standby and Streaming Replication are the two new features that mark Version 9.0 as a landmark in PostgreSQL's development and the motivation for allocating a full version number to this release &ndash; 9.0 (instead of 8.5). Combined, these two features add built-in, "binary replication" to PostgreSQL.<br />
<br />
There is further documention on how to use [[Hot Standby]] on its page.<br />
<br />
===Hot Standby===<br />
<br />
This feature allows users to create a 'Standby' database &ndash; that is, a second database instance (normally on a separate server) replaying the primary's binary log, while making that standby server available for read-only queries. It is similar to the standby database features of proprietary databases, such as Oracle's Active DataGuard. Queries execute normally while the standby database continuously replays the stream of binary modifications coming from the primary database. Visibility of new data changes follows the MVCC model, so that new changes do not lock out queries.<br />
<br />
Enabling Hot Standby is a simple process. On the primary database server add this to the <tt>postgresql.conf</tt> file:<br />
wal_level = 'hot_standby' # Adds the required data in the WAL logs<br />
<br />
And on the standby server, add this to its <tt>postgresql.conf</tt> file:<br />
hot_standby = on<br />
<br />
Hot Standby works well with the new Streaming Replication feature, though it can also be used with file-based log shipping as available in previous versions and also to create standalone copies that receive no updates at all.<br />
<br />
In some cases, changes from the primary database can conflict with queries on the standby. A simple example is when DROP TABLE executes on the master, but the standby is still executing a query against that table. The standby cannot process that DROP statement without first canceling the running query, and the longer it delays doing that the further behind current replication the standby will become. The two options here are to pause the replay or cancel the read-only queries and move forward. <br />
<br />
A variety of parameters allow adjusting the conflict resolution mechanism used.<br />
<br />
max_standby_archive_delay = 30s # -1= always wait, 0= never wait, else wait for this<br />
max_standby_streaming_delay = 30s # -1= always wait, 0= never wait, else wait for this<br />
<br />
The two max_standby_{archive,streaming}_delay settings determine the behaviour of the standby database when conflicts between replay and read-only queries occur. In this situation, the standby database will wait at most until it's lagging behind the primary database by that amount before canceling the conflicting read-only queries. The two parameters allow different lag time tolerance levels for files appearing via regular file archive shipping vs. ones that are streamed via the new 9.0 feature for streaming replication.<br />
<br />
On the master it is also possible to avoid conflicts by increasing this parameter<br />
<br />
vacuum_defer_cleanup_age = 10000 # Adjust updwards slowly to reduce conflicts<br />
<br />
This feature is rich and complex, so it's advisable to read the documentation before planning your server deployments.<br />
<br />
===Streaming Replication===<br />
<br />
Complementing Hot Standby, Streaming Replication is the second half of the "great leap forward" for PostgreSQL. While there are several third-party replication solutions available for PostgreSQL that meet a range of specific needs, this new release brings a simple, sturdy and integrated version that will probably be used as a default in most High Availability installations using PostgreSQL.<br />
<br />
This time, the goal is improving the archiving mechanism to make it as continuous as possible and to not rely on log file shipping. Standby servers can now connect to the master/primary and get sent, whenever they want, what they are missing from the Write Ahead Log, not in terms of complete files ('wal segments'), but in terms of records in the WAL (you can think of them as fragments of these files).<br />
<br />
Streaming Replication is an asynchronous mechanism; the standby server lags behind the master. But unlike other replication methods, this lag is very short, and can be as little as a single transaction, depending on network speed, database activity, and Hot Standby settings. Also, the load on the master for each slave is minimal, allowing a single master to support dozens of slaves.<br />
<br />
Primary and standby databases are identical at the binary level (well, almost; but don't worry if your datafiles don't have the same checksum).<br />
<br />
For Streaming Replication, wal_level should be 'archive' or 'hot standby'.<br />
<br />
<tt>postgresql.conf</tt>, Primary:<br />
max_wal_senders = 5 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server<br />
wal_keep_segments # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind)<br />
<br />
On the standby server:<br />
<br />
<tt>recovery.conf</tt>, Standby:<br />
standby_mode = 'on'<br />
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' # connection string to reach the primary database<br />
<tt>postgresql.conf</tt>, Secondary:<br />
wal_level # same value as on the primary (you'll need this after a failover, to build a new standby)<br />
hot_standby=on/off # Do you want to use Hot Standby at the same time ?<br />
pg_hba.conf file:<br />
<br />
There must be an entry here for the replication connections. The fake database is 'replication', the designated user should be superuser. Be careful not to give broad access to this account: a lot of privileged data can be extracted from WAL records.<br />
<br />
<tt>pg_hba.conf</tt>, Primary:<br />
host replication foo 192.168.1.100/32 md5<br />
As for Hot Standby, this feature is rich and complex. It's advised to read the documentation. And to perform failover and switchover tests when everything is in place.<br />
<br />
One thing should be stressed about these two features: you can use them together. This means you can have a near-realtime standby database, and run read-only queries on it, such as reporting queries. You can also use them independently; a standby database can be Hot Standby with file shipping only, and a Streaming Replication database can stream without accepting queries.<br />
<br />
=Other New features=<br />
<br />
There are literally hundreds of improvements, updates, and new features in 9.0 ... enough to make it a major release even without binary replication. We'll tour a few of them below, by category, with details on how to use them.<br />
<br />
==Security and Authentication==<br />
<br />
Of course, as the most secure SQL database (according to the SQL Hacker's Handbook) we're always eager to improve our data security. 9.0 adds several new features in this realm.<br />
<br />
===GRANT/REVOKE IN SCHEMA===<br />
<br />
One annoying limitation in PostgreSQL has been the lack of global GRANT/REVOKE capabilities. With 9.0 it's now possible to set privileges on all tables, sequences and functions within a schema using without having to write a script or a stored procedure:<br />
<br />
GRANT SELECT ON ALL TABLES IN SCHEMA public TO toto;<br />
And reverting this:<br />
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM toto;<br />
<br />
See the [http://www.postgresql.org/docs/9.0/static/sql-grant.html GRANT] documentation page for further details.<br />
<br />
Note that the above only works for existing objects. However, it's now also possible to define default permissions for new objects:<br />
<br />
===ALTER DEFAULT PRIVILEGES===<br />
<br />
This feature also makes permission management more efficient.<br />
ALTER DEFAULT PRIVILEGES FOR ROLE marc GRANT SELECT ON TABLES TO public;<br />
CREATE TABLE test_priv (a int);<br />
\z test_priv<br />
Access privileges<br />
Schema | Name | Type | Access privileges | Column access privileges<br />
--------+------------+-------+-------------------+--------------------------<br />
public | test_priv | table | =r/marc +|<br />
| | | marc=arwdDxt/marc |<br />
<br />
This new information is stored in the pg_default_acl system table.<br />
<br />
===passwordcheck===<br />
<br />
This contrib module can check passwords, and prevent the worst of them from getting in. After having it installed and set up as described in the documentation, here is the result:<br />
marc=# ALTER USER marc password 'marc12';<br />
&lt;marc%marc&gt; ERROR: password is too short<br />
&lt;marc%marc&gt; STATEMENT: ALTER USER marc password 'marc12';<br />
ERROR: password is too short<br />
marc=# ALTER USER marc password 'marc123456';<br />
&lt;marc%marc&gt; ERROR: password must not contain user name<br />
&lt;marc%marc&gt; STATEMENT: ALTER USER marc password 'marc123456';<br />
ERROR: password must not contain user name<br />
This module has limitations, mostly due to PostgreSQL accepting already encrypted passwords to be declared, making correct verification impossible.<br />
<br />
Moreover, its code is well documented, and can be easily adapted to suit specific needs (one can activate cracklib very easily, for instance)<br />
<br />
<br />
<br />
==SQL Features==<br />
<br />
SQL03 has a huge array of functionality, more than any one DBMS currently implements. But we keep adding SQL features, as well as extending SQL in a compatible way with various little things to make writing queries easier and more powerful.<br />
<br />
===Column triggers===<br />
<br />
Column triggers fire only when a specific column is explicitly UPDATED. They allow you to avoid adding lots of conditional logic and value comparisons in your trigger code.<br />
<br />
Example:<br />
CREATE TRIGGER foo BEFORE UPDATE OF some_column ON table1 FOR EACH ROW EXECUTE PROCEDURE my_trigger();<br />
This trigger fires only when '<tt>some_column</tt>' column of '<tt>table1</tt>' table has been updated.<br />
<br />
Column triggers are not executed if columns are set to DEFAULT.<br />
<br />
===WHEN Triggers===<br />
<br />
Completing PostgreSQL's effort to limit IF ... THEN code in triggers, conditional triggers define simple conditions under which the trigger will be executed. This can dramatically decrease the number of trigger executions and reduce CPU load on the database server.<br />
<br />
For example, this trigger would check that an account was correctly balanced only when the balance changes:<br />
<br />
CREATE TRIGGER check_update<br />
BEFORE UPDATE ON accounts<br />
FOR EACH ROW<br />
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)<br />
EXECUTE PROCEDURE check_account_update();<br />
<br />
And this trigger will only log a row update when the row actually changes. It's very helpful with framework or ORM applications, which may attempt to save unchanged rows:<br />
<br />
CREATE TRIGGER log_update<br />
AFTER UPDATE ON accounts<br />
FOR EACH ROW<br />
WHEN (OLD.* IS DISTINCT FROM NEW.*)<br />
EXECUTE PROCEDURE log_account_update();<br />
<br />
You could even go further than this and decide not to save a row at all if it hasn't changed:<br />
<br />
CREATE TRIGGER log_update<br />
AFTER UPDATE ON accounts<br />
FOR EACH ROW<br />
WHEN (OLD.* IS NOT DISTINCT FROM NEW.*)<br />
EXECUTE PROCEDURE no_op();<br />
<br />
===DEFERRABLE UNIQUE CONSTRAINTS===<br />
<br />
This feature will also be very useful. Here is an example, using a primary key instead of a simple unique key:<br />
marc=# CREATE TABLE test (a int primary key);<br />
marc=# INSERT INTO test values (1), (2);<br />
marc=# UPDATE test set a = a+1;<br />
ERROR: duplicate key value violates unique constraint "test_pkey"<br />
DETAIL: Key (a)=(2) already exists.<br />
That's a pity: at the end of the statement, my data would have been consistent, so far as this constraint is concerned. Even worse, if the table had been physically sorted by descending order, the query would have worked! With 8.4, there was no easy way out; we had to find a trick to update the records in the right order.<br />
<br />
We can now do this:<br />
marc=# CREATE TABLE test (a int primary key deferrable);<br />
marc=# INSERT INTO test values (1), (2);<br />
marc=# UPDATE test set a = a+1;<br />
UPDATE 2<br />
<br />
With a DEFERRABLE unique index, uniqueness is enforced as of the end of the statement, rather than after each row update as with a simple<br />
index. This is a bit slower sometimes but is a lifesaver if you need to do this sort of update.<br />
<br />
It is also possible to have the uniqueness check enforced as of the end of the transaction, rather than after each statement. This helps<br />
if you need to do "conflicting" updates that require more than one SQL statement to complete. For example:<br />
marc=# CREATE TABLE test (a int primary key deferrable, b text);<br />
marc=# INSERT INTO test values (1, 'x'), (2, 'y');<br />
marc=# BEGIN;<br />
marc=# SET CONSTRAINTS ALL DEFERRED;<br />
marc=# UPDATE test SET a = 2 WHERE b = 'x';<br />
marc=# UPDATE test SET a = 1 WHERE b = 'y';<br />
marc=# COMMIT;<br />
<br />
If one doesn't want to perform a SET CONSTRAINTS each time, the constraint can also be declared as INITIALLY DEFERRED:<br />
CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);<br />
<br />
Keep in mind that the list of records to be checked at the end of the statement or transaction has to be stored somewhere. So be careful of not doing this for millions of records at once. This is one of the reasons that unique indexes aren't DEFERRABLE by default, even though a strict reading of the SQL spec would require it.<br />
<br />
===New frame options for window functions===<br />
<br />
If you don't know window functions yet, you'd better learn about them. You can start here : [http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions waiting-for-84-window-functions]. They make writing certain kind of queries much easier.<br />
<br />
New options have been added for declaring frames of windowing functions. Let's use this table (not having a better example…)<br />
marc=# SELECT * FROM salary ;<br />
entity | name | salary | start_date<br />
-----------+-----------+---------+---------------<br />
R&amp;D | marc | 700.00 | 2010-02-15<br />
Accounting | jack | 800.00 | 2010-05-01<br />
R&amp;D | maria | 700.00 | 2009-01-01<br />
R&amp;D | kevin | 500.00 | 2009-05-01<br />
R&amp;D | john | 1000.00 | 2008-07-01<br />
R&amp;D | tom | 1100.00 | 2005-01-01<br />
Accounting | millicent | 850.00 | 2006-01-01<br />
Here is a window function example, without declaring the frame:<br />
marc=# SELECT entity, name, salary, start_date,<br />
avg(salary) OVER (PARTITION BY entity ORDER BY start_date)<br />
FROM salary;<br />
<br />
entity | name | salary | start_date | avg <br />
-----------+-----------+---------+---------------+-----------------------<br />
Accounting | millicent | 850.00 | 2006-01-01 | 850.0000000000000000<br />
Accounting | jack | 800.00 | 2010-05-01 | 825.0000000000000000<br />
R&amp;D | tom | 1100.00 | 2005-01-01 | 1100.0000000000000000<br />
R&amp;D | john | 1000.00 | 2008-07-01 | 1050.0000000000000000<br />
R&amp;D | maria | 700.00 | 2009-01-01 | 933.3333333333333333<br />
R&amp;D | kevin | 500.00 | 2009-05-01 | 825.0000000000000000<br />
R&amp;D | marc | 700.00 | 2010-02-15 | 800.0000000000000000<br />
The frame is the group of records over which the window function is run. Of course, if the frame isn't explicitly declared, there is a default one.<br />
<br />
Here is the same query, with an explicit frame:<br />
marc=# SELECT entity, name, salary, start_date,<br />
avg(salary) OVER (PARTITION BY entity ORDER BY start_date<br />
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)<br />
FROM salary;<br />
<br />
entity | name | salary | start_date | avg <br />
-----------+-----------+---------+---------------+-----------------------<br />
Accounting | millicent | 850.00 | 2006-01-01 | 850.0000000000000000<br />
Accounting | jack | 800.00 | 2010-05-01 | 825.0000000000000000<br />
R&amp;D | tom | 1100.00 | 2005-01-01 | 1100.0000000000000000<br />
R&amp;D | john | 1000.00 | 2008-07-01 | 1050.0000000000000000<br />
R&amp;D | maria | 700.00 | 2009-01-01 | 933.3333333333333333<br />
R&amp;D | kevin | 500.00 | 2009-05-01 | 825.0000000000000000<br />
R&amp;D | marc | 700.00 | 2010-02-15 | 800.0000000000000000<br />
In this example, the frame is a 'range' frame, between the start of the partition (the group of similar rows) and the current row (not exactly the current row, but let's put that aside for now, read the documentation if you want to learn more). One can see, the average (avg) function is evaluated from the frame's first row (grouped together records) and the current row.<br />
<br />
First new feature: as of 9.0, the frame can be declared to be between the current row and the end of the partition:<br />
marc=# SELECT entity, name, salary, start_date,<br />
avg(salary) OVER (PARTITION BY entity ORDER BY start_date<br />
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)<br />
FROM salary;<br />
<br />
entity | name | salary | start_date | avg <br />
-----------+-----------+---------+---------------+----------------------<br />
Accounting | millicent | 850.00 | 2006-01-01 | 825.0000000000000000<br />
Accounting | jack | 800.00 | 2010-05-01 | 800.0000000000000000<br />
R&amp;D | tom | 1100.00 | 2005-01-01 | 800.0000000000000000<br />
R&amp;D | john | 1000.00 | 2008-07-01 | 725.0000000000000000<br />
R&amp;D | maria | 700.00 | 2009-01-01 | 633.3333333333333333<br />
R&amp;D | kevin | 500.00 | 2009-05-01 | 600.0000000000000000<br />
R&amp;D | marc | 700.00 | 2010-02-15 | 700.0000000000000000<br />
Second new feature: frames can be declared as 'x previous records to y next records'. There is no point with this example, but let's do it anyway::<br />
marc=# SELECT entity, name, salary, start_date,<br />
avg(salary) OVER (PARTITION BY entity ORDER BY start_date<br />
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)<br />
FROM salary;<br />
<br />
entity | name | salary | start_date | avg <br />
-----------+-----------+---------+---------------+-----------------------<br />
Accounting | millicent | 850.00 | 2006-01-01 | 825.0000000000000000<br />
Accounting | jack | 800.00 | 2010-05-01 | 825.0000000000000000<br />
R&amp;D | tom | 1100.00 | 2005-01-01 | 1050.0000000000000000<br />
R&amp;D | john | 1000.00 | 2008-07-01 | 933.3333333333333333<br />
R&amp;D | maria | 700.00 | 2009-01-01 | 733.3333333333333333<br />
R&amp;D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333<br />
R&amp;D | marc | 700.00 | 2010-02-15 | 600.0000000000000000<br />
The frame is still limited to the partition (see tom's record, for instance: jack's record isn't use for it's average).<br />
<br />
If one wanted the same query, with a moving average on three rows, not reset on each partition switch (still no practical use):<br />
marc=# SELECT entity, name, salary, start_date,<br />
avg(salary) OVER (ORDER BY entity, start_date<br />
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)<br />
FROM salary;<br />
<br />
entity | name | salary | start_date | avg <br />
-----------+-----------+---------+---------------+----------------------<br />
Accounting | millicent | 850.00 | 2006-01-01 | 825.0000000000000000<br />
Accounting | jack | 800.00 | 2010-05-01 | 916.6666666666666667<br />
R&amp;D | tom | 1100.00 | 2005-01-01 | 966.6666666666666667<br />
R&amp;D | john | 1000.00 | 2008-07-01 | 933.3333333333333333<br />
R&amp;D | maria | 700.00 | 2009-01-01 | 733.3333333333333333<br />
R&amp;D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333<br />
R&amp;D | marc | 700.00 | 2010-02-15 | 600.0000000000000000<br />
<br />
In short, a powerful tool to be mastered, even if I couldn't provide a good example.<br />
<br />
===Sort in aggregates===<br />
<br />
This feature is a subtle one: the result of an aggregate function may depend on the order it receives the data.<br />
<br />
Of course, we're not talking about count, avg, but of array_agg, string_agg…<br />
<br />
This is nice, as this will showcase string_agg, which is another 9.0 feature, killing two birds with one stone.<br />
<br />
Let's start again with our salary table. We want the list of employees, concatenated as a single value, grouped by entity. It's going into a spreadsheet…<br />
marc=# SELECT entity,string_agg(name,', ') FROM salary GROUP BY entity;<br />
entity | string_agg <br />
-----------+-------------------------------<br />
Accounting | stephanie, etienne<br />
R&amp;D | marc, maria, kevin, john, tom<br />
That's already nice. But I want them sorted in alphabetical order, because I don't know how to write a macro in my spreadsheet to sort this data.<br />
marc=# SELECT entity,string_agg(name,', ' ORDER BY name) FROM salary GROUP BY entity;<br />
entity | string_agg <br />
-----------+-------------------------------<br />
Accounting | etienne, stephanie<br />
R&amp;D | john, kevin, marc, maria, tom<br />
To use this new feature, the sort clause must be inserted inside the aggregate function, without a comma to separate it from the parameters.<br />
<br />
==Database Administration==<br />
<br />
DBA is a hard and often thankless job -- especially if that's not your job title. 9.0 includes new and improved features to make that job a bit easier.<br />
<br />
===Better VACUUM FULL===<br />
<br />
Until now, VACUUM FULL was very slow. This statement can recover free space from a table to reduce its size, mostly when VACUUM itself hasn't been run frequently enough.<br />
<br />
It was slow because of the way it operated: records were read and moved one by one from their source bloc to a bloc closer to the beginning of the table. Once the end of the table was emptied, this empty part was removed.<br />
<br />
This strategy was very inefficient: moving records one by one creates a lot of random IO. Moreover, during this reorganization, indexes had to be maintained, making everything even more costly, and fragmenting indexes. It was therefore advised to reindex a table just after a VACUUM FULL.<br />
<br />
The VACUUM FULL statement, as of version 9.0, creates a new table from the current one, copying all the records sequentially. Once all records are copied, index are created back, and the old table is destroyed and replaced.<br />
<br />
This has the advantage of being much faster. VACUUM FULL still needs an AccessExclusiveLock while running though. The only drawback of this method compared to the old one, is that VACUUM FULL can use as much as two times the size of the table on disk, as it is creating a new version of it.<br />
<br />
Let's now compare the runtimes of the two methods. In both cases, we prepare the test data as follows (for 8.4 and 9.0)<br />
marc=# CREATE TABLE test (a int);<br />
CREATE TABLE<br />
marc=# CREATE INDEX idxtsta on test (a);<br />
CREATE INDEX<br />
marc=# INSERT INTO test SELECT generate_series(1,1000000);<br />
INSERT 0 1000000<br />
marc=# DELETE FROM test where a%3=0; -- making holes everywhere<br />
DELETE 333333<br />
marc=# VACUUM test;<br />
VACUUM<br />
With 8.4:<br />
marc=# \timing<br />
Timing is on.<br />
marc=# VACUUM FULL test;<br />
VACUUM<br />
Time: 6306,603 ms<br />
marc=# REINDEX TABLE test;<br />
REINDEX<br />
Time: 1799,998 ms<br />
So around 8 seconds.<br />
With 9.0:<br />
marc=# \timing<br />
Timing is on.<br />
marc=# VACUUM FULL test;<br />
VACUUM<br />
Time: 2563,467 ms<br />
That still doesn't mean that VACUUM FULL is a good idea in production. If you need it, it's probably because your VACUUM policy isn't appropriate.<br />
<br />
===application_name in pg_stat_activity===<br />
<br />
In a monitoring session:<br />
marc=# SELECT * from pg_stat_activity where procpid= 5991;<br />
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query<br />
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------<br />
16384 | marc | 5991 | 10 | marc | psql | | -1 | 2010-05-16 13:48:10.154113+02 | | | f | &lt;IDLE&gt;<br />
(1 row)<br />
In the '5991' session:<br />
marc=# SET application_name TO 'my_app';<br />
SET<br />
Back to the monitoring session:<br />
>marc=# SELECT * from pg_stat_activity where procpid= 5991;<br />
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query<br />
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+-----------------+----------------<br />
16384 | marc | 5991 | 10 | marc | my_app | | -1 | 2010-05-16 13:48:10.154113+02 | | 2010-05-16 13:49:13.107413+02 | f | &lt;IDLE&gt;<br />
(1 row)<br />
It's your job to set this up correctly in your program or your sessions. Your DBA will thank you for this, at last knowing who runs what on the database easily.<br />
<br />
===Per database+role configuration===<br />
Instead of being able to set up configuration variables per database or per user, one can now set them up for a certain user in a certain database:<br />
<br />
marc=# ALTER ROLE marc IN database marc set log_statement to 'all';<br />
ALTER ROLE<br />
To know who has which variables set-up in which user+database, there is a new psql command:<br />
marc=# \drds<br />
List of settings<br />
role | database | settings<br />
-----+----------+-----------------<br />
marc | marc | log_statement=all<br />
(1 row)<br />
There was a catalog change to store this:<br />
Table "pg_catalog.pg_db_role_setting"<br />
Column | Type | Modifier<br />
------------+--------+----------<br />
setdatabase | oid | not null<br />
setrole | oid | not null<br />
setconfig | text |<br />
<br />
===Log all changed parameters on a postgresql.conf reload===<br />
<br />
Here is an example, the log_line_prefix parameter has been changed:<br />
LOG:&nbsp; received SIGHUP, reloading configuration files<br />
&lt;%&gt; LOG:&nbsp; parameter "log_line_prefix" changed to "&lt;%u%%%d&gt; "<br />
<br />
===Better unique constraints error messages===<br />
<br />
With 8.4: <br />
marc=# INSERT INTO test VALUES (1);<br />
ERROR: duplicate key value violates unique constraint "test_a_key"<br />
With 9.0:<br />
marc=# INSERT INTO test VALUES (1);<br />
ERROR: duplicate key value violates unique constraint "test_a_key"<br />
DETAIL: Key (a)=(1) already exists.<br />
This will make diagnosing constraint violation errors much easier.<br />
<br />
===vacuumdb --analyze-only===<br />
<br />
As the parameter indicates, one can now use vacuumdb to run analyze only. It may be useful for cronjobs for instance.<br />
<br />
<br />
<br />
==Performance==<br />
<br />
It wouldn't be a new version of PostgreSQL if it didn't get faster, now would it? While 9.0 is not a "performance release", it does add new features which make some specific operations up to 1000% faster.<br />
<br />
===64 bit binaries for Windows===<br />
<br />
It is now possible to compile PostgreSQL for Windows as a 64-bit binary, and the PostgreSQL project is releasing 64-bit packages.<br />
<br />
This has a number of advantages for Windows users: better performance on 64-bit number operations (like BIGINT and BIGSERIAL), the ability to use over 2GB of work_mem, and enhanced compatibility with 64-bit versions of PostgreSQL running on Linux. This last is particularly important given Hot Standby.<br />
<br />
Note, however, that there is no evidence for now the 500MB shared_buffers size limit before performance degrades seen on the 32 bits version for Windows is solved with this 64 bit version, though. There is also the limitation that many 3rd-party open-source libraries are not available in 64-bit for Windows, so you may not be able to add all PostgreSQL extensions. Test reports welcome!<br />
<br />
===Join Removal===<br />
<br />
This new optimization allows us to remove unnecessary joins from SQL execution plans.<br />
<br />
When using automatically generated SQL, such as from ORM (Object Relation Mapping) tools it is possible for the SQL to be sub-optimal. Removing unnecessary joins can improve query plans by an order of magnitude in some cases.<br />
<br />
This is particularly important for databases that use many joins and nested views.<br />
<br />
marc=# CREATE TABLE t1 (a int);<br />
CREATE TABLE<br />
marc=# CREATE TABLE t2 (b int);<br />
CREATE TABLE<br />
marc=# CREATE TABLE t3 (c int);<br />
CREATE TABLE<br />
We put a little bit of data with a generate_series…<br />
<br />
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);<br />
QUERY PLAN <br />
------------------------------------------------------------------------------<br />
Merge Right Join (cost=506.24..6146.24 rows=345600 width=8)<br />
Merge Cond: (t3.c = t1.a)<br />
-&gt; Sort (cost=168.75..174.75 rows=2400 width=4)<br />
Sort Key: t3.c<br />
-&gt; Seq Scan on t3 (cost=0.00..34.00 rows=2400 width=4)<br />
-&gt; Materialize (cost=337.49..853.49 rows=28800 width=8)<br />
-&gt; Merge Join (cost=337.49..781.49 rows=28800 width=8)<br />
Merge Cond: (t1.a = t2.b)<br />
-&gt; Sort (cost=168.75..174.75 rows=2400 width=4)<br />
Sort Key: t1.a<br />
-&gt; Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)<br />
-&gt; Sort (cost=168.75..174.75 rows=2400 width=4)<br />
Sort Key: t2.b<br />
-&gt; Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)<br />
<br />
For now, everything is normal, and we have the same behavior in 8.4. But let's imagine that on t3, there is a UNIQUE constraint on the 'c' column. In this case, the join on t3 doesn't serve any purpose, theoretically speaking: the number of rows returned won't change, neither will their content. It's because the column is UNIQUE, the join is a LEFT JOIN, and no column of t3 is retrieved. If the column wasn't UNIQUE, the join could bring more rows. If that wasn't a LEFT JOIN, the join could ignore some rows.<br />
<br />
With 9.0:<br />
marc=# ALTER TABLE t3 ADD UNIQUE (c);<br />
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "t3_c_key" for table "t3"<br />
ALTER TABLE<br />
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);<br />
QUERY PLAN <br />
------------------------------------------------------------------<br />
Merge Join (cost=337.49..781.49 rows=28800 width=8)<br />
Merge Cond: (t1.a = t2.b)<br />
-&gt; Sort (cost=168.75..174.75 rows=2400 width=4)<br />
Sort Key: t1.a<br />
-&gt; Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)<br />
-&gt; Sort (cost=168.75..174.75 rows=2400 width=4)<br />
Sort Key: t2.b<br />
-&gt; Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)<br />
(8 rows)<br />
<br />
===IS NOT NULL can now use indexes===<br />
<br />
For this demonstration, we will compare the 8.4 and 9.0 versions (the table I created contains mostly nulls):<br />
<br />
With 8.4:<br />
marc=# EXPLAIN ANALYZE SELECT max(a) from test;<br />
QUERY PLAN <br />
------------------------------------------------------------------------------------------------------------------------------------------------<br />
Result (cost=0.03..0.04 rows=1 width=0) (actual time=281.320..281.321 rows=1 loops=1)<br />
InitPlan 1 (returns $0)<br />
-&gt; Limit (cost=0.00..0.03 rows=1 width=4) (actual time=281.311..281.313 rows=1 loops=1)<br />
-&gt; Index Scan Backward using idxa on test (cost=0.00..29447.36 rows=1001000 width=4) (actual time=281.307..281.307 rows=1 loops=1)<br />
Filter: (a IS NOT NULL)<br />
Total runtime: 281.360 ms<br />
(6 rows)<br />
With 9.0:<br />
marc=# EXPLAIN ANALYZE SELECT max(a) from test;<br />
QUERY PLAN <br />
--------------------------------------------------------------------------------------------------------------------------------------------<br />
Result (cost=0.08..0.09 rows=1 width=0) (actual time=0.100..0.102 rows=1 loops=1)<br />
InitPlan 1 (returns $0)<br />
-&gt; Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)<br />
-&gt; Index Scan Backward using idxa on test (cost=0.00..84148.06 rows=1001164 width=4) (actual time=0.089..0.089 rows=1 loops=1)<br />
Index Cond: (a IS NOT NULL)<br />
Total runtime: 0.139 ms<br />
(6 rows)<br />
The difference is that 9.0 only scans the not-null keys in the index. 8.4 has to go check in the table (Filter step, when 9.0 uses an index condition). In this precise use case, the gain is really big.<br />
<br />
===Use of index to get better statistics on the fly===<br />
<br />
Before starting to explain this new feature, let's talk about histograms: PostgreSQL, like some other databases, uses a statistical optimizer. This means that when planning a query it has (or should have) an approximately correct idea of how many records each step of the query will bring back. In order to do this, it uses statistics, such as the approximate number of records in a table, its size, most common values, and histograms. PostgreSQL use these to get estimates about the number of records brought back by a WHERE clause on a column, depending on the value or range asked in this WHERE clause.<br />
<br />
In some cases, these histograms are rapidly out of date, and become a problem, for certain SQL queries. For instance, a log table in which timestamped records would be inserted, and from which we would most of the time want to get the records from the last 5 minutes.<br />
<br />
In this specific case, it was impossible before 9.0 to get correct statistics. Now, when PostgreSQL detects while planning that a query asks for a 'range scan' on a value larger than the largest of the histogram (or smaller than the smallest), that is, the largest detected value during the last statistics calculation, and this column has an index, it gets the max (or min) value for this column using the index BEFORE really executing the query, in order to get more realistic statistics. As PostgreSQL uses an index for this, there HAS to be an index, of course.<br />
<br />
Here comes an example. The a column of the test table has already been filled with a lot of dates, all in the past. It's statistics are up to date.<br />
<br />
It's 13:37, and I haven't inserted anything after 13:37 yet.<br />
marc=# EXPLAIN ANALYZE select * from test where a &gt; '2010-06-03 13:37:00';<br />
QUERY PLAN <br />
--------------------------------------------------------------------------------------------------------------<br />
Index Scan using idxtsta on test (cost=0.00..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)<br />
Index Cond: (a &gt; '2010-06-03 13:37:00'::timestamp without time zone)<br />
Total runtime: 0.027 ms<br />
(3 rows)<br />
Everything's normal. The upper boundary of the histogram is '2010-06-03 13:36:16.830007' (this information comes from pg_stats). There is no way of guessing how many records are larger than 13:37, and with 8.4, PostgreSQL would have continued estimating '1' until the next analyze.<br />
marc=# DO LANGUAGE plpgsql<br />
$$<br />
DECLARE<br />
i int;<br />
BEGIN<br />
FOR i IN 1..10000 LOOP<br />
INSERT INTO test VALUES (clock_timestamp());<br />
END LOOP;<br />
END<br />
$$<br />
;<br />
DO<br />
(I must say I really like 'DO').<br />
We just inserted 10000 records with a date larger than 13:37.<br />
marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a &gt; '2010-06-03 13:37:00';<br />
QUERY PLAN <br />
-----------------------------------------------------------------------------------------------------------------------<br />
Index Scan using idxtsta on test (cost=0.00..43.98 rows=1125 width=8) (actual time=0.012..13.590 rows=10000 loops=1)<br />
Index Cond: (a &gt; '2010-06-03 13:37:00'::timestamp without time zone)<br />
Total runtime: 23.567 ms<br />
(3 rows)<br />
The estimated rows isn't 0 or 1 anymore. The statistics haven't been updated, though:<br />
marc=# SELECT last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'test';<br />
last_autoanalyze <br />
-------------------------------<br />
2010-06-03 13:36:21.553477+02<br />
(1 row)<br />
We still have a one magnitude error in the evaluation (10 times). But it's not that bad: without this enhancement, it would be of four magnitudes (10,000). Anyway, a much smaller error makes it more likely we'll get a good plan out of this kind of queries.<br />
<br />
===Explain buffers, hashing statistics, xml, json, yaml, new optional explain syntax===<br />
<br />
Here is EXPLAIN ANALYZE as we all know it:<br />
marc=# EXPLAIN ANALYZE SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;<br />
QUERY PLAN <br />
---------------------------------------------------------------------------------------------------------------------------------<br />
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=0.444..0.453 rows=6 loops=1)<br />
-&gt; Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.104..0.423 rows=6 loops=1)<br />
-&gt; Bitmap Heap Scan on fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.040..0.154 rows=30 loops=1)<br />
Recheck Cond: ((b &gt;= 1000) AND (b &lt;= 300000))<br />
-&gt; Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.023..0.023 rows=30 loops=1)<br />
Index Cond: ((b &gt;= 1000) AND (b &lt;= 300000))<br />
-&gt; Index Scan using pere_pkey on pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=30)<br />
Index Cond: (pere.a = fils.b)<br />
Total runtime: 0.560 ms<br />
(9 rows)<br />
To get access to the new available information, use the new syntax::<br />
<br />
EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] instruction<br />
For instance:<br />
marc=# EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true) SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;<br />
QUERY PLAN<br />
-------------------------------------------------------------------------------------------------------------------------------------<br />
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=1.326..1.336 rows=6 loops=1)<br />
Output: pere.a, sum(fils.c)<br />
Buffers: shared hit=58 read=40<br />
-&gt; Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.278..1.288 rows=6 loops=1)<br />
Output: pere.a, fils.c<br />
Buffers: shared hit=58 read=40<br />
-&gt; Bitmap Heap Scan on public.fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.073..0.737 rows=30 loops=1)<br />
Output: fils.b, fils.c<br />
Recheck Cond: ((fils.b &gt;= 1000) AND (fils.b &lt;= 300000))<br />
Buffers: shared hit=4 read=28<br />
-&gt; Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.030..0.030 rows=30 loops=1)<br />
Index Cond: ((fils.b &gt;= 1000) AND (fils.b &lt;= 300000))<br />
Buffers: shared hit=3<br />
-&gt; Index Scan using pere_pkey on public.pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.013..0.014 rows=0 loops=30)<br />
Output: pere.a<br />
Index Cond: (pere.a = fils.b)<br />
Buffers: shared hit=54 read=12<br />
Total runtime: 1.526 ms<br />
(18 rows)<br />
VERBOSE displays the 'Output' lines (it already existed on 8.4).<br />
<br />
BUFFERS displays data about buffers (input-output operations performed by the query): hit is the number of blocks obtained directly from shared_buffers, read is the number of blocs asked to the operating system. Here, there was very little data in shared_buffers.<br />
<br />
One can also ask for another formatting than plain text. For a user, it's not useful. For people developing GUIs over EXPLAIN, it simplifies development as they can get rid of an 'explain' parser (and its potential bugs), and use a more standard one, such as XML.<br />
<br />
Costs display can also be deactivated with COSTS false.<br />
<br />
===Per tablespace seq_page_cost/random_page_cost===<br />
<br />
marc=# ALTER TABLESPACE pg_default SET ( random_page_cost = 10, seq_page_cost=5);<br />
ALTER TABLESPACE<br />
We just changed random_page_cost and seq_page_cost for all the objects contained in pg_default. What for ?<br />
<br />
The use case is when different tablespaces have different performance: for instance, you have some critical data on a SSD drive, or historical data on an older disk array, slower than the brand new array you use for active data. This makes it possible to tell PostgreSQL that all your tablespaces don't always behave the same way, from a performance point of view. This is only useful, of course, for quite big databases.<br />
<br />
===Force distinct statistics on a column===<br />
<br />
This makes it possible to set the number of different values for a column. This mustn't be used lightly, but only when ANALYZE on this column can't get a good value.<br />
<br />
Here's how to do this:<br />
marc=# ALTER TABLE test ALTER COLUMN a SET (n_distinct = 2);<br />
ALTER TABLE<br />
ANALYZE has to be run again for this to be taken into account:<br />
marc=# ANALYZE test;<br />
ANALYZE<br />
Let's try now:<br />
marc=# EXPLAIN SELECT distinct * from test;<br />
QUERY PLAN <br />
------------------------------------------------------------------<br />
HashAggregate (cost=6263.00..6263.02 rows=2 width=8)<br />
-&gt; Seq Scan on test (cost=0.00..5338.00 rows=370000 width=8)<br />
(2 rows)<br />
This is an example of what SHOULDN'T be done : there REALLY is 370 000 distinct values in my table. Now my execution plans may be very bad.<br />
<br />
If n_distinct is positive, it's the number of distinct values.<br />
<br />
If it's negative (between 0 and -1), it's the multiplying factor regarding the number of estimated records in the table: for instance, -0.2 means that there is a distinct value for each 5 records of the table.<br />
<br />
0 brings the behavior back to normal (ANALYZE estimates distinct by itself).<br />
<br />
Don't change this parameter, unless you are completely sure you have correctly diagnosed your problem. Else, be assured performance will be degraded.<br />
<br />
===Statement logged by auto_explain===<br />
<br />
auto_explain contrib module will now print the statement with its plan, which will make it much easier to use.<br />
<br />
===Buffers accounting for pg_stat_statements===<br />
<br />
This already very useful contrib module now also provides data about buffers. pg_stat_statements, as a reminder, collects statistics on the queries run on the database. Until now, it stored the query's code, number of executions, accumulated runtime, accumulated returned records. It now collects buffer operations too.<br />
marc=# SELECT * from pg_stat_statements order by total_time desc limit 2;<br />
-[ RECORD 1 ]-------+---------------------<br />
userid | 10<br />
dbid | 16485<br />
query | SELECT * from table1 ;<br />
calls | 2<br />
total_time | 0.491229<br />
rows | 420000<br />
shared_blks_hit | 61<br />
shared_blks_read | 2251<br />
shared_blks_written | 0<br />
local_blks_hit | 0<br />
local_blks_read | 0<br />
local_blks_written | 0<br />
temp_blks_read | 0<br />
temp_blks_written | 0<br />
-[ RECORD 2 ]-------+---------------------<br />
userid | 10<br />
dbid | 16485<br />
query | SELECT * from table2;<br />
calls | 2<br />
total_time | 0.141445<br />
rows | 200000<br />
shared_blks_hit | 443<br />
shared_blks_read | 443<br />
shared_blks_written | 0<br />
local_blks_hit | 0<br />
local_blks_read | 0<br />
local_blks_written | 0<br />
temp_blks_read | 0<br />
temp_blks_written | 0<br />
When this contrib is installed, one can now answer these questions:<br />
* Which query has the biggest accumulated runtime ?<br />
* Which query generates the most IO operations ? (we still can't know if data has been found in the Operating System's cache)<br />
* Which query uses mostly the cache (and hence won't be faster if we make it bigger) ?<br />
* Which query modifies the most blocks ?<br />
* Who does sorting ?<br />
'temp' and 'local' are the buffer operations relative to temporary tables and other local operations (sorts, hashes) to a database backend.<br />
<br />
<br />
<br />
==Stored Procedures==<br />
<br />
PostgreSQL isn't just a database, it's a whole application platform. Many of our users write entire applications using stored procedures and functions. So, it's no surprise that 9.0 brings a number of improvements in database procedural code:<br />
<br />
===PL/pgSQL by default===<br />
<br />
You won't have to add PL/pgSQL in databases, as it will be installed by default. This has been requested for a long time.<br />
<br />
===Many improvements on PL languages.===<br />
<br />
Many languages have been vastly improved, PLPerl for instance. Read the release notes if you want more details, there are too many to detail here.<br />
<br />
===Anonymous Functions (aka Anonymous Blocks)===<br />
<br />
This new feature is for creating run-once functions. Effectively, this allows you to run stored procedure code on the command line or dynamically as you can on SQL Server and Oracle. Unlike those, however, PostgreSQL allows you to run an anonymous function in any procedural language which is installed of the more than a dozen which PostgreSQL supports.<br />
<br />
This feature will be very useful for schema upgrade scripts for instance. Here is a slightly different version of the 'GRANT SELECT ON ALL TABLES' that will be seen later in this document, giving SELECT rights to a bunch of tables, depending on the table owner, and excluding two schemas:<br />
DO language plpgsql $$<br />
DECLARE<br />
vr record;<br />
<br />
BEGIN<br />
<br />
FOR vr IN SELECT tablename FROM pg_tables WHERE tableowner = 'marc' AND schemaname NOT IN ('pg_catalog','information_schema')<br />
LOOP<br />
EXECUTE 'GRANT SELECT ON ' || vr.tablename || ' TO toto';<br />
END LOOP;<br />
END<br />
$$;<br />
As of 8.4, this would have required creating a function (with CREATE FUNCTION), running it, then removing it (with DROP FUNCTION). All of this requiring having rights to do this. 9.0 simplifies performing this kind of procedures.<br />
<br />
Anonymous functions are also called "anonymous code blocks" in the software industry.<br />
<br />
===Named Parameter Calls===<br />
<br />
Combined with the Default Parameters introduced in version 8.4, named parameters allow for dynamic calling of functions with variable numbers of arguments, much as they would be inside a programming language. Named parameters are familiar to users of SQL Server or Sybase, but PostgreSQL does one better by supporting both named parameter calls ''and'' function overloading.<br />
<br />
The chosen syntax to name parameters is the following:<br />
CREATE FUNCTION test (a int, b text) RETURNS text AS $$<br />
DECLARE<br />
value text;<br />
BEGIN<br />
value := 'a is ' || a::text || ' and b is ' || b;<br />
RETURN value;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
Until now, we wrote:<br />
SELECT test(1,'foo');<br />
test <br />
-------------------------<br />
a is 1 and b is foo<br />
(1 row)<br />
Now this explicit syntax can be used:<br />
SELECT test( b:='foo', a:=1);<br />
test <br />
-------------------------<br />
a is 1 and b is foo<br />
(1 row)<br />
Named parameters should eliminate the need to write many overloaded "wrapper" functions. Note that this does add a backwards compatibility issue; you are no longer able to rename function parameters using a REPLACE command, but must now drop and recreate the function.<br />
<br />
===ALIAS keyword===<br />
<br />
ALIAS can now be used. As its name suggests, it can be used to alias variable names to other names.<br />
<br />
The syntax is <tt>new_name ALIAS FOR old_name</tt>. This is put in the DECLARE section of PL/pgSQL code.<br />
<br />
It has two main use cases:<br />
* to give names to PL function variables:<br />
myparam ALIAS FOR $0<br />
* to rename potentially conflicting variables. In a trigger for instance:<br />
<br />
new_value ALIAS FOR new<br />
: (without this, we might have conflicted with the NEW variable in the trigger function).<br />
<br />
==Advanced Features==<br />
<br />
Some features in PostgreSQL are cutting-edge database features which are pretty much "PostgreSQL only". It's why we're the "most advanced database". These features enable new types of applications.<br />
<br />
===Exclusion constraints===<br />
<br />
Exclusion constraints are very similar to unique constraints. They could be seen as unique constraints using other operators than '=': A unique constraint defines a set of columns for which two records in the table cannot be identical.<br />
<br />
To illustrate this, we will use the example provided by this feature's author, using the temporal data type, that he also developed. This datatype stores time ranges, that is 'the time range from 10:15 to 11:15'.<br />
<br />
First, we need to retrieve the temporal module here: http://pgfoundry.org/projects/temporal/ , then compile and install it as a contrib (run the provided sql script). We may also need to install the btree_gist module as a contrib. From source, one can run 'make install' in contrib/btree_gist directory for the same.<br />
<br />
CREATE TABLE reservation<br />
(<br />
room TEXT,<br />
professor TEXT,<br />
during PERIOD);<br />
<br />
ALTER TABLE reservation ADD CONSTRAINT test_exclude EXCLUDE USING gist (room WITH =,during WITH &amp;&amp;);<br />
Doing this, we declare that a record should be rejected (exclusion constraint) if there already is one verifying the two conditions 'the same room' and 'be in intersection for the time range' (the &amp;&amp; operator).<br />
marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'mark', 'tech room', period('2010-06-16 09:00:00', '2010-06-16 10:00:00'));<br />
INSERT 0 1<br />
marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'john', 'chemistry room', period('2010-06-16 09:00:00', '2010-06-16 11:00:00'));<br />
INSERT 0 1<br />
marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'mark', 'chemistry room', period('2010-06-16 10:00:00', '2010-06-16 11:00:00'));<br />
ERROR: conflicting key value violates exclusion constraint "test_exclude"<br />
DETAIL: Key (room, during)=(chemistry room, [2010-06-16 10:00:00+02, 2010-06-16 11:00:00+02)) conflicts with existing key (room, during)=(chemistry room, [2010-06-16 09:00:00+02, 2010-06-16 11:00:00+02)).<br />
The insert is forbidden, as the chemistry room is already reserved from 9 to 11.<br />
<br />
Exclusion constraints may also be used with arrays, geographic data, or other non-scalar data in order to implement advanced scientific and calendaring applications. No other database system has this feature.<br />
<br />
===Message passing in NOTIFY/pg_notify===<br />
<br />
Messages can now be passed using NOTIFY. Here is how:<br />
* Subscribe in session 1 to the 'instant_messenging' queue.<br />
: Session 1:<br />
marc=# LISTEN instant_messenging;<br />
LISTEN<br />
* Send a notification through 'instant_messenging', from another session<br />
: Session 2:<br />
marc=# NOTIFY instant_messenging, 'You just received a message';<br />
NOTIFY<br />
* Check the content of the queue in the first session<br />
: Session 1:<br />
marc=# LISTEN instant_messenging;<br />
LISTEN<br />
Asynchronous notification "instant_messenging" with payload "You just received a message" received from server process with PID 5943.<br />
<br />
So we can now associate messages (payloads) with notifications, making NOTIFY even more useful.<br />
<br />
Let's also mention the new pg_notify function. With it, the second session's code can also be:<br />
SELECT pg_notify('instant_messenging','You just received a message');<br />
This can simplify some code, in the case of a program managing a lot of different queues.<br />
<br />
===Hstore contrib enhancements===<br />
<br />
This already powerful contrib module has become even more powerful:<br />
* Keys and values size limit has been removed.<br />
* GROUP BY and DISTINCT can now be used.<br />
* New operators and functions have been added.<br />
<br />
An example would take too long, this module has a lot of features. Read the documentation at once !<br />
<br />
===Unaccent filtering dictionary===<br />
<br />
Filtering dictionaries can now be set up. This is about Full Text Search dictionaries.<br />
<br />
These dictionaries' purpose it applying a first filter on words before lexemizing them. The module presented here is the first one to use this mechanism. Filtering can consist in removing words or modifying them.<br />
<br />
Unaccent doesn't remove words, it removes accents (all diacritic signs, as a matter of fact), replacing accentuated characters with non-accentuated ones (many people, at least in French, don't type them). Unaccent is a contrib module.<br />
<br />
Installing it, as all contrib modules, is as easy as<br />
psql mydb &lt; contribs_path/unaccent.sql.<br />
We'll now follow unaccent's documentation, the example being filtering french words.<br />
<br />
Let's create a new 'fr' dictionary (keeping standard 'french' dictionary clean): <br />
marc=# CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );<br />
CREATE TEXT SEARCH CONFIGURATION<br />
The next statement alters the 'fr' setup for word and alike lexemes. These now have to go through unaccent and french_stem instead of only french_stem.<br />
marc=# ALTER TEXT SEARCH CONFIGURATION fr<br />
>ALTER MAPPING FOR hword, hword_part, word<br />
>WITH unaccent, french_stem;<br />
>ALTER TEXT SEARCH CONFIGURATION<br />
<br />
SELECT to_tsvector('fr','Hôtels de la Mer');<br />
to_tsvector <br />
-------------------<br />
'hotel':1 'mer':4<br />
(1 row)<br />
<br />
marc=# SELECT to_tsvector('fr','Hôtel de la Mer') @@ to_tsquery('fr','Hotels');<br />
?column?<br />
----------<br />
t<br />
(1 row)<br />
It's now easy, without changing even one line of code in the client application, and keeping accentuated characters in the database, to look up words without taking accents into account.<br />
<br />
<br />
<br />
<br />
===get_bit and set_bit for bit strings===<br />
<br />
Here is a very simple example. This tool can manipulate bits in a bit() independently.<br />
marc=# SELECT set_bit('1111'::bit(4),2,0);<br />
set_bit<br />
---------<br />
1101<br />
(1 row)<br />
<br />
<br />
marc=# SELECT get_bit('1101'::bit(4),2);<br />
get_bit<br />
---------<br />
0<br />
(1 row)<br />
<br />
<br />
=Backwards Compatibility and Upgrade Issues=<br />
<br />
The PostgreSQL project has a commitment not to break backwards compatibility when we can possibly avoid doing so. Sometimes, however, we have to break things in order to add new features or fix longstanding problematic behavior. Some of these issues are documented below.<br />
<br />
==PL/pgSQL changes which may cause regressions==<br />
<br />
There are two changes in PL/pgSQL which may break code which works in 8.4 or earlier, meaning PL/pgSQL functions should be audited before before migrating to 9.0 to prevent possible runtime errors. A lot of these come about due to uniting the lexer for SQL and PL/pgSQL, which is an important architectural improvement which has made several new features possible.<br />
<br />
===Removal of column/variable name ambiguity===<br />
<br />
In 8.4 and earlier, PL/PgSQL variables will take preference over a table or view column with the same name. While this behaviour is consistent, it is a potential source of coding errors. 9.0 will throw a runtime error if this situation occurs:<br />
<br />
marc=# DO LANGUAGE plpgsql<br />
$$<br />
DECLARE<br />
a int;<br />
BEGIN<br />
SELECT a FROM test;<br />
END<br />
$$<br />
;<br />
ERROR: column reference "a" is ambiguous<br />
LINE 1: select a from test<br />
DETAIL: It could refer to either a PL/pgSQL variable or a table column.<br />
QUERY: select a from test<br />
CONTEXT: PL/pgSQL function "inline_code_block" line 4 at SQL statement<br />
<br />
This behaviour can be altered globally in postgresql.conf, or on a per function basis by inserting one of these three options in the function declaration:<br />
<br />
#variable_conflict error (default)<br />
#variable_conflict use_variable (variable name name takes precedence - pre-9.0 behaviour)<br />
#variable_conflict use_column (column name takes precedence)<br />
<br />
The [http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html manual] contains more details.<br />
<br />
===Reserved words===<br />
<br />
From 9.0, use of unquoted reserved words as PL/PgSQL variable names is no longer permitted:<br />
<br />
marc=# DO LANGUAGE plpgsql<br />
$$<br />
DECLARE<br />
table int;<br />
BEGIN<br />
table :=table+1;<br />
END<br />
$$<br />
;<br />
ERROR: syntax error at or near "table"<br />
LINE 6: table :=table+1;<br />
<br />
The correct syntax is:<br />
<br />
marc=# DO LANGUAGE plpgsql<br />
$$<br />
DECLARE<br />
"table" int;<br />
BEGIN<br />
"table" :="table"+1;<br />
END<br />
$$<br />
;<br />
DO<br />
<br />
Best practice is of course to avoid reserved words completely.</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Running_%26_Installing_PostgreSQL_On_Native_Windows&diff=11255Running & Installing PostgreSQL On Native Windows2010-06-23T05:01:51Z<p>Mwtoews: mention and link to 64 bit version</p>
<hr />
<div>{{Languages}}<br />
<br />
== Supported Platforms ==<br />
=== What versions of Windows does PostgreSQL run on?===<br />
PostgreSQL is supported on Windows 2000 and above. It will run on 32 and 64 bit systems. We aim to support new versions of Windows in the PostgreSQL major version following their release at the latest.<br />
<br />
=== I heard that NT4 is supported. Is that true? ===<br />
Although not officially supported, PostgreSQL will run on Windows NT4 <br />
with a few minor issues including:<br />
* The installer will not work correctly, thus you will need to compile and install the code manually.<br />
* PostgreSQL uses a feature in the NTFS filesystem called 'reparse points' to implement tablespaces. Reparse points are not available in NT4, hence tablespaces cannot be used.<br />
* There is no 'runas.exe' included in Windows NT4 as standard, making it difficult to start PostgreSQL from an administrative account.<br />
It should also be noted that very little testing has been done on NT4.<br />
<br />
=== What about Windows 95/98/ME? ===<br />
<br />
PostgreSQL requires functionality that is not available on these<br />
platforms and will not run on them. If you need to run PostgreSQL<br />
on these platforms, you can look at the <br />
[http://www.postgresql.org/files/documentation/faqs/text/FAQ_CYGWIN Cygwin]<br />
port, which has basic support for 9x platforms.<br />
<br />
=== Is there a 64-bit build of PostgreSQL for Windows? ===<br />
<br />
There is a [[64bit Windows port]] planned for the 9.0 release.<br />
<br />
However, the 32bit build of PostgreSQL works on 64bit platforms, and in fact there are good reasons why a 64bit build is less important with PostgreSQL than for some other software:<br />
* PostgreSQL relies on the operating system to do much of the caching of data. Since the main limitation of running a 32-bit process is the total amount of memory being addressable, a system that is based on the database engine doing all the caching cannot address all the memory on a system with say 16Gb memory. For PostgreSQL, we leave much of that caching to the operating system which can address it, as long as the OS is 64-bit.<br />
* PostgreSQL uses a multi-process architecture, not multi-threaded. In a multi-threaded database server, all client connections share the memory space, and are again limited by the total addressable memory region. With PostgreSQL, you could easily have more than 1Gb per backend (if necessary) without running out of memory, which significantly pushes away the point when you need to go to 64-bit.<br />
* In some cases it may actually be better to run a 32-bit build of PostgreSQL to reduce memory usage. In a 64-bit server, every pointer and every integer will take twice as much space as in a 32bit server. That overhead can be significant, and is most likely unnecessary.<br />
<br />
== Installation ==<br />
<br />
=== What do I need to install PostgreSQL on Windows? ===<br />
<br />
The easiest way to install PostgreSQL on Windows is with the Windows<br />
Installer package available from the PostgreSQL FTP site and mirrors.<br />
This will install a precompiled version of PostgreSQL along with<br />
pgAdmin (a graphical administration and management program), a<br />
selection of 'contrib' modules to provide additional specialised<br />
functionality, and a choice of procedural languages.<br />
<br />
To use the installer, you need a computer running Windows 2000, XP,<br />
2003 or Vista with the Windows Installer service installed. The installer<br />
will create a service account if required, and initialise the database<br />
cluster.<br />
<br />
The Installer may be downloaded from <br />
[http://www.postgresql.org/ftp/binary/ here].<br />
<br />
=== What do I need to compile PostgreSQL from source code? ===<br />
<br />
The Windows compilation FAQ at <br />
[http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html FAQ_MINGW]<br />
contains full details of compilation of the PostgreSQL source code on <br />
Windows systems using MinGW. PostgreSQL 8.3 and above can be built using<br />
Microsoft Visual C++ 2005 (2008 should also work, but is currently <br />
untested). See the [http://www.postgresql.org/docs/8.3/static/install-win32-full.html documentation]<br />
for details.<br />
<br />
=== Why do I need a non-administrator account to run PostgreSQL under? ===<br />
<br />
When a hacker gains entry to a computer using a software bug in a<br />
package, she gains the permissions of the user account under which the<br />
service is run. Whilst we do not know of any such bugs in PostgreSQL,<br />
we enforce the use of a non-administrative service account to minimise<br />
the possible damage that a hacker could do should they find and utilise<br />
a bug in PostgreSQL to hack the system.<br />
<br />
This has long been common practice in the Unix world, and is starting to<br />
become standard practice in the Windows world as well as Microsoft and<br />
other vendors work to improve the security of their systems.<br />
<br />
Note, that with the release of PostgreSQL 8.2, it is possible to run under<br />
a administrative account. PostgreSQL 8.2 and above are able to irrevocably<br />
give up administrative rights at startup thus ensuring the rest of the <br />
system remains secure in the extremely unlikely event that PostgreSQL<br />
becomes compromised.<br />
<br />
=== Can I install PostgreSQL on a FAT partition? ===<br />
<br />
PostgreSQL's number one priority is the integrity of your data. FAT and<br />
FAT32 filesystems simply do not offer the reliability required to allow<br />
this. In addition, the lack of security features offered by FAT make it<br />
impossible to secure the raw data files from unauthorised modification.<br />
Finally, PostgreSQL utilises a feature called 'reparse points' to<br />
implement tablespaces. This feature is not available on FAT partitions.<br />
<br />
The NTFS file system is a journalled filesystem offering much better<br />
reliability and crash recovery. In addition, it has a comprehensive<br />
access control system and offers the reparse point functionality used<br />
by PostgreSQL.<br />
<br />
For this reason, the PostgreSQL installer package will not initialise<br />
a database cluster on anything but an NTFS partition. The server and<br />
utilities may be installed on any partition type.<br />
<br />
It is recognised however, that on some systems such as developer's<br />
PCs, FAT partitions may be the only choice. In such cases, you can<br />
simply install PostgreSQL as normal, but without initialising the<br />
database cluster. When the installation has finished, manually run<br />
the 'initdb.exe' program on the FAT partition. Security and<br />
reliability will be compromised however, and any attempts to create<br />
tablespaces will fail.<br />
<br />
=== What filesystem permissions does PostgreSQL require? ===<br />
The PostgreSQL service account needs ''read'' permissions on<br />
all directories leading up to the service directory. It needs ''write'' permissions ''only''<br />
on the data directory. Specifically, it should ''not'' be granted anything other than ''read''<br />
permissions on the directories containing binary files. (All directories below the installation<br />
directory are set by the installer, so unless you change something, there should be no problem with this).<br />
<br />
PostgreSQL also needs ''read'' permissions on system DLL files like kernel32.dll and user32.dll<br />
(among others), which is normally granted by default, and on the CMD.EXE binary, which may in some<br />
scenarios be locked down and need opening.<br />
<br />
If you are running PostgreSQL on a multi-user system, you should remove the permissions from all<br />
non-administrative users from the PostgreSQL directories. No user ''ever'' needs permissions on the<br />
PostgreSQL files - all communication is done through the libpq connection. Direct access to data files <br />
can lead to information disclosure or system instability!<br />
<br />
=== Why can't I select Unicode as an encoding? ===<br />
Starting with PostgreSQL 8.1, (UTF8) UNICODE encoding is fully supported on Windows. 8.0 is no longer supported.<br />
<br />
=== I installed in a non-english language, but all messages show up in english! ===<br />
The language choice made during the installation only selects which language is used by the installer. To<br />
change the language of the messages of the installed product, make sure you have installed the ''National language<br />
support'' feature. Then edit your postgresql.conf file and change the value of the ''lc_messages'' parameter<br />
to the language you want.<br />
<br />
== Common installation errors ==<br />
<br />
=== PostgreSQL and/or the installer crashes at startup, fails to start or hangs on start ===<br />
<br />
By far most common reasons for this are antivirus and firewalls. If<br />
you have any firewall software installed on your machine, try either<br />
disabling it or uninstalling it. If you have any antivirus software<br />
installed, you '''must''' disable it for the directories that are to<br />
be used by PostgreSQL. If that still does not help, it may be required<br />
to completely uninstall the software from the machine. See the next<br />
section for more information about specific products.<br />
<br />
There is also an issue if you have installed ''cygwin'', and the cygwin\bin directory is present<br />
in the system PATH variable. There are DLL files in the cygwin directory related to interpreted languages (TCL,<br />
perl, python) that contain bugs that can cause the installer or the installed version of PostgreSQL to either hang<br />
or crash. Remove the cygwin\bin directory from your path before running the installer!<br />
<br />
=== What Anti-Virus software is compatible? ===<br />
<br />
The systems used to build the Windows installers all run either Sophos AV or AVG Free Edition, and<br />
those systems pass a full set of PostgreSQL regression tests running those programs.<br />
<br />
Specific issues have been reported with the ''nod32'' antivirus<br />
product. If you are using this product, add "postmaster.exe" to the list<br />
of excluded processes (available under advanced options). This has been<br />
reported to fix the problem.<br />
<br />
Specific issues have also been reported with McAfee and Panda anti-virus <br />
software and NetLimiter network monitoring software. While some people do have <br />
PostgreSQL working with these software packages, there is no specific or even <br />
recommend solutions that have not worked in some cases, so the issues would appear<br />
to be installation specific, sometimes even requiring uninstallation.<br />
<br />
=== The installer claims the specified account is an administrator, but it isn't! ===<br />
<br />
Most likely, the specified account is an administrator or power user,<br />
even though you aren't aware of it. The check used by the installer<br />
specifically checks for membership in the Administrators or<br />
Power Users group. Work your way backwards using Local Users and Groups -<br />
open the Administrators group, see who's a member. Then check any<br />
groups (domain or local) that are a member of the Administrators group,<br />
and any groups members of that group etc. PostgreSQL checks any level<br />
of nested groups.<br />
<br />
=== I get the error "the user has not been granted the requested logon type at this computer" or similar ===<br />
Make sure the specified PostgreSQL account has the "Log on as a service"<br />
and "Log on locally" rights. The "Log on locally" is only required for<br />
the install part, and can be removed once the installation is completed<br />
if security policies require it. (Rights are granted and revoked using<br />
the "Local Security Policy" MMC snapin. "Log on locally" is default,<br />
and "Log on as a service" will normally be granted automatically by<br />
the installer).<br />
<br />
If you still have this problem, enable auditing (also using<br />
the "Local Security Policy" snapin) and let us know what other rights<br />
were required in your setup.<br />
<br />
Note that if your computer is a member of a domain, the settings of<br />
the security policies may be controlled at the domain level using<br />
Group Policy.<br />
<br />
=== I'm getting permissions errors when installing/running initdb ===<br />
<br />
Make sure the PostgreSQL service account has permissions on the directories leading up to the one<br />
you have installed into. The installer will set permissions on the install directory but not on<br />
parent directories of it.<br />
<br />
=== I'm getting an error message that says PostgreSQL cannot be installed from a Terminal Services session ===<br />
This is unfortunately so. The PostgreSQL backend won't run from a TS session, and in order to do initdb the installer<br />
has to start a standalone backend. Therefore, installation has to be performed from the console. Note that if you are<br />
using Windows Server 2003, you can get remote access to the actual console and not just an administrative session.<br />
To do this, start the Remote Desktop Connection by executing <I>mstsc /console</I>, and then connect as usual.<br />
This will lock the local console of the server and give you control over that session. In this scenario,<br />
PostgreSQL should install just fine.<br />
<br />
=== I changed the directory but PostgreSQL was still installed in the default directory ===<br />
<br />
Make sure you changed the directory of the ''root feature''. The PostgreSQL installer permits changing the directory<br />
of some individual features. If you change the root feature ("PostgreSQL"), any subfeatures (such as "Database Server")<br />
will automatically inherit this value as default, but if you only change a subfeature the rest of the installation<br />
will remain in the default location.<br />
<br />
=== When upgrading the installer says I don't have permissions to install a service, but I'm logged in as administrator ===<br />
The workaround for this issue is to first ''uninstall'' the previous version. Note that this will ''not'' remove<br />
your data!! Then reinstall the new version, and make sure you use the ''exact same directories''. That should solve the<br />
problem. Note that this is only possible in minor version upgrades (such as 8.0.1 to 8.0.2) that do not require a dump/reload.<br />
<br />
=== I get an error message saying that the installation package cannot be opened ===<br />
This can be caused by two things. The most likely one is that you double-clicked<br />
on the MSI file in the ZIP file. For the installation to work, you must uncompress the<br />
whole ZIP file to a temporary directory and then run it.<br />
The other reason is that your download is broken. Try to download again, using a different<br />
mirror.<br />
<br />
=== How do I delete the service account - it's not listed under users! ===<br />
Windows sometimes hide some accounts from the GUI tools, so they cannot be removed from there. This includes<br />
the auto-created service account for PostgreSQL (that may be left over from a previous installation). To delete this<br />
account, use the NET command on the commandline:<br />
NET USER <username> /DELETE<br />
Where <username> is the windows login name of the user, for example ''postgres''.<br />
<br />
== Common runtime issues ==<br />
<br />
=== I'm getting "dynamic load error" when installing a procedural language ===<br />
<br />
Most commonly this means that the actual language DLL for the procedural language is missing. The PostgreSQL<br />
DLLs only contains the language bindings, and require the language distribution DLLs to be present<br />
in the system PATH. For a list of the current required DLLs for the different procedural languages,<br />
please see [http://pginstaller.projects.postgresql.org the installation instructions].<br />
<br />
To find out exactly which DLL files are missing, you can use the ''depends'' tool from Microsoft.<br />
It is available in the Windows Support Tools, that are on the Windows CD as a separate install. Just run<br />
''depends plpython.dll'' (for PL/python) to show which imports are missing.<br />
<br />
=== I'm seeing a lot of postgres.exe processes even though I only started the server once ===<br />
<br />
This is normal. PostgreSQL uses a multi-process architecture. In an empty system you will see anything from two to<br />
five processes. Once clients start to connect, the number of processes will increase.<br />
<br />
=== How do I set an environment variable? ===<br />
PostgreSQL uses environment variables for several settings. To change an environment variable in most<br />
Windows versions, go into Properties for My Computer, then into Advanced. Note that there are two sets of <br />
environment variables - one for the system which applies to all users and one for the current user. If you <br />
want an environment variable to affect the PostgreSQL service, you must change the system variable. After <br />
changing a system variable, you must restart the service.<br />
<br />
=== I cannot run with more than about 125 connections at once, despite having capable hardware ===<br />
When running as a service, you may experience failures with more than approximately 125 simultaneous connections. <br />
This can happen because some of the libraries that PostgreSQL is dependent on are dependent on user32.dll which allocates<br />
memory from an area known as the Desktop Heap. The desktop heap is assigned to each logon session, with non-interactive<br />
sessions typically being assigned 512KB. Each postgres process that runs typically consumes approximately 3.2KB of desktop<br />
heap, which coupled with other overhead can exhaust the allocated heap at somewhere around 125 connections. This doesn't<br />
happen when running from the command line (or more precisely, happens with much higher numbers of connections) because the <br />
interactive logon session is typically allocated 3MB of Desktop Heap.<br />
<br />
You can increase the non-interactive Desktop Heap by modifying the third SharedSection value in the registry as described<br />
in this [http://support.microsoft.com/kb/184802 Microsoft Knowledgebase article]. Note that this should be done<br />
with great care as specifying a value that is excessively high may prevent your system from booting.<br />
<br />
[[Category:FAQ]] [[Category:Windows]]</div>Mwtoewshttps://wiki.postgresql.org/index.php?title=Replication,_Clustering,_and_Connection_Pooling&diff=10880Replication, Clustering, and Connection Pooling2010-05-19T20:57:07Z<p>Mwtoews: no trac wiki for pgcluster</p>
<hr />
<div>==Introduction==<br />
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.<br />
<br />
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:<br />
<br />
* 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.<br />
* 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.<br />
<br />
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<br />
[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.<br />
<br />
==Features in the Core of PostgreSQL==<br />
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 Team's statement].<br />
<br />
*[[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.<br />
<br />
*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]].<br />
<br />
==Comparison matrix==<br />
<br />
This page is being overhauled at [[Clustering]]<br />
<br />
{| 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;"<br />
|- style="background: #ececec"<br />
! Program<br />
! License<br />
! Maturity<br />
! Replication Method<br />
! Sync<br />
! Connection Pooling<br />
! Load Balancing<br />
! Query Partitioning<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://pgcluster.projects.postgresql.org/ PGCluster]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | See version details on site<br />
| bgcolor="#ffffaa" | Master-Master<br />
| bgcolor="#ffffaa" | Synchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ddffdd" | Yes<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | '''pgpool-I'''<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Stable<br />
| bgcolor="#ffffaa" | Statement-Based Middleware<br />
| bgcolor="#ffffaa" | Synchronous<br />
| bgcolor="#ddffdd" | Yes<br />
| bgcolor="#ddffdd" | Yes<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://pgpool.projects.postgresql.org/ pgpool-II]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Recent release<br />
| bgcolor="#ffffaa" | Statement-Based Middleware<br />
| bgcolor="#ffffaa" | Synchronous<br />
| bgcolor="#ddffdd" | Yes<br />
| bgcolor="#ddffdd" | Yes<br />
| bgcolor="#ddffdd" | Yes<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://slony.info/ slony-I]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Stable<br />
| bgcolor="#ffffaa" | Master-Slave<br />
| bgcolor="#ffffaa" | Asynchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://bucardo.org/ Bucardo]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Stable<br />
| bgcolor="#ffffaa" | Master-Master, Master-Slave<br />
| bgcolor="#ffffaa" | Asynchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://wiki.postgresql.org/wiki/Skytools Londiste]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Stable<br />
| bgcolor="#ffffaa" | Master-Slave<br />
| bgcolor="#ffffaa" | Asynchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://www.commandprompt.com/products/mammothreplicator/ Mammoth]<br />
| bgcolor="#ffffaa" | BSD<br />
| bgcolor="#ffffaa" | Stable<br />
| bgcolor="#ffffaa" | Master-Slave<br />
| bgcolor="#ffffaa" | Asynchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
|-<br />
! style="text-align:block;" bgcolor="#ececec" | [http://www.rubyrep.org/ rubyrep]<br />
| bgcolor="#ffffaa" | MIT<br />
| bgcolor="#ffffaa" | Recent Release<br />
| bgcolor="#ffffaa" | Master-Master, Master-Slave<br />
| bgcolor="#ffffaa" | Asynchronous<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
| bgcolor="#ffaaaa" | No<br />
|}<br />
<br />
==Replication==<br />
<br />
Aside from Warm Standby, mentioned above...<br />
<br />
*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.<br />
<br />
* 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 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.<br />
<br />
* 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.<br />
<br />
* "Mammoth Replicator" - BSD - http://www.commandprompt.com/products/mammothreplicator/ - Former proprietary solution, now open source. Uses a central logging process to distribute data changes amongst nodes. Essentially a fork of Postgres, as the changes are written directly into the backend. <br />
<br />
* "Bucardo" - BSD License - http://bucardo.org/ - Trigger-based, asynchronous, multi-master or master-slave, written using plperl.<br />
<br />
* Cybertec, an Austrian company, offers a proprietary packaging of PGCluster. They simply call it PostgreSQL Multimaster-Replication, see http://www.cybertec.at.<br />
<br />
* [[Londiste_Tutorial|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.<br />
<br />
* [http://www.continuent.com/index.php?option=com_content&task=view&id=212&Itemid=169 Continuent uni/cluster], proprietary and the related Sequoia (jdbc, formerly known as c-jdbc)<br />
<br />
* [http://www.postgres-r.org Postgres-R] is still in development. It features eager and thus conflict-free, but async multi-master replication.<br />
<br />
* 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.<br />
<br />
* "Daffodil Replicator" - GPL - http://sourceforge.net/projects/daffodilreplica/ <br />
<br />
* "RubyRep" - MIT License - http://www.rubyrep.org/ - Ruby based, asynchronous, multi-master replication system, which supports Postgres and MySQL.<br />
<br />
* "pg_comparator" - BSD License - http://pgfoundry.org/projects/pg-comparator/ - Perl-based, table-level async master-slave "diff" and "patch" method of replication. Low configuration overhead.<br />
<br />
===Inactive projects===<br />
* Slony-II<br />
* PGReplication<br />
<br />
==Clustering==<br />
<br />
* [http://www.greenplum.com/index.php?page=greenplum-database Greenplum Database] (formerly Bizgres MPP), proprietary. 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.<br />
<br />
*[http://www.enterprisedb.com/products/gridsql.do GridSQL for EnterpriseDB Advanced Server] (formerly ExtenDB) <br />
<br />
*sequoia (jdbc, formerly known as c-jdbc)<br />
<br />
* [[PL/Proxy]] - database partitioning system implemented as PL language.<br />
<br />
*[http://db.cs.yale.edu/hadoopdb/hadoopdb.html HadoopDB] - A MapReduce layer put in front of a cluster of postgres back end servers. Shared-nothing clustering.<br />
<br />
==Connection Pooling and Acceleration==<br />
<br />
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.<br />
<br />
Some programs that implement connection pooling are:<br />
* [[PgBouncer]]<br />
* [http://pgpool.projects.postgresql.org/ pgpool]<br />
<br />
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.<br />
<br />
==Credits==<br />
<br />
Sources for the initial information on this page include:<br />
*[http://archives.postgresql.org/pgsql-performance/2007-06/msg00264.php replication thread]<br />
*[http://archives.postgresql.org/pgsql-general/2007-08/msg00085.php pgpool2 vs sequoia]<br />
*[http://archives.postgresql.org/pgsql-hackers/2006-10/msg00810.php Postgresql Caching]<br />
<br />
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].<br />
<br />
Sources for more information located but not yet integrated into here:<br />
* [http://bristlecone.continuent.org/uploads/bristlecone/HomePage/PG_East-Scale-Out-Benchmarks_FINAL2.pdf Portable Scale-Out Benchmarks for PostgreSQL] by Robert Hodges<br />
* [http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf High Availability and PostgreSQL] by Gavin Sherry<br />
<br />
[[Category:Replication]][[Category:Administration]][[Category:Performance]][[Category:Clustering]]</div>Mwtoews