Re: List users privileges for whole cluster/all databases in the cluster
You can use pg_dumpall -g to get user information for the cluster. Regards, Priyanka On Thu, 12 Oct 2023 at 3:46 PM, Jana Mihalidesová wrote: > Hi, > > I try to find out some view, select or something what show me the > privileges for the user across the whole postgresql cluster. The > username/user is global for whole cluster not individual database, so I > would like to know the privileges for the user in all databases in the > cluster using one view, select. > I know how to list user's privileges in the individual database, but for > all databases... > > Thanks for the advice. > Jana >
Re: Bloated pg_catalog.pg_largeobjects
You have to run vacuumlo to remove orphaned large objects. https://www.postgresql.org/docs/current/vacuumlo.html Regards, Priyanka On Sun, 21 Jul 2024 at 12:46 AM, wrote: > Hello All, > > I've got a cluster that's having issues with pg_catalog.pg_largeobject > getting massively bloated. Vacuum is running OK and there's 700GB of free > space in the table and only 100GB of data, but subsequent inserts seem to > be not using space from the FSM and instead always allocating new pages. > The table just keeps growing. > > Is this a known thing, maybe something special about LOs? > > Also, is the only way to recover space here a vacuum full on the table > since it's a catalog table? > > Thanks, > -- > Jon Erdman (aka StuckMojo on IRC) > PostgreSQL Zealot >
Re: Bloated pg_catalog.pg_largeobjects
Vacuum Full will not help here if you have large objects present in the pg_largeobjects table but not being referred to by any table. Vacuumlo doesn't require downtime but based on the data it needs to remove can run long and use resources and hence schedule it in off peak hours. You can do a dry run to get an estimate. On Mon, Jul 22, 2024 at 1:21 PM khan Affan wrote: > Hi > > I would suggest to backup your DB before doing such a thing. > > Run Vaccum Full, (VACUUM FULL pg_catalog.pg_largeobject) Running this on > the system table might be risky Make sure you backup the database. > > & if you are using PG version above 9.1 use Pg_repack to reclaim the space. > > Note: It can be disruptive, so planning and preparing for potential > downtime is essential. > > Thanks & regards > > > *Muhammad Affan (*아판*)* > > *PostgreSQL Technical Support Engineer** / Pakistan R&D* > > Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan > > On Sun, Jul 21, 2024 at 3:46 AM wrote: > >> Hello All, >> >> I've got a cluster that's having issues with pg_catalog.pg_largeobject >> getting massively bloated. Vacuum is running OK and there's 700GB of free >> space in the table and only 100GB of data, but subsequent inserts seem to >> be not using space from the FSM and instead always allocating new pages. >> The table just keeps growing. >> >> Is this a known thing, maybe something special about LOs? >> >> Also, is the only way to recover space here a vacuum full on the table >> since it's a catalog table? >> >> Thanks, >> -- >> Jon Erdman (aka StuckMojo on IRC) >> PostgreSQL Zealot >> >
Re: Unknown temp directories and library files
Hi Adrian, There is patroni and pgqd running on the server. So possibly these are using psycopg2. Regards, Priyanka On Sun, 13 Oct 2024 at 6:01 PM, Adrian Klaver wrote: > On 10/10/24 11:36, Priancka Chatz wrote: > > Hi admins, > > > > I am observing a new/unknown behavior on some of my instances. My > > postgres Data directory path is /home/postgres/pgdata/pgroot/data. And I > > see a temp directory present inside /home/postgres/pgdata which has 100s > > of directory underneath it and inside each directory some library files > > related to Psycopg2. Not sure what these files are and why it is getting > > created. However, the only pattern I found is wherever Postgres is down > > this behavior is seen. > > They are coming from an install of psycopg2-binary: > > pip install psycopg2-binary > > ll lib/python3.12/site-packages/psycopg2_binary.libs/ > total 6944 > drwxrwxr-x 2 aklaver aklaver4096 Oct 13 08:55 ./ > drwxrwxr-x 348 aklaver aklaver 20480 Oct 13 08:55 ../ > -rwxrwxr-x 1 aklaver aklaver 17497 Oct 13 08:55 > libcom_err-2abe824b.so.2.1* > -rwxrwxr-x 1 aklaver aklaver 3133185 Oct 13 08:55 > libcrypto-0628e7d4.so.1.1* > -rwxrwxr-x 1 aklaver aklaver 345209 Oct 13 08:55 > libgssapi_krb5-497db0c6.so.2.2* > -rwxrwxr-x 1 aklaver aklaver 219953 Oct 13 08:55 > libk5crypto-b1f99d5c.so.3.1* > -rwxrwxr-x 1 aklaver aklaver 17913 Oct 13 08:55 > libkeyutils-dfe70bd6.so.1.5* > -rwxrwxr-x 1 aklaver aklaver 1018953 Oct 13 08:55 > libkrb5-fcafa220.so.3.3* > -rwxrwxr-x 1 aklaver aklaver 76873 Oct 13 08:55 > libkrb5support-d0bcff84.so.0.1* > -rwxrwxr-x 1 aklaver aklaver 60977 Oct 13 08:55 > liblber-5a1d5ae1.so.2.0.200* > -rwxrwxr-x 1 aklaver aklaver 447329 Oct 13 08:55 > libldap-5d2ff197.so.2.0.200* > -rwxrwxr-x 1 aklaver aklaver 406817 Oct 13 08:55 > libpcre-9513aab5.so.1.2.0* > -rwxrwxr-x 1 aklaver aklaver 370777 Oct 13 08:55 libpq-e8a033dd.so.5.16* > -rwxrwxr-x 1 aklaver aklaver 119217 Oct 13 08:55 > libsasl2-883649fd.so.3.0.0* > -rwxrwxr-x 1 aklaver aklaver 178337 Oct 13 08:55 > libselinux-0922c95c.so.1* > -rwxrwxr-x 1 aklaver aklaver 646065 Oct 13 08:55 libssl-3e69114b.so.1.1* > > > Looks like something is setting up psycopg2-binary and psycopg2 as part > of some script/tool/orm/etc in your $DATA directory. > > Are you using something that uses psycopg2 as the client library? > > > > > I am attaching screenshots for reference. > > > > Can anyone shed some light or direct me to any links to troubleshoot > this? > > > > Regards, > > Priyanka > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
