PostgreSQL backup script

[nets]

Doing a PostgreSQL (v11) backup script, and having trouble with

Code: Select all

pg_dump: could not open large object 100000: ERROR:  permission denied for large object 100000

Whats the right way to handle this? Turn on lo_compat_privileges in postgresql.conf or do you have another way?
Script is basically the postgresql.org cmd script for windows.

[Tommy.c]

I am talking to our internal DB guys to figure out what our recommendation for this would be.

My googling has me think that you should be fine with setting your PostgreSQL "lo_compat_privileges = on" and he should be good to go.

But I am going to double check myself. Stay tuned.

[Tommy.c]

Sorry for letting this sit for a while:

Here's what I have dug up as extra options, if you could let us know if you figured it out or if one of these work for you that'd be great.

1. Grant access to the specific large object
As a superuser, run the following command:
GRANT SELECT ON LARGE OBJECT 100000 TO your_user;
Note: This resolves the issue only for the specified object. Or create and run script for all needed objects
2. Run pg_dump as a superuser
This approach ensures full access to all objects, including large objects, during the dump process.
3. Exclude large objects from the dump
If large objects are not required, you can skip them using the following command:
pg_dump --no-blobs -U your_user -d your_db -f dump.sql
Caution: Excluding large objects may lead to issues during restoration if application depends on them.