nyuuzyou/wikis huggingface dataset:
PUG Presentations

PostgreSQL_Related_Slides_and_Presentations
Here you can find a list of all Postgresql User Groups that have submitted Slides and Presentations.
* LAPUG - Los Angeles PostgreSQL Users Group
* SFPUG - San Francisco PostgreSQL Users Group
* NYCPUG - New York City PostgreSQL Users Group
* NYCDPUG - New York Capital District PostgreSQL Users Group
Category:Users group
PugsDrupalInstall

== Rough Notes ==
* dumped the postgres database
* pkgdb -F (clean up ports dir)
* portsnap fetch (to update ports)
uh oh.
 pugs# cd /
 pugs# du -sh .
 2.4G	.
 pugs# pwd
 /
 pugs# df
 Filesystem 1K-blocks Used Avail Capacity Mounted on
 /dev/da0s1g 195122110 173737996 5774346 97% /
 pugs# df -hl
 Filesystem Size Used Avail Capacity Mounted on
 /dev/da0s1g 186G 166G 5.5G 97% /
 pugs#
5/31/2009, sweet: 
 pugs:root /usr/local/etc/apache22/virtual_hosts> df -hl
 Filesystem Size Used Avail Capacity Mounted on
 /dev/da0s1g 186G 169G 2.3G 99% /
Plowing ahead...
* Downloaded version 5.18 of drupal
* Updated apache config: /usr/local/etc/apache22/virtual_hosts/www.pugs.postgresql.org.conf to point to test directory
* pg_dump -U drupal -d drupal > drupaldb_backup2.sql
* dropdb -U pgsql testdrupal
* createdb -U pgsql testdrupal -O drupal -E UTF8
* psql -U pgsql -d testdrupal < drupaldb_backup2.sql
Category:Software Ports
PyCon2013Booth

= PyCon 2013 Volunteer Booth Schedule =
This is a draft schedule for booth volunteers at PyCon 2013, for the PostgreSQL booth in the exhibit hall.
Thursday 4pm to 8pm: Josh Berkus, Quinn Weaver, Christophe Pettus
Friday 10am to 2pm: Josh Berkus, Jeff Frost
Friday 1:30pm to 5pm: Jignesh Shah*, Christophe Pettus
Saturday 10am to 2pm: Josh Berkus, Quinn Weaver
Saturday 1:30pm to 5pm: Josh Berkus, Steve Atkins*
The Thursday shift includes booth setup and the Vendor Reception. The Saturday afternoon shift includes booth pack-up.
Please mark yourself with a * if you need to be registered for PyCon as a booth volunteer, because you don't have PyCon registration otherwise.
PyCon 2017 Booth

The PostgreSQL community booth at PyCon in Portland, Oregon is hosted by the Portland PostgreSQL Users Group. The dates are May 18-20 at the Oregon Convention Center. Please sign up by editing this wiki or emailing one of us before the end of the first full week of April for the t-shirt order.
Contact one of us for more detail:
* Gabrielle Roth <gorthx at gmail.com>
* Grant Holly <gdholly at gmail.com>
* Mark Wong 
= Thursday May 18 (6pm to 8:30pm) - Opening Reception =
* Mark Wong
* Gabrielle Roth
= Friday May 19 (8am to 5pm) =
== 8am - 9:30am (High Traffic) ==
* Mark Wong
== 9:30am - 10:30am ==
* Mark Wong
== 10:30am - 11am (High Traffic) ==
* Mark Wong
* Josh Berkus
== 11am - 12:30pm ==
* Mark Wong
== 12:30pm - 1:30pm (High Traffic) ==
* Mark Wong
* Josh Berkus
== 1:30pm - 3:30pm ==
* Mark Wong
== 3:30pm - 4pm (High Traffic) ==
* Mark Wong
* Josh Berkus
== 4pm - 5pm ==
* Mark Wong
= Saturday May 20 (8am to 5pm) =
== 8am - 9:30am (High Traffic) ==
* Mark Wong
== 9:30am - 10:30am ==
* Mark Wong
== 10:30am - 11am (High Traffic) ==
* Robert Berry
* Mark Wong
== 11am - 12:30pm ==
* Robert Berry
* Mark Wong
== 12:30pm - 1:30pm (High Traffic) ==
* Robert Berry
* Mark Wong
== 1:30pm - 3:30pm ==
* Mark Wong
== 3:30pm - 4pm (High Traffic) ==
* Mark Wong
== 4pm - 5pm ==
* Mark Wong
PyPgDay2013

