Thread: Backup & Restore

Backup & Restore

From
Marcelo Pereira
Date:
Hello All,

Which is the simplest way to do a backup? My db is really small (about
4Mb), so I would like to make a backup to each day, save it in separated
files, and restore it if necessary.

So, which is the usual way to create the backup files? And how can I
restore these backups?

Thanks in advance,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___                             |
       / (_/ _ \__    Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/



Re: Backup & Restore

From
Doug McNaught
Date:
Marcelo Pereira <gandalf@sum.desktop.com.br> writes:

> Hello All,
>
> Which is the simplest way to do a backup? My db is really small (about
> 4Mb), so I would like to make a backup to each day, save it in separated
> files, and restore it if necessary.
>
> So, which is the usual way to create the backup files? And how can I
> restore these backups?

The simplest way is to run pg_dump from a cron job and redirect the
output to a file.  You can name the file whatever you want.  The
output of pg_dump is SQL that will create and populate your database.

To restore, feed the dump file to 'psql'.

Note that if you're using large objects it gets a little more
complicated and you have to use 'pg_restore' rather than 'psql' for
restores.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Backup & Restore

From
"Gavin M. Roy"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Attached is a small backup script that I wrote.  It requires command
line PHP w/ PostgreSQL support.  What it does is rip through all the
databases, gets the table listings, makes a directory structure and
dumps every table to its own .sql file using pg_dump.  Then it backs
it up to tape.  With some minor modifications it may work for you :D
The reason I wrote this is with the size of my database, single table
restorations from on pg_dumpall file were horrendous, and since the
database changes structure on a regular basis (new tables, etc) I
didn't want to be limited to a shell script I had to hand configure
every time I added/removed a table.

Cheers,

Gavin

- -----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marcelo
Pereira
Sent: Monday, February 25, 2002 4:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Backup & Restore


Hello All,

Which is the simplest way to do a backup? My db is really small
(about
4Mb), so I would like to make a backup to each day, save it in
separated
files, and restore it if necessary.

So, which is the usual way to create the backup files? And how can I
restore these backups?

Thanks in advance,

Marcelo Pereira

- -- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___                             |
       / (_/ _ \__    Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/



- ---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBPHpvKH9xeBXAlKqsEQIH/QCgkm6mqH/LjMeaFFE6UBZtC/825JcAoI7i
ZyYUF2Y/Sr3Ni+AoIbSG1CZi
=SIMe
-----END PGP SIGNATURE-----


Attachment

Re: Backup & Restore

From
Bruce Momjian
Date:
From my reading of this code, it dumps each table in a separate
transaction, meaning it does not make a consistent backup.  I recommend
pg_dump in -Fc mode and the use of pg_restore.

---------------------------------------------------------------------------

Gavin M. Roy wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Attached is a small backup script that I wrote.  It requires command
> line PHP w/ PostgreSQL support.  What it does is rip through all the
> databases, gets the table listings, makes a directory structure and
> dumps every table to its own .sql file using pg_dump.  Then it backs
> it up to tape.  With some minor modifications it may work for you :D
> The reason I wrote this is with the size of my database, single table
> restorations from on pg_dumpall file were horrendous, and since the
> database changes structure on a regular basis (new tables, etc) I
> didn't want to be limited to a shell script I had to hand configure
> every time I added/removed a table.
>
> Cheers,
>
> Gavin
>
> - -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marcelo
> Pereira
> Sent: Monday, February 25, 2002 4:17 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Backup & Restore
>
>
> Hello All,
>
> Which is the simplest way to do a backup? My db is really small
> (about
> 4Mb), so I would like to make a backup to each day, save it in
> separated
> files, and restore it if necessary.
>
> So, which is the usual way to create the backup files? And how can I
> restore these backups?
>
> Thanks in advance,
>
> Marcelo Pereira
>
> - -- Remember that only God and Esc+:w saves.
>         __
>        (_.\           Marcelo Pereira       |
>         / / ___                             |
>        / (_/ _ \__    Matematica/99 - IMECC |
> _______\____/_\___)___Unicamp_______________/
>
>
>
> - ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBPHpvKH9xeBXAlKqsEQIH/QCgkm6mqH/LjMeaFFE6UBZtC/825JcAoI7i
> ZyYUF2Y/Sr3Ni+AoIbSG1CZi
> =SIMe
> -----END PGP SIGNATURE-----
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Backup & Restore

From
"Gavin M. Roy"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is true to a point.  Since my DB is dormant from 11 PM PST time
until 5 AM PST time, I'm not concerned about the table relationships
getting out of sync.  I guess this isn't the most eloquent way of
doing this, but it works for me...

So based upon your comment, pg_dump locks the database while running,
preventing any data inconsistencies?

Gavin

- -----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Monday, February 25, 2002 12:02 PM
To: Gavin M. Roy
Cc: 'Marcelo Pereira'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Backup & Restore



From my reading of this code, it dumps each table in a separate
transaction, meaning it does not make a consistent backup.  I
recommend
pg_dump in -Fc mode and the use of pg_restore.

- ----------------------------------------------------------------------
- -----

Gavin M. Roy wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Attached is a small backup script that I wrote.  It requires
> command line PHP w/ PostgreSQL support.  What it does is rip
> through all the databases, gets the table listings, makes a
> directory structure and dumps every table to its own .sql file
> using pg_dump.  Then it backs it up to tape.  With some minor
> modifications it may work for you :D  The reason I wrote this is
> with the size of my database, single table restorations from on
> pg_dumpall file were horrendous, and since the database changes
> structure on a regular basis (new tables, etc) I
> didn't want to be limited to a shell script I had to hand configure
> every time I added/removed a table.
>
> Cheers,
>
> Gavin
>
> - -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marcelo
> Pereira
> Sent: Monday, February 25, 2002 4:17 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Backup & Restore
>
>
> Hello All,
>
> Which is the simplest way to do a backup? My db is really small
> (about
> 4Mb), so I would like to make a backup to each day, save it in
> separated
> files, and restore it if necessary.
>
> So, which is the usual way to create the backup files? And how can
> I restore these backups?
>
> Thanks in advance,
>
> Marcelo Pereira
>
> - -- Remember that only God and Esc+:w saves.
>         __
>        (_.\           Marcelo Pereira       |
>         / / ___                             |
>        / (_/ _ \__    Matematica/99 - IMECC |
> _______\____/_\___)___Unicamp_______________/
>
>
>
> - ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 7.0.3 for non-commercial use
> <http://www.pgp.com>
>
> iQA/AwUBPHpvKH9xeBXAlKqsEQIH/QCgkm6mqH/LjMeaFFE6UBZtC/825JcAoI7i
> ZyYUF2Y/Sr3Ni+AoIbSG1CZi
> =SIMe
> -----END PGP SIGNATURE-----
>

[ Attachment, skipping... ]

>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: if posting/reading
> through Usenet, please send an appropriate subscribe-nomail command
> to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

- --
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania
19026

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBPHqblX9xeBXAlKqsEQIIXwCeN+FGZRjWlPFHq5/fV2+XrdNw04IAoJOK
r9FYL2QPWr7nkC7GOgiozt3o
=EAnc
-----END PGP SIGNATURE-----


Re: Backup & Restore

From
Bruce Momjian
Date:
Gavin M. Roy wrote:
[ There is text before PGP section. ]
>
-- Start of PGP signed section.
> This is true to a point.  Since my DB is dormant from 11 PM PST time
> until 5 AM PST time, I'm not concerned about the table relationships
> getting out of sync.  I guess this isn't the most eloquent way of
> doing this, but it works for me...
>
> So based upon your comment, pg_dump locks the database while running,
> preventing any data inconsistencies?

Yes, doesn't so much lock as use MVCC to return a constent snapshot.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Backup & Restore

From
Doug McNaught
Date:
"Gavin M. Roy" <gmr@justsportsusa.com> writes:

>
> This is true to a point.  Since my DB is dormant from 11 PM PST time
> until 5 AM PST time, I'm not concerned about the table relationships
> getting out of sync.  I guess this isn't the most eloquent way of
> doing this, but it works for me...
>
> So based upon your comment, pg_dump locks the database while running,
> preventing any data inconsistencies?

No, but it does the whole backup within a transaction, which
guarantees a consistent view.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Permissions on file created by COPY TO

From
Joel Shellman
Date:
Of I run a COPY TO command it creates a file. What determines the
permissions of that file? The created file is owned by postgres, but I
need a different user to be able to manipulate it (actually, I just want
to delete it). So I thought to make it group writable so the other user
could be in postgres group and delete the file.

Thank you,
--
Joel Shellman
Comprehensive Internet Solutions -- Building business dreams.
[ web design | database | e-commerce | hosting | marketing ]
iKestrel, Inc.  http://www.ikestrel.com/


Re: Permissions on file created by COPY TO

From
Tom Lane
Date:
Joel Shellman <joel@ikestrel.com> writes:
> Of I run a COPY TO command it creates a file. What determines the
> permissions of that file? The created file is owned by postgres, but I
> need a different user to be able to manipulate it (actually, I just want
> to delete it). So I thought to make it group writable so the other user
> could be in postgres group and delete the file.

Uh, deletion has nothing to do with writability of the file, and
everything to do with writability of the directory it's in.   So you
could solve the stated problem without touching the Postgres code.

However, the answer to your question is that the mode is hardwired to
644 (rw-r--r--) by the umask call in src/backend/commands/copy.c.

            regards, tom lane