8000 Add functions pg_start_backup, pg_stop_backup to create backup label · postgres/postgres@58c4171 · GitHub
[go: up one dir, main page]
Skip to content

Commit 58c4171

Browse files
tglsfdctglsfdc
committed
Add functions pg_start_backup, pg_stop_backup to create backup label
and history files as per recent discussion. While at it, remove pg_terminate_backend, since we have decided we do not have time during this release cycle to address the reliability concerns it creates. Split the 'Miscellaneous Functions' documentation section into 'System Information Functions' and 'System Administration Functions', which hopefully will draw the eyes of those looking for such things.
1 parent a83c45c commit 58c4171

File tree

12 files changed

+2314
-1911
lines changed

12 files changed

+2314
-1911
lines changed

‎doc/src/sgml/backup.sgml

Lines changed: 86 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.39 2004/04/22 07:02:35 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.40 2004/08/03 20:32:30 tgl Exp $
33
-->
44
<chapter id="backup">
55
<title>Backup and Restore</title>
@@ -14,12 +14,14 @@ $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.39 2004/04/22 07:02:35 neilc Exp
1414
</para>
1515

1616
<para>
17-
There are two fundamentally different approaches to backing up
17+
There are three fundamentally different approaches to backing up
1818
<productname>PostgreSQL</> data:
1919
<itemizedlist>
2020
<listitem><para><acronym>SQL</> dump</para></listitem>
2121
<listitem><para>File system level backup</para></listitem>
22+
<listitem><para>On-line backup</para></listitem>
2223
</itemizedlist>
24+
Each has its own strengths and weaknesses.
2325
</para>
2426

2527
<sect1 id="backup-dump">
@@ -314,8 +316,8 @@ tar -cf backup.tar /usr/local/pgsql/data
314316
The database server <emphasis>must</> be shut down in order to
315317
get a usable backup. Half-way measures such as disallowing all
316318
connections will <emphasis>not</emphasis> work
317-
(<command>tar</command> and similar tools do not take an atomic
318-
snapshot of the state of the filesystem at a point in
319+
(mainly because <command>tar</command> and similar tools do not take an
320+
atomic snapshot of the state of the filesystem at a point in
319321
time). Information about stopping the server can be found in
320322
<xref linkend="postmaster-shutdown">. Needless to say that you
321323
also need to shut down the server before restoring the data.
@@ -335,7 +337,8 @@ tar -cf backup.tar /usr/local/pgsql/data
335337
information. Of course it is also impossible to restore only a
336338
table and the associated <filename>pg_clog</filename> data
337339
because that would render all other tables in the database
338-
cluster useless.
340+
cluster useless. So file system backups only work for complete
341+
restoration of an entire database cluster.
339342
</para>
340343
</listitem>
341344
</orderedlist>
@@ -355,7 +358,7 @@ tar -cf backup.tar /usr/local/pgsql/data
355358
properly shut down; therefore, when you start the database server
356359
on the backed-up data, it will think the server had crashed
357360
and replay the WAL log. This is not a problem, just be aware of
358-
it.
361+
it (and be sure to include the WAL files in your dump).
359362
</para>
360363

361364
<para>
@@ -373,6 +376,70 @@ tar -cf backup.tar /usr/local/pgsql/data
373376
the contents of indexes for example, just the commands to recreate
374377
them.)
375378
</para>
379+
</sect1>
380+
381+
<sect1 id="backup-online">
382+
<title>On-line backup and point-in-time recovery</title>
383+
384+
<para>
385+
At all times, <productname>PostgreSQL</> maintains a <firstterm>write ahead
386+
log</> (WAL) that shows details of every change made to the database's data
387+
files. This log exists primarily for crash-safety purposes: if the system
388+
crashes, the database can be restored to consistency by <quote>replaying</>
389+
the log entries made since the last checkpoint. However, the existence
390+
of the log makes it possible to use a third strategy for backing up
391+
databases: we can combine a filesystem-level backup with backup of the WAL
392+
files. If recovery is needed, we restore the backup and then replay from
393+
the backed-up WAL files to bring the backup up to current time. This
394+
approach is notably more complex to administer than either of the previous
395+
approaches, but it has some significant benefits to offer:
396+
<itemizedlist>
397+
<listitem>
398+
<para>
399+
We do not need a perfectly consistent backup as the starting point.
400+
Any internal inconsistency in the backup will be corrected by log
401+
replay (this is not significantly different from what happens during
402+
crash recovery). So we don't need filesystem snapshot capability,
403+
just <application>tar</> or a similar archiving tool.
404+
</para>
405+
</listitem>
406+
<listitem>
407+
<para>
408+
Since we can string together an indefinitely long sequence of WAL files
409+
for replay, continuous backup can be had simply by continuing to archive
410+
the WAL files. This is particularly valuable for large databases, where
411+
making a full backup may take an unreasonable amount of time.
412+
</para>
413+
</listitem>
414+
<listitem>
415+
<para>
416+
There is nothing that says we have to replay the WAL entries all the
417+
way to the end. We could stop the replay at any point and have a
418+
consistent snapshot of the database as it was at that time. Thus,
419+
this technique supports <firstterm>point-in-time recovery</>: it is
420+
possible to restore the database to its state at any time since your base
421+
backup was taken.
422+
</para>
423+
</listitem>
424+
<listitem>
425+
<para>
426+
If we continuously feed the series of WAL files to another machine
427+
that's been loaded with the same base backup, we have a <quote>hot
428+
standby</> system: at any point we can bring up the second machine
429+
and it will have a nearly-current copy of the database.
430+
</para>
431+
</listitem>
432+
</itemizedlist>
433+
</para>
434+
435+
<para>
436+
As with the plain filesystem-backup technique, this method can only
437+
support restoration of an entire database cluster, not a subset.
438+
Also, it requires a lot of archival storage: the base backup is bulky,
439+
and a busy system will generate many megabytes of WAL traffic that
440+
have to be archived. Still, it is the preferred backup technique in
441+
many situations where high reliability is needed.
442+
</para>
376443

