up vote 1 down vote favorite
1

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

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 in my command? How I can take only the specific table backup with only those blobs that belong to it?

Update:

I read 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, there is no use / possibility to take selective table's backup with blob / oid ?

share|improve this question
    
Sorry, but have you tried to pg_dump without the -b option? I would think that this would dump the whole table anyway. – ypercubeᵀᴹ Aug 5 '16 at 8:27
    
@ypercube, I have tried with / without -b option; with b, its taking complete backup of large objects (where my specific table is having only few entries), without b, its taking backup as flat data as expected. – Spike Aug 5 '16 at 8:55
up vote 0 down vote

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

share|improve this answer
    
i am not clear with first point. Is all tables having oid by default even table script not having any oid datatypes?. And -b takes complete blob backup (not only the mentioned table's oids). – Spike Aug 4 '16 at 5:12
1  
'The oid column are avaliable in all tables' - well, that's not true unless you create your tables with WITH OIDS. At least not true in recent (last 15 years or so ;) versions. Also, the OP has all these options in there command already, so what does your answer add that they don't already know? – dezso Aug 5 '16 at 8:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.