Postgresql migrate schema with large objects included, but only for this schema

user_13052711 0
Viewed 272

I have a super large database in postgresql 13, the size is 1 TB and I need to migrate only one schema to another database, the problem is that this schema has blobs. So if I migrate with pg_dump and the --blobs property, the command makes a backup of all the blobs in the database and I only want it to store only the blobs of this scheme. is this possible? this is the command i am executing to do the dump.

pg_dump --host=$HOST_ORIGIN --dbname=$BD_ORIGIN --port=$BD_PORT_ORIGIN --username=$BD_USER_ORIGIN --schema=$SCHEMA --no-privileges --blobs -v -Fc > schema.sql
pg-dump psql postgresql
1 Answers

I had this problem and I solved it like this: I created a second database with the total data. And I copied only the oids from one DB to another using:

create extension dblink;

INSERT INTO pg_catalog.pg_largeobject(loid, pageno, data)
select loid, pageno, data from dblink('dbname=dbOrigin', 'select * from pg_largeobject where loid in 
(select oid from schema.table)') as t 
( loid oid,
  pageno integer,
  data bytea
);

INSERT INTO pg_catalog.pg_largeobject_metadata(oid, lomowner, lomacl)
select oid, lomowner, lomacl from dblink('dbname=DBOrigin', 'select oid, lomowner, lomacl from pg_largeobject_metadata  where oid in 
(select oid from schema.table)') as t 
( oid oid,
  lomowner oid,
  lomacl aclitem[]
 );
user_194034780
Related