= PyPgDay 2013 =
See The PyPgDay page at pgExperts.com for full information about this event.
PyPgDay was held on March 13, 2013. It was presented by Disqus, and sponsored by Salesforce, Heroku, VMware, 2ndQuadrant, Apsalar, File-Away.UK, Pandora, PostgreSQL Experts, Translattice, Uber.com and Urban Mapping. It was additionally supported by Adobe, CommandPrompt and Jeff Davis.
== List of Talks ==
Below are the talks which were given at the PyPgDay. Where slides are available, they have been linked. Licensing information, if any, is as noted in the slides. If no licensing is given, assume All Rights Reserved.
Full information about the schedule is on the schedule page
There was no video recording for this event due to convention center rules.
* Evan Kiltzke, Uber.com: Migration to PostgreSQL
* Greg Smith, 2ndQuadrant, Monitoring PostgreSQL with Python
* Jignesh Shah, VMWware, Best Practices for HA and Replication in Virtualized Environments
* Jeff Davis, Aster Data, Using Range Types in your Applications
* Mike Clarke, Disqus, Sharding with the Django ORM
* Christophe Pettus, pgExperts, Extending PostgreSQL Into Python
* Fazal Majid, Apsalar, Scaling Mobile Analytics with PostgreSQL and Python
Lightning Talks Included:
* Python for Social Content Filtering - Satish Viswanatham, Cloudable
* Simple Python Interface to PostgreSQL using JSON - Robert Geiger, Translattice
* Deploying Postgres in the Cloud with Ubuntu - Mark Ramm, Canonical
* Query Better with HTSQL: Catherine Devlin, Dell KACE
* James Pye: py-postgresql and PL/Python3 (no slides)
* Christophe Pettus: New Transactions in the Django ORM
* Zain Memon: DIY Geocoder
* Dance! Ines Sombra, Engineyard
Python

There are several Python drivers for PostgreSQL. This is the incomplete feature matrix for them; please help complete it as you see fit.
In general, Python users want to use psycopg2 unless they have a strong reason to try another driver, most of which are no longer maintained. Since DBAPI allows drivers to have different semantics, porting applications from one driver to another is non-trivial.
Software 	License	Platforms 	Python versions	DB API 2.0	Native (uses libpq)	Last Release	Notes
Psycopg2 	LGPL 	Unix, Win32 	2.6-3.6 	yes 	yes 	2019 	Most popular python driver, required for most Python+Postgres frameworks
pg8000 	BSD 	any (pure Python)	3.3+ 	yes 	no 	2019 	Used by Web2Py. current updated official site
py-postgresql	BSD 	any (pure Python)	3.0+ 	yes 	no 	2018 	Pure Python with optional C accelerator modules,extensive custom API. Python 3 only.
PyGreSQL 	BSD 	Unix, Win32 	2.6 thru 3.6 	yes 	yes 	2017 	The first PostgreSQL adapter for Python. Still actively maintained.
ocpgdb 	BSD 	Unix 	2.3-2.6 	yes 	yes 	2010 	PG8.1+
bpgsql 	LGPL 	any (pure Python)	2.3-2.6 	yes 	no 	2009 	labeled alpha
aiopg 	BSD 	any 	3.52+ 	(ish) 	Native 	2019 	
More abandoned driver projects:
* pgasync, Twisted-native driver, no updates since 2005
* PoPy, no updates since 2003 - development was merged with pygresql
* pyPgSQL, no updates since 2006
There is similar page on the Python wiki
Python PostgreSQL Driver TODO
Category:Python
Query progress indication

