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



  • I have a super large database in postgresql 13, the size if 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
    


  • The problem is that large objects don't belong to a specific schema. What you mean is that only some of those large objects are referenced from tables in your schema. Now this reference is not known to the database (there is no referential integrity), so there is no feature to do what you want.

    Perhaps you can write some code to extract only the large objects you need, but there is no help from PostgreSQL. Yoü have just learned one of the reasons why large objects should be avoided.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2