7

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 ?

2
  • Sorry, but have you tried to pg_dump without the -b option? I would think that this would dump the whole table anyway. Aug 5, 2016 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, 2016 at 8:55

1 Answer 1

1

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

2
  • 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, 2016 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, 2016 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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