Edit: there was a presentation on this in 2013. The source for that is here.
Postgres currently doesn't give any meaningful feedback about the query execution process to the user. This would be valuable for:
# reporting whether the execution of a query is blocked on a lock; many users in #postgresql are confused about why their query takes so long to execute, when in fact it is blocked on a lock -- Isn't this easily gleaned from the "waiting" boolean in pg_stat_activity?
# reporting the progress of a long-running analysis query. When interactively executing complex, long-running queries, providing feedback about how long they will take to execute (and approximate answers in the mean-time) would be cool
# reporting the progress of long-running utility queries. For example, it can be difficult to predict whether the runtime of a large CREATE INDEX will be minutes or hours; similarly, the progress of manual VACUUMs can be difficult to estimate accurately
This has been discussed in the DBMS literature (see below). Offhand, I think implementing #1 and #3 would be pretty doable (perhaps with FE/BE changes), but #2 would take some Thought.
Oracle already has something similar. Any operation that takes longer than 6 seconds is automatically tracked in V$SESSION_LONGOPS. The tracking is done on e.g. the step level of the execution plan ("TABLE SCAN", "12344 ov 654764 blocks read").
In PostgreSQL 9.6, a generic facility for (3) above has been introduced:
* Add a generic command progress reporting facility.
And the following uses it:
* Add simple VACUUM progress reporting.
Docs for the same:
* 27.4. Progress Reporting
== References ==
Relevant papers:
* A Lightweight Online Framework For Query Progress Indicators
* Multi-query SQL Progress Indicators
* Toward a Progress Indicator for Database Queries
* Increasing the Accuracy and Coverage of SQL Progress Indicators
Relevant pgsql-hackers threads:
* Progress bar updates
Category:Feature request
Queuing

