Re: Automatic autovacuum to prevent wraparound - PG13.5

[Ninad Shah]

Frankly speaking, Aurora PostgreSQL's behaviour is quite unpredictable.
In our case, the autovacuum was not even getting triggered in spite of
crossing the autovacuum_freeze_max_age. Finally, the database went down
abruptly, which resolved the issue.


Thanks,
Ninad

On Wed, Jun 15, 2022 at 7:57 PM Laurenz Albe 
wrote:

> On Wed, 2022-06-15 at 12:13 +0100, Mauro Farracha wrote:
> > Have recently upgraded from PG10 to PG13.5 and would like to understand
> the reason why we
> > are seeing triggered autovacuum to prevent the wraparound while all
> the metrics are still
> > far off from the multixact/freeze max ages defined. And inclusive there
> was one time where
> > it was triggered as aggressive.
> >
> > Some background:
> > - autovacuum_freeze_max_age: 400M
> > - autovacuum_multixact_freeze_max_age: 800M
> > - the activity is mostly insert intensive in one particular table (60M
> daily)
> > - the team execute vacuum freeze verbose every day at night to keep the
> multixact ids down
> > - we generally reach near 70M mxids before running vacuum freeze at night
> > - the postgresql is Aurora
> >
> > The scenario:
> > - Out of nowhere (during the weekend), without database activity load or
> batches running,
> >   with previous nightly run of vacuum freeze, in the middle of the day,
> with xids and mxids
> >   below 20M we are seeing autovacuum being triggered to prevent
> wraparound.
> >
> > My question is why this is occurring, which condition might be
> responsible for this behaviour?
>
> A long-running transaction or a prepared transaction.
> Or an abandoned replication slot with an old "xmin".
>
> That would be the answer for PostgreSQL.  It might apply to Amazon Aurora,
> unless they
> changed the behavior there.  Perhaps ask Amazon.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>

Re: Obsolete or dead serverconnections after reboot

[Ninad Shah]

Hello,

Would you be able to verify the process trees for those PIDs on Windows?
You may be able to see who holds the connections?


Regards,
Ninad Shah

On Wed, 21 Jul 2021 at 19:15, WR  wrote:

> Hello Vijaykumar Jain,
>
> thank you for fast answer, today I'm not able to access the hardware,
> I'll be back tomorrow and will do the required tests.
>
> Wolfgang
>
> Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
> > select pg_stat_reset();
> >
> > Can you run above function, to check if stats are reset and things are
> > fine without a pg restart?
> > I not able to understand how new connection stats are added, along with
> > old stale states, if I assume stats collector process is having issues ,
> > or the stats folder is corrupt etc.
> > That would also mean, all table stats would be off or not updated too?
> > Is that so? Or analyse works fine on tables without a restart?
> >
> > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
> >  > <mailto:vijaykumarjain.git...@gmail.com>> wrote:
> >
> >
> >
> > Is there a way to avoid this (without restarting the service
> > after every
> > reboot). Is this a bug or a normal behavior?
> >
> >
> > I have less knowledge of windows.
> > https://www.enterprisedb.com/blog/postgresql-shutdown
> > <https://www.enterprisedb.com/blog/postgresql-shutdown>
> >
> >
> >
> > Do you see shutdown/termination messages in the db logs or windows
> > event logs when the machine is rebooted?
> >
> > You get the same pid and query, does it also has the same age( time
> > since it started),
> > I mean is the stats table updated with new data for stale
> > connections or they remain static.
> >
> > Do you see the same issue when the machine is power cycled.
> >
> > Maybe windows might be preserving the memory state on disk and
> > reading it back on reboot (like sleep) unless there are instructions
> > to shutdown the db server on reboot. Idk.
> >
> > What are the state of the connections in pg_stat_activity abd
> > process explorer before and after reboot. The sockets exists and
> > active,  or are residual in pg stats only.
> >
> >
>
>
>
>

Re: Obsolete or dead serverconnections after reboot

[Ninad Shah]

Factually, Windows itself has a number of issues. Hence, it is always
suggested to use Linux with it.

It can be explored further by querying the table from which
pg_stat_activity gathers data.


Regards,
Ninad Shah


On Thu, 22 Jul 2021 at 12:51, WR  wrote:

> Hello Ninad Shah,
>
> I think, nobody holds the connections, because the state is idle. But
> I'm not shure what means: the connection is idle.
>
> One interesting fact was: when I dont stop the psql commandline on the
> client (and dont start another query, wht ends up in a connection reset,
> while the server is down), psql uses the same connection after reboot
> and the state goes from idle back to active.
>
> So there are two questions: what will happen to idle connctions after a
> while, if the client doesnt exist anymore.
> And is this desired behavior, the the postgres-Server-Service does not a
> shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)
>
> Thanks you, Greeting from germany,
> Wolfgang
>
> Am 21.07.2021 um 16:10 schrieb Ninad Shah:
> > Hello,
> >
> > Would you be able to verify the process trees for those PIDs on Windows?
> > You may be able to see who holds the connections?
> >
> >
> > Regards,
> > Ninad Shah
> >
> > On Wed, 21 Jul 2021 at 19:15, WR  > <mailto:wolle...@freenet.de>> wrote:
> >
> > Hello Vijaykumar Jain,
> >
> > thank you for fast answer, today I'm not able to access the hardware,
> > I'll be back tomorrow and will do the required tests.
> >
> > Wolfgang
> >
> > Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
> >  > select pg_stat_reset();
> >  >
> >  > Can you run above function, to check if stats are reset and
> > things are
> >  > fine without a pg restart?
> >  > I not able to understand how new connection stats are added,
> > along with
> >  > old stale states, if I assume stats collector process is having
> > issues ,
> >  > or the stats folder is corrupt etc.
> >  > That would also mean, all table stats would be off or not updated
> > too?
> >  > Is that so? Or analyse works fine on tables without a restart?
> >  >
> >  > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
> >  >  > <mailto:vijaykumarjain.git...@gmail.com>
> >  > <mailto:vijaykumarjain.git...@gmail.com
> > <mailto:vijaykumarjain.git...@gmail.com>>> wrote:
> >  >
> >  >
> >  >
> >  > Is there a way to avoid this (without restarting the
> service
> >  > after every
> >  > reboot). Is this a bug or a normal behavior?
> >  >
> >  >
> >  > I have less knowledge of windows.
> >  > https://www.enterprisedb.com/blog/postgresql-shutdown
> > <https://www.enterprisedb.com/blog/postgresql-shutdown>
> >  > <https://www.enterprisedb.com/blog/postgresql-shutdown
> > <https://www.enterprisedb.com/blog/postgresql-shutdown>>
> >  >
> >  >
> >  >
> >  > Do you see shutdown/termination messages in the db logs or
> > windows
> >  > event logs when the machine is rebooted?
> >  >
> >  > You get the same pid and query, does it also has the same
> > age( time
> >  > since it started),
> >  > I mean is the stats table updated with new data for stale
> >  > connections or they remain static.
> >  >
> >  > Do you see the same issue when the machine is power cycled.
> >  >
> >  > Maybe windows might be preserving the memory state on disk and
> >  > reading it back on reboot (like sleep) unless there are
> > instructions
> >  > to shutdown the db server on reboot. Idk.
> >  >
> >  > What are the state of the connections in pg_stat_activity abd
> >  > process explorer before and after reboot. The sockets exists
> and
> >  > active,  or are residual in pg stats only.
> >  >
> >  >
> >
> >
> >
>
>
> --
> May the source be with you
>