377444
</sect1>
378445

@@ -393,16 +460,16 @@ tar -cf backup.tar /usr/local/pgsql/data
393460
change between major releases of <productname>PostgreSQL</> (where
394461
the number after the first dot changes). This does not apply to
395462
different minor releases under the same major release (where the
396-
number of the second dot changes); these always have compatible
463+
number after the second dot changes); these always have compatible
397464
storage formats. For example, releases 7.0.1, 7.1.2, and 7.2 are
398465
not compatible, whereas 7.1.1 and 7.1.2 are. When you update
399-
between compatible versions, then you can simply reuse the data
400-
area in disk by the new executables. Otherwise you need to
466+
between compatible versions, you can simply replace the executables
467+
and reuse the data area on disk. Otherwise you need to
401468
<quote>back up</> your data and <quote>restore</> it on the new
402469
server, using <application>pg_dump</>. (There are checks in place
403470
that prevent you from doing the wrong thing, so no harm can be done
404471
by confusing these things.) The precise installation procedure is
405-
not subject of this section; these details are in <xref
472+
not the subject of this section; those details are in <xref
406473
linkend="installation">.
407474
</para>
408475

@@ -427,7 +494,7 @@ pg_dumpall -p 5432 | psql -d template1 -p 6543
427494

428495
<para>
429496
If you cannot or do not want to run two servers in parallel you can
430-
do the back up step before installing the new version, bring down
497+
do the backup step before installing the new version, bring down
431498
the server, move the old version out of the way, install the new
432499
version, start the new server, restore the data. For example:
433500

@@ -447,6 +514,14 @@ psql template1 < backup
447514
you of strategic places to perform these steps.
448515
</para>
449516

517+
<para>
518+
You will always need a SQL dump (<application>pg_dump</> dump) for
519+
migrating to a new release. Filesystem-level backups (including
520+
on-line backups) will not work, for the same reason that you can't
521+
just do the update in-place: the file formats won't necessarily be
522+
compatible across major releases.
523+
</para>
524+
450525
<note>
451526
<para>
452527
When you <quote>move the old installation out of the way</quote>

‎doc/src/sgml/ddl.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.26 2004/03/07 04:31:01 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.27 2004/08/03 20:32:30 tgl Exp $ -->
22

33
<chapter id="ddl">
44
<title>Data Definition</title>
@@ -1723,7 +1723,7 @@ SET search_path TO myschema;
17231723
</para>
17241724

17251725
<para>
1726-
See also <xref linkend="functions-misc"> for other ways to access
1726+
See also <xref linkend="functions-info"> for other ways to access
17271727
the schema search path.
17281728
</para>
17291729

0 commit comments

Comments
 (0)
0