I am newer to PostgreSQL than Oracle. So, I'm going to explain what I do in Oracle and then ask if anyone knows if there is a way to do this in PostgreSQL.
I have over 300 tables in Oracle. Of them, some contain LOBs. Two of the ones I know, which consume a ton of space to house PDFs, are called JP_PDFS and JP_PRELIMPDFS. When I need a copy of this database DMP to transport to someone else, I don't need the contents of these tables to do a majority of so many other troubleshooting steps. So, I can export this database in two DMP files; one with the exclude directive and one with the include + content directives:
expdp full=n schemas=mySchema directory=DMPs dumpfile=mySchema_PDFS_schema.dmp logfile=expdp1.log include=TABLE:"IN('JP_PDFS','JP_PRELIMPDFS')" content=metadata_only
expdp full=n schemas=mySchema directory=DMPs dumpfile=mySchema_noPDFS.dmp logfile=expdp2.log exclude=TABLE:"IN('JP_PDFS','JP_PRELIMPDFS')"
Unfortunately, all LOBs are stored in PG_LARGEOBJECT. The references/pointers to the actual LO rows that comprise the LOB is stored in the aforementioned tables. But, there ARE other tables with LOBs I DO need to be exported to the .backup file with pg_dump.
What I want is a way to do what I do in the Oracle world with PostgreSQL. I know how to export the schemas, only, for JP_PDFS and JP_PRELIMPDFS. But, is there a way to tell pg_dump to not include the objects from PG_LARGEOBJECT for the referenced items from both JP_PDFS and JP_PRELIMPDFS?
Thanks!
No, large objects don't “belong” to anybody. Either all of them are dumped (for a dump of the complete database ot if the -b option of pg_dump was used) or none.
Large objects are cumbersome and require a special API. If the size of your binary data doesn't exceed 1GB, consider using the data type bytea for them. That is much easier to handle and will work like you expect.