pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres 7.2.1 pg_restore large objects
7+ messages / 4 participants
[nested] [flat]

* postgres 7.2.1 pg_restore large objects
@ 2005-03-29 18:53 Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-03-30 18:26 ` postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  0 siblings, 1 reply; 7+ messages in thread

From: Dario Pudlo @ 2005-03-29 18:53 UTC (permalink / raw)
  To: pgsql-admin

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 think I finally got the "right" way to do it.
I'm running this as OS 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.
/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. I don use it. 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
        before or 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 abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your 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
----------------------------------

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
tried to update, 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 manual 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)!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* postgres 7.2.1 pg_restore large objects
  2005-03-29 18:53 postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
@ 2005-03-30 18:26 ` Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-04-03 10:43   ` Re: postgres 7.2.1 pg_restore large objects Juan Miguel <juanmime@ono.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Dario Pudlo @ 2005-03-30 18:26 UTC (permalink / raw)
  To: pgsql-admin


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
        before or 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 abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your 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)!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: postgres 7.2.1 pg_restore large objects
  2005-03-29 18:53 postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-03-30 18:26 ` postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
@ 2005-04-03 10:43   ` Juan Miguel <juanmime@ono.com>
  2005-06-02 19:05     ` Re: postgres 7.2.1 pg_restore large objects Dario <dario_d_s@unitech.com.ar>
  2006-01-11 15:22     ` Re: postgres 7.2.1 pg_restore large objects Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Juan Miguel @ 2005-04-03 10:43 UTC (permalink / raw)
  To: Dario Pudlo <dariop_d_s@unitech.com.ar>; +Cc: pgsql-admin

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=iso-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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 &gt; base.tar 2&gt;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&gt;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&gt;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
        before or 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 abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your 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&ntilde;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 headaches in 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 copy in
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 production system):</b><br>
<br>
1. I dump the database.<br>
<br>
pg_dump -Ft -b -o -U postgres databasename &gt; 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 super user 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 with lo types in 7.4 and 7.5, and
dumping and restoring in these versions works fine for me (in those
cases I restore the full database in one step, structure and data at
same time). I only need to restore first structure and after data
without triggers, when the dump is from 7.2 version.<br>
<br>
Phearps are missing some parameters in the restore and dumping process,
but basically are that I have written.<br>
<br>
I hope my experience will be usefull for you.<br>
<br>
</body>
</html>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: postgres 7.2.1 pg_restore large objects
  2005-03-29 18:53 postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-03-30 18:26 ` postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-04-03 10:43   ` Re: postgres 7.2.1 pg_restore large objects Juan Miguel <juanmime@ono.com>
@ 2005-06-02 19:05     ` Dario <dario_d_s@unitech.com.ar>
  1 sibling, 0 replies; 7+ messages in thread

From: Dario @ 2005-06-02 19:05 UTC (permalink / raw)
  To: pgsql-admin

Regarding this old subject, we moved to 8.0.2. Updating to 7.2.5 didn't
solved the issue (I could not find an 7.2.7 rpm release for Red hat 7.2.
since that's the plataform already installed by client, I had to stick to
that). The upgrade to 8.0.2 was done in a Red Hat 9.0 system.

Import of 'lo' was now succesfully.

Greetings


---
I'll resume here steps taken to upgrade(not recommended, just taken for
compatibility with a VB6 app with ODBC)
-----------------------------------------------------
usefull changes in postgresql.conf
regex_flavor = advanced	# advanced, extended, or basic
default_with_oids = true
-----------------------------------------------------
createdb --encoding SQL-ANSI BASEDEDATOS               (for import from 7.2
database)
-----------------------------------------------------
our app use this datatype:
CREATE DOMAIN datetime AS timestamp
---------------------------------------------------------------
From the docs: An empty string ('') is no longer allowed as the input into
an integer field. Formerly, it was silently interpreted as 0.

This kind of insertion:
INSERT INTO rca_crt (order) VALUES ('')
had to be changed.


-----Mensaje original-----
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]En nombre de Juan Miguel
Enviado el: domingo, 03 de abril de 2005 7:44
Para: Dario Pudlo
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] postgres 7.2.1 pg_restore large objects


Dario Pudlo wrote:
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
        before or 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 abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your 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
espanol)!


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


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.
Well, I remember that initially restoring tables with lo type columns made
me headaches in 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 copy in other machine in
7.5. This is the process that I use to dump and restore the database.

In 7.2 (the production system):

1. I dump the database.

pg_dump -Ft -b -o -U postgres databasename > dbdump_date.tar

Then in the testing machine (7.5) I do this steps:

1. Create de database

createdatabase -U dbusername databasename

2.- Restore only the structure

/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 super user 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


This is the method that I use for dumping and restoring, and works fine for
me. I have serveral databases with lo types in 7.4 and 7.5, and dumping and
restoring in these versions works fine for me (in those cases I restore the
full database in one step, structure and data at same time). I only need to
restore first structure and after data without triggers, when the dump is
from 7.2 version.

Phearps are missing some parameters in the restore and dumping process, but
basically are that I have written.

I hope my experience will be usefull for you.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: postgres 7.2.1 pg_restore large objects
  2005-03-29 18:53 postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-03-30 18:26 ` postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
  2005-04-03 10:43   ` Re: postgres 7.2.1 pg_restore large objects Juan Miguel <juanmime@ono.com>
@ 2006-01-11 15:22     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2006-01-11 15:22 UTC (permalink / raw)
  To: Juan Miguel <juanmime@ono.com>; +Cc: tsirkin@gmail.com; pgsql-admin

Juan Miguel <juanmime@ono.com> writes:
> DEBUG:  server process (pid 25276) was terminated by signal 11

Looks like you have run into a Postgres bug.  My advice is to update to
a newer PG version ... 7.2.1 is ancient.  So ancient that we stopped
supporting the entire 7.2 branch some time ago.  It might be that this
problem is fixed in the last release in the 7.2 branch (7.2.8), but
really you need to be on something newer than 7.2.  There isn't anyone
here who is going to take an interest in fixing bugs in 7.2.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* postgres 7.2.1 pg_restore large objects
@ 2005-03-30 21:41 Dario <dario_d_s@unitech.com.ar>
  2005-03-31 04:21 ` Re: postgres 7.2.1 pg_restore large objects Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Dario @ 2005-03-30 21:41 UTC (permalink / raw)
  To: pgsql-admin

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
         before or 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 abnormally and possibly corrupted shared memory.
         I have rolled back the current transaction and am
         going to terminate your 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)!



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: postgres 7.2.1 pg_restore large objects
  2005-03-30 21:41 postgres 7.2.1 pg_restore large objects Dario <dario_d_s@unitech.com.ar>
@ 2005-03-31 04:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2005-03-31 04:21 UTC (permalink / raw)
  To: Dario <dario_d_s@unitech.com.ar>; +Cc: pgsql-admin

Dario <dario_d_s@unitech.com.ar> writes:
> 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.  

If the release notes between 7.2.1 and 7.2.7 did not curl your toes,
then you didn't understand what you were reading.  There are numerous
serious data-loss-grade bugs in 7.2.1.

Update to 7.2.7 first and then see if you still have trouble loading
this dump.  If you do, I'd be interested to see more details ... but
7.2.1 isn't supportable.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-01-11 15:22 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-29 18:53 postgres 7.2.1 pg_restore large objects Dario Pudlo <dariop_d_s@unitech.com.ar>
2005-03-30 18:26 ` Dario Pudlo <dariop_d_s@unitech.com.ar>
2005-04-03 10:43   ` Juan Miguel <juanmime@ono.com>
2005-06-02 19:05     ` Dario <dario_d_s@unitech.com.ar>
2006-01-11 15:22     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-30 21:41 postgres 7.2.1 pg_restore large objects Dario <dario_d_s@unitech.com.ar>
2005-03-31 04:21 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox