pg_ctl.exe register -N pgsql-8.1 -P 000000 -U postgres -D "c:\Program Files\PostgresSQL\8.1\data"

-- недоверенный
CREATE PROCEDURAL LANGUAGE 'plperlu'
HANDLER plperl_call_handler
VALIDATOR plperl_validator;
ALTER LANGUAGE plperlu OWNER TO postgres;
-- доверенный
CREATE TRUSTED PROCEDURAL LANGUAGE "plperl"
  HANDLER "plperl_call_handler"
  VALIDATOR "plperl_validator";

create table some_table (
  f1 int4 primary key,
  f2 text,
  f3 text
);

insert into some_table values (1, 'qwerty', 'йцукен');
insert into some_table values (2, 'asdfgh', 'фывапр');

create function foo(bool)
  returns setof record as
'
declare
  x alias for $1;
  rec record;
begin
  if x then
    for rec in select f1, f2, f3 from some_table
    loop
      return next rec;
    end loop;
  else
    for rec in select f1, f2 from some_table
    loop
      return next rec;
    end loop;
  end if;
  return;
end;
' language plpgsql;

select * from foo(true) as (f1 int4, f2 text, f3 text);
 f1 |   f2   |   f3
----+--------+--------
  1 | qwerty | йцукен
  2 | asdfgh | фывапр

select * from foo(false) as (f1 int4, f2 text);
 f1 |   f2
----+--------
  1 | qwerty
  2 | asdfgh

Active code page: 1251

c:\home\pg>cmd.exe
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

#
# Исходное состояние:
# Дистрибутив pg 8.3.1 распакован в c:\home\pg\8.3.1
# Файл 8.3.1-cmd.bat  устанавливает переменные окружения и запускает cmd.exe
#
c:\home\pg>dir
 Volume in drive C has no label.
 Volume Serial Number is 4CA6-478A

 Directory of c:\home\pg

05.09.2008  16:41    <DIR>          .
05.09.2008  16:41    <DIR>          ..
05.09.2008  16:40    <DIR>          8.3.1
05.09.2008  16:08                69 8.3.1-cmd.bat
               1 File(s)             69 bytes
               3 Dir(s)  16 775 454 720 bytes free

c:\home\pg>type 8.3.1-cmd.bat
@chcp 1251
@set path=%cd%\8.3.1\bin;%cd%\8.3.1\lib;%path%
cmd.exe

#
# Инициализируем кластер (часть сообщений поскипана)
#
c:\home\pg>initdb -D data
[skipped]
Success. You can now start the database server using:

    "postgres" -D "data"
or
    "pg_ctl" -D "data" -l logfile start


#
# сохраним дефолтную версию конфига
#
c:\home\pg>copy data\postgresql.conf data\postgresql.conf.orig
        1 file(s) copied.

#
# Отредактируем конфиг, включив WAL
#
c:\home\pg>notepad data\postgresql.conf

c:\home\pg>fc data\postgresql.conf.orig data\postgresql.conf
Comparing files DATA\postgresql.conf.orig and DATA\POSTGRESQL.CONF
***** DATA\postgresql.conf.orig

#archive_mode = off             # allows archiving to be done
                                # (change requires restart)
#archive_command = ''           # command to use to archive a logfile segment
#archive_timeout = 0            # force a logfile segment switch after this
***** DATA\POSTGRESQL.CONF

archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'copy %p c:\\home\\pg\\xlogs\\%f'             # command to use to archive a logfile segment
#archive_timeout = 0            # force a logfile segment switch after this
*****


#
# Создаем директорию куда будут складываться WALы
#
c:\home\pg>mkdir xlogs

#
# Запускаем пг
#
c:\home\pg>pg_ctl start -D data
server starting

c:\home\pg>LOG:  система была отключена: 2008-09-05 16:42:18 MSD
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

#
# Запускаем пг
# и начинаем бекап
#
c:\home\pg>psql -U sotinov
Active code page: 1251
                       version                       |            now
-----------------------------------------------------+----------------------------
 PostgreSQL 8.3.1, compiled by Visual C++ build 1400 | 2008-09-05 16:46:55.957+04
(1 запись)

Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select pg_start_backup('label');
 pg_start_backup
-----------------
 0/494D68
(1 запись)

postgres=# \q

#
# Создаем резервную копию файлов данных
#
c:\home\pg>mkdir data-bak

c:\home\pg>xcopy /E data data-bak
[skipped]
431 File(s) copied

#
# Выключаем режим бекапа
#
c:\home\pg>psql -U sotinov
Active code page: 1251
                       version                       |            now
-----------------------------------------------------+----------------------------
 PostgreSQL 8.3.1, compiled by Visual C++ build 1400 | 2008-09-05 16:51:44.836+04
(1 запись)

Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select pg_stop_backup();
 pg_stop_backup
----------------
 0/494DD0
(1 запись)

postgres=# \q

#
# Как видно из вывода dir WAL был успешно скоприрован
#
c:\home\pg>dir xlogs\*
 Volume in drive C has no label.
 Volume Serial Number is 4CA6-478A

 Directory of c:\home\pg\xlogs

05.09.2008  16:51    <DIR>          .
05.09.2008  16:51    <DIR>          ..
05.09.2008  16:42        16 777 216 000000010000000000000000
05.09.2008  16:51               235 000000010000000000000000.00494D68.backup
               2 File(s)     16 777 451 bytes
               2 Dir(s)  16 698 986 496 bytes free

#
# Имитируем работу с базой
#
c:\home\pg>psql -U sotinov
Active code page: 1251
                       version                       |            now
-----------------------------------------------------+---------------------------
 PostgreSQL 8.3.1, compiled by Visual C++ build 1400 | 2008-09-05 16:52:41.14+04
(1 запись)

Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# create table tab1 as select * from pg_type;
SELECT                          ^
postgres=# insert into tab1 select * from tab1;
INSERT 0 269
postgres=# insert into tab1 select * from tab1;
INSERT 0 538
postgres=# insert into tab1 select * from tab1;
INSERT 0 1076
postgres=# insert into tab1 select * from tab1;
INSERT 0 2152
postgres=# insert into tab1 select * from tab1;
INSERT 0 4304
postgres=# insert into tab1 select * from tab1;
INSERT 0 8608
postgres=# insert into tab1 select * from tab1;
INSERT 0 17216
postgres=# insert into tab1 select * from tab1;
INSERT 0 34432
postgres=# insert into tab1 select * from tab1;
INSERT 0 68864
postgres=# \q

#
# Колво WALов увеличилось
#
c:\home\pg>dir xlogs\*
 Volume in drive C has no label.
 Volume Serial Number is 4CA6-478A

 Directory of c:\home\pg\xlogs

05.09.2008  16:53    <DIR>          .
05.09.2008  16:53    <DIR>          ..
05.09.2008  16:42        16 777 216 000000010000000000000000
05.09.2008  16:51               235 000000010000000000000000.00494D68.backup
05.09.2008  16:53        16 777 216 000000010000000000000001
               3 File(s)     33 554 667 bytes
               2 Dir(s)  16 624 394 240 bytes free

#
# Теперь восстановим нашу резервную копию
# Выключаем текущую базу
#
c:\home\pg>pg_ctl stop -D data
waiting for server to LOG:  получен запрос об умном осshut down...танове
.LOG:  autovacuum launcher shutting down
LOG:  отключение в процессе
..LOG:  система отключена
 готово
server stopped

#
# Удалим ненужные файлы
# Отключим в конфиге резервной копии WAL
#
c:\home\pg>del data-bak\postmaster.pid

c:\home\pg>del data-bak\postgresql.conf.orig

c:\home\pg>copy data-bak\postgresql.conf data-bak\postgresql.conf.orig
        1 file(s) copied.

c:\home\pg>notepad data-bak\postgresql.conf

c:\home\pg>fc data-bak\postgresql.conf.orig data-bak\postgresql.conf
Comparing files DATA-BAK\postgresql.conf.orig and DATA-BAK\POSTGRESQL.CONF
***** DATA-BAK\postgresql.conf.orig

archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'copy %p c:\\home\\pg\\xlogs\\%f'             # command to use to archive a logfile segment
#archive_timeout = 0            # force a logfile segment switch after this
***** DATA-BAK\POSTGRESQL.CONF

archive_mode = off              # allows archiving to be done
                                # (change requires restart)
archive_command = ''            # command to use to archive a logfile segment
#archive_timeout = 0            # force a logfile segment switch after this
*****


#
# Логи в резервной копии нам не нужны
#
c:\home\pg>del data-bak\pg_xlog\*
c:\home\pg\data-bak\pg_xlog\*, Are you sure (Y/N)? y

c:\home\pg>del data-bak\pg_xlog\archive_status\*
c:\home\pg\data-bak\pg_xlog\archive_status\*, Are you sure (Y/N)? y


#
# Однако нам понядобятся еще не заархивированные логи из "продакшн" базы
#
c:\home\pg>xcopy /S data\pg_xlog data-bak\pg_xlog
data\pg_xlog\000000010000000000000000.00494D68.backup
data\pg_xlog\000000010000000000000001
data\pg_xlog\000000010000000000000002
data\pg_xlog\000000010000000000000003
data\pg_xlog\archive_status\000000010000000000000000.00494D68.backup.done
data\pg_xlog\archive_status\000000010000000000000001.done
6 File(s) copied

