9.2 | Michael Paquier

Postgres 9.3 feature highlight: timeline switch of slave node without archives

PostgreSQL Comments Off

Dec 252012

Since PostgreSQL 9.1, it is possible to switch a standby server to follow another server that has been freshly promoted after the master node of a cluster is out due to a failure (disaster or another).
This can be summarized by the schema below.

Timeline switch schema

In this example, a master and two slaves are running on the same machine. The master running with port 5432 fails, Slave 1 is promoted as the new master:
pg_ctl promote -D $SLAVE1_DATA
As the master is not accessible, Slave 2 is unable to keep pace in the cluster. So you need to update the following parameters of recovery.conf of Slave 2 as follows to reconnect it to the new master (which is now Slave 1):

primary_conninfo = 'host=localhost port=5532 application_name=slave2'

recovery_target_time = 'latest'

Finally restart Slave 2 to continue recovery from the new master.
pg_ctl restart -D $SLAVE2_DATA

When doing this in PostgreSQL 9.1 and 9.2, you need a WAL archive (archive_mode = ‘on’ on all servers) to allow a standby to recover WAL files that are missing in order to complete the timeline change (please note that you can also copy the WAL files from the slave node directly). If no archive is available, a standby trying to reconnect to a promoted node will stop its recovery with those types of errors due to missing WAL information:
FATAL: timeline 2 of the primary does not match recovery target timeline 1
This can only be solved by copying the WAL segments from the master node or using a WAL archive.

