Обсуждение: Pg_dump and bytaA

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.

Στις Πέμπτη 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

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.

Στις Παρασκευή 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

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

Στις Παρασκευή 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

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

-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.

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

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

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.

"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

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