PostgreSQL 9.3 released!

The PostgreSQL Global Development Group announces the release of PostgreSQL 9.3, the latest version of the world's leading open source relational database system. This release expands PostgreSQL's reliability, availability, and ability to integrate with other databases. Users are already finding that they can build applications using version 9.3 which would not have been possible before.

"PostgreSQL 9.3 provides features that as an app developer I can use immediately: better JSON functionality, regular expression indexing, and easily federating databases with the Postgres foreign data wrapper. I have no idea how I completed projects without 9.3," said Jonathan S. Katz, CTO of VenueBook.

Writable External Data

Version 9.3 makes PostgreSQL's Foreign Data Wrappers writable, enabling two-way data interchange between systems. Today's complex IT environments involve multiple databases and semi-structured data sources, and PostgreSQL helps you integrate them into a coherent stack. The project has also released postgres_fdw, a higher-performance, read/write PostgreSQL-to-PostgreSQL federation driver.

"Writable foreign data wrappers enable us to plug in and seamlessly test various backend alternatives, allowing us to address different needs quickly and prototype intelligently," explained Lee Holloway, Co-founder and Lead Engineer at CloudFlare. "It is exciting to conceive and toss up new data stores (including our in-house experimental ones written in Go) and then watch them read, write, and even transact each other."

Enhanced Reliability and Availability

"Postgres has been my favorite choice for its well known stability, robustness, strong coherency, safety, ACID, and SQL conformance," said Pascal Bouchareine, director of research and development at Gandi.net. "I'm especially excited to hear more about the fast failover coming in 9.3."

This new release includes features to further improve and extend PostgreSQL's well-known reliability and availability:

• Data Page Checksums: help administrators quickly detect failing disks and bad hardware that corrupts data
• Fast Failover: enables sub-second switchoffs from master to replica, supporting "carrier-grade" availability
• Streaming-Only Remastering: easier, faster reconfiguration of cascading replicas after failover

Developer-friendly Features

As with every annual release, PostgreSQL 9.3 includes many features to make working with PostgreSQL easier, more flexible and more fun for application developers, administrators and architects. These features include:

• Additional JSON constructor and extractor methods
• Automatically updatable VIEWs
• Parallel pg_dump to speed backups of large databases
• LATERAL JOINs

Further, PostgreSQL 9.3's User-Defined Background Workers allow developers to write task managers, request handlers, parallel processors, queuing tools and other helper applications to enable PostgreSQL as a workload coordinator. One example of this already released is Mongres, a background worker which accepts MongoDB queries, interprets them and passes them on to PostgreSQL.

Links

• Downloads
• Press Kit
• Release Notes
• What's New in 9.3

the chamelion circuit

I decided to create a brand new project to migrate schema and data from mysql (for now) to PostgreSQL.

I've named pg_chamelion as the database from can be anything. As I said I'm starting with mysql but when I'll get an usable product I will write libraries for sqllite and other dbms.

I'm using sqlalchemy, quick and dirty.

I'm not a great fan of those database abstraction layers; they are making developers ignorant about a wonderful language called SQL and generating monsters IMHO.

Anyway, the metadata package is idiots proof, I've written a conect-read-transform library in barely 120 rows and now I'm able to dump the ddl on file or write directly over a postgresql connection.

The next step is to move data across the databases; I'll give freedom of choice, statements or dump reload using copy.

My old script used statements and incredibly worked fine even for medium size databases, but if I want my new library to be usable for the new buzzword, bigdata I need an high performance strategy or this will remain a nice toy.

The project can be pulled here, GPL V3 licence.
https://github.com/the4thdoctor/pg_chameleon  

Pythons elephants and zombie

Sorry for the delay.
I want to thank the brighton and hove python users group for the great evening.

As I promised here's the view to get the dead files on the PGDATA.
Dead files are files not mapped in the pg_class system table.
They can be present if any new file generation is interrupted by a crash recovery where the wal replay rollback the system catalogue operation but not delete the new files.
Not sure if this is a bug.
Anyway this is the code on pastebin.

Please be aware the view is specific for PostgreSQL 9.2. I'll try to build a self adapting version sooner or later.

http://pastebin.com/THzti7gW

As I had some interest in PostgreSQL I've started a simple google group, the sea elephants, for aggregating PostgreSQL users in the Brighton and Hove area.

I know there's a national usergroup (or some sort of it) but in my humble experience, local is better.

Because it's better to start from the little things, like a good beer in a lovely pub.

And yes this is an hint for the first meeting ever.

Talk on slideshare

Still WIP, I need to write ~ 10 slides yet. Sorry for my english in advance.

A couple of things about PostgreSQL... from Federico Campoli

Upcoming Talk

A quick post to update my upcoming tlalk.

I still having some problems to organise and concentrate, so I decided to cancel my talk in Milton Keynes.

I'll be this Tuesday 9th of July at the skiff in Brighton with a talk on PostgreSQL covering the physical storage, the memory management and the query execution, plus a couple of things.

Details here.
http://brightonpy.org/


I'll publish the slides on slideshare ASAP.

still alive....

I'm not dead.
I've just passed the worst period in my life. Four months, it seems ages.

Anyway, I'm back.
For now I'll do just a quick update then I hope I will start to post with more frequency.

I've submitted my first bug report on PostgreSQL, but id #8192, the default block size. Funny indeed.
The bug, if a bug it is, is quite strange. I noticed on a strange lag on my hot standby machines,  9.1 and 9.2. During a conventional vacuum on the master the slave started lagging in a strange way, the recovery process stuck on a single wal file accumulating lag and lag.
The bug description with the steps to reproduce can be found here. With small databases probably the problem will run silent unnoticed, but I'm not dealing with small databases...

And that's drive us to the next news. I'll do a talk to the upcoming PGDay UK next July the 12th. I will talk about very large database maintenance, how to keep in shape indices and tables with minimal impact on the application layer using a very small maintenance window.

Last but not least July the 9th at the Skiff in the sunny Brighton UK I will do a talk for the Python Brighton and Hove Users Group.
I'm still deciding the title, probably will be some sort of cookbook for developers from a DBA point of view.

A couple of things to know about Postgresql....
(before start coding) 
Sounds good.

PostgreSQL 9.2.3, 9.1.8, 9.0.12, 8.4.16 and 8.3.23 released

The PostgreSQL Global Development Group has released a security update to all current versions of the PostgreSQL database system, including versions 9.2.3, 9.1.8, 9.0.12, 8.4.16, and 8.3.23. This update fixes a denial-of-service (DOS) vulnerability. All users should update their PostgreSQL installations as soon as possible.

The security issue fixed in this release, CVE-2013-0255, allows a previously authenticated user to crash the server by calling an internal function with invalid arguments. This issue was discovered by independent security researcher Sumit Soni this week and reported via Secunia SVCRP, and we are grateful for their efforts in making PostgreSQL more secure.

Today's update also fixes a performance regression which caused a decrease in throughput when using dynamic queries in stored procedures in version 9.2. Applications which use PL/pgSQL's EXECUTE are strongly affected by this regression and should be updated. Additionally, we have fixed intermittent crashes caused by CREATE/DROP INDEX CONCURRENTLY, and multiple minor issues with replication.

This release is expected to be the final update for version 8.3, which is now End-of-Life (EOL). Users of version 8.3 should plan to upgrade to a later version of PostgreSQL immediately. For more information, see our Versioning Policy.

This update release also contains fixes for many minor issues discovered and patched by the PostgreSQL community in the last two months, including:

• Prevent unnecessary table scans during vacuuming
• Prevent spurious cached plan error in PL/pgSQL
• Allow sub-SELECTs to be subscripted
• Prevent DROP OWNED from dropping databases or tablespaces
• Make ECPG use translated messages
• Allow PL/Python to use multi-table trigger functions (again) in 9.1 and 9.2
• Fix several activity log management issues on Windows
• Prevent autovacuum file truncation from being cancelled by deadlock_timeout
• Make extensions build with the .exe suffix automatically on Windows
• Fix concurrency issues with CREATE/DROP DATABASE
• Reject out-of-range values in to_date() conversion function
• Revert cost estimation for large indexes back to pre-9.2 behavior
• Make pg_basebackup tolerate timeline switches
• Cleanup leftover temp table entries during crash recovery
• Prevent infinite loop when COPY inserts a large tuple into a table with a large fillfactor
• Prevent integer overflow in dynahash creation
• Make pg_upgrade work with INVALID indexes
• Fix bugs in TYPE privileges
• Allow Contrib installchecks to run in their own databases
• Many documentation updates
• Add new timezone "FET".

As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shut down PostgreSQL and update its binaries. Users who have skipped multiple update releases may need to perform additional, post-update steps; see the Release Notes for details.

Links:

• Download
• Release Notes

Moving neo_my2pg to github

The neo_my2pg is a python script that migrate schema and data from mysql to PostgreSQL without intermediate dump.

I've written this code years ago to help some friends to move their phpbb from mysql to PostgreSQL.

At that time, when I was young and dumb (now I'm only dumb), my knowledge on python was absolutely basic. I've no difficult to say this script is c***p, but it does the job quite well.


So, after the last pgfoundry outage I decided to move my code on github in order to have a more reliable platform and start a new development branch for this script.

I should start pushing the new code in few weeks, for now the old one is accessible here.

https://github.com/the4thdoctor/neo_my2pg

Should you need any help please shout :)