Bug#611352: pg_restore does not import all data as expected
Hi Philipp, let's recap what I did. I have an application (DMS) that store images as large objects into the database. All tables used by the application are inside a single schema. A database usually host many application instances in many different schemas. (So, as I now understand, all large objects are mixed together into the same pg_public schema.) Now, I tried to move one application instance from one database in lenny to one database in squeeze. So, I exported the complete database with pg_dump and imported only one schema using pg_restore with option --schema. The resulted database have all tables, all records, but no large objects. You say this is not a bug of pg_restore, I think it is. Anyway, could you tell me how I should have done such migration? Thank you very much, Giuseppe -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Bug#611352: pg_restore does not import all data as expected
Giuseppe, am Mon, Jan 31, 2011 at 11:24:24AM +0100 hast du folgendes geschrieben: let's recap what I did. I have an application (DMS) that store images as large objects into the database. All tables used by the application are inside a single schema. A database usually host many application instances in many different schemas. (So, as I now understand, all large objects are mixed together into the same pg_public schema.) Now, I tried to move one application instance from one database in lenny to one database in squeeze. So, I exported the complete database with pg_dump and imported only one schema using pg_restore with option --schema. The resulted database have all tables, all records, but no large objects. You say this is not a bug of pg_restore, I think it is. Anyway, could you tell me how I should have done such migration? blobs are a contributed module and not part of pgsql proper. It's not a bug that was caused by upgrading the package, but instead you did a manual copy. Normally folks copy a whole database. If you restrict your import, you have to make sure that all relevant data is copied. If you don't copy all relevant data, you lose. I don't say it's not a bug, I just say it's wishlist. It's a missing feature of pg_restore to detect a contributed module in use and warn that data in another schema is indirectly referenced. You need to install the lo module by manual action. The documentation even says this: This appendix contains information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness. That said: I don't think there's a sane way to extract blobs of different schemas just using pg_dump and pg_restore. You either restore pg_catalog along with the schema you want or you import the whole database and drop superfluous schemas. You can also actually *look* at the dump and look for the blobs and add the part that's needed to the database afterwards. The bug should also be filed upstream. Please note: I'm not the maintainer of the package. However, as this was filed at RC severity, I had a look. And it's not RC. Kind regards Philipp Kern signature.asc Description: Digital signature
Bug#611352: pg_restore does not import all data as expected
Hello Giuseppe, Giuseppe Sacco [2011-01-31 11:24 +0100]: Now, I tried to move one application instance from one database in lenny to one database in squeeze. So, I exported the complete database with pg_dump and imported only one schema using pg_restore with option --schema. Did you use --format for this? The SQL (plain text) format isn't supposed to have blobs, as you cannot sensibly represent them. The custom and tar formats can deal with blobs. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Bug#611352: pg_restore does not import all data as expected
Il giorno lun, 31/01/2011 alle 11.49 +0100, Martin Pitt ha scritto: Hello Giuseppe, Giuseppe Sacco [2011-01-31 11:24 +0100]: Now, I tried to move one application instance from one database in lenny to one database in squeeze. So, I exported the complete database with pg_dump and imported only one schema using pg_restore with option --schema. Did you use --format for this? The SQL (plain text) format isn't supposed to have blobs, as you cannot sensibly represent them. The custom and tar formats can deal with blobs. I think I used custom: giuseppe@titanium4:~$ file dump-20100528.pgdump dump-20100528.pgdump: PostgreSQL custom database dump - v1.10-0 Bye, Giuseppe -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Bug#611352: pg_restore does not import all data as expected
severity 611352 normal On Fri, Jan 28, 2011 at 01:20:59PM +0100, Giuseppe Sacco wrote: For one of these databases, I select to only import only one schema instead of importing the whole database. The schema include a tables with blobs, i.e., a field of type OID referring to a blob. Did you import pg_catalog? Because that's where your blobs are stored. If you unselect this, the export and subsequent import will succeed due to the OIDs being valid and due to a missing foreign key relationship when used with large objects. However they will be dangling. I thus don't agree that this is utterly broken. PostgreSQL could do better with a check for large objects, and presenting a warning if pg_catalog isn't selected, but as a DBA I guess you need to know that fact if you handle large objects. Kind regards and thanks for the bug report Philipp Kern signature.asc Description: Digital signature
Bug#611352: pg_restore does not import all data as expected
Il giorno sab, 29/01/2011 alle 17.36 +0100, Philipp Kern ha scritto: [...] Did you import pg_catalog? Because that's where your blobs are stored. If you unselect this, the export and subsequent import will succeed due to the OIDs being valid and due to a missing foreign key relationship when used with large objects. However they will be dangling. I imported only one schema using pg_restore with option --schema. I thus don't agree that this is utterly broken. PostgreSQL could do better with a check for large objects, and presenting a warning if pg_catalog isn't selected, but as a DBA I guess you need to know that fact if you handle large objects. well, no, I still think it is an important bug, since not everyone working with postgresql need to know how it works internally. Bye, Giuseppe -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Bug#611352: pg_restore does not import all data as expected
On Sat, Jan 29, 2011 at 06:49:27PM +0100, Giuseppe Sacco wrote: Il giorno sab, 29/01/2011 alle 17.36 +0100, Philipp Kern ha scritto: [...] Did you import pg_catalog? Because that's where your blobs are stored. If you unselect this, the export and subsequent import will succeed due to the OIDs being valid and due to a missing foreign key relationship when used with large objects. However they will be dangling. I imported only one schema using pg_restore with option --schema. Meh, so it wasn't the package's doing? I thus don't agree that this is utterly broken. PostgreSQL could do better with a check for large objects, and presenting a warning if pg_catalog isn't selected, but as a DBA I guess you need to know that fact if you handle large objects. well, no, I still think it is an important bug, since not everyone working with postgresql need to know how it works internally. True. But it's not a bug in the package if your manual data conversion goes wrong. Kind regards Philipp Kern signature.asc Description: Digital signature
Bug#611352: pg_restore does not import all data as expected
Package: postgresql-client-8.4 Version: 8.4.5-0squeeze2 Severity: grave Hi all, I just moved from an old lenny machine to a new squeeze (testing) one, and moved a few postgresql databases from 8.3 to 8.4 as these are the shipped versions on Debian. For one of these databases, I select to only import only one schema instead of importing the whole database. The schema include a tables with blobs, i.e., a field of type OID referring to a blob. The import did not produce any error and the application using the database was running correctly... until accessing the blob. Then I discovered that no blob was imported. The table was correctly filled with all OIDs, but the real blobs were not imported at all. I believe this is a grave bug since there is warning to the user, and not all data were restored. Bye, Giuseppe -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
