Abnormal Growth of Index Size - Index Size 3x large than table size.
Hi Team, We are facing a problem in our PostgreSQL production database related to abnormal growth of index size. Some of the indexes are having abnormal growth and index size is larger than table data size. One table is having 75 G.B of index though table size is only 25 G.B. On monthly basis we are performing vacuum to release the used space. I am attaching the screen shot for your reference. Could you please help us in resolving the same as this is degrading performance drastically. [cid:image002.png@01D623B9.565D10F0] Regards, Ram Pratap. Lava International Limited. Tel+ 91-120-4637148 [cid:image001.jpg@01CFD804.C427DF90]
RE: Abnormal Growth of Index Size - Index Size 3x large than table size.
Hello Ravi, Total number of index is 10 and 65GB is the sum total of index size of all indexes for table "tstock_movement" I am attaching the screen shot for your reference. [cid:image001.png@01D623D7.65173990] Regards, Ram Pratap. Lava International Limited. Tel+ 91-120-4637148 [cid:image001.jpg@01CFD804.C427DF90] From: Ravi Krishna [mailto:srkrish...@comcast.net] Sent: 06 May 2020 16:28 To: Ram Pratap Maurya Cc: pgsql-gene...@postgresql.org; Ashish Chugh Subject: Re: Abnormal Growth of Index Size - Index Size 3x large than table size. On May 6, 2020, at 5:48 AM, Ram Pratap Maurya mailto:ram.mau...@lavainternational.in>> wrote: Hi Team, We are facing a problem in our PostgreSQL production database related to abnormal growth of index size. Some of the indexes are having abnormal growth and index size is larger than table data size. One table is having 75 G.B of index though table size is only 25 G.B. On monthly basis we are performing vacuum to release the used space. I am attaching the screen shot for your reference. Could you please help us in resolving the same as this is degrading performance drastically. How many indexes are there in the table tstock_movement? Could it be that 65GB is the sum total of index size of all indexes.
pg_basebackup: child process exited with error 1
Hello team, I am facing the below error related to pg_basebackup.Tried to find solution. I also see the DB log but we not found any backup failed error log. [cid:image001.png@01D62D00.27C23FE0] Regards, Ram Pratap. Lava International Limited. Tel+ 91-120-4637148 [cid:image001.jpg@01CFD804.C427DF90]
Huge archive log generate in Postgresql-13
Hi Support, We have upgraded postgresql DB from version 11 to 13 . after upgrade to 13 huge archive log generate in system . Before upgrade per day 120GB to 150 GB log generated but after upgrade per day approx. 250 to 300 GB log generated. Can you please suggest why huge archive log generated after upgrade there any configure setting or this is Postgresql-13 behaviour. Postgresql-13 Postgresql conf file attached for your references. Regards, Ram Pratap. postgresql.conf Description: postgresql.conf
RE: Huge archive log generate in Postgresql-13
Dear Adrian, We have two replica system one is Slave and other id DR server . Salve server replicate from PG_WAL and DR system replicate from pg_log_archive. Can you please suggest what changes need to required in PG13 conf file. Regards, Ram Pratap. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: 18 April 2022 21:30 To: Ram Pratap Maurya ; pgsql-gene...@postgresql.org Cc: Manu Saxena Subject: Re: Huge archive log generate in Postgresql-13 On 4/18/22 04:34, Ram Pratap Maurya wrote: > Hi Support, > > We have upgraded postgresql DB from version 11 to 13 . after upgrade > to > 13 huge archive log generate in system . > > Before upgrade per day 120GB to 150 GB log generated but after > upgrade per day approx. 250 to 300 GB log generated. Where are you measuring this in the WAL directory or the archive directory? Do you have replication set up from this server and if so what type? FYI, the wal_level setting of hot_standby is deprecated and maps to replica since version 9.6. At some point you might want to change to match current documentation. > > Can you please suggest why huge archive log generated after upgrade > there any configure setting or this is Postgresql-13 behaviour. > > Postgresql-13 Postgresql conf file attached for your references. > > Regards, > > Ram Pratap. > -- Adrian Klaver adrian.kla...@aklaver.com postgresql.conf Description: postgresql.conf
RE: Huge archive log generate in Postgresql-13
Dear Adrian, If we set parameter "PG-WAL = replica" in PG13 conf file , there is any issue in replication through PG_WAL and pg_log_archive log. https://www.postgresql.org/docs/13/runtime-config-wal.html Regards, Ram Pratap. -Original Message----- From: Ram Pratap Maurya Sent: 19 April 2022 09:00 To: Adrian Klaver ; pgsql-gene...@postgresql.org Cc: Manu Saxena Subject: RE: Huge archive log generate in Postgresql-13 Dear Adrian, We have two replica system one is Slave and other id DR server . Salve server replicate from PG_WAL and DR system replicate from pg_log_archive. Can you please suggest what changes need to required in PG13 conf file. Regards, Ram Pratap. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: 18 April 2022 21:30 To: Ram Pratap Maurya mailto:ram.mau...@lavainternational.in>>; pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org> Cc: Manu Saxena mailto:manu.sax...@lavainternational.in>> Subject: Re: Huge archive log generate in Postgresql-13 On 4/18/22 04:34, Ram Pratap Maurya wrote: > Hi Support, > > We have upgraded postgresql DB from version 11 to 13 . after upgrade > to > 13 huge archive log generate in system . > > Before upgrade per day 120GB to 150 GB log generated but after > upgrade per day approx. 250 to 300 GB log generated. Where are you measuring this in the WAL directory or the archive directory? Do you have replication set up from this server and if so what type? FYI, the wal_level setting of hot_standby is deprecated and maps to replica since version 9.6. At some point you might want to change to match current documentation. > > Can you please suggest why huge archive log generated after upgrade > there any configure setting or this is Postgresql-13 behaviour. > > Postgresql-13 Postgresql conf file attached for your references. > > Regards, > > Ram Pratap. > -- Adrian Klaver adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com> postgresql.conf Description: postgresql.conf
RE: Huge archive log generate in Postgresql-13
Dear Adrian, We are using binary replication and par day pg_log_archive total size is increase after upgrade for PG11 to PG13. Regards, Ram Pratap. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: 19 April 2022 22:51 To: Ram Pratap Maurya ; pgsql-gene...@postgresql.org Subject: Re: Huge archive log generate in Postgresql-13 On 4/18/22 21:41, Ram Pratap Maurya wrote: > Dear Adrian, > > If we set parameter *"PG-WAL = replica"* in PG13 conf file , there > is any issue in replication through PG_WAL and pg_log_archive log. > In versions of Postgres from 9.6+ your setting of hot_standby is mapped to the replica setting so it is really not a change. In other words the hot_standby setting = replica setting as of 9.6. I made the suggestion to change just so the conf setting matches the choices in current documentation. Less confusion for someone looking through the postgresql.conf file and then looking up the values in the documentation. As to your replication is that binary replication or logical replication? Also you have not said where you are measuring the size growth in WAL files? > > Regards, > > Ram Pratap. > > -----Original Message- > From: Ram Pratap Maurya > Sent: 19 April 2022 09:00 > To: Adrian Klaver > mailto:adrian.kla...@aklaver.com>>; > pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org> > Cc: Manu Saxena > mailto:manu.sax...@lavainternational.in>> > Subject: RE: Huge archive log generate in Postgresql-13 > > Dear Adrian, > > We have two replica system one is Slave and other id DR server . > > Salve server replicate from PG_WAL and DR system replicate from > pg_log_archive. > > Can you please suggest what changes need to required in PG13 conf file. > > Regards, > > Ram Pratap. > > -Original Message- > > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com > <mailto:adrian.kla...@aklaver.com>] > > Sent: 18 April 2022 21:30 > > To: Ram Pratap Maurya <mailto:ram.mau...@lavainternational.in>>; > pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org> > <mailto:pgsql-gene...@postgresql.org> > > Cc: Manu Saxena <mailto:manu.sax...@lavainternational.in>> > > Subject: Re: Huge archive log generate in Postgresql-13 > > On 4/18/22 04:34, Ram Pratap Maurya wrote: > > > Hi Support, > > > > > > We have upgraded postgresql DB from version 11 to 13 . after > upgrade > > > to > > > 13 huge archive log generate in system . > > > > > > Before upgrade per day 120GB to 150 GB log generated but after > > > upgrade per day approx. 250 to 300 GB log generated. > > Where are you measuring this in the WAL directory or the archive directory? > > Do you have replication set up from this server and if so what type? > > FYI, the wal_level setting of hot_standby is deprecated and maps to > replica since version 9.6. At some point you might want to change to > match current documentation. > > > > > > Can you please suggest why huge archive log generated after upgrade > > > there any configure setting or this is Postgresql-13 behaviour. > > > > > > Postgresql-13 Postgresql conf file attached for your references. > > > > > > Regards, > > > > > > Ram Pratap. > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com> > <mailto:adrian.kla...@aklaver.com> > -- Adrian Klaver adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>
RE: Huge archive log generate in Postgresql-13
Dear Adrian, We are using "pg_log_archive" for Point-in-Time Recovery and DR replication. Regards, Ram Pratap. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: 21 April 2022 21:35 To: Ram Pratap Maurya ; pgsql-gene...@postgresql.org Subject: Re: Huge archive log generate in Postgresql-13 On 4/19/22 21:31, Ram Pratap Maurya wrote: > Dear Adrian, > > We are using binary replication and par day *pg_log_archive* total > size is increase after upgrade for PG11 to PG13. So what is pruning the WAL's in pg_log_archive? Or to put it another way what is the purpose of pg_log_archive and how is it managed? > > Regards, > > Ram Pratap. > -- Adrian Klaver adrian.kla...@aklaver.com
Server unable to UP after restore
Hi Team,
I am facing error during restore of PG_BASEBACKUP , server is not going UP
after restoring .
We follow below process .
1. First we take backup by command : pg_basebackup -h127.0.01 -U postgres -D
/mnt/backup
2. Maintain restore_command = 'cp /var/lib/pgsql/13/data/pg_log_archive/%f
%p' in postgresql.conf file .
3. Create file recovery.signal file in data directory.
But when we starting server showing error below error , can you please suggest .
[root@NHQ-MYLAVA-DB01 data]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled;
vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2023-07-21 09:00:15 IST; 17s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 8407 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
(code=exited, status=1/FAILURE)
Process: 8401 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir
${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8407 (code=exited, status=1/FAILURE)
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: Starting PostgreSQL 13 database
server...
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: postgresql-13.service: Main process
exited, code=exited, status=1/FAILURE
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: postgresql-13.service: Failed with
result 'exit-code'.
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: Failed to start PostgreSQL 13
database server.
Regards,
Ram Pratap.
After DB upgrade from PG13 to PG15 showing error
Dear Team, We have upgraded PostgreSQL DB from version 13 to 15 version . We are facing issue in PG15 we not enable any parameter related to AUTOVACUUM , but still running on PG15 data base. Below process showing : 2313192 | 02:10:01.283176 || myLava | active | autovacuum: VACUUM ANALYZE public.tstock_movement (to prevent wraparound) And one more issue we are facing after upgrade lot of alert is coming in DB log file , please suggest this is bug in Postgresql-15 version. 2024-08-26 00:00:36.783 IST [702937] WARNING: oldest xmin is far in the past 2024-08-26 00:00:36.783 IST [702937] HINT: Close open transactions soon to avoid wraparound problems. You might also need to commit or roll back old prepared transactions, or drop stale replication slots. 2024-08-26 00:00:36.784 IST [702937] WARNING: oldest xmin is far in the past 2024-08-26 00:00:36.784 IST [702937] HINT: Close open transactions soon to avoid wraparound problems. You might also need to commit or roll back old prepared transactions, or drop stale replication slots. DB OS -RHEL8. Regards, Ram Pratap.
RE: BUG #17046: Upgrade postgres 11 to 13 version
Hi Team, I am creating new server and installed postgres -13 and take PG_BACKUP form current PRD server (PG version 11) and restore DB on PG-13 server. can you suggest if we doing upgrade by this activity there is any issue. Regards, Ram Pratap. -Original Message- From: David Rowley [mailto:dgrowle...@gmail.com] Sent: 03 June 2021 18:01 To: Ram Pratap Maurya; PostgreSQL mailing lists Subject: Re: BUG #17046: Upgrade postgres 11 to 13 version On Fri, 4 Jun 2021 at 00:22, PG Bug reporting form wrote: > i am creating new server and installed postgres -13 and take PG_BACKUP > form current PRD server (PG version 11) and restore DB on PG-13 > server. can you suggest if we doing upgrade by this activity there is any > issue. The form you've used is for reporting bugs yet, this does not seem to be a bug report. If you're looking for general help and advice then you should consider asking on one of the mailing lists. https://www.postgresql.org/list/ pgsql-general might be the best fit. David
RE: BUG #17046: Upgrade postgres 11 to 13 version
Dear Adrian, Currently I am using postgres 11 version and I want to upgrade postgres 13 and I am doing this activity , first we take pgbase_backup from from Postgres 11 and restore this backup on postgres 13 (new server). I am not using pg_dump . I have done this activity on test server and its is working fine . My confusion is it is possible to in future we face any issue like upgrade and vacume or we can`t use all added new feature in postgres 13. Regards, Ram Pratap. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: 04 June 2021 03:07 To: Ram Pratap Maurya; pgsql-general@lists.postgresql.org Subject: Re: BUG #17046: Upgrade postgres 11 to 13 version On 6/3/21 5:53 AM, Ram Pratap Maurya wrote: > Hi Team, > > I am creating new server and installed postgres -13 and take PG_BACKUP form > current PRD server (PG version 11) and restore DB on PG-13 server. > can you suggest if we doing upgrade by this activity there is any issue. When doing this you should use the later version(13) of pg_dump to dump the earlier version, are you doing that? If you are then you have eliminated one possible issue. You should also read the Release Notes section of version 12 and 13: https://www.postgresql.org/docs/12/release.html https://www.postgresql.org/docs/13/release.html to see what things have changed that may affect your current set up. > > > > Regards, > Ram Pratap. > > -Original Message- > From: David Rowley [mailto:dgrowle...@gmail.com] > Sent: 03 June 2021 18:01 > To: Ram Pratap Maurya; PostgreSQL mailing lists > Subject: Re: BUG #17046: Upgrade postgres 11 to 13 version > > On Fri, 4 Jun 2021 at 00:22, PG Bug reporting form > wrote: >> i am creating new server and installed postgres -13 and take >> PG_BACKUP form current PRD server (PG version 11) and restore DB on >> PG-13 server. can you suggest if we doing upgrade by this activity there is >> any issue. > > The form you've used is for reporting bugs yet, this does not seem to be a > bug report. > > If you're looking for general help and advice then you should consider asking > on one of the mailing lists. > > https://www.postgresql.org/list/ > > pgsql-general might be the best fit. > > David > -- Adrian Klaver adrian.kla...@aklaver.com
Avg/max size of these JSON docs in Postgres
Hi Team, Confirm what is Avg/max size of these JSON docs in Postgres. Regards, Ram Pratap.