Re: Doubt on pgbouncer

[Ninad Shah]

Pgbouncer doesn't provide such a functionality. Kindly consider using
pgpool.

On Thu, 22 Jul 2021 at 15:58, Ganesh Korde  wrote:

> Try using HAProxy.
>
> Regards,
> Ganesh Korde.
>
> On Sat, 3 Jul 2021, 9:06 pm Rama Krishnan,  wrote:
>
>>
>> Hi Team,
>>
>> How can I split read and write queries using pgbouncer
>>
>> Regards
>>
>> A.Rama Krishnan
>>
>>
>> 
>>  Virus-free.
>> www.avast.com
>> 
>> <#m_2585993038985863414_m_3619964688060668000_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>
>

Re: Obsolete or dead serverconnections after reboot

[Ninad Shah]

Just to make it clear, "idle in transaction" and "idle" are different
terms. "Idle in transaction" refers to connections that are waiting for
either commit or rollback.

Additionally, this could be a bug as well. However, it's difficult to
conclude at this stage. You may report this to postgresql-bugs group.


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 17:25, WR  wrote:

> Hello,
>
> unfortunately, it's my companys choice of OS.
> In private life I'm riding the penguin.
>
> One last thing: I have 2 Laptops, where the shutdown doesn't work, and
> one VirtualBox machine where it works like a charm. Maybe it has
> something to do with the power management.
>
> As a workaround I set the value
> idle_in_transaction_session_timeout
> to 5min. So the server kills this idle connections from before the power
> cycle (and all others too) after 5 minutes.
>
> Thank you, guys
>
> (PS.: still no answer from EDB)
>
> Am 22.07.2021 um 16:51 schrieb Ninad Shah:
> > Factually, Windows itself has a number of issues. Hence, it is always
> > suggested to use Linux with it.
> >
> > It can be explored further by querying the table from which
> > pg_stat_activity gathers data.
> >
> >
> > Regards,
> > Ninad Shah
> >
> >
> > On Thu, 22 Jul 2021 at 12:51, WR  > <mailto:wolle...@freenet.de>> wrote:
> >
> > Hello Ninad Shah,
> >
> > I think, nobody holds the connections, because the state is idle. But
> > I'm not shure what means: the connection is idle.
> >
> > One interesting fact was: when I dont stop the psql commandline on
> the
> > client (and dont start another query, wht ends up in a connection
> > reset,
> > while the server is down), psql uses the same connection after reboot
> > and the state goes from idle back to active.
> >
> > So there are two questions: what will happen to idle connctions
> after a
> > while, if the client doesnt exist anymore.
> > And is this desired behavior, the the postgres-Server-Service does
> > not a
> > shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)
> >
> > Thanks you, Greeting from germany,
> > Wolfgang
> >
> > Am 21.07.2021 um 16:10 schrieb Ninad Shah:
> >  > Hello,
> >  >
> >  > Would you be able to verify the process trees for those PIDs on
> > Windows?
> >  > You may be able to see who holds the connections?
> >  >
> >  >
> >  > Regards,
> >  > Ninad Shah
> >  >
> >  > On Wed, 21 Jul 2021 at 19:15, WR  > <mailto:wolle...@freenet.de>
> >  > <mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>>> wrote:
> >  >
> >  > Hello Vijaykumar Jain,
> >  >
> >  > thank you for fast answer, today I'm not able to access the
> > hardware,
> >  > I'll be back tomorrow and will do the required tests.
> >  >
> >  > Wolfgang
> >  >
> >  > Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
> >  >  > select pg_stat_reset();
> >  >  >
> >  >  > Can you run above function, to check if stats are reset and
> >  > things are
> >  >  > fine without a pg restart?
> >  >  > I not able to understand how new connection stats are
> added,
> >  > along with
> >  >  > old stale states, if I assume stats collector process is
> > having
> >  > issues ,
> >  >  > or the stats folder is corrupt etc.
> >  >  > That would also mean, all table stats would be off or not
> > updated
> >  > too?
> >  >  > Is that so? Or analyse works fine on tables without a
> restart?
> >  >  >
> >  >  > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
> >  >  >  > <mailto:vijaykumarjain.git...@gmail.com>
> >  > <mailto:vijaykumarjain.git...@gmail.com
> > <mailto:vijaykumarjain.git...@gmail.com>>
> >  >  > <mailto:vijaykumarjain.git...@gmail.com
> > <mailto:vijaykumarjain.git...@gmail.com>
> >  > <mailto:vijaykumarjain.git...@gmail.com
> > <mailto:vijaykumarjain.git...@gmail.com>>>> wrote:
> >  >  >
> > 

Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

[Ninad Shah]

Question 1 : What's a need to perform a VACUUM FULL operation on pg_class?

Question 2 : Ideally, a VACUUM FULL operation seeks an exclusive lock on a
table, while I can see it waiting for a shared lock here. Why? (Not asking
you)

Additionally, The situation you have described should be termed a
"deadlock". Do you see any transaction getting rolled back?


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 11:39, Marcin Barczynski <
mbarczyn...@starfishstorage.com> wrote:

> On Thu, Jul 22, 2021 at 5:08 PM Marcin Barczynski <
> mbarczyn...@starfishstorage.com> wrote:
>
>> On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe 
>> wrote:
>> >
>> > On Thu, 2021-07-22 at 12:55 +0200, Marcin Barczynski wrote:
>> > > There was a long-running transaction consisting of two queries:
>> > >
>> > > CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
>> > > INSERT INTO xyz_table SELECT * FROM abc;
>> > >
>> > > When I ran VACUUM FULL pg_class, it waited for ShareLock on that
>> transaction:
>> >
>> > There must have been something else using "pg_class", since the above
>> > won't take any permament locks on "pg_class", nor should it block
>> VACUUM.
>>
>>
>> Thanks for your reply. I dugged a bit deeper, and it turned out that
>> VACUUM FULL hung in heapam_index_build_range_scan.
>> It's PostgreSQL 13.3. Comments around heapam_handler.c:1482:
>>
>
> What's more, running VACUUM FULL pg_class sometimes causes a deadlock with
> transactions using temp tables. For example:
>
> DETAIL:  Process 6703 waits for ShareLock on transaction 108850229;
> blocked by process 6591.
> Process 6591 waits for AccessShareLock on relation 1259 of
> database 16517; blocked by process 6703.
> Process 6703: VACUUM (FULL, VERBOSE, ANALYZE) pg_class
> Process 6591: SELECT * FROM stored_procedure()
>
> Isn't it a bug?
> Is there any way to safely run VACUUM FULL pg_class?
> My workload involves lots of temp tables, and I need to get rid of the
> bloat regularly.
>
> --
> Regards,
> Marcin Barczynski
>

Re: PostgreSQL 9.2 high replication lag

[Ninad Shah]

Version 9.2 is very old and has a lot of issues related to streaming
replication. Additionally, it is already EOL.

Furthermore, max_standby_streaming_delay has no relation with the streaming
lag. It's associated with queries conflicting with data. This parameter
will not speed up the WAL apply operation.

Kindly consider upgrading to a supported version.


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 15:18, Luca Ferrari  wrote:

> On Fri, Jul 16, 2021 at 12:38 AM Lucas  wrote:
> > However, it hasn't changed and the replication lag is still high all the
> time.
> >
>
> Since the documentation states that max_standby_streaming_delay
> "[...]it is the maximum total time allowed to apply WAL data once it
> has been received from the primary server[...]", could it be the
> trivial explaination that the master is serving WALs with an high
> delay?
>
> Luca
>
>
>

Re: pg_restore (fromuser -> touser)

[Ninad Shah]

This is true. Such a feature is not available in PostgreSQL.

What you need to do is you have to take a structure dump, and change the
schema name as per required. And, then, you may copy the data.


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 23:08, Mayan  wrote:

> Hi,
>
>
>
> I had a general question about a feature that we depended on heavily when
> using other RDBMS providers which was the ability to take a schema dump and
> restore it to a different database and a different schema in that database
> (could be to the same database as well).  Basically, there was an option
> on restore to specify a FROMUSER and a TOUSER directive so schema A could
> be restored elsewhere but as schema B.
>
>
>
> I don’t see such an option in Postgres and the only workaround I am aware
> of is to do a plain-text (format=p) and then a crude find/replace to
> replace the old schema name with the new schema name.  I’ve never
> actually tested this to be sure even this would work.  Also, using this
> option will prevent us from parallelizing the import or export, so it’s
> really not something we want to do.
>
>
>
> This would be a really useful feature in my opinion along with the ability
> to maintain parallelization options.
>
>
>
> Are there any such features on the roadmap?  Is my understanding correct
> about the available ways to accomplish this – again, in a practical and
> performant way?
>
>
>
> Thanks,
>
> Mayan
>

Re: autovacuum worker started without a worker entry

[Ninad Shah]

Most probably, it runs a wraparound process, however, if you may see what
command was invoked by that worker, it would be helpful.


Regards,
Ninad Shah

On Mon, 9 Aug 2021 at 01:48, Luca Ferrari  wrote:

> On Thu, Aug 5, 2021 at 6:27 PM Vijaykumar Jain
>  wrote:
> > postgres/varsup.c at master · postgres/postgres (github.com)
> > I think, this block when it is about to assign the next xid, it does the
> math, and triggers an autolauncher start.
> > I might be wrong, I did not run a backtrace though :)
> >
> >  * Check to see if it's safe to assign another XID. This protects against
> >  * catastrophic data loss due to XID wraparound. The basic rules are:
> >  * If we're past xidVacLimit, start trying to force autovacuum cycles.
> >  * If we're past xidWarnLimit, start issuing warnings.
> >  * If we're past xidStopLimit, refuse to execute transactions, unless
> >  * we are running in single-user mode (which gives an escape hatch
> >  * to the DBA who somehow got past the earlier defenses).
>
> Seem reasonable as explaination, even if sounds to me xidVacLimit is 65536.
>
> Thanks,
> Luca
>
>
>

Re: Manual failover cluster

[Ninad Shah]

What are the parameters have you set in the recovery.conf file?


Regards,
Ninad Shah

On Fri, 20 Aug 2021 at 18:53, Hispaniola Sol  wrote:

> Team,
>
> I have a pg 10 cluster with a master and two hot-standby nodes. There is a
> requirement for a manual failover (nodes switching the roles) at will. This
> is a vanilla 3 node PG cluster that was built with WAL archiving (central
> location) and streaming replication to two hot standby nodes.  The failover
> is scripted in Ansible. Ansible massages and moves around the
> archive/restore scripts, the conf files and the trigger and calls `
> pg_ctlcluster` to start/stop. This part _seems_ to be doing the job fine.
>
> The issue I am struggling with is the apparent fragility of the process -
> all 3 nodes will end up in a "good" state after the switch only every other
> time. Other times I have to rebase the hot-standby from the new master with
> pg_basebackup. It seems the issues are mostly with those nodes, ending up
> as slaves after the roles switch runs.
> They get errors like mismatch in timelines, recovering from the same WAL
> over and over again, invalid resource manager ID in primary checkpoint
> record, etc.
>
> In this light, I am wondering - using what's offered by PostgreSQL itself,
> i.e. streaming WAL replication with log shipping - can I expect to have
> this kind of failover 100% reliable on PG side ? Anyone is doing this
> reliably on PostgreSQL 10.1x ?
>
> Thanks !
>
> Moishe
>

Re: Manual failover cluster

[Ninad Shah]

Hi Saul,

Hope you are doing well. My apology for no response for a longer time.

Pgbackrest helps build a streaming replication. While performing role
reversal(switchover), it is mandatory to set recover_target_timeline to
latest in recovery.conf(in data directory). Steps to perform switchover is
as below.

1) Stop master database
2) Promote the slave database
3) Prepare the recovery.conf file on the stopped master node, and set
recover_target_timeline to latest in that file
4) Start the stopped master database; it will automatically come up as a
slave
5) Check status of the database.

Additionally, entries in pg_hba.conf is required.

Hope this helps.


Regards,
Ninad Shah


On Mon, 23 Aug 2021 at 23:12, Saul Perdomo  wrote:

> Sorry, I misspoke there - I meant to say that since one should not count
> on the standby-failover process to always run smoothly (whether it's due to
> hardware, operator, automated scripts, or software issues), DB backups
> should also be in place if at all possible.
>
> On Mon, Aug 23, 2021 at 1:37 PM Saul Perdomo 
> wrote:
>
>> Hi Moishe,
>>
>> Since we use pgbackrest ourselves, this is the process I followed to set
>> up something similar on PG 10:
>>
>>
>> https://pgstef.github.io/2018/11/28/combining_pgbackrest_and_streaming_replication.html
>>
>> (Not knowing much [if at all] about the reason for your requirements, I
>> would recommend looking into enabling PITR backups in addition to the hot
>> standby[s], which as you've seen are not bulletproof.)
>>
>> Anyway, just wanted to add that your experience roughly matches ours --
>> it seems that an attempt to promote the standby isn't always smooth. My
>> sample size is almost certainly smaller than yours, about half a dozen
>> attempts. In the past I've attributed this to a poor understanding on my
>> part of the behind-the-scenes of the process, and chalked it up to having
>> made a mistake or other in the manual steps (mainly in the order they are
>> executed). That being said, if you find a way to faithfully reproduce the
>> issue, I'm sure the community will want to know, there is certainly an
>> expectation that the failover is reliable from the PG software side of
>> things, as long as there are no hardware nor operator issues! Again, not
>> knowing a lot about your setup, my first instinct would be to troubleshoot
>> your automated scripts, you might find that you need to change the order
>> things are run when on server B vs server A, for example..
>>
>> On Fri, Aug 20, 2021 at 9:23 AM Hispaniola Sol 
>> wrote:
>>
>>> Team,
>>>
>>> I have a pg 10 cluster with a master and two hot-standby nodes. There is
>>> a requirement for a manual failover (nodes switching the roles) at will.
>>> This is a vanilla 3 node PG cluster that was built with WAL archiving
>>> (central location) and streaming replication to two hot standby nodes.  The
>>> failover is scripted in Ansible. Ansible massages and moves around the
>>> archive/restore scripts, the conf files and the trigger and calls `
>>> pg_ctlcluster` to start/stop. This part _seems_ to be doing the job fine.
>>>
>>> The issue I am struggling with is the apparent fragility of the process
>>> - all 3 nodes will end up in a "good" state after the switch only every
>>> other time. Other times I have to rebase the hot-standby from the new
>>> master with pg_basebackup. It seems the issues are mostly with those nodes,
>>> ending up as slaves after the roles switch runs.
>>> They get errors like mismatch in timelines, recovering from the same WAL
>>> over and over again, invalid resource manager ID in primary checkpoint
>>> record, etc.
>>>
>>> In this light, I am wondering - using what's offered by PostgreSQL
>>> itself, i.e. streaming WAL replication with log shipping - can I expect to
>>> have this kind of failover 100% reliable on PG side ? Anyone is doing this
>>> reliably on PostgreSQL 10.1x ?
>>>
>>> Thanks !
>>>
>>> Moishe
>>>
>>

Issue with a query while running on a remote host

[Ninad Shah]

Hello All,

While working with a PostgreSQL database, I came across an issue where data
is not being fetched over the network.

Version : PostgreSQL 11.10
Operating system : RHEL 8.4

*Issue description:*

We tried to execute the below query on the database host using psql prompt,
it works without any issue.

select regexp_replace(state_cd, E'[\\n\\r]+', '', 'g' ) as  state_cd,
off_cd, user_cd, regexp_replace(user_name, E'[\\n\\r]+', '', 'g' ) as
user_name, regexp_replace(desig_cd, E'[\\n\\r]+', '', 'g' ) as desig_cd,
regexp_replace(user_id, E'[\\n\\r]+', '', 'g' ) as user_id,
regexp_replace(user_pwd, E'[\\n\\r]+', '', 'g' ) as user_pwd,
regexp_replace(phone_off, E'[\\n\\r]+', '', 'g' ) as phone_off, mobile_no,
regexp_replace(email_id, E'[\\n\\r]+', '', 'g' ) as email_id,
regexp_replace(user_catg, E'[\\n\\r]+', '', 'g' ) as user_catg,
regexp_replace(status, E'[\\n\\r]+', '', 'g' ) as status, created_by,
created_dt, aadhaar, op_dt, regexp_replace(login_ipaddress, E'[\\n\\r]+',
'', 'g' ) as login_ipaddress, regexp_replace(forget_password, E'[\\n\\r]+',
'', 'g' ) as forget_password, regexp_replace(newuser_change_password,
E'[\\n\\r]+', '', 'g' ) as newuser_change_password from  tm_user_info
where  ( user_cd >= 1068540014 ) AND ( user_cd <= 2137079303 ) ;

While trying to execute the same query over the network using psql prompt,
the execution doesn't finish.

*My Analysis:*

By digging further, we came to see that a specific record was causing the
issue, and by further analysis, we saw that the records that contain a
specific string("*bash@*") in the column user_id are not being fetched over
the network.

To confirm that, we also changed some records manually by creating a test
table. And, we were able to reproduce the issue.

vow4_xdb=# select * from vahan4.test_tbl where user_cd =2106011301;
.
.

But, this issue doesn't occur if we try to fetch on the database host or
via PgAdmin4. In such cases, we get the record in a few milliseconds.

*Surprisingly, this table has only one record.*

There is no table/row-level lock found here.


*Table definition:-*
  Table "test_tbl"
 Column  |Type | Collation |
Nullable | Default | Storage  | Stats targe
t | Description
-+-+---+--+-+--+
--+-
 state_cd| character varying(2)|   | not
null | | extended |
  |
 off_cd  | numeric(5,0)|   | not
null | | main |
  |
 user_cd | numeric(10,0)   |   | not
null | | main |
  |
 user_name   | character varying(99)   |   | not
null | | extended |
  |
 desig_cd| character varying(10)   |   | not
null | | extended |
  |
 user_id | character varying(20)   |   | not
null | | extended |
  |
 user_pwd| character varying(100)  |   | not
null | | extended |
  |
 phone_off   | character varying(20)   |   |
  | | extended |
  |
 mobile_no   | numeric(10,0)   |   | not
null | | main |
  |
 email_id| character varying(50)   |   |
  | | extended |
  |
 user_catg   | character varying(1)|   | not
null | | extended |
  |
 status  | character varying(1)|   | not
null | | extended |
  |
 created_by  | numeric(10,0)   |   | not
null | | main |
  |
 created_dt  | date|   | not
null | | plain|
  |
 aadhaar | numeric(12,0)   |   |
  | | main |
  |
 op_dt   | timestamp without time zone |   | not
null | now()   | plain|
  |
 login_ipaddress | character varying(20)   |   |
  | | extended |
  |
 forget_password | character varying(1)|   |
  | | extended |
  |
 newuser_change_password | character varying(1)|   |
  | | extended |
  |
Indexes:
"tm_user_info_pkey" PRIMARY KEY, btree (user_cd)
"idx_tm_user_info_user_id" UNIQUE, btree (user_id)
Replica Identity: FULL


*Record with an issue:-*
state_cd|off_cd|user_cd|user_name|desig_cd|user_

Re: Issue with a query while running on a remote host

[Ninad Shah]

Hi Karsten,

I apologize for the delayed response.

There is no script-related transfer happening here. It creates an issue
while using "bash@" inside a column.


Regards,
Ninad Shah

On Fri, 27 Aug 2021 at 12:35, Karsten Hilbert 
wrote:

> Deep packet inspection naively scanning for potential
> fragments of bash scripts being transferred ?
>
> Karsten
>
> Am Fri, Aug 27, 2021 at 12:32:09PM +0530 schrieb Ninad Shah:
> > Date: Fri, 27 Aug 2021 12:32:09 +0530
> > From: Ninad Shah 
> > To: pgsql-general 
> > Subject: Issue with a query while running on a remote host
> >
> > Hello All,
> >
> > While working with a PostgreSQL database, I came across an issue where
> data
> > is not being fetched over the network.
> >
> > Version : PostgreSQL 11.10
> > Operating system : RHEL 8.4
> >
> > *Issue description:*
> >
> > We tried to execute the below query on the database host using psql
> prompt,
> > it works without any issue.
> >
> > select regexp_replace(state_cd, E'[\\n\\r]+', '', 'g' ) as  state_cd,
> > off_cd, user_cd, regexp_replace(user_name, E'[\\n\\r]+', '', 'g' ) as
> > user_name, regexp_replace(desig_cd, E'[\\n\\r]+', '', 'g' ) as desig_cd,
> > regexp_replace(user_id, E'[\\n\\r]+', '', 'g' ) as user_id,
> > regexp_replace(user_pwd, E'[\\n\\r]+', '', 'g' ) as user_pwd,
> > regexp_replace(phone_off, E'[\\n\\r]+', '', 'g' ) as phone_off,
> mobile_no,
> > regexp_replace(email_id, E'[\\n\\r]+', '', 'g' ) as email_id,
> > regexp_replace(user_catg, E'[\\n\\r]+', '', 'g' ) as user_catg,
> > regexp_replace(status, E'[\\n\\r]+', '', 'g' ) as status, created_by,
> > created_dt, aadhaar, op_dt, regexp_replace(login_ipaddress, E'[\\n\\r]+',
> > '', 'g' ) as login_ipaddress, regexp_replace(forget_password,
> E'[\\n\\r]+',
> > '', 'g' ) as forget_password, regexp_replace(newuser_change_password,
> > E'[\\n\\r]+', '', 'g' ) as newuser_change_password from  tm_user_info
> > where  ( user_cd >= 1068540014 ) AND ( user_cd <= 2137079303 ) ;
> >
> > While trying to execute the same query over the network using psql
> prompt,
> > the execution doesn't finish.
> >
> > *My Analysis:*
> >
> > By digging further, we came to see that a specific record was causing the
> > issue, and by further analysis, we saw that the records that contain a
> > specific string("*bash@*") in the column user_id are not being fetched
> over
> > the network.
> >
> > To confirm that, we also changed some records manually by creating a test
> > table. And, we were able to reproduce the issue.
> >
> > vow4_xdb=# select * from vahan4.test_tbl where user_cd =2106011301;
> > .
> > .
> >
> > But, this issue doesn't occur if we try to fetch on the database host or
> > via PgAdmin4. In such cases, we get the record in a few milliseconds.
> >
> > *Surprisingly, this table has only one record.*
> >
> > There is no table/row-level lock found here.
> >
> >
> > *Table definition:-*
> >   Table "test_tbl"
> >  Column  |Type | Collation |
> > Nullable | Default | Storage  | Stats targe
> > t | Description
> >
> -+-+---+--+-+--+
> > --+-
> >  state_cd| character varying(2)|   | not
> > null | | extended |
> >   |
> >  off_cd  | numeric(5,0)|   | not
> > null | | main |
> >   |
> >  user_cd | numeric(10,0)   |   | not
> > null | | main |
> >   |
> >  user_name   | character varying(99)   |   | not
> > null | | extended |
> >   |
> >  desig_cd| character varying(10)   |   | not
> > null | | extended |
> >   |
> >  user_id | character varying(20)   |   | not
> > null | | extended |
> >   |
> >  user_pwd| character varying(100)  |   | not
> > null |

Re: oldest WAL files not removed

[Ninad Shah]

Dear Oliver,

Kindly do not remove any WAL file from pg_wal. You should consider checking
out following settings in the postgresql.conf file.

1) wal_keep_segments
- This setting enable retention of last this number of files. e.g. if this
parameter is set to 256, last 256 files will not be deleted.

