PostgreSQL News

Syndicate content
PostgreSQL news
Updated: 16 weeks 6 days ago

Barman 1.3.1 released

14 April, 2014 - 03:00

14 April 2014: 2ndQuadrant is proud to announce the release of version 1.3.1 of Barman, Backup and Recovery Manager for PostgreSQL.

This minor release introduces support for concurrent backup using physical file based copy through "rsync", in conjunction with pgespresso, a new open source extension available for PostgreSQL 9.2 and 9.3. Concurrent backup allows database administrators that rely on Barman, to finally offload backup operations to a streaming replicated standby server, opening new important scenarios in disaster recovery architectures of PostgreSQL 9.2+ database servers.

The "barman diagnose" command has been implemented to print important information about the system and the configuration of Barman, allowing users to provide detailed diagnostics data in case of support requests.

Version 1.3.1 fixes an important bug on recovery that was affecting only those users having tablespaces created inside the PGDATA directory. This behaviour was introduced in version 1.3.0.

Minor bugs have also been fixed.

Many thanks for funding towards the development of this release go to Adyen (www.adyen.com).

For a complete list of changes, see the "Release Notes" section below.

Links

Release notes

  • Added support for concurrent backup of PostgreSQL 9.2 and 9.3 servers that use the "pgespresso" extension. This feature is controlled by the "backup_options" configuration option (global/server) and activated when set to "concurrent_backup". Concurrent backup allows DBAs to perform full backup operations from a streaming replicated standby.
  • Added the "barman diagnose" command which prints important information about the Barman system (extremely useful for support and problem solving)
  • Improved error messages and exception handling interface
  • Fixed bug in recovery of tablespaces that are created inside the PGDATA directory (bug introduced in version 1.3.0)
  • Fixed minor bug of unhandled -q option, for quiet mode of commands to be used in cron jobs (bug introduced in version 1.3.0)
  • Minor bug fixes and code refactoring

Download

About Barman

Barman (Backup and Recovery Manager) is an open source administration tool for disaster recovery of PostgreSQL servers written in Python. It allows your organisation to perform remote backups of multiple servers in business critical environments and help DBAs during the recovery phase. Barman’s most requested features include backup catalogues, retention policies, remote backup and recovery, archiving and compression of WAL files and backups. Barman is distributed under GNU GPL 3.

Database .NET 11 released!

14 April, 2014 - 03:00

Database .NET v11 is an innovative, powerful and intuitive multiple database management tool, With it you can Browse objects, Design tables, Edit rows, Export data and Run queries with a consistent interface. Free, All-In-One, Portable, Standalone (No Installation) and Multlanguage.

New features from version 10.1 to 11.0:
  • Compatible with the latest versions of PostgreSQL.
  • Updated to Npgsql.dll 2.1.3
  • Added Support for JSON data type of PostgreSQL
  • Added Support for renaming all objects of PostgreSQL
  • Added Executing SQL Statements from a text file
  • Added Displaying Row Count of tables
  • Added Displaying Connection time
  • Added Data Editor for View objects
  • Added Search Table Data
  • Added Empty Table
  • Added New Connection Manager
  • Added New Object Navigator
  • Added Script All Table Data (INSERTs)
  • Added IntelliSense for Cross-schema table access
  • Added Selected Text to Query Builder
  • Added Dynamic Context Menu[?]
The new version is immediately available for download.

Postgres Open 2014 - Opens the Call for Papers

10 April, 2014 - 03:00

Postgres Open 2014 will be held in Chicago, IL, at the Hotel Sax, September 17 - 19, 2014. It will feature two full days of multiple parallel tracks of PostgreSQL presentations (September 18 - 19th) from both local and global speakers, covering a wide range of topics. In addition we will also be offering a separate day of tutorials (Wednesday, September 17th). For more information about the conference, please see our website http://postgresopen.org/2014

The Program Committee is currently accepting proposals for presentations at the conference. We are interested in submissions from both seasoned PostgreSQL experts and people new in the community, from both locals and representatives of the global community. In short, from anybody who has an interesting story to tell about PostgreSQL, whether deeply technical or story about a successful (or failed) usage. All presentations are 45 minutes, with time for questions. Talks can be submitted via the website: http://postgresopen.org/2014/callforpapers/

Our early-bird ticket registration will open in May 19, 2014 and are available through June 30, 2014, after which tickets will go up to their regular price.

Finally, we are also looking for sponsors! We several tiers of sponsorship, to make sure there is a choice for everybody. If you are interested, please see the Postgres Open sponsor page http://postgresopen.org/2014/becomesponsor/

