Обсуждение: pg_dump without blobs
Hi, I have a postgressql database with a table containing a bytea field. I would like to dump without this particulary field in order to improve dump and restore time. I try with options : -n public (without -b) but it's always the same, the dump is huge. How can i do that ? Do i have to convert my field bytea to oid ? Thank you for your help, Sebastien
Sébastien Boutté writes: > I try with options : -n public (without -b) but it's always the same, > the dump is huge. According to the man page that should have done it: Note: Non-schema objects such as blobs are not dumped when -n is specified. You can add blobs back to the dump with the --blobs switch. I guess the next question is 'what does postgresql considers a blob'? bytea fields? How about a large text with megabytes worth of data?
On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: > I guess the next question is 'what does postgresql considers a blob'? > bytea fields? How about a large text with megabytes worth of data? bytea and text fields are NOT blobs. they are what you access via the 'large object' functions.
Vivek Khera wrote: > > On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: > >> I guess the next question is 'what does postgresql considers a blob'? >> bytea fields? How about a large text with megabytes worth of data? > > bytea and text fields are NOT blobs. they are what you access via the > 'large object' functions. To follow up on this. In oracle large text CLOB and binary objects BLOB are synonomous (I believe) with PostgreSQL TEXT and BYTEA. PostgreSQL also supports a non standard, and frankly better implementation called lo for binary data, which also uses BYTEA data but breaks it up to make it more efficient per row. There is no way to "not" dump your TEXT and BYTEA data from a particular column if you are dumping the whole table. One option would be to use CREATE TEMP TABLE AS SELECT... and then dump that temp table. Joshua D. Drake > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
I understand that i cannot do a dump of a database without bytea values.I hope that these feature would be present in next version of pg_dump as we can store large binary values in these sort of fields and pg_dump taking a lot of time for dumping the database. For the moment, I will patch my database to transform my bytea field into lo (oid) field. I would to do something similar like this : update table set new_field = oldfield but i have problem with cast from bytea to oid. Is there an efficient way of doing this update ? Thank you, Sébastien Boutté On 7/16/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Vivek Khera wrote: > > > > On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: > > > >> I guess the next question is 'what does postgresql considers a blob'? > >> bytea fields? How about a large text with megabytes worth of data? > > > > bytea and text fields are NOT blobs. they are what you access via the > > 'large object' functions. > > To follow up on this. > > In oracle large text CLOB and binary objects BLOB are synonomous (I > believe) with PostgreSQL TEXT and BYTEA. > > PostgreSQL also supports a non standard, and frankly better > implementation called lo for binary data, which also uses BYTEA data but > breaks it up to make it more efficient per row. > > There is no way to "not" dump your TEXT and BYTEA data from a particular > column if you are dumping the whole table. > > One option would be to use CREATE TEMP TABLE AS SELECT... and then dump > that temp table. > > Joshua D. Drake > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Sébastien Boutté writes: > I understand that i cannot do a dump of a database without bytea > values. What version of postgresql? If 8.2 uou can do a copy to with a subselect copy (select with fields except bytea) to '/somepath';
I use version 8.2.4 since last week end in my production database. I don't want to do a per table dump (I have more than 300 tables). It's not maintainable as the schema evolves regulary. On 7/16/07, Francisco Reyes <lists@stringsutils.com> wrote: > Sébastien Boutté writes: > > > I understand that i cannot do a dump of a database without bytea > > values. > > What version of postgresql? > If 8.2 uou can do a copy to with a subselect > copy (select with fields except bytea) to '/somepath'; > >
Joshua D. Drake skrev: > Vivek Khera wrote: >> >> On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: >> >>> I guess the next question is 'what does postgresql considers a blob'? >>> bytea fields? How about a large text with megabytes worth of data? >> >> bytea and text fields are NOT blobs. they are what you access via the >> 'large object' functions. > > To follow up on this. > > In oracle large text CLOB and binary objects BLOB are synonomous (I > believe) with PostgreSQL TEXT and BYTEA. I don't have experience with Oracle versions >8, but back then this was not the case. Back then, Oracles LOBs had a special syntax for access, quite similar to the one documented here: http://www.postgresql.org/docs/8.2/interactive/largeobjects.html > PostgreSQL also supports a non standard, and frankly better > implementation called lo for binary data, which also uses BYTEA data but > breaks it up to make it more efficient per row. What I would really like would be for the two models to be merged - a LO should be a datatype in its own right, having all the same functionality as BYTEA, plus the additional interface. As you say, this is an additional implementation, and the programmer shouldn't need additional syntax unless it needs the additional functionality. So in my dream world, you could do: CREATE TABLE image ( name text, raster LOB ); INSERT INTO image (name, raster) VALUES ('beautiful image', lo_import('/tmp/image.gif')); INSERT INTO image (name, raster) VALUES ('beautiful image 2', ' _ / \ |O| \_/ ASCII ART RULEZ')); SELECT name, raster FROM image WHERE name = 'beautiful image'; SELECT name, lo_export(raster, '/tmp/ascii.txt') FROM image WHERE name = 'beautiful image 2'; I realize that there may be all kinds of implementation problems with this - but this is how it 'ought' to work, IMO. Nis
I think i've found a solution, i will do this :
CREATE FUNCTION save_bytea_on_system(bytea) RETURNS varchar AS $$
use File::Temp ();
$fh = new File::Temp();
binmode($fh);
my $data = $_[0];
$data =~ s{\\(\\|[0-7]{3})}{$1 eq "\\" ? $1 : chr(oct($1))}ge;
$fname = $fh->filename;
syswrite $fh,$data;
close $fh;
return $fname;
$$ LANGUAGE plperlu;
update my_table set new_field = lo_import(save_bytea_on_system(old_field));
I think that postgres team should add a method for doing these
conversion much simplier.
Sebastien
On 7/15/07, Sébastien Boutté <sebastien.boutte@gmail.com> wrote:
> Hi,
>
> I have a postgressql database with a table containing a bytea field. I
> would like to dump without this particulary field in order to improve
> dump and restore time.
> I try with options : -n public (without -b) but it's always the same,
> the dump is huge.
>
> How can i do that ?
> Do i have to convert my field bytea to oid ?
>
> Thank you for your help,
>
> Sebastien
>