pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Pg_dump and bytaA
13+ messages / 5 participants
[nested] [flat]

* Pg_dump and bytaA
@ 2007-03-08 20:02 Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 15:18 ` Re: Pg_dump and bytaA Ireneusz Pluta <ipluta@wp.pl>
  0 siblings, 2 replies; 13+ messages in thread

From: Lukas @ 2007-03-08 20:02 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org; +Cc: pgsql-admin

Hello,

 we have one table in database (db has over 200 tables), which has one
byteA filed for storing user photos. We are making backup every night, but
it is now too large because of photos. We do not need to make backup of
photos every night, but the question is how to backup database without
this field?
 I tried to look at keys of pgdump, but found nothing...

Now we are using it like this:
pg_dump -f /home/backup/DB.sql -d -Fp -v DB

thx
Lukas


-- 
This message has been scanned for viruses and
dangerous content, and is believed to be clean.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
@ 2007-03-09 08:05 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  1 sibling, 1 reply; 13+ messages in thread

From: Achilleas Mantzios @ 2007-03-09 08:05 UTC (permalink / raw)
  To: pgsql-admin

Στις Πέμπτη 08 Μάρτιος 2007 22:02, ο/η Lukas έγραψε:
> Hello,
>
>  we have one table in database (db has over 200 tables), which has one
> byteA filed for storing user photos. We are making backup every night, but
> it is now too large because of photos. We do not need to make backup of
> photos every night, but the question is how to backup database without
> this field?
>  I tried to look at keys of pgdump, but found nothing...

This isnt directly possible.
You could have a mirror of the database,
and nullify these bytea columns in the mirror db before 
taking the dump of this mirror db.

>
> Now we are using it like this:
> pg_dump -f /home/backup/DB.sql -d -Fp -v DB
>
> thx
> Lukas

-- 
Achilleas Mantzios



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2007-03-09 09:00   ` Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Lukas @ 2007-03-09 09:00 UTC (permalink / raw)
  To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin

Hello,

 pg_dump can avoid bloob fields (it can dumb without blobs), but as I
understand byteA is not bloob, so in this case what is blob? I did not
found such type as blob..

Lukas


> Στις Πέμπτη 08 Μάρτιος 2007 22:02, ο/η Lukas
> έγραψε:
>> Hello,
>>
>>  we have one table in database (db has over 200 tables), which has one
>> byteA filed for storing user photos. We are making backup every night,
>> but
>> it is now too large because of photos. We do not need to make backup of
>> photos every night, but the question is how to backup database without
>> this field?
>>  I tried to look at keys of pgdump, but found nothing...
>
> This isnt directly possible.
> You could have a mirror of the database,
> and nullify these bytea columns in the mirror db before
> taking the dump of this mirror db.
>
>>
>> Now we are using it like this:
>> pg_dump -f /home/backup/DB.sql -d -Fp -v DB
>>
>> thx
>> Lukas
>
> --
> Achilleas Mantzios
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
> --
> This message has been scanned for viruses and
> dangerous content, and is believed to be clean.
>
>



-- 
This message has been scanned for viruses and
dangerous content, and is believed to be clean.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
@ 2007-03-09 09:50     ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  0 siblings, 1 reply; 13+ messages in thread

From: Achilleas Mantzios @ 2007-03-09 09:50 UTC (permalink / raw)
  To: Lukas <lukas@fmf.vtu.lt>; +Cc: pgsql-admin

Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas έγραψε:
> Hello,
>
>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
> understand byteA is not bloob, so in this case what is blob? I did not
> found such type as blob..
>

Take a look at 
http://www.postgresql.org/docs/7.4/interactive/largeobjects.html

You might also consider writing a program for transforming your db/app
from bytea based to large object based, and simply exclude blobs
from your pg_dump's.

> Lukas
>
> > Στις Πέμπτη 08 Μάρτιος 2007 22:02, ο/η Lukas
> >
> > έγραψε:
> >> Hello,
> >>
> >>  we have one table in database (db has over 200 tables), which has one
> >> byteA filed for storing user photos. We are making backup every night,
> >> but
> >> it is now too large because of photos. We do not need to make backup of
> >> photos every night, but the question is how to backup database without
> >> this field?
> >>  I tried to look at keys of pgdump, but found nothing...
> >
> > This isnt directly possible.
> > You could have a mirror of the database,
> > and nullify these bytea columns in the mirror db before
> > taking the dump of this mirror db.
> >
> >> Now we are using it like this:
> >> pg_dump -f /home/backup/DB.sql -d -Fp -v DB
> >>
> >> thx
> >> Lukas
> >
> > --
> > Achilleas Mantzios
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content, and is believed to be clean.

-- 
Achilleas Mantzios



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2007-03-09 13:05       ` Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Milen A. Radev @ 2007-03-09 13:05 UTC (permalink / raw)
  To: pgsql-admin

Achilleas Mantzios wrote:
> Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas έγραψε:
>> Hello,
>>
>>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
>> understand byteA is not bloob, so in this case what is blob? I did not
>> found such type as blob..
>>
> 
> Take a look at 
> http://www.postgresql.org/docs/7.4/interactive/largeobjects.html
> 
> You might also consider writing a program for transforming your db/app
> from bytea based to large object based, and simply exclude blobs
> from your pg_dump's.

How to exclude them from a custom dump? (That's for version 8.1/8.2)



-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
@ 2007-03-09 13:18         ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  0 siblings, 1 reply; 13+ messages in thread

From: Achilleas Mantzios @ 2007-03-09 13:18 UTC (permalink / raw)
  To: pgsql-admin

Στις Παρασκευή 09 Μάρτιος 2007 15:05, ο/η Milen A. Radev έγραψε:
> Achilleas Mantzios wrote:
> > Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas έγραψε:
> >> Hello,
> >>
> >>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
> >> understand byteA is not bloob, so in this case what is blob? I did not
> >> found such type as blob..
> >
> > Take a look at
> > http://www.postgresql.org/docs/7.4/interactive/largeobjects.html
> >
> > You might also consider writing a program for transforming your db/app
> > from bytea based to large object based, and simply exclude blobs
> > from your pg_dump's.
>
> How to exclude them from a custom dump? (That's for version 8.1/8.2)

you just dont include them :)

-- 
Achilleas Mantzios


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2007-03-09 14:02           ` Milen A. Radev <milen@radev.net>
  2007-03-09 14:27             ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 14:47             ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  0 siblings, 2 replies; 13+ messages in thread

From: Milen A. Radev @ 2007-03-09 14:02 UTC (permalink / raw)
  To: pgsql-admin

Achilleas Mantzios wrote:
> Στις Παρασκευή 09 Μάρτιος 2007 15:05, ο/η Milen A. Radev έγραψε:
>> Achilleas Mantzios wrote:
>>> Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas έγραψε:
>>>> Hello,
>>>>
>>>>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
>>>> understand byteA is not bloob, so in this case what is blob? I did not
>>>> found such type as blob..
>>> Take a look at
>>> http://www.postgresql.org/docs/7.4/interactive/largeobjects.html
>>>
>>> You might also consider writing a program for transforming your db/app
>>> from bytea based to large object based, and simply exclude blobs
>>> from your pg_dump's.
>> How to exclude them from a custom dump? (That's for version 8.1/8.2)
> 
> you just dont include them :)

Yes, and how exactly you do not include them?

For example - this is my backup script that is executed nightly:

-------------------------------------------------------------------
PSQL=/usr/local/pgsql/bin/psql
PG_DUMP=/usr/local/pgsql/bin/pg_dump
PG_DUMPALL=/usr/local/pgsql/bin/pg_dumpall


$PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql

for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM
pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"`
do
        echo -n -e "Dumping database $db...\t\t"
        $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump
        echo "done."
done
-------------------------------------------------------------------

Guess what? There was a special command-line parameter for including
large objects in the custom/tar dump until version 8.1
(http://www.postgresql.org/docs/8.0/static/app-pgdump.html, look for
"--blobs"). Now they are included by default AND could not be excluded,
at least I could not find a way to do that
(http://www.postgresql.org/docs/8.1/static/app-pgdump.html).

So what are you suggesting?


-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
@ 2007-03-09 14:27             ` Lukas <lukas@fmf.vtu.lt>
  2007-03-09 14:42               ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  1 sibling, 1 reply; 13+ messages in thread

From: Lukas @ 2007-03-09 14:27 UTC (permalink / raw)
  To: Milen A. Radev <milen@radev.net>; +Cc: pgsql-admin


-b
--blobs
    Include large objects in dump.

Examples

To dump a database:
$ pg_dump mydb > db.out


To dump a database called mydb that contains large objects to a tar file:
$ pg_dump -Ft -b mydb > db.tar


> Achilleas Mantzios wrote:
>> Στις Παρασκευή 09 Μάρτιος 2007 15:05, ο/η Milen A.
>> Radev έγραψε:
>>> Achilleas Mantzios wrote:
>>>> Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas
>>>> έγραψε:
>>>>> Hello,
>>>>>
>>>>>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
>>>>> understand byteA is not bloob, so in this case what is blob? I did
>>>>> not
>>>>> found such type as blob..
>>>> Take a look at
>>>> http://www.postgresql.org/docs/7.4/interactive/largeobjects.html
>>>>
>>>> You might also consider writing a program for transforming your db/app
>>>> from bytea based to large object based, and simply exclude blobs
>>>> from your pg_dump's.
>>> How to exclude them from a custom dump? (That's for version 8.1/8.2)
>>
>> you just dont include them :)
>
> Yes, and how exactly you do not include them?
>
> For example - this is my backup script that is executed nightly:
>
> -------------------------------------------------------------------
> PSQL=/usr/local/pgsql/bin/psql
> PG_DUMP=/usr/local/pgsql/bin/pg_dump
> PG_DUMPALL=/usr/local/pgsql/bin/pg_dumpall
>
>
> $PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql
>
> for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM
> pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"`
> do
>         echo -n -e "Dumping database $db...\t\t"
>         $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump
>         echo "done."
> done
> -------------------------------------------------------------------
>
> Guess what? There was a special command-line parameter for including
> large objects in the custom/tar dump until version 8.1
> (http://www.postgresql.org/docs/8.0/static/app-pgdump.html, look for
> "--blobs"). Now they are included by default AND could not be excluded,
> at least I could not find a way to do that
> (http://www.postgresql.org/docs/8.1/static/app-pgdump.html).
>
> So what are you suggesting?
>
>
> --
> Milen A. Radev
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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
>
> --
> This message has been scanned for viruses and
> dangerous content, and is believed to be clean.
>
>



-- 
This message has been scanned for viruses and
dangerous content, and is believed to be clean.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 14:27             ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
@ 2007-03-09 14:42               ` Milen A. Radev <milen@radev.net>
  2007-03-09 16:27                 ` Re: Pg_dump and bytaA Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Milen A. Radev @ 2007-03-09 14:42 UTC (permalink / raw)
  To: pgsql-admin

Lukas wrote:
> -b
> --blobs
>     Include large objects in dump.
> 
> Examples
> 
> To dump a database:
> $ pg_dump mydb > db.out
> 
> To dump a database called mydb that contains large objects to a tar file:
> $ pg_dump -Ft -b mydb > db.tar


Please read what I've written. This was the case up to version 8.0.
After it the large objects are *always* included in the custom/tar type
dumps. So no need for "-b/--blobs" *but* you have no way to exclude them
 . In the docs for pg_dump for versions after 8.1 the large objects are
not even mentioned.


> 
>> Achilleas Mantzios wrote:
>>> Στις Παρασκευή 09 Μάρτιος 2007 15:05, ο/η Milen A.
>>> Radev έγραψε:
>>>> Achilleas Mantzios wrote:
>>>>> Στις Παρασκευή 09 Μάρτιος 2007 11:00, ο/η Lukas
>>>>> έγραψε:
>>>>>> Hello,
>>>>>>
>>>>>>  pg_dump can avoid bloob fields (it can dumb without blobs), but as I
>>>>>> understand byteA is not bloob, so in this case what is blob? I did
>>>>>> not
>>>>>> found such type as blob..
>>>>> Take a look at
>>>>> http://www.postgresql.org/docs/7.4/interactive/largeobjects.html
>>>>>
>>>>> You might also consider writing a program for transforming your db/app
>>>>> from bytea based to large object based, and simply exclude blobs
>>>>> from your pg_dump's.
>>>> How to exclude them from a custom dump? (That's for version 8.1/8.2)
>>> you just dont include them :)
>> Yes, and how exactly you do not include them?
>>
>> For example - this is my backup script that is executed nightly:
>>
>> -------------------------------------------------------------------
>> PSQL=/usr/local/pgsql/bin/psql
>> PG_DUMP=/usr/local/pgsql/bin/pg_dump
>> PG_DUMPALL=/usr/local/pgsql/bin/pg_dumpall
>>
>>
>> $PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql
>>
>> for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM
>> pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"`
>> do
>>         echo -n -e "Dumping database $db...\t\t"
>>         $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump
>>         echo "done."
>> done
>> -------------------------------------------------------------------
>>
>> Guess what? There was a special command-line parameter for including
>> large objects in the custom/tar dump until version 8.1
>> (http://www.postgresql.org/docs/8.0/static/app-pgdump.html, look for
>> "--blobs"). Now they are included by default AND could not be excluded,
>> at least I could not find a way to do that
>> (http://www.postgresql.org/docs/8.1/static/app-pgdump.html).
>>
>> So what are you suggesting?



-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 14:27             ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 14:42               ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
@ 2007-03-09 16:27                 ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-03-09 16:57                   ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2007-03-09 16:27 UTC (permalink / raw)
  To: Milen A. Radev <milen@radev.net>; +Cc: pgsql-admin

"Milen A. Radev" <milen@radev.net> writes:
> ... In the docs for pg_dump for versions after 8.1 the large objects are
> not even mentioned.

Not so: -b came back in 8.2.

-b
--blobs

     Include large objects in the dump. This is the default behavior
     except when --schema, --table, or --schema-only is specified, so
     the -b switch is only useful to add large objects to selective dumps.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 14:27             ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 14:42               ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 16:27                 ` Re: Pg_dump and bytaA Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-09 16:57                   ` Milen A. Radev <milen@radev.net>
  0 siblings, 0 replies; 13+ messages in thread

From: Milen A. Radev @ 2007-03-09 16:57 UTC (permalink / raw)
  To: pgsql-admin

Tom Lane wrote:
> "Milen A. Radev" <milen@radev.net> writes:
>> ... In the docs for pg_dump for versions after 8.1 the large objects are

Doh, I meant "versions after 8.0" but I believe you understood me.

>> not even mentioned.
> 
> Not so: -b came back in 8.2.

That's nice. A change I haven't noticed.

> -b
> --blobs
> 
>      Include large objects in the dump. This is the default behavior
>      except when --schema, --table, or --schema-only is specified, so
>      the -b switch is only useful to add large objects to selective dumps.

So it's useless for my case. Very unfortunate.

-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 08:05 ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 09:00   ` Re: Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
  2007-03-09 09:50     ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 13:05       ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
  2007-03-09 13:18         ` Re: Pg_dump and bytaA Achilleas Mantzios <achill@matrix.gatewaynet.com>
  2007-03-09 14:02           ` Re: Pg_dump and bytaA Milen A. Radev <milen@radev.net>
@ 2007-03-09 14:47             ` Milen A. Radev <milen@radev.net>
  1 sibling, 0 replies; 13+ messages in thread

From: Milen A. Radev @ 2007-03-09 14:47 UTC (permalink / raw)
  To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin

On 09/03/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
[...]
> maybe with --format=p (plain text sql) ?
[...]

And to miss all the advantages of the custom format?


-- 
Milen A. Radev



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Pg_dump and bytaA
  2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
@ 2007-03-09 15:18 ` Ireneusz Pluta <ipluta@wp.pl>
  1 sibling, 0 replies; 13+ messages in thread

From: Ireneusz Pluta @ 2007-03-09 15:18 UTC (permalink / raw)
  To: Lukas <lukas@fmf.vtu.lt>; +Cc: pgsql-novice@postgresql.org; pgsql-admin

Lukas napisał(a):
> Hello,
>
>  we have one table in database (db has over 200 tables), which has one
> byteA filed for storing user photos. We are making backup every night, but
> it is now too large because of photos. We do not need to make backup of
> photos every night, but the question is how to backup database without
> this field?
>  I tried to look at keys of pgdump, but found nothing...
>
> Now we are using it like this:
> pg_dump -f /home/backup/DB.sql -d -Fp -v DB
>
> thx
> Lukas
>
>   
A different idea:

Why don't you move your photos table to a dedicated schema? Then you can 
dump only main schema, not including the photos schema, with the -n 
option to pg_dump. So, assuming that 'public' is the schema where all 
your other tables exist, your command would look like:

pg_dump -n public -f /home/backup/DB.sql -d -Fp -v DB

If you then change your mind and find that you need photos dump too, but 
possibly on a schedule different from the rest, you may be dumping only 
photos schema with something like:

pg_dump -n photos -f /home/backup/photos.sql -d -Fp -v DB

however you might want a different output format (compressed?) in this case.

Of course, this change would require you to make your application know 
where the photos table is. But most probably this can be easily done by 
executing something like "SET search_path TO photos, public", just after 
each connection to your database.

Irek.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2007-03-09 16:57 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-08 20:02 Pg_dump and bytaA Lukas <lukas@fmf.vtu.lt>
2007-03-09 08:05 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-03-09 09:00   ` Lukas <lukas@fmf.vtu.lt>
2007-03-09 09:50     ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-03-09 13:05       ` Milen A. Radev <milen@radev.net>
2007-03-09 13:18         ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-03-09 14:02           ` Milen A. Radev <milen@radev.net>
2007-03-09 14:27             ` Lukas <lukas@fmf.vtu.lt>
2007-03-09 14:42               ` Milen A. Radev <milen@radev.net>
2007-03-09 16:27                 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 16:57                   ` Milen A. Radev <milen@radev.net>
2007-03-09 14:47             ` Milen A. Radev <milen@radev.net>
2007-03-09 15:18 ` Ireneusz Pluta <ipluta@wp.pl>

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