However, in order to make Postgres cluster management far more flexible, the following feature has been developed for 9.3:
commit abfd192b1b5ba5216ac4b1f31dcd553106304b19 Author: Heikki Linnakangas Date: Thu Dec 13 19:00:00 2012 +0200 　 Allow a streaming replication standby to follow a timeline switch. 　 Before this patch, streaming replication would refuse to start replicating if the timeline in the primary doesn't exactly match the standby. The situation where it doesn't match is when you have a master, and two standbys, and you promote one of the standbys to become new master. Promoting bumps up the timeline ID, and after that bump, the other standby would refuse to continue. 　 There's significantly more timeline related logic in streaming replication now. First of all, when a standby connects to primary, it will ask the primary for any timeline history files that are missing from the standby. The missing files are sent using a new replication command TIMELINE_HISTORY, and stored in standby's pg_xlog directory. Using the timeline history files, the standby can follow the latest timeline present in the primary (recovery_target_timeline='latest'), just as it can follow new timelines appearing in an archive directory. 　 START_REPLICATION now takes a TIMELINE parameter, to specify exactly which timeline to stream WAL from. This allows the standby to request the primary to send over WAL that precedes the promotion. The replication protocol is changed slightly (in a backwards-compatible way although there's little hope of streaming replication working across major versions anyway), to allow replication to stop when the end of timeline reached, putting the walsender back into accepting a replication command. 　 Many thanks to Amit Kapila for testing and reviewing various versions of this patch.
This feature allows to switch to the latest timeline on a standby server just by using streaming replication, a WAL archive becoming non-mandatory (archive_mode = ‘off’ on all servers). In order to complete that, a new streaming replication command called TIMELINE_HISTORY has been created, which makes the standby recover all the missing timeline history files from the node it connects to, facilitating the switch to the latest timeline available.

When timeline history files are requested from another node, the following things are logged:
LOG: fetching timeline history file for timeline 2 from primary server LOG: started streaming WAL from primary at 0/5000000 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: restarted WAL streaming at 0/5000000 on timeline 2

Removing the obligation to use WAL archives really brings more flexibility in a PostgreSQL cluster, making this feature a non-negligible must-have, especially when thinking about cascading nodes. However, having that does not mean that you should bypass the use of WAL archives, so be sure to tune your system depending on the needs of your applications.

Postgres FDW highlight: redis_fdw

PostgreSQL, redis 1 Response »

Nov 282012

A foreign-data wrapper (FWD) in a Postgres server allows to fetch data from an foreign entity or a foreign server. In this case, the Postgres planner and executer have the notion of what is called a foreign scan, which can be called using customized routines and fetch data that is not directly stored inside the Postgres server itself.

The core code of Postgres includes one FDW which is fdw_file, postgres_fdw is planned to be also included at some point (9.3 discussions).

The installation of a FDW can be done since PostgreSQL 9.1 with the use of CREATE EXTENSION. There are many existing FDW modules that are developed and maintained by the community. Among some of them are:

oracle_fdw, to fetch data from an Oracle server
mysql_fdw, to fetch data from a MySQL server
pgsql_fdw (or sometimes postgres_fdw), to fetch data from another Postgres server
twitter_fdw, to fetch data from a Twitter server

Note: Once I thought about a git FDW as git is itself a NoSQL database managing concurrency of commits and branches its own way… But got no time to design or code it.

By the way, the FDW this post is focused on is called redis_fdw, which allows to fetch data from a foreign Redis server and materialize it directly on Postgres side. Before continuing reading this post, be sure that you already have running a Redis server and a Postgres server.
Here both Redis and Postgres server run on a local machine with respectively 6379 and 5432 as port numbers (default values).

Then it is time to install redis_fdw. First fetch the code.
mkdir $REDIS_SRC cd $REDIS_SRC git init git remote add origin https://github.com/dpage/redis_fdw.git git fetch origin git checkout master

Then install it. Please note that the current version of the code is not compilable with Postgres 9.2 and upper versions, so for this post the Postgres server is 9.1.X.
make install USE_PGXS=1
This will add redis_fdw.so in folder lib of pgsql install folder and redis_fdw.control and redis_fdw–1.0.sql in share/extension.
Then finalize installation on the server by using CREATE EXTENSION.
postgres=# CREATE EXTENSION redis_fdw; CREATE EXTENSION postgres=# \dx redis_fdw List of installed extensions Name | Version | Schema | Description -----------+---------+--------+-------------------------------------------------- redis_fdw | 1.0 | public | Foreign data wrapper for querying a Redis server (1 row)

Then create the foreign server, its attached foreign table and a user mapping for remote connectivity (you can also refer to the redis_fdw README for additional details).
postgres=# CREATE SERVER redis_server postgres-# FOREIGN DATA WRAPPER redis_fdw postgres-# OPTIONS (address '127.0.0.1', port '6379'); CREATE SERVER postgres=# postgres=# CREATE FOREIGN TABLE redis_db0 (key text, value text) postgres-# SERVER redis_server postgres-# OPTIONS (database '0'); CREATE FOREIGN TABLE postgres=# CREATE USER MAPPING FOR PUBLIC postgres-# SERVER redis_server postgres-# OPTIONS (password ''); CREATE USER MAPPING

On the Redis server side, let’s add a couple of keys with some values.
# redis-cli redis 127.0.0.1:6379> set foo bar OK redis 127.0.0.1:6379> set foo2 bar2 OK

Finally it is possible to query the Redis data directly by connecting on Postgres.
postgres=# EXPLAIN VERBOSE SELECT * FROM redis_db0 WHERE key = 'foo2' OR key = 'foo'; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public.redis_db0 (cost=10.00..12.00 rows=2 width=64) Output: key, value Filter: ((redis_db0.key = 'foo2'::text) OR (redis_db0.key = 'foo'::text)) Foreign Redis Database Size: 2 (4 rows) postgres=# SELECT * FROM redis_db0 WHERE key = 'foo2' OR key = 'foo'; key | value ------+------- foo | bar foo2 | bar2 (2 rows)
And the set of key/values defined on Redis side have been fetched correctly.

Please note that redis_fdw code should not yet be used for production environment, I found for example that it crashes when the EXPLAIN query above is launched two times in a row. However, I think it is a good entry point to understand the possible Redis/Postgres interactions. It would also be worth stabilizing it and realigning it with Postgres master core code at some point.

Postgres feature highlight: pg_buffercache

PostgreSQL No Responses »

Nov 272012

pg_buffercache is a PostgreSQL contrib module allowing to get an instant relation-based view of the shared buffer usage by querying the wanted server.

This can be pretty useful for performance analysis of queries on a given relation as it allows to have a look at how much a relation is cached. In the case of data cached for a given relation, you do not need to access data directly on disk to retrieve the data and can directly rely on the cache, so the data fetching is simply faster, by a factor of the order of 1000 (Shared memory/disk speed difference). Take care however that a shared lock is taken when analyzing the shared buffer content, so it can impact concurrent queries.

In order to install pg_buffercache from source code, you need to perform the following commands to install its related files.
cd contrib/pg_buffercache make install
Depending on your environment and the Postgres packages you installed, you might not need to do that of course. Once this is done the following files are installed in $INSTALL_FOLDER/share/extension.
ls $INSTALL_FOLDER/share/extension pg_buffercache--1.0.sql pg_buffercache.control pg_buffercache--unpackaged--1.0.sql

Then connect to your Postgres server and finish pg_buffercache installation with CREATE EXTENSION command.
postgres=# CREATE EXTENSION pg_buffercache; CREATE EXTENSION postgres=# \dx pg_buffercache List of installed extensions Name | Version | Schema | Description ----------------+---------+--------+--------------------------------- pg_buffercache | 1.0 | public | examine the shared buffer cache (1 row)

The view created after installation of the extension called pg_buffercache has several columns.

bufferid, the block ID in the server buffer cache
relfilenode, which is the folder name where data is located for relation
reltablespace, Oid of the tablespace relation uses
reldatabase, Oid of database where location is located
relforknumber, fork number within the relation
relblocknumber, age number within the relation
isdirty, true if the page is dirty
usagecount, page LRU (least-recently used) count

The buffer ID corresponds (surprisingly!) to the number of the buffer used by the relation. The total number of buffers available is defined by two things:

Size of a buffer block, this is defined by the option –with-blocksize when running configure. Default value if 8kB, which is sufficient in most of the situations, but you can go up to 32kB or down to 1kB depending on the situations. In order to change this value, it is necessary to recompile the code and rebuild a database server from the initdb step.
Number of shared buffer allocated for the system defined by shared_buffers in postgresql.conf. This can be changed at will by restarting the server.

For example, by using 128MB of shared_buffers with 8kB of block size, there are 16,384 buffers, so pg_buffercache has the same number of 16,384 rows.
With shared_buffers set at 256MB and block-size at 1kB, there are 262,144 buffers.

Let’s have a quick look at the feature with a pgbench database that has been already used with a 5-minute test. This simple query (given by the documentation) provides the number of buffers used by each relation of the current database.
postgres=# SELECT c.relname, count(*) AS buffers postgres=# FROM pg_buffercache b INNER JOIN pg_class c postgres=# ON b.relfilenode = pg_relation_filenode(c.oid) AND postgres=# b.reldatabase IN (0, (SELECT oid FROM pg_database postgres=# WHERE datname = current_database())) postgres=# GROUP BY c.relname postgres=# ORDER BY 2 DESC postgres=# LIMIT 10; relname | buffers -----------------------+--------- pgbench_history | 2515 pgbench_accounts | 1818 pgbench_accounts_pkey | 276 pgbench_tellers | 61 pgbench_branches | 61 pg_attribute | 22 pg_statistic | 11 pg_proc | 10 pg_class | 8 pg_proc_oid_index | 8 (10 rows)
Not only relation data, but also indexes are included in the image given. The server of this example used an amount of shared_buffer of 128MB, so all the relation data was completely cached.
The usage count of each buffer page is a good indication of how many times a buffer is used after being created. In case it is low, it means that the buffers do not survive a long time and that the cache hit ratio is low.

Postgres feature highlight: hstore

PostgreSQL 5 Responses »

Oct 222012

hstore is a PostgreSQL contrib module in core code for a pretty long time. Its code is located in contrib/hstore in source folder. It is particularly useful to store sets of key/value in a single table column.

Since Postgres 9.1, its installation needs to be done in two phases.
First install the hstore library, here done from the source code. Please note that your postgres package normally already contains it.
So after downloading the source code and installing the core, do the following commands.
cd $PG_SOURCE_ROOT cd contrib/hstore make install
At this point all the libraries and files related to hstore are installaed in $INSTALL_FOLDER/share/extension.
ls $INSTALL_FOLDER/share/extension hstore--1.0--1.1.sql hstore--1.1.sql hstore--unpackaged--1.0.sql hstore.control

Then connect to your Postgres server and finish hstore installation with CREATE EXTENSION command.
postgres=# CREATE EXTENSION hstore; CREATE EXTENSION postgres=# \dx hstore List of installed extensions Name | Version | Schema | Description --------+---------+--------+-------------------------------------------------- hstore | 1.1 | public | data type for storing sets of (key, value) pairs (1 row)
With a psql client, ‘\dx’ allows to check the list of extensions already installed on your server.

A new column type called hstore has been added. This is the column used to store the list of key/value pairs for the table.
Let’s take a table referencing a list of products as an example.
postgres=# create table products (id serial, characs hstore); CREATE TABLE
The insertion of data can be done with several methods. Here are some of them.
postgres=# -- common insertion postgres=# INSERT INTO products(characs) VALUES ('author=>Dave, date=>"Dec 2012", price=>"500", currency=>"dollar"'); INSERT 0 1 postgres=# -- array-based insertion postgres=# INSERT INTO products (characs) VALUES (hstore(array['author','date','stock'],array['Mike','Nov 2012','200'])); INSERT 0 1 postgres=# -- single-pair insertion postgres=# INSERT INTO products (characs) VALUES (hstore('author','Kim')); -- single-element INSERT 0 1 postgres=# SELECT * FROM products; id | characs ----+---------------------------------------------------------------------------- 1 | "date"=>"Dec 2012", "price"=>"500", "author"=>"Dave", "currency"=>"dollar" 2 | "date"=>"Nov 2012", "stock"=>"200", "author"=>"Mike" 3 | "author"=>"Kim" (3 rows)

Based on the existing fields, it is also possible to add or update values for a given key using a concatenate-based method.
Here is the update of a key “author”.
postgres=# UPDATE products SET characs = characs || 'author=>Sarah'::hstore where id = 1; UPDATE 1 postgres=# select * from products where id = 1; id | characs ----+----------------------------------------------------------------------------- 1 | "date"=>"Dec 2012", "price"=>"500", "author"=>"Sarah", "currency"=>"dollar" (1 row)
If the key updated is not present in existing list, it is simply added as a new element.

You can also delete single elements.
postgres=# UPDATE products SET characs = delete(characs,'price') where id = 1; UPDATE 1 postgres=# select * from products where id = 1; id | characs ----+------------------------------------------------------------- 1 | "date"=>"Dec 2012", "author"=>"Sarah", "currency"=>"dollar" (1 row)

SELECT query can also use key-based scan. Here for instance this query looks for the products with less than 300 stocks.
postgres=# SELECT id FROM products WHERE (characs->'stock')::int <= 300; id ---- 2 (1 row)
(OK, not the best way of doing for your application but this is just a scholar example!).

On top of that, hstore also supports gin and gist indexes for the operators @>, ?, ?& and ?, as well as btree and hash indexes for '='.
postgres=# create index products_index on products(characs); CREATE INDEX

Hope this gives a good introduction to hstore.

Postgres 9.2 highlight: JSON data type

PostgreSQL 4 Responses »

Oct 152012

PostgreSQL 9.2 has introduced a new feature related to JSON with a built-in data type. So you can now store inside your database directly JSON fields without the need of an external format checker as it is now directly inside Postgres core. The feature has been added by this commit.
commit 5384a73f98d9829725186a7b65bf4f8adb3cfaf1 Author: Robert Haas Date: Tue Jan 31 11:48:23 2012 -0500 　 Built-in JSON data type. 　 Like the XML data type, we simply store JSON data as text, after checking that it is valid. More complex operations such as canonicalization and comparison may come later, but this is enough for now. 　 There are a few open issues here, such as whether we should attempt to detect UTF-8 surrogate pairs represented as \uXXXX\uYYYY, but this gets the basic framework in place.

A couple of system functions have also been added later to output some row or array data directly as json.
commit 39909d1d39ae57c3a655fc7010e394e26b90fec9 Author: Andrew Dunstan Date: Fri Feb 3 12:11:16 2012 -0500 　 Add array_to_json and row_to_json functions. 　 Also move the escape_json function from explain.c to json.c where it seems to belong. 　 Andrew Dunstan, Reviewed by Abhijit Menon-Sen.

What actually Postgres core does with JSON fields is to store them as text fields (so maximum size of 1GB) and top of that a string format check can be performed directly in core. Let’s use that in a practical use case, a table storing a list of shop items for an RPG game. In an RPG game, there are several types of items, each of them having different fields for its statistics. For example a sword will have an attack value, and a shield a defense value, the opposite being unlogical (except if the sword has a magical defense bonus and the shield some fire protection for example…). Well, what I meant is that you do not need to create multiple tables for each item type but you can possibly store this data in a unique item table thanks to the flexibility of JSON. With the format check done now in Postgres core, you do not need either to perform the string format check on application side.

postgres=# CREATE TABLE rpg_items (c1 serial, data json); CREATE TABLE postgres=# INSERT INTO rpg_items (data) VALUES postgres-# ('{"name":"sword","buy":"500","sell":"200","description":"basic sword","attack":"10"}'); INSERT 0 1 postgres=# INSERT INTO rpg_items (data) VALUES postgres-# ('{"name":"shield","buy":"200","sell":"80","description":"basic shield","defense":"7"}'); INSERT 0 1 postgres=# SELECT * FROM rpg_items; c1 | data ----+-------------------------------------------------------------------------------------- 1 | {"name":"sword","buy":"500","sell":"200","description":"basic sword","attack":"10"} 2 | {"name":"shield","buy":"200","sell":"80","description":"basic shield","defense":"7"} (2 rows)

In case of a format error you will obtain something similar to this:
postgres=# INSERT INTO rpg_items (data) VALUES ('{"name":"dummy","buy":"200","ppo"}'); ERROR: invalid input syntax for type json LINE 1: INSERT INTO rpg_items (data) VALUES ('{"name":"dummy","buy":...

Then, you can also manipulate existing tables and output its data to client as JSON.
postgres=# CREATE TABLE rpg_items_defense (c1 serial, buy int, sell int, description text, defense int); CREATE TABLE postgres=# INSERT INTO rpg_items_defense (buy, sell,description, defense) postgres-# VALUES (200, 80, 'basic shield', 7); INSERT 0 1 postgres=# SELECT row_to_json(row(buy,sell,description,defense)) FROM rpg_items_defense; row_to_json ----------------------------------------------- {"f1":200,"f2":80,"f3":"basic shield","f4":7} (1 row)
The field names have default values generated automatically by Postgres.

Or output array values as JSON.
postgres=# CREATE TABLE rpg_items_attack(int serial, fields int[], description text); CREATE TABLE postgres=# INSERT INTO rpg_items_attack (fields, description) VALUES postgres-# ('{500,200,10}','basic sword'); INSERT 0 1 postgres=# SELECT row_to_json(row(array_to_json(fields), description)) FROM rpg_items_attack; row_to_json ---------------------------------------- {"f1":[500,200,10],"f2":"basic sword"} (1 row)
This is of course not the only solution possible. Take care of making the good choice when designing your application.

Older Entries

Michael Paquier

Postgres 9.3 feature highlight: timeline switch of slave node without archives

Postgres FDW highlight: redis_fdw

Postgres feature highlight: pg_buffercache

Postgres feature highlight: hstore

Postgres 9.2 highlight: JSON data type

My Tweets

Follow Me

Links

Michael Paquier

Postgres 9.3 feature highlight: timeline switch of slave node without archives

Postgres FDW highlight: redis_fdw

Postgres feature highlight: pg_buffercache

Postgres feature highlight: hstore

Postgres 9.2 highlight: JSON data type

My Tweets

Follow Me

Links

Tags