nyuuzyou/wikis huggingface dataset:
Multimaster

PostgreSQL supports unidirectional master-slave replication. Moreover, it supports hot standby mode in which it is possible to execute read-only queries at replicas. Replication can be either asynchronous or synchronous, but even in the case of synchronous replication there is a time gap between primary and standby, so the client executing read-only query at standby may not see changes it has performed in the previous transaction at primary. Also, prior to PostgreSQL 9.6 only one synchronous standby was supported, so it was practically not possible to use synchronous replication for load balancing. As a result, current streaming replication in PostgreSQL provides only fault tolerance (HA), but not scaling performance.
2ndQuadrant provides Postgres-BDR®, a multi-master technology stack. In this case, updates can be performed at any node of the cluster and then propagated to other nodes. BDR is essentially asynchronous: changes are applied at nodes some time later after committing transaction at master and various ways of resolving conflicts are proposed. BDR is fast (provides almost the same speed as hot standby), and provides the option to choose between 'eager' and 'efficient' consistency modes.
BDR is based on a new PostgreSQL feature named logical decoding. Changes are extracted from WAL and are followed by the logical output plugin. It can then apply these changes to some other database, save in log, or do whatever else it likes. BDR uses logical replication to deliver changes to other nodes. Logical replication is part of PostgreSQL since v9.5. The multimaster is based on the pglogical_output plugin provided by 2ndQuadrant. Implementation of the receiver part for this plugin has been made, which is also partly based on the BDR code. At the receiver side, we have a pool of background workers that concurrently apply changes received from remote walsender. 
From a client's point of view, it works just with a set of identical PostgreSQL instances. It can login and send queries to any of them. It does not mean whether it is read-only or update query. But certainly, as far as updates have to be applied to all nodes, multimaster can provide scaling only for read-only queries.
The diagram below shows the performance results of multimaster installed at three node clusters. We run our dtmbench benchmark, varying percent of updates. We compare results of multimaster with the performance of standalone PostgreSQL. Providing ACID properties for distributed transactions adds essential overhead: multimaster is about 2 times slower on updates than a single node. In the case of asynchronous replication, it is possible to get much better results but without global consistency. At mostly read-only workloads multimaster provides much better performance, but still, there is on linear scalability.
Vertical axis: TPS, thousands
Horizontal axis: number of client connections
Multi Replace Perl

/*
 Substitute strings by other strings within a larger string, with
 Perl s// operator, in a single pass.
 Each element in @orig is replaced by the element at the same index
 in @repl
 If multiple strings in the array match simultaneously, the longest
 match wins.
*/
CREATE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
 my ($string, $orig, $repl) = @_;
 my %subs;
 my $i=0;
 if (@$orig != @$repl) {
 elog(ERROR, "array sizes mismatch");
 }
 if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
 elog(ERROR, "array dimensions mismatch");
 }
 # Each element of $orig is a key in %subs to the element at the same
 # index in $repl
 @subs{@$orig} = @$repl;
 # Build a regexp of the form (s1|s2|...)
 # with the substrings sorted to prioritize the longest match
 my $re = join "|", map quotemeta,
 sort { length($b) <=> length($a) } keys %subs;
 $re = qr/($re)/;
 # The order will be used in the matching because (from perlre):
 # "Alternatives are tried from left to right, so the first alternative
 # found for which the entire expression matches, is the one that is
 # chosen"
 $string =~ s/$re/$subs{$1}/g;
 return $string;
$BODY$ language plperl strict immutable;
Multi Replace plpgsql

/* This function quotes characters that may be interpreted as special in a regular expression.
 It's used by the function below and declared separately for clarity. */
CREATE FUNCTION quote_meta(text) RETURNS text AS $$
 select regexp_replace($1, '([\[\]\\\^\$\.\|\?\*\+\(\)])', '\\\1', 'g');
$$ language sql strict immutable;
----
/* Substitute a set of substrings within a larger string.
 When several strings match, the longest wins.
 Similar to php's strtr(string $str, array $replace_pairs).
 Example:
 select multi_replace('foo and bar is not foobar',
 '{"bar":"foo", "foo":"bar", "foobar":"foobar"}'::jsonb);
 => 'bar and foo is not foobar'
 */
CREATE FUNCTION multi_replace(str text, substitutions jsonb)
RETURNS text
as $$
DECLARE
 rx text;
 s_left text;
 s_tail text;
 res text:=;
BEGIN
 select string_agg(quote_meta(term), '|' )
 from jsonb_object_keys(substitutions) as x(term)
 where term <> ''
 into rx;
 if (coalesce(rx, ) = ) then
 -- the loop on the RE can't work with an empty alternation
 return str;
 end if;
 rx := concat('^(.*?)(', rx, ')(.*)$'); -- match no more than 1 row 
 loop
 s_tail := str;
 select 
 concat(matches[1], substitutions->>matches[2]),
 matches[3]
 from
 regexp_matches(str, rx, 'g') as matches
 into s_left, str;
 
 exit when s_left is null;
 res := res || s_left;
 end loop;
 res := res || s_tail;
 return res;
END 
$$ LANGUAGE plpgsql strict immutable;
MVCC

MVCC is the method PostgreSQL uses to handle data consistency when multiple processes are accessing the same table. The manual documents it in the chapter on Concurrency Control.
There is a good example of how MVCC works in the PostgreSQL Through Pictures presentation, on pages 56-58, and a long discussion in the MVCC Unmasked presentation.
MVCC is not unique to PostgreSQL, it's a common database technique.
Category:Documentation
MySQL

This is a meta-page about MySQL, with hopes that it can point you in the right direction. There are a lot of different avenues to explore, are you looking for... 
== Reasons Why I Would Use Postgres Instead of MySQL? ==
* http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009
* http://sql-info.de/mysql/gotchas.html
* http://casey.shobe.info/documents/mysql_limitations/
== Case Studies / Comparisons of Postgres and MySQL? ==
* http://tweakers.net/reviews/657/6
* http://www.mysqlperformanceblog.com/2007/07/25/mysql-and-postgresql-specjappserver-benchmark-results/
* http://www.oreillynet.com/onlamp/blog/2005/01/cd_baby_rewrite_in_postgres_an.html
== Tools and Techniques for Converting Database From MySQL to Postgres? ==
* https://www.ispirer.com/products/mysql-to-postgresql-migration
* https://www.sqlways.com
* https://www.convertum.ru/database-migration/mysql-to-postgresql-migration
* https://www.spectralcore.com/fullconvert/tutorials-en/MySQL-to-PostgreSQL
* http://dbconvert.com/convert-mysql-to-postgresql-pro.php 
* http://www.in-nomine.org/~asmodai/mysql-to-pgsql.html 
* http://www.metatrontech.com/wpapers/mysql2postgresql.pdf
* https://pgxn.org/dist/mysqlcompat/
* http://208.76.222.114/confluence/display/REP/Convert+from+MySQL+to+PostgreSQL
* http://www.raditha.com/mysql/mysql2pgsql.php
* http://search.cpan.org/search?mode=module&query=SQL::Translator
* http://www.convert-in.com/sql2pgs.htm
Category:MySQL
MySQL PostgreSQL terminology

= MySQL PostgreSQL terminology =
MySQL term 	PostgreSQL term
Instance 	Cluster
Database 	Schema
? 	Database
User 	Login role
Table 	Relation or Table
Cross-database query 	Cross-schema query
Materialized View 	Materialized View
Backticks 	Single quote
bind-address 	listen_addresses
key_buffer_size 	shared_buffers
max_connections 	max_connections and superuser_reserved_connections
datadir 	data_directory
socket 	unix_socket_directories and ff
DBMS (Database Management System)	ORDBMS (Object Relational Database Management System)
Query Cache 	N/A
MyISAM 	N/A (also see Table Access Method)
InnoDB 	N/A (also see Table Access Method)
JSON 	JSON & JSON types
GPL 	PostgreSQL License
Undo Log 	WAL (Write Ahead Log)
CURDATE() 	CURRENT_DATE()
CURTIME() 	CURRENT_TIME()
INSERT IGNORE / REPLACE 	INSERT ... ON CONFLICT
SHOW TABLES 	\dt (in psql)SELECT * FROM information_schema.tables (in general)
SHOW DATABASES 	\l (in psql)SELECT * FROM pg_catalog.pg_database (in general, information_schema is limited to the current database)
DESC table 	\d table (in psql, other tools vary)
back to terminology
Category:Migration to PostgreSQL
Natural Numbers

