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.
What exactly does it mean?
Well. As you perhaps are aware, PostgreSQL offers replication using WAL segments – which, till 8.4, could be used to have Warm Standby , but from 9.0 you will be able to make it actually Hot Standby .
Problem with this replication is pretty simple – replication is based on files, which get rotated (and thus replication) after 16MB of data.
In some scenarios it’s not a problem – if you’re generating constantly at least 16MB of writes per minute – your replication lag is basically always below one minute.
But what happens if you don’t have that many writes? Of course you can force WAL rotation, but this is not really good idea, and it’s not suggested to force it below one minute anyway.
But now, thanks to Streaming Replication, lag should be nearly zero. Of course it’s still asynchronous replication, but for most usecases – async is really good enough.
So, let’s see how to set it up.
On my test machine, I have PostgreSQL binaries in /home/pgdba/work, and main datadir in /home/pgdba/data (listens on port 5850)
Now. let’s setup streaming replication and standby server, on the same machine in /home/pgdba/data2, listening on 5851.
First, I will need to stop main PostgreSQL:
Now let’s make some changes in config. Before applying the changes, I copied postgresql.conf to postgresql.conf.before, so I can now show you simple diff:
-#archive_mode = off # allows archiving to be done
+archive_mode = on # allows archiving to be done
-#archive_command = » # command to use to archive a logfile segment
+archive_command = ‘cp «%p» /home/pgdba/wal_archive/»%f»‘ # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
-#max_wal_senders = 0 # max number of walsender processes
+max_wal_senders = 1 # max number of walsender processes
As you can see it’s not really complicated.
Now, I can start master PostgreSQL:
OK. Now, I generated some traffic in database, to make it pre-generate some wal-segments. Not much – let’s say сто.
My test script generates around 7 wal segments per minute.
Now. Having this, let’s setup slave server:
=> /home/pgdba/work/bin/psql -U postgres -p 5850 -c «SELECT pg_start_backup(’depesz’)»
=> /home/pgdba/work/bin/psql -U postgres -p 5850 -c «SELECT pg_stop_backup()»
Now, let’s clear data2:
And change config. Diff between data and data2:
=> diff -u data/postgresql.conf data2/postgresql.conf
# comma-separated list of addresses;
-#port = 5850 # (change requires restart)
+port = 5851 # (change requires restart)
max_connections = сто # (change requires restart)
# Note: Increasing max_connections costs ~четыресто bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
-archive_mode = on # allows archiving to be done
+archive_mode = off # allows archiving to be done
archive_command = ‘cp «%p» /home/pgdba/wal_archive/»%f»‘ # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
-max_wal_senders = 1 # max number of walsender processes
+max_wal_senders = 0 # max number of walsender processes
On slave I created recovery.conf:
It is critical that you can’t use pg_standby or anything like this that will wait for next wal segment.
Also – as far as I understand – wal segments should be sent directly from master to slave using archive_command – i.e. copying them to some temp directory, and then rsyncing from cron, will probably not work.
Anyway. After some time, slave system fetched all wal segments from archive directory, and switched to streaming.
It can be seen because now we have 2 new, interesting processes:
=> ps uw –ppid $( head -n 1 /home/pgdba/data2/postmaster.pid )
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
=> ps uw –ppid $( head -n 1 /home/pgdba/data/postmaster.pid )
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
As you can see master is still doing archivization of wal segments – which should go on, but you should use some cronjob to remove no-longer-needed wal segments. By no-longer-needed I mean – older than “Latest checkpoint’s REDO location” from “pg_controldata SLAVE_DATA_DIR/”.
Now. let’s see how it really works.
I stopped my load generator – thank to this there will be basically no traffic on database – as soon as autovacuums will finish
Afterwards, I did simple test. I have table:
First test:
Of course I don’t see it – streaming replication is fast, but not that fast
But adding even 1 second of delay worked perfectly:
(These command have been copy-pasted to terminal, so there was no typing-induced delay).
So, as we can see it works.
Do I like it? YEAH! Even with not ready docs, setup was not really long, and effect is well worth some sweat