Simple PostgreSQL Blog

A new book from Packt Publishing ! Guess what !

noreply@blogger.com (Baji Shaik) — Mon, 03 Oct 2016 12:56:00 +0000

One of my dreams..!! .. not expected..!!.. too soon..!!

I am glad to announce that my first book(PostgreSQL Development Essentials) as an author has been published.

https://www.packtpub.com/big-data-and-business-intelligence/postgresql-development-essentials

https://www.amazon.com/PostgreSQL-Development…/…/B01LFAN8B6…

The purpose of this book is to teach database developers fundamental practices and techniques for programming database applications with PostgreSQL. It is targeted at Database developers using PostgreSQL who have basic experience developing database applications with the system, but want a deeper understanding of how to implement programmatic functions with PostgreSQL.

Thanks for Packt Publishing for giving me this opportunity. Special thanks to Izzat Contractor for choosing me and Dinesh Kumar for helping in writing. Thanks to Anish Sukumaran, Nitin Dasan, and Sunith Shetty for working with me.

Aha, you can count the rows for \copy command.

noreply@blogger.com (Baji Shaik) — Sat, 18 Apr 2015 11:55:00 +0000

We all know that \copy command does not return anything when you load the data. The idea is to capture how many # of records got loaded into table through \copy command.

Here's a shell script that should work:

echo number of rows in input: $(wc -l data.in)
( echo "\copy test from stdin delimiter '|';" ; cat data.in  ) | psql -v ON_ERROR_STOP=1
echo psql exit code $?

If the exit code printed is 0, everything went well, and the value printed by the first echo can be used to to indicate how many rows were inserted. If the printed exit code is non-zero, no rows were inserted, of course. If the exit code printed is 3 then the data being copied had some error.

From the docs: If the exit code printed is 1 or 2 then something went wrong in psql (like it ran out of memory) or the server connection was broken, respectively. Following facts play a role in the above script:

.) COPY (and hence \copy) expects the input records to be terminated by a newline. So counting the number of newlines in the input is a reliable way of counting the records inserted.
.) psql will exit with code 3 iff there's an error in script and ON_ERROR_STOP is set.
Note: This seems to not apply to the `psql -c "sql command"` construct.

# Example clean input

$ pgsql -c "create table test(a text,b int);"
CREATE TABLE
$ cat data.in 
column1|2
column1|2
column1|2
column1|2
column1|2
column1|2

$ echo number of rows in input: $(wc -l data.in); ( echo "\copy test from stdin delimiter '|';" ; cat data.in  ) | psql -v ON_ERROR_STOP=1 ; echo psql exit code $? 
number of rows in input: 6 data.in
psql exit code 0

# Example malformed input
$ cat data.in 
column1|2
column1|2
column1|2c
column1|2
column1|2
column1|2

$ echo number of rows in input: $(wc -l data.in); ( echo "\copy test from stdin delimiter '|';" ; cat data.in  ) | pgsql -v ON_ERROR_STOP=1 ; echo psql exit code $? 
number of rows in input: 6 data.in
ERROR:  invalid input syntax for integer: "2c"
CONTEXT:  COPY test, line 3, column b: "2c"
psql exit code 3

I hope this helps someone.

Woohoo !! Packt Publishing has published a book on troubleshooting PostgreSQL database.

noreply@blogger.com (Baji Shaik) — Sat, 18 Apr 2015 11:29:00 +0000

(Baji is trying to impress 'X')
==========
Baji: Packt Publishing has published a book on troubleshooting PostgreSQL database.
_X_: Uh, so what(!?). It published other 4 PostgreSQL books this year !
Baji: yeah, I know !
_X_: then why do you care about thisssss.
Baji: I should care about it as I was part of technical reviewing team.. :(
_X_: Oh really !, thats fantastic.. Congratulations !
==========
Note: Finally, Baji impressed _X_ :-)

Ok, in reality, I am glad to announce that "My first book as a Technical Reviewer has been published by Packt Publishing" ;-)

https://www.packtpub.com/big-data-and-business-intelligence/troubleshooting-postgresql
http://my.safaribooksonline.com/book/databases/postgresql/9781783555314/troubleshooting-postgresql/pr02_html

Author of this book is Hans-Jürgen Schönig, he has couple of other PostgreSQL Books as well.

This book is to provide a series of valuable troubleshooting solutions to database administrators responsible for maintaining a PostgreSQL database. It is aimed at PostgreSQL administrators who have developed an application with PostgreSQL, and need solutions to common administration problems they encounter when managing a database instance. So give a try ;-)

I would like to thank my loving parents for everything they did for me. Personal time always belongs to family, and I did this in my personal time.

I want to thank the Packt Publishing for giving me this opportunity and thanks to Sanchita Mandal and Paushali Desai for choosing me and working with me for this project.

Last but not least, would like to thanks Dinesh Kumar who taught me PostgreSQL and inspiring me for this. :)

Someone asked me.. "how to change the location of core file generated by postgres".

noreply@blogger.com (Baji Shaik) — Sat, 07 Feb 2015 16:55:00 +0000

I remember someone asking me about changing the location of core file generated by postgres. We all know it creates under PGDATA by default, however some people want to avoid that as core file size will be huge some times and eats all space of data directory which will turn into shutdown of cluster. So I thought it will be good if we have an article which shows changing location.

On Linux servers, core file generation can be enabled by running "ulimit -c unlimited" before starting the server, or by using the -c option to pg_ctl start. On Windows, if you're running PostgreSQL 9.1, you can create a "crashdumps" subdirectory inside the data directory. On earlier versions, it's harder.

Before enabling/disabling, if you want to verify if your cluster started to generate core files or not, then check this. Ok, I have enabled core file gneration for my cluster, let change the location. Here are detailed steps:

-- Start the cluster using "-c" option(cluster user must be set to generate core files).
-- Check the core file pattern as root user using below command:

[root@localhost ~]# sysctl kernel.core_pattern
kernel.core_pattern = |/usr/libexec/abrt-hook-ccpp %s %c %p %u %g %t e

-- Change the kernel.core_pattern to the location in which you want to generate core files(Contact your Admin to do that). Please note that location given in kernel.core_pattern must be writable by the cluster user, or else the kernel will decline to write a core file there.

[root@localhost ~]# echo "kernel.core_pattern=/tmp/core-%e-%s-%u-%g-%p-%t" >> /etc/sysctl.conf
[root@localhost ~]# tail -5 /etc/sysctl.conf
# max OS transmit buffer size in bytes
net.core.wmem_max = 1048576
fs.file-max = 6815744
########
kernel.core_pattern=/tmp/core-%e-%s-%u-%g-%p-%t
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# sysctl -p |tail -5
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744
kernel.core_pattern = /tmp/core-%e-%s-%u-%g-%p-%t
[root@localhost ~]#
%% - A single % character
%p - PID of dumped process
%u - real UID of dumped process
%g - real GID of dumped process
%s - number of signal causing dump
%t - time of dump (seconds since 0:00h, 1 Jan 1970)
%h - hostname (same as ’nodename’ returned by uname(2))
%e - executable filename

-- Verify if cluster is started to generate core file.

bash-4.1$ ps -ef|grep data|grep "9.3"
504       3405     1  0 20:44 ?        00:00:00 /opt/PostgresPlus/9.3AS/bin/edb-postgres -D /opt/PostgresPlus/9.3AS/data
postgres  6155     1  0 21:37 pts/0    00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
bash-4.1$ grep -i core /proc/6155/limits
Max core file size        unlimited            unlimited            bytes  
bash-4.1$

-- Check if cluster crash creates core files in the given location. Let me kill a process to do generate core.

bash-4.1$ ps -ef|grep 6155
postgres  6155     1  0 21:37 pts/0    00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres  6156  6155  0 21:37 ?        00:00:00 postgres: logger process                                  
postgres  6158  6155  0 21:37 ?        00:00:00 postgres: checkpointer process                            
postgres  6159  6155  0 21:37 ?        00:00:00 postgres: writer process                                  
postgres  6160  6155  0 21:37 ?        00:00:00 postgres: wal writer process                              
postgres  6161  6155  0 21:37 ?        00:00:00 postgres: autovacuum launcher process                    
postgres  6162  6155  0 21:37 ?        00:00:00 postgres: stats collector process                        
postgres  6527  6001  0 21:38 pts/0    00:00:00 grep 6155

bash-4.1$ kill -ABRT 6159  -- Killing writer process to get core dump.
bash-4.1$
bash-4.1$
bash-4.1$ ls -ltrh /tmp/core*postgre*
-rw-------. 1 postgres postgres 143M Feb  7 21:41 /tmp/core-postgres-6-501-501-6159-1423325468
bash-4.1$
bash-4.1$ date
Sat Feb  7 21:41:25 IST 2015

-- Check the log entries

-bash-4.1$ grep "6159"  postgresql-2015-02-07_213749.log
2015-02-07 21:41:09 IST LOG:  background writer process (PID 6159) was terminated by signal 6: Aborted

Wow, it generated in new location. Any comments/suggestions are most welcome.

Ah, Does it mean a bad hardware or a kernel...Uh, Just want to avoid it.

noreply@blogger.com (Baji Shaik) — Sun, 16 Nov 2014 16:53:00 +0000

I have seen many customers coming up with below errors and asking for root cause. They wonder with the reasons behind it and say "Ah, its because of a bad hardware or a kernel.. I hate it, just want to know how to avoid these"

Lets start with this:

ERROR: could not read block 4285 in file "base/xxxxx/xxxx": read only 0 of 8192 bytes

... have rarely been known to be caused by bugs in specific Linux kernel versions. Such errors are more often caused by bad hardware, anti-virus software, improper backup/restore procedures, etc.

