Hi!
>I have imported data from other clusters and executed lot of different sql commands. I have used grant, revoke, reassign commands to change privileges for other users and have deleted and added users.
I don't suppose this was done in a structured way that could be gone back over?
Exact command sequence cannot restored.
I have script to normalize rights for user. It removes all rights first and set desired rights afterwards.
This was executed lot of times, it is used for years. Also there were manual user rights adjustments using sql commands in cases there this universal script cannot used. There are approx. 300 postgres roles in cluster, users are changing in every week.
Previous dumpuser backup which suceeds was at January 4th, 9 MB in custom format. There are nightly backups of databases is cluster.
There is also hot standby, base backups in every sunday using pg_basebackup and WAL archiving. WAL archiving and hot standby was broken in previous week (I increased max_connections=400 in main server but forget to increase this in standby server, WAL archiving is also from hot standby server).
You could try some of the functions here:
https://www.postgresql.org/docs/12/lo-funcs.html
to see if you can figure it out.
There is only one function , lo_get() in this page which returns data. I tried
Actually there is a second lo_export() at bottom of page. It needs superuser privilege and access to the server file system.
Tried in server using psql
select lo_export(200936761,'large1');
select lo_export(200936762,'large2');
select lo_export(200936767,'large3');
result files have zero size. >What happens if you query:
https://www.postgresql.org/docs/12/catalog-pg-largeobject.html
as a superuser?
> Do you see anything in the data field?
select * from pg_largeobject
running as superuser returs empty table with 3 columns:
loid, pageno and data
Andrus.