Excel function for concatenate

 =CONCATENATE("Insert into test values('",B1,"','",C1,"');")

streaming replication parameters

http://www.network-theory.co.uk/docs/postgresql9/vol3/StreamingReplication.html

psql Segmentation fault

bash-3.2$ /opt/PostgreSQL/9.1/bin/psql -p 5433
psql.bin (9.1.1)
Type "help" for help.
/opt/PostgreSQL/9.1/bin/psql: line 30: 25199 Segmentation fault      LD_LIBRARY_PATH=$PG_BIN_PATH/../lib:$LD_LIBRARY_PATH "$PG_BIN_PATH/psql.bin" "$@"

So remove some  contents in the .psql_history file and then restart instance.

Wal growth calculation pg_xlog directory

There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or 2*checkpoint_segments + wal_keep_segments+ 1 files. Each segment file is normally 16 MB (though this size can be altered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence).

 If, due to a short-term peak of log output rate, there are more than 3 *checkpoint_segments + 1 segment files, the unneeded segment files will be deleted instead of recycled until the system gets back under this limit.

Based on the below mentioned formula we can calculate the pg_xlog growth.

checkpoint_completion_target=0.5

checkpoint_segments         =256

wal_keep_segments =128

F1=(2+0.5)*256+1=642.5

F2=2*256+128=640
F3=(3*256)+1=768

Based on this pg xlog wall files will grow upto  642 pg xlog files normal scenario and 768 critical scenario due to short term peak transactions.

When ever we perform huge update and delete operation on a tables that time these log files will grow upto this level.

postgresql tuning

 1.pg_stats  table having info

vacuum and analyze

1) How to Enable and Desable autovacuum on a table/toasttable.

--disable auto vacuum
ALTER TABLE my_table SET (
    autovacuum_enabled = FALSE, toast.autovacuum_enabled = FALSE
);
 
--enable auto vacuum
ALTER TABLE my_table SET (
    autovacuum_enabled = TRUE, toast.autovacuum_enabled = TRUE

);

To Change the auto-vacuum parameters at Table level
---------------------------------------------------
ALTER TABLE "PlayerEvents" SET (autovacuum_analyze_threshold = 2500, autovacuum_vacuum_threshold = 2500);


To Check the status weather at got applied thos changes are not use :
---------------------------------------------------------------------

For getting OID of Schema:
--------------------------
select nspname from pg_namespace ,pg_class where pg_namespace.oid = pg_class.relnamespace;

checking auto vacuum manual settings:
--------------------------------------------------------
SELECT c.relname, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) where c.relnamespace=62952 and c.relname ='PlayerEvents';

2)when the auto vacuum demon is ariseing for vacuum.

  autovacuum_starts=(autovacum_scale_factor*totale tuples of a table) + autovacuum_threshold_value

Eg:  total tuples of a emp table =1000

     av threshold =100

     av scal factor=0.2

then avstart=(1000*0.2)+100

            =200+100

            =300.

NOTE: when ever 300 updates/delete are performed on the table then only autovacuumed emp table.

ANALYZE operation also same.

Administrative queries in postgressql

1)Finding the table size

In bytes:

select relpages * 8192 as size_in_bytes
  from pg_class where relname = 'mytable';

(or)

postgres=# select schemaname,relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables;
 schemaname | relname | pg_size_pretty 
------------+---------+----------------
 public     | test1   | 72 kB
(1 row)

postgres=# 

In human-readable form:

select pg_size_pretty(relpages * 8192) as size
  from pg_class where relname = 'mytable';

2)Findout how many tuples and pages are used by a table
1page=8kb in postgresql.

select relname         as "table",
       reltuples       as "number of tuples",
       relpages        as "number of 8kb pages"
  from pg_class
where relname = 'my_table';
3)Find live and dead tuples of a table

select relname         as "table",
       n_live_tup      as "live tuples",
       n_dead_tup      as "dead tuples"
  from pg_stat_user_tables
where relname = 'my_table';

4)Findout when a table was last vacuumed and analysed

select relname         as "table",
       last_vacuum     as "last manual vacuum",
       last_autovacuum as "last auto vacuum",
       last_analyze    as "last manual analyze",
       last_autoanalyze as "last auto analyze"
  from pg_stat_user_tables
where relname = 'my_table';