Re: postgres 7.2.1 pg_restore large objects - Mailing list pgsql-admin
From | Juan Miguel |
---|---|
Subject | Re: postgres 7.2.1 pg_restore large objects |
Date | |
Msg-id | 424FC8E2.8010502@ono.com Whole thread Raw |
In response to | postgres 7.2.1 pg_restore large objects ("Dario Pudlo" <dariop_d_s@unitech.com.ar>) |
Responses |
Re: postgres 7.2.1 pg_restore large objects
Re: postgres 7.2.1 pg_restore large objects |
List | pgsql-admin |
Dario Pudlo wrote: <blockquote cite="midMHEDJHCKDNOEHJKHIOCJAEAKCBAA.dariop_d_s@unitech.com.ar" type="cite"><pre wrap="">Problem:Not being able to restore a database with lo. I have already read forums and doc. Probably I'm loosing something, but I just can figure. Reading forums I though I finally got a "right" way to do it. I'm running this as OS user "postgres" which is a super user pg_dump -Ft -o -b base > base.tar 2>log_err2 # If I untar the file, i see table data, and blob data, toc and restore.sql. createdb base2 //from OS shell /usr/local/pgsql/bin/pg_restore -Ft -s -v -d base2 base.tar 2>elogerr #creates schema psql -f src/contrib/lo.sql base2 #creates lo type and functions... my base.tar includes lo type definition. O also tried to delete the type previously, and did not make any difference. If I do not do this, pg_restore claims that I lo and oid must be explicity casted. One more thing, I can't make lo_manage to work (trigger type doesn't exist). I don use it anyway, since I manage lo updates and delete from app. I delete blobs from app. /usr/local/pgsql/bin/pg_restore -Ft -a -v -d base2 base.tar 2>flogerr the pg_restore fails with the following error. ---------------------------------- pg_restore log (flogerr file) pg_restore: [archiver (db)] error while updating column "tca_texto" of table "tca": server closed the connection unexpectedly This probably means the server terminated abnormally beforeor while processing the request. pg_restore: *** aborted because of error ---------------------------------- postgres log file DEBUG: server process (pid 25276) was terminated by signal 11 DEBUG: terminating any other active server processes NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormallyand possibly corrupted shared memory. I have rolled back the current transaction and am going to terminateyour database system connection and exit. Please reconnect to the database system and repeat your query. DEBUG: all server processes terminated; reinitializing shared memory and semaphores ---------------------------------- No table is updated. I tried to create a table with oid column instead of lo, restore (worked in this way), I renamed table, I created a table with lo datatype, but when I tried to insert into ... , same error occurs. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. I have two different versions of postgres, one compiled by someone here, and another version, installed via rpm. unlikely, upgrading to a different version, is not an option, I probably update to 7.2.(last version), but only if this specific problem is solved. I have read the release section of the doc and did not apeared explicity... greetings. TIA to all "Hoy es un buen dia para morir" (today is a good day to die, Klingon en español)! ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings </pre></blockquote> Hello, I use lo type in several databases. I use postgres since version 6.4, and I started to use "lo"type since version 7.x.<br /> Well, I remember that initially restoring tables with lo type columns made me headachesin early 7.x versions. Nowaydays I only have a database 7.2 with lo types. I dump in 7.2, and then I restore a copyin other machine in 7.5. This is the process that I use to dump and restore the database.<br /><br /><b>In 7.2 (the productionsystem):</b><br /><br /> 1. I dump the database.<br /><br /> pg_dump -Ft -b -o -U postgres databasename > dbdump_date.tar<br/><br /><b>Then in the testing machine (7.5) I do this steps</b>:<br /><br /> 1. Create de database<br/><br /> createdatabase -U dbusername databasename<br /><br /> 2.- Restore only the structure<br /><pre wrap="">/usr/local/pgsql/bin/pg_restore-Ft -s -v -U postgres -d databasename dbdump_date.tar Why I restore the structure like postgres user ? Because, the dump file, restores the lo type in the database (only the superuser can do that), after that changes to the correct user (dbusername) for creating the structure of the database. 3.- Restore Only the data, "without triggers" /usr/local/pgsql/bin/pg_restore -Ft -a -v -X disable-triggers -U postgres -d databasename dbdump_date.tar </pre><br /> This is the method that I use for dumping and restoring, and works fine for me. I have serveral databases withlo types in 7.4 and 7.5, and dumping and restoring in these versions works fine for me (in those cases I restore thefull database in one step, structure and data at same time). I only need to restore first structure and after data withouttriggers, when the dump is from 7.2 version.<br /><br /> Phearps are missing some parameters in the restore and dumpingprocess, but basically are that I have written.<br /><br /> I hope my experience will be usefull for you.<br /><br/>
pgsql-admin by date: