PG Kerberos

http://www.berabera.info/oldblog/lenglet/howtos/kerberoshowto/

http://jpmens.net/2012/06/23/postgresql-and-kerberos/

Delphi AD

http://adsi.mvps.org/adsi/Delphi/index.html

PostgreSQL SHMMAX, SHMALL calculation

8GB = memory=8589934592

python pgtune \ -i /etc/postgresql/9.2/main/postgresql.conf \ -o postgresql.conf \ --memory=8589934592 \ --type=Desktop \ --connections=10
---------------------------------------------------------------------
http://dbasolutions.wikispaces.com/SHMMAX+and+SHMALL

SHMMAX and SHMALL

 Edytuj  0  7 …

Configuring SHMMAX and SHMALL for Oracle in Linux

SHMMAX and SHMALL -
SHMMAX is the maximum size of a single shared memory segment set in “bytes”.

silicon:~ # cat /proc/sys/kernel/shmmax536870912      
536870912 = 512 kB -> (512 * 1024 * 1024)
SHMALL is the total size of Shared Memory Segments System wide set in “pages”.silicon:~ # cat /proc/sys/kernel/shmall
1415577
==============================================================================================The key thing to note here is the value of SHMMAX is set in "bytes" but the value of SHMMALL is set in "pages".==============================================================================================

Postgresql Debian APT install

http://www.postgresql.org/about/news/1432/

PGDG apt repository for Debian/Ubuntu

Posted on 2012-12-06

We are pleased to announce the availability of the PostgreSQL Global Development Group apt repository of PostgreSQL packages for Debian and Ubuntu.

The repository itself is located at http://apt.postgresql.org/pub/repos/apt/, with instructions in the PostgreSQL wiki at https://wiki.postgresql.org/wiki/Apt. The FAQ list is athttps://wiki.postgresql.org/wiki/Apt/FAQ.

The repository already includes the latest PostgreSQL versions released today.

People using the old "pgapt.debian.net" location of this repository should update their sources.list entries.

Postgresql ROWNUM

http://explainextended.com/2009/05/05/postgresql-row-numbers/

select max(e.jednostka) as jednostka,
--       e.id_klienta,
       e.modulo, 
       e.konto,
       max(case e.rownum when 1 then e.wlasc end)        as wlasc_id,
       max(case e.rownum when 1 then e.typ_formatki end) as wlasc_f,
       max(case e.rownum when 1 then e.nazwa end)        as wlasc_nazwa,      

PG misc

http://vnull.pcnet.com.pl/db/psql.tricks

[From documentation]:
======================

SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;


SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));


SELECT relname, idx_tup_fetch as seeks, n_tup_ins + n_tup_upd + n_tup_del as writes FROM pg_stat_user_tables ORDER BY relname;

SELECT pid, mode, current_query FROM pg_locks, pg_stat_activity 
WHERE granted=false AND locktype = 'transactionid' AND pid=procpid ORDER BY pid, granted;


[blog www.depesz.com]:
======================

// w bajtach wielkosc bazy
select pg_database_size(db);

// czas najdluzszego wykonywanego aktualnie zapytania
select cast(extract(epoch from now() - query_start) * 1000 as int8) from pg_stat_activity;

// zwraca w bajtach wielkosc tabeli/indexu
select pg_relation_size(nazwa_relacji);

// ile transakcji (od ostatniego restartu) postgres wykonal (niezaleznie czy byly one zatwierdzone czy wycofane)
select sum(xact_commit) + sum(xact_rollback) from pg_stat_database;

// replikacja via slony 
select st_lag_num_events, cast(extract(epoch from st_lag_time) * 1000 as int8) from _slony.sl_status

[other queries]:

SELECT
    datname,
    COUNT(*) AS open_connections,
    MAX(backend_start) AS oldest_connection,
    MIN(backend_start) AS newest_connection
    FROM pg_stat_activity GROUP BY datname
UNION SELECT
    'Summary',
    COUNT(*),
    MAX(backend_start),
    MIN(backend_start)
    FROM pg_stat_activity;

[Size queries]:
===============

Databases:
SELECT datname,pg_size_pretty(pg_database_size(oid)) FROM pg_database ORDER BY pg_database_size(oid) DESC;

Tables and indexes:
SELECT relname,pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname NOT LIKE 'pg_%' ORDER BY pg_relation_size(oid) DESC;

Tables only:
SELECT pg_tables.tablename, pg_tables.schemaname, pg_size_pretty(pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text)) AS pg_size_pretty, pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) AS rs
    FROM pg_tables
   ORDER BY pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) DESC;

Indexes only:
SELECT pg_indexes.indexname, pg_size_pretty(pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text)) AS pg_size_pretty, pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) AS rs
    FROM pg_indexes
   ORDER BY pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) DESC;

Introspection
If there's a relation with name FOO
SELECT COUNT(relname) FROM pg_class WHERE relname = 'FOO';


[Env]:
======
export PGDATA=path/to/cluster
export PGPORT=5432
export PGHOST=/tmp_or_path_to_".pgsql"_socket


[Utils]:
========
ptop
PgBouncer: https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer


[Parameters]
shared_buffers - 10-25% RAM (15%!)
fullpage_writes ?? = raid cache protected by batteries(?!)
log_min_duration_statement.
log_timestamp = true

http://www.westnet.com/~gsmith/content/postgresql/
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html
http://www.westnet.com/~gsmith/content/linux-pdflush.htm

http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html

http://blog.charcoalphile.com/category/postgresql/

IO contention discussed:
http://qaix.com/postgresql-database-development/337-722-tuning-postgres-for-large-data-import-using-copy-from-read.shtml
http://archives.postgresql.org/pgsql-performance/2005-08/msg00144.php
http://www.mail-archive.com/pgsql-performance@postgresql.org/msg18102.html


wylaczac foreign key i indeksy przy loadzie !
COPY dump
ip4r type

# 50 % RAM
effective_cache_size =

# jesli czescniej niz x sekund
# 2..5 minut?
checkpoint_warning = 300


reduced cpu_index_tuple_cost to 0.0005
(encourages indexes which may reduce disk hits)

increased work_mem to 15000 - sort/create index(sort!)

"commit=600" and writeback for ext3

"This tunable is used to define when dirty data is old enough to be eligible for writeout by the pdflush daemons. It is expressed in 100'ths of a second. Data which has been dirty in memory for longer than this interval will be written out next time a pdflush daemon wakes up.":
echo 60000 > /proc/sys/vm/dirty_expire_centisecs


alter table set statistics

"The default settings for autovacuum are very conservative, though, and are more suitable for a very small database. I generally use something aggressive like:
-D -v 400 -V 0.4 -a 100 -A 0.3
This vacuums tables after 400 rows + 40% of the table has been updated or deleted, and analyzes after 100 rows + 30% of the table has been inserted, updated or deleted. The above configuration also lets me set my max_fsm_pages to 50% of the data pages in the database with confidence that that number won't be overrun, causing database bloat. We are currently testing various settings at OSDL and will have more hard figures on the above soon."


max_fsm_relations = ilosc tabel w pgsql + zapas
max_fsm_pages = minimum na tyle ile wynosi suma liczb stron wywietlanych przez VACUUM
VERBOSE dla wszystkich tabel. 

0 3 * * * root psql -c 'VACUUM FULL;' test
0 3 * * * root vacuumdb -a -f


SELECT relfilenode, relpages * 8 AS kilobytes FROM pg_class ORDER BY relpages DESC;

SELECT c2.relname, c2.relpages * 8 AS kilobytes
FROM pg_class c, pg_class c2, pg_index i
WHERE
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname;

FSM:
VACUUM ANALYZE VERBOSE ;

CLUSTER - reorganizuje tabele na bazie indeksu (alfanumeric sort, dane blisko siebie w przypadku index range scanu)

http://www.wlug.org.nz/PostgreSQLNotes
commit_delay = 20000
commit_siblings = 3

wal_buffers = 128
checkpoint_timeout = 600
checkpoint_warning = 300
checkpoint_segments = (wal_write_rate * 600) / 16
checkpoint_segments is (300 * 3) / 16 = 56
For example, to complete recovery within 5 minutes at 3.0MiB/sec

http://www.postgresonline.com/journal/index.php?/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

"It is impossible to tune checkpoint_segments for worst-case WAL activity, such as a bulk load using COPY FROM STDIN SQL command. This might generate 30MiB/s or more of WAL activity. 30MiB/s for 5 minutes is 8.7GiB of WAL data! The best suggestion appears to be let the server run for a while, then study the timestamps on the WAL segment files in pg_xlog to calculate the WAL write rate."

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration does not apply when archive_mode is set, as all commands must write WAL in that case.

