Thread: pg_lo_import alternative ...
Hi all. One of our clients wants to store images into his postgres database hosted by us. To do this is trying to use the PHP function pg_lo_import, receiving this error message from server: Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must have Postgres superuser privilege to use server-side lo_import(). Anyone can use the client-side lo_import() provided by libpq. . in /apps/web/html/funcions.php on line 121 I can read that there's a client-side function in libpq, but I can't find any information about it and their use. Anyone knows something? -- ******************************************************** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio@oasi.org ********************************************************
Are there many images? I do not suggest to store the images in the database. Better save them on the disk and in the database save only the path to the image. Think also to the size of the dump(with images). It is more easy to handle this way. This is my opinion. Best regards. Andy. ----- Original Message ----- From: "Daniel Rubio" <drubior@tinet.org> To: <pgsql-admin@postgresql.org> Sent: Wednesday, November 26, 2003 3:58 PM Subject: [ADMIN] pg_lo_import alternative ... > Hi all. > > One of our clients wants to store images into his postgres database > hosted by us. > > To do this is trying to use the PHP function pg_lo_import, receiving > this error message from server: > > Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must > have Postgres superuser privilege to use server-side lo_import(). Anyone > can use the client-side lo_import() provided by libpq. . in > /apps/web/html/funcions.php on line 121 > > I can read that there's a client-side function in libpq, but I can't > find any information about it and their use. > > Anyone knows something? > -- > ******************************************************** > Daniel Rubio Rodríguez > OASI (Organisme Autònom Per la Societat de la Informació) > c/ Assalt, 12 > 43003 - Tarragona > Tef.: 977.244.007 - Fax: 977.224.517 > e-mail: drubio@oasi.org > ******************************************************** > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
I'm after experiences similar to what you are describing with lo_export. I have used lo_import and ol_export to test imagestorage and retrival. One problem that you mention is with super user access required to export to the local file system.The second problem is that it seems to add another layer to a web application. A table constructed that contains pointersoutside the database seems to be the easiest to deal with in terms of retrieval of images and dislpay within a webpage. Anyone that that is using Postgres for storage and retrival of images please post your experiences. Thank you. >-----Original Message----- >From: Andrei Bintintan [mailto:klodoma@ar-sd.net] >Sent: Wednesday, November 26, 2003 02:20 PM >To: 'Daniel Rubio', pgsql-admin@postgresql.org >Subject: Re: [ADMIN] pg_lo_import alternative ... > >Are there many images? > >I do not suggest to store the images in the database. Better save them on >the disk and in the database save only the path to the image. Think also to >the size of the dump(with images). > >It is more easy to handle this way. > >This is my opinion. > >Best regards. >Andy. > >----- Original Message ----- >From: "Daniel Rubio" <drubior@tinet.org> >To: <pgsql-admin@postgresql.org> >Sent: Wednesday, November 26, 2003 3:58 PM >Subject: [ADMIN] pg_lo_import alternative ... > > >> Hi all. >> >> One of our clients wants to store images into his postgres database >> hosted by us. >> >> To do this is trying to use the PHP function pg_lo_import, receiving >> this error message from server: >> >> Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must >> have Postgres superuser privilege to use server-side lo_import(). Anyone >> can use the client-side lo_import() provided by libpq. . in >> /apps/web/html/funcions.php on line 121 >> >> I can read that there's a client-side function in libpq, but I can't >> find any information about it and their use. >> >> Anyone knows something? >> -- >> ******************************************************** >> Daniel Rubio Rodríguez >> OASI (Organisme Autònom Per la Societat de la Informació) >> c/ Assalt, 12 >> 43003 - Tarragona >> Tef.: 977.244.007 - Fax: 977.224.517 >> e-mail: drubio@oasi.org >> ******************************************************** >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend > > >---------------------------(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 >
I'm storing images and files in the database. I have a database server with all of the binaries and several web servers that attach to it. The web servers are for load balancing and each maintain a cache of image files locally. It is the cached images that are served by the web servers. When a request comes in, the web server checks to the database server to see if there's a newer file, and if so it downloads the file and stores it in the cache. If not, it just uses the cached version. This helps us out because changes to images and files themselves are part of transactions, not just changes to their names. Backup is easier because I don't have to worry about a mismatch between the files and the names in the database. Also, I can have one connection changing making changes to a file or image while another downloads the last committed version without having to worry about any contention for it. Let me know if you're interested in any of our implementation details. Adam Ruth On Nov 26, 2003, at 9:10 AM, kent@wareham.k12.ma.us wrote: > I'm after experiences similar to what you are describing with > lo_export. I have used lo_import and ol_export to test image storage > and retrival. One problem that you mention is with super user access > required to export to the local file system. The second problem is > that it seems to add another layer to a web application. A table > constructed that contains pointers outside the database seems to be > the easiest to deal with in terms of retrieval of images and dislpay > within a web page. > > Anyone that that is using Postgres for storage and retrival of images > please post your experiences. Thank you. > >> -----Original Message----- >> From: Andrei Bintintan [mailto:klodoma@ar-sd.net] >> Sent: Wednesday, November 26, 2003 02:20 PM >> To: 'Daniel Rubio', pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] pg_lo_import alternative ... >> >> Are there many images? >> >> I do not suggest to store the images in the database. Better save >> them on >> the disk and in the database save only the path to the image. Think >> also to >> the size of the dump(with images). >> >> It is more easy to handle this way. >> >> This is my opinion. >> >> Best regards. >> Andy. >> >> ----- Original Message ----- >> From: "Daniel Rubio" <drubior@tinet.org> >> To: <pgsql-admin@postgresql.org> >> Sent: Wednesday, November 26, 2003 3:58 PM >> Subject: [ADMIN] pg_lo_import alternative ... >> >> >>> Hi all. >>> >>> One of our clients wants to store images into his postgres database >>> hosted by us. >>> >>> To do this is trying to use the PHP function pg_lo_import, receiving >>> this error message from server: >>> >>> Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must >>> have Postgres superuser privilege to use server-side lo_import(). >>> Anyone >>> can use the client-side lo_import() provided by libpq. . in >>> /apps/web/html/funcions.php on line 121 >>> >>> I can read that there's a client-side function in libpq, but I can't >>> find any information about it and their use. >>> >>> Anyone knows something? >>> -- >>> ******************************************************** >>> Daniel Rubio Rodríguez >>> OASI (Organisme Autònom Per la Societat de la Informació) >>> c/ Assalt, 12 >>> 43003 - Tarragona >>> Tef.: 977.244.007 - Fax: 977.224.517 >>> e-mail: drubio@oasi.org >>> ******************************************************** >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 8: explain analyze is your friend >> >> >> ---------------------------(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 >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
Hi, > Anyone that that is using Postgres for storage and retrival of > images please post your experiences. I've been very happy with a web application I built which stores images in Postgresql in text format. I use PHP to base_64 encode the binary image file into a text string and store that. I can send you a PHP class which I use to encode/decode the images and inserts them into a table. Email me off list if your keen. Cheers Rudi.
Hi, We are developing a management software for our clients. This software si already used for a year. The database dump is around 40MB(zipped around 10). Our clients receive every day between 100 - 200 photos(size between 80-200kB). For the moment they have around 30000 photos, the overall size of photos is around 5 GB. We decided that we do not put these photos in the database, we store it on the disk. In the database we store only the "realname" and the "storage path" of the photos and some additional info. Yes it is true, that things may become complicated if a photo is erased or think also to a backup to the photos. But this comes in the hand of the administrator. I tried first also to put the photos in the database, but I gave up on this ideea. I think I have the code "somewhere"... have to search it it's already 1 year old....if somebody needs it just say. Best regards. Andy. ----- Original Message ----- From: "Rudi Starcevic" <rudi@oasis.net.au> To: <pgsql-admin@postgresql.org> Sent: Thursday, November 27, 2003 6:51 AM Subject: Re: [ADMIN] pg_lo_import alternative ... > Hi, > > > Anyone that that is using Postgres for storage and retrival of > > images please post your experiences. > > I've been very happy with a web application I built which stores > images in Postgresql in text format. I use PHP to base_64 encode > the binary image file into a text string and store that. > > I can send you a PHP class which I use to encode/decode the > images and inserts them into a table. > Email me off list if your keen. > > Cheers > Rudi. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
I'm storing images and files in the database. I have a database server with all of the binaries and several web servers that attach to it. The web servers are for load balancing and each maintain a cache of image files locally. It is the cached images that are served by the web servers. When a request comes in, the web server checks to the database server to see if there's a newer file, and if so it downloads the file and stores it in the cache. If not, it just uses the cached version. This helps us out because changes to images and files themselves are part of transactions, not just changes to their names. Backup is easier because I don't have to worry about a mismatch between the files and the names in the database. Also, I can have one connection changing making changes to a file or image while another downloads the last committed version without having to worry about any contention for it. Let me know if you're interested in any of our implementation details. Adam Ruth On Nov 26, 2003, at 9:10 AM, kent@wareham.k12.ma.us wrote: > I'm after experiences similar to what you are describing with > lo_export. I have used lo_import and ol_export to test image storage > and retrival. One problem that you mention is with super user access > required to export to the local file system. The second problem is > that it seems to add another layer to a web application. A table > constructed that contains pointers outside the database seems to be > the easiest to deal with in terms of retrieval of images and dislpay > within a web page. > > Anyone that that is using Postgres for storage and retrival of images > please post your experiences. Thank you. > >> -----Original Message----- >> From: Andrei Bintintan [mailto:klodoma@ar-sd.net] >> Sent: Wednesday, November 26, 2003 02:20 PM >> To: 'Daniel Rubio', pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] pg_lo_import alternative ... >> >> Are there many images? >> >> I do not suggest to store the images in the database. Better save >> them on >> the disk and in the database save only the path to the image. Think >> also to >> the size of the dump(with images). >> >> It is more easy to handle this way. >> >> This is my opinion. >> >> Best regards. >> Andy. >> >> ----- Original Message ----- >> From: "Daniel Rubio" <drubior@tinet.org> >> To: <pgsql-admin@postgresql.org> >> Sent: Wednesday, November 26, 2003 3:58 PM >> Subject: [ADMIN] pg_lo_import alternative ... >> >> >>> Hi all. >>> >>> One of our clients wants to store images into his postgres database >>> hosted by us. >>> >>> To do this is trying to use the PHP function pg_lo_import, receiving >>> this error message from server: >>> >>> Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must >>> have Postgres superuser privilege to use server-side lo_import(). >>> Anyone >>> can use the client-side lo_import() provided by libpq. . in >>> /apps/web/html/funcions.php on line 121 >>> >>> I can read that there's a client-side function in libpq, but I can't >>> find any information about it and their use. >>> >>> Anyone knows something? >>> -- >>> ******************************************************** >>> Daniel Rubio Rodríguez >>> OASI (Organisme Autònom Per la Societat de la Informació) >>> c/ Assalt, 12 >>> 43003 - Tarragona >>> Tef.: 977.244.007 - Fax: 977.224.517 >>> e-mail: drubio@oasi.org >>> ******************************************************** >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 8: explain analyze is your friend >> >> >> ---------------------------(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 >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
Your method sounds very interesting for storing images. I'd be
interested in knowing what other attributes you store with the images.
My interest in storing images is for documents that pertain to personnel
records such as copy of certifications, licenses, diploma, training
courses, personnel evaluations. Many are scanned images.
create table external_object(
exobid integer not null default
nextval('external_object_seq'::text),
exob OID not null,
useradd_eid integer references employee,
insert_timestamp timestamp,
mod_timestamp timestamp default now(),
mimetype varchar(30) not null default 'jpeg',
description varchar(30) not null,
filename varchar(100) not null,
table_ref varchar(20),
key_id_ref integer,
flags char(2),
doctype_version varchar(4),
constraint external_obj_pk primary key(exobid)
);
When you do backups do you use pg_dump or are you able to shutdown
postgres to use something like rsync?
On Wed, 2003-11-26 at 23:51, Rudi Starcevic wrote:
> Hi,
>
> > Anyone that that is using Postgres for storage and retrival of
> > images please post your experiences.
>
> I've been very happy with a web application I built which stores
> images in Postgresql in text format. I use PHP to base_64 encode
> the binary image file into a text string and store that.
>
> I can send you a PHP class which I use to encode/decode the
> images and inserts them into a table.
> Email me off list if your keen.
>
> Cheers
> Rudi.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Kent L. Nasveschuk <kent@wareham.k12.ma.us>