Thread: Backups

Backups

From

"John J. Urbaniak"

Date:

14 July 2005, 15:05:45

Hello,

I am new to this list and new to PostGreSQL.

My PostGreSQL  applications will typically be small networks - maybe
from 1 to 5 users.

The data will be on a server, at, say

  E:\PGSQLDATA

I have a question about backups:

Is it ok simply to zip the contents of E:\PGSQLDATA to a zip file and
store this file on a CD or DVD?  I am sure that the data will not exceed
50 Megabytes, so it should fit on a CD.

Is there anything I am overlooking in such a backup procedure?  Will
that backup everything I need to restore (via unzip) in case of emergency?

Thanks,

John J. Urbaniak
Aviar, Inc.

Re: Backups

From

Tom Lane

Date:

14 July 2005, 15:15:43

"John J. Urbaniak" <jjurban@attglobal.net> writes:
> I have a question about backups:

> Is it ok simply to zip the contents of E:\PGSQLDATA to a zip file and
> store this file on a CD or DVD?

Yes, *if* you shut down the postmaster while making the backup.

See
http://www.postgresql.org/docs/8.0/static/backup.html
for discussion --- what you propose is the "file system backup"
approach.

            regards, tom lane

Re: Backups

From

"John J. Urbaniak"

Date:

14 July 2005, 15:43:41

Tom Lane wrote:

"John J. Urbaniak" <jjurban@attglobal.net> writes:

I have a question about backups:

Is it ok simply to zip the contents of E:\PGSQLDATA to a zip file and 
store this file on a CD or DVD?

Yes, *if* you shut down the postmaster while making the backup.

See
http://www.postgresql.org/docs/8.0/static/backup.html
for discussion --- what you propose is the "file system backup"
approach.
		regards, tom lane

Thank you for the quick reply. I think I am going to like working with PostGreSQL. The documentation refers to "tar" - I just wanted to make sure that zip would work as well. I am using Rexx, so I think I can write a CMD to

1. Stop the postmaster
2. Vacuum the database
3. Zip the directory
4. Write the zip file to CD or DVD
5. Start postmaster again

Again, thanks.

John

Re: Backups

From

"John J. Urbaniak"

Date:

14 July 2005, 16:03:02

OOPS! I guess I have to reverse steps 1 and 2 below.

John

John J. Urbaniak wrote:

See
http://www.postgresql.org/docs/8.0/static/backup.html
for discussion --- what you propose is the "file system backup"
approach.
		regards, tom lane

Re: Backups

From

Philip Hallstrom

Date:

14 July 2005, 16:09:21

> Is it ok simply to zip the contents of E:\PGSQLDATA to a zip file and store
> this file on a CD or DVD?  I am sure that the data will not exceed 50
> Megabytes, so it should fit on a CD.

if the data won't exceed 50megs I would add step 1a just so you have the
data in a format other than the native postgresql format (say in case for
some freaky reason your machine crashes and *no one* has a copy of that
version of postgres... crazy, but if you have the space...)

1. Vacuum the database

1a. pg_dump the database

2. Stop the postmaster
3. Zip the directory
4. Write the zip file to CD or DVD
5. Start postmaster again

Re: Backups

From

Bruno Wolff III

Date:

15 July 2005, 17:03:19

On Thu, Jul 14, 2005 at 09:09:06 -0700,
  Philip Hallstrom <postgresql@philip.pjkh.com> wrote:
> >Is it ok simply to zip the contents of E:\PGSQLDATA to a zip file and
> >store this file on a CD or DVD?  I am sure that the data will not exceed
> >50 Megabytes, so it should fit on a CD.
>
> if the data won't exceed 50megs I would add step 1a just so you have the
> data in a format other than the native postgresql format (say in case for
> some freaky reason your machine crashes and *no one* has a copy of that
> version of postgres... crazy, but if you have the space...)

The platform (mostly processor type) should be the same and some build
options also need to be the same (in particular integer datetimes) or
you will have some problems with the binary data. With just 50MB, using
pgdump is going to be safer if you don't control the platform and what
build options were used.