nyuuzyou/wikis huggingface dataset:
Compile and Install from source code

A set of instructions for building Postgres from a source archive. 
= Acquire the source code =
=== From postgresql.org ===
The source code can be found at git.postgresql.org or in the main file browser. 
=== From GitHub ===
The mirror of Postgres on GitHub provides a "Download ZIP" option.
= Build+install the source code =
Detailed instructions can be found in the documentation.
=== Red Hat requirements===
The following packages are needed for building Postgres with SSL support:
 sudo yum install -y bison-devel readline-devel zlib-devel openssl-devel wget ccache
 sudo yum groupinstall -y 'Development Tools'
=== Ubuntu requirements ===
The following packages are needed for building Postgres with including SSL support:
 sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache pkg-config
You might also consider installing the package libipc-run-perl if you want to run TAP tests and the package meson for building
CompressedTables

v0.1 11 Dec 2007
Frequently with large data archives there is a requirement to reduce the
footprint of the data to allow longer term storage costs to be reduced.
For Insert-only data we might imagine we can reduce the size of tables
by removing unused tuple header information. Although that is possible,
repeated headers compress fairly well, so it seems easier to tackle the
problem directly by having compressed tables.
Using a streaming library like zlib, it will be easy to read/write data
files into a still-usable form but with much reduced size. Access to a
compressed table only makes sense as a SeqScan. That would be handled by
introducing tablespace-specific access costs, discussed below. Indexes
on compressed tables would still be allowed, but would hardly ever be
used.
Access would probably be via tablespace-specific storage managers. So
implement mdcompress.c alongside md.c in src/backend/storage/smgr. If
that implementation route was chosen, it would then allow the
compression option to be made at tablespace level, so commands would be:
CREATE TABLESPACE tablespacename LOCATION 'directory' [COMPRESS];
(ALTER TABLESPACE support is going to be much more complex, so leave
that alone for now)
So when we copy a read-only table to another tablespace the compression
would take place without additional syntax at ALTER TABLE level. i.e.
nothing new goes in tblcmds.c. Cool.
mdcompress.c seems fairly straightforward, though we would need to think
about how to implement smgr_nblocks() since lseek-ing to get it won't
work because the file size is smaller than the actual decompressed
table. Perhaps with an info file that contains something like an index
metapage where we can read the number of blocks. Ideas?
In the first pass, I would only allow compressed read-only tables. In
time we might allow Inserts, Updates and Deletes though the latter two
will never be very effective. So my initial approach does *not* allow
writing directly to a compressed table. A couple of reasons why that
isn't desirable/easy:
If we write data straight to the table then any aborted loads will be
written to the table, so VACUUMing the table would need to re-compress
the table which sounds horrible. Plus hint status bits would need to be
set *after* the data was written. (Perhaps in conjunction with a
visibility map we can just forget that aspect of it). The other problem
is that blocks would need to be written out of shared buffers
sequentially, which unfortunately we do not guarantee. We can force that
in the smgr layer by keeping track of last written blockid and then
writing all cached blocks up the currently requested one, but that seems
problematic. I'd say if we want more, we do that in the next release, or
at least the next phase of development. So we would prevent the direct
use of CREATE TABLE on a COMPRESSED tablespace, for now.
I'm open to arguments that we don't need this at all because filesystem
utilities exist that do everything we need. You're experience will be
good to hear about in regard to this feature.
Category:Historical informationCategory:Development
Continuous Integration

Here is some information about how to use a variety of free continuous integration services, for PostgreSQL hackers. You'll need a BitBucket, GitHub, GitLab or other public source repository account. Then you'll need to select one or more CI service and tell it to watch your account. In the case of the popular repo hosts you don't have to create a new account on the CI provider websites -- you just grant access. Finally you'll have to tell it how to build your branches with one or more control file in your source tree. You can add the control files in an extra commit that lives only in your feature development branch.
The unofficial PostgreSQL Cfbot Patch Tester uses a couple of these services to test patches posted to the -hackers mailing list, and the branches it creates contain some example control files that might be useful.
== Postgres CI on github ==
This is the recommended way to test a patch on various environmment, using the same CI than the unofficial commit fest bot. You can configure it on your own github repository following the instruction at https://git.postgresql.org/cgit/postgresql.git/tree/src/tools/ci/README. It's very easy and can be configured in a few minutes.
== AppVeyor ==
AppVeyor builds and tests code on Windows, Linux, macOS. Support for GitHub, GitHub Enterprise, Bitbucket, GitLab, VSTS, Kiln or custom publicly accessible repos. Discussion MingGW, Cygwin, MSVC appveyor.yml files from Peter Eisentraut
== Cirrus CI ==
Cirrus CI supports at least Windows, Linux, FreeBSD and macOS, so it currently has the widest range of operating systems targeted by PostgreSQL (and there may more soon). It supports only Github as a source, and can be enabled for your account very easily from Github Marketplace. It's free for open source projects.
Here is a work-in-progress example of how to use it to build a PostgreSQL branch on those four operating systems. Click the green checkmark to see results, and look at the top commit in that branch for the control files. The .cirrus.yml file contains a list of to-do items for further work...
== CodeCov ==
CodeCov is not a CI system, but is closely related and worth mentioning. If you configure Travis CI to build and test your branches with coverage enabled, you might also be interested to see code coverage information in a nice web interface on CodeCov.
== Travis CI ==
Travis CI supports Linux, macOS and maybe more. Works only with GitHub. Controlled by a .travis.yml file.
Contributor Gifts

The PostgreSQL Funds Group may commission gifts for contributors recognized for a major release. The Funds Group makes an effort to contact everyone, but, nevertheless, people get missed. This page describes those gifts and links to the acknowledgements of the release to see if you are to receive a gift and how to inquire.
Please contact the team in charge of distribution at contributor-gifts(at)postgresql(dot)org with any questions. Please also send a mailing address, formatted as if being printed for a label, for us to send the gift to when claiming a gift. Even if you have received a gift previously, it would be good to let us know whether your address has changed.
= Version 12 (2019-10-03) =
File:Pg12-challenge-coin.jpeg | 2-inch Challenge Coin
* v12 Contributor Acknowledgements
= Version 13 (2020-09-23) =
File:Pg13-challenge-coin.jpg | 2-inch Challenge Coin
Good luck symbols used:
* Elephant posing as a maneki-neko holding a koban
* horseshoe
* four leaf clover
* koi (in the form of a koinobori)
* evil eye
* acorn
* hamsa
* skeleton key
* cornicello
* red string
* lucky bamboo
* ladybug
v13 Contributor Acknowledgements
= Version 14 (2021-09-30) =
File:Pg14-challenge-coin.jpg | 2-inch Challenge Coin
Artistic representation of a homonym of "B-tree".
* v14 Contributor Acknowledgements
= Version 15 (2022-10-13) =
File:Pg15-challenge-coin.jpg | 2-inch Challenge Coin
The artwork incorporates "cohesion, support, and merging as represented via a color analogy. The choice to utilize all colors of the rainbow further represents inclusiveness/increased support."
* v15 Contributor Acknowledgements
= Version 16 (2023-09-14) =
File:Pg16-challenge-coin.png | 2-inch Challenge Coin
The artwork depicts a baker making cloud cakes with jays on (pun) the apron.
* v16 Contributor Acknowledgements
= Version 17 (2024-09-26) =
File:Pg17-challenge-coin.jpg | 2-inch Challenge Coin
* v17 Contributor Acknowledgements
ContributorListings