We look forward to seeing you in Chicago in September!

PostgreSQL updates 9.3.4, 9.2.8, 9.1.13, 9.0.17, and 8.4.21 released

20 March, 2014 - 03:00

The PostgreSQL Global Development Group has released an update to all supported version of the database system, including versions 9.3.4, 9.2.8, 9.1.13, 9.0.17, and 8.4.21. This minor release fixes a data corruption issue with replication and crash recovery in version 9.3, as well as several other minor issues in all versions. All users of version 9.3 are urged to update their installations at the next possible downtime. Users of older versions should update at their convenience.

The data corruption issue in PostgreSQL 9.3 affects binary replication standbys, servers being recovered from point-in-time-recovery backup, and standalone servers which recover from a system crash. The bug causes unrecoverable index corruption during recovery due to incorrect replay of row locking operations. This can then cause query results to be inconsistent depending on whether or not an index is used, and eventually lead to primary key violations and similar issues. For this reason, users are encouraged to replace each of their standby databases with a new base backup after applying the update.

Other PostgreSQL 9.3-only fixes in this update include:

  • Make sure that statistics files for dropped databases get deleted
  • Allow materialized views to be referenced in UPDATE and DELETE queries
  • Add read-only data_checksum parameter
  • Prevent erroneous operator push-down in postgres_fdw

This release resolves some other issues in all versions of PostgreSQL, including:

  • Fix timing consistency issue with NOTIFY
  • Allow regular expression execution to be cancelled
  • Improve performance of index checks for newly added rows
  • Prevent premature walsender disconnection
  • Prevent memory errors on newer Windows versions
  • Update timezone files

Additional changes and details of some of the above issues can be found in the Release Notes. Two of the issues which affect version 9.3 have additional information on the 9.3.4 Update Wiki Page.

Users of version 8.4 should note that it will reach End-of-Life (EOL) three months from now, per our Versioning Policy. This means that this is likely to be the next-to-last update for version 8.4, and users should be planning to upgrade to a newer version of PostgreSQL.

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 * 9.3.4 Update Wiki Page

PostgreSQL Database Appliance from Fujitsu

10 March, 2014 - 03:00

Fujitsu has released a PostgreSQL based database appliance system, called HA Database Ready SX2. This appliance is capable of achieving up to 180,000 transactions per minute and AES based data encryption. HA Database Ready SX2 combines the Open Source PostgreSQL Database with advanced hardware configuration and Fujitsu's own Smart Software Technology, to deliver high performance and high reliability database systems that will help organizations to achieve high-speed batch processing and data consolidation.

More details can be found at: http://www.fujitsu.com/global/news/pr/archives/month/2014/20140228-02.html

PostgreSQL participating in Google Summer of Code 2014

24 February, 2014 - 03:00

The PostgreSQL project has again been selected to take part in Summer of Code for 2014. Google will be funding several students to work with mentors from our project in order to hack PostgreSQL code for the summer.

Applications for students open March 10th. Our Summer Of Code page has all the information you need about applying this year.

If you are connected with a university, please make sure that students know about this opportunity. If you are a student, please apply!

PostgreSQL 9.3.3, 9.2.7, 9.1.12, 9.0.16 and 8.4.20 released!

20 February, 2014 - 03:00

The PostgreSQL Global Development Group has released an important update to all supported versions of the PostgreSQL database system, which includes minor versions 9.3.3, 9.2.7, 9.1.12, 9.0.16, and 8.4.20. This update contains fixes for multiple security issues, as well as several fixes for replication and data integrity issues. All users are urged to update their installations at the earliest opportunity, especially those using binary replication or running a high-security application.

Security Fixes

This update fixes CVE-2014-0060, in which PostgreSQL did not properly enforce the WITH ADMIN OPTION permission for ROLE management. Before this fix, any member of a ROLE was able to grant others access to the same ROLE regardless if the member was given the WITH ADMIN OPTION permission. It also fixes multiple privilege escalation issues, including: CVE-2014-0061, CVE-2014-0062, CVE-2014-0063, CVE-2014-0064, CVE-2014-0065, and CVE-2014-0066. More information on these issues can be found on our security page and the security issue detail wiki page.

With this release, we are also alerting users to a known security hole that allows other users on the same machine to gain access to an operating system account while it is doing "make check": CVE-2014-0067. "Make check" is normally part of building PostgreSQL from source code. As it is not possible to fix this issue without causing significant issues to our testing infrastructure, a patch will be released separately and publicly. Until then, users are strongly advised not to run "make check" on machines where untrusted users have accounts.

Replication and Data Integrity Fixes

This update also fixes some issues which affect binary replication and row locking, and can cause recoverable data corruption in some cases. There are several fixes to index page locking issue during replication which can cause indexes on the replica to be corrupted. There is a fix to a transaction freezing bug in version 9.3 which could cause databases which cycled through transaction ID wraparound several times to have old row versions reappear. We have also fixed three bugs which could cause new standbys to fail to start up. Finally, this update fixes an issue which could break foreign keys, although the keys themselves will still need to be fixed manually after applying the update.

In version 9.3, these fixes result in the addition of several new server configuration settings to control multixact freezing. Importantly, standby servers must be updated to 9.3.3 or later before the replication master is updated, or replication will be broken.

Other Improvements

In addition to the above, the following issues are fixed in this release:

  • Fix WAL logging of visibility map change
  • Make sure that GIN indexes log all insertions
  • Get pause_at_recovery_target to pause at correct time
  • Ensure walreceiver sends hot-standby feedback messages on time
  • Prevent timeout interrupts from taking control away from mainline code
  • Eliminate several race conditions
  • Fix some broken HINTs in error messages
  • Prevent server lockup on SSL connection loss
  • Fix two Unicode handling issues
  • Prevent crash on certain subselect syntax
  • Prevent crash on select from zero column table
  • Fix two bugs with LATERAL
  • Fix issue with UNION ALL, partitioning, and updates
  • Ensure that ANALYZE understands domains over ranges
  • Eliminate permissions check when using default tablespace
  • Fix memory leakage in JSON functions
  • Allow extensions with event triggers
  • Distinguish numbers correctly in JSON output
  • Fix permissions for pg_start_backup() and pg_stop_backup()
  • Accept SHIFT_JIS as locale name
  • Fix .* expansion for SQL function variables
  • Prevent infinite loop on some COPY connection failures
  • Several fixes for client issues on Windows
  • Enable building PostgreSQL with Visual Studio 2013
  • Update time zone files for recent changes

There are also fixes to all of the following optional modules (extensions): ECPG, dblink, ISN, pgbench, pg_stat_statements and postgres_fdw. Additional changes and details of some of the above issues can be found in the Release Notes.

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 * Security Page * Security Issue Detail Page.

pgBadger 5: Analyze your logs daily with the incremental mode

10 February, 2014 - 03:00

Paris, France - Feb. 10, 2014

DALIBO is proud to announce the release of pgBadger 5, a PostgreSQL performance analyzer, built for speed with fully detailed reports based on your Postgres log files.

This major version comes with a bunch of new metrics such as the SQL queries times histogram, some fixes in the new HTML5 design and the ability to build cumulative reports.

New incremental mode

The incremental mode is an old request issued at PgCon Ottawa 2012 that concern the ability to construct incremental reports with successive runs of pgBadger. It is now possible to run pgbadger once a day (or even every hours) and have cumulative reports per day and per week. A top index page allow you to go directly to the weekly and daily reports.

Here's screenshot of the new index page http://dalibo.github.io/pgbadger/screenshots/pgbadgerv5_index.png

This mode have been build with simplicity in mind. You just need to running pgbadger with cron as follow:

0 23 * * * pgbadger -q -I -O /var/www/pgbadger/ /var/log/postgresql.log

This is enough to have daily and weekly reports viewable using your browser.

Take a look at our demo here: http://dalibo.github.io/pgbadger/demov5/

There's also a useful improvement to allow pgBadger to seek directly to the last position in the same log file after a successive execution. This feature is only available using the incremental mode or the -l option and parsing a single log file. Let's say you have a weekly rotated log file and want to run pgBadger each days. With 2GB of log per day, pgbadger was spending 5 minutes per block of 2 GB to reach the last position in the log, so at the end of the week this feature will save you 35 minutes. Now pgBadger will start parsing new log entries immediately. This feature is compatible with the multiprocess mode using -j option (n processes for one log file).

New Histograms

This new major release adds some new metrics like an hourly graphic representation of the average count and duration of top normalized queries. Same for errors or events, you will be able to see graphically at which hours they are occurring the most often.

For example: http://dalibo.github.io/pgbadger/screenshots/pgbadgerv5_histogram.png

There's also a new "Histogram of query times", which is a new graph in the top queries slide that shows the query times distribution during the analyzed period.

For example: http://dalibo.github.io/pgbadger/screenshots/pgbadgerv5_histogram_2.png

There is also some graphic and report improvements, such as the mouse tracker formatting that have been reviewed. It now shows a vertical crosshair and all dataset values at a time when mouse pointer moves over series. Automatic queries formatting has also been changed, it is now done on double click event as simple click was painful when you wanted to copy some part of the queries.

Autovacuum reports now associate database name to the autovacuum and autoanalyze entries. Statistics now refer to "dbname.schema.table", previous versions was only showing the pair "schema.table".

This release also adds "Session peak" information and a report about "Simultaneous sessions". Parameters log_connections and log_disconnections must be enabled in postgresql.conf for this.

Links & Credits

DALIBO would like to thank the developers who submitted patches and the users who reported bugs and feature requests, especially Martin Prochazka, Herve Werner, tmihail, Reeshna Ramakrishnan, Guillaume Smet, Alexander Korotkov and Casey Allen Shobe.

pgBadger is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly on our mailing list.

About pgBadger:

pgBagder is a new generation log analyzer for PostgreSQL, created by Gilles Darold (also author of ora2pg, the powerful migration tool). pgBadger is a fast and easy tool to analyze your SQL traffic and create HTML5 reports with dynamics graphs. pgBadger is the perfect tool to understand the behavior of your PostgreSQL servers and identify which SQL queries need to be optimized.

Docs, Download & Demo at http://dalibo.github.io/pgbadger/

About DALIBO:

DALIBO is the leading PostgreSQL company in France, providing support, trainings and consulting to its customers since 2005. The company contributes to the PostgreSQL community in various ways, including : code, articles, translations, free conferences and workshops

Check out DALIBO's open source projects at http://dalibo.github.io

DBD::Pg 3.0.0 released

5 February, 2014 - 03:00

Version 3.0.0 of DBD::Pg, the Perl interface to Postgres, has just been released. The new version is available from CPAN.

Barman 1.3.0 released

3 February, 2014 - 03:00

2ndQuadrant is proud to announce the release of version 1.3.0 of Barman, Backup and Recovery Manager for PostgreSQL.

This major release has a new code infrastructure in terms of output, subprocesses, remote commands, file system, events ('hooks') and metadata management, making the application more robust.

Barman 1.3.0 introduces a few configuration options for:

  • managing hook scripts before and after archiving a WAL file
  • network compression of remote backup, and
  • recovery operations and immediate checkpoint at backup time

Administrators can now force a rebuild of the "xlog.db" file (WAL archive) through the "'barman rebuild-xlogdb'" command.

This major version now correctly handles those tablespaces that had been created inside the "PGDATA" directory, removing content duplication.

Remote recovery code has been improved in order to behave exactly like local recovery does, including preparation of the directories layout ("PGDATA" and tablespaces).

Minor bugs have also been fixed.

Many thanks for funding towards the development of this release go to Ecometer (www.ecometer.it), Agile Business Group (www.agilebg.com) and a French company (that prefers to remain anonymous).

For a complete list of changes, see the "Release Notes" section below.

Links Release notes
  • Refactored "BackupInfo" class for backup metadata to use the new "FieldListFile" class ("infofile" module)
  • Refactored output layer to use a dedicated module, in order to facilitate integration with Nagios ("NagiosOutputWriter" class)
  • Refactored subprocess handling in order to isolate stdin/stderr/stdout channels ("command_wrappers" module)
  • Refactored hook scripts management
  • Extracted logging configuration and userid enforcement from the configuration class.
  • Support for hook scripts to be executed before and after a WAL file is archived, through the "'pre_archive_script'" and "'post_archive_script'" configuration options.
  • Implemented immediate checkpoint capability with "--immediate-checkpoint" command option and "'immediate_checkpoint'" configuration option
  • Implemented network compression for remote backup and recovery through the "'network_compression'" configuration option (#19)
  • Implemented the "'rebuild-xlogdb'" command (Closes #27 and #28)
  • Added deduplication of tablespaces located inside the "PGDATA" directory
  • Refactored remote recovery code to work the same way local recovery does, by performing remote directory preparation (assuming the remote user has the right permissions on the remote server)
  • "'barman backup'" now tries and create server directories before attempting to execute a full backup (#14)
  • Fixed bug #22: improved documentation for tablespaces relocation
  • Fixed bug #31: "'barman cron'" checks directory permissions for lock file
  • Fixed bug #32: "xlog.db" read access during cron activities
Download About Barman:

Barman (Backup and Recovery Manager) is an open source administration tool for disaster recovery of PostgreSQL servers written in Python. It allows your organisation to perform remote backups of multiple servers in business critical environments and help DBAs during the recovery phase. Barman’s most requested features include backup catalogues, retention policies, remote recovery, archiving and compression of WAL files and backups. Barman is distributed under GNU GPL 3.