opendbteam workspace

opendbteam workspace
opendbteam workspace

Tuesday, May 23, 2017

#postgresql #pitrery 1.13 released

pitrery is a set of Bash scripts to manage PITR backups for PostgreSQL. 

pitrery automates Continuous Archiving and Point-in-Time Recovery 
(PITR) as much as possible with the following goals: 

* Handle only PITR. log-shipping and replication are out of scope 
* Be as minimally intrusive as possible for archiving 

It is free software licensed under the PostgreSQL License and works 
with all versions of PostgreSQL from 8.2. 

This is a bugfix release, including the following fixes: 

* Fix tablespaces links being overwritten by postmaster on 9.5 due to 
tablespace_map. This conflicted with our tablespace relocation 

* Fix the purge of WAL file that would remove all archives if the stop 
file is missing 

* Fix backups failing due to incorrect parsing of the stop time when 
iso8601 timestamps are used 

Thursday, May 18, 2017

Beta release of #PostgreSQL 10 is available for download

The PostgreSQL Global Development Group announces today that the first
beta release of PostgreSQL 10 is available for download. This release
contains previews of all of the features which will be available in
the final release of version 10, although some details will change
before then.  Users are encouraged to begin testing their applications
against this latest release.

Major Features of 10

The new version contains multiple features that will allow users to
both scale out and scale up their PostgreSQL infrastructure:

* Logical Replication: built-in option for replicating specific tables
or using replication to upgrade
* Native Table Partitioning: range and list partitioning as native
database objects
* Additional Query Parallelism: including index scans, bitmap scans,
and merge joins
* Quorum Commit for Synchronous Replication: ensure against loss of
multiple nodes

We have also made three improvements to PostgreSQL connections, which
we are calling on driver authors to support, and users to test:

* SCRAM Authentication, for more secure password-based access
* Multi-host "failover", connecting to the first available in a list of hosts
* target_session_attrs parameter, so a client can request a read/write host

Additional Features

Many other new features and improvements have been added to PostgreSQL
10, some of which may be as important, or more important, to specific
users than the above.  Certainly all of them require testing. Among
them are:

* Crash-safe and replicable Hash Indexes
* Multi-column Correlation Statistics
* New "monitoring" roles for permission grants
* Latch Wait times in pg_stat_activity
* XMLTABLE query expression
* Restrictive Policies for Row Level Security
* Full Text Search support for JSON and JSONB
* Compression support for pg_receivewal
* ICU collation support
* Push Down Aggregates to foreign servers
* Transition Tables in trigger execution

Further, developers have contributed performance improvements in the
SUM() function, character encoding conversion, expression evaluation,
grouping sets, and joins against unique columns. Analytics queries
against large numbers of rows should be up to 40% faster. Please test
if these are faster for you and report back.

See the Release Notes for a complete list of new and changed features.

Test for Bugs and Compatibility

We count on you to test the altered version with your workloads and
testing tools in order to find bugs and regressions before the release
of PostgreSQL 10. As this is a Beta, minor changes to database
behaviors, feature details, and APIs are still possible. Your feedback
and testing will help determine the final tweaks on the new features,
so test soon. The quality of user testing helps determine when we can
make a final release.

Additionally, version 10 contains several changes that are
incompatible with prior major releases, particularly renaming "xlog"
to "wal" and a change in version numbering. We encourage all users
test it against their applications, scripts, and platforms as soon as
possible.  See the Release Notes and the What's New page for more

Beta Schedule

This is the first beta release of version 10. The PostgreSQL Project
will release additional betas as required for testing, followed by one
or more release candidates, until the final release in late 2017. For
further information please see the Beta Testing page.


* Downloads Page:

* Beta Testing Information:

* 10 Beta Release Notes:

* What's New in 10:

* 10 Open Items:

Thursday, February 9, 2017

#New #PostgreSQL #Releases!

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 9.6.2, 9.5.6, 9.4.11, 9.3.16, and 9.2.20. This release includes fixes that prevent data corruption issues in index builds and in certain write-ahead-log replay situations, which are detailed below. It also patches over 75 other bugs reported over the last three months.
Users should plan to apply this update at the next scheduled downtime.


There existed a race condition if CREATE INDEX CONCURRENTLY was called on a column that had not been indexed before, then rows that were updated by transactions running at the same time as the CREATE INDEX CONCURRENTLY command could have been indexed incorrectly.
If you suspect this may have happened, the most reliable solution is to rebuild affected indexes after installing this update.
This issue is present in the 9.2, 9.3, 9.4, 9.5, and 9.6 series of PostgreSQL.

Fixes for visibility and write-ahead-log stability

These release contains several fixes to improve the stability of visible data and WAL logging that we wish to highlight here.
Prior to this release, data could be prematurely pruned by a vacuum operation when a special snapshot used for catalog scans was presently available. Specifically, the vacuum operation would not be aware of the oldest xmin for this special snapshot. The error would surface with a message such as:
"cache lookup failed for relation 1255"
This release ensures that vacuum operations will account for the catalog scan snapshots.
Additionally, there are several fixes to improved the stability of write-ahead-logging, including:
* A fix for BRIN index WAL logging where a replay could make a portion of the BRIN index useless and require recomputing
* A fix for an unlogged table where a WAL-log would be created under the "wal_level = minimal" setting, and upon replay after crash, the table would not appear to be properly reset
* A fix in the WAL page header validation when re-reading segments that fixes the error of  "out-of-sequence TLI" that could be reported during recovery
Theses issues are present in the 9.6 series of PostgreSQL releases and may also be present in the 9.2, 9.3, 9.4, and 9.5 series.

Bugs fixes and improvements at:

Monday, January 30, 2017

#PostgreSQL #Ora2Pg 18.0 released

Version 18.0 of Ora2Pg, a free and reliable tool used to migrate an
Oracle database to PostgreSQL, has been officially released and is
publicly available for download.

This new major release adds several new useful features and lot of

  * Automatic rewrite of simple form of (+) outer join Oracle's
    syntax. This major feature makes Ora2Pg become the first free
    tool that is able to rewrite automatically some (+) outer join
    in command line mode. It works with simple form of outer join
    but this is a beginning.
  * Add export of Oracle's virtual column using a real column and
    a trigger.
  * Allow conversion of RAW/CHAR/VARCHAR2 type with precision in
    DATA_TYPE directive. Useful for example to transform all RAW(32)
    or VARCHAR2(32) columns into PostgreSQL special type uuid.
  * Add export NOT VALIDATED state from Oracle foreign keys and check
    constraints into NOT VALID constraints in PostgreSQL.
  * Replace call to SYS_GUID() with uuid_generate_v4() by default.
  * Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
    transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
  * Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
    calls and other PL/SQL to plpgsql replacement. There should not
    be any limitation in rewriting when a function contains a sub
    query or an other function call inside his parameters.
  * Refactoring of ora2pg to not requires any dependency other than
    the Perl DBI module by default. All DBD drivers are now optionals
    and ora2pg will expect an Oracle DDL file as input by default.
  * Add export of Oracle's global variables defined in package. They
    are exported as user defined custom variables and available in
    a session. If the variable is a constant or have a default value
    assigned at declaration, ora2pg will create a new file with the
    declaration (global_variables.conf) to be included in the main
    configuration file postgresql.conf file.
  * Create full text search configuration when USE_UNACCENT directive
    is enabled using the auto detected stemmer or the one defined in
    FTS_CONFIG. For example:
            hword, hword_part, word WITH unaccent, french_stem;
        CREATE INDEX place_notes_cidx ON places
                USING gin(to_tsvector('fr', place_notes));

Changes and incompatibilities from previous release:

  * FTS_INDEX_ONLY is now enabled by default because the addition of
    a column is not always possible and not always necessary where a
    simple function-based index is enough.
  * Remove use to setweigth() on single column FTS based indexes.
  * Change default behaviour of Ora2Pg in Full Text Search index

A new command line option and some configuration directive have
been added:

  * Option -D | --data_type to allow custom data type replacement
    at command line like in configuration file with DATA_TYPE.
  * UUID_FUNCTION to be able to redefined the function called to
    replace SYS_GUID() Oracle function. Default to uuid_generate_v4.
  * REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle
    native syntax (+) into OUTER JOIN if rewritten code is broken.
  * USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to
    use the unaccent extension with pg_trgm with the FTS indexes.
  * FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column
    with a dedicated triggers for FTS indexes. Enable this directive
    if you just want a function-based index like:
    CREATE INDEX ON t_document USING
        gin(to_tsvector('pg_catalog.english', title));
  * FTS_CONFIG, use this directive to force the text search stemmer
    used with the to_tsvector() function. Default is to auto detect
    the Oracle FTS stemmer. For example, setting FTS_CONFIG to
    pg_catalog.english or pg_catalog.french will override the auto
    detected stemmer.

Thanks to all contributors, they are all cited in the changelog file.

Useful Links:

    - Website:
    - Download:
    - Development:
    - Changelog:
    - Documentation:

About Ora2Pg :

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL.
It is developed since 2001 and can export most of the Oracle objects
(table, view, materialized view, tablespace, sequence, indexes, trigger,
grant, function, procedure, package, partition, data, blob and external

Ora2Pg works on any platform and is available under the GPL v3 licence.

Tuesday, January 24, 2017

#postgresql #pgbadger 9.1 is out !

pgBadger is a PostgreSQL performance analyzer, built for speed with 
fully detailed reports based on your PostgreSQL log files. 

pgBadger 9.1 was released today, this is a maintenance release that 
fix some minor bugs. There is also a new report on error class 
distribution and some feature enhancements: 

* Add report of error class distribution when SQLState is available 
in the log_line_prefix (see %e placeholder). 
* Update SQL Beautifier to pgFormatter v1.6 code. 
* Improve error message normalization. 
* Add --normalized-only option to generate a text file containing all 
normalized queries found in a log with count. 
* Allow %c (session id) to replace %p (pid) as unique session id. 
* Add waiting for lock messages to event reports. 
* Add --start-monday option to start calendar weeks in Monday 
instead of default to Sunday

Here are the complete list of changes: 

- Add report of error class distribution when SQLState is available 
in the log line prefix. Thanks to jacks33 for the feature request. 
- Fix incremental global index on resize. Thanks to clomdd for the 
- Fix command tag log_line_prefix placeholder %i to allow space 
- Fix --exclude-line options and removing of obsolete directory 
when retention is enabled and --noreport is used. 
- Fix typo in "vacuum activity table". Thanks to Nicolas Gollet for 
the patch. 
- Fix autovacuum report. Thanks to Nicolas Gollet for the patch. 
- Fix author of pgbadger's logo - Damien Cazeils and English in 
comments. Thanks to Thibaut Madelaine for the patch. 
- Add information about pgbouncer log format in the -f option. 
Thanks to clomdd for the report. 
- Add --normalized-only information in documentation. 
- Fix broken report of date-time introduced in previous patch. 
- Fix duration/query association when log_duration=on and 
log_statement=all. Thanks to Eric Jensen for the report. 
- Fix normalization of messages about advisory lock. Thanks to 
Thibaut Madelaine for the report. 
- Fix report of auto_explain output. Thanks to fch77700 for the 
- Fix unwanted log format auto detection with log entry from stdin. 
Thanks to Jesus Adolfo Parra for the report. 
- Add left open parentheses to the "stop" chars of regex to look 
for db client in the prefix to handle the PostgreSQL client 
string format that includes source port. Thanks to Jon Nelson 
for the patch. 
- Fix some spelling errors. Thanks to Jon Nelson for the patch. 
- Allow %c (session id) to replace %p (pid) as unique session id. 
Thanks to Jerryliuk for the report. 
- Allow pgBadger to parse default log_line_prefix that will be 
probably used in 10.0: '%m [%p] ' 
- Fix missing first line with interpreter call. 
- Fix missing Avg values in CSV report. Thanks to Yosuke Tomita 
for the report. 
- Fix error message in autodetect_format() method. 
- Add --start-monday option to start calendar weeks in Monday 
instead of default to Sunday. Thanks to Joosep Mae for the feature 
- Fix --histo-average option. Thanks to Yves Martin for the report. 
- Remove plural form of --ssh-option in documentation. Thanks to 
mark-a-s for the report. 
- Fix --exclude-time filter and rewrite code to skip unwanted line 
as well code to update the progress bar. Thanks to Michael 
Chesterton for the report. 
- Fix support to %r placeholder in prefix instead of %h. 

===== Links & Credits ===== 

DALIBO would like to thank the developers who submitted patches and the 
users who reported bugs and feature requests. 

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. 

Links : 

* Mailing List : 

Monday, January 16, 2017


If you’re trying to install CentOS 7 on HP server and you receive the error from the caption, don’t worry – you’re not alone. According to Google, there are about 48400 results related to this topic
The fix is still not available and according to HP, the problem is related to “Processor Power and Utilization Monitoring” function which should be disabled to fix this mess.
Affected servers:
– All ProLiant Gen8 Servers
– ProLiant DL580 G7
– ProLiant BL620 G7
– ProLiant BL680 G7
How to disable “Processor Power and Utilization Monitoring”:
– enter BIOS (press F9 during boot)
– press CTRL+A (Service Option is hidden by default)
– select “Service Options” -> Processor Power and Utilization Monitoring -> Disable
Press F10 to save and exit and reboot the server.
DL380 Gen9 is also affected with this problem. The solution remains the same (disable Processor Power and Utilization Monitoring)
Edit: 2016-03-31 (comment by Jimmy)
There really isn’t any fix needed. It is just an informational message. The system is reserving performance counters for system management and the kernel wants to own all the performance counters regardless. You can disable the ProLiant management features if you really want to stop the message. Other than printing the message during boot, there isn’t any negative impact on the system or performance.

Friday, January 6, 2017

#Barman #2.1 #Released

A new minor version of Barman has been released, introducing the following features:
  • Add --archive and --archive-timeout options to switch-xlog command
  • Preliminary support for PostgreSQL 10 
  • Add last archived WAL info to 'diagnose' output
  • Add start time and execution time to the output of 'delete' command

It also fixes a few minor bugs (see below):
  • Return failure for 'get-wal' command on inactive server
  • Make 'streaming_archiver_names' and 'streaming_backup_name' options global 
  • Fix rsync failures due to files truncated during transfer 
  • Correctly handle compressed history files 
  • Avoid de-referencing symlinks in 'pg_tblspc' when preparing recovery
  • Fix comparison of last archiving failure
  • Avoid failing recovery if postgresql.conf is not writable
  • Fix output of 'replication-status' command 
  • Exclude files from backups like pg_basebackup
  • Exclude directories from other Postgres versions while copying tablespaces