Postgresql – pg_restore does not restore blobs

blobpg-dumppg-restorepostgresqlpostgresql-9.4

I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command:

pg_dump --format=c --file=/var/backups/pgsql/db-neos.pgdump \
  --compress=6 --blobs neos

and then I restore it, on a different machine, with command:

createdb neos
pg_restore -d neos /mnt/db-neos.pgdump

but, while all tables are correctly created, large objects aren't.
Instead, an error message is displayed by pg_restore: 

pg_restore: [compress_io] decompressione dei dati fallita: (null)
(english: could not uncompress data)

Best Answer

I've faced the same issue some months ago, and I have managed to solve it with a bash script. This is the piece of code that take care to backup large objects, and save it to a local folder (/path/to/destination/lo/folder/) with filename = oid (the filename is important in order to restore lo with same oid) : 

echo -e "going to export largeobjects belonging to community $i..." && sleep 2
psql -U <username> -X -c "SELECT file_oid FROM <my_table>" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -d <my_source_db> \
| while read file_oid ; do
    echo "Exporting largeobject OID: $file_oid"
    ${PSQL} -d <my_source_db> -c "SELECT lo_export($file_oid, '/path/to/destination/lo/folder/$file_oid');"
done

Then to restore them I've written this other piece of code:

echo "going to import largeobjects belonging to community $i..." && sleep 2
LOBJECTS=/path/to/destination/lo/folder/*
for f in $LOBJECTS
do
    echo "Processing $f file..."
    filename=$(basename "$f")
    oid=${filename}

    psql -U <username> -d <my_source_db> -c"
    -- check if largeobject is already present, if it is delete it
    SELECT lo_unlink(${oid});"

    psql -U <username> -d <my_source_db> -c"
    -- import largeobjects
    SELECT lo_import('$f', ${oid});
    "
done

Out there it may exist a simpler solution, but at the time I've not been able to find it, this is why used this approach. I hope you will be more lucky and find a cleaner solution. I'll follow this post in case I can learn something useful too : )

Related Question