== Contributor Listing Draft Policy ==
People to be listed on the PostgreSQL contributor listings include only people who have made substantial, long-term contributions of time to the PostgreSQL project. One-time only contributions are not usually considered adequate for listing, unless they involve quite large amounts of code and time. Financial contributions get listed on the Sponsors page (TBD), not here.
Listings will last, on average, two years before being re-evaluated. For this purpose, all listings will in the future contain information on what exactly the person contributed and when, although this information might not be displayed. Contributor listings will be updated at least once a year.
Editing the contributor listings will be carried out by the Core Team.
== Core Team Section ==
This contains only the core team.
== Major Contributor Section ==
This contains listings of contributors who have made substantial positive impact on the development of the PostgreSQL Community.
== Contributor Section ==
Contains listings of developers who have made smaller code contributions to the PostgreSQL project. Also lists people who have made large, sustained non-code contributions of their own time to the PostgreSQL project, within the last two years.
== Past Contributors ==
Lists people who were in the Major Developer or Contributor section, but have stopped contributing for two years or more.
== Hacker Emeritus ==
Contains a listing of former core team members.
Category:Policies
Category:Community
Converting your data from MS SQL Server 7 to PostgreSQL 7.1.x

by Ryan C. Bonham <ryan@ryanbonham.com>
v1.00, Last updated 5th January 2002
== Prerequisites ==
* MS SQL 7 (of course)
* RedHat Linux 7.1
* PostgreSQL 7.1 RPM's
* PostgreSQL ODBC Client
== Directions ==
Follow the steps below to convert your Microsoft SQL Server 7 data to PostgreSQL.
From Your Redhat Machine
* As root, create a postgres user.
 adduser postgres
* Install the PostgreSQL RPM's on your machine.
* Start PostgreSQL for the first time.
 service PostgreSQL start
* Set PostgreSQL to run at system Startup.
 chkconfig --add PostgreSQL
* Login as the Postgres User
 su postgres
* Create a Blank Database
 createdb Databasename
From your SQL 7 Server or a machine with the SQL administration tools installed
# Install the PostgreSQL ODBC Drivers
# Create A New User or System DSN (Data Source) On Windows 2000, you can find this under the "Administrative Tools" Control Panel
## When Prompted for Driver to use, select "PostgreSQL"
## For Data Source enter the name you wish to give this connection. (e.g. Pgsql)
## For Database enter the name you used in Step 6 above
## For Server enter the IP or name of your PostgreSQL Database Server
## Leave the Username and password blank
## Under the Driver Options Select "Parse Statements" and Uncheck "Bools as char"
## Under "DataSource" Options select "Row Versioning"
# Open the SQL 7 Enterprise Manager
# Expand your server and select "Data Transformation Services"
# Open the "Local Packages"
# Select New package from the Action Menu
# Select "Microsoft OLE DB Provider for SQL Server" from the Data Menu
## Pick Your SQL Server and database you wish to export and press ok
# Select "Other Connection" from the Data menu
## For Data Source select "PostgreSQL"
## Select the DSN we created above (Pgsql)
## Enter your username (postgres)
# Select both Connections you just created
# Select "Add Transformation" from the Workflow menu
# Double click the Transformation Line that was just created
## On the sources tab select the table you wish to export
## On the destination tab select Create new
## Check the DataTypes and Column Lengths. Make any corrections and press ok
## On the Transformation screen make sure the source columns are pointing to the correct destination columns. You can change the Column mappings by selecting the appropriate columns and using the delete and insert buttons on the screen.
## Check the options on the Advanced Tab. You shouldn't need to make changes
## Press Ok
# Select Save from the package menu
# Select Run from the package menu
# Your data should now be exported. If you get any errors double check the data type and lengths are correct on the table you created in Step 11
Category:MySQL
Category:PostgreSQL 7.1
Corruption detection

