pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup procedure
5+ messages / 5 participants
[nested] [flat]
* Backup procedure
@ 2003-08-13 17:11 Enio Schutt Junior <enio@pmpf.rs.gov.br>
0 siblings, 0 replies; 5+ messages in thread
From: Enio Schutt Junior @ 2003-08-13 17:11 UTC (permalink / raw)
To: pgsql-admin
Hi,
According to some answers about the consistency of a database in a snapshot of a whole
HD (excluding proc, tmp, and dev, of course) and without stopping the postmaster service,
it is said it should work because it's like if the system has crashed, so restoring files from
the HD and starting the server would be ok.
According to postgresql administrator's guide, section 9.2 (File system level backup),
item #1: "The database server *must* be shutdown in order to get a usable backup. Half-way
measures such as disallowing all connections will not work as there is always some buffering
going on. For this reason it is also not advisable to trust file systems that claim to support
"consistent snapshots" ".
Assuming the admin's guide is a "little bit more" right, my backup restore procedure would
be as follows:
1-Restore data from the backup Hd into a new Hd.
2-Copy some files from /usr/local/pgsql/data like postgresql.conf, pg_hba.conf, pg_ident.conf,
and some other authentication files whose names appear in pg_hba.conf (I suspect PG_VERSION and
postmaster.opts would not be needed as initdb should create these files) to another directory.
3-Remove the entire /usr/local/pgsql/data directory
4-Run initdb -D "/usr/local/pgsql/data"
5-Copy those files back to /usr/local/pgsql/data
6-Start the server
7-Restore information generated by pg_dump (tables) and pg_dumpall -g (users and groups)
Would this work well? I know there are other procedures and I would be pleased to know that
this procedure works fine.
Thanks in advance,
Enio
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Backup procedure
@ 2003-08-13 22:10 Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
0 siblings, 0 replies; 5+ messages in thread
From: Murthy Kambhampaty @ 2003-08-13 22:10 UTC (permalink / raw)
To: Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-admin
I think its fair to say that the documentation is outdated on this subject,
becuase it had in mind a different definition of "consistent snapshots".
See:
http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshots&q=b
where Tom Lane and Peter Eisentraut agree that if your hardware or software
can copy the data and log files from a given instant in time, you have a
consistent backup. (In that thread, you can even see examples for the
postmaster logs at startup with the backup data. ) Bruce Momjian makes the
same point earlier in this thread as well.
Note that the way to recover data from filesystem level backup is to start a
postmaster on the backup copy of the $PGDATA cluster, dump from the backup
and restore into the "production" databases. (Piping this will avoid having
to create dump files to restore from.) You can't simply copy files around to
recover data from backup.
The bottom line is that if you want backups that are as reliable as pgdump-s
(the gold standard), you can get them off of filesystem snapshots.
Filesystem snapshots are a lot faster than pgdump-s.
Cheers,
Murthy
-----Original Message-----
From: Enio Schutt Junior [mailto:enio@pmpf.rs.gov.br]
Sent: Wednesday, August 13, 2003 13:12
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Backup procedure
Hi,
According to some answers about the consistency of a database in a snapshot
of a whole
HD (excluding proc, tmp, and dev, of course) and without stopping the
postmaster service,
it is said it should work because it's like if the system has crashed, so
restoring files from
the HD and starting the server would be ok.
According to postgresql administrator's guide, section 9.2 (File system
level backup),
item #1: "The database server *must* be shutdown in order to get a usable
backup. Half-way
measures such as disallowing all connections will not work as there is
always some buffering
going on. For this reason it is also not advisable to trust file systems
that claim to support
"consistent snapshots" ".
Assuming the admin's guide is a "little bit more" right, my backup restore
procedure would
be as follows:
1-Restore data from the backup Hd into a new Hd.
2-Copy some files from /usr/local/pgsql/data like postgresql.conf,
pg_hba.conf, pg_ident.conf,
and some other authentication files whose names appear in pg_hba.conf (I
suspect PG_VERSION and
postmaster.opts would not be needed as initdb should create these files) to
another directory.
3-Remove the entire /usr/local/pgsql/data directory
4-Run initdb -D "/usr/local/pgsql/data"
5-Copy those files back to /usr/local/pgsql/data
6-Start the server
7-Restore information generated by pg_dump (tables) and pg_dumpall -g (users
and groups)
Would this work well? I know there are other procedures and I would be
pleased to know that
this procedure works fine.
Thanks in advance,
Enio
^ permalink raw reply [nested|flat] 5+ messages in thread
* Backup procedure
@ 2005-03-07 15:34 Gourish Singbal <gourish@gmail.com>
2005-03-07 16:00 ` Re: Backup procedure Michael Fuhr <mike@fuhr.org>
2005-03-07 16:47 ` Re: Backup procedure Michael Adler <adler@pobox.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gourish Singbal @ 2005-03-07 15:34 UTC (permalink / raw)
To: pgsql-admin
Hi,
Can anybody provided me the procedure to take online backups in 8.0.1.
Simple steps would be really great..
--
Best,
Gourish Singbal
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Backup procedure
2005-03-07 15:34 Backup procedure Gourish Singbal <gourish@gmail.com>
@ 2005-03-07 16:00 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-03-07 16:00 UTC (permalink / raw)
To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin
On Mon, Mar 07, 2005 at 09:04:04PM +0530, Gourish Singbal wrote:
> Can anybody provided me the procedure to take online backups in 8.0.1.
> Simple steps would be really great..
Does the "Backup and Restore" chapter in the documentation not
answer your questions? If not, could you be more specific about
what you're trying to do and what problems you've having?
http://www.postgresql.org/docs/8.0/interactive/backup.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Backup procedure
2005-03-07 15:34 Backup procedure Gourish Singbal <gourish@gmail.com>
@ 2005-03-07 16:47 ` Michael Adler <adler@pobox.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Adler @ 2005-03-07 16:47 UTC (permalink / raw)
To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin
On Mon, Mar 07, 2005 at 09:04:04PM +0530, Gourish Singbal wrote:
> Hi,
>
> Can anybody provided me the procedure to take online backups in 8.0.1.
> Simple steps would be really great..
>
> --
> Best,
> Gourish Singbal
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
try pg_dump(1)
http://www.postgresql.org/docs/current/static/app-pgdump.html
-Mike
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-03-07 16:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-13 17:11 Backup procedure Enio Schutt Junior <enio@pmpf.rs.gov.br>
2003-08-13 22:10 Re: Backup procedure Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
2005-03-07 15:34 Backup procedure Gourish Singbal <gourish@gmail.com>
2005-03-07 16:00 ` Michael Fuhr <mike@fuhr.org>
2005-03-07 16:47 ` Michael Adler <adler@pobox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox