473,686 Members | 2,782 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

BLOBs, pg_dump & pg_restore

My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2

I have no difficulty inserting and managing BLOBs into the Large Object
system table, and I have a user table called images which maintains the
relationship between the BLOB loid and the identity that relates to it in
my user tables. So far so good.

When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export
and \lo_unlink functions.

The syntax for the \lo_import function indicates that a comment may be
appended to the BLOB entry in the large object system table. What is not
mentioned is that this will only occur if psql is run as the PostgreSQL
superuser.

Now, my concern is that if I use pg_dump with the --clean or --create, and
the --blobs options, and then try a pg_restore from the resulting archive
file, I believe the BLOBs will take up a different loid to the one they
came from, and hence the relation in my user table will be broken and I
will not be able to relocate the BLOBs using my identifier in my images
table.

My other problem is that the various functions in PHP, namely the various
pg_lo_* functions do not appear to have the ability to include the comment
option that is available to \lo_import under psql.

I suppose one workaround, though not very elegant, would be to use under
PHP something like `psql \lo_export <known_file_nam e>` whilst running
through the records in the images table, and not to use the --blobs option
under pg_dump, then use `psql \lo_import <known_file_nam e>` called from
PHP to reload them after a pg_restore has been run, at the same time
updating the loids in my images table. As I say very inelegant.

I guess this must be a shortfall in both PHP, in as much as it doesn't
appear to handle BLOBs to cleanly, and PostgreSQL in its way that it
handles the description column in the large opjects system table.

Am I right or wrong, or is there a better workaround?

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux .com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
7 6937
Howard Lowndes <la****@lannet. com.au> writes:
Now, my concern is that if I use pg_dump with the --clean or --create, and
the --blobs options, and then try a pg_restore from the resulting archive
file, I believe the BLOBs will take up a different loid to the one they
came from, and hence the relation in my user table will be broken
No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs. If you have a test case where this fails to work, let's
see it ...
My other problem is that the various functions in PHP, namely the various
pg_lo_* functions do not appear to have the ability to include the comment
option that is available to \lo_import under psql.


psql is out on a limb claiming that LOs can have comments --- there's no
support for that in the backend or any other client application. It's
doing it by direct manual injection of entries into the pg_description
system catalog, which is why superuser privilege is needed. It's a
useful hack if you only use psql, but still a hack. Feel free to
contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd
like to see a better level of support for this.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2
On Wed, 1 Oct 2003, Tom Lane wrote:
Howard Lowndes <la****@lannet. com.au> writes:
Now, my concern is that if I use pg_dump with the --clean or --create, and
the --blobs options, and then try a pg_restore from the resulting archive
file, I believe the BLOBs will take up a different loid to the one they
came from, and hence the relation in my user table will be broken
No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs. If you have a test case where this fails to work, let's
see it ...


No, I don't have any example, it is an enquiry. What I am reading into
the above however is that the loid column in my table should have a
CONSTRAINT REFERENCES clause to whereever in the system large objects
table. Correct?
My other problem is that the various functions in PHP, namely the various
pg_lo_* functions do not appear to have the ability to include the comment
option that is available to \lo_import under psql.


psql is out on a limb claiming that LOs can have comments --- there's no
support for that in the backend or any other client application. It's
doing it by direct manual injection of entries into the pg_description
system catalog, which is why superuser privilege is needed. It's a
useful hack if you only use psql, but still a hack. Feel free to
contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd
like to see a better level of support for this.


Sorry, way beyond my competency level.

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux .com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3
Howard Lowndes <la****@lannet. com.au> writes:
On Wed, 1 Oct 2003, Tom Lane wrote:
No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs. If you have a test case where this fails to work, let's
see it ...
No, I don't have any example, it is an enquiry. What I am reading into
the above however is that the loid column in my table should have a
CONSTRAINT REFERENCES clause to whereever in the system large objects
table. Correct?


No. No doubt if Postgres had had foreign keys when the large-object stuff
was invented, it would have required such a constraint for LO
references, but it didn't and it doesn't. The pg_restore code simply
goes through all "oid" columns (and all "lo" columns if you've installed
the contrib/lo datatype) and looks for matches to LO OIDs that existed
in the dumped database. When it finds a match, it replaces that value
with the new BLOB's OID. Simple, effective, crufty ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4
On Thu, 2 Oct 2003, Tom Lane wrote:
Howard Lowndes <la****@lannet. com.au> writes:
On Wed, 1 Oct 2003, Tom Lane wrote:
No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs. If you have a test case where this fails to work, let's
see it ...

No, I don't have any example, it is an enquiry. What I am reading into
the above however is that the loid column in my table should have a
CONSTRAINT REFERENCES clause to whereever in the system large objects
table. Correct?


No. No doubt if Postgres had had foreign keys when the large-object stuff
was invented, it would have required such a constraint for LO
references, but it didn't and it doesn't. The pg_restore code simply
goes through all "oid" columns (and all "lo" columns if you've installed
the contrib/lo datatype) and looks for matches to LO OIDs that existed
in the dumped database. When it finds a match, it replaces that value
with the new BLOB's OID. Simple, effective, crufty ...


OK, I'm convinced, except for one small, but not insignificant hiccup.
When you dump a database with the BLOBs, even with the -c option, and then
restore that database again with the -c option, you get double the BLOBs.
The original BLOBs are there as are the new copies, and the cross
referenced oids are updated. It looks as if there should be some way of
removing the old BLOB at restore time once the new BLOB is in place. I
don't know the detail of how pg_restore works but it does create a table
solely for the purpose of cross referencing the oids.

This of course means that each dump and subsequent restore doubles up on
the BLOBs and since BLOBs are by nature Large there could be disk space
problems.

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux .com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5
Howard Lowndes <la****@lannet. com.au> writes:
OK, I'm convinced, except for one small, but not insignificant hiccup.
When you dump a database with the BLOBs, even with the -c option, and then
restore that database again with the -c option, you get double the BLOBs.
The original BLOBs are there as are the new copies, and the cross
referenced oids are updated. It looks as if there should be some way of
removing the old BLOB at restore time once the new BLOB is in place. I
don't know the detail of how pg_restore works but it does create a table
solely for the purpose of cross referencing the oids.

This of course means that each dump and subsequent restore doubles up on
the BLOBs and since BLOBs are by nature Large there could be disk space
problems.


If you blow away the database (DROP DATABASE) and recreate it before
doing the restore, those LOs will be gone. If not, something is very
wrong. pg_restore basically assumes a virgin database.

If you just clear out the tables before the restore, you should also
clear out the pg_largeobject table. It's not hard to keep garbage LOs
from hanging around by putting an ON DELETE trigger on the referencing
table.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #6
On 2 Oct 2003, Doug McNaught wrote:
Howard Lowndes <la****@lannet. com.au> writes:
OK, I'm convinced, except for one small, but not insignificant hiccup.
When you dump a database with the BLOBs, even with the -c option, and then
restore that database again with the -c option, you get double the BLOBs.
The original BLOBs are there as are the new copies, and the cross
referenced oids are updated. It looks as if there should be some way of
removing the old BLOB at restore time once the new BLOB is in place. I
don't know the detail of how pg_restore works but it does create a table
solely for the purpose of cross referencing the oids.

This of course means that each dump and subsequent restore doubles up on
the BLOBs and since BLOBs are by nature Large there could be disk space
problems.


If you blow away the database (DROP DATABASE) and recreate it before
doing the restore, those LOs will be gone. If not, something is very
wrong. pg_restore basically assumes a virgin database.

If you just clear out the tables before the restore, you should also
clear out the pg_largeobject table. It's not hard to keep garbage LOs
from hanging around by putting an ON DELETE trigger on the referencing
table.


Tks

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux .com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #7
Howard Lowndes <la****@lannet. com.au> writes:
When you dump a database with the BLOBs, even with the -c option, and then
restore that database again with the -c option, you get double the BLOBs.
The original BLOBs are there as are the new copies, and the cross
referenced oids are updated.


Yeah. I don't believe "-c" causes anything much to be done with BLOBs.
It would be fairly risky to try, since the premise of "-c" is that you
don't want the *whole* database wiped out, only the objects you are
replacing. I'd worry about zapping BLOBs that are still referenced
elsewhere ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3750
by: Sam | last post by:
I'm having trouble restoring databases that have to lo type installed in /contrib/lo. The dump seems to work just fine, I get no errors when I execute the following command #pg_dump -Fc -o -b mydb > mydb_dump > CREATE DATABASE mydb_restore TEMPLATE template0
4
4456
by: Mark Mikulec | last post by:
Hi there, I wonder if anyone can shed some light on a very frustrating problem. I'm running a debian linux 3.0 "woody" server, nothing special, with the latest version of postres that apt-get will allow me, which I *think* it;s 7.1 something, I don't know how to figure out the postgres version. Anywho - I'm trying to backup my databases, which I did at one point, but I have no idea what happened, could have been an upgrade. My Dbs...
1
1844
by: Ruth Hsieh | last post by:
The postgreSQL release used is 7.3.4. We would like to dump the objects for particular user. How? I used -U option, the result still dump all the objects with all the users. In order to migrate to 7.4, if the users were not created in 7.4 the pg_dump result will fail or create under wrong user. How to prevent this? What is different between pg_restore and psql if restore the pg_dump result? I tried to use pg_restore, but it always...
0
1853
by: Aienthiwan | last post by:
Hello all, I'm running a debian server and recently updated my version of PostgreSQL to "unstable", that being v7.4.1. I had no trouble at all getting everything up to date and going. But my auto-database backup scripts do not work any more: I get this error when I try to do a pg_dump with the new version of Postgres:
0
991
by: Otto Blomqvist | last post by:
Hello ! I have a table that is created as follows Create Table file_200 (Record_number integer, Customer_Image OID; CREATE UNIQUE INDEX file_200_Record_Number_Key ON file_200 (record_number); I store pictures using (In Delphi 7)
0
1397
by: Otto Blomqvist | last post by:
Hello ! I was just wondering if anyone knows if this is a bug or whats up. Im using psql 7.2.2 I can do full restores and dumps all day long, however when I do the following I run into problems Do a full dump with pg_dump -Fc -O Database1 > dump.tar
1
1871
by: Neil Zanella | last post by:
Hello, I have an SQL database which I create with: psql -f create.sql foodb I then access this database and perform several insertions, modifications, and deletions. Finally, I want to backup my database. I do not want to backup the schema. All I want is a set of insert statements stored in a file insert.sql which I can run on a set of empty database tables
4
1459
by: David Rysdam | last post by:
I have a bunch of data in Sybase and some of it is in image fields. We use bcp on this data transparently all the time without major issues in character mode. Is there a fundamental technical reason that BLOBs can't be COPY'd in postgresql or is it just that nobody has ever wanted to before? (If I was starting from scratch I'd probably be happy with pg_dump/pg_restore, but I'd like to migrate in easy stages over from Sybase, so I'd...
2
2812
by: Jerry LeVan | last post by:
Hi, I am just getting into large objects and bytea "stuff". I created a small db called pictures and loaded some large objects and then tried to do a restore. Here is how I got the dump. pg_dump -Fc -b pictures > /Users/jerry/desktop/db.comp
0
8516
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8932
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7599
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4308
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4532
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2945
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1934
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.