Temporary increasing the maintenance_work_mem configuration variable when loading large amounts of data can lead to improved performance. This will help to speed up CREATE INDEX commands and ALTER TABLE ADD FOREIGN KEY commands. It won't do much for COPY itself, so this advice is only useful when you are using one or both of the above techniques.

First for fairly static tables such as large lookup tables, that rarely change or when they change are bulk changes, there is little point in leaving blank space in pages. It takes up disk space and causes Postgres to scan thru useless air. In these cases - you basically want to set your FillFactor high to like 99.

CREATE UNIQUE INDEX (!)

osobny tablespace indextblspace

\set ECHO_HIDDEN t


2. Give the file system a hint that you work with larger block sizes.
   Ext3: mke2fs -b 4096 -j -R stride=2 /dev/sda1 -L LABEL
   I made a I/O test with PostgreSQL on a RAID system with stripe size
   of 64kByte and block size of 8 kByte in the RAID system.
   Stride=2 was the best value. 

64/4 = 16

largefile!

For example if you have a 4 drive raid 5 and it is using 64K chunks, your stripe size will be 256K. Given a 4K filesystem block size you would then have a stride of 64 (256/4). 
If it was 4 disk RAID0 array, than it would be 64(4x64k/4k=64). 
If it was 4 disk RAID10 array, than it would be 32 ((4/2)*64k/4k=32)
4 dyski przez 2 (RAID10) = 2 * 64kB (stripe-size) / 4kB (fs-block-size) === 32 dla 64kB chunk size


-------------8<-----------_ -------------8="-------------8" -ald="-ald" -d="-d" -l="-l" -m="-m" -s="-s" -sh="-sh" 0.69="0.69" 00000001.history:="00000001.history:" 0000000100000006000000f3.00000068.backup:="0000000100000006000000f3.00000068.backup:" 0000000100000006000000f3:="0000000100000006000000f3:" 0000000100000006000000f3="0000000100000006000000f3" 0="0" 1.2g="1.2g" 1.95="1.95" 11="11" 12:56:51="12:56:51" 12:56:53="12:56:53" 12:58:45="12:58:45" 12="12" 13:00:28="13:00:28" 13:00:33="13:00:33" 13:01:58="13:01:58" 13:01:59="13:01:59" 13:02:00="13:02:00" 13:02:01="13:02:01" 13:02:02="13:02:02" 13:02="13:02" 1470="1470" 15:15:37="15:15:37" 15:15="15:15" 15:17:07="15:17:07" 16="56" 1="1" 2.="2." 2009-01-15="2009-01-15" 2009-01-16="2009-01-16" 210="210" 2205="2205" 24990="24990" 25072="25072" 29006="29006" 3.0mib="3.0mib" 3.="3." 3157="3157" 3910="3910" 3917="3917" 3="3" 4.="4." 4096="4096" 438896="438896" 458752="458752" 4710="4710" 4734="4734" 4738="4738" 4744="4744" 5.="5." 5="5" 6.="6." 6="6" 7.="7." 7656="7656" 8932="8932" 8a="8a" 8b="8b" 95.67="95.67" a.granted="false" a.pid="c.procpid;" a.relation::regclass="a.relation::regclass" a.relation="b.relation" a.transactionid="a.transactionid" a="a" above="above" activity="activity" an="an" and="and" archives.postgresql.org="archives.postgresql.org" archives="archives" archiving="archiving" as="as" at="at" attempting="attempting" available="available" awk="awk" b.granted="true" b.pid="b.pid" b="b" backup="backup" be:="be:" be="be" beat="beat" below="below" best.="best." bin="bin" blah="blah" bloat="bloat" boot="boot" both="both" box:="box:" box="box" browser="browser" but="but" by="by" c.oid="i.indrelid" c.usename="c.usename" c2.relname="c2.relname" c2="c2" c="c" cache:="cache:" cache="cache" caching="caching" called="called" can="can" check="check" checking="checking" checkpoint_segments="checkpoint_segments" complete="complete" conenctivity:="conenctivity:" config="config" configured="configured" configuring="configuring" connection="connection" contained="contained" contrib="contrib" copy="copy" correct="correct" cos:="================================================================" cos_="cos_" count:="count:" data.master="data.master" data="data" database.="database." database="database" db.postgresql.skytools.user="db.postgresql.skytools.user" dbname="template1]" dead="dead" dead_tuple_count="dead_tuple_count" dead_tuple_len="dead_tuple_len" dead_tuple_percent="dead_tuple_percent" definicji="definicji" determine="determine" directory="directory" disabling="disabling" done="done" down....="down...." drwx------="drwx------" dtrace="dtrace" du="du" dump="dump" each="each" enables="enables" entirely="entirely" example="example" exclusive="exclusive" execute="execute" exists="exists" exiting="exiting" fast="fast" fatal:="fatal:" file:="file:" file="file" first="first" for="for" found="found" free_percent="free_percent" free_space="free_space" from="from" full="full" function="function" funkcji="funkcji" gb="gb" generates="generates" good="good" got="got" gotta="gotta" ha="ha" have="have" heap="heap" heartbeat="heartbeat" help="help" home="home" http:="http:" i.indexrelid="c2.oid" i.indisprimary="f" i="i" ibm1:="ibm1:" ibm1="ibm1" ibm2:="ibm2:" ignoring="ignoring" impact="impact" in="in" index.="index." index.php="index.php" index="index" indexow="indexow" info.="info." info="info" io="io" is:="is:" is="is" it.="it." it="it" keep="keep" keepalived="keepalived" labs.omniti.com="labs.omniti.com" length="length" linux:="linux:" lock="lock" lots="lots" ls="ls" maintenance="maintenance" makarevitch.org="makarevitch.org" mammoth="mammoth" master="master" maximum="maximum" may="may" message="message" minutes="minutes" ml="ml" mode="mode" module="module" more="more" move="move" msg00089.html="msg00089.html" msg00274.php="msg00274.php" must="must" na="na" necessary="necessary" new="new" normal="normal" not.="not." not="not" obtained.="obtained." of="of" old-slave="old-slave" old="old" on="on" one="one" or="or" order="order" osdir.com="osdir.com" other="other" over="over" particular="particular" passes="passes" past="past" patch="=======" people.planetpostgresql.org="people.planetpostgresql.org" percentage="percentage" performance="performance" pg_catalog.pg_class="pg_catalog.pg_class" pg_catalog.pg_get_indexdef="pg_catalog.pg_get_indexdef" pg_catalog.pg_index="pg_catalog.pg_index" pg_catalog.pg_proc="pg_catalog.pg_proc" pg_ctl="pg_ctl" pg_dump="pg_dump" pg_hba.conf="pg_hba.conf" pg_ident.conf="pg_ident.conf" pg_locks="pg_locks" pg_okazjerw.0="pg_okazjerw.0" pg_okazjerw="pg_okazjerw" pg_start_backup="pg_start_backup" pg_stat_activity="pg_stat_activity" pgdbarw="pgdbarw" pgsql-performance="pgsql-performance" pgstattuple="pgstattuple" physically="physically" pid_locked="pid_locked" pid_locker="pid_locker" pidfile="pidfile" postgresql-database-development="postgresql-database-development" postgresql.conf="postgresql.conf" postgresql="postgresql" postmaster.pid="postmaster.pid" postmaster:="postmaster:" postmaster="postmaster" pplications="pplications" pre="pre" print="print" probes="probes" problem-with-pqsendquery-pqgetresult-and-copy-from-statement-read.shtml="========" production="production" project-dtrace="project-dtrace" properly="=======" psql:="psql:" psql="psql" qaix.com="qaix.com" raid="raid" ram="ram" random_page_cost="0.5" rant="rant" recovery.conf="recovery.conf" recovery="recovery" regex="regex" regular="regular" released.="released." renamed="renamed" replicator="replicator" required="required" requires="requires" restore="restore" returns="returns" running="running" scenario="scenario" sec="sec" see="see" select="select" sending="sending" seq_page_cost="0.3" server="server" serverlog="serverlog" setting="setting" setup="setup" shouldn="shouldn" shrink="shrink" shut="shut" sighup="sighup" significant="significant" significantly="significantly" size="size" slave="slave" sql:="sql:" start="start" starting="starting" stop="stop" stopped="stopped" stopping="stopping" stored="stored" successful="successful" such="such" syncdaemon.="syncdaemon." system="system" systemexit="systemexit" t="t" tabeli="tabeli" table="table" table_len="table_len" tablespaces="tablespaces" tail="tail" test="test" that="that" the="the" this="this" to="to" tool="tool" trac="trac" true="true" trunk="trunk" tunning="tunning" tuple_count="tuple_count" tuple_len="tuple_len" tuple_percent="tuple_percent" tuples="tuples" two="two" ullbackup="ullbackup" up="up" usamadar="usamadar" used="used" useful="useful" user_locked="user_locked" users="users" using="using" usr="usr" utovacuum-internals.html="utovacuum-internals.html" vacuum="vacuum" verify="verify" very="very" waiting="waiting" wal-master.ini="wal-master.ini" wal-slave.ini="wal-slave.ini" wal-slave.log="wal-slave.log" wal="wal" walmgr.py="walmgr.py" walmgr="walmgr" warning="warning" way="way" we="we" well="well" where="where" whether="whether" which="which" wiki="wiki" within="within" working:="working:" write="write" wszystkich="wszystkich" wyciagniecie="wyciagniecie" z="z">

Ciekawy cast

Link

Ciekawy CAST
Type Conversion: CREATE CAST
Values and objects can be converted between types using casts. Casts can be explicit only, implicit on assignment, and implicit generally. In some cases casts can be dangerous.

The overall syntax of CREATE CAST is discussed in the PostgreSQL manual. In general almost all user created casts are likely to use a SQL function to create one to another. In general explicit casts are best because this forces clarity in code and predictability at run-time.

For example see our previous country table:

or_examples=# select * from country;
id | name | short_name
----+---------------+------------
1 | France | FR
2 | Indonesia | ID
3 | Canada | CA
4 | United States | US
(4 rows)

Now suppose we create a constructor function:

CREATE FUNCTION country(int) RETURNS country
LANGUAGE SQL AS $$
SELECT * FROM country WHERE id = $1 $$;

We can show how this works:

or_examples=# select country(1);
country
---------------
(1,France,FR)
(1 row)

or_examples=# select * from country(1);
id | name | short_name
----+--------+------------
1 | France | FR
(1 row)

Now we can:

CREATE CAST (int as country)
WITH FUNCTION country(int);

And now we can cast an int to country:

or_examples=# select (1::country).name;
name
--------
France
(1 row)

The main use here is that it allows you to pass an int anywhere into a function expecting a country and we can construct this at run-time. Care of course must be used the same way as with dereferencing foreign keys if performance is to be maintained. In theory you could also cast country to int, but in practice casting complex types to base types tends to cause problems than it solves.

SUSE pljava

link
INSTALL PLJAVA ON OPENSUSE
Install SUN/Oracle JDK and PostgreSQL via zypper or Yast.

Download pljava here http://pgfoundry.org/frs/?group_id=1000038&release_id=1024

Create a directory for example /usr/src/pljava and extract pljava there.

create /etc/ld.so.conf.d/postgres.conf with this two lines in it if your using i386 cpu architecture.

/usr/lib/jvm/java/lib
/usr/lib/jvm/jre/lib/i386/server
now you have to run:

ldconfig

edit /var/lib/pgsql/data/postgresql.conf and add this two lines

custom_variable_classes = 'pljava'
pljava.classpath = '/usr/lib/postgresql/pljava.jar'
copy /usr/src/pljava/pljava.jar and /usr/src/pljava/pljava.so to /usr/lib/postgresql/

cp /usr/src/pljava/pljava.jar /usr/lib/postgresql/
cp /usr/src/pljava/pljava.so /usr/lib/postgresql/
restart postgres service:

rcpostgres restart

apply the install.sql:

su postgres -c "/usr/bin/psql -d template1 -f /usr/src/pljava/install.sql"

Iostat

Interpretacja wyników Iostat za postem ....

