I have PostgreSQL, loaded some data, and have app using it. Now what?

I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.

What to use, how, and why? This is what this post is all about.

Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?

Waiting for 9.3 – Allow a streaming replication standby to follow a timeline switch.

On 13th of December, Heikki Linnakangas committed patch:

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.

Continue reading Waiting for 9.3 – Allow a streaming replication standby to follow a timeline switch.

Waiting for 9.2 – cascading streaming replication

On 19th of July, Simon Riggs committed patch:

Cascading replication feature FOR streaming log-based replication.
Standby servers can now have WALSender processes, which can WORK WITH
either WALReceiver OR archive_commands TO pass DATA. Fully updated
docs, including NEW conceptual terms OF sending server, upstream AND
downstream servers. WALSenders TERMINATED WHEN promote TO master.
 
Fujii Masao, review, rework AND doc rewrite BY Simon Riggs

Continue reading Waiting for 9.2 – cascading streaming replication

Waiting for 9.1 – Synchronous replication

On 6th of March, Simon Riggs committed patch:

Efficient transaction-controlled synchronous replication.
If a standby is broadcasting reply messages and we have named
one or more standbys in synchronous_standby_names then allow
users who set synchronous_replication to wait for commit, which
then provides strict data integrity guarantees. Design avoids
sending and receiving transaction state information so minimises
bookkeeping overheads. We synchronize with the highest priority
standby that is connected and ready to synchronize. Other standbys
can be defined to takeover in case of standby failure.
 
This version has very strict behaviour; more relaxed options
may be added at a later date.
 
Simon Riggs and Fujii Masao, with reviews by Yeb Havinga, Jaime
Casanova, Heikki Linnakangas and Robert Haas, plus the assistance
of many other design reviewers.

Continue reading Waiting for 9.1 – Synchronous replication

OmniPITR – update

As of yesterday OmniPITR got following changes/fixes:

  1. Fixed bug which caused immediate finish request be treated the same as smart finish request.
  2. Fixed problem with using omnipitr-backup-slave on PostgreSQL 9.0 slave, which is using streaming replication.
  3. Added option to omnipitr-restore, so that you can now use it for streaming-replication slaves

I'm very ashamed of the first thing (smart/immediate finish request), as it was simply my lack of test.

Second thing – the problem was that with streaming replication slave behaves a bit differently than normally, and so the backup procedure had to be modified.

As for third – restore command in streaming replication environment has to behave differently than normal restore command – i.e. it should finish, with error, as soon as it will be called for wal file that does not exist in wal archive. Which is direct opposite of what should be done normally.

Now, omnipitr-restore got switch (-sr) to make it work correctly in SR situation.

Links for svn/docs are listed on project page.

Waiting for 9.1 – pg_basebackup

On 23rd of January, Magnus Hagander committed patch which adds:

Add pg_basebackup tool for streaming base backups
 
This tool makes it possible to do the pg_start_backup/
copy files/pg_stop_backup step in a single command.
 
There are still some steps to be done before this is a
complete backup solution, such as the ability to stream
the required WAL logs, but it's still usable, and
could do with some buildfarm coverage.
 
In passing, make the checkpoint request optionally
fast instead of hardcoding it.
 
Magnus Hagander, reviewed by Fujii Masao and Dimitri Fontaine

Continue reading Waiting for 9.1 – pg_basebackup

Waiting for 9.0 – Streaming replication

The BIG feature. The feature that made PostgreSQL leap from 8.4 to 9.0. Patch was written by Fujii Masao, and committed by Heikki Linnakangas on 15th of January 2010:

Log Message:
-----------
Introduce Streaming Replication.
 
This includes two new kinds of postmaster processes, walsenders and
walreceiver. Walreceiver is responsible for connecting to the primary server
and streaming WAL to disk, while walsender runs in the primary server and
streams WAL from disk to the client.
 
Documentation still needs work, but the basics are there. We will probably
pull the replication section to a new chapter later on, as well as the
sections describing file-based replication. But let's do that as a separate
patch, so that it's easier to see what has been added/changed. This patch
also adds a new section to the chapter about FE/BE protocol, documenting the
protocol used by walsender/walreceivxer.
 
Bump catalog version because of two new functions,
pg_last_xlog_receive_location() and pg_last_xlog_replay_location(), for
monitoring the progress of replication.
 
Fujii Masao, with additional hacking by me

Continue reading Waiting for 9.0 – Streaming replication