It can be useful to have a source of natural numbers to join to e.g. to generate some kind of sequence.
I know two ways to create such a source:
== view ==
create or replace view nats as 
select row_number() over (order by 1) as n
from (select 1 union select 2) t1
join (select 1 union select 2) t2
join (select 1 union select 2) t3
join (select 1 union select 2) t4
join (select 1 union select 2) t5
...
Append more lines as required. The t-number is the power-of-2 number of rows that the view produces
e.g. t5 will produce 32 rows (2^5).
== function ==
Using a function as a source of numbers seems to be slightly faster than the view,
but more importantly, there's no limit on how many you can generate.
create or replace function nats(numeric) returns setof numeric as
$$
-- return a table of ints from 0 to specified limit.
-- usage:
-- select nats(4096) as n;
declare
 i numeric;
begin
 for i in 0..$1 loop
 return next i;
 end loop;
 return;
end;
$$
language 'plpgsql' immutable strict;
I used the nats function to generate a sequence of date intervals like so:
select
 start_time
 , end_time
 , period_start_ts
 , period_end_ts
from (
 select
 CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_start_ts AS VARCHAR) || ' s' AS INTERVAL) AS start_time
 , CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_end_ts AS VARCHAR) || ' s' AS INTERVAL) AS end_time
 , period_start_ts
 , period_end_ts
 from (
 select
 :start_ts + n * :period_len_secs as period_start_ts
 , :start_ts + (n + 1) * :period_len_secs as period_end_ts
 from (select nats(50000) as n) nats
 ) nats
 where period_end_ts <= :end_ts
) dates
Category:SQL
NewAnnounceProcessSeptember2020

As of September 10th 2020, direct emails to the pgsql-announce mailing list are no longer accepted.
There are several reasons for this:
* Reduce the chances that email to the list gets flagged as spam. Many senders on pgsql-announce today are sending from domains that disallow relaying through mailing lists, which also means other emails to the list get hurt because systems “learn” that this happens.
* More clearly differentiate between emails from the PostgreSQL project itself and other announcements.
* Ensure that no tracking pixels and similar entries can be sent to subscribers through the lists.
* Make it possible for subscribers to decide which topics they are interested in receiving email about, through the use of tags.
* Unify the moderation practices between news on www.postgresql.org and pgsql-announce that have sometimes been inconsistent, and posting to both have often led to very different delivery times depending on order of moderation.
Instead of posting directly to the list, posters will be using the system for posting news to www.postgresql.org. Anything posted as news there will automatically be sent as an email to pgsql-announce, and the same set of tags that are used for the website will be used for email delivery.
All emails will be sent from a system address that will handle eventual bounces, but the reply address of the email will be selected by the person sending the announcement from one of the confirmed addresses for the organisation. All organisations that want to post news will have to confirm at least one email address to be used (this is done from the same interface), and can have multiple ones if wanted. Specifically, the From address in the email letter header will be set to noreply-announce@postgresql.org, and the Reply-To header will be set to the selected email address, so a user hitting 'reply' in nearly all mail clients will have the To address populated with the selected email address.
Just like for the website, email content can be written in markdown (and previewed on the site), which will be delivered as a rich text email to the recipients (along with a plain markdown version as well, of course). Emails will be delivered immediately as the news item on the website is approved.
There are sure to be some issues with this system in the beginning, so if you are running into any form of problems or have questions about it, please don’t hesitate to reach out to us at webmaster@postgresql.org. If you are uncertain, please do so *before* submitting news for moderation!
NLS/ru/dict

== Словарь терминов ==
 This page will be merged and moved to http://wiki.postgresqlrussia.org/index.php/Термины_PostgreSQL
 Эта страница будет объединена и перенесена на http://wiki.postgresqlrussia.org/index.php/Термины_PostgreSQL
This page contains some translation of subject-specific terminology into Russian. It is intended to simplify translation of the documentation and .po files. Please, use 'discussion' link at top of page for discussion about Russian translation.
Эта страница предназначена для помощи при переводе сообщений сервера и документации. Она содержит перевод некоторых специфических терминов. Для обсуждения перевода пожалуйста используйте ссылку «обсуждение» вверху страницы.
=== A ===
=== B ===
==== backend ====
backend
=== C ===
==== commit ====
фиксация
==== connect (network) ====
подключение
==== chunk (size of a TOAST chunk) ====
порция (размер порции TOAST)
==== core (file, produce core files) ====
core-файл (генерировать core-файлы)
=== D ===
==== directory (filesystem) ====
директория
==== default (default value) ====
по умолчанию (значение по умолчанию)
==== declared (constraint declared) ====
объявлен (ограничение объявленное)
=== E ===
==== exception (terminated by exception) ====
исключение (завершён исключением)
=== F ===
=== G ===
=== H ===
==== host (network) ====
узел
=== I ===
==== ID (transaction, user) ====
ID (ID транзакции, ID пользователя)
=== J ===
==== join (sql) ====
соединение
=== K ===
=== L ===
==== log (transaction) ====
журнал транзакций
==== log (file, /var/log/postgresql.log) ====
протокол выполнения
==== logger (system logger process) ====
процесс протокола выполнения (системный процесс протокола выполнения)
==== locale (find suitable encoding for locale) ====
локаль (найти подходящую кодировку для локали)
=== M ===
==== multitransaction ====
мультитранзакция
=== N ===
=== O ===
==== options (--help) ====
опции
=== P ===
==== prepared (prepared transaction, prepared statement) ====
подготовлен (подготовленная транзакция, подготовленный запрос)
==== postmaster ====
postmaster
=== Q ===
=== R ===
=== S ===
==== system-wide (configuration files) ====
общесистемный (общесистемные конфигурационные файлы)
==== symbolic link, symlink (flesystem) ====
символическая ссылка
==== service (could not start service) ====
служба (не удалось запустить службу)
==== socket (network) ====
сокет
=== T ===
==== transaction ====
транзакция
==== two-phase (two-phase commit) ====
двухфазная (двухфазная фиксация транзакций)
=== U ===
==== union (sql) ====
объединение
==== unrecognized ====
нераспознанный
=== V ===
==== view (sql) ====
представление
==== vacuum ====
vacuum
=== W ===
==== WAL ====
WAL, журнал транзакций
==== wraparound (wraparound data loss) ====
циклическое переполнение (повреждение данных из-за циклического переполнения счётчика транзакций)
=== X ===
=== Y ===
=== Z ===
Category:Russian
Nordic PGDay 2014

==Schedule==
Schedule is available at the following website: http://www.postgresql.eu/events/schedule/nordicpgday2014/
== Talks: Thursday, March 20th, 2014 ==
* The worst day of your life, Christophe Pettus
* 2TB of RAM ought to be enough for anybody, Renaud Bruyeron
* A look at the Elephants trunk - PostgreSQL 9.4, Magnus Hagander
* Useful yet frequently omitted extensions, Tomas Vondra
* Intro to PostgreSQL Security, Stephen Frost (slightly updated)
* Old and new tricks with GIN, Heikki Linnakangas
* PostgreSQL is YeSQL!, Dimitri Fontaine
Nordic PGDay 2015

==Schedule==
Schedule is available at the following website: http://www.postgresql.eu/events/schedule/nordicpgday2015/
== Talks: Wednesday, March 11th, 2015 ==
* Modern SQL in PostgreSQL, Markus Winand
* Enrich your data with geocoordinates from OpenStreetMap or ArcGIS, Andreas Scherbaum
* pg_rewind - resynchronizing servers after failover, Heikki Linnakangas
* Fixing Nightmare Queries, Christophe Pettus
* Secure PostgreSQL Deployments, Magnus Hagander
* PostgreSQL for Developers, Dimitri Fontaine
* Joining 1 million tables, Hans-Jürgen Schönig
Nordic PGDay 2016

==Schedule==
Schedule is available at the following website: http://www.postgresql.eu/events/schedule/nordicpgday2016/
== Talks: Thursday, March 17th, 2016 ==
* Explaining Explain, Hans-Jurgen Schönig
* Sorting through the ages, Greg Stark
* When good databases go bad: dealing with database corruption, Ants Aasma
* What's new in PostgreSQL 9.5, Magnus Hagander
* PGHoard, hoarding your backups just right, Hannu Valtonen
* How Did We Live Without LATERAL?, Vik Fearing
* Game Day PostgreSQL, Christophe Pettus
Nordic PGDay 2017

==Schedule==
Schedule is available at the following website: http://www.postgresql.eu/events/schedule/nordicpgday2017/
== Talks: Tuesday, March 21th, 2017 ==
* Time Related Range Types Revisited, Charles Clavadetscher
* Speeding up Query Execution through Just-In-Time-Compilation, Andres Freund
* A look at the Elephants trunk - PostgreSQL 10, Magnus Hagander
* Why Uber Was (Mostly) Wrong, Christophe Pettus
* How Green Was My Valley - Advanced Spatial Analytics, Joe Conway
* SCRAM authentication in PostgreSQL, Heikki Linnakangas
* Linux IO internals for database administrators, Ilya Kosmodemiansky
Normalize whitespace

This SQL function normalizes the space in a string, removing any leading or trailing space and reducing any internal whitespace to one space character per occurrence. It can be useful in creating domains.
CREATE OR REPLACE FUNCTION normalize_space(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT regexp_replace(
 trim($1),
 E'\\s+',
 ' ',
 'g'
);
$$;
CREATE OR REPLACE FUNCTION normalize_space(text) 
RETURNS text LANGUAGE plperl AS $$
 $_ = $_[0]; 
 s/(^\s+|\s+$)//g;
 s/\s+/ /og;
 return $_;
$$;
So, if you want to update/insert to a table and normalize
the text previously this action, you might create a trigger
before insert for each row, like this:
 
CREATE TABLE pepito (wordy text, moody text, bar text);
DROP IF EXISTS trigger n_space on pepito;
CREATE TRIGGER n_space BEFORE INSERT OR UPDATE ON pepito FOR EACH ROW EXECUTE PROCEDURE n_space();
This function trigger, looks up fields of text type and touch NEW values with the replace expression.
CREATE OR REPLACE FUNCTION n_space() 
RETURNS trigger
VOLATILE
LANGUAGE plperl
AS $$
	my $rs = spi_exec_query(<<" EOT");
 SELECT
 a.attname as name,
 format_type(a.atttypid,pg_attribute.atttypmod) 
 FROM
 pg_class AS c
 JOIN
 pg_attribute AS a
 ON (
 a.attrelid = c.oid
 AND c.oid = '$_TD->{table_name}'::regclass 
 AND a.attstorage <> 'p'
 AND format_type(a.atttypid,pg_attribute.atttypmod) = 'text'
 EOT
 
	my $rs_rows = $rs->{processed}-1;
	foreach my $rn (0 .. $rs_rows){
		my $row = $rs->{rows}[$rn]->{name};
		$_TD->{new}{$row} =~ s/(^\s+|\s+$)//g;
		$_TD->{new}{$row} =~ s/\s+/ /g;		
	}	
	return "MODIFY";
$$;
TRUNCATE TABLE pepito;
INSERT INTO pepito
VALUES
 ('asd asd AS asd asf ',' asd 4t45gr g er ergt',' asd sa'),
 ('asd asd AS asdbhfg',' asd 4t45gr g er ergt',' asd sa'),
 (' asd asd AS asd asf ',' asd 4t45gr g er ergt',' asd sa');
SELECT * FROM pepito;
Notes from FOSDEM

= Notes from FOSDEM =
Campus map: Openstreetmap
== Things we should bring with us for next years conference ==
=== For sale or give away ===
* flyers to post around the venue to advertise elephant auctions etc. (A4 or even A3)
** A3 flyers (just a logo on the top, for notes)
* fewer t-shirts, pins and pens
* more elephants and mugs
* flyer or business card with both the pgeu mailing list details and general PostgreSQL mailing list details
* fewer different kind of flyers, consolidate into one design
* prepacked folders for flyers, stickers and sponsor flyers (to avoid having lots of loose flyers on the booth)
* Live CDs / USB sticks
* we've been asked for posters
=== Not for sale ===
* marker pens (to use, not for sale)
* print a price list
* open a laptop or two with pgAdmin running. With a external screen
** maybe running an automated demonstration or some kind of presentation on the laptop (done)
* coins for change
** cash box
* table cover
* adhesive tape
* power cables and dividers
* name tags for speakers/booth helpers
* scissor
* VGA cable
* coffee maker
== Addresses ==
=== Hotel ===
* Hotel Agenda Louise
** rue de Florence 6
** B-1000 Brussels
** Phone: + 32 2 539 00 31
** Fax: + 32 2 539 00 63
** hotel-agenda.com
=== Bars and Restaurants for Social events ===
Consider the this one for the Friday night social - excellent food and plenty of beer. Also within a short walk of Cafe Delirium where the main FOSDEM event is held. Dave was told to volunteer to organise this by Ads <sigh> :-)
* Les Brasseurs de la Grand Place
** Grand Place Brussels
** rue de la Colline 24
** 1000 Brussels
** Belgium
** Phone: +32 (0)2 512 28 00
** les-brasseurs.com
==== Previous venues ====
* Mont Liban Restaurant
** Rue de Livourne, 30-32
** B-1000 Brussels
** Phone: +32 2 537 71 31
** Fax: +32 2 538 23 78
** montliban.be
* Restobières
** Rue des Renards, 32
** B-1000 Brussels
** Phone: + 32 2 502 72 51
** Fax: + 32 495 50 63 00
** restobieres.be
Category: PostgreSQL Europe
Not Worth Doing

__TOC__
This list contains items that were formerly on the Todo list. 
Please do not add items here without discussion on the mailing list.
== Administration ==
=== Configuration files ===
=== Tablespaces ===
=== Statistics Collector ===
=== Point-In-Time Recovery (PITR) ===
=== Standby server mode ===
== Data Types ==
=== Domains ===
=== Dates and Times ===
=== Arrays ===
=== MONEY Data Type ===
=== Text Search ===
=== XML ===
== Functions ==
== Character Formatting ==
== Multi-Language Support ==
== Views and Rules ==
== SQL Commands ==
== Vacuum ==
== Optimizer / Executor ==
== Server-Side Languages ==
== Clients ==
=== psql ===
== Miscellaneous Performance ==
== Exotic Features ==
== Features We Do Not Want ==
NTT

* NTT Software Innovation Center
* Development Roadmap
* Projects
** Streaming Replication
** Built-in Sharding
** Table partitioning
* Tool development github
** pg_rman
*** Backup and restore management tool for PostgreSQL
** pg_bulkload
*** High speed data loading utility for PostgreSQL postgresql bulk-loader
** pg_dbms_stats
*** Let PostgreSQL to make execution plans using frozen statistics.
** pg_hint_plan
*** Give PostgreSQL ability to manually force some decisions in execution plans.
** pg_store_plans
*** Store execution plans like pg_stat_statements does for queries.
** pgaudit
*** Forked from pgaudit/pgaudit
*** PostgreSQL Audit Extension
** syncdb
*** Synchronize table data between remote databases
** dblink_plus
*** Send query from PostgreSQL to other RDBMS like contrib/dblink
** pg_reorg
*** Re-organize tables on a PostgreSQL database without keeping any locks so that you can retrieve or update rows
** db_syntax_diff
*** Forked from db-syntax-diff/db_syntax_diff
*** Check incompatible SQL statements among multiple RDBs