If you think you might be suffering database corruption you're in the wrong place. Read and act on the Corruption article.
== Introduction ==
Detecting storage layer corruption inside PostgreSQL as early as possible is important. If left undetected, it could not only lead to wrong answers, it can also end up being copied during a base backup (affecting backups and replication). That is a major problem because users will get a false sense of confidence relying on their backup schedule.
If you think you might be suffering database corruption you're in the wrong place. Read and act on the Corruption article.
An article about steps to avoid and prevent database corruption can be found here.
== Types of Corruption ==
Theoretically, corruption can happen in many areas of hardware, including the memory or even the CPU. However, for this article we focus on corruption happening between the time a page leaves the address space of the backend process until the time it is read back in.
== Minor Challenges ==
=== Utilities and Background Checking ===
Utilities (such as pg_basebackup) should be updated to verify pages while creating the backup. Also, users might want a continuously-running daemon to slowly validate cold data.
=== Detecting Transposed Data ===
=== Zero Pages ===
== Major Challenges ==
=== Upgrade and On/Off ===
=== Torn Pages ===
Category:Administration
Corruption Detection and Containment

===Introduction===
PostgreSQL relies heavily on the filesystem returning the same data that was previously written. Unfortunately, due to the complexity of filesystems (and the storage systems upon which they rely), returning different (corrupt) data is common enough to cause serious problems. For large systems with many disks, you can expect this to happen with some regularity.
The largest problem is that these errors are silent, meaning that the system gives no indication that the data is corrupt, and it's left up to PostgreSQL to continue processing the corrupt data. Furthermore, because it's the filesystem returning the corrupt data, then taking a backup or syncing a new replica is likely to copy the corrupt block, leaving the backup/replica corrupt as well.
Checksums are a way to make the error known early, before returning results to the user and before copying the data to a backup/replica. That allows a number of potential solutions, the simplest of which is to replace the faulty hardware and restore from a backup.
===Requirements===
# Determine when a data page has been corrupted
# Determine when a CLOG page has been corrupted
# Determine when a temporary table or file has been corrupted
# Enable/Disable corruption detection online
# Upgrade from a system without corruption detection to a system with corruption detection
# Detect corruption before processing the data
# Detect corruption when taking a base backup or syncing a new replica
# Detect corruption in background or when offline (e.g. test a backup)
===High-Level Design===
# initdb-time option to enable checksums
## Add 16-bit checksum to every page in place of pd_tli
## When setting a hint bit, a full page image is now required if it's the first modification since the last checkpoint (otherwise, the xlog action can return early).
## Add bits to the page header so that we can differentiate between a page with checksums and one without. This will help satisfy the upgrade requirement later, by offering a transition state where some pages have checksums and some don't.
# detect zeroed pages
## WAL log all relation extensions
### For performance, probably need to extend in bulk (TODO: explore alternatives)
## always initialize all pages
# Add GUC to detect checksum failures in temporary tables and files (e.g. for Sort).
## TODO: what to do about UNLOGGED tables?
# Option to VACUUM, or other command, that allows turning checksums on/off while online.
## TODO: details
# Detect corruption in CLOG
## TODO: other SLRU?
# Background/offline checker, and checker in pg_basebackup
## Challenge: how to deal with a race condition where Postgres is writing data and the other utility is reading it (kind of like an online torn page)?
### Should be very rare: retry?
### Could also have a function in postgres that can recheck an individual block, so if the retry fails, then the user could be absolutely sure it's corrupt by asking postgres.
===High-Level Design===
Count elements in Array

Category:SQL
by Emanuel "3manuek"
If you are on PostgreSQL 8.3 or below you will need to add the unnest() function to convert an array to a set of rows.
Function:
CREATE FUNCTION count_elements(text[]) RETURNS text AS $BODY$
select string_agg(i || ':' || c, ' ' ) from (select i, count(*) c FROM (select unnest($1::text[]) i) i group by i order by c desc) foo;
$BODY$
LANGUAGE SQL;
Basic test:
postgres=# select count_elements('{1,2,3,4,2,1,2,3,4,5,3,1,2,3,43,1,2,3}'::text[]);
-[ RECORD 1 ]--+------------------------------
count_elements | 3:5 2:5 1:4 4:2 5:1 43:1
You can improve A LOT the previous function using specific data type for integers or date instead cast to text (which will improve the CPU consumption). 
The best of this is that you can combine with array_agg, i.e.:
postgres=# create table da_values as select round(random()*40) val from generate_series(1,100);
SELECT 100
postgres=# select array_agg(val) from da_values ;
 {33,25,23,6,9,5,17,18,36,28,23,23,40,21,13,12,23,30,38,30,14,10,27,24,22,13,1,18,0,3,28,33,28,12,40,37,17,17,15,13,5,38,36,4,19,8,16,2,38,14,32,12,24,19,36,6,32,37,24,32,1,12,25,29,24,25,26,1,2,1,13,6,38,9,11,17,18,27,18,16,2,10,28,25,29,24,31,21,21,15,13,22,27,38,11,10,23,37,11,25}