One very common cause for such corruption lately seems to be incorrect backup and restore. (For example, failure to exclude or delete all files from the pg_xlog directory can cause problems like this, or using filesystem "snapshots" which aren't really atomic.) The history of the database, including any recoveries from backup or promotion of replicas to primary, could indicate whether this is a possible cause. Faulty hardware is another fairly common cause, including SANs. If fsync or full_page_writes were ever turned off for the cluster, that could also explain it.

It is good to establish the cause where possible, so that future corruption can be avoided, but to recover the cluster should normally be dumped with pg_dumpall and/or pg_dump, and restored to a freshly created (via initdb) cluster on a machine which is not suspected of causing corruption. It may be possible to fix up or drop and recreate individual damaged objects, but when doing that it can be hard to be sure that the last of the corruption (or the cause of the initial corruption) has been eliminated.

Here is a nice article to find why-is-my-database-corrupted from Robert Haas.

Errors like this:

ERROR: unexpected data beyond EOF in block xxxx of relation pg_tblspc/xxxx
HINT: This has been seen to occur with buggy kernels; consider updating your system.

... are most often caused by Linux kernel bugs. If you are seeing both types of errors suggests it is likely that a hardware problem (like bad RAM) may be the cause of both problems, although other causes cannot be ruled out. It is recommended that you schedule a maintenance window and run thorough hardware checks. The latter error mes age has never been known to be caused by a bug in PostgreSQL itself or by improper backup/restore; it can only be caused by an OS bug or something which is interfering with the OS-level actions -- like hardware problems or AV software. The kernel bug can affect anything adding pages to a table or its indexes. It is a race condition in the kernel, so it will probably be infrequent and it will be hard to reproduce or to predict when it will be encountered. It can be caused by an fallocate() bug which is indeed fixed in below release:

6.5, also termed Update 5, 21 November 2013 (kernel 2.6.32-431): https://rhn.redhat.com/errata/RHSA-2013-1645.html

Given all the distributions of Linux and the different timings with which each has incorporated different bug fixes, it is not feasible to give a list of Linux versions that are known to work well. A more practical approach would be to find out the exact version of Linux being used, and then do a web search for known bugs in that version. Most often the main source of that is the list of bugs fixed in later versions. The bug which could cause this error was fixed several years ago in all major distributions, so any bug-fix version of Linux released in the last two years is unlikely to contain the relevant bug, so simply applying available bug fixes for the distribution should rule out OS problems unless this is a new OS bug which is not yet run into. If you continue to see this error while running with the latest OS bug fixes, the most likely cause is bad hardware.

I have googled around on "suggestions to avoid corruptions" and found this article from Craig Ringer. Here are some suggestions made by community/core team members:

** Maintain rolling backups with proper ageing. For example, keep one a day for the last 7 days, then one a week for the last 4 weeks, then one a month for the rest of the year, then one a year.
** Use warm standby with log shipping and/or replication to maintain a live copy of the DB.

** If you want point-in-time recovery, keep a few days or weeks worth of WAL archives and a basebackup around. That'll help you recover from those "oops I meant DROP TABLE unimportant; not DROP TABLE vital_financial_records;" issues.

** Keep up to date with the latest PostgreSQL patch releases. Don't be one of those people still running 9.0.0 when 9.0.10 is out.

** Plug-pull test your system when you're testing it before going live. Put it under load with something like pgbench, then literally pull the plug out. If your database doesn't come back up fine you have hardware, OS or configuration problems.

** Don't `kill -9` the postmaster. It should be fine, but it's still not smart.

** ABSOLUTELY NEVER DELETE postmaster.pid

** Use good quality hardware with proper cooling and a good quality power supply. If possible, ECC RAM is a nice extra.

** Never, ever, ever use cheap SSDs. Use good quality hard drives or (after proper testing) high end SSDs. Read the SSD reviews periodically posted on this mailing list if considering using SSDs. Make sure the SSD has a supercapacitor or other reliable option for flushing its write cache on power loss. Always do repeated plug-pull testing when using SSDs.

** Use a solid, reliable file system. zfs-on-linux, btrfs, etc are not the right choices for a database you care about. Never, ever, ever use FAT32.

** If on Windows, do not run an anti-virus program on your database server. Nobody should be using it for other things or running programs on it anyway.

** Avoid RAID 5, mostly because the performance is terrible, but also because I've seen corruption issues with rebuilds from parity on failing disks.

** Use a good quality hardware RAID controller with a battery backup cache unit if you're using spinning disks in RAID. This is as much for performance as reliability; a BBU will make an immense difference to database performance.

** If you're going to have a UPS (you shouldn't need one as your system should be crash-safe), don't waste your money on a cheap one. Get a good online double-conversion unit that does proper power filtering. Cheap UPSs are just a battery with a fast switch, they provide no power filtering and what little surge protection they offer is done with a component that wears out after absorbing a few surges, becoming totally ineffective. Since your system should be crash-safe a cheap UPS will do nothing for corruption protection, it'll only help with uptime.

Finally, its.... Slony !! (switchover and failover)

noreply@blogger.com (Baji Shaik) — Sat, 15 Nov 2014 23:29:00 +0000

Slony is a great PostgreSQL replication tool and it’s used by many organizations. The tool works with different versions of Postgres, and it’s easy to upgrade with minimum downtime – sometimes with zero downtime!

Recently, I was working with a customer on a switchover and failover of Slony. I have seen a lot of bloggers post about installing and configuring Slony so I thought I would share my experience.

The customer wanted to upgrade their database from PostgreSQL 8.4 to v9.2. They had about 400 tables and wanted one set for each table, hence about 400 sets.

Below are the steps I took. Just for convenience, I'm using 10 tables/sets to explain.

Create tables using below script in source(8.4) and target(9.3) databases:

source=# select 'create table slony_tab_'||generate_series(1,10)||'(t int primary key);';

Inserted values using below script in source database:

source=# select 'insert into slony_tab_'||a||' values (generate_series(1,100));' from generate_series(1,10) a;

Configure Slony using below scripts:

1. Init cluser script

#################################################################################################

cluster name = shadow;
node 1 admin conninfo='host=127.0.0.1 dbname=source user=postgres port=5434';
node 2 admin conninfo='host=127.0.0.1 dbname=target user=postgres port=5432';
init cluster (id = 1 , comment = 'Primary Node For the Slave postgres');
#Setting Store Nodes ...
store node (id = 2, event node = 1 , comment = 'Slave Node For The Primary postgres');
#Setting Store Paths ...
echo  'Stored all nodes in the slony catalogs';
store path(server = 1 , client = 2, conninfo = 'host=127.0.0.1 dbname=source user=postgres port=5434');
store path(server = 2, client = 1 , conninfo = 'host=127.0.0.1 dbname=target user=postgres port=5432');
echo  'Stored all Store Paths for Failover and Switchover into slony catalogs ..';

#################################################################################################

2. Create Set Script

#################################################################################################

cluster name = shadow;
node 1 admin conninfo='host=127.0.0.1 dbname=source user=postgres port=5434';
node 2 admin conninfo='host=127.0.0.1 dbname=target user=postgres port=5432';
try { create set (id = 1 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set1 for upgrade!'; exit 1;}
try { create set (id = 2 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set2 for upgrade!'; exit 1;}
try { create set (id = 3 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set3 for upgrade!'; exit 1;}
try { create set (id = 4 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set4 for upgrade!'; exit 1;}
try { create set (id = 5 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set5 for upgrade!'; exit 1;}
try { create set (id = 6 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set6 for upgrade!'; exit 1;}
try { create set (id = 7 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set7 for upgrade!'; exit 1;}
try { create set (id = 8 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set8 for upgrade!'; exit 1;}
try { create set (id = 9 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set9 for upgrade!'; exit 1;}
try { create set (id = 10 ,origin = 1 , comment = 'Set for public'); } on error { echo  'Could not create Subscription set10 for upgrade!'; exit 1;}
set add table (set id = 1 ,origin = 1 , id = 1, full qualified name = 'public.slony_tab_1', comment = 'Table slony_tab_1 with primary key');
set add table (set id = 2 ,origin = 1 , id = 2, full qualified name = 'public.slony_tab_2', comment = 'Table slony_tab_2 with primary key');
set add table (set id = 3 ,origin = 1 , id = 3, full qualified name = 'public.slony_tab_3', comment = 'Table slony_tab_3 with primary key');
set add table (set id = 4 ,origin = 1 , id = 4, full qualified name = 'public.slony_tab_4', comment = 'Table slony_tab_4 with primary key');
set add table (set id = 5 ,origin = 1 , id = 5, full qualified name = 'public.slony_tab_5', comment = 'Table slony_tab_5 with primary key');
set add table (set id = 6 ,origin = 1 , id = 6, full qualified name = 'public.slony_tab_6', comment = 'Table slony_tab_6 with primary key');
set add table (set id = 7 ,origin = 1 , id = 7, full qualified name = 'public.slony_tab_7', comment = 'Table slony_tab_7 with primary key');
set add table (set id = 8 ,origin = 1 , id = 8, full qualified name = 'public.slony_tab_8', comment = 'Table slony_tab_8 with primary key');
set add table (set id = 9 ,origin = 1 , id = 9, full qualified name = 'public.slony_tab_9', comment = 'Table slony_tab_9 with primary key');
set add table (set id = 10 ,origin = 1 , id = 10, full qualified name = 'public.slony_tab_10', comment = 'Table slony_tab_10 with primary key');

#################################################################################################

3. Starting Slon Processes

#################################################################################################
/opt/PostgreSQL/9.2/bin/slon -s 1000 -d2 shadow 'host=127.0.0.1 dbname=source user=postgres port=5434' > /tmp/node1.log 2>&1 &
/opt/PostgreSQL/8.4/bin/slon -s 1000 -d2 shadow 'host=127.0.0.1 dbname=target user=postgres port=5432' > /tmp/node2.log 2>&1 &

#################################################################################################

4. Subscribing the sets.

#################################################################################################
cluster name = shadow;
node 1 admin conninfo='host=127.0.0.1 dbname=source user=postgres port=5434';
node 2 admin conninfo='host=127.0.0.1 dbname=target user=postgres port=5432';
try { subscribe set (id = 1, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 1';
try { subscribe set (id = 2, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 2';
try { subscribe set (id = 3, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 3';
try { subscribe set (id = 4, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 4';
try { subscribe set (id = 5, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 5';
try { subscribe set (id = 6, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 6';
try { subscribe set (id = 7, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 7';
try { subscribe set (id = 8, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 8';
try { subscribe set (id = 9, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 9';
try { subscribe set (id = 10, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1; } echo  'Subscribed nodes to set 10';
#################################################################################################

Till here it is normal, I mean, all can do very easily.... and of-course many sources you can find. However switchover.. failover !!

If you try to insert values in tables of "target" database, it throws below error:

target=# insert into slony_tab_1 values (11);
ERROR:  Slony-I: Table slony_tab_1 is replicated and cannot be modified on a subscriber node - role=0

Ah, its time to SwitchOver and FailOver...

Let us start with Switch-Over script --

cluster name = shadow;
node 1 admin conninfo='host=127.0.0.1 dbname=source user=postgres port=5434';
node 2 admin conninfo='host=127.0.0.1 dbname=target user=postgres port=5432';
lock set (id = 1, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 1, old origin = 1, new origin = 2); echo 'Set 1 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 2, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 2, old origin = 1, new origin = 2); echo 'Set 2 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 3, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 3, old origin = 1, new origin = 2); echo 'Set 3 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 4, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 4, old origin = 1, new origin = 2); echo 'Set 4 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 5, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 5, old origin = 1, new origin = 2); echo 'Set 5 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 6, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 6, old origin = 1, new origin = 2); echo 'Set 6 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 7, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 7, old origin = 1, new origin = 2); echo 'Set 7 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 8, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 8, old origin = 1, new origin = 2); echo 'Set 8 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 9, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 9, old origin = 1, new origin = 2); echo 'Set 9 Has Been Moved From Origin Node 1 To 2 ';
lock set (id = 10, origin = 1); sync (id = 1); wait for event (origin = 1, confirmed = 2, wait on = 2); move set (id = 10, old origin = 1, new origin = 2); echo 'Set 10 Has Been Moved From Origin Node 1 To 2 ';

Executing this script gives you this:

-bash-4.1$ /opt/PostgreSQL/9.2/bin/slonik /tmp/Switchover_script.slonik
/tmp/Switchover_script.slonik:4: Set 1 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:5: Set 2 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:6: Set 3 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:7: Set 4 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:8: waiting for event (1,5000000211) to be confirmed on node 2
/tmp/Switchover_script.slonik:8: Set 5 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:9: Set 6 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:10: Set 7 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:11: Set 8 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:12: waiting for event (1,5000000224) to be confirmed on node 2
/tmp/Switchover_script.slonik:12: Set 9 Has Been Moved From Origin Node 1 To 2
/tmp/Switchover_script.slonik:13: Set 10 Has Been Moved From Origin Node 1 To 2

-- Now try to insert values in tables of "target" database.. that should allow you.. :-)

target=# insert into slony_tab_1 values(21);
INSERT 0 1

Wow, its done.. isn't it easy.. !!
And now go ahead with failover.. its yours :P
Below is the script for FailOver...

cluster name = shadow;
node 1 admin conninfo='host=127.0.0.1 dbname=source user=postgres port=5434';
node 2 admin conninfo='host=127.0.0.1 dbname=target user=postgres port=5432';
try {failover (id = 1, backup node = 2 );} on error { echo 'Failure Of The Failover For The Set 1  to 2 ';exit 1; }echo 'Failover Has been performed from 1 to 2';

Check if replication is happening..it should not !
so we are done with Switchover and Failover..
Hope this helps to someone. Any comments or suggestion would be appreciated !

Updating pg_cast helps, however sometimes !!

noreply@blogger.com (Baji Shaik) — Sun, 06 Jul 2014 05:29:00 +0000

I've seen one of our customer is migrating a table from SQL Server to PostgreSQL using EnterpriseDB's Migration ToolKit. This table has a boolean datatype column. In migration process, MTK converts datatype "boolean" to "bit" in PostgreSQL and the process was taking 6 hrs to complete. Customer wanted to change the datatype from "Bit" to "Integer" and alter command for changing type was taking another 6 hrs in PostgreSQL. If he migrates only structure to PostgreSQL first, and then change the type to "Integer" from "Bit", then it does not allow you to load the data with below error. If it allows, it takes only 6 hrs as no need of alter the type after data load.

ERROR: column "hidehelm" is of type integer but expression is of type boolean
 Hint: You will need to rewrite or cast the expression.

So I found a work around to type cast from "boolean" to "Integer" implicitly by updating "pg_cast" table as below. By this, he can directly load the boolean data into integer column which saves the time of altering the type from BIT to Integer after migrating.

postgres=# insert into tarik values (1::boolean);
ERROR:  column "t" is of type integer but expression is of type boolean
LINE 1: insert into tarik values (1::boolean);
HINT:  You will need to rewrite or cast the expression.

postgres=# select * from pg_cast where castsource='boolean'::regtype and casttarget='int4'::regtype;
castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
         16 |         23 |     2558 | e           | f
(1 row)

postgres=# update pg_cast set castcontext ='i' where castsource='boolean'::regtype and casttarget='int4'::regtype;
UPDATE 1

postgres=# select * from pg_cast where castsource='boolean'::regtype and casttarget='int4'::regtype;
 castsource | casttarget | castfunc | castcontext | castmethod 
------------+------------+----------+-------------+------------
         16 |         23 |     2558 | i           | f
(1 row)

postgres=# insert into tarik values (1::boolean);
INSERT 0 1

Irrespective of any side effects(which I'm not aware of) of this workaround, this worked and migration had take only 6 hrs. Of-course, customer rolled back this setting after migrating the table. Updating catalogs is very dangerous, so might be I should have concentrated on how to reduce the time of ALTER command after migration?, anyways, it worked, so I was happy !! ;-)

Thanks for any suggestions/comments.

Oops I corrupted my table, of-course just to recover salvaged data.

noreply@blogger.com (Baji Shaik) — Fri, 04 Jul 2014 20:47:00 +0000

The way I started the title might be confusing, "I corrupted my table", so everyone starts with "Crap !! why did you do that !!, ...", so just to justify it.......
I see many customers coming for recovering the corrupted tables without any backup. In such cases, hard to recover the tables completely and it needs lot of work, however we can recover salvaged data if they dont care about corrupted rows.

Let me corrupt the table first.. :-)

I created a million-row table called "to_be_damaged"

postgres=# select count(*) from to_be_damaged ;
  count 
---------
 1000000
(1 row)
postgres=# select relfilenode,relname from pg_class where relname='to_be_damaged';
relfilenode |    relname   
-------------+---------------
       461257 | to_be_damaged

(1 row)

I've used "hexedit" to damage it. Open relfilenode file from OS level using hexedit and try picking a line which is the start of an 8K boundary and typing hex DE AD BE EF across it.

postgres=# select count(*) from to_be_damaged ;
ERROR:  invalid page in block 0 of relation base/12896/461257

Now create an identical table "salvaged" to recover salvaged data from "to_be_damaged" table.

postgres=# create table salvaged(t int);
CREATE TABLE

Prepared below function which copies the rows which are still salvageable:

create or replace function salvage_damaged()
  returns void
  language plpgsql
as $$
declare
  pageno int;
  tupno int;
  pos tid;
begin
  <<pageloop>>
  for pageno in 0..35930 loop  -- pg_class.relpages for the damaged table
    for tupno in 1..1000 loop
      pos = ('(' || pageno || ',' || tupno || ')')::tid;
      begin
        insert into salvaged select * from to_be_damaged where ctid = pos;
      exception
        when sqlstate 'XX001' then
          raise warning 'skipping page %', pageno;
          continue pageloop;
        when others then
          raise warning 'skipping row %', pos;
      end;
    end loop;
  end loop;
end;
$$;

Now run the function to copy salvagable rows:

postgres# select salvage_damaged();
WARNING: skipping page 0
salvage_damaged
-----------------

(1 row)
postgres=# select count(*) from salvaged ;
count
----------
12999815
(1 row)

postgres=# select 13000000-12999815;
?column?
----------
185
(1 row)

I hope it helps someone. Thanks for reading.

Difference between Warm, hot standby and Streaming Replication:

noreply@blogger.com (Baji Shaik) — Sun, 20 Apr 2014 16:51:00 +0000

We all know about replication and its types in postgresql. There are basic 3 types of replication in postgresql i.e Warm, hot standby and Streaming Replication. I used to get confused between these three and was unable to find the difference when I was in my initial stages of postgres learning. However I understand the difference later by reading the docs. So I would like to post key differences between these 3 types of replication in short(it also helps if anyone wants to find which one is configured for his/her environment).

Warm Standby:

==========

Its introduced in PostgreSQL 8.3(IIRC).

1. It is based on WAL log shipping which typically means WAL archives generated on Master will be transferred and applied at Standby side. So Warm Standby always waits for the WAL archive in which Master is currently writing and keeps throw messages like "cp: cannot stat <archive>: No such file or directory". So it is always one archive behind than Master and data loss will be max of 16MB(assuming a healthy warm standby by :-) )

2. In postgresql.conf file, you would need to change just three parameters in master; wal_level to archive, archive_mode and archive_command, however nothing in postgresql.conf file at standby side.

On Master:

wal_level = archive
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

3. In recovery.conf file, three parameters; standby_mode, restore_command and trigger_file.

4. You cannot connect to Standby, so database is not even open for read operations (or read operations are not permitted on db).

Detailed explanation and related docs are here
Hot Standby:
========
Its introduce in PostgreSQL 9.0.

1. It is also based on WAL log shipping(same as warm standby). And of-course, WALs will be transferred and applied at Standby, so one WAL behind and always waits for the WAL archive in which Master is currently writing.
2. In postgresql.conf file, you would need to change wal_level to hot_standby, archive_mode and archive_command. Since you'll likely want to use pg_basebackup you should also set max_wal_senders to at least 2 or 3. And hot_stanby = on in standby conf file.
On Master:

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

On Slave:

hot_standby = on

3. In recovery.conf file, three parameters; standby_mode, restore_command and trigger_file. 4. You can connect to Standby for read queries(you should set hot_stanby to ON in standby postgresql.conf file).

Detailed explanation and related docs are here.
Steaming Replication:
==============
Its introduced in PostgreSQL 9.0.
1. XLOG records generated in the primary are periodically shipped to the standby via the network. XLOG records shipped are replayed as soon as possible without waiting until XLOG file has been filled. The combination of Hot Standby and SR would make the latest data inserted into the primary visible in the standby almost immediately. So minimal data loss(almost only open transactions will be lost if its async rep, 0 loss if it is sync rep) 2. In postgresql.conf file, this time 5 parameters, streaming related params like below:
On Master:

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

On Slave:

hot_standby=on

3. In recovery.conf file, you would need to an extra parameter including three which you add in hot/warm standby. i.e primary_conninfo, so below are four parameters:

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.0.10 port=5432 user=postgres'
trigger_file = '/path_to/trigger'
restore_command = 'cp /path_to/archive/%f "%p"'

4. You can connect to Standby for read queries(you should set hot_stanby to ON in standby postgresql.conf file).
Detailed explanation and related docs are here: && http://bajis-postgres.blogspot.in/2013/12/step-by-step-guide-to-setup-steaming.html

Kindly let me know if I miss anything.

Anyone wants to change the tablespaces locations while running pg_upgrade ?? isn't it easy !!

noreply@blogger.com (Baji Shaik) — Sat, 05 Apr 2014 09:42:00 +0000

As we all know pg_upgrade handles tablespaces in a smart way. However it creates the tablespaces in the same location of old ones. If you want to change the location after upgrade, then you would need some work including updating the catalog table with the new location(IIRC, in PG9.4, you can do it by using ALTER TABLESPACE command). So, for current versions I would like to give you a work around to change the locations of tablespaces while running the pg_upgrade. This tweak is also applicable, If you have any contrib modules installed in databases of old cluster, you have to install them in new cluster also, however we can not create databases in new cluster as it should be empty to run pg_upgrade. "pg_upgrade" creates the databases using template "template 0" in the new cluster.

You can follow below steps to tweak the running pg_upgrade process.

1. I've upgraded PG9.1 cluster "Im_old_one" to PG9.3 cluster "Hey_Im_New", as you see below it is created new tablespace in the same directory.

-bash-4.1$ /opt/PostgreSQL/9.1/bin/psql -p 5666 -U postgres postgres
Timing is on.
psql.bin (9.1.7)
Type "help" for help.


postgres=# CREATE TABLESPACE tablsc_91 LOCATION '/tmp/tablsc_91';
CREATE TABLESPACE
Time: 1.663 ms
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin/ -B /opt/PostgreSQL/9.3/bin/ -d /tmp/Im_old_one/ -D /tmp/Hey_Im_New/ -p 5666 -P 5667
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok

.
.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh
-bash-4.1$
-bash-4.1$
-bash-4.1$ ls -ltrh /tmp/tablsc_91/
total 8.0K
drwx------. 2 postgres postgres 4.0K Apr  5 14:25 PG_9.1_201105231
drwx------. 2 postgres postgres 4.0K Apr  5 14:29 PG_9.3_201306121

2. Now delete the new cluster and run the pg_upgrade command and keep monitoring it.
3. Press control + z (pause the job) just after the catalog dump is created. You can pause the job when you see the below output:

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin/ -B /opt/PostgreSQL/9.3/bin/ -d /tmp/Im_old_one/ -D /tmp/Hey_Im_New/ -p 5666 -P 5667
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok ---> press control + z just after this.
^Z


**Note : If we fail to pause job after this "Creating catalog dump" step, then please kill this job and re-initiate the new pg_upgrade process. The re-initiate of pg_upgrade does not require any creation of new cluster or any other changes.

4. Open the global object dump and modify CREATE TABLESAPCE command to your new location:

-bash-4.1$ vi pg_upgrade_dump_globals.sql
Above file will be created in the location from where you run pg_upgrate command, open the file and change the create tablespace command from old location to new location.
Ex:-
Change all the CREATE TABLESPACE statements in the file "pg_upgrade_dump_globals.sql"

From
CREATE TABLESPACE "tablsc_91" OWNER "postgres" LOCATION '/tmp/tablsc_91';
To
CREATE TABLESPACE "tablsc_91" OWNER "postgres" LOCATION '/tmp/tablsc_93';

5. Once modifications has been completed, then resume the job by using "fg" command. It should continue with the restoration process.

Any suggestions/comments would be most welcome!

Is it failback or switchover with PostgreSQL? Ah, maybe not...

noreply@blogger.com (Baji Shaik) — Sun, 30 Mar 2014 04:50:00 +0000

Sometimes I feel like I found something new in postgres, but most of the times it turned out to be known one, I mean, already known by people. I believe, this is a kinda same thing now(this might be known by many already). I see many asking about switchover/failback in PostgreSQL and I always suggest to go for a fresh backup of new master(after failover) to sync old master as a slave to it. Of course, it is the correct procedure. However there is something to achieve this(like below procedure):
1. Setup steaming replication between two clusters i.e "Master_but_Slave_soon" with 8888 port and "Slave_gonna_Master" with 8889. See here to setup SR.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /tmp/rep/Slave_gonna_Master/ start
server starting
-bash-4.1$ LOG:  database system was interrupted; last known up at 2014-03-30 09:26:59 IST
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000003" from archive
LOG:  redo starts at 0/3000028
LOG:  consistent recovery state reached at 0/30000F0
LOG:  database system is ready to accept read only connections
cp: cannot stat `/tmp/rep/arch/000000010000000000000004': No such file or directory
LOG:  started streaming WAL from primary at 0/4000000 on timeline 1
-bash-4.1$
-bash-4.1$ ./pg_ctl -D /tmp/rep/Master_but_Slave_soon/ status
pg_ctl: server is running (PID: 26531)
/opt/PostgreSQL/9.3/bin/postgres "-D" "/tmp/rep/Master_but_Slave_soon"
-bash-4.1$
-bash-4.1$
-bash-4.1$ ./psql -p 8888 postgres<
Timing is on.
psql.bin (9.3.2)
Type "help" for help.

postgres=# \x 
Expanded display is on. 
postgres=# select * from pg_stat_replication; 
-[ RECORD 1 ]----+--------------------------------- 
pid              | 26651 
usesysid         | 10 
usename          | postgres 
application_name | walreceiver 
client_addr      | 127.0.0.1 
client_hostname  | 
client_port      | 26058 
backend_start    | 2014-03-30 09:28:59.204298+05:30 
state            | streaming 
sent_location    | 0/4000000 
write_location   | 0/4000000 
flush_location   | 0/4000000 
replay_location  | 0/4000000 
sync_priority    | 0 
sync_state       | async 
 
Time: 155.089 ms 
postgres=# 
postgres=# 
postgres=# \q 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ ./pg_ctl -D /tmp/rep/Slave_gonna_Master/ status 
pg_ctl: server is running (PID: 26643) 
/opt/PostgreSQL/9.3/bin/postgres "-D" "/tmp/rep/Slave_gonna_Master" 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ ./psql -p 8889 postgres 
Timing is on. 
psql.bin (9.3.2) 
Type "help" for help. 
 
postgres=# 
postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
------------------- 
 t 
(1 row) 
 
Time: 3.031 ms 
postgres=#

2. Go for Failover now by creating trigger file. Now our Slave is new Master.

-bash-4.1$ cat /tmp/rep/Slave_gonna_Master/recovery.conf 
standby_mode=on 
primary_conninfo='host=localhost port=8888' 
restore_command='cp /tmp/rep/arch/%f %p' 
trigger_file='/tmp/rep/8888.trigger' 
 
-bash-4.1$ touch /tmp/rep/8888.trigger 
LOG:  trigger file found: /tmp/rep/8888.trigger 
LOG:  redo done at 0/5000028 
cp: cannot stat `/tmp/rep/arch/000000010000000000000005': No such file or directory 
cp: cannot stat `/tmp/rep/arch/00000002.history': No such file or directory 
LOG:  selected new timeline ID: 2 
cp: cannot stat `/tmp/rep/arch/00000001.history': No such file or directory 
LOG:  archive recovery complete 
LOG:  autovacuum launcher started 
LOG:  database system is ready to accept connections 
 
-bash-4.1$ 
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 8889 -U postgres postgres 
Timing is on. 
psql.bin (9.3.2) 
Type "help" for help. 
 
postgres=# 
postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
------------------- 
 f 
(1 row)

3. Once failover is done, timeline ID of WALs will be changed.

bash-4.1$ ls -ltrh /tmp/rep/Slave_gonna_Master/pg_xlog/ 
total 97M 
-rw-------. 1 postgres postgres  16M Mar 30 09:27 000000010000000000000002 
-rw-------. 1 postgres postgres  16M Mar 30 09:27 000000010000000000000001 
-rw-------. 1 postgres postgres  16M Mar 30 09:28 000000010000000000000003 
-rw-------. 1 postgres postgres  16M Mar 30 09:35 000000010000000000000004 
-rw-------. 1 postgres postgres  16M Mar 30 09:35 000000010000000000000005 
-rw-------. 1 postgres postgres   41 Mar 30 09:36 00000002.history 
drwx------. 2 postgres postgres 4.0K Mar 30 09:36 archive_status 
-rw-------. 1 postgres postgres  16M Mar 30 09:41 000000020000000000000005 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ cat /tmp/rep/Slave_gonna_Master/pg_xlog/*.history 
1  0/5000090    no recovery target specified

4. Now shutdown the Master and create a recovery.conf file(change the port number to new Master for primary_conninfo parameter) and change hot_standby parameter to on in postgresql.conf and add pg_hba.conf entries in new Master to allow connections.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /tmp/rep/Master_but_Slave_soon/ stop -mf 
waiting for server to shut down....LOG:  received fast shutdown request 
LOG:  aborting any active transactions 
LOG:  autovacuum launcher shutting down 
LOG:  shutting down 
LOG:  database system is shut down 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ 
-bash-4.1$ cp -R /tmp/rep/Slave_gonna_Master/recovery.done /tmp/rep/Master_but_Slave_soon/recovery.conf 
-bash-4.1$

5. Now move pg_control of Old Master to pg_control.old and Copy the pg_control of New Master to Old Master(I'm not sure if copying pg_control between replication clusters while they are in sync is recommended).

-bash-4.1$ mv /tmp/rep/Master_but_Slave_soon/global/pg_control /tmp/rep/Master_but_Slave_soon/global/pg_control.old 
-bash-4.1$ 
-bash-4.1$ cp -R /tmp/rep/Slave_gonna_Master/global/pg_control /tmp/rep/Master_but_Slave_soon/global/

6. Now try to start the Old Master:

-bash-4.1$ ./pg_ctl -D /tmp/rep/Master_but_Slave_soon/ start 
server starting 
-bash-4.1$ LOG:  database system was interrupted; last known up at 2014-03-30 09:36:11 IST 
LOG:  entering standby mode 
LOG:  restored log file "00000002.history" from archive 
LOG:  record with zero length at 0/50000F8 
LOG:  invalid primary checkpoint record 
LOG:  using previous checkpoint record at 0/4000060 
LOG:  database system was not properly shut down; automatic recovery in progress 
LOG:  redo starts at 0/4000028 
LOG:  record with zero length at 0/5000090 
LOG:  consistent recovery state reached at 0/5000090 
LOG:  database system is ready to accept read only connections 
cp: cannot stat `/tmp/rep/arch/000000020000000000000005': No such file or directory 
LOG:  restored log file "000000010000000000000005" from archive 
LOG:  record with zero length at 0/5000090 
LOG:  started streaming WAL from primary at 0/5000000 on timeline 2

7. Check the replication status between New Master(Old Slave) and Old Master(New Slave).

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 8889 -U postgres postgres 
Timing is on. 
psql.bin (9.3.2) 
Type "help" for help. 
 
postgres=# 
postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
------------------- 
 f 
(1 row) 
 
Time: 1.469 ms 
postgres=# \x 
Expanded display is on. 
postgres=# select * from pg_stat_replication ; 
-[ RECORD 1 ]----+--------------------------------- 
pid              | 26948 
usesysid         | 10 
usename          | postgres 
application_name | walreceiver 
client_addr      | 127.0.0.1 
client_hostname  | 
client_port      | 52443 
backend_start    | 2014-03-30 09:38:09.862199+05:30 
state            | streaming 
sent_location    | 0/5000340 
write_location   | 0/5000340 
flush_location   | 0/5000340 
replay_location  | 0/5000340 
sync_priority    | 0 
sync_state       | async 
 
Time: 52.213 ms 
postgres=# 
postgres=# 
postgres=# 
postgres=# create table after_failover(t int); 
CREATE TABLE 
Time: 67.959 ms 
postgres=# 
postgres=# 
postgres=# \q 
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 8888 -U postgres postgres 
Timing is on. 
psql.bin (9.3.2) 
Type "help" for help. 
 
postgres=# 
postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
------------------- 
 t 
(1 row) 
 
Time: 1.411 ms 
postgres=# \dt after_failover 
             List of relations 
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+---------- 
 public | after_failover | table | postgres 
(1 row)

Though it works here, I don't have any other test results as it might not work in other situations.

Want to ensure that Postgres generates core dump after server crash?

noreply@blogger.com (Baji Shaik) — Sat, 08 Mar 2014 10:18:00 +0000

As you all know, we have to start the server with "-c" option in order to generate core dump during server crash. However if you want to know whether your server is already started to generate core dump, then here you go:
Start the PostgreSQL cluster with core dump option

.pg_ctl -c -D <Data directory location>

Here are the steps to find whether the server is started with -c option.

1. Check the procpid of the postmaster. Go to the location /proc/<procpid of postmaster>.
2. At this location, you can verify limits file to check whether server has started with "-c" option. If you do not have any limitations for enterprisedb user in "limits.conf" file at "/etc/security" location, then the "Soft Limit" of "Max core file size" in "limits" file will be "unlimited". The entry in limits file should be like below:

Limit Soft Limit Hard Limit Units
Max core file size unlimited unlimited bytes

If "Soft Limit" of "Max core file size" in "limits" file is "0", it indicates that your postmaster was not started with "-c" option.

Limit Soft Limit Hard Limit Units
Max core file size 0 unlimited bytes

If you have any limitations for all users or enterprisedb user in "limits.conf" file under "/etc/sercurity", then the above value for Soft Limit or Hard Limit will depends on value in "limits.conf" file.

In simple way, if your postmaster has started with "-c" option, then the "Soft Limit" of "Max core file size" in "limits" file should be some value or unlimited otherthan "0"(which depends on limits.conf file).

If we have started our postmaster with "-c" option, a core dump file will be generated under $PG_DATA directory during a backend crash. A core dump will be generated by the operating system, and we will be able to attach gdb to it to collect a stack trace or other information.

Here is one simple test case which shows us how core dump will be generated:

1. We have started the postmaster with "-c" option.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data -c start
server starting

2. Go to the location /proc/<procpid of postmaster> and check the limits file to know whether postmaster was started with "-c" option.

-bash-4.1$ ps -ef|grep PostgreSQL|grep 9.3
postgres 48892     1  0 14:28 pts/15   00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 48980 46943  0 14:30 pts/15   00:00:00 grep PostgreSQL
-bash-4.1$
-bash-4.1$ cd /proc/48892/
-bash-4.1$ ls -ltrh


enterprisedb@ubuntu:/proc/6711$ ls -ltr limits
-r-------- 1 enterprisedb enterprisedb 0 2012-08-28 05:15 limits
-bash-4.1$ grep 'core' limits
Max core file size        unlimited            unlimited            bytes   
-bash-4.1$ cat limits
Limit                     Soft Limit           Hard Limit           Units   
Max cpu time              unlimited            unlimited            seconds 
Max file size             unlimited            unlimited            bytes   
Max data size             unlimited            unlimited            bytes  
Max stack size            10485760             unlimited            bytes   
Max core file size        unlimited            unlimited            bytes   
Max resident set          unlimited            unlimited            bytes   
Max processes             1024                 15777                processes
Max open files            1024                 1024                 files   
Max locked memory         65536                65536                bytes   
Max address space         unlimited            unlimited            bytes   
Max file locks            unlimited            unlimited            locks   
Max pending signals       15777                15777                signals 
Max msgqueue size         819200               819200               bytes   
Max nice priority         0                    0                   
Max realtime priority     0                    0                   
Max realtime timeout      unlimited            unlimited            us

3. For example, aborting any backend process will generate a core dump file under $PG_DATA directory.

-bash-4.1$ ps -ef|grep 48892
postgres 48892     1  0 14:28 pts/15   00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 48893 48892  0 14:28 ?        00:00:00 postgres: logger process         postgres 49090 48892  0 14:32 ?        00:00:00 postgres: checkpointer process   postgres 49091 48892  0 14:32 ?        00:00:00 postgres: writer process         postgres 49092 48892  0 14:32 ?        00:00:00 postgres: wal writer process     
postgres 49093 48892  0 14:32 ?        00:00:00 postgres: autovacuum launcher process

postgres 49094 48892  0 14:32 ?        00:00:00 postgres: stats collector process
postgres 49114 46943  0 14:32 pts/15   00:00:00 grep 48892
-bash-4.1$
-bash-4.1$ kill -ABRT 49091 
-bash-4.1$ ls -ltrh /opt/PostgreSQL/9.3/data/core*
-rw-------. 1 postgres postgres 143M Mar  8 14:33 /opt/PostgreSQL/9.3/data/core.49091

4. We can attach gdb to core dump to collect a stack trace by using below command.

-bash-4.1$ gdb -q -c /opt/PostgreSQL/9.3/data/core.49091 /opt/PostgreSQL/9.3/bin/postgres

we should have debug symbols in our installer which was used for installation to get proper trace?, maybe, I'm poor at that, so better stay here.. ;-)

Huge archive generation with PostgreSQL cluster??

noreply@blogger.com (Baji Shaik) — Sat, 01 Mar 2014 20:10:00 +0000

Recently I found huge archive logs generation on one of the servers(for my client). Around 3500 files per day and it needs 3500 * 16 MB = 54GB space every day and space is low on the server. First thing came to my mind was checkpoint parameters, I had a look at them and they were at default. Hmm, so had changed them as below:

checkpoint_segments = 180
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9

Monitored for couple of days and found that archive logs generation decreased to 50%(around 1700 files).

With the above parameter settings, archive log generation should be minimal. However if not, Here are some general suggestions to look at:

-- Make sure that you don't UPDATE a row to the same values it already has. The row will be logged even if old and new values match. One way to handle this is to make sure that any UPDATE statements which might set a column to its existing value include in the WHERE clause a test that oldvalue IS DISTINCT FROM new value. Or you can consider creating trigger on problem tables using the Postgres built-in function suppress_redundant_updates_trigger (you might want to look here: http://www.postgresql.org/docs/9.3/static/functions-trigger.html)

-- Use one UPDATE statement instead of several where possible. If updates to different columns or from multiple processing steps can be combined, this can reduce WAL logging.

-- Consider using UNLOGGED or TEMPORARY tables where possible. For example, if data is inserted into the database and updated right away, perform the initial steps in an UNLOGGED or TEMPORARY table, and INSERT into the main table once the data has been prepared.

-- Identify and eliminate unused indexes. Index changes are WAL-logged so that indexes can be recovered on a crash. The storage space needed for indexes can be more than the storage space needed for the data itself, with a corresponding increase in generated WAL.

Copy all objects of one schema to other schema with in same database

noreply@blogger.com (Baji Shaik) — Mon, 27 Jan 2014 06:50:00 +0000

There are two ways to copy all objects of one schema to other schema with in the same database.

Procedure 1:
------------
1. Take the dump of Source schema using below command.

$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>

Ex:-
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_dump -U postgres -p 5435 -n test postgres >> /tmp/test_dmp.sql

2. Open the dumpfile which was created in step 1, and remove the "CREATE SCHEMA" and "ALTER SCHEMA" commands. Below are for example:

CREATE SCHEMA test;

ALTER SCHEMA test OWNER TO postgres;

3. Modify the Source schema name with Target schema name in the dump file.
4. Restore in the database by using below command.

$./psql -U <username> -p <port> -d <database> -f <dump filename>

Ex:- 
$./psql -U postgres -p 5435 -d postgres -f dump.sql

Or connect to psql prompt and execute the dump file from target database.

postgres=#\i <dump file location>
Ex:- #\i dump.sql

Procedure 2:
------------ --
-- Rename the target schema to different name and rename the source schema to target schema name. Take the dump of source schema which is renamed to target schema(You can use step 1 in procedure 1 to take the dump). And rename the source schema and target schema to their previous names. And then restore the schema(you can use step 4 in procedure 1 to restore the dump)

For example your source schema name is test1 and target schema name is test2.
1. Rename the test2 schema to test3 and rename the test1 schema to test2.

postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;

2. Take the dump of test2.

$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>

3. Rename test2 to test1 and test3 to test2.

postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;

4. Restore the dump of test2 which is taken in step 2.

$./psql -U <username> -p <port> -d <database> -f <dump filename>

Note: You can ignore the errors of schema already exists during the restore process as target schema is already exists.

Types of Reads and Levels of Transaction Isolation in PostgreSQL

noreply@blogger.com (Baji Shaik) — Sat, 21 Dec 2013 07:28:00 +0000

I see many people asking differences between different types of reads and transaction isolation levels of PostgreSQL. Here you go:

Q1: Types of Reads:

=============

1. dirty reads:

If one transaction were able to see the uncommitted work of another transaction, that would be a dirty read. That would generally be a very bad thing, since that work might never be committed.

2. non-repeatable reads

If a transaction were able to read a row, and then later read the same row and see different values based on the work of another transaction, that would be a non-repeatable read.

3. phantom reads

If a transaction were able to read a set of rows matching some logical condition, and later read that same logical set of rows and see new ones which qualify because of the work of another transaction, that would be a phantom read. A transaction always sees its own work.

Q2: Levels of Transaction Isolation:

======================

1. TRANSACTION_NONE

This is not a real transaction isolation level. This is defined in the JDBC specification specifically to deal with drivers which don't support transaction isolation levels. It does not match to any transaction isolation level in the SQL standard, and should not be used with a driver which does support transaction isolation levels.

The following isolation levels do correspond to transaction isolation levels described in the SQL standard. Three of these levels are defined by which of the phenomena from Q1 are allowed; the fourth is not defined by the above phenomena, although the standard notes that due to how it is defined, none of the above phenomena are possible. It is also worth noting that the standard does not require any of these phenomena to be allowed at any level; an implementation is conforming to the standard if it is more strict, but not if it is less strict. Among other things, this means that it is OK for a transaction isolation level's implementation to be the same as a more strict transaction isolation level's implementation.

Because PostgreSQL uses Multi-Version Concurrency Control (MVCC) for managing concurrency, it takes “snapshots” as a key component of how it implements these transaction isolation levels. A snapshot controls which other transactions' work will be visible. The work of transactions which have committed at the moment a snapshot is taken will be visible. The work of any transactions which have rolled back will not be visible, nor will the work of any transactions which are active or which have not yet begun when the snapshot was taken.

2. TRANSACTION_READ_COMMITTED

In the SQL standard, this transaction isolation level must not allow dirty reads, but non-repeatable reads and phantom reads are allowed. In PostgreSQL dirty reads are not possible and non-repeatable and phantom reads can, in fact, happen.

PostgreSQL takes a snapshot at the start of each statement, after it has parsed that statement and analyzed it sufficiently to determine that a snapshot is in fact required. This provides the statement with a stable view of the database for the duration of that statement, with one exception: if an UPDATE or DELETE statement finds that it is about to modify a row which a concurrent, uncommitted transaction has already updated or deleted, it blocks and waits for the other transaction to complete. If that other transaction rolls back rather than committing, the UPDATE or DELETE can proceed normally. If the other transaction commits, PostgreSQL will look for the newest version of the row created by updates; if the row has not been deleted and if it still matches the selection criteria for the query, that new version is updated or deleted. Because the statement is no longer working with a single snapshot of the database after blocking for a concurrent transaction's work, there are rare conditions under which this can produce surprising results.

3. TRANSACTION_READ_UNCOMMITTED

In the SQL standard, any of the phenomena described in Q1 are allowed, including dirty reads. In PostgreSQL this is implemented the same as TRANSACTION_READ_COMMITTED; dirty reads are not, in fact, allowed. This is permitted by the SQL standard.

4. TRANSACTION_REPEATABLE_READ

In the SQL standard, this transaction isolation level must not allow dirty reads or non-repeatable reads, but phantom reads are allowed. In PostgreSQL none of these phenomena can, in fact, happen.

PostgreSQL takes a snapshot the first time within such a transaction that it finds that it needs one. This does not happen with the statement which starts the transaction, nor for many of the utility statements such as LOCK TABLE or SET READ ONLY which might be executed near the beginning of the transaction. This is by design, and is an important point to understand when using explicit locking; if you acquire a lock after the snapshot is taken, you might block because of a concurrent update to a row, but if you read that row after the blocking transaction commits, you will see the value according to the snapshot – that is, the old version before the concurrent update. Explicit locks should normally be taken at the start of the transaction, before a snapshot has been acquired, to prevent this.

Once a repeatable read transaction has acquired a snapshot, it uses that for the remainder of the transaction. This technique is called “snapshot isolation” and provides more guarantees than required for repeatable read, but falls short of the requirements of the standard for serializable transactions. It is worth noting that versions of PostgreSQL prior to 9.1 used this transaction isolation level when a serializable transaction was requested. At least one other database product still does this, although it allows certain well-known serialization anomalies to occur.

5. TRANSACTION_SERIALIZABLE

In the SQL standard, there is a requirement that any set of concurrent serializable transactions produces an effect which is consistent with some serial (one-at-a-time) execution of those transactions. The standard notes that due to this definition, none of the phenomena described in Q1 will be possible. Although this definition of the serializable transaction isolation level has been present in every version of the SQL standard for almost 20 years, confusion persists – one can still find mistaken assertions that avoiding the three phenomena mentioned in Q1 is not only necessary but also sufficient to comply with the standard.

In version 9.1 and later PostgreSQL uses a technique called Serializable Snapshot Isolation (often abbreviated as SSI), to implement the serializable transaction isolation level. Rather than using blocking to implement the desired transaction isolation, it allows snapshot isolation (described above for repeatable read transactions) to operate, while monitoring for the conditions under which a serialization anomaly could cause problems. If such a situation occurs, a transaction will be rolled back with a serialization failure (SQLSTATE 40001) and should be retried from the beginning.

In versions prior to 9.1 there was no difference between serializable and repeatable read transactions; the behavior provided by both transaction isolation levels in earlier releases is still available as the repeatable read transaction isolation level in version 9.1 and later.

Physical Machine Vs Virtual Machine

noreply@blogger.com (Baji Shaik) — Sat, 14 Dec 2013 17:35:00 +0000

1)Databases on (VM's) guest operating systems by design when they start they grab blocks of a resource and manage it directly for performance reasons. As soon as you make the core operating system of the database server a guest in virtualized hosting environment then you are placing an arbitration layer with the hypervisor between the block allocated element of disk and RAM and the database server. It will slow down. The more inefficient your queries, the more it will slow.To be clear, that a finely tuned, massively busy, high performance database server should have its own physical hardware.

2) Not Recommended to go with a virtualized deployment because of the I/O penalty in VM its hard to know exactly what the performance penalty is but there is one. It is intrinsically more complex and harder to trace performance problems and does not allow disk I/O bandwidth to be allocated on a per-virtual server basis.

3)There is no doubt that virtualization requires extra hardware resources. The problem is that it is almost impossible to estimate in advance how many extra resources will be needed. We know that there are capacity planning guides and tools but from our experience every piece of software behaves differently in a virtualized environment. We have applications that are quite modest as long as they run on a physical server, but when they were virtualized their resource requirement multiplied.

4)If They are running multiple servers on the same host, the IO situation gets worse: it becomes even more important to carefully manage how many Servers end up on a single physical host, and more difficult to balance the IO requirements of each server.

5)Unit of DB performance per unit of Hardware is a bit lower for a virtualized db. This means it requires more hardware to get the same level of performance compare to physical server.A common resource problem to look out for is adding additional VM's and thinning out the available resources or allocation of existing resources among virtual servers.And when it shows a sign of getting affected by OS resources it does not remain a recommended option to host large production database in virtual environment.

6)Level of interaction would increase the cost of processes on the virtual server due to its extra layer which could be a potential threat for CPU consumption and writing latency.

Step By Step Guide to setup Steaming Replication.

noreply@blogger.com (Baji Shaik) — Tue, 10 Dec 2013 16:08:00 +0000

Though many know about streaming replication, I would like to start from Prerequisites and with some Introduction about this replication as this blog is for beginners :-).

Prerequisites:

1. Same version of the PostgreSQL Database must be installed on both servers.
 2. Configure the Password-less ssh authentication to use the “postgres” user.
 3. Production server must operate in WAL archiving enabled mode by setting archive_mode and archive_command in postgresql.conf file.
 4. Production and stand by servers always should have connectivity between them to transfer the archived WAL files from production.
 5. Set up your standby host's environment and directory structure exactly the same as your primary.

Introduction:

It is an asynchronous mechanism; the standby server lags behind the master. But unlike other replication methods, this lag is very short, and can be as little as a single transaction, depending on network speed, database activity, and Streaming replication settings. Also, the load on the master for each slave is minimal, allowing a single master to support dozens of slaves.   The feature is included in Postgresql-9.0, with this the second database instance (normally on a separate server) replaying the primary's binary log, while making that standby server can accept read-only queries.

Here are the practical steps with necessary commands:
---------------------------------------------------------------

1. Connect to Master and create a "replication" user with replication privilege.

Ex:

$ psql
Password for user postgres:
psql.bin (9.2.1) Type "help" for help.
postgres=# create user replication with replication password '<password>';

2. We need to change some parameters in postgresql.conf and authentication in pg_hba.conf which are located at /opt/PostgreSQL92/data/ location on Master. Set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary.

 $ $EDITOR postgresql.conf

listen_addresses = '*'

$ $EDITOR pg_hba.conf

#The standby server must have superuser access privileges.
host replication replication 10.176.0.0/16 md5

3. Set up the streaming replication related parameters on the primary server.

 $EDITOR postgresql.conf
#To enable readonly queries on a standby server, wal_level must be set to "hot_standby". But you can choose "archive" if you never connect to the server in standby mode.

wal_level = hot_standby

#Set the maximum number of concurrent connections from the standby servers.

max_wal_senders = 5 

#To prevent the primary server from removing the WAL segments required for the standby server before shipping them, set the minimum number of segments retained in the pg_xlog directory. 

wal_keep_segments = 32

#Enable WAL archiving on the primary to an archive directory accessible from the standby. If wal_keep_segments is a high enough number to retain the WAL segments required for the standby server, this may not be necessary.

archive_mode    = on 

archive_command = 'cp %p <archive location>%f && scp %p postgres@10.176.112.189:<archive location>/%f'

Note: Restart the cluster after modifying the above parameters in postgresql.conf file.

4. Re-start postgres on the primary server and check if the parameters are affected.

postgres=# show archive_command ;
       archive_command
      -----------------
 cp %p /var/PG92_Archives/%f
(1 row)
postgres=# show archive_mode ;
 archive_mode
-------------
 on
(1 row)
postgres=# show wal_level ;
  wal_level
------------
 hot_standby
(1 row)
postgres=# show max_wal_senders ;
 max_wal_senders
----------------
 5
(1 row)
postgres=# show wal_keep_segments ;
 wal_keep_segments
------------------
 32

5. Make a base backup of Master server's data directory.

$ psql c "SELECT pg_start_backup('label', true)" 
$ cp /opt/PostgreSQL92/data/* backup/ 
$psql c "SELECT pg_stop_backup()" 
-- tar the backup directory and move to standby location. 
$tar cvzf backup.tar backup/

$scp backup.tar postgres@10.176.112.189:/opt/PostgreSQL92/

6. Move the slave data directory contents to any other location, untar the backup file and copy contents to slave data directory.

7. Set up replication-related parameters, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover. 8. Enable read-only queries on the standby server. But if wal_level is archive on the primary, leave hot_standby unchanged (i.e., off).

$ $EDITOR postgresql.conf
hot_standby = on

9. Create a recovery command file in the standby server; the following parameters are required for streaming replication.

$ $EDITOR recovery.conf

# Specifies whether to start the server as a standby. In streaming
# replication, this parameter must to be set to on.
standby_mode          = 'on'
# Specifies a connection string which is used for the standby server to
# connect with the primary.

primary_conninfo      = 'host=10.176.112.188 port=5432 user=replication
password=<password> application=<app_name>'

# Specifies a trigger file whose presence should cause streaming
# replication to end (i.e., failover).

trigger_file = '' ===> Do not create the file. You have to
create the file when failover.

# Specifies a command to load archive segments from the WAL archive. If
# # wal_keep_segments is a high enough number to retain the WAL segments
# # required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base
# backup.

restore_command = 'cp %f "%p"'

10. Start postgres in the standby server. It will start streaming replication and you will see log messages like below:

LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/1D000078
LOG:  record with zero length at 0/1D000078
LOG:  streaming replication successfully connected to primary

11. You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location function on the primary side and the pg_last_xlog_receive_location or pg_last_xlog_replay_location function on the standby, respectively.

$ psql c "SELECT pg_current_xlog_location()" h192.168.0.10
(primary host)
 pg_current_xlog_location

 0/2000000
(1 row)

$ psql c "select pg_last_xlog_receive_location()" h192.168.0.20
(standby host)
 pg_last_xlog_receive_location

 0/2000000
(1 row)
$ psql c "select pg_last_xlog_replay_location()" h192.168.0.20
(standby host)
 pg_last_xlog_replay_location

 0/2000000
(1 row)

12. Other ways to check streaming replication: the easiest way is "select now()-pg_last_xact_replay_timestamp();" at slave side. pg_last_xact_replay_timestamp() function gives time stamp of last transaction replayed during recovery, time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last transaction applied during that recovery. When the server has been started normally without recovery the function returns NULL. You can try with some operation on Master and then check the fuction output.

If you want to check the delay manually, then go for below steps:

Step 1:Need to create table on Primary using below command.

create table stream_delay (tstamp timestamp without time zone );

insert into stream_delay select now();

Step 2: schedule the below command on primary to execute every minute on cronjob.

update stream_delay set tstamp='now()';

step 3: verify the delay on slave by selecting the "stream_delay" table.

It should show the last time that was updated in primary. Difference between this timestamp and current timestamp of slave server shows the time delay between Primary and slave.

You can also check the progress of streaming replication by using ps command.#The displayed LSNs indicate the byte position that the standby server has written up to in the xlogs.

[primary] $ ps ef | grep sender
postgres  6879  6831  0 10:31 ?        00:00:00 postgres: wal sender
process postgres 127.0.0.1(44663) streaming 0/2000000
[standby] $ ps ef | grep receiver
postgres  6878  6872  1 10:31 ? receiver process   streaming 0/2000000

Thanks all, Kindly let me know if I miss anything.

Getting Started with PostgreSQL for Beginners

noreply@blogger.com (Baji Shaik) — Sat, 16 Nov 2013 16:54:00 +0000

I hope these commands will hep beginners to start with PostgreSQL.

We have “pg_ctl” command which we can use to control the postgresql database. We can see this commands behavior as below.
>> How to check the postgresql service status.

$ su – root
# service postgresql9.2 status
(OR)
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl D /opt/PostgreSQL92/data status # We need to pass data directory path after D.

>> How to start the postgresql service.

 $ su – root
# service postgresql9.2 start

>> How to start the postgresql as a postgres user(operating system user).

 $ /opt/PostgreSQL92/bin/pg_ctl D /opt/PostgreSQL92/data start.

>> How to stop the postgresql.

$ su – root
# service postgresql9.2 stop
(OR)
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl D /opt/PostgreSQL92/data stop mf

>> How to reload the postgresql.
Reload postgresql means, force the postgresql to allow the modifications which we do in postgresql.conf/pg_hba.conf.

 $ su – root
# service postgresql9.2 reload 

(OR)
$/opt/PostgreSQL92/bin/pg_ctl D /opt/PostgreSQL92/data reload

**Note: Any modifications in pg_hba.conf must require a reload operation of a postgresql database. But, all the parameters in postgresql.conf don’t imply on the database until and unless we restart the database.
>> How to find which parameter change require restart.
This query list out all the parameters which require your postgresql database restart.

 postgres=# select name from pg_settings where context ~ 'postmaster';

This query list out all the parameters which do not require postgresql database restart but only reload.

 postgres=# select name from pg_settings where context != 'postmaster';

>> How to monitor the database connections like which application user executing which query from which ip. This query list out all the connection details of a cluster.

 postgres=# select * from pg_stat_activity ;

This query list out all the active connections list, i.e, the connections which are doing some actions on the database.

 postgres=# select * from pg_stat_activity where waiting is false;

This query list out all the connections list which are waiting.

 postgres=# select * from pg_stat_activity where waiting is true;

>> How to get the object details. In postgresql, we have some meta commands which helps you to get the list of objects. Ex:-

 postgres=# \dt > Meta command to get the list of tables.
List of relations


Schema | Name | Type | Owner
+++
public | test_p | table | postgres 

\dv --> Meta command to get the list of views.
\di --> List of indexes.


\d  --> Table description

You can find all these meta commands by executing “\?” in the psql terminal. These commands are not supported in any other applications. For this, we need to use “psql” terminal as below.

 bash4.1$ ./psql p 5432 U postgres postgres
psql.bin (9.1.6, server 9.2.0)

WARNING: psql.bin version 9.1, server version 9.2.
Some psql features might not work.
Type "help" for help.
postgres=#

where p - port, U - user
postgres is a database in the cluster.

>> How to get the current locks in database.

 postgres=# select * from pg_locks;

>> How to check the details of replication between the primary and slave.

 postgres=# select * from pg_stat_replication ;

The above query gives us the state of the replication and lag between the primary and slave servers.
>> How to find the blocked queries.

This query list out all the sessions those are currently waiting on which sessions.

SELECT now()::timestamp,waiting.locktype AS waiting_locktype,waiting.relation::regclass::VARCHAR  ASwaiting_table,waiting_stm.Datname as WDatabase,waiting_stm.current_query AS waiting_query,(extract(epochfrom now()) - extract(epoch from  waiting_stm.query_start))::VARCHAR AS Waiting_Stmt_Total_Time,waiting.mode AS waiting_mode,waiting.pid::VARCHAR AS waiting_pid,other.locktype::VARCHAR AS other_locktype,other.relation::regclass::VARCHAR AS other_table,other_stm.Datname::VARCHAR as ODatabase,other_stm.current_query AS other_query,(extract(epoch from now())-extract(epoch from other_stm.query_start))::VARCHAR AS Other_Stmt_Total_Time,other.mode AS other_mode,other.pid::VARCHAR AS other_pid,other.granted::VARCHAR AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.procpid = waiting.pid) JOIN pg_catalog.pg_locks AS other On ((waiting.database = other.database AND waiting.relation = other.relation) OR waiting.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.procpid = other.pid) WHERE NOT waiting.granted AND waiting.pid <> other.pid;

>> How to get the database statistics like hitratio, dml statistics.

SELECT sdb.datname,blks_read,blks_hit,round((blks_hit::float / (blks_read+blks_hit+1) * 100)::numeric, 2) as cachehitratio,xact_commit,xact_rollback,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,ROUND(100.0*AGE(DATFROZENXID)/ (SELECT SETTING::float FROM PG_SETTINGS WHERE NAME ILIKE 'autovacuum_freeze_max_age')) FROM pg_stat_database sdb INNER JOIN pg_database db on sdb.datname=db.datname WHERE sdb.datname !~ '^(template(0|1))' ORDER BY cachehitratio desc;

The above query will give you the database statistics.

PostgreSQL Installation

noreply@blogger.com (Baji Shaik) — Sat, 16 Nov 2013 15:59:00 +0000

PostgreSQL Installation using installer:

Here are steps to install the postgres through installer:

--> Download the installer from below link:

http://www.enterprisedb.com/products-services-training/pgdownload

--> Goto the location of PostgreSQL 9.2 installer(postgresql-9.2.5-1-linux-x64.run) and run the installer in text mode as below

 ./postgresql-9.2.5-1-linux-x64.run --mode text   --> You need to provide the below details while installation.

Installation Directory [/opt/PostgreSQL/9.2]: ===> Provide the base directory for installation (We have provided /opt/PostgreSQL92 in test servers)

Data Directory [/opt/PostgreSQL92/data]: ===> Provide the data directory location(We have provided /opt/PostgreSQL92/data location)

Password : ==> provide the password for "postgres" superuser Retype password :

Port [5433]: ===> provide the port number

Locale
[1] [Default locale] [2] C
[3] en_AG
[4] en_AG.utf8
[5] en_AU.utf8 [6] en_BW.utf8 [7] en_CA.utf8 [8] en_DK.utf8 [9] en_GB.utf8 [10] en_HK.utf8 [11] en_IE.utf8 [12] en_IN
[13] en_IN.utf8 [14] en_NG [15] en_NG.utf8 [16] en_NZ.utf8 [17] en_PH.utf8 [18] en_SG.utf8
[19] en_US.utf8
[20] en_ZA.utf8
[21] en_ZW.utf8
[22] POSIX
Please choose an option [1] : 1 ==> select the locale (By default it is [1] Default locale)
Do you want to continue? [Y/n]: y
---------------------------------------------------------------------------- Please wait while Setup installs PostgreSQL on your computer.
Installing
0% ______________ 50% ______________ 100% 
#################################

--> After the installation, you need to set environment variables. There is a pg_env.sh file in which all environment variables are exported in /opt/PostgreSQL92(which base installation directory). You can run that file to set env settings Or you can set it in .bash_profile where every postgres user login env file gets run.

--> Open the bash file $vi .bash_profile -- Enter the below line:
source /opt/PostgreSQL92/pg_env.sh -- Run the bash file for the first time
$. .bash_profile

--> Now you can connect to database by just typing the "psql"

-bash-4.1$ psql
Timing is on.
psql.bin (9.2.5)
Type "help" for help.

postgres=#

PostgreSQL Architecture

noreply@blogger.com (Baji Shaik) — Mon, 28 Oct 2013 18:20:00 +0000

I know its not a first blog on PostgreSQL Architecture, but its my first one.. :-) I have created a diagram of PostgreSQL architecture based on my experience with the database.

I have discussed this chart with PostgreSQL experts on how Postgres works and here is some description from my discussion:

Architecture Diagram:
=============

Working Process:
===========

When you start PostgreSQL, The Postmaster starts first and allocates the shared memory. It also accepts connections and spins off a backend for each new connection. So each backend (server process) gets its pointers to shared memory from the postmaster. It is pretty disastrous if the postmaster dies with backends still running, so we have it do as little as possible, so that there isn't as much which can crash it. Postgres does have a pool of shared memory; however, it does not have a library or dictionary cache stored in that memory. This means that statements do need to be parsed and planned every time they are entered. If parse/plan overhead is an issue, we suggest the use of prepared statements. While Oracle is able to avoid the repeated parse/plan overhead, it must still do enough analysis of the query to determine whether the information is present in the library cache, which also consumes some time and CPU resources. The parser is quite lightweight, so we feel that the overhead of parsing the query each time is acceptable.

1. Shared Memory:
----------------------

SHARED BUFFERS -- The biggest chunk of shared memory is shared_buffers. When pages from a table or index are read from the OS, they are read into shared_buffers, and the backends reference the pages and their contents right there in shared memory. An exception is temporary tables, where (since only the creating backend can reference the temp table) data is accessed in temp_buffer space as much as possible(temp_buffers is separate. It is not in shared memory). It's faster to access process-local memory like that because you don't need to worry about pinning or locking the data, since you are not sharing it.
WAL BUFFERS -- are for buffering data to be written to the WAL files.
CLOG BUFFERS -- are one of the SLRU-style buffers oriented toward circular "rings" of data, like which transaction numbers have been committed or rolled back.
LOCK SPACE -- Memory structures in shared memory are generally protected by "lightweight" locks, which are in shared memory. Tables are protected by "heavyweight" locks which are also in shared memory (and themselves protected by lightweight locks). Of course, lightweight locks are protected by spinlocks. It gets very complicated and fussy. :-)
OTHER BUFFERS -- are probably mostly SLRU buffers besides CLOG (which was the first user of the SLRU system). SLRU is good for data where you mostly want to use recently accessed data and then you are done with it relatively quickly.
The opposite of shared memory is process-local memory -- only the one process that allocates it can access it. Each SLRU system has a separate subdirectory. Shared memory is memory that all of the backend server processes can directly access. To prevent chaos, access to shared memory must follow some rules which tends to make it a little slower, like locking areas of memory a process will be using. Process-local memory is allocated by one backend server process, and the other backend server processes can't see it or use it, so it's faster to access, and no worries about another process trashing it while you're using it.
Getting back to the shared memory uses I hadn't talked about, CLOG buffers and SLRU buffers like multixact, notify, subtrans, serial, etc. use buffers in memory for recently accessed data, but spill to disk in those subdirectories beyond a limited number of buffers.

2. Utility Processes:
---------------------
With a default configuration I see the postmaster, the checkpointer process, the writer process, the wal writer process, the autovacuum launcher process, and the stats collector process. I think you will see more processes running if you turn on archiving or streaming replication. You might also get a process for writing the server log, depending on configuration. As their name say, WRITER -- process is responsible to write the dirty buffers to data files, CHECKPOINTER -- process is for checkpoint, WAL WRITER -- is for writing the dirty buffers in WAL buffers to WAL files, AUTOVACUUM LAUNCHER -- process lauches autovacuum when require(depends on your autovacuum settings in postgresql.conf file) and STATS COLLECTOR -- process to collect the statistics of objects in the database require by Optimizer to improve the performance.

The checkpointer process is responsible for creating safe points from which a recovery can begin; the background writer tries to keep some pages available for re-use so that processes running queries don't need to wait for page writes in order to have free spots to use in shared buffers. Both checkpointer and writer processes writes to the same files, however the checkpointer writes all data that was dirty as of a certain time (the start of the checkpoint) regardless of how often it was used since dirtied, and the background writer writes data that hasn't been used recently, regardless of when it was first dirtied. Neither knows or cares whether the data being written was committed, rolled back, or still in progress.

3. Directory Structure:
--------------------------
All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as PGDATA. You can get the detailed description at below link:
http://www.enterprisedb.com/docs/en/9.2/pg/storage-file-layout.html

I see the diagram left out the one I would like to add: pg_serial. pg_serial is used to track summarized information about committed serializable transactions which might still become part of a serialization failure rolling back some not-yet-committed transaction to protect data integrity.
The catalog cache is information from the system tables which describes the tables, indexes, views, etc. in the database. If you had to re-read that from the system tables each time, it would be slow. Even shared memory would be clumsy for that, so each backend process has its own cache of system catalog data for fast lookup. When anything changes, all backends are sent a signal to update or reload their cache data. When pages are read or written, they go through the OS cache, which is not directly under PostgreSQL control. The optimizer needs to keep track of a lot of information while it parses and plans a query, which is why that is shown. A plan has execution nodes, some of which may need to use memory; that is where work_mem comes in -- a sort or hash table (as examples) will try not to exceed work_mem *for that node*. It is significant that one query might use quite a few nodes which each allocate memory up to work_mem. But since most queries are simpler and might not use any work_mem allocations, people often do their calculations based on an expected maximum of one allocation per backend (i.e., per connection). But that could be off by quite a bit if all connections might be running queries with five nodes allocating memory.

It is worth noting that if there is enough RAM on the machine to have a good-sized OS cache, a PostgreSQL page read will often just be a copy from system cache to pg shared_buffers, and a page write will often just be a copy from pg shared_buffers to the system cache. The fsync of tables which is part of the checkpoint process is when they are actually written from the OS to the storage system. But even there a server may have a battery-backed RAM cache, so the OS write to storage is often just a copy in RAM.... unless there is so much writing that the RAID controller's cache fills, at which point writes suddenly become hundreds of times slower than they were.

Other interesting dynamics: pg will try to minimize disk writes by hanging onto dirty buffers (ones which have logically been updated) before writing them to the OS. But buffers may need to be written so they can be freed so that a new read or write has a buffer to use. If a request to read a page or write to a new buffer can't find an idle page, the query might need to write a buffer dirtied by some other backend before it can do its read (or whatever). The background writer can help with this. It tries to watch how fast new pages are being requested and write out dirty pages at a rate which will stay ahead of demand.