#
# Создаем простейший конфиг для восстановления
#
c:\home\pg>notepad data-bak\recovery.conf

c:\home\pg>type data-bak\recovery.conf
restore_command = 'copy c:\\home\\pg\\xlogs\\%f %p'

#
# Запускаем пг с резервной копией кластера
#
c:\home\pg>pg_ctl start -D data-bak
server starting

c:\home\pg>LOG:  database system was interrupted; last known up at 2008-09-05 16:47:39 MSD
LOG:  starting archive recovery
LOG:  restore_command = 'copy c:\\home\\pg\\xlogs\\%f %p'
The system cannot find the file specified.
        1 file(s) copied.
LOG:  restored log file "000000010000000000000000.00494D68.backup" from archive
        1 file(s) copied.
LOG:  restored log file "000000010000000000000000" from archive
LOG:  automatic recovery in progress
LOG:  REDO (повторить) начинается с 0/494DB0
        1 file(s) copied.
LOG:  restored log file "000000010000000000000001" from archive
The system cannot find the file specified.
LOG:  запись с нулевой длинноц в 0/2AB0A90
LOG:  REDO (повторить) завершено на 0/2AB0A48
LOG:  last completed transaction was at log time 2008-09-05 16:54:22.814+04
The system cannot find the file specified.
The system cannot find the file specified.
LOG:  selected new timeline ID: 2
The system cannot find the file specified.
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

#
# как видно сервер нашел логи и успешно их восстановил
# при этом наш recovery.conf переименовался в recovery.done
#
c:\home\pg>dir data-bak\reco*
 Volume in drive C has no label.
 Volume Serial Number is 4CA6-478A

 Directory of c:\home\pg\data-bak

05.09.2008  16:57                52 recovery.done
               1 File(s)             52 bytes
               0 Dir(s)  16 549 830 656 bytes free

#
# Попробуем приконектиться к восстановленной базе
#
c:\home\pg>psql -U sotinov
Active code page: 1251
                       version                       |            now
-----------------------------------------------------+----------------------------
 PostgreSQL 8.3.1, compiled by Visual C++ build 1400 | 2008-09-05 16:59:24.637+04
(1 запись)

Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select count(1) from tab1;
 count
--------
 137728
(1 запись)

postgres=# \q

c:\home\pg>

#
# Вроде все
#

SELECT extract(epoch from '2011-04-07 08:47:02'::timestamp - '2011-04-07 08:46:41'::timestamp);

CREATE TABLE public.example_table (
  id BIGSERIAL, 
  description VARCHAR(100) NOT NULL, 
  CONSTRAINT example_table_pkey PRIMARY KEY(id)
) WITHOUT OIDS;

CREATE INDEX example_table_idx ON public.example_table
  USING gin (description public.gin_trgm_ops);

SELECT COUNT(0) FROM  public.example_table;
---------
2739337

EXPLAIN ANALYZE
SELECT 
  id,
  description
FROM 
  public.example_table 
WHERE   description % '%адин%';
----------------------------------------------
Bitmap Heap Scan on example_table  (cost=417.38..8533.23 rows=2739 width=53) (actual time=2.808..73.209 rows=2 loops=1)
  Recheck Cond: ((description)::text % '%адин%'::text)
  ->  Bitmap Index Scan on example_table_idx  (cost=0.00..416.69 rows=2739 width=0) (actual time=2.681..2.681 rows=1031 loops=1)
        Index Cond: ((description)::text % '%адин%'::text)
Total runtime: 73.247 ms

EXPLAIN ANALYZE
SELECT 
  id,
  description
FROM 
  public.example_table 
WHERE   description LIKE '%адин%';
----------------------------------------------
Seq Scan on example_table  (cost=0.00..62755.71 rows=274 width=53) (actual time=0.039..1389.810 rows=2 loops=1)
  Filter: ((description)::text ~~ '%адин%'::text)
Total runtime: 1389.841 ms

id     description
59       адин
61       адинадин

CREATE INDEX example_table_idx ON public.example_table
  USING gist (description public.gist_trgm_ops);

EXPLAIN ANALYZE
SELECT 
  id,
  description
FROM 
  public.example_table 
WHERE   description LIKE '%адин%';
------------------------------------------------------
Bitmap Heap Scan on example_table  (cost=360.81..8476.66 rows=2739 width=53) (actual time=1843.539..1843.541 rows=2 loops=1)
  Recheck Cond: ((description)::text % '%адин%'::text)
  ->  Bitmap Index Scan on example_table_idx  (cost=0.00..360.12 rows=2739 width=0) (actual time=1843.518..1843.518 rows=2 loops=1)
        Index Cond: ((description)::text % '%адин%'::text)
Total runtime: 1843.585 ms