postgres=# select count_elements(array_agg(val::text)) from da_values ;
 count_elements 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 25:5 38:5 13:5 23:5 24:5 1:4 12:4 18:4 28:4 17:4 21:3 2:3 37:3 6:3 11:3 10:3 36:3 27:3 32:3 15:2 40:2 16:2 33:2 22:2 5:2 29:2 9:2 14:2 19:2 30:2 26:1 31:1 4:1 3:1 0:1 8:1
(1 row)
If you create a count_elements function to receive int[] instead text[], you won't need to cast the parameter before array_agg-it. This is IMPORTANT becuase this is a basic and example code, but if you are planning to run it seriously you *should* respect the types.
Other stuff you can add is "labels", i.e: "Element:25, Count:5" in the string_agg function inside count_elements.
== See also ==
Array Range Array Median
Count estimate

Category:Snippets
Authors: Erwin Brandstetter, Michael Fuhr
Comment by: Emanuel Calvo Franco
The basic SQL standard query to count the rows in a table is:
SELECT count(*) FROM table_name;
This can be rather slow because PostgreSQL has to check visibility for all rows, due to the MVCC model.
If you don't need an exact count, the current statistic from the catalog table pg_class might be good enough and is much faster to retrieve for big tables.
SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table_name';
 estimate
-----------
 100
Tables named "table_name" can live in multiple schemas of a database, in which case you get multiple rows for this query. To overcome ambiguity:
SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;
The cast to bigint formats the real number nicely, especially for big counts.
Quoting the manual for Postgres 13 on pg_class.reltuples:
 Number of live rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
If you didn't ANALYZE recently (after last changes), the estimates will be off more or less.
If you are running the autovacuum daemon as is the default for modern PostgreSQL, ANALYZE is run automatically, too (except for temporary tables which need manual attention). So the estimates should be good unless you had major changes very recently.
For more sophisticated queries (other than counting all rows from a table), or if you cannot SELECT from the catalog table pg_class (which every user can by default), consider the following function, that takes advantage of JSON formatting added in Postgres 9.0. (Older versions can still benefit from plpgsql function by Michael Fuhr.)
⚠️ Do not pass an unsanitized `query` to this function, as it is subject to SQL injection.
CREATE OR REPLACE FUNCTION count_estimate(
 query text
) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
 plan jsonb;
BEGIN
 EXECUTE FORMAT('EXPLAIN (FORMAT JSON) %s', query) INTO plan;
 RETURN plan->0->'Plan'->'Plan Rows';
END;
$$;
Demo:
CREATE TEMP TABLE tbl AS SELECT * FROM generate_series(1, 1000) AS t;
ANALYZE tbl;
SELECT count_estimate('SELECT * FROM tbl WHERE t < 100');
 count_estimate 
----------------
 100
EXPLAIN SELECT * FROM tbl WHERE t < 100;
 QUERY PLAN
------------------------------------------------------
 Seq Scan on tbl (cost=0.00..35.00 rows=100 width=4)
 Filter: (t < 100)
As you can see, it's an estimate - actual count would be 99.
Related web resources:
* Source material: Why PostgreSQL Instead of MySQL (also discusses how this is different in MySQL)
* Query alternatives on Stackoverflow: Fast way to discover the row count of a table in PostgreSQL