Backup certain months old data

Hello! I would like to know how we can backup certain months old data from PgSQL and then delete it. The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing. Regards Siraj

Fwd: pgadmin not opening in concurrent sessions

Hello! I installed PgAdmin4 8.2 in a Windows platform, for all the users. The issue we are having is, if any user launches it, nobody else can launch it. Eg., userB logs in and launches PgAdmin, it gets launched. Now if UserA and UserB tried to login, they get nothing, no event/response from Windo

User roles for gathering performance metrics data

Greetings! I am from Oracle background. In Oracle, we grant select_catalog_role or select any dictionary role to users who want to study performance data. I am trying to get similar information on the roles or privileges in PgSQL that we might want to request to investigate the performance problem

Problem with a Query

Hello! We migrated a PgSQL database from Cloud SQL to compute engine and since then there is a SQL we observed taking a long time. After some study, I found that the SQL is using NESTED LOOP where the cost is too high. I tried VACUUM FULL and ANALYZE, but to no avail. Only when I disabled the nest

Re: Problem with a Query

Thanks Tom. Collecting full stats on the tables involved corrected the execution. On Tue, Aug 13, 2024 at 9:57 AM Tom Lane wrote: > Siraj G writes: > > We migrated a PgSQL database from Cloud SQL to compute engine and since > > then there is a SQL we observed taking a long t

Problem with a query

Hello! We have a couple of queries that all of a sudden became rather slow. I took explain analyze from one of the SQLs as bdlow. Can you please check and suggest if anything can be done? '-> Table scan on (actual time=0.019..71.526 rows=38622 loops=1)\n -> Aggregate using temporary table (act

Re: Help in dealing with OOM

he error: FATAL: hot standby is not possible because max_connections = 4000 is a lower setting than on the master server (its value was 1) If I am clubbing multiple things, sorry for the clutter. Regards Siraj On Tue, Oct 15, 2024 at 12:39 AM Joe Conway wrote: > On 10/14/24 14:37, Si

Help in dealing with OOM

Hello Experts! My secondary instance has been unstable today. The service is crashing with Out of Memory. Please see below error (/var/log/postgresql/postgresql-2024-10-14.log): 10.2.52.50,2024-10-14 15:28:11 IST,686671,finance_revamp,finance_b2b,1,LOG: duration: 1148.527 ms statement: SELECT "

Need assistance in converting subqueries to joins

Hello Tech gents! I am sorry if I am asking the wrong question to this group, but wanted assistance in converting a query replacing subqueries with joins. Please find the query below (whose cost is very high): select em_exists_id from IS_SEC_FILT WHERE (IS_SEC_FILT_GUID) NOT IN (SELECT IS_OBJ_GU

Re: Need assistance in converting subqueries to joins

=IS_SEC_FILT.IS_PROJ_ID) (cost=0.72 rows=1) (actual time=0.001..0.001 rows=1 loops=517350)\n' On Fri, Sep 20, 2024 at 9:49 AM Adrian Klaver wrote: > On 9/19/24 21:07, Siraj G wrote: > > Hello Tech gents! > > > > I am sorry if I am asking the wrong question to this gr

Performance difference between Primary & Secondary in the query execution

Hello Experts! We have a PgSQL instance running with HA (secondary is being in sync with streaming replication). Both the ends, we have same version, but not sure a few SQLs behave badly in the secondary: Primary: PostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled b

Re: Export operation efficiency in read replica

anything that would prevent the operation from being successful? Regards Siraj On Thu, Mar 20, 2025 at 5:29 PM Laurenz Albe wrote: > On Thu, 2025-03-20 at 17:22 +0530, Siraj G wrote: > > I have a DB with 1TB in size serving needs of one of our critical > > applications. I have a

Export operation efficiency in read replica

Hello Experts! I have a DB with 1TB in size serving needs of one of our critical applications. I have a requirement to take export of the DB on a daily basis, but want to carry out this operation in read replica. The postgresql version is: 16.6 What would be the RPO of such backup? What would be

Re: Export operation efficiency in read replica

Thank you everyone! On Fri, Mar 21, 2025 at 7:23 PM Guillaume Lelarge < guillaume.lela...@dalibo.com> wrote: > On 20/03/2025 15:04, Adrian Klaver wrote: > > On 3/20/25 05:58, Siraj G wrote: > >> Hello Laurenz > >> > >> As per my understanding coming

Re: size of attributes table is too big

Johnson wrote: > On Wed, Mar 19, 2025 at 1:06 PM Siraj G wrote: > >> Hello! >> >> I have a PG (v16) instance which is occupying around 1TB of storage. Out >> of this, around 350GB is occupied by the table pg_catalog.pg_attribute. >> Why is the catalog table

Re: size of attributes table is too big

:47 PM Pavel Stehule wrote: > > > st 19. 3. 2025 v 18:14 odesílatel Álvaro Herrera > napsal: > >> Hello >> >> On 2025-Mar-19, Siraj G wrote: >> >> > I have a PG (v16) instance which is occupying around 1TB of storage. >> O

Re: size of attributes table is too big

ed, Mar 19, 2025 at 10:47 PM Adrian Klaver wrote: > On 3/19/25 10:06, Siraj G wrote: > > Hello! > > > > I have a PG (v16) instance which is occupying around 1TB of storage. Out > > Exact version of Postgres 16, include the x in 16.x. > > > of this, around 350GB is

size of attributes table is too big

Hello! I have a PG (v16) instance which is occupying around 1TB of storage. Out of this, around 350GB is occupied by the table pg_catalog.pg_attribute. Why is the catalog table's size so big? Here are the sizes: pg_attribute 338 GB pg_attribute_relid_attnam_index 117 GB pg_attribute_relid_attnum

hide data from admins

Hello Experts! What are the features available in Postgresql to hide PII (personal identifiable information) from the Admin team? Like in Oracle we have data vault and data redaction, I am looking for similar features in PostgreSQL.We do not want to do code level changes. Regards Siraj