root/trunk/omnipitr/doc/howto.pod

Revision 156, 11.6 kB (checked in by depesz, 4 years ago)

Update copyright info

Line 
1 =head1 OmniPITR - how to setup.
2
3 =head2 OVERVIEW
4
5 Following documentation walks you through installation, setup and running of
6 master-slave replication of PostgreSQL database using WAL segment files (also
7 known as PiTR Replication.
8
9 It will also cover methods of making backups, both from master and slave
10 servers, and methods for monitoring replication status.
11
12 =head2 SETUP OVERVIEW
13
14 Following document provides information about setting replication/backup
15 scenario, on 3 machines, with following responsibilities:
16
17 =over
18
19 =item * master server - server (both hardware and software) that contains the working,
20 writable PostgreSQL instance
21
22 =item * slave server - server (both hardware and software) that contains secondary,
23 warm or hot standby server. With PostgreSQL pre 9.0 it's not readable, but from
24 9.0 you can send read-only queries to it.
25
26 =item * backup server - additional machine that is being used as storage for
27 backup files - both full-database backups and/or backup of currently generated
28 xlog files (WAL segments)
29
30 =back
31
32 Before setting replication it is required that you have PostgreSQL installed on
33 both master and slave servers, and working PostgreSQL cluster on master.
34
35 Paths should be the same on master and slave - at least to data directory.
36
37 For the purpose of example, I assume following paths on each servers - you are
38 in no way obliged to use the same paths - these are just explanations if
39 something wouldn't be clear from examples later on:
40
41 =head3 Master server paths
42
43 =over
44
45 =item * /home/postgres - home directory of postgres user - user that is used to
46 run PostgreSQL
47
48 =item * /home/postgres/data - data directory for PostgreSQL
49
50 =item * /home/postgres/data/postgresql.conf - PostgreSQL configuration file for
51 master
52
53 =item * /var/tmp - temporary directory that can be freely used
54
55 =back
56
57 =head3 Slave server paths
58
59 =over
60
61 =item * /home/postgres - home directory of postgres user - user that is used to
62 run PostgreSQL
63
64 =item * /var/tmp - temporary directory that can be freely used
65
66 =back
67
68 =head3 Backup server paths
69
70 =over
71
72 =item * /var/backups - backups directory - all backups should arrive in here.
73
74 =back
75
76 Also I assume that PostgreSQL itself, and OmniPITR programs work from postgres
77 system account.
78
79 =head2 PREPARATION
80
81 To make everything working we need a way to copy files from master to slave and
82 backup, and from slave to backup.
83
84 Since I<OmniPITR> is using RSync for all transfers, we can effectively use two
85 ways of communication:
86
87 =over
88
89 =item * rsync over ssh
90
91 =item * direct rsync
92
93 =back
94
95 First method is usually easier to setup - as you most likely have ssh daemon on
96 all machines, so it is enough to install rsync program, and you're virtually
97 good to go.
98
99 Second method is a bit more complex, as you need additinally to setup rsync
100 daemon, but it pays off in increased security, faster transfers and less
101 resource-intensive work. The biggest drawback is that data being sent are not
102 encrypted. Which might, or might not be an issue, depending on distance, and
103 trust for connection - i.e. using encrypted vpn and then plain rsync is (in my
104 opinion) favorable over rsync-over-ssh, due to extra security steps we can have
105 when using rsync as daemon.
106
107 In any way, we need to make some extra directories (all this directories should
108 be writable by postgres user, preferably also owned by it):
109
110 =over
111
112 =item * On master server:
113
114 =over
115
116 =item * /home/postgres/omnipitr - this is where omnipitr will store it's
117 internal datafiles (rather small)
118
119 =item * /home/postgres/omnipitr/log - place for omnipitr log
120
121 =item * /home/postgres/omnipitr/state - place for omnipitr state files
122
123 =item * /var/tmp/omnipitr - place for temporary files (larger) created and used
124 by omnipitr
125
126 =back
127
128 =item * On slave server:
129
130 =over
131
132 =item * /home/postgres/wal_archive - this is where master will send xlog
133 segments to be used for replication
134
135 =item * /home/postgres/omnipitr - this is where omnipitr will store it's
136 internal datafiles (rather small)
137
138 =item * /home/postgres/omnipitr/log - place for omnipitr log
139
140 =item * /home/postgres/omnipitr/state - place for omnipitr state files
141
142 =item * /var/tmp/omnipitr - place for temporary files (larger) created and used
143 by omnipitr
144
145 =back
146
147 =item * On backup server:
148
149 =over
150
151 =item * /var/backups/database - top level directory for database backups
152
153 =item * /var/backups/database/hot_backup - directory to put hot backup files in
154
155 =item * /var/backups/database/xlog - directory to put xlog segments in
156
157 =back
158
159 =back
160
161 And then we need to allow uploads to them.
162
163 For this - you'd rather consult your sysadmins. For the sake of this document, I
164 assume that the chosen method was direct rsync, and we have working following
165 rsync paths:
166
167 =over
168
169 =item * rsync://slave/wal_archive/ - points to /home/postgres/wal_archive/, with
170 write access for master, without password
171
172 =item * rsync://backup/database/ - points to /var/backups/database, with write
173 access for master and slave, without password
174
175 =back
176
177 =head2 ACTUAL INSTALLATION AND CONFIGURATION
178
179 On both master and slave machines, please install omnipitr to /opt/omnipitr
180 directory, for example using:
181
182     $ svn co https://labs.omniti.com/pgtreats/trunk/omnipitr /opt/omnipitr
183
184 Now, on both machines, check if the installation is OK, that is run sanity
185 check:
186
187     $ /opt/omnipitr/bin/sanity-check.sh
188     Checking:
189     - /opt/omnipitr/bin
190     - /opt/omnipitr/lib
191     5 programs, 9 libraries.
192     All checked, and looks ok.
193
194 if there are any errors/warnings - do whatever you can to fix them.
195
196 Now. If this works well, we can move on to setting up all subsequent steps.
197
198 =head3 Archival of XLOGs from master
199
200 In /home/postgres/data/postgresql.conf find section "WRITE AHEAD LOG" ->
201 "Archiving". Usually it contains lines like these:
202
203     #archive_mode = off    # allows archiving to be done
204     #archive_command = ''  # command to use to archive a logfile segment
205     #archive_timeout = 0   # force a logfile segment switch after this
206
207 You need to enable archive_mode:
208
209     archive_mode = on
210
211 (in older versions ( pre 8.2 ) there is no archive_mode. It's ok - just don't
212 add it).
213
214 Set archive_command to:
215
216     archive_command = '/opt/omnipitr/bin/omnipitr-archive -l /home/postgres/omnipitr/log/omnipitr-^Y^m^d.log -s /home/postgres/omnipitr/state -dr gzip=rsync://slave/wal_archive/ -dr gzip=rsync://backup/database/xlog/ -db /var/tmp/omnipitr/dstbackup -t /var/tmp/omnipitr/ -v "%p"'
217
218 and archive_timeout to:
219
220     archive_timeout = 60
221
222 This will make sure that in worst case you have 1 minute lag.
223
224 Meaning of options:
225
226 =over
227
228 =item * -l /home/postgres/omnipitr/log/omnipitr-^Y^m^d.log : Path to logfile,
229 will be automatically rotated on date change.
230
231 =item * -s /home/postgres/omnipitr/state : Directory to keep state information,
232 internal stuff, not really interesting, small files only, but it is required if
233 we have more than 1 destination
234
235 =item * -dr gzip=rsync://slave/wal_archive/ : sends gzip compressed wal segments
236 to slave to appropriate path
237
238 =item * -dr gzip=rsync://backup/database/xlog/ : sends the same gzip compressed
239 wal segments to backup server for long term storage
240
241 =item * -db /var/tmp/omnipitr/dstbackup : it is important that this path
242 shouldn't exist - it's a directory, that (if it exists) will be used as
243 additional local destination - for the purposes of omnipitr-backup-master program
244
245 =item * -t /var/tmp/omnipitr/ : where to keep temporary files (when needed)
246
247 =item * -v : log verbose information - mostly timings.
248
249 =back
250
251 Afterwards you need to restart PostgreSQL (or reload if you changed only
252 archive_command and/or archive_timeout).
253
254 Archiving should work nicely now, which you can see after couple of minutes
255 (assuming you set archive_timeout to 60 seconds, like I showed above).
256
257 You should start seeing compressed files showing on slave and backup servers,
258 and appropriate information in logfile.
259
260 =head3 Creation of hot backup on master
261
262 This is actually pretty simple - assuming you have working archiving (described
263 in previous section of this howto.
264
265 You simply run this command:
266
267     /opt/omnipitr/bin/omnipitr-backup-master -D /home/postgres/data -l /home/postgres/omnipitr/log/omnipitr-^Y^m^d.log -x /var/tmp/omnipitr/dstbackup -dr gzip=rsync://backup/database/hot_backup/ -t /var/tmp/omnipitr/ --pid-file /home/postgres/omnipitr/backup-master.pid -v
268
269 Meaning of options:
270
271 =over
272
273 =item * -D /home/postgres/data : Where is data directory for PostgreSQL instance
274
275 =item * -l /home/postgres/omnipitr/log/omnipitr-^Y^m^d.log : Where to log
276 information - logfile will be daily rotated. Logfile can be shared between
277 OmniPITR programs as each line contains identifier of program that wrote it
278
279 =item * -x /var/tmp/omnipitr/dstbackup : Should be the same path as in I<-db>
280 option to omnipitr-archive, and it shouldn't exist - omnipitr-backup-master will
281 create it, and later on remove.
282
283 =item * -dr gzip=rsync://backup/database/hot_backup/ : Will send gzip compressed
284 backups to backup server to appropriate directory.
285
286 =item * -t /var/tmp/omnipitr/ : Where to create temporary files
287
288 =item * --pid-file /home/postgres/omnipitr/backup-master.pid : When running
289 manually, it's not needed, but when the same command will be put in cronjob -
290 it's nice to be sure that only 1 backup can run at the same time
291
292 =item * -v : Log verbosely - mostly add timings.
293
294 =back
295
296 After running, on backup server you will get 2 files:
297
298 =over
299
300 =item * master-data-YYYY-MM-DD.tar.gz
301
302 =item * master-xlog-YYYY-MM-DD.tar.gz
303
304 =back
305
306 which together form backup for master database.
307
308 =head3 Starting up slave
309
310 First you need to obtain master-data backup file from backup server. Xlog file
311 is only used if you want to start the server as fully working r/w server and not
312 replication slave.
313
314 So, get the .tar.gz file to /home/postgres, and uncompress it.
315
316 It will create /home/postgres/data directory which contains copy of PostgreSQL
317 files from master.
318
319 Now, edit /home/postgres/data/postgresql.conf (this, like any other file
320 mentioned in this section - on slave), and change archive_command to:
321
322     archive_command = '/bin/true'
323
324 To make sure that slave will not try to archive xlog files.
325
326 Afterwards, in /home/postgres/data directory make I<recovery.conf> file with
327 single line:
328
329     restore_command = ' /opt/omnipitr/bin/omnipitr-restore -l /var/tmp/omnipitr-^Y^m^d.log -s gzip=/home/postgres/wal_archive -f /home/postgres/omnipitr/finish.recovery -r -p /home/postgres/omnipitr/pause.removal -v -t /var/tmp/omnipitr/ -w 900 %f %p'
330
331 Meaning of options:
332
333 =over
334
335 =item * -l /var/tmp/omnipitr-^Y^m^d.log : Where to log information - logfile
336 will be daily rotated. Logfile can be shared between OmniPITR programs as each
337 line contains identifier of program that wrote it
338
339 =item * -s gzip=/home/postgres/wal_archive : Where to find xlog segments sent in
340 from master, and how are they compressed
341
342 =item * -f /home/postgres/omnipitr/finish.recovery : Trigger file - if it will
343 exist - recovery will be stopped.
344
345 =item * -r : remove no longer needed xlog segments - it's a bit more tricky than
346 'segments that have been already applied', but we can be sure it's safe
347 operation - even in cases like stopping slave recovery for days.
348
349 =item * -p /home/postgres/omnipitr/pause.removal : trigger file - if it exists -
350 segments will no longer be removed when no longer needed - this is useful for
351 omnipitr-backup-slave
352
353 =item * -v : log verbosely
354
355 =item * -t /var/tmp/omnipitr/ : where to create temporary files, if needed.
356
357 =item * -w 900 : wait 15 minutes between xlog arrival and application - this is
358 to be able to stop recovery in case of catastrophic query being run on master
359 (think: I<truncate table users>)
360
361 =back
362
363 Afterwards - just start PostgreSQL on slave, and check generated logs for
364 information about progress, but everything should be working just fine now.
365
366 =head2 COPYRIGHT
367
368 The OmniPITR project is Copyright (c) 2009-2010 OmniTI. All rights reserved.
Note: See TracBrowser for help on using the browser.