[GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1
Hi John, Database seem now completely transferred after "create extension lo" on my new database then restore all data from the backup file. (tested by my application to call/save all information data and picture with database) Thank you for your kindly help. Regards, NETsolutions Asia Limited http://www.nsasia.co.th >>> John R Pierce <pie...@hogranch.com> 2016-03-04 01:37 >>> On 3/3/2016 3:53 AM, Premsun Choltanwanich wrote: > > I have no source code for this contrib/lo and dll/function seem > already be stored with my 8.0.13 installation package. upon looking at the current docs again in the light of day, I see that there *IS* a contrib/lo module in all recent versions. try, before doing the restore... as postgres with your empty database... psql mynewdatabase -c "create extension lo" I would also consider doing the dump in two parts. first pass use --schema-only to just dump the defninitions, and restore these, this will create your database structures with no data. you may need to edit the generated .sql file to remove these redundant/erroneous function definitions.then once the schema is created cleanly, do the same dump but with --data-only and to a different file, this will dump just the actual database data, which you them 'restore' to the same database you've already created the schema in... -- john r pierce, recycling bits in santa cruz
[GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1
Hi Joon,
I have no source code for this contrib/lo and dll/function seem already be
stored with my 8.0.13 installation package.
Any suggestion?
Regards,
NETsolutions Asia Limited
http://www.nsasia.co.th
>>> John R Pierce <pie...@hogranch.com> 2016-03-03 09:05 >>>
On 3/2/2016 5:52 PM, Premsun Choltanwanich wrote:
>
> And, almost tables are transferred to new server except tables which
> contain lo data (all those tables are missing from the database) after
> running pg_dump and psql following as per your suggestion.
>
> The attachment is a log file created after all processes are completed.
your logfile shows a bunch of custom functions being defined which are
dependent on a missing binary library, apparently your previous install
of postgres has some customizations.
> 2016-03-02 18:06:25 ICT ERROR: could not find function "lo_in" in file
> "C:/Program Files/PostgreSQL/9.5/lib/lo.dll"
> 2016-03-02 18:06:25 ICT STATEMENT: CREATE FUNCTION lo_in(cstring) RETURNS lo
> LANGUAGE c IMMUTABLE STRICT
> AS '$libdir/lo', 'lo_in';
these custom functions are being used to define a custom data type.
> 2016-03-02 18:06:25 ICT ERROR: function lo_in(cstring) does not exist
> 2016-03-02 18:06:25 ICT STATEMENT: CREATE TYPE lo (
> INTERNALLENGTH = 4,
> INPUT = lo_in,
> OUTPUT = lo_out,
> ALIGNMENT = int4,
> STORAGE = plain
> );
and your tables are using this custom data type 'lo', which is invalid
since the code to implement it is missing.
> 2016-03-02 18:06:26 ICT ERROR: type "lo" is only a shell at character 186
> 2016-03-02 18:06:26 ICT STATEMENT: CREATE TABLE t_familypic (
> sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text)
> NOT NULL,
> mbrsysid bigint NOT NULL,
> familysysid bigint NOT NULL,
> familypic lo
> );
do you have the source code to this lo.dll so you can be rebuild it for
9.5 ?
--
john r pierce, recycling bits in santa cruz
[GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1
Hi Magnus, My database size is about 1.5 GB by most of them are lo (large object) data. Regards, NETsolutions Asia Limited http://www.nsasia.co.th >>> Magnus Hagander <mag...@hagander.net> 2016-03-02 15:29 >>> On Mar 2, 2016 06:01, "John R Pierce" <pie...@hogranch.com> wrote: > > (thread moved from pg_bugs) > (upgrading a 8.0.13 database on Windows XP 32bit to 9.5.1 on Windows 8 64 > bit.....) > > > On 3/1/2016 8:05 PM, Premsun Choltanwanich wrote: >> >> Modified command by remove -Ft flag as per you suggestion: >> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U clubadmin >> -d clubprogram >> >> Result (got same message even with parameter -b or not): >> pg_dump: reading rewrite rules >> pg_dump: reading policies >> pg_dump: reading large objects >> ... >> >> pg_dump: creating FUNCTION "public.plpgsql_call_handler()" >> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)" >> ... >> >> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)" > > > those all sound like standard postgres functions, its not clear to me why > pg_dump is generating the CREATE FUNCTION code for them. > > >> pg_dump: [archiver] could not write to output file: Invalid argument >> > > ok, presumably your new box has plenty of disk space? try this on the new 9.5 > system... > How large is the total database? The earliest versions of pg on Windows had bugs in pg_dump for files larger than 2GB. I don't recall exactly when they were fixed, but this was a long time ago.. Through if my memory is correct the actual bugs were in pg_dump itself, so using a new pg_dump against the old server should be safe. /Magnus
[GENERAL] could not migrate 8.0.13 database with large object data to 9.5.1
Dear All, I have very old project database which also contain lo data (large object data managed by database's functions as lo(oid), lo_in(cstring), lo_oid(lo), lo_out(lo) and oid(lo) to manage ) running on PostgreSQL 8.0.13 and need to migrate it to most recently version as PostgreSQL 9.5.1. After install PostgreSQL 9.5.1, I running a simple command pg_dumb to 8.0.13 server with parameters required for generate dump file by blobs also be included then running psql with all required parameters to create those dump data to 9.5.1 server. However, almost tables are transferred to new server after the process completed except tables which contain my lo data that all those tables are missing from the database. Please advise to me, How could I migrate 8.0.13 database with large object data to be working on 9.5.1? NETsolutions Asia Limited http://www.nsasia.co.th
[GENERAL] PostgreSQL does not support updateable cursors
Dear All, I got error message 'ERROR: column "ctid" does not exist; Error while executing the query' when I try to query SELECT on my VIEW as 'rsSystem.Open "SELECT * FROM v_memocatlist ORDER BY memocategory", connSystem, adOpenStatic, adLockOptimistic'. I found some information on internet about the PostgreSQL does not support updateable cursors so I change my code tobe read only cursors as 'rsSystem.Open "SELECT * FROM v_memocatlist ORDER BY memocategory", connSystem, adOpenForwardOnly, adLockReadOnly' then it work fine.However, I need touse this query for make data ready to be updated on some record. How can I fix this problem? More Information about my system. Database: PostgreSQL 8.3ODBC: psqlODBC 08.03.0100Language: MS Visual Basic 6.0 Regards, Premsun NETsolutions Asia Limited +66 (2) 237 7247
[GENERAL] Slow Query problem
Dear All,I am currently using PostgreSQL database version 8.0.13. My problem relates to a slow result when a query using a defined view joins to another table for a result.Background: I have 7 tables of invoice transactions. The tables are slightly different in that they record different data (some different columns in each table). There are about 250,000 records when a union view is created. A simply query on this union performs satisfactorily. The invoice table union view is then joined with a table of receipts (which have a total of about 150,000 records). It takes around 3.5 seconds for "select * from view_transaction where member_id = 999 and receipt_no is null" (which returns unpaid invoices). By hard coding I created a single table from the 7 invoice tables (instead of creating a union) and then used it with receipt table. This time for the same query improved to 1.8 seconds. To further improve things I tried to code the selection rather than to use a view, and so "select * from temp_transaction where member_id = 999 and receipt_no is null" provided the result in .5 second. (2 records returned containing the details of receipt_no, transaction_no, transaction_type, transaction_amount, member_id). I would prefer to be able to have completed the above by using unions and views. Is it possible to do this, or am I better creating a permanent table of invoices and writing the query as I did above? Any comments on this and suggestions would be appreciated. If there is documentation where I can read up please let me have a link.Thank You,Premsun NETsolutions Asia Limited +66 (2) 237 7247
[GENERAL] Does it has a way to compact the database size?
Dear All,
I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I use database_size('name') function for checking my database size and found thatit's about1209715345.
I think that something maybe wrong on my databasebecause I backup my database everyday and the backup size is about 10 MB.So I restore databse from my backup file then use database_size('name') function again and found that database size is about 56642193.
I can said both 1209715345 and 56642193 are coming from the same database but I wonder that why it quiet difference on size.
Any Idea?
And How can I control or compact my database sizefor make it smallest as possibled?
NETsolutions Asia
Limited
+66 (2) 237 7247
Re: [GENERAL] Does it has a way to compact the database size?
Does it has a way to schedulethe process for make sure thati'm running VACUUM often enough? How?
(ie. create some script on PostgreSQL from running VACUUM FULL on 3.00 AM for first date of every month.)
Richard Huxton dev@archonet.com 3/6/2007 16:50 Premsun Choltanwanich wrote: Dear All, I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I use database_size('name') function for checking my database size and found that it's about 1209715345. I think that something maybe wrong on my database because I backup my database everyday and the backup size is about 10 MB. So I restore databse from my backup file then use database_size('name') function again and found that database size is about 56642193. I can said both 1209715345 and 56642193 are coming from the same database but I wonder that why it quiet difference on size. Any Idea? And How can I control or compact my database size for make it smallest as possibled?To expand on Andreas'
answer.If you want to get your live database down to 56642193 you'll probably want to VACUUM FULL and REINDEX the whole database. That should basically get it as small as possible.While the database is in use, make sure you are running VACUUM often enough (and have free-space-map [fsm] settings high enough) to keep track of freed space in your database files. That way the database size should stay static.You'll never get as small as the backup file, because (1) it doesn't contain any indexes etc. and (2) it's compressed.-- Richard Huxton Archonet Ltd
NETsolutions Asia
Limited
+66 (2) 237 7247
Re: [GENERAL] Header meaning for pg_dump
Dear Richard, Regardingthe information you give to me, I understand that this information is a thing that normally used by PostgreSQL system.Andthe informationseem to be placed on a comment area. So,Who need to have a clearly understanding on the header information? Thank you Richard Huxton dev@archonet.com 11/6/2006 15:41 Premsun Choltanwanich wrote: Dear All, I had open the backup file of PostgreSQL created by pg_dump command. I found that pg_dump make a comment line as header for each module it backup. I try to understand the meaning of value contained on header for my sample header -- -- TOC entry 1427 (class 1259 OID 1216127) -- Dependencies: 7 -- Name: t_option; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- Could you please advise me for What does they mean? and What the value used for?I'm not sure that they're used for anything in that form - that's just documenting internal details.TOC - Table Of ContentsOID - Object IDentifier - as used by PG to reference tables,functions etc.Dependencies - iirc is the number of references to this table (e.g. foreign keys).The others explain themselves.Check the manual section on system tables, and perhaps try: SELECT OID,* FROM pg_class WHERE relname='t_option'HTH-- Richard Huxton Archonet Ltd NETsolutions Asia Limited +66 (2) 237 7247
[GENERAL] Header meaning for pg_dump
Dear All, I had open the backup file of PostgreSQL created by pg_dump command. I found that pg_dump make a comment line as header for each module it backup. I try to understand the meaning of value contained on header for my sample header TOC entry 1427 (class 1259 OID 1216127)-- Dependencies: 7-- Name: t_option; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- Could you please advise me for What does they mean? and What the value used for? Thank You. NETsolutions Asia Limited +66 (2) 237 7247
[GENERAL] lo function changed in PostgreSQL 8.1.1 (ask again)
From contrib/lo I found that it has something difference between old and new version of PostgreSQL. And I'm sure that I already tick on Large Object (lo) option when I install. How can I manage on difference function? :::New Version::: DOMAIN lo AS pg_catalog.oid; FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE; FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C; :::Old Version::: FUNCTION lo_in(cstring) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION lo_out(lo) RETURNS cstring AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; TYPE lo ( INTERNALLENGTH = 4, EXTERNALLENGTH = variable, INPUT = lo_in, OUTPUT = lo_out); FUNCTION lo_oid(lo) RETURNS oid AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION oid(lo) RETURNS oid AS '$libdir/lo', 'lo_oid' LANGUAGE C IMMUTABLE STRICT; CAST (lo as oid) WITH FUNCTION oid(lo) AS IMPLICIT; FUNCTION lo(oid) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; CAST (oid as lo) WITH FUNCTION lo(oid) AS IMPLICIT; FUNCTION lo_manage() RETURNS trigger AS '$libdir/lo' LANGUAGE C;
[GENERAL] lo function changed in PostgreSQL 8.1.1
Dear All, I use '$libdir/lo' for manage myLarge Object for PostgreSQL 8.0.4 . Now I try to up my PostgreSQL to new version as 8.1.1 but I think I got some error about lo (Large Object). lo (Large Object)function that normally shown in function list now disappear. I'm sure that I already check on Large Object box whenI install.I found some information on http://www.postgresql.org/docs/8.1/interactive/lo-interfaces.html#AEN26978about lo function. PostgreSQL has many new lo function that I never use in version 8.0.4 and some function I have use is not found (lo_in , lo_out). Could you please advise me how to manage lo (Large Object) in PostgreSQL 8.1.1? (Normally I use VB6 as develop tool.) And I'm not sure about my old lo (Large Object) data. How can I restore it for use inPostgreSQL 8.1.1? Thank you
Re: [GENERAL] How to delete Large Object from Database?
The lib I use is call lo_in and lo_out for manage BLOB. I understand that lo_unlink be related with lo_import and lo_export so I don't think that it work. Douglas McNaught [EMAIL PROTECTED] 07-Oct-05 20:13:36 pm Moises Alberto Lindo Gutarra [EMAIL PROTECTED] writes: try: delete from pg_catalog.pg_largeobject; from your database.Umm, the standard way to do this is lo_unlink(). You can put anON DELETE trigger on your referencing table that calls this function.-Doug
Re: [GENERAL] How to delete Large Object from Database?
I found loid column in pg_catalog.pg_largeobject. I try to match loid with any numbers in oidcolumn and lo column but I cannot found some that matched. What think I do that wrong? Please advise Moises Alberto Lindo Gutarra [EMAIL PROTECTED] 07-Oct-05 18:40:12 pm try: delete from pg_catalog.pg_largeobject;from your database.2005/10/6, Premsun Choltanwanich [EMAIL PROTECTED]: Dear All, I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine for me to get and put Large Object from and to database. However I found something that may not correct when I try to backup my data. It seem that I cannot delete Large Object from database. It seem the thing I can do is only delete the reference oid from table but Object still in database. A Detail shown below is reason I think that I cannot delete Large Object out of database. 14MB of file size when BackUp Data (BLOB Included) 900KB of file size when BackUp Data (BLOB Excluded) 13MB of file size when BackUp Data after delete all data from table that has 'lo' column. (BLOB Included) So,How to delete Large Object from Database? or Is my understanding wrong?--AtteMoises Alberto Lindo GutarraConsultor y Desarrollador Java / Open SourceTUMI Solutions SACTel: +51.13481104Cel: +51.197366260MSN : [EMAIL PROTECTED]
Re: [GENERAL] How to delete Large Object from Database?
Excuse me, How? I try to find the way but not found. Douglas McNaught [EMAIL PROTECTED] 10-Oct-05 20:07:57 pm "Premsun Choltanwanich" [EMAIL PROTECTED] writes: The lib I use is call lo_in and lo_out for manage BLOB. I understand that lo_unlink be related with lo_import and lo_export so I don't think that it work.It's exactly what you want--it removes a LO with the specified OIDfrom the large object table. The 'contrib/lo' interface is just a thinwrapper around the base LO calls.-Doug
[GENERAL] How to delete Large Object from Database?
Dear All, I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine for me to get and put Large Object from and to database. However I found something that may not correct when I try to backup my data. It seem that I cannot delete Large Object from database. It seem the thing I can do is only delete the reference oid from table but Object still in database. A Detail shown below is reason I think that I cannot delete Large Object out of database. 14MB of file size when BackUp Data (BLOB Included) 900KB of file size when BackUp Data (BLOB Excluded) 13MB of file size when BackUp Data after delete all data from table that has'lo' column.(BLOB Included) So,How to delete Large Object from Database? or Is my understanding wrong?
[GENERAL] How to secure PostgreSQL Data for distribute?
Dear All, Ineed todistribute my applicationthat use PostgreSQL as databaseto my customer. But I still have some questions in my mindon database security. I understand that everybody who get my application databasewill be have a full control permission on my database in case thatPostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it allsecure? Please advise.
[GENERAL] How can I recovery old Data from files and folders on windows?
I got some problem on PostgreSQL 8 for windows so I uninstall and reinstall it again. Before I uninstall PostgreSQL 8 I already backup all files and folders (copy all to other place). The problem is how can I restore by use files and folders that I already backup. If I try to restore by put all of it back it will be make a same error. I just want to restore only DATA (databases, functions, views, users, group etc). Please suggest me that How can I recovery old Data from files and folders on windows? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How can I recovery old Data from files and
My problem is I cannot start PostgreSQL service on Windows 2000 Advance Server. For more information it's work fine for all 3 - 4 months ago. The error message that show when I try to start PostgreSQL service is: Microsoft Management Console Could not start the PostgreSQL Database Server 8.0-beta1 service on Local Computer. The service did not return an error. This could be an internal Windows error or an internal service error. If the problem persists, contact your system administrator. So I cannot restore my erroneous version and dump my data for reinstall. Could you give me some suggestion for correct this problem? FYI: The ways I already done are - reinstall with out uninstall the old one. - reinstall with uninstall the old one first. then copy the old data directory to the new one - reinstall with uninstall the old one first. then copy the old files by try to compare between the old one and new one file by file. Markus Wollny [EMAIL PROTECTED] 7/12/2004 5:14:02 pm Hi! You can't. You'll have to restore your erroneous version first, then dump your data, the reinstall and use restore to restore the data you want. Kind regards Markus -Urspr?ngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Premsun Choltanwanich Gesendet: Dienstag, 7. Dezember 2004 10:49 An: [EMAIL PROTECTED] Betreff: [GENERAL] How can I recovery old Data from files and folders on windows? I got some problem on PostgreSQL 8 for windows so I uninstall and reinstall it again. Before I uninstall PostgreSQL 8 I already backup all files and folders (copy all to other place). The problem is how can I restore by use files and folders that I already backup. If I try to restore by put all of it back it will be make a same error. I just want to restore only DATA (databases, functions, views, users, group etc). Please suggest me that How can I recovery old Data from files and folders on windows? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How to make lo_import and lo_export to use file on client local machine?
I try to use lo_import and lo_export for manage large object on PostgreSQL. I found that all files must be process on server with these commands. Could I make lo_import and lo_export to use file on client local machine? And How? Or Is it has other ways to manage large object that process is between client local machine and PostgreSQL database? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to make lo_import and lo_export to use file
I'm new for PostgreSQL. How can I check that what thing I use? Tom Lane [EMAIL PROTECTED] 19/11/2004 10:02:44 pm Premsun Choltanwanich [EMAIL PROTECTED] writes: Could I make lo_import and lo_export to use file on client local machine? If you're using libpq, it has lo_export and lo_import functions. If you're using some other client-side code, see its documentation. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
