Postgresql – postgres backup for specific table with blob oid

blobpg-dumppostgresqlpostgresql-9.1

I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 entries in pg_largeobject table). I read the following link and added -o and -b to the backup parameters.

https://www.postgresql.org/docs/9.1/static/app-pgdump.html

I am using below command to take backup.

 pg_dump -i -h myhost -p 5432 -U myuser -F c -v -t schema.my_table -o -b -f mytablebkp.backup "mydb"

Is anything wrong with that command? How can I take a backup of only the specific table with only those blobs that belong to it?

Update:

I read the following from documentation (https://www.postgresql.org/docs/9.5/static/app-pgrestore.html),

pg_restore cannot restore large objects selectively; for instance, only those for a specific table. If an archive contains large objects, then all large objects will be restored, or none of them if they are excluded via -L, -t, or other options

Since pg_restore can not do selective restore, is there no way / possibility to take selective table backups with blob / oid ?

Best Answer

The oid column are avaliable in all tables, and I believe thats not a issue.

To create a logical dump of this table, you just need add a option -b (or --blobs in the pg_dump arguments, eg:

pg_dump \
  -U user\
  -t table_name \
  --blobs \
  -f dump_file_name \
  database_name

Another options will be useful is the -F c (or --format=custom) to change the default format from plain to custom.

Please take a look at the documentation for more details: https://www.postgresql.org/docs/current/static/app-pgdump.html

Related Question