Wynik polecenia iostat-dmx /dev/sda1 3
util% - im wyższy - tym gorzej , znaczy że dysk jest mocno zutylizowany (duży ruch I/O). Jesli ciągle przekracza 80% należy rozważyć rozbudowę/zmianę RAID
w/s - zapisy na sekundę
r/s  - odczyty na sekundę
svctm - czas obsługi requesta zapisu lub odczytu. Im większa wartość tym gorzej, znaczy że system ma długi czas obsługi requesta :(((

Powyższe wartości składają się na wyliczenie UTIL% = (r/s + w/s) * svctim / 1000ms * 100
---------------------------------------------------------------

• (await-svctim)/await*100: The percentage of time that IO operations spent waiting in queue in comparison to actually being serviced. If this figure goes above 50% then each IO request is spending more time waiting in queue than being processed. If this ratio skews heavily upwards (in the >75% range) you know that your disk subsystem is not being able to keep up with the IO requests and most IO requests are spending a lot of time waiting in queue. In this scenario you will again need to take any of the actions above
• %iowait: This number shows the % of time the CPU is wasting in waiting for IO. A part of this number can result from network IO, which can be avoided by using an Async IO library. The rest of it is simply an indication of how IO-bound your application is. You can reduce this number by ensuring that disk IO operations take less time, more data is available in RAM, increasing disk throughput by increasing number of disks in a RAID array, using SSD (Check my post on Solid State drives vs Hard Drives) for portions of the data or all of the data etc

Timestamp (0/2/4/8)

'Przedruk' z http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

Dropping milliseconds from timestamp

Usually we don't need timestamp value in maximum precision. For mostly people only seconds are significant. Timestamp type allows to define precision - and we could to use this feature:

postgres=# select current_timestamp;
             now              
------------------------------
 2009-05-23 20:42:21.57899+02
(1 row)

Time: 196,784 ms
postgres=# select current_timestamp::timestamp(2);
          now           
------------------------
 2009-05-23 20:42:27.74
(1 row)

Time: 51,861 ms
postgres=# select current_timestamp::timestamp(0);
         now         
---------------------
 2009-05-23 20:42:31
(1 row)

Time: 0,729 ms

Binary casting

Bo zawsze zapominam, a już nie raz potrzebowałem...
...cytat z oficjalnej dokumentacji ...

Table 9.10. Bit String Operators

Operator	Description	Example	Result
||	concatenation	B'10001' || B'011'	10001011
&	bitwise AND	B'10001' & B'01101'	00001
|	bitwise OR	B'10001' | B'01101'	11101
#	bitwise XOR	B'10001' # B'01101'	11100
~	bitwise NOT	~ B'10001'	01110
<<	bitwise shift left	B'10001' << 3	01000
>>	bitwise shift right	B'10001' >> 2	00100

The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring.
In addition, it is possible to cast integral values to and from type bit. Some examples:

44::bit(10)                    0000101100
44::bit(3)                     100
cast(-44 as bit(12))           111111010100
'1110'::bit(4)::integer        14

Note that casting to just “bit” means casting to bit(1), and so it will deliver only the least significant bit of the integer.

VM Ware ESXi resource allocation

Zapisuję żeby pamiętać czego się spodziewać po odpowiednich ustawieniach
PostgreSQL Server 8.2 na VMWareESXi 4.x
- HDD  clustra PG - Mode Independent,Persistent (Changes are immediately and permanently written to the disk)
- Resource 
-- CPU =2 core 4522MHz +Shares=HIGH +Reservation=4522MHz +Limit=Unlimited
-- Memory 4096MB +Shares=HIGH +Reservation=4096MB +Limit=Unlimited

Dla ciężkiego selecta:

iostat -m -d /dev/sdb1 1  

Odczyt rzędu 45 - 100 MB

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
sdb1            361,00        42,54         0,00         42          0

 iostat -x -d /dev/sdb1 2

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sdb1            151,00     0,00  350,00    0,00 84816,00     0,00   242,33     1,05    2,94   2,14  74,80

Zapis DDL z EMS PostgreSQL Manager

1. Opis dla EMS 2010 ver 4.6.0.3
Zapis DDL wykonuj ZAWSZE w kodowaniu != UNICODE(USC-2) oraz UNICODE(UTF-8)

psql nie potrafi zinterpretować żadnego z nich!!!!

Dodanie set client_encoding = 'UNICODE' nie załatwia sprawy
2. Jedyną możliwością jest wybranie Charset 'cp1250(Windows CentralEuropean)
3. Można również skopiować do schowka (Ctrl+C), a następnie utworzenie pliku
- na windowsie edytory tworzą standardowo w kodowaniu ANSI (czyli win1250)
wówczas dla spokoju sumienia dodajemy na początku

set client_encoding = 'WIN1250'

----
Ewentualnie zapisujemy plik w UTF-8 (wybierając w edytorze) wówczas koniecznie musimy dodać
set client_encoding = 'UNICODE';