Thread: 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_______________/
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
-----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
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 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-----
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
"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
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/
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