PostgreSQL

Links

2 links submitted so far. Submit your own links.

News

Edit RSS feeds.

Francisco Figueiredo Jr: Finally post my PGCon2008 pictures!!

Posted 1 day ago

Hi all,
I finally post my pictures from PGCon2008.
You can see them on my picasa web album.

Devrim Gündüz: Looking for a presentation regarding HOT?

Posted 1 day ago

In case you missed this, here is a very good presentation about HOT. Oscar goes to Pavan Deolasse. (I prepared a Turkish presentation regarding MVCC,Vacuum,Bgwriter and HOT, and you can also find it here, if you know Turkish ) ...and yes, I'm enjoying a perfect holiday -- I have always made good decisions while swimming.

Kenny Gorman: Python script showing PostgreSQL objects in linux memory: pg_osmem.py

Posted 2 days ago

I got some email and comments about the code I used for my post; Mapping Linux memory for PostgreSQL using fincore so I thought I would post the code I am using. Nothing too fancy here, it needs a config file and some more bits. I highly recommend ... [More] someone do this in perl vs python and incorporate fincore as a module instead. I used psycopg2, other than that, all the other modules are stock with python 2.5.2. If someone wants to show me how to query the data directory PostgreSQL is currently using and incorporate that into the script vs the static string: mydir = “/data/mydb/base”, that would be great. In order to use this script, you must change the variables for fincore, and mydir below.

 
#!/home/postgres/python/bin/python
#
# script to find show memory usage of PG buffers in OS cache
# 2008 kcg
#
import os
import psycopg2
import commands
import re
import sys
from optparse import OptionParser
 
parser = OptionParser()
parser.add_option("-u","--username",dest="username",help="username for PostgreSQL")
parser.add_option("-m","--machine",dest="machine",help="machine to connect to.. aka: hostname")
parser.add_option("-d","--dbname",dest="dbname",help="database name to connect to")
parser.add_option("-p","--password",dest="password",help="password for PostgreSQL")
(options, args) = parser.parse_args()
 
osmem ={}
# change these to match actual locations
fincore ="/home/kgorman/fincore.pl"
mydir = "/data/mydb/base"
 
# get list of dbs on host, and return dictionary of db=oid sets
def lookup_dbs():
dbs={}
connectstr="host=" options.machine " dbname=" options.dbname " user=" options.username " port=5432 password=" options.password
handle=psycopg2.connect(connectstr)
curs=handle.cursor()
sql="select datname,oid from pg_database where datname = '" options.dbname "' and datname not like '%template%'"
curs.execute(sql)
for d in curs.fetchall():
dbs[d[0]]=d[1]
return dbs
 
# get object
def lookup_oid(oid,dbname):
connectstr="host=" options.machine " dbname=" dbname " user=" options.username " port=5432 password=" options.password
handle=psycopg2.connect(connectstr)
curs=handle.cursor()
sql="select relname from pg_class where oid = " oid
curs.execute(sql)
for d in curs.fetchall():
return d[0]
 
dbs=lookup_dbs()
for v, i in dbs.iteritems():
for ii in os.listdir(mydir "/" str(i)):
p = re.compile('\d')
if p.match(ii):
#print ii
rel=lookup_oid(ii,v)
fullpath=mydir "/" str(i) "/" ii
cmd=fincore " " fullpath
#print cmd
pages=commands.getstatusoutput(cmd)
#print pages
n=pages[1].split(' ')
size=n[1]
if p.match(size):
if rel:
osmem[v ":" rel]=(int(size)*1024)
 
# sort and output
sdata=sorted(osmem.iteritems(), key=lambda (k,v): (v,k), reverse=True)
a=0
print "OS Cache Usage:"
while a < len(sdata):
print sdata[a][0] ":" str(sdata[a][1])
a=a 1 [Less]

Jignesh K. Shah: wal_buffers and PostgreSQL 8.3.3

Posted 2 days ago

Recently Felix Malinkevich joined the ISV-E Open Source Team at Sun as an Intern working on PostgreSQL [Read More] 

Robert Hodges: PostgreSQL Gets Religion About Replication

Posted 2 days ago

The PostgreSQL community is getting really serious about replication. On Thursday May 29th, Tom Lane issued a manifesto concerning databas replication on behalf of the PostgreSQL core team to the pgsql-hackers mailing list. Tom's post basically ... [More] said that lack of easy-to-use, built-in replication is a significant obstacle to wider adoption of PostgreSQL and proposed a technical solution based on log shipping, which is already a well-developed and useful feature.

What was the reaction? The post generated close to 140 responses within the next two days, with a large percentage of the community weighing in. It's one of the most significant announcements on the list in recent history. There is pent up demand for this feature and within a few hours people were already deep into the details of the implementation.

The basic idea comes from an excellent presentation by Takahiro Itagaki and Masao Fujii of NTT at PGCon 2008 in Ottowa. They have developed a system that replicates database log records synchronously to a standby database. The standby can recover quickly and without data loss, which makes it a good availability solution. The core team manifesto proposes to integrate this into the PostgreSQL core and add the ability to open the standby for reads.

So, is this the end of the story on replication? I don't think so. There's no question that synchronous log shipping with reads would be a great feature. Basic availability is the first problem users run into when setting up production systems and this feature looks considerably better than alternatives for other databases like MySQL. It will help if NTT donates their code to the community, but still the whole effort will take considerable time. Adding the ability to open a standby for reads is at least a version out (read: up to 2 years).

More importantly, log shipping is most useful for availability. It does not help you replicate across database versions (nice for upgrades), between different databases, from a master to large numbers of slaves, or bi-directionally between databases. Finally, it's a less than ideal solution for clustering data between sites, something that is rapidly becoming one of the most important overall uses of replication. For these and other cases you need logical replication, which turns log records into SQL statements and applies them using a client.

I'm therefore starting an effort to get logical replication hooks included as a parallel effort. If you are interested in this let me know. Meanwhile, stay tuned. Tom's message represents a real change of heart for the PostgreSQL community. Accepting the important of replication opens up the doors for a new round of innovation in scale-out based on PostgreSQL. It could not come at a better time. [Less]

Read all PostgreSQL articles…