1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
Normal backups
==============
It is not completely safe to rely on normal Linux backup utilities to
protect your database, unless you stop the postmaster first. If you back
up a database file while it is being vacuumed, you have a good chance of
preserving a version that is corrupted either in its information, or,
even worse, in its internal structure. In the latter case, the entire
database might turn out to be inaccessible.
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [QUESTIONS] Q: online backup. also comparison to mSQL
To: olly@lfix.co.uk (Oliver Elphick)
Date: Mon, 15 Dec 1997 21:41:42 -0500 (EST)
Cc: he@pprd.abbott.com, pgsql-questions@postgreSQL.org
>
> Bruce Momjian wrote:
> >Because we are a no-overwrite database, you can do a backup while the
> >system is running. Just don't do it while a vacuum is running.
>
> It is true, isn't it, that it is not *informationally* safe to backup
> while the system is running. Would it not be possible for one part of a
> transaction to be stored while another was not, and for the backup to
> contain a broken transaction?
If you backup pg_log/pg_time first, then no matter what you restore,
only those transactions committed at the time of the pg_log/pg_time
backup will be considered valid after the restore.
We just went over these issues in the hackers list in an attempt to
develope a system system to give no-fsync performance, with robust
rollback cabapility.
>
> Second is it possible (I know it's unlikely) for a backup program to
> copy a disk sector at the moment that it is being written, so that a corrupt
> sector is recorded on the bakup?
Again, as long as no vacuum is running, the data should be OK.
--
Bruce Momjian
maillist@candle.pha.pa.us
Here is an alternative approach:
Date: Sat, 22 Nov 1997 12:27:07 +0100
From: "Micha3 Mosiewicz" <mimo@lodz.pdi.net>
To: ewhardy@mindspring.com
Cc: "pgsql-questions@postgreSQL.org" <pgsql-questions@postgreSQL.org>
Subject: Re: [QUESTIONS] Backing up a DB
Ted Hardy wrote:
>
> Are there anything special that needs to be done to back up a postgres
> database other than backing up what's in the data/ directory off of the
> postgres install root? Are there any other files needed to restore a
> database? Of course assuming that the data/ directory is installed under
> a working postgres install.
This method is WRONG. You shouldn't back up your data dir. You may find
it unusable with next version of postgres.
To backup you database type:
pg_dump dbname|gzip -9>backup.sql.gz
To backup your entire database system:
pg_dumpall |gzip -9>backup.sql.gz
Additionally it will take less space.
Mike
--
WWW: http://www.lodz.pdi.net/~mimo tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz * Bugaj 66 m.54 * 95-200 Pabianice * POLAND
As of 6.2.1, grant permissions and table ownership get lost with pg_dump.
|