2) min_wal_size and max_wal_size
- Files beyond max_wal_size limit can be removed. e.g. if max_wal_size is
set to 1GB, last few files that collectively sizes 1GB will be retained.

In any case, if files are still preserved in spite of they are eligible to
get removed, kindly execute "CHECKPOINT" command or wait for next
checkpoint to occur.


Regards,
Ninad Shah


On Wed, 1 Sept 2021 at 16:19,  wrote:

> Hi,
>
>
>
> Looking at WAL folder after a crash, I noticed that new files after
> restarting overwrite the more recent files before the crash and not the
> oldest, which was what I expected.
>
> Is that normal ?  I got only one file marked .deleted. Does that happens
> when a WAL file hase been completed updated in the database and if then
> while all oldest files aren’t marked .deleted after restarting ?
>
>
>
> Example :
>
> Crash occurs Aug 31 22:03 which is the more recent Wal file, the oldest
> file is Aug 30 17:20 (and 105 files between those two)
>
> After restarting Aug 30 17:20 is still there, Aug 31 22:03 disappeared,
> one new file is Sep 1 12:15 marked .deleted (restarting date) and one new
> Sep 1 12:36 which I guess is normal. Right now, I see an new wal file and
> the previous one marked .deleted which is ok.
>
>
>
> Why are the oldest wal files still there ?? Can I remove them ?
>
>
>
> Hope I’m clear enough and thanks for explanations,
>
>
>
> Olivier
>
>
>

Re: Issue with a query while running on a remote host

[Ninad Shah]

Hi David/Karsten,

Thank you for your response. This helped me.

This thread can be closed.


Regards,
Ninad Shah

On Tue, 31 Aug 2021 at 13:26, David G. Johnston 
wrote:

> On Tuesday, August 31, 2021, Ninad Shah  wrote:
>
>> Hi Karsten,
>>
>> I apologize for the delayed response.
>>
>> There is no script-related transfer happening here. It creates an issue
>> while using "bash@" inside a column.
>>
>>>
>>>
> That wasn’t what was meant.  Ignore the “why” for the moment, the theory
> is something in the network or OS sees that string of data and fires off a
> rule that causes the data to be filtered.  Period.  The comment about “bash
> script” was just saying that whatever the “something” is might be guessing
> that the text sequence “bash@“ has something to do with bash scripts.  It
> was just a hint.  But regardless of why the false positive exists the
> theory is that there is one happening in the environment externally to any
> PostgreSQL related software.
>
> David J.
>
>

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

[Ninad Shah]

Hi Bala,

Are your statistics updated? Also, have you used the gin
operator(gin_similarity_ops) correctly?

It is fetching just 6 records out of a million, hence, it should not go for
bitmap index scan. As bitmap index scan loads a complete index, and access
relevant pages from the table later by bitmap heap scan.


Regards,
Ninad Shah


On Thu, 2 Sept 2021 at 16:39, balasubramanian c r 
wrote:

> Hi Team
>
> We have encountered a problem in our testing environment.
> I have a scenario where I am running a similarity match for an address
> I have created a table with following number of records
> 1603423
>
> We are using pg_similarity extension in postgresql version is 13.
>
> And I have created GIN index (since i am using pg_similarity) library
> jaccard similarity method
> when I run the Explain analyze
> EXPLAIN ANALYZE select complete_address, jaccard(complete_address, 'raj
> nagar ext near ajnara integrity up ghaziabad 201017') as qsim from address
> where complete_address~??'raj nagar ext near ajnara integrity up ghaziabad
> 201017' order by qsim DESC;
> QUERY PLAN
>
> Sort (cost=5856.13..5860.14 rows=1603 width=93) (actual
> time=12101.194..12101.197 rows=6 loops=1)
>
> Sort Key: (jaccard(complete_address, 'raj nagar ext near ajnara integrity
> up ghaziabad 201017'::text)) DESC
> Sort Method: quicksort Memory: 25kB
> -> Bitmap Heap Scan on address (cost=172.43..5770.80 rows=1603 width=93)
> (actual time=3516.233..12101.172 rows=6 loops=1)
> Recheck Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity
> up ghaziabad 201017'::text)
> Rows Removed by Index Recheck: 1039186
> Heap Blocks: exact=58018
> -> Bitmap Index Scan on address_complete_address_idx1 (cost=0.00..172.02
> rows=1603 width=0) (actual time=256.037..256.037 rows=1039192 loops=1)
> Index Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity up
> ghaziabad 201017'::text)
> Planning Time: 0.141 ms
> Execution Time: 12101.245 ms
> (11 rows)
> it took 12 seconds
> following are my pgconf file settings
> shared buffer as 4GB
> work_mem 256 MB
> maintenence_work_mem 512MB
> autovacuum_work_mem 20MB
> My index definition is this "address_complete_address_idx1" gin
> (complete_address gin_similarity_ops)
>
> It is taking the index correctly. But why it took 12 seconds to process I
> really don't understand.
>
> Please help.
>
> Thanks
> C.R.Bala
>

Re: calling store procedure / insert statement never complete

[Ninad Shah]

Kindly consider checking tcp_keepalive in PostgreSQL as well as OS kernel.
By default, it is 2 hours(7200).


Regards,
Ninad Shah

On Thu, 2 Sept 2021 at 11:43, Trang Le  wrote:

> Hi Mladen,
>
> I missed previous emails, so I am creating a new one.
>
> I think the problem is coming from front-end side. when I run store
> procedure, job in pg_locks is done, data is inserted, log table shows that
> job completed with number of insert. However, store procedure in front-end
> side is still show running until the sever closed connection. The same
> problem when I run the query statement only, insert about 190k rows, when
> check target table, data is insert, but in the front-end side is still show
> wait until server close connection.
>
> I use pgamin4 5.3.
>
> Error: Server closed the connection unexpectedly. This probably means the
> server terminated abnormally before or while processing the request.
>
> Could you please double check?
>
> Thanks and regards,
> Trang
>

Re: memory consumption of memory for postgres db container

[Ninad Shah]

No need to worry. This isn't really a concern.

No operating system releases memory upon using it, but caches it once the
program finishes. This actually saves time as memory blocks can be
allocated in less time than loading new blocks.
However, if it's required to release memory, it can be performed via a few
commands.


Regards,
Ninad Shah

On Thu, 2 Sept 2021 at 20:05, M Tarkeshwar Rao <
m.tarkeshwar@ericsson.com> wrote:

> Hi all,
>
>
>
> We did a stability test for our product, where we continuously inserting
> data into postgres tables. We did this for three days continuously.
>
> Memory utilization of our Postgres container continuously increasing.
>
>
>
>
>
> Can you please provide us the clue why it is not constant and continuously
> increasing?
>
>
>
> Regards
>
> Tarkeshwar
>

Re: memory consumption of memory for postgres db container

[Ninad Shah]

You may checkout using "free -m" and may see how much memory is cache.

If you are using RHEL, you may clear cache using below commands.

sync; echo 1 > /proc/sys/vm/drop_caches; sync

Though this is going to release some memory from the cache, it may fill up
the cache again.


Regards,
Ninad Shah

On Thu, 2 Sept 2021 at 20:41, M Tarkeshwar Rao <
m.tarkeshwar@ericsson.com> wrote:

> Can you please suggest few commands?
>
>
>
> Regards
>
> Tarkeshwar
>
>
>
> *From:* Ninad Shah 
> *Sent:* Thursday, September 2, 2021 8:30 PM
> *To:* M Tarkeshwar Rao 
> *Cc:* pgsql-gene...@postgresql.org
> *Subject:* Re: memory consumption of memory for postgres db container
>
>
>
> No need to worry. This isn't really a concern.
>
>
>
> No operating system releases memory upon using it, but caches it once the
> program finishes. This actually saves time as memory blocks can be
> allocated in less time than loading new blocks.
>
> However, if it's required to release memory, it can be performed via a few
> commands.
>
>
>
>
>
> Regards,
>
> Ninad Shah
>
>
>
> On Thu, 2 Sept 2021 at 20:05, M Tarkeshwar Rao <
> m.tarkeshwar@ericsson.com> wrote:
>
> Hi all,
>
>
>
> We did a stability test for our product, where we continuously inserting
> data into postgres tables. We did this for three days continuously.
>
> Memory utilization of our Postgres container continuously increasing.
>
>
>
>
>
> Can you please provide us the clue why it is not constant and continuously
> increasing?
>
>
>
> Regards
>
> Tarkeshwar
>
>

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

[Ninad Shah]

Try the pg_tgrm extension. It is a rich set of operators.


Regards,
Ninad Shah

On Thu, 2 Sept 2021 at 23:39, balasubramanian c r 
wrote:

> HI Ninad
>
> Thanks for your reply.
> If bitmap index should not be used. Do i need to disable it for the time
> being and carry out the test.
>
> The documentation in pg_similarity shows that index can be created on text
> column using gin with gin_similarity_ops.
> The same way the index is created like
> CREATE INDEX on address using GIN(complete_address gin_similarity_ops);
>
> AFAIK I have not seen any other operators other than gin in the
> pg_smilarity extension.
>
> Thanks
> C.R.Bala
>
> On Thu, Sep 2, 2021 at 8:17 PM Ninad Shah 
> wrote:
>
>> Hi Bala,
>>
>> Are your statistics updated? Also, have you used the gin
>> operator(gin_similarity_ops) correctly?
>>
>> It is fetching just 6 records out of a million, hence, it should not go
>> for bitmap index scan. As bitmap index scan loads a complete index, and
>> access relevant pages from the table later by bitmap heap scan.
>>
>>
>> Regards,
>> Ninad Shah
>>
>>
>> On Thu, 2 Sept 2021 at 16:39, balasubramanian c r 
>> wrote:
>>
>>> Hi Team
>>>
>>> We have encountered a problem in our testing environment.
>>> I have a scenario where I am running a similarity match for an address
>>> I have created a table with following number of records
>>> 1603423
>>>
>>> We are using pg_similarity extension in postgresql version is 13.
>>>
>>> And I have created GIN index (since i am using pg_similarity) library
>>> jaccard similarity method
>>> when I run the Explain analyze
>>> EXPLAIN ANALYZE select complete_address, jaccard(complete_address, 'raj
>>> nagar ext near ajnara integrity up ghaziabad 201017') as qsim from address
>>> where complete_address~??'raj nagar ext near ajnara integrity up ghaziabad
>>> 201017' order by qsim DESC;
>>> QUERY PLAN
>>>
>>> Sort (cost=5856.13..5860.14 rows=1603 width=93) (actual
>>> time=12101.194..12101.197 rows=6 loops=1)
>>>
>>> Sort Key: (jaccard(complete_address, 'raj nagar ext near ajnara
>>> integrity up ghaziabad 201017'::text)) DESC
>>> Sort Method: quicksort Memory: 25kB
>>> -> Bitmap Heap Scan on address (cost=172.43..5770.80 rows=1603 width=93)
>>> (actual time=3516.233..12101.172 rows=6 loops=1)
>>> Recheck Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity
>>> up ghaziabad 201017'::text)
>>> Rows Removed by Index Recheck: 1039186
>>> Heap Blocks: exact=58018
>>> -> Bitmap Index Scan on address_complete_address_idx1 (cost=0.00..172.02
>>> rows=1603 width=0) (actual time=256.037..256.037 rows=1039192 loops=1)
>>> Index Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity
>>> up ghaziabad 201017'::text)
>>> Planning Time: 0.141 ms
>>> Execution Time: 12101.245 ms
>>> (11 rows)
>>> it took 12 seconds
>>> following are my pgconf file settings
>>> shared buffer as 4GB
>>> work_mem 256 MB
>>> maintenence_work_mem 512MB
>>> autovacuum_work_mem 20MB
>>> My index definition is this "address_complete_address_idx1" gin
>>> (complete_address gin_similarity_ops)
>>>
>>> It is taking the index correctly. But why it took 12 seconds to process
>>> I really don't understand.
>>>
>>> Please help.
>>>
>>> Thanks
>>> C.R.Bala
>>>
>>

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

[Ninad Shah]

I see an issue with the operator. GIN index is capable of performing index
scan and index-only scan.


Regards,
Ninad Shah

On Thu, 2 Sept 2021 at 20:57, Michael Lewis  wrote:

> This is showing many false positives from the index scan that get removed
> when the actual values are examined. With such a long search parameter,
> that does not seem surprising. I would expect a search on "raj nagar
> ghaziabad 201017" or something like that to yield far fewer results from
> the index scan. I don't know GIN indexes super well, but I would guess that
> including words that are very common will yield false positives that get
> filtered out later.
>
>>

Re: prevent WAL replication to fill filesystem

[Ninad Shah]

These suggestions are appropriate. However, if you are ready to lose your
replica to keep production running, there are a couple of methods.

1) Set archive_command to "/bin/true"
2) rename .ready files in archive_status to .done.

Again, I personally do not recommend this, so consider this as a last
resort to save your system from the space crunch issue.


Regards,
Ninad Shah


On Tue, 31 Aug 2021 at 15:08, Jehan-Guillaume de Rorthais 
wrote:

> On Tue, 31 Aug 2021 10:53:45 +0200
> Laurenz Albe  wrote:
>
> > On Tue, 2021-08-31 at 10:36 +0200, basti wrote:
> > >  have a old PG 9.6 with WAL replication.
> > > for some reason the rsync was failed and the filesystem was filled by
> > > pg_xlog files.
> > > As result PG stops working.
> > >
> > > Is there a way to prevent WAL replication to fill filesystem by xlog
> > > files in case of failure?
>
> No.
>
> You have to size your partition big enough to handle few days of normal
> WAL production, monitor the archiving success and react quickly to fix
> things
> when it fails.
>
> > Upgrade to v13 and set "max_slot_wal_keep_size".
>
> How this would prevent the failure if $OP doesn't (want to) use slots ? He
> speaks about rsync, so it might be purely archiving error, not streaming.
>
> Regards,
>
>
>

Re: How to restore roles into new Database server?

[Ninad Shah]

This can be accomplished by taking a global dump. pg_dumpall utility has an
option "-g" to accomplish that.


Regards,
Ninad Shah

On Wed, 15 Sept 2021 at 19:24, Sridhar Parepalli 
wrote:

> Hello Pros,
> Is there a quick way to create roles from database server to another db
> server?
> Regards,
> Sridhar Parepalli
>
>
>

Re: autocommit for multi call store procedure

[Ninad Shah]

Have you used an EXCEPTION block in the procedure?


Regards,
Ninad Shah

On Thu, 16 Sept 2021 at 13:06, Trang Le  wrote:

> Hi guys,
>
> I am using pgadmin4 to interact with Postgres database. For now I would
> like to run 2 store procedure (those have commit statement in begin end
> block). I enable autocommit and run call 2 store procedures at the same
> time. However, there is an error with invalid transaction termination.
>
> Could you help me on this issue?
>
> Thanks,
> Trang
>

Re: Question about behavior of conditional indexes

[Ninad Shah]

On Tue, 21 Sept 2021 at 15:59, Koen De Groote  wrote:

> Greetings all,
>
> Working on postgres 11.
>
> I'm researching an index growing in size and never shrinking, and not
> being used anymore after a while.
>
> The index looks like this:
>
> "index002" btree (action_performed, should_still_perform_action, 
> action_performed_at DESC) WHERE should_still_perform_action = false AND 
> action_performed = true
>
> So, there are 2 boolean fields, and a conditional clause for both. The
> table has about 50M rows, the index barely ever goes over 100K matched rows.
>
> The idea is to search for rows matching these conditions quickly, and then
> update them. This happens daily.
>
> This means the condition no longer match the index. At this point, does
> the row get removed from the index? Or does it stay in there as a dead row?
>
> I'm noticing index bloat on this index and am wondering if all these
> updated rows just stay in the index?
>
> The index currently sits at 330MB. If I drop and re-create it, it's 1.5MB.
>
> A cronjob runs a vacuum once per day, I can see the amount of dead rows
> dropping in monitoring software.
>
   - This doesn't reclaim the space. VACUUM operation cleans up space above
the upper edge(High-water mark). Interleaved fragmentation will be marked
for reuse.

>
> But should this also take care of indexes? In postgres 11, you can't
> reindex concurrently, so I was wondering if indexes are skipped by vacuum?
> Or only in case of conditional indexes?
>
  - They aren't left untouched by VACUUM, but as I mentioned reclaiming
space is not a job of VACUUM operation. You must execute VACUUM FULL.
AFAIK, reindexing the 1.5MB index may not need a lot of time(regardless of
concurrent reindexing feature).

>
>
>
> So I'm wondering if the behavior is as I described.
>
> Regards,
> Koen De Groote
>

Re: spannerdb migration to PostgreSQL

[Ninad Shah]

Frankly speaking, nothing is impossible. But, I don't see any tools
available for the same.

I found the below link, which has migrated using code only.

https://niravshah2705.medium.com/spanner-database-to-postgres-90740424f744


Regards,
Ninad Shah

On Tue, 19 Oct 2021 at 14:58, Pawan Sharma  wrote:

> Hello All,
>
> Is it possible to migrate the spannerdb database into PostgreSQL? If yes,
> which tool can we refer to migrate it?
>
> -Pawan
>
>
>
>

Issue with pg_basebackup v.11

[Ninad Shah]

Hello experts,

I am facing an issue with a customer's production server while trying to
take backup using pg_basebackup.

Below is the log from pg_basebackup execution.

* 115338208/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115355616/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115372640/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115389568/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115405792/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115423776/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.1)
 115440640/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.2)
 115454656/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.2)
pgbasebackup: could not read COPY data: could not receive data from server:
Connection timed out
pgbasebackup: removing contents of data directory
"/u01/PostgreSQL/11/datastaging"*

It copied nearly 110 GB of data and exited. Initially, we suspected it as a
network/OS issue. However, we tried to copy a 150 GB large file over the
network, which finished successfully.

What I observed is that it takes a couple of hours between below 2 lines.

 115454656/1304172127 kB (8%), 0/1 tablespace
(...atastaging/base/115868/154220.2)
pgbasebackup: could not read COPY data: could not receive data from server:
Connection timed out

In other words, it run for an hour, and later, it takes 2 hours before it
times out.

Can someone please help me out here?


Regards,
Ninad Shah

Re: Issue with pg_basebackup v.11

[Ninad Shah]

Hey Tom,

Thank you for your response. Actually, when we copy data using scp/rsync,
it works without any issue. But, it fails while attempting to transfer
using pg_basebackup.

Would keepalive setting address and mitigate the issue?


Regards,
Ninad Shah

On Fri, 22 Oct 2021 at 21:39, Tom Lane  wrote:

> Ninad Shah  writes:
> > What I observed is that it takes a couple of hours between below 2 lines.
>
> >  115454656/1304172127 kB (8%), 0/1 tablespace
> > (...atastaging/base/115868/154220.2)
> > pgbasebackup: could not read COPY data: could not receive data from
> server:
> > Connection timed out
>
> We have heard reports of network connections dropping while pg_basebackup
> is busy doing something disk-intensive such as fsync'ing.  The apparent
> 2-hour delay here does not mean that pg_basebackup was out to lunch for
> 2 hours; more likely that reflects the TCP timeout delay before the kernel
> realizes that the connection is lost.  The actual blame probably resides
> with some firewall or router that has a short timeout for idle
> connections.
>
> I'd try turning on fairly aggressive TCP keepalive settings for the
> connection, say keepalives_idle=30 or so.
>
> regards, tom lane
>

Re: Issue with pg_basebackup v.11

[Ninad Shah]

Thanks Tom.


Regards,
Ninad Shah

On Sat, 23 Oct 2021 at 20:12, Tom Lane  wrote:

> Ninad Shah  writes:
> > Would keepalive setting address and mitigate the issue?
>
> [ shrug... ]  Maybe; nobody else has more information about this
> situation than you do.  I suggested something to experiment with.
>
> regards, tom lane
>

Re: Doubt in pgbouncer

[Ninad Shah]

There is no equation to determine the best value. It depends on your
environment.


Regards,
Ninad Shah

On Wed, 3 Nov 2021 at 19:18, Rama Krishnan  wrote:

>
> Hi All,
>
> What is the limit value for fixing connection timeout issues?
>
> As i searched in google and verified pgbouncer document
> client_login_timeout
>
> If a client connects but does not manage to log in in this amount of time,
> it will be disconnected. Mainly needed to avoid dead connections stalling
> SUSPEND and thus online restart. [seconds]
>
> Default: 60.0
>
> Can you pls suggest the best value ? or how to decide this parameter value
>
>
>
> Regards
>
> A.Rama Krishnan
>

Re: Streaming replication versus Logical replication

[Ninad Shah]

Yes, it is going to resolve the issue because streaming is completely a
slave(with few exceptions). Even the VACUUM operation gets replicated
through the master, which is not a case with logical replication. In
logical replication, only data from a few tables gets replicated. In terms
of database administration, they are different entities.

In case the subscriber has long-running queries, unlike streaming
replication, it does not affect synchronisation operations.


Regards,
Ninad Shah


On Thu, 4 Nov 2021 at 21:16, Alanoly Andrews  wrote:

> We are currently running some long-running SELECT queries on the
> replication database in a streaming replication pair. Some of these queries
> can run for 1 hour or more. To avoid errors related to "data no more being
> available" on the replication due to vacuuming of old data on the primary
> database, we have set the following parameters: max_standby_archive_delay
> = -1, max_standby_streaming_delay = -1, hot_standby_feedback = on. With
> these set, the long queries are able to run to completion, but there is
> table bloat on both the primary and the replicated databases, leading to
> throughput delay on the primary production database.
>
> Will this issue exist if we use "logical replication" instead? With the
> above three parameters set back to normal, will the replicated database get
> overwritten when vacuuming runs on the primary and removes old data? If it
> does not, will there be table bloat on the primary database? What is the
> mechanism by which data changes on the "publisher" are propagated to the
> "subscriber"? What happens when the subscriber database has an long-running
> query?
>
> Thanks.
>
> Alanoly Andrews.
>
>
> This e-mail may be privileged and/or confidential, and the sender does not
> waive any related rights and obligations. Any distribution, use or copying
> of this e-mail or the information it contains by other than an intended
> recipient is unauthorized. If you received this e-mail in error, please
> advise me (by return e-mail or otherwise) immediately.
>
> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux
> droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou
> copie de ce message ou des renseignements qu'il contient par une personne
> autre que le (les) destinataire(s) désigné(s) est interdite. Si vous
> recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par
> retour de courriel ou par un autre moyen.'. If the disclaimer can't be
> applied, attach the message to a new disclaimer message.
>