Replication slots is a new feature of PostgreSQL 9.4 that has been introduced by this commit:

commit 858ec11858a914d4c380971985709b6d6b7dd6fc
Author: Robert Haas <rhaas@postgresql.org>
Date:   Fri Jan 31 22:45:17 2014 -0500

Introduce replication slots.

Replication slots are a crash-safe data structure which can be created
on either a master or a standby to prevent premature removal of
write-ahead log segments needed by a standby, as well as (with
hot_standby_feedback=on) pruning of tuples whose removal would cause
replication conflicts.  Slots have some advantages over existing
techniques, as explained in the documentation.

In a few places, we refer to the type of replication slots introduced
by this patch as "physical" slots, because forthcoming patches for
logical decoding will also have slots, but with somewhat different
properties.

Andres Freund and Robert Haas

This feature has been designed to be part of a set for the support of logical replication, but it can be used independently to force a master server to keep WAL files that are still needed by a standby node (note that for more theorical details, you can refer as well to this blog post by Craig Ringer, here the focus is made more on how to use it). There are two types of replication slots: logical and physical. Logical slots have the property to be able to connect to a particular database, while physical slots are more general. I'll come back in more details to logical decoding in some future posts as well as there is much to tell about that, for now let's talk only about physical replication slots. One property that both share is the conservation of WAL files though.

In the case of a standby node using streaming replication, the server does not actually wait for the slave to catch up if it disconnects and simply deletes the WAL files that are not needed. This has the advantage to facilitate management of the disk space used by WAL files: use checkpoint_segments as well in this case. The amount of WAL to keep on master side can as well be tuned

[continue reading]

I’ll be presenting “The Worst Day of Your Life” (dealing with major PostgreSQL disasters) at Nordic PostgreSQL Day 2014. My first trip to Scandinavia!

 IMHO, the problem is simpler than that: no single process should
 be allowed to completely screw over every other process on the
 system.  When the checkpointer process starts calling fsync(), the
 system begins writing out the data that needs to be fsync()'d so
 aggressively that service times for I/O requests from other process
 go through the roof.  It's difficult for me to imagine that any
 application on any I/O scheduler is ever happy with that behavior.
 We shouldn't need to sprinkle of fsync() calls with special magic
 juju sauce that says "hey, when you do this, could you try to avoid
 causing the rest of the system to COMPLETELY GRIND TO A HALT?".
 That should be the *default* behavior, if not the *only* behavior. 

root@plug01:/backup# blockdev --getsize64 /dev/sdd2
2999557554176
root@plug01:/backup# blockdev --getsize64 /dev/sdc2
2999557554176
root@plug01:/backup# blockdev --getsize64 /dev/sdb2
2999557554176

[continue reading]

.pg_ctl -c -D <Data directory location>

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

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

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

[continue reading]

Changed the definition of "JSON text" so that it can be any JSON value, removing the constraint that it be an object or array.

An object whose names are all unique is interoperable in the sense that all software implementations receiving that object will agree on the name-value mappings. When the names within an object are not unique, the behavior of software that receives such an object is unpredictable. Many implementations report the last name/value pair only. Other implementations report an error or fail to parse the object, and some implementations report all of the name/value pairs, including duplicates.

JSON parsing libraries have been observed to differ as to whether or not they make the ordering of object members visible to calling software. Implementations whose behavior does not depend on member ordering will be interoperable in the sense that they will not be affected by these differences.

When: 7-9pm Thu Mar 20, 2014
Where: Iovation
Who: Mark Wong
What: An Adventure in Data Modeling: The Entity-Attribute-Value Model

Emma, Inc. is an email marketing company that provides a Web based
application for managing email lists and marketing campaigns. This is a
tale about Emma’s experience with a database schema design refactor that
is now using the Entity-Attribute-Value (EAV) data model on the members
in email lists. We will briefly describe the previous data model and
why we had to move to a new one. This is followed by all the places
where Emma stumbled and recovered in implementing the EAV data model and
rebuilding the application around it. Finally we will touch on where
Emma might go next with respect to the data.

Mark is a Data Architect at Emma, Inc. and works with their PostgreSQL
database systems.

–

Our meeting will be held at Iovation, on the 32nd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines. Underground bike parking is available in the parking garage; outdoors all around the block in the usual spots. No bikes in the office, sorry!

Building security will close access to the floor at 7:30.

See you there!

The Ad

[continue reading]

When using COPY, there is an option called FORCE_NOT_NULL allowing to enforce a string to be not null even if it is not quoted. Here is an example of how it works:

=# CREATE TABLE aa (a text);
CREATE TABLE
=# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(a));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>> 
>> \.
=# \pset null 'null'
Null display (null) is "null".
=# SELECT * FROM aa;
 a 
---


(2 rows)

In this case, even if the values are empty, FORCE_NOT_NULL forces them to be inserted as empty strings and not NULL values. Now, let's look at the new option FORCE_NULL that is introduced in Postgres 9.4 by this commit, feature actually written by Ian, hacker living in Japan as well as the author of this blog.

commit 3b5e03dca2afea7a2c12dbc8605175d0568b5555
Author: Andrew Dunstan <andrew@dunslane.net>
Date:   Tue Mar 4 17:31:59 2014 -0500

Provide a FORCE NULL option to COPY in CSV mode.

This forces an input field containing the quoted null string to be
returned as a NULL. Without this option, only unquoted null strings
behave this way. This helps where some CSV producers insist on quoting
every field, whether or not it is needed. The option takes a list of
fields, and only applies to those columns. There is an equivalent
column-level option added to file_fdw.

Ian Barwick, with some tweaking by Andrew Dunstan, reviewed by Payal
Singh.

Contrary to FORCE_NOT_NULL, this can be used to force an empty value to be inserted as NULL even if it is quoted.

=# TRUNCATE aa;
TRUNCATE TABLE
=# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NULL(a));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>> 
>> \.
=# SELECT * FROM aa;
  a   
------
 null
 null
(2 rows)

file_fdw comes up as well with a similar new option, and it can be used on columns.

=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
=# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
=# CREATE FOREIGN TABLE t

[continue reading]

Version 2.0RC2 of repmgr, Replication Manager for PostgreSQL clusters, has been released. This release introduces a new experimental feature, autofailover. With autofailover repmgr is able to automatically promote a standby and let the other standbys follow the new master, without interaction of the DBA. It also adds a lot of bug fixes and several new features. Read the full announcement at http://repmgr.org/release-notes.html.

About repmgr

repmgr is a set of open source tools that helps DBAs and System administrators manage a cluster of PostgreSQL databases.

By taking advantage of the Hot Standby capability introduced in PostgreSQL 9, repmgr greatly simplifies the process of setting up and managing database with high availability and scalability requirements.

repmgr simplifies administration and daily management, enhances productivity and reduces the overall costs of a PostgreSQL cluster by:

• monitoring the replication process;
• allowing DBAs to issue high availability operations such as switch-overs and fail-overs

repmgr is production quality software and used widely across the world
with PostgreSQL. Many users rely on repmgr to maintain their
replication setups and as such we take new releases seriously and mark
the level of maturity as a guide for users. As repmgr 2.0 moves
towards production we continue to regard the autofailover feature as
currently in beta; we expect that to fully mature in the 2.1 release.

2ndQuadrant provides contract support for PostgreSQL that includes
both repmgr and the autofailover feature.

The PostgreSQL Project is yet again participating in Google Summer of Code for 2014. Google will be funding students who take part at $5,500 USD per student (see GSoC FAQ for more details). We would like to hear from students who would be willing to work on projects to add new or enhance existing features. You won't be going it alone, we'll assign experienced community developers to mentor you throughout your project.

Have a look at the TODO list on the wiki for inspiration, or the list of project ideas submitted so far by the community, although these are by no means what projects are limited to. Whether you've spotted something you think you could work on, or have a new idea to propose, submit it to the pgsql-students mailing list for discussion (see the mailing list page to subscribe) but do it soon as there's less than 3 weeks to register final proposals.

Also, if you know of any students who may be interested in participating, please send them our way.

More information can be found on the PostgreSQL GSoC page

I just attended and presented at ConFoo, where I always learn new things. This year, the best talk I attended was by our own Magnus Hagander. Called Integrated Cache Invalidation for Better Hit Rates, it addresses the difficult task of controlling web page cache refresh by providing a reliable mechanism for invalidating the cache when the database changes. It shows how to use triggers and pgq to communicate changes to a Varnish web frontend.

Caching in front of a database is often risky. However, with triggers, pgq, and Varnish, proper invalidation can be accomplished, dramatically increasing the scalability of database-backed websites.

"mongodb all the way. full web scale power. i think mongodb should be written in nodejs to take advantage of event driven nature of fast web scale loop. and gruntjs and lessc and uglifyjs adds full power to angulrjs real time web scale RDBMS."

One of the changes in the recently released DBD::Pg version 3 (in addition to the big utf8 change), is the addition of a new attribute, pg_switch_prepared. This accompanies a behavior change in the use of prepare/execute. DBD::Pg will now postpone creating a server-side PREPARE statement until the second time a query is run via the execute() method.

Technically, DBD::Pg will use PQexecParams (part of the underlying libpq system that DBD::Pg uses) the first time a statement is executed, and switch to using PQexecPrepared the second time the statement is executed (by calling PQprepare first). When it actually switches is controlled by the pg_switch_prepared attribute, which defaults to 2 (the behavior above). You can set it to 0 or 1 to always use PQexecPrepared (as the older versions did), or you can set it to -1 to always use PQexecParams and avoid creating prepared statements entirely.

The typical flow of events in a DBI script is to create a statement handle via the prepare() method, then call the execute() time with varying arguments as many times as needed.

#!perl

use strict;
use warnings;
use DBI;

my $DSN = 'DBI:Pg:dbname=postgres';
my $dbh = DBI->connect($DSN, '', '', {AutoCommit=>0,RaiseError=>1,PrintError=>0})
  or die "Connection failed!\n";
print "DBI is version $DBI::VERSION, DBD::Pg is version $DBD::Pg::VERSION\n";

## We do this so we can see the version number in the logs
my $SQL = 'SELECT ?::text';
$dbh->do($SQL, undef, "DBD::Pg version $DBD::Pg::VERSION");

my $sth = $dbh->prepare('SELECT count(*) FROM pg_class WHERE relname = ?');
$sth->execute('foobar1');
$sth->execute('foobar2');
$sth->execute('foobar3');

When the script above is run on DBD::Pg versions 2.19.1 and 3.0.0, you can see the difference:

LOG:  execute <unnamed>: SELECT $1::text
DETAIL:  parameters: $1 = 'DBD::Pg version 2.19.1'
LOG:  execute dbdpg_p30462_1: SELECT count(*) FROM pg_class WHERE relname = $1
DETAIL:  parameters: $1 = 'foobar1'
LOG:  execute dbdpg_p30462_1: SELECT count(*) FROM pg_class WHERE relname = $1
DETAI

[continue reading]

• The original PostgreSQL 9.0 Replication shirt
• The PostgreSQL 9.3 shirt 
• The PostgreSQL 9.2 poster
• Even a PostgreSQL 9.3 laptop sleeve

Just a quick reminder that Dallas/Fort Worth PostgreSQL Users Group has a Meetup next week.

What: Talk about PostgreSQL and column based data storage

When: Wednesday, March 5, 2014 7:00 PM

Where:
Improving Enterprises
16633 Dallas Parkway Suite 110 Addison, TX 75001

DFW PUG on Meetup

We had our Streaming Rep Lab followup last night¹. It was mostly successful: half of the new people got it up & running, and the people who’d been at the previous workshop got to experiment with almost everything on the agenda.

The cheatsheet is up on our git repo: https://github.com/pdxpug/cheatsheets ².

We talked a bit about monitoring and messed around with some of the available functions (e.g. pg_last_xact_replay_timestamp), started looking at pg_xlogdump (for “educational purposes”, of course), checked out a packet capture between the master & standby, and finished up with breaking the link between the master & the standby: the standby cried a bit about not being able to reach the master, but as soon as we restored the link, everything was fine.

The overarching theme of the evening is that we need to come up with a more robust plot to make Postgres fail in an interesting way.

SELECT pg_last_xact_replay_timestamp(); will give you a WAL ID, but is only valid on a standby.
The function to translate it to a file name: SELECT pg_xlogfile_name([id]); can only be run on the master. So that’s kinda weird and requires some puzzling out.

I started a wiki page of ideas for future labs, but we may be hung up on streaming rep & breaking it for a little while

Thanks to Emma for hosting & providing dinner!

–

1 – With a puppy.
2 – Yes, I know there is something wrong with the step numbering. It renders correctly in github’s md validator, but not when I upload it.