= Queueing in core =
Internal use cases:
* Materialized Views
* Alter Table Concurrently
* Cluster Concurrently
* Parallel queries
* PG/Strom: GPU Offloading (parallel too)
Parallel queries have different transactional semantics needs (it's all running in the same transaction, producer and consumer)
External use cases:
* Very Common Design Pattern
* Datawarehouse and pruning historical data (avoid vacuum issues)
Implementation:
* it's a table?
* concurrent inserts and pruning
* no update
* payload would be a record/tuple (create type / create table)
* FIFO, Insert Only Segment Optimisation with a starting block that can be > 0
Category:Development
Category:PostgreSQL 9.3
Random integer in a range

CREATE OR REPLACE FUNCTION pg_round_random_range(int, int) RETURNS integer 
LANGUAGE plperl IMMUTABLE RETURNS NULL ON NULL INPUT
AS $pg_round_random_range$
	my($imin, $imax) = @_;	
	if ($_[0] == $_[1]){
		return $_[0];	}
	if($imin > $imax){
		$imin = $_[1];
		$imax = $_[0];	}	
	$_number_ = $imin + ( (rand) * ($imax -$imin + 1));
 return sprintf "%d",$_number_;
$pg_round_random_range$;
If you don't want to control the arguments:
CREATE OR REPLACE FUNCTION pg_round_random_range(int, int) RETURNS integer 
LANGUAGE plperl IMMUTABLE RETURNS NULL ON NULL INPUT
AS $pg_round_random_range$
 my($imin, $imax) = @_;	
 $_number_ = $imin + ( (rand) * ($imax -$imin + 1));
 return sprintf "%d",$_number_;
$pg_round_random_range$;
- 3manuek
Category:PL/Perl
Random Range

Category:SQL
by Scott Bailey 'Artacus'
CREATE OR REPLACE FUNCTION random(numeric, numeric)
RETURNS numeric AS
$$
 SELECT ($1 + ($2 - $1) * random())::numeric;
$$ LANGUAGE 'sql' VOLATILE;
== Usage ==
SELECT random(1,10)::int, random(1,10);
 random random 
 --------- ---------------- 
 5 3.20581795740873
Refactor Type System

== Requirements ==
=== Rich type modifier ===
This is a requirement from PostGIS for creaky typmod system:
One 32-bit integer does not contain enough structure to describe conveniently what a type should be. Pushing information into userland, as PostGIS is forced to do, exposes these types to the vicissitudes of user environment, search_path, etc.
Typmod is not preserved over all operations, leading to surprising behavior.
Need to store a separate entry for a type's array and other possible multiplicities
=== Type with collation ===
In addition to typmod, collation attributes might be required to support type or expr with collation.
See also Todo:Collate for details.
 CREATE TABLE .. ( ..., field COLLATE <collation>, ... )
 expr COLLATE <collation>
=== Anonymous MULTISET ===
The SQL standard provides anonymous multiset:
 CREATE TABLE .. ( .... ROW(integer, varchar(32)) MULTISET )
=== Generics ===
A real subtyping system might also be nice to support some kind of generics, type generators, or type interfaces.
Note that the current pseudo types (anyelement, anyarray, etc.) can be used only for arguments or result types for functions. In addition, we can use only one pseudo type for each function.
== References ==
=== Associative array ===
hstore in PostgreSQL is a text-to-text associative array, but other databases support typed assoc array data types.
Syntax by DB2:
 CREATE TYPE char2int AS INTEGER ARRAY[VARCHAR(20)];
Syntax by Oracle:
 TYPE char2int IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(20);
Note that Oracle supports assoc array only as variables in PL/SQL.
Category:Development
ReleaseAnnounce92

This is a very draft version of the 9.2 Release announcement. Nothing in here is final and the quotes are not approved. This is posted here for the purposes of collaborative editing.
== Draft 9.2 Release Announcement ==
SEPTEMBER 2012: The PostgreSQL Global Development Group announces the release of PostgreSQL 9.2. This release increases performance of the leading open source database in multiple ways, including improved vertical and horizontal scalability. New JSON support makes PostgreSQL more suitable for non-relational as well as relational workloads.
=== Improved Performance and Scalability ===
"With the release of PostgreSQL 9.2, the PostgreSQL Project has significantly advanced scalability and developer flexibility to provide our customers a highly performant database for their most demanding workloads," BIG USER #1 said. "We are especially happy with the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption."
Version 9.2 has multiple enhancements which gives users more options when scaling vertically, horizontally, or both, as well as improving performance for common single-server workloads. New features include:
* Index-only scans, allowing users to avoid inefficient scans of base tables
* Enhanced read-only workload scaling to 64 cores and over 300,000 queries per second
* Improvements to data write speeds, including group commit
* Reductions in CPU power consumption
* Cascading replication, supporting geographically distributed standby databases
"COMPANY2 relies on Postgres for storing millions of sites and subscriptions. Solid and reliable for years," said SOME GUY, Founder of COMPANY2. "We're consistently on the bleeding edge (9.1 now, moving to 9.2 soon for the cascading replication alone) and it's been a pleasure since 8.4."
=== Developer-Friendly JSON Support ===
PostgreSQL 9.2 includes support for JSON data types and receiving query results in JSON. Combined with the new PL/v8 Javascript and PL/Coffee database programming extensions, and the optional HStore key-value store, users can now utilize PostgreSQL like a "NoSQL" document database, while retaining PostgreSQL's reliability, flexibility and performance.
"Native JSON support in PostgresSQL provides an efficient mechanism for creating and storing documents for web APIs. Often, front-end libraries like jQuery request tabular and tree-structured data, and this feature makes it convenient and provides performance advantages in casting records to JSON," said ANOTHER GUY, Senior Architect, SOME TELCO.
Release Management Team

The Release Management Team (RMT) oversees the final stage of each development cycle, to ensure all open items are closed before a major release.
Duties:
* Meet the deadlines pgsql-release@ sets for beta1 and final release.
* When the open items list has an unowned item, promptly inform the associated committer that they own that item.
* Approve the creation of the corresponding stable branch, the welcoming of PostgreSQL submissions, and the resumption of CommitFests.
* Nag item owners to ensure forward progress.
Powers, exercisable by majority vote of the RMT, even in the face of no consensus or contrary consensus:
* Approve the commit of a patch for the purpose of resolving an open item, including reversion commits.
* Add or remove open items.
* Enact a feature freeze.
Before using one of those powers, the Release Management Team (RMT) should make more than zero effort to let normal community processes arrive at a sufficient answer. Given the aggressive schedule, the RMT may nonetheless use these powers quickly and often.
pgsql-release@ appoints the RMT and has the power to dissolve it.
== Best Practices ==
* Have scheduled, weekly meetings to:
** Review open items and assign who is responsible for seeing them through
** Discuss timing for beta and final releases
** Discuss challenging issues and brainstorm ways to help find a solution
** Report back to pgsql-release with a summary of meeting, and any actionable items -release needs to be aware of
** Take notes; this is a good reference to look back and see how things are progressing
* Agree to a chat medium of choice to be able to quickly reach each other
* When prompting on updates to open items, try to bring something to drive it forward: a patch, a test, a question
* Hard decisions happen: it's good to discuss with others in the community to get more feedback before reaching a conclusion
** Bad news is easier to deal with when a decision is given fair consideration and transparent reasoning
== Contact ==
You can contact the current RMT by emailing the rmt@lists.postgresql.org email address.
== History ==
* 17: 
* 16: 
* 15: 
* 14: 
* 13: 
* 12: 
* 11: 
* 10: 
* 9.6: 
Category:Development
Reliable Writes

Databases have many levels of read and write caches they use. There's the database's own dedicated memory (shared_buffers), the operating system cache, potentially a disk controller cache (typical on a RAID card), and caches on each individual hard drive. PostgreSQL expects some of these caches to work effectively in order to obtain good performance. But periodically, the database issues a synchronous write request: requesting that something must be committed to disk before the database can proceed. If any of those writes say they are successful to the database, but the data is not really safe, database corruption can be expected if there is a server crash for any reason. Software bug, power outage, UPS failure, accidentally unplugging the server, any of these things can lead to a corrupted database that will not start normally if your system has unsafe writes.
There is a good diagram of how all these levels of caching fit together at Database Hardware Selection Guidelines (PDF, Bruce Momjian)
The recommended way to test reliability of all the caches on a server is with Disk Plug Pull Testing. A plug pull test that fails is always accurate, but passing such a test cannot guarantee unsafe writes are impossible.
== References ==
This page lists articles about this topic that include a mix of theory and practical recommendations about how to setup a reliable system that doesn't have this sort of problem after a crash:
* Reliability: from the official documentation
* Database Hardware (PDF): Chapter on general hardware selection, introduces write-back vs. write-through caching terminology and notes about issues with SSD writes. (Greg Smith)
* Server Configuration Tuning: lists things to be aware of when setting wal_sync_method, as well as warnings about settings changes that can make your server unsafe--fsync, full_page_writes, and synchronous_commit (Greg Smith)
* Tuning PostgreSQL WAL Synchronization: background about the topic of drives that handle writes unsafely and how this impacts PostgreSQL writes. (Greg Smith)
== Known problem hardware ==
* The first and second generation Intel SSD drives such as the Intel X25-E will write unsafely, and this behavior is impossible to fix without serious impact to both the speed and longevity of the drive. See SSD, XFS, LVM, fsync, write cache, barrier and lost transactions (Vadim Tkachenko) for an introduction. Their consumer drives such as the X-25-M G2 have been reported to be even worse (Evan Jones). The 3rd generation Intel 320 and 710 series drives do not have any of these problems, having added a capacitor to provide battery-backup for flushing the drive cache when power drops.
Category:DocumentationCategory:Development
Repmgr

repmgr is a popular tool for PostgreSQL replication and failover management, introduced by 2ndQuadrant (now EDB) in 2010. 
repmgr greatly simplifies the process of setting up and managing databases with high availability (HA) and scalability requirements. It helps DBAs and System Administrators manage a cluster of PostgreSQL databases by taking advantage of the Hot Standby capability introduced in PostgreSQL 9.
repmgr streamlines the process of administration and daily management, enhances productivity, complements built-in replication capabilities, and reduces overall costs of a PostgreSQL cluster by:
* monitoring the replication process.
* providing support for HA operations such as switchovers and failovers.
repmgr is available via the EDB package repositories as well as the PGDG community repositories. You can use standard yum and apt package managers for installing repmgr with your instance of PostgreSQL.
* repmgr 5 is compatible with PostgreSQL 9.4 and above. See the repmgr compatibility matrix for full details.
== repmgr 5 Features ==
Current release: 5.3.3 (2022-10-17).
* Implemented as a PostgreSQL extension
* Replication cluster monitoring
* Standby cloning with pg_basebackup or Barman
* Timeline following:
** a standby that can be promoted to a primary without requiring a restart
** other standbys that can connect to the new master without being resynced
* Cascading standby support
** Standbys not directly connected to the master node are not affected during failover of the primary to another standby mode
* Replication slot support , simplifying WAL retention management
* Switchover support for role-switching between primary and standby
== Downloads ==
The repmgr project is hosted at https://github.com/EnterpriseDB/repmgr
Source code downloads are available from repmgr.org.
Package installation instructions for Red Hat/Debian-based distributions can be found here.
== Documentation ==
* repmgr documentation
== Administrative code snippets ==
* Repmgr cleanup trigger
Category:Replication
Repmgr cleanup trigger

repmgr's repmgrd daemon can optionally store replication lag history in the repl_monitor table; this table can grow quite quickly and requires regular pruning. This can be achieved by a cronjob which executes 'repmgr cluster cleanup'; alternatively following trigger could be used (only tested on repmgr 1.2.0):
== Cleanup trigger ==
SET search_path=repmgr_CLUSTERNAME; -- change CLUSTERNAME to your repmgr cluster name
CREATE OR REPLACE FUNCTION repl_cleanup_proc() RETURNS trigger LANGUAGE plpgsql
SET search_path=repmgr_CLUSTERNAME
AS $$
DECLARE
 cleanup_age interval = '1 day'; -- how many records to keep
 cleanup_batch interval = '1 hour'; -- how many records to delete in one shot
BEGIN
 IF EXISTS(SELECT * FROM repl_monitor WHERE last_monitor_time < (now() - cleanup_age - cleanup_batch)) THEN
 DELETE FROM repl_monitor WHERE last_monitor_time < (now() - cleanup_age);
 END IF;
 RETURN new;
END;
$$;
CREATE TRIGGER repl_cleanup_trigger AFTER INSERT ON repl_monitor FOR EACH STATEMENT EXECUTE PROCEDURE repl_cleanup_proc();
-- Test the trigger (does not insert any data, but triggers deletion if necessary)
INSERT INTO repl_monitor (primary_node) SELECT null WHERE false;
Category:Replication
Retrieve primary key columns

This returns the names and data types of all columns of the primary key for the tablename table:
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
 AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = 'tablename'::regclass
AND i.indisprimary;
==== Notes ====
* To see all indexed columns, remove the indisprimary restriction.
* The table name defaults to the first match in the current search_path unless qualified with a schema explicitly: 'public.tablename'::regclass
* The table name must be double-quoted for otherwise illegal names (upper-case letters, reserved words etc.): '"oDD table name"'::regclass
* Use quote_ident() to double-quote as needed automatically, works for column names in the result as well: quote_ident(a.attname)
Category:SQL
Return default value

Author: Emanuel
This function return the default value of a column:
CREATE FUNCTION ret_def(text,text,text) RETURNS text AS $$
SELECT 
 columns.column_default::text
FROM 
 information_schema.columns
 where table_name = $2
 and table_schema = $1
 and column_name = $3
$$ LANGUAGE sql IMUTABLE;
Just call it at this way:
SELECT ret_def('schema','table','column');