nyuuzyou/wikis huggingface dataset:
Main Page/ru

== Сообщество ==
* Группа на Facebook: PostgreSQL в России
* Русскоязычная группа в Telegram
* Крупнейший форум по PostgreSQL на русском
* Русскоязычный список рассылки
* PGDay.ru - Ежегодная конференция по PostgreSQL, Санкт-Петербург
* PGConf.ru - Ежегодная конференция по PostgreSQL, Москва
== Документация ==
* Официальная документация на русском языке
* Глоссарий терминов
== Коммерческая поддержка ==
* Postgres Professional - компания, основанная российскими разработчиками PostgreSQL. 
* Postgres Pro - российский форк PostgreSQL
* Сборки PostgreSQL для работы с платформой 1С
== Учебные материалы ==
* Postgres: первое знакомство
* Курсы для администраторов баз данных и разработчиков приложений. Свободно доступны полные материалы курсов, включая видеозаписи.
* Сертификация администраторов баз данных и разработчиков приложений
* Язык SQL. Базовый курс: учеб.-практ. пособие / Е. П. Моргунов; под ред. Е. В. Рогова, П. В. Лузанова; Postgres Professional. — М., 2017. — 257 с. ISBN 978-5-6041193-0-3 (электронное издание)
* Основы технологий баз данных / Основы технологий баз данных: учеб. пособие / Б. А. Новиков, Е. А. Горшкова, Н. Г. Графеева; под ред. Е. В. Рогова. — 2-е изд. — М.: ДМК Пресс, 2020. — 582 с. ISBN 978-5-97060-841-8 (ДМК Пресс) ISBN 978-5-6041193-5-8 (Постгрес Профессиональный, электронное издание)
* Курсы для вузов
* Демонстрационная база данных "Авиаперевозки"
== Разное ==
* Официальный FAQ на русском
* FAQ для разработчиков
* PostgreSQL на Хабре
* Работа с PostgreSQL: настройка и масштабирование (сайт Алексея Васильева)
* Какие инструменты визуального редактирования существуют?
* Какие инструменты тестирования доступны?
* Проверка патчей 
* Руководство по установке PostgreSQL в один клик
* Как найти причины медленного выполнения запроса?
* Руководство по публикации вопросов о медленных запросах
* Как ускорить запрос?
* Как настроить аутентификацию без ввода пароля?
* Как изменить порядок следования колонок таблицы?
* Как исправить последовательности?
Category:Russian
Mammoth

== NOTE ==
This project seems stale and there does not seem to be any updates since 2010. See section below in links for what seemed to be the most recent links for the project.
== Overview ==
Mammoth Replicator is an integrated asynchronous master - slave replication solution with failover support.
Features:
# Complete integration with PostgreSQL
# Master to multiple slaves
# Support for replication of large objects
# Support for switchover and failover
# Can replicate to periodically connecting slaves (batched updates)
# Can coexist with other replication solutions (i.e. warm standby or slony)
Limitations:
# No support for replication of DDL commands (i.e. CREATE TABLE)
# Single database only
# No support for cascading slaves
== Current version ==
* The latest stable version is 1.8, which is based on PostgreSQL 8.3 
* Version 1.9 is in-development (pre-alpha) and will support 8.4 and further version of PostgreSQL
== Links and contacts ==
* Homepage: https://launchpad.net/mammoth-replicator or https://public.commandprompt.com/projects/replicator
* Mailing list: https://lists.commandprompt.com/mailman/listinfo/replicator-general
* Subversion: svn co https://projects.commandprompt.com/public/replicator/repo
Category:Replication
Manage privileges for backup

by 3manuek
Suppose you want to have a 'backup' user, that only have perms when the backup is running. So, here a function that can manage privileges for each schema (Grant and Revoke).
CREATE OR REPLACE FUNCTION manage_backup_privileges(p_user text, p_schema text, action boolean)
RETURNS void AS
$BODY$
DECLARE
 objeto text;
BEGIN
 FOR objeto IN
 SELECT tablename FROM pg_tables WHERE schemaname = p_schema
 UNION
 SELECT relname FROM pg_statio_all_sequences WHERE schemaname =
p_schema
 LOOP
 IF action = true THEN
 RAISE NOTICE 'Asignando todos los privilegios a % sobre %.%', p_user,p_schema, objeto;
 EXECUTE 'GRANT ALL PRIVILEGES ON ' || p_schema || '.' || objeto || ' TO ' || p_user ;
 ELSE
 RAISE NOTICE 'Quitando todos los privilegios a % sobre %.%', p_user,p_schema, objeto;
 EXECUTE 'REVOKE ALL PRIVILEGES ON ' || p_schema || '.' || objeto || ' FROM ' || p_user ;
 END IF;
 END LOOP;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
Run it, is very simple. If you want active the privileges use this form:
select manage_backup_privileges('usuario','esquema', true); --look the third parametter, is true
If you want to revoke them:
select manage_backup_privileges('usuario','esquema', false); 
Enjoy it!
Category:Backup
Mantenimiento y Administracion de Bases de Datos

:Category:Administration
== Autenticación ==
* Autenticación del Cliente
== Resguardar los datos ==
* Programar Tareas por Gilberto Castillo
* Automated Backup on Windows by Tom Hart & Justin
== Rutinas de mantenimiento y monitoreo ==
*Explain por Gilberto Castillo
*Uso de Vacuum
*Monitoreo
*Monitoreo de bloqueos TODO: Desarrollar un poco más el tema.
*Mantenimiento de los Indeces
*Uso del Espacio en Disco
== Versiones antiguas ==
* Tips_7.x
Category:Español
Manual Setup at the Command Line

Start with the documentation on creating a cluster. In 8.3 the default of using auth has been removed, it now runs as unsecured for local users by default. Note that some packagers (such as RedHat) still use auth by default in their versions.
That page suggests some options you can pass to initdb to adjust the default security level. Now you want to look at initdb See that if you use -W/--pwprompt (same thing) you can assign a password to the database superuser at cluster creation time. If you do that, you can now change the default authentication scheme to password-based (-A md5 passed to initdb will do that), you'll be secure, and you'll have one user you can login as (postgres) to create more.
To see what other authentication methods are available and to learn what your options are look at Client Authentication The one you really need to dive into is pg_hba.conf which is the magic text file to edit here. A new one of those will be put in the base directory of your new database cluster. Open that file up, look at the documentation, and you'll need to add a line to add network support like one those in the examples. Probably something like
host postgres all 192.168.12.0/24 md5
(allow access to anybody on the 192.168.12 subnet access the database with a password)
The next document you may need is how to add more users: CREATE ROLE
See also First_steps.
Again look at the examples first and then backtrack to the parameters, will make more sense that way. After that you'll want to create more databases with createdb
And then you should be able to find your away around from there using the psql command line tool.
* Relevant manual sections: Included in-line
* Source material: http://slashdot.org/comments.pl?sid=441604&cid=22301248
Category:Install
Marketing Blurbs

== Blurbs for use with conference advertising ==
=== OSCON ===
PostgreSQL is the world's most advanced and rapidly evolving open
source database, with more than 20 years of development by a community
of hundreds of database developers from dozens of countries. Our long
time support of an enterprise level feature set including
transactions, SQL standards compliance, aggregation and subqueries
makes PostgreSQL suitable for large enterprises and governments. Easy
extensibility through stored procedures and custom data objects makes
PostgreSQL exciting for developers. Try PostgreSQL for your next
database application!
Category:PostgreSQL Events
Mass type replacement

Occasionally one wishes to alter the type of all columns of some specified (user-defined) type to be a different type. For obvious reasons this shouldn't generally be attempted with builtin types, but for domains, enums, and composite types it may be very useful (especially for altering enum definitions prior to 9.1).
With the aid of the Dynamic DDL helper function exec(), we can do the alteration as follows, assuming we want to change all columns of type "foo" to "foo2" using a text cast as intermediate (this is the usual way to handle enums):
select exec('alter table ' || t || ' ' || array_to_string(atts,', '))
 from (select attrelid::regclass as t,
 array_agg('alter ' || quote_ident(attname)
 || ' type foo2 using ' || quote_ident(attname)
 || '::text::foo2') as atts
 from pg_attribute
 where atttypid = 'foo'::regtype
 group by attrelid) s;
Category:SQL
Materialized Views

Materialized Views have been implemented in PostgreSQL 9.3 after they had been the #1 requested feature in a user survey.
== Versions before Postgres 9.3 ==
In earlier versions it was possible to build materialized views using the trigger capabilities of the database. Some implementations available include:
* PostgreSQL Materialized Views by Jonathan Gardner. 
* Materialized Views that Really Work by Dan Chak.
== Adding built-in Materialized Views ==
Having MV support built into the database has been discussed actively since at least 2003. The main components required fall into three pieces:
1. Creation of materalized view
* Current state: using "CREATE TABLE AS" or similar mechanism, maintain manually
* Optimal: "CREATE MATERIALIZED VIEW" grammar, metadata to store MV data, dump/reload support
2. Updating materialized views
* Current state: periodically create new snapshots, or maintain using triggers
* Optimal: Built-in refresh via multiple strategies, with minimal locking as to improve concurrent access
3. Using materialized views in the planner
* Current state: specify the manually created MV in queries that can use it
* Optimal: Automatically accelerate queries that could be satisfied by substituting available MVs
== Prior Work ==
* FlexViews for MySQL
* Oracle
* Sybase SQL Anywhere
== Implementation ==
FlexViews and Oracle both seem to use a change-tracking / replication system as the basis for updating the views incrementally. Once such a system is in place you can inspect changes to determine whether they affect a materialized view and, if so, which rows of the view need to be updated. Then the view can be sub-selected against to insert new rows into the table backing the view.
For each view therefore you need to be able to transform the original select into an algorithm that takes a change (or list of changes) and outputs ranges of rows in the view to update. Updates are probably best done by issuing a DELETE and SELECT INTO or lower-level equivalent. This then triggers updates to any attached indices.
== Examples / Use Cases ==
 CREATE TABLE vec (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER)
 CREATE MATERIALIZED VIEW hypot AS SELECT sqrt(a*a + b*b) as c
 
 => Add primary key id:INTEGER to hypot
 => INSERT INTO hypot (c) SELECT sqrt(a*a + b*b) FROM vec
 => ON UPDATE/INSERT/DELETE vec:
 DELETE FROM hypot WHERE hypot.id = ROW.id
 INSERT INTO hypot (c) SELECT sqrt(a*a + b*b) FROM vec WHERE vec.id = row.id
Category:DevelopmentCategory:PostgreSQL 9.3
Materialized Views GSoC 2010

===Overview===
A materialized view (MV) is an optimization technique that behaves like a cache. Classic views are virtual tables while an MV is a real table based on view definition. By creating an MV you can pre-compute expensive queries to speed up certain queries.
Materialized views are a very useful feature implemented by leading database systems like Oracle, DB2, SQL Anywhere or SQL Server and it is the number one requested feature by users in PostgreSQL.
Student name: Pavel Baroš
Mentor name: Robert Haas
===Deliverables===
The general idea is to implement so called Snapshot MV for this summer. Snapshot materialized views are refreshed by a special refresh command by rebuilding the MV definition.
This project could be divided to three main steps:
# Create MV
#* change grammar
 CREATE MATERIALIZED VIEW .. 
#* ensure that backend will create table, make record in pg_class, etc.
# Using in SELECT
#* learn backend (rule system) to use MV in SELECT command (when used in SELECT, fetch row from MV and not rewrite with view definition)
# Create Refresh command
#* again change gramar
 ALTER MATERIALIZED VIEW mvname REFRESH
#* make changes to command
===Schedule===
until May 31:
* discuss about implementation specifics with comunity
* reading code (especialy understanding new version 9.0)
June 1 - June 15:
* implement new relation kind
* change grammar create mv
* create mv (physical table), save tuple in pg_class
June 16 - June 30:
* change grammar for refresh command
* considering new system rule for MV
* change rule system - backend should behave to MV like table
July 1 - July 15:
* create refresh command - consider and implement the way it could work
* make other changes - psql, pg_dump, etc.
* make regression tests, test cases
July 16 - August 9:
* consider extensibility for better MV
* code rewiew
* test for psql, pg_dump, etc.
August 10 - August 16:
* testing and commiting
===Related pgsql-hackers posts===
* Initial patch
* 2nd patch
Category:Google Summer of Code
Meeting Checklist

== Grand Unified Consideration List ==
* Venue
** Contact info for venue
** Backup contact info for venue
** Security at venue: RSVP list
* Signage to guide speakers, attendees, etc.
* Food
** Venue sponsorship
** Delivery logistics
* Audio/Video
** Does the venue have a projector?
*** Does the venue need to arrange it in advance?
* Network
** Wifi
** Wired
** Does the venue have network that the PUG can use?
** Does that need to be arranged in advance?
* Drink
** Does the venue allow alcohol?
** Make sure there's non-alcohol beverages
* Seating
** How many can the venue hold?
* Transportation
** Parking
** Public transit options
** Walk/bike/unicycle
== When Scheduling the Meeting ==
* Start at least 2 weeks before the meeting
* Get a speaker
** Get speaker to write a topic and blurb
** Edit same
* Get a venue
** Find out security/access arrangements for venue
** Record contact information for venue
* Food
** Get a food sponsor, if possible
** Choose a food vendor
** Figure out drinks
* Broadcast meeting details
** PUG mailing list
** FOSS and/or database lists nearby
** PostgreSQL Weekly News <david@fetter.org>
* A/V
** Does the venue have a projector?
*** If not, arrange to have one brought.
** Check with videographer -- available? (Chris)
*** If not, check if someone else has video & audio recording.
** Check with streaming feed person (Dirk)
*** If not available, maybe set up alternate.
== Friday before the meeting ==
* If there is a waiting list:
** E-mail all "maybes" that they have to confirm
** E-mail all "yeses" with a reminder.
* Post details of streaming meeting, if appropriate
** Give to PWN
== 24-48 hours before the meeting ==
* Attendees
** Estimate attendance numbers for venue and food budget.
** Bump all "maybes" if there's a waiting list.
*** Remember to switch RSVPs to manual.
* Check to make sure speaker is still available
** Contact backup speaker if not
* Check with venue to make sure they're ready
** If venue has tight security, send them a list of RSVPs.
* Email out reminders to the broadcast lists
* Confirm:
** at least one PUG leader
** food sponsor (if applicable)
** videographer
** streaming video person
** projector availability
== 2 hours before the meeting ==
* Check with speaker
* Check with venue
== Meeting start ==
* Announcements
* Pass hat for food if no sponsor
* Thank speaker, venue and sponsor as appropriate
* Speaker on!
Category:Community
Meson for patch authors

From PostgreSQL 16, the Meson build system will be supported. In most cases, patches submitted for inclusion in PostgreSQL will need to update the Meson configuration accordingly. This article (WIP) aims to provide an overview of necessary adjustments, particularly for anyone not yet familiar with Meson.
== Meson documentation ==
The PostgreSQL Wiki Meson article provides a general overview.
PostgreSQL devel documentation: Building and Installation with Meson
=== Meson installation ===
See here for general installation details.
Note that Meson (and the ninja build tool) are somewhat bleeding edge, and the versions provided by some current distributions may not work correctly.
== TAP tests ==
If a patch adds any TAP tests, these must be included in the relevant "meson.build" file, which should contain a section like this:
 'tap': {
 'tests': [
 't/001_basic.pl',
 ],
 },
E.g. to add a second test, change this section to
 'tap': {
 'tests': [
 't/001_basic.pl',
 't/002_advanced.pl',
 ],
 },
To verify any new entries are correctly recognised, it should be sufficient to (from the PostgreSQL source directory) execute the following:
 meson setup ~/devel/postgresql-builddir
 cd ~/devel/postgresql-builddir
 meson test --list
which will produce a list of all tests recognized by Meson.
Migrating from one database to another with Pentaho ETL

Category:Historical information
Note: this tool is no longer available, and the link does not lead to a version of this tool. This page is kept for the moment as a reference.
Migration (schema + data) from one database to another can easily be done with Pentaho ETL. It's an opensource software and I personally recommend you to take a look at.
Steps for migration are very simple:
1) Create a New Job
2) Create Source Database Connection
3) Create Destination Database Connection
4) From Wizard menu, choose Copy Tables Wizard...
5) Choose Source and Destination
6) Run the task
That's all folks !!!
Bye,
Nicola
Category:Tool
Monitorando o PostgreSQL

==Monitorando o PostgreSQL==
Em um ambiente corporativo com aplicações de missão crítica, qualquer instante de instabilidade pode causar um grande prejuízo para seu cliente, e conseqüentemente para sua empresa. Neste universo, onde todas as camadas de infra-estrutura que dão suporte ao funcionamento de suas aplicações precisam estar altamente-disponíveis, faz-se necessário tomar medidas preventivas a fim de identificar, pro-ativamente, qualquer incidente que pode acarretar em um grande problema.
A comunidade brasileira possui algumas informações a respeito:
* Monitorando o PostgreSQL, por Euler T. Oliveira onde você pode ter uma visão geral sobre o assunto;
* Monitorando o PostgreSQL com ptop, por Dickson S. Guedes, onde você pode ter um exemplo prático;
Você tem um artigo sobre monitoramento do PostgreSQL? Coloque um link aqui! Afinal é um Wiki! ":D
Category:MonitoringCategory:Português
Monitoreo de bloqueos

* Mirando el link pg_locks nos muestra que bloqueos han sido otorgados y que procesos están esperando por estos. Una buena consulta para empezar a mirar los problemas inherentes a los bloqueos:
 SELECT relname,pg_locks.* 
 FROM pg_class,pg_locks 
 WHERE relfilenode=relation and not granted;
* Revisando cuales son los procesos que estan manteniendo o esperando los bloqueos, es fácil si utiliza la referencia en pg_stat_activity
Category:Español
Month based partitioning

As of now PostgreSQL 8.4, table partitioning is not provided out of the box.
This an example of how to get partitioned tables.
The purpose is to have monthly table partitioning using PostgreSQL. 
First we need a master table, then we can create partitions inheriting from the master table. A function is used to create the partitions for a given time range.
A timestamp will be used as a partitioning variable, and in this case it is assumed that it will never be updated.
A trigger must be created to intercept the insertions on the master table and redirect the data rows to the correct partition table. A trigger function is used to perform this. 
In this specific example, it is assumed that the partitioning variable is a creation timestamp which will never change.
This only leads to the need of redirecting insert operations from the master table to the appropriate partition.
If the partitioning variable may be updated, then extra trigger and trigger functions would be needed for update operations.
Updating the partitioning variable may imply that the updated row must be moved to another partition.
== Create the master table ==
-- drop table test cascade;
create table test (
	id 	bigserial primary key,
	time	timestamp
);
== Load language module ==
CREATE LANGUAGE plpgsql;
== Create a function to create the partitions and indexes ==
-- drop function test_partition_creation();
create or replace function test_partition_creation( date, date )
returns void as $$
declare
	create_query text;
	index_query text;
begin
	for create_query, index_query in select
			'create table test_'
			|| to_char( d, 'YYYY_MM' )
			|| ' ( check( time >= date '''
			|| to_char( d, 'YYYY-MM-DD' )
			|| and time < date 
			|| to_char( d + interval '1 month', 'YYYY-MM-DD' )
			|| ) ) inherits ( test );',
			'create index test_'
			|| to_char( d, 'YYYY_MM' )
			|| '_time on test_' 
			|| to_char( d, 'YYYY_MM' )
			|| ' ( time );'
		from generate_series( $1, $2, '1 month' ) as d
	loop
		execute create_query;
		execute index_query;
	end loop;
end;
$$
language plpgsql;
== Partition creation for a given time period ==
select test_partition_creation( '2010-01-01', '2012-01-01' ) ;
== Trigger function creation ==
-- drop function test_partition_function();
create or replace function test_partition_function()
returns trigger as $$
begin
	execute 'insert into test_'
		|| to_char( NEW.time, 'YYYY_MM' )
		|| ' values ( $1, $2 )' using NEW.id, NEW.time ;
	return null;
end;
$$
language plpgsql;
== Trigger activation ==
-- drop trigger test_partition_trigger;
create trigger test_partition_trigger
	before insert
	on test
	for each row
	execute procedure test_partition_function() ;
MONTH() equivalent

These functions extract the month from timestamp (with/without TZ and
date format). If you want to extract another piece of data (like year), you only
have to replace the correct word in the extract function. 
CREATE OR REPLACE FUNCTION month(timestamp without time zone) RETURNS integer
AS $$ 
 SELECT extract(MONTH FROM $1)::integer; 
$$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION month(timestamp with time zone) RETURNS integer
AS $$
 SELECT extract(MONTH FROM $1)::integer;
$$ LANGUAGE sql STABLE;
CREATE OR REPLACE FUNCTION month(date) RETURNS integer
AS $$
 SELECT extract(MONTH FROM $1)::integer;
$$ LANGUAGE sql IMMUTABLE;
=== See also ===
EXTRACT() documentation
Category:SQL
Move PGDATA Fedora 17

= Moving PGDATA to a directory below /home in Fedora 17, 18 or 19 =
Moving PGDATA can be a challenge on systems with SELinux. In my case, I wanted PGDATA to be on my encrypted /home partition. This is how it works on Fedora 17 through 19:
# new home directory. create this, change owner to postgres:postgres, and
# change home dir for user "postgres" in /etc/passwd.
PGHOME=/home/postgres
# change PGDATA in systemd service file
PGDATA="$PGHOME""/data"
sed "s#^Environment=PGDATA=.*#Environment=PGDATA=$PGDATA#" /usr/lib/systemd/system/postgresql.service > /etc/systemd/system/postgresql.service
# change PGHOME selinux context
semanage fcontext -a -t postgresql_db_t "$PGHOME(/.*)?"
restorecon -R -v $PGHOME
# allow postgres to search /home
cd /root
mkdir selinux.local
cd selinux.local
chcon -R -t usr_t .
ln -s /usr/share/selinux/devel/Makefile .
touch postgresqlhome.fc
touch postgresqlhome.if
cat > postgresqlhome.te <<EOF
module postgresqlhome 0.1;
require {
 class dir search; 
 class lnk_file read;
 type home_root_t;
 type postgresql_t;
 type var_lib_t;
};
# Allow postgresql to search directory /home
allow postgresql_t home_root_t:dir search; 
EOF
make
semodule -i postgresqlhome.pp
Category:SELinux