Обсуждение: BUG #2386: pg_restore doesn't restore large objects
The following bug has been logged online: Bug reference: 2386 Logged by: Patrick Headley Email address: LinxConsulting@comcast.net PostgreSQL version: 8.0.4 + Operating system: Windows XP Description: pg_restore doesn't restore large objects Details: I have been trying to restore some PostgreSQL databases with a single large object in them. By single I mean a single table with one lo and only one record. Using the pg_restore utility that ships with the Windows version of PGAdmin III v1.4.1 and 1.4.2 didn't work. I was finally able to restore the databases with the pg_restore that was on the Mac OS X machine hosting the PostgreSQL Server. Postgres was compiled and installed by using the Fink project. The version on that machine is 8.1.x. Backups don't seem to be the problem as I was able to make a backup using the Windows version of pg_backup that was shipped with PGAdmin III v1.4.2. I also tried v1.4.1 and though I didn't realize it was a restore problem the backups didn't error out in any way. The restore operation did error out in the same way. I tried restoring by using the pg_restore that ships with v1.4.1 and 1.4.2 of PGAdmin III on both a Windows XP machine to a Mac OS X 10.4 server hosting PostgreSQL v8.0.7 and on a Windows Server 2003 hosting it's own Postgres server v8.0.4 and from the Windows XP machine to the Windows Server 2003 machine. None of those combinations worked. What finally worked was logging onto the Mac OS X machine and running pg_restore from the bin directory. That machine has a Fink compiled and installed version of PostgreSQL. I don't know yet if the restore will work on the G4 machines but suspect that it will. It just seems to be something to do with the Windows dll. I thgought I saw something on a custom pg_restore but I don't remember where I saw that. Maybe I was using a custom pg_restore without knowing it. If so, and if this issue isn't a bug I appologize. However, I searched on the Internet for several hours while trying to figure out what to do and cannot find anything regarding this problem. Please let me know if there is something that I may have done wrong or if you can reproduce the symptom. Patrick Headley.
"Patrick Headley" <LinxConsulting@comcast.net> writes: > Description: pg_restore doesn't restore large objects At no point did you show us exactly what you did or exactly what went wrong, so even though this report has a lot of version-number details, it's just about useless :-(. Please see the reporting suggestions at http://www.postgresql.org/docs/8.1/static/bug-reporting.html regards, tom lane
I'm a bit hurt by your statement that what I sent was just about useless :( The problem here is that I am new to PostgreSQL and PGAdmin III and so, in my confusion about what's normal and what's not, I am unable to provide you with all the details that would help you resolve the problem. However, I tried to be clear about what actions didn't work and those that did. Just as a point of reference, I was essentially thrown into the world of PostgreSQL where the installations were incomplete and the databases were poorly designed so the learning curve has been short and steep. So, let me try to explain this again. I recently added an LO object to a database using Peter Mount's LO type. So far, that's working. Yesterday, I made a backup of the database in order to restore it onto my test server. I used PGAdmin III to do the backup and it worked OK. Due to the problems I'm having with the restore, I tried the backup from two Mac OS X G4 servers and one Mac OS X Intel Dou server. All the backups were run from PGAdmin III and they all seem to work. I didn't attempt to restore every backup from every machine but they all ran the same and no error messages appeared. When I try to restore the backup using PGAdmin III, the log window begins to fill up. Near the end, when it should say it's restoring the BLOBS an error message appears stating the BLOBS couldn't be restored. I don't have the exact text of the message but I could get it for you if needed. I even created a test database with one table and two fields. The fields were recordid and logo (the LO type field). I couldn't even get this database to restore using PGAdmin III. The point here is that it doesn't matter which server I tried to restore too or which database I used (as long as it had at least one large object stored in it), if I used PGAdmin III, the same error message appeared at the same place in the process. However, if I restored the backup by opening a command or terminal window and ran the command from the command line, it worked. You should have no problem reproducing the same error message that I received. If you don't see the same problem, let me know and the next time I go to do a restore I'll get the details for you. By the way, when I put the backup file on one of the Macs and then ran the restore using the command line from the Mac Terminal window I was only prompted for a password once. However, when restoring the backup onto the Windows 2003 server I was prompted for the password at the beginning of the process and then just before restoring the BLOBs. Don't know how this might be related by I thought I would let you know. If you are unable to reproduce the problem by simply attempting to restore a backup of a database that has some LO data stored in it, let me know and I'll start from scratch and send you all the details that I can come up with. Patrick Headley Linx Consulting, Inc. (303) 916-5522 LinxConsulting@comcast.net www.linxco-inc.com -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, April 11, 2006 2:14 PM To: Patrick Headley Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects "Patrick Headley" <LinxConsulting@comcast.net> writes: > Description: pg_restore doesn't restore large objects At no point did you show us exactly what you did or exactly what went wrong, so even though this report has a lot of version-number details, it's just about useless :-(. Please see the reporting suggestions at http://www.postgresql.org/docs/8.1/static/bug-reporting.html regards, tom lane
I think our problem is that we understand the backend very well, but not how pgadmin does this operation. --------------------------------------------------------------------------- Patrick Headley wrote: > I'm a bit hurt by your statement that what I sent was just about useless :( > The problem here is that I am new to PostgreSQL and PGAdmin III and so, in > my confusion about what's normal and what's not, I am unable to provide you > with all the details that would help you resolve the problem. However, I > tried to be clear about what actions didn't work and those that did. Just as > a point of reference, I was essentially thrown into the world of PostgreSQL > where the installations were incomplete and the databases were poorly > designed so the learning curve has been short and steep. > So, let me try to explain this again. > > I recently added an LO object to a database using Peter Mount's LO type. So > far, that's working. Yesterday, I made a backup of the database in order to > restore it onto my test server. I used PGAdmin III to do the backup and it > worked OK. Due to the problems I'm having with the restore, I tried the > backup from two Mac OS X G4 servers and one Mac OS X Intel Dou server. All > the backups were run from PGAdmin III and they all seem to work. I didn't > attempt to restore every backup from every machine but they all ran the same > and no error messages appeared. > > When I try to restore the backup using PGAdmin III, the log window begins to > fill up. Near the end, when it should say it's restoring the BLOBS an error > message appears stating the BLOBS couldn't be restored. I don't have the > exact text of the message but I could get it for you if needed. I even > created a test database with one table and two fields. The fields were > recordid and logo (the LO type field). I couldn't even get this database to > restore using PGAdmin III. The point here is that it doesn't matter which > server I tried to restore too or which database I used (as long as it had at > least one large object stored in it), if I used PGAdmin III, the same error > message appeared at the same place in the process. However, if I restored > the backup by opening a command or terminal window and ran the command from > the command line, it worked. You should have no problem reproducing the same > error message that I received. If you don't see the same problem, let me > know and the next time I go to do a restore I'll get the details for you. > > By the way, when I put the backup file on one of the Macs and then ran the > restore using the command line from the Mac Terminal window I was only > prompted for a password once. However, when restoring the backup onto the > Windows 2003 server I was prompted for the password at the beginning of the > process and then just before restoring the BLOBs. Don't know how this might > be related by I thought I would let you know. > > If you are unable to reproduce the problem by simply attempting to restore a > backup of a database that has some LO data stored in it, let me know and > I'll start from scratch and send you all the details that I can come up > with. > > Patrick Headley > Linx Consulting, Inc. > (303) 916-5522 > LinxConsulting@comcast.net > www.linxco-inc.com > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Tuesday, April 11, 2006 2:14 PM > To: Patrick Headley > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects > > "Patrick Headley" <LinxConsulting@comcast.net> writes: > > Description: pg_restore doesn't restore large objects > > At no point did you show us exactly what you did or exactly what went > wrong, so even though this report has a lot of version-number details, > it's just about useless :-(. Please see the reporting suggestions at > http://www.postgresql.org/docs/8.1/static/bug-reporting.html > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
pgAdmin just uses pg_dump/pg_restore to handle the heavy lifting. Regards, Dave.=20 > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org=20 > [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Bruce Momjian > Sent: 13 April 2006 20:40 > To: Patrick Headley > Cc: 'Tom Lane'; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore=20 > large objects >=20 >=20 > I think our problem is that we understand the backend very=20 > well, but not how pgadmin does this operation. >=20 > -------------------------------------------------------------- > ------------- >=20 > Patrick Headley wrote: > > I'm a bit hurt by your statement that what I sent was just about=20 > > useless :( The problem here is that I am new to PostgreSQL=20 > and PGAdmin=20 > > III and so, in my confusion about what's normal and what's=20 > not, I am=20 > > unable to provide you with all the details that would help=20 > you resolve=20 > > the problem. However, I tried to be clear about what actions didn't=20 > > work and those that did. Just as a point of reference, I was=20 > > essentially thrown into the world of PostgreSQL where the=20 > > installations were incomplete and the databases were poorly=20 > designed so the learning curve has been short and steep. > > So, let me try to explain this again. > >=20 > > I recently added an LO object to a database using Peter Mount's LO=20 > > type. So far, that's working. Yesterday, I made a backup of the=20 > > database in order to restore it onto my test server. I used PGAdmin=20 > > III to do the backup and it worked OK. Due to the problems=20 > I'm having=20 > > with the restore, I tried the backup from two Mac OS X G4=20 > servers and=20 > > one Mac OS X Intel Dou server. All the backups were run=20 > from PGAdmin=20 > > III and they all seem to work. I didn't attempt to restore every=20 > > backup from every machine but they all ran the same and no=20 > error messages appeared. > >=20 > > When I try to restore the backup using PGAdmin III, the log window=20 > > begins to fill up. Near the end, when it should say it's=20 > restoring the=20 > > BLOBS an error message appears stating the BLOBS couldn't=20 > be restored.=20 > > I don't have the exact text of the message but I could get=20 > it for you=20 > > if needed. I even created a test database with one table and two=20 > > fields. The fields were recordid and logo (the LO type field). I=20 > > couldn't even get this database to restore using PGAdmin III. The=20 > > point here is that it doesn't matter which server I tried=20 > to restore=20 > > too or which database I used (as long as it had at least one large=20 > > object stored in it), if I used PGAdmin III, the same error message=20 > > appeared at the same place in the process. However, if I=20 > restored the=20 > > backup by opening a command or terminal window and ran the command=20 > > from the command line, it worked. You should have no problem=20 > > reproducing the same error message that I received. If you=20 > don't see the same problem, let me know and the next time I=20 > go to do a restore I'll get the details for you. > >=20 > > By the way, when I put the backup file on one of the Macs=20 > and then ran=20 > > the restore using the command line from the Mac Terminal=20 > window I was=20 > > only prompted for a password once. However, when restoring=20 > the backup=20 > > onto the Windows 2003 server I was prompted for the password at the=20 > > beginning of the process and then just before restoring the BLOBs.=20 > > Don't know how this might be related by I thought I would=20 > let you know. > >=20 > > If you are unable to reproduce the problem by simply attempting to=20 > > restore a backup of a database that has some LO data stored=20 > in it, let=20 > > me know and I'll start from scratch and send you all the=20 > details that=20 > > I can come up with. > >=20 > > Patrick Headley > > Linx Consulting, Inc. > > (303) 916-5522 > > LinxConsulting@comcast.net > > www.linxco-inc.com > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Tuesday, April 11, 2006 2:14 PM > > To: Patrick Headley > > Cc: pgsql-bugs@postgresql.org > > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large=20 > > objects > >=20 > > "Patrick Headley" <LinxConsulting@comcast.net> writes: > > > Description: pg_restore doesn't restore large objects > >=20 > > At no point did you show us exactly what you did or exactly=20 > what went=20 > > wrong, so even though this report has a lot of=20 > version-number details,=20 > > it's just about useless :-(. Please see the reporting=20 > suggestions at=20 > > http://www.postgresql.org/docs/8.1/static/bug-reporting.html > >=20 > > regards, tom lane > >=20 > >=20 > > ---------------------------(end of=20 > > broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > >=20 >=20 > --=20 > Bruce Momjian http://candle.pha.pa.us > EnterpriseDB http://www.enterprisedb.com >=20 > + If your life is a hard drive, Christ can be your backup. + >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org=20 > so that your > message can get through to the mailing list cleanly >=20
Dave Page wrote: > pgAdmin just uses pg_dump/pg_restore to handle the heavy lifting. > >>> When I try to restore the backup using PGAdmin III, the log >>> window begins to fill up. Near the end, when it should say it's >>> restoring the BLOBS an error message appears stating the BLOBS >>> couldn't be restored. This is the original output from pg_restore. pgAdmin will also log the precise parameters used to restore in the first log line, it might help to see those. Regards, Andreas
"Patrick Headley" <linxconsulting@comcast.net> writes:
> I then switched over to a Windows Server 2003 machine with PostgreSQL 8.0.4
> installed and used pg_restore from the PostgreSQL bin folder. The version
> for pg_restore on that machine is v8.0.4.5277. On the Windows 2003 machine
> the restore worked. Only thing is that I lost a lot of the output from
> pg_restore that appeared in the command window because the lines scrolled
> out of the display buffer. However, at the point where the error occurred on
> the Windows XP machine the Windows 2003 machine prompted me for the password
> a second time.
Hm, that's interesting. IIRC, pre-8.1 pg_restore uses a second
connection while trying to restore blobs. It sounds like you're
describing some sort of failure to establish that second connection.
Do the two servers have identical pg_hba.conf setups?
regards, tom lane
Andreas, I had another opportunity to do a restore for a client today. Both restore attempts that I'll be describing were to a remote Mac OS X machine with PostgreSQL 8.0.7 installed. Knowing that it wasn't going to work using PGAdmin III, I went directly to a command window on the machine with PGAdmin III v1.4.2 installed. The version of pg_restore on that machine is 8.1.3.6044. That machine is running Windows XP SP2. The following command, which didn't work was taken directly from the command window. I've removed the IP address and password for security reasons. C:\Program Files\pgAdmin III\1.4>pg_restore -i -h xx.xx.xx.xx -p 5432 -U password -d mtviewDEV -v C:\mtviewDEV.backup The restore errored out with the following lines: pg_restore: restoring large object data pg_restore: [archiver] could not create large object 28305 pg_restore: *** aborted because of error I then switched over to a Windows Server 2003 machine with PostgreSQL 8.0.4 installed and used pg_restore from the PostgreSQL bin folder. The version for pg_restore on that machine is v8.0.4.5277. On the Windows 2003 machine the restore worked. Only thing is that I lost a lot of the output from pg_restore that appeared in the command window because the lines scrolled out of the display buffer. However, at the point where the error occurred on the Windows XP machine the Windows 2003 machine prompted me for the password a second time. It then reported that it was restoring the large objects and then proceeded to restore other items in the database. Hope that provides some insights into the cause of the problem. Patrick Headley Linx Consulting, Inc. (303) 916-5522 LinxConsulting@comcast.net www.linxco-inc.com -----Original Message----- From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] Sent: Friday, April 14, 2006 3:52 AM To: Dave Page Cc: Bruce Momjian; Patrick Headley; Tom Lane; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects Dave Page wrote: > pgAdmin just uses pg_dump/pg_restore to handle the heavy lifting. > >>> When I try to restore the backup using PGAdmin III, the log >>> window begins to fill up. Near the end, when it should say it's >>> restoring the BLOBS an error message appears stating the BLOBS >>> couldn't be restored. This is the original output from pg_restore. pgAdmin will also log the precise parameters used to restore in the first log line, it might help to see those. Regards, Andreas