Backup & Restore

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&gt;

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

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&gt;

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&gt;

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&gt;

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