question on streaming replication

[Atul Kumar]

Hi,

I have postgres edb 9.6 version, i have below query to solve it out.

i have configured streaming replication having master and slave node
on same  server just to test it.

All worked fine but when i made slave service stop, and create some
test databases in master, after then i made slave service start, slave
didn't pick the changes.

The replication was on async state.

Then after doing some search on google i tried to make it sync state
but even making changes in postgresql.conf file I am neither getting
sync state nor getting any changes on slave server.

Please suggest the needful.


Regards,
Atul

how to check PEM version

[Atul Kumar]

Hi,

I have installed pem in centos 7, now i want to check the version of
PEM using sql query.

Please let me know, how should i do that.

My postgres version is 11.8


Regards,
Atul

pgbouncer installation example (Step by step)

[Atul Kumar]

Hi Team,

Please share a clean example of installing, configuring and testing pgBouncer.

Your official links are not organized so I need an example of
PgBouncer with organized steps.



Regards,
Atul

remgr installation and configuration steps required

[Atul Kumar]

Hi,

I have 3 different servers, i want to configure streaming replication
(1 master and 2 slaves) using repmanager.

Please share the installation and configuration steps for the same.

Note: the steps should be organized.


Regards,
Atul

database shutting down

[Atul Kumar]

Hi,

I am configuring repmgr, so in postgresql.conf when i changed the
parameter share_preload_libraries='repmgr', my database server is not
starting.

When i comment that parameter, database server started.


Please help why that parameter is stopping database to start.



Regards,
Atul

High CPU utilization

[Atul Kumar]

Hi,

I have high CPU utilization on my server, when I checked
pg_stat_activity , I found that I am attaching here as an attachment.


I am unbale to catch the actual cause.

Please help me in rectifying  so that CPU consumption can be reduced.


Regards,
Atul

psql asks for password despite configuring trust authentication

[Atul Kumar]

hi,

My problem is, that I am always asked for password when trying to
interact with the database or access it, although the authentication
is set to trust for all users and databases.

Please help.



Regards,
Atul

archive command in streaming replication in windows server

[Atul Kumar]

hi,

I am trying to configure streaming replication on windows server.

i have postgres version 10

after successful installation of postgres, I create a archive
directory "C:\Program Files\PostgreSQL\10\archive_files"  and here in
archive_flies, I need to copy the wal files.

in postgresql conf file I gave path in archive_command like this:

'copy %p C:\Program Files\PostgreSQL\10\archive_files\%f'

After successfully restarting the postgres service, I could not find
any file in the archive directory.

and when I am giving command
show archive_command

I get this as an output

"copy %p C:Program FilesPostgreSQL archive_files%f"

So please help in setting me the correct path format.

My target is to configure streaming replication on same windows
machine using 2 differnet instances having different ports.(like
example master will be on 5432 and slave will be 5433).



Regards,
Atul

Re: archive command in streaming replication in windows server

[Atul Kumar]

hi,

Still it is not getting copied in the archive directory

show archive_command
"copy "%p" "C:\Program Files\PostgreSQL\10\archive_files\%f""

in postgresql.conf
archive_command = 'copy "%p" "C:\\Program
Files\\PostgreSQL\\10\\archive_files\\%f"'


and when I restarted the postgres service using command prompt I am
getting the below
alert in cmd
C:\Program Files\PostgreSQL\10\bin>0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.
0 file(s) copied.




Please help.



Regards,
Atul















On 10/30/20, Andreas Kretschmer  wrote:
>
>
> Am 29.10.20 um 20:12 schrieb Atul Kumar:
>> hi,
>>
>> I am trying to configure streaming replication on windows server.
>>
>> i have postgres version 10
>>
>> after successful installation of postgres, I create a archive
>> directory "C:\Program Files\PostgreSQL\10\archive_files"  and here in
>> archive_flies, I need to copy the wal files.
>>
>> in postgresql conf file I gave path in archive_command like this:
>>
>> 'copy %p C:\Program Files\PostgreSQL\10\archive_files\%f'
>>
>> After successfully restarting the postgres service, I could not find
>> any file in the archive directory.
>>
>> and when I am giving command
>> show archive_command
>>
>> I get this as an output
>>
>> "copy %p C:Program FilesPostgreSQL archive_files%f"
>>
>> So please help in setting me the correct path format.
>
> from the fine documentation an example:
>
> archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
> /mnt/server/archivedir/%f'  # Unix
> archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows
>
>
> https://www.postgresql.org/docs/current/continuous-archiving.html
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

identify partitioning columns and best practices of partitioning in prod enviornments

[Atul Kumar]

Hi,

I want to about best practices of partitioning in prod environments
and how to identify partitioning columns.


Regards,
Atul

autovacuum recommendations for Large tables

[Atul Kumar]

Hi,

I have a large table having no. of live tuples approx 7690798868 and
no. of dead tuples approx 114917737.

So Please share autovacuum tuning recommendations for this table so
that our time can be spent better than repeatedly vacuuming large
tables.


Please help.



Regards,
Atul

Re: autovacuum recommendations for Large tables

[Atul Kumar]

Hi,

Could you help me by explaining in simple words, as I am new to postgres.

What value of which parameter should I set and why.

I only have this one big table in the database of size 3113 GB with rows
7661353111.

Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_
factor=0.2,autovacuum_analyze_scale_factor=0.2}

Please help.






Regards
Atul






On Tuesday, November 17, 2020, Olivier Gautherot 
wrote:

> Hi Atul,
>
> I would start with a factor of 0.1 (10 parts per million) and explore
> down to 0.01.
>
> I did some massive updates on a partition with 12 millions rows and my
> factor was 0.001.
>
> Depending on the number of big tables you have in the database, you may
> wish to reduce the number of parallel workers.
>
> Hope it helps
> --
> Olivier Gautherot
> Tel: +33 6 02 71 92 23
> https://www.linkedin.com/in/ogautherot/
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>  Libre
> de virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_-4472748644679516424_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar  wrote:
>
>> Ok,
>>
>> Right Now the autovacuum setting for that table is set to
>> {autovacuum_enabled=true,autovacuum_vacuum_scale_
>> factor=0.2,autovacuum_analyze_scale_factor=0.2}
>>
>>
>> So Please suggest, how much should i set atleast, to avoid increasing
>> in dead tuple.
>>
>>
>>
>> Regards,
>> Atul
>>
>>
>>
>> On 11/16/20, Olivier Gautherot  wrote:
>> > Hi Atul,
>> >
>> > Le lun. 16 nov. 2020 à 18:38, Atul Kumar  a
>> écrit :
>> >
>> >> Hi,
>> >>
>> >> I have a large table having no. of live tuples approx 7690798868 and
>> >> no. of dead tuples approx 114917737.
>> >>
>> >> So Please share autovacuum tuning recommendations for this table so
>> >> that our time can be spent better than repeatedly vacuuming large
>> >> tables.
>> >>
>> >
>> > Vacuuming will affect you when it has a lot of work to do. I would try
>> to
>> > trigger an autovacuum every 10,000 insert/update to minimize the impact.
>> > You can play with the parameter autovacuum_vacuum_scale_factor for that
>> > table.
>> >
>> > Good luck
>> > Olivier
>> >
>>
>

vacuum vs vacuum full

[Atul Kumar]

Hi,

We have a table of 3113GB, and we are planning to vacuum it in non
business hours i.e. 12AM to 4AM, So my queries are:

1. What should be perform on the table Vacuum or Vacuum full ?
2. Do we need to perform Analyze also?
3. Will the operation be completed in the given time frame? how to
check the same.
4. Who acquire lock on table vacuum or vacuum full.
5. If the activity goes beyond time frame, do we have any option to do
continue doing t without acquiring lock on the table ?

If you also need the structure of the table, Please let me know.

Please help me by responding my query wise.



Regards,
Atul

maintenance_work_mem

[Atul Kumar]

Hi,

I have below queries:

1. How do i check the maintenance_work_mem for current session, before
setting some other value for this parameter for the same session.

2. and How do I set maintenance_work_mem for a session only, and how
will it be "rollback" once my maintainance work is done, Do I need to
execute any command for that or just closing the session will rollback
what I set for the session.


Please respond me query wise.



Regards,
Atul

meaning of (to prevent wraparound) ..??

[Atul Kumar]

Hi,

I have autovacuum running in background from last 30-40 minutes but I
noticed that it is with "(to prevent wraparound)" say for example
autovacuum: VACUUM trk.move (to prevent wraparound).

So what is the meaning of "(to prevent wraparound)" here ?

Will it impact the autovacuum anyhow ?

Please help me.



Regards,
Atul

Re: meaning of (to prevent wraparound) ..??

[Atul Kumar]

Thanks Jessica. Could help me out by sharing documents that can help me
understand “to prevent wraparound “ in simplest way, postgres doc is little
bit harder for a newbee like me.




Regards
Atul




On Thursday, November 26, 2020, Jessica Sharp 
wrote:

> Hi Atul,
>
> This means autovacuum is doing it’s job — most likely no need to be
> alarmed here based on this alone. The autovacuum is helping avoid a txid
> limit. This is part of PostgreSQL MVCC. You may check that section of the
> documentation for more information on this.
>
> On Wed, Nov 25, 2020 at 22:54 Atul Kumar  wrote:
>
>> Hi,
>>
>> I have autovacuum running in background from last 30-40 minutes but I
>> noticed that it is with "(to prevent wraparound)" say for example
>> autovacuum: VACUUM trk.move (to prevent wraparound).
>>
>> So what is the meaning of "(to prevent wraparound)" here ?
>>
>> Will it impact the autovacuum anyhow ?
>>
>> Please help me.
>>
>>
>>
>> Regards,
>> Atul
>>
>>
>> --
> Kind regards,
> Jessica Sharp
> -
> (469) 602-2363
>

error on connecting port 5432

[Atul Kumar]

Hi,

When I m creating a test db user using below command using root OS user
 sudo -u postgres createuser -p 5432 --pwprompt testuser

I am getting the attached error of port.

But my postgres services are running fine on port 5432, I am sharing
the screenshot of that also.

So please help me in letting me know why I am getting the above error
when I am connected to the root OS user?

Note - I am able to create a test db user when I am connected to
postgres using psql command.


Regards,
Atul

Re: error on connecting port 5432

[Atul Kumar]

Hi,

Just to clarify that I am at root OS user, trying to create a test user
using postgres user(-u) with create user command.

Error screenshots are already shared with you.

Please re-check the same.





Regards
Atul



On Tuesday, December 1, 2020, Adrian Klaver 
wrote:

> On 12/1/20 12:51 AM, Atul Kumar wrote:
>
>> Hi,
>>
>> When I m creating a test db user using below command using root OS user
>>   sudo -u postgres createuser -p 5432 --pwprompt testuser
>>
>> I am getting the attached error of port.
>>
>> But my postgres services are running fine on port 5432, I am sharing
>> the screenshot of that also.
>>
>
> In future please include this as text not images.
>
>
>> So please help me in letting me know why I am getting the above error
>> when I am connected to the root OS user?
>>
>
> You are not connecting as OS root you are connecting as the database user
> testuser.
>
> Does /var/run/postgresql/.s.PGSQL.5432 exits?
>
> Do you have more then one instance of Postgres on this machine?
>
> If so how where they installed and more importantly where they installed
> differently?
>
> As a test try running the command with addition of -h localhost.
>
>
>> Note - I am able to create a test db user when I am connected to
>> postgres using psql command.
>>
>>
>> Regards,
>> Atul
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: error on connecting port 5432

[Atul Kumar]

hi,

There is no directory of postgresql in /var/run.

Please help me out.



Regards,
Atul




On 12/1/20, Tom Lane  wrote:
> Atul Kumar  writes:
>> Just to clarify that I am at root OS user, trying to create a test user
>> using postgres user(-u) with create user command.
>
> Given the reference to /var/run/postgresql, I'm suspecting that you
> are running a server that thinks it should put its socket in /tmp,
> but you have some copies of libpq on the machine that were built with
> default socket location /var/run/postgresql.  When you are root, you
> are very likely using a different PATH that is finding a different
> createuser program linked to a different libpq.so than when you are
> not root.
>
> A possible workaround is to add "-h /tmp" to your command when
> running as root.  Eventually you'd want to try to not have
> multiple postgres installations on the machine.
>
>   regards, tom lane
>

Re: error on connecting port 5432

[Atul Kumar]

Thanks a lot Tom, I appended the -h /tmp and it worked.

I need just one more help from you.

Could you tell me that why & how that socket file existed in /tmp directory.

What is the practice to make sure that this file (.s.PGSQL.5432)
should be inside /var/run directory ? so that it will not throw such
error.

Please help me.






Regards,
Atul



On 12/1/20, Tom Lane  wrote:
> Atul Kumar  writes:
>> Just to clarify that I am at root OS user, trying to create a test user
>> using postgres user(-u) with create user command.
>
> Given the reference to /var/run/postgresql, I'm suspecting that you
> are running a server that thinks it should put its socket in /tmp,
> but you have some copies of libpq on the machine that were built with
> default socket location /var/run/postgresql.  When you are root, you
> are very likely using a different PATH that is finding a different
> createuser program linked to a different libpq.so than when you are
> not root.
>
> A possible workaround is to add "-h /tmp" to your command when
> running as root.  Eventually you'd want to try to not have
> multiple postgres installations on the machine.
>
>   regards, tom lane
>

Required checkpoints occurs too frequently

[Atul Kumar]

Hi,

We are getting this alert frequently "Required checkpoints occurs too
frequently" on postgres version 11.8

The RAM of the server is 16 GB.

and we have already set the max_wal_size= 4096 MB
min_wal_size= 192 MB.

Please help me in optimizing the same to avoid this alert.


Regards,
Atul

"Required checkpoints occurs too frequently"

[Atul Kumar]

Hi,

We are getting this alert frequently "Required checkpoints occurs too
frequently" on postgres version 11.8

The RAM of the server is 16 GB.

and we have already set the max_wal_size= 4096 MB
min_wal_size= 192 MB.

Please help me in optimizing the same to avoid this alert.


Regards,
Atul

Re: "Required checkpoints occurs too frequently"

[Atul Kumar]

Ok I m sorry, I had no idea of it.

On Friday, December 11, 2020, Stephen Frost  wrote:

> Greetings,
>
> * Atul Kumar (akumar14...@gmail.com) wrote:
> > Please help me in optimizing the same to avoid this alert.
>
> Please don't post the same question to multiple lists, nor post
> the same question over and over to the same list with little time
> between them.
>
> Thanks,
>
> Stephen
>

protect data of postgres database

[Atul Kumar]

Hi,

How can I protect my data by using any security on my postgres database.


Please help me.


Regards,
Atul

Re: PostgreSQL HA

[Atul Kumar]

Use xdb, simpler and better

On Saturday, December 26, 2020, venkata786 k  wrote:

> Hi Team,
>
> Could you please share postgresql's best active(R/W)-active(R/W) (multi
> master replication) solutions.
> My Team is thinking about implementing active-active replacing
> master-slave.
>
> Regards,
> Venkat
>
>
>
>
>
>
> 
>  Virus-free.
> www.avast.com
> 
> <#m_1212501648721883306_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

alter system command

[Atul Kumar]

hi,

I have set archive_command like below:

alter system set archive_command ='pgbackrest --stanza=main archive-push %';

then I reloaded the conf file

select pg_reload_conf();


now when i checked it using the show command like it gave me correct output

postgres=# show archive_command;
 archive_command
-
 pgbackrest --stanza=main archive-push %


but...

while checking the postgresql.conf file it didn't show me above
output, the parameter acrhive_command is still set with default value

archive_command = '/bin/true/'  # command to use to archive a
logfile segment

So please help me in giving the reason of it that even after reloading
the conf file why it didn't set the value in postgresql.conf file ?


Regards,
Atul

postgres optimization

[Atul Kumar]

Hi,

I am new to postgres and I find optimization as a challenge in
postgres being a newbee.

As I am unable to understand explain plan and its components like
merge join, hash join, loop join etc.

I get totally confused about how to read it and how to understand what
thing is making my query slow.

I will be grateful if you can help me in understanding the basics and
slowly in depth optimization, by understanding explain plan.

I really need to have the easiest language to understand, the explain
plan and how to read long explain plans to optimize the query (with
examples).




Regards,
Atul

migration from postgres to enterprosedb

[Atul Kumar]

Hi,

I have postgres setup of having version postgres 9.5 and I want to
migrate it to enterprisedb 10.

So Please help me for the same as I am newbee for postgres migration.




Regards,
Atul

upgrade postgres 9.5 to 9.6

[Atul Kumar]

Hi,

I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
is in TBs and I want to do it in minimum downtime (2-3 hours) so
please help me how should I perform it.


Please share the document, if possible, it will be grateful.



Regards,
Atul

Re: upgrade postgres 9.5 to 9.6

[Atul Kumar]

May you provide me steps to perform it (logical replication upgrade).

I will be thankful to you.


Regards
Atul



On Friday, January 15, 2021, Enrico Pirozzi  wrote:

> Hi Atul,
> you could use logical replication and do an hot upgrade between the 2
> servers with a zero downtime ;)
>
> If you want to use logical replication between a 9.5 , 9.6 version you
> could use for example pglogical.
>
> You could achieve the same result using an external logic replication tool
> like slony, although pglogical is more powerful.
>
> I hope that this can help you
>
> Regards,
> Enrico
>
> On Fri, 15 Jan 2021 18:47:51 +0530
> Atul Kumar  wrote:
>
> > Hi,
> >
> > I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
> > is in TBs and I want to do it in minimum downtime (2-3 hours) so
> > please help me how should I perform it.
> >
> >
> > Please share the document, if possible, it will be grateful.
> >
> >
> >
> > Regards,
> > Atul
>
> --
> Enrico Pirozzi 
>

Re: Accounting for between table correlation

[Atul Kumar]

Hi Alexander,

As per Ron, you are not supposed to ask your questions here.

As According to him, we should keep on doing research on internet rather
than asking for support directly even you have done enough research and
until unless “Ron” won’t be satisfied you have to do keep on researching.





Regards
Atul


On Friday, January 15, 2021, Ron  wrote:

> On 1/15/21 9:19 AM, Alexander Stoddard wrote:
>
>> I am having ongoing trouble with a pair of tables, the design of which is
>> beyond my control.
>>
>> There is a 'primary' table with hundreds of millions of rows. There is
>> then a 'subclass' table ~ 10% of the primary which has additional fields.
>> The tables logically share a primary key field (although that is not
>> annotated in the schema).
>>
>> Membership of the subclass table has high correlation with fields in the
>> primary table - it is very much not random. It seems query plans where the
>> two tables are joined are 'unstable'. Over time very different plans can
>> result  even for unchanged queries and some plans are exceedingly
>> inefficient.
>>
>> I think what is going on is that the query planner assumes many fewer
>> rows are going to join to the subtable than actually do (because of the
>> strong correlation).
>>
>> Can anyone offer any advice on dealing with this scenario (or better
>> diagnosing it)?
>>
>
> Do the tables get analyzed on a regular basis?
>
> --
> Angular momentum makes the world go 'round.
>
>
>

Re: Accounting for between table correlation

[Atul Kumar]

Hi Ron,

I have a simple mindset that If I don’t know about something about anyone’s
query I don’t respond.

Please start learning to not to interrupt or raising useless questions/
phrases on someone’s query bcz it makes you judgemental.

So please take an advice to not to interrupt if you can’t help. It would be
helpful for all of us.



Regards
Atul


On Friday, January 15, 2021, o1bigtenor  wrote:

> On Fri, Jan 15, 2021 at 9:29 AM Atul Kumar  wrote:
> >
> > Hi Alexander,
> >
> > As per Ron, you are not supposed to ask your questions here.
> >
> > As According to him, we should keep on doing research on internet rather
> than asking for support directly even you have done enough research and
> until unless “Ron” won’t be satisfied you have to do keep on researching.
> >
> >
> Mr Atul
>
> With respect - - - -
> When you asked your question I thought you had a valid question but
> really hadn't done any research for an answer.
> The list is here to help you help yourself.
> I have found myself answering my own questions sometimes. But that
> also means that I now have a better idea of what's going on.
> Just demanding answers with all the steps - - - - well - - - you are
> hindering your own learning.
> I would suggest that instead of barging into others threads
> complaining that you didn't get the answer you wanted that you try
> actually working on your own problem.
> (I am not a senior listee here - - - - just not happy with someone who
> is grumbly AND doesn't want to help themselves!)
>
> (To the admins - - - - if I have overstepped - - - please advise!)
>
> Regards
>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Hi o1bigtenor,

Now what will you say about below query that he should have come with some
research before asking here ?

On Friday, January 15, 2021, svsn raju  wrote:

> Hi All,
>
> Can someone please suggest some tools to monitor and tune postgres
>
>
> Sent from Yahoo Mail on Android
> 
>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Again Thanks alot David for ur response.

You expect everyone to be perfect which cannot happen.

And related to thread, if someone has joined this thread today then it is
obvious that he may not be able to find the previous threads as well.




Regards
Atul

On Friday, January 15, 2021, David G. Johnston 
wrote:

> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:
>
>> Hi o1bigtenor,
>>
>> Now what will you say about below query that he should have come with
>> some research before asking here ?
>>
>
> Yes.  The question gets asked frequently so if one searches the archive
> usually you'll get a thread from the past 6 months or so which is usually
> recent enough.  Plus lots of people publish blog posts and articles on the
> topic, which are probably more well written and researched than
> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
> found these three tools, and am leaning toward such-and-such because of
> reasons, does anyone have any thoughts I should consider?" would should the
> effort and allow for better responses since at least some background is
> given.  Knowing generally how the poster uses PostgreSQL also helps and
> should be included.
>
> A posting like this I'll usually just ignore since I know that answers
> already exist to be found, and it is not something that I have chosen to
> become a "librarian/interactive assistant" for - I relegate to the
> documentation for that.
>
> David J.
>
>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

And basically you are improving your knowledge as you are expecting those
questions the answers of which you dont know about.

Thats why you seem to not to be helpful and somehow stopping others to not
to be helpful.

I don’t find find it wrong if any new guy asking the question that has been
repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
TIMES, “unlike you”.





On Friday, January 15, 2021, Atul Kumar  wrote:

> Again Thanks alot David for ur response.
>
> You expect everyone to be perfect which cannot happen.
>
> And related to thread, if someone has joined this thread today then it is
> obvious that he may not be able to find the previous threads as well.
>
>
>
>
> Regards
> Atul
>
> On Friday, January 15, 2021, David G. Johnston 
> wrote:
>
>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar  wrote:
>>
>>> Hi o1bigtenor,
>>>
>>> Now what will you say about below query that he should have come with
>>> some research before asking here ?
>>>
>>
>> Yes.  The question gets asked frequently so if one searches the archive
>> usually you'll get a thread from the past 6 months or so which is usually
>> recent enough.  Plus lots of people publish blog posts and articles on the
>> topic, which are probably more well written and researched than
>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>> found these three tools, and am leaning toward such-and-such because of
>> reasons, does anyone have any thoughts I should consider?" would should the
>> effort and allow for better responses since at least some background is
>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>> should be included.
>>
>> A posting like this I'll usually just ignore since I know that answers
>> already exist to be found, and it is not something that I have chosen to
>> become a "librarian/interactive assistant" for - I relegate to the
>> documentation for that.
>>
>> David J.
>>
>>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

Yes you are right. And it seems for those that ignore queries to answer.

Your mates have already said that they ignore answering repeated questions.

On Friday, January 15, 2021, Hemil Ruparel 
wrote:

> http://catb.org/~esr/faqs/smart-questions.html
>
> On Fri, Jan 15, 2021 at 9:58 PM Atul Kumar  wrote:
>
>> And basically you are improving your knowledge as you are expecting those
>> questions the answers of which you dont know about.
>>
>> Thats why you seem to not to be helpful and somehow stopping others to
>> not to be helpful.
>>
>> I don’t find find it wrong if any new guy asking the question that has
>> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
>> TIMES, “unlike you”.
>>
>>
>>
>>
>>
>> On Friday, January 15, 2021, Atul Kumar  wrote:
>>
>>> Again Thanks alot David for ur response.
>>>
>>> You expect everyone to be perfect which cannot happen.
>>>
>>> And related to thread, if someone has joined this thread today then it
>>> is obvious that he may not be able to find the previous threads as well.
>>>
>>>
>>>
>>>
>>> Regards
>>> Atul
>>>
>>> On Friday, January 15, 2021, David G. Johnston <
>>> david.g.johns...@gmail.com> wrote:
>>>
>>>> On Fri, Jan 15, 2021 at 8:56 AM Atul Kumar 
>>>> wrote:
>>>>
>>>>> Hi o1bigtenor,
>>>>>
>>>>> Now what will you say about below query that he should have come with
>>>>> some research before asking here ?
>>>>>
>>>>
>>>> Yes.  The question gets asked frequently so if one searches the archive
>>>> usually you'll get a thread from the past 6 months or so which is usually
>>>> recent enough.  Plus lots of people publish blog posts and articles on the
>>>> topic, which are probably more well written and researched than
>>>> off-the-cuff replies to a mailing list would be.  At least posting "hey, I
>>>> found these three tools, and am leaning toward such-and-such because of
>>>> reasons, does anyone have any thoughts I should consider?" would should the
>>>> effort and allow for better responses since at least some background is
>>>> given.  Knowing generally how the poster uses PostgreSQL also helps and
>>>> should be included.
>>>>
>>>> A posting like this I'll usually just ignore since I know that answers
>>>> already exist to be found, and it is not something that I have chosen to
>>>> become a "librarian/interactive assistant" for - I relegate to the
>>>> documentation for that.
>>>>
>>>> David J.
>>>>
>>>>

Re: Best tools to monitor and fine tune postgres

[Atul Kumar]

You keep on giving excuses (imperfect world and blah blah) and I will keep
on raising questions on them.

The link you shared could have been shared earlier as well.


But instead of that as usual you and your mates stretched the conversation
without going in any direction so basically you wasted author’s time.


And I have no doubt that you will waste more time of yours as well as of us
by giving some kore excuses.





On Saturday, January 16, 2021, Adrian Klaver 
wrote:

> On 1/15/21 8:28 AM, Atul Kumar wrote:
>
>> And basically you are improving your knowledge as you are expecting those
>> questions the answers of which you dont know about.
>>
>> Thats why you seem to not to be helpful and somehow stopping others to
>> not to be helpful.
>>
>> I don’t find find it wrong if any new guy asking the question that has
>> been repeated 1000 times earlier, if I know the answer I WILL RESPOND 1000
>> TIMES, “unlike you”.
>>
>>
> It was for this reason that FAQ's(https://wiki.postgresql.org/wiki/FAQ)
> where created. Also why the mailing list archives are searchable:
>
> https://www.postgresql.org/search/?m=1&ln=pgsql-general&q=monitor+tool
>
> Which leads to:
>
> https://www.postgresql.org/message-id/CAODZiv7LE+OAwpTyA8G6v
> bd0ggmrwfrp1x0jxs5wvauoren...@mail.gmail.com
>
> https://www.postgresql.org/message-id/2020052809.mog2nxi
> yuan7xjjy%40office.hexack.fr
>
> The frustration is that with literally a world of information at hand and
> searchable, folks often do not do the basic homework that would answer
> their question in less time then waiting for a response from a list. You
> see it enough times and even the calmest person gets irritated. In a
> perfect world that would not happen, we don't live in a perfect world.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

upgrade using logical replication

[Atul Kumar]

Hi,

We are planning to upgrade from postgres 9.5  to postgres 10, on
centos version 6.8, We have a database of around 400GBs.

We need to perform the upgrade activity with minimum downtime (around
1-2 hours). We are thinking of logical replication for the same.

but The issue is we already have configured streaming replication on
it (1 master and 2 slaves).

So On master, we can upgrade the master by changing parameter
wal_level to replica.

but I need to know how will I upgrade the slave servers, so I am
little confused for the approach for upgrade
.

pljava": ERROR

[Atul Kumar]

Hi,

When I was trying to upgrade from postgres 9.5  to postgres 10 using command
 su - enterprisedb/usr/edb/as10/bin/pg_upgrade -d
/data/apps/ppas/9.5/data -D  /data/edb/as10/data -U enterprisedb -b
/usr/ppas-9.5/bin/ -B /usr/edb/as10/bin/ -p 5444 -P 5445 --check

I got below error:
could not load library "pljava": ERROR:  could not access file
"pljava": No such file or directory

what is this actual issue and what can be solution of it.

vacuum is time consuming

[Atul Kumar]

Hi,

We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3%  only.

We have  configured maintenance_work_mem to 10GBs and restarted the
postgres service.

We have done upgrade from postgres 9.5 to 9.6, then when we run vacuum
analyze on database, it is taking more than 5 hours and still running.

Any suggestions for making the process(vacuum analyze) faster are welcome.

Re: vacuum is time consuming

[Atul Kumar]

Ok Martin, I got ur ur point of max limit of maintenance_work_mem is 1 GB
but there is nothing mentioned about the same in postgresql.conf as remarks
for this specific parameter.


Is there any other option to increase the speed of vacuum?


Regards
Atul




On Tuesday, February 2, 2021, Martín Marqués 
wrote:

> Hi Atul,
>
> > We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3%
> only.
> >
> > We have  configured maintenance_work_mem to 10GBs and restarted the
> > postgres service.
>
> Just wanted to mention that maintenance_work_mem has a hardcoded upper
> limit threshold of 1GB, so any size bigger than that to
> maintenance_work_mem or autovacuum_maintenance_work_mem will leave it
> effectively at 1GB.
>
> There have been a few attempts the past few years on lifting that
> restriction.
>
> > We have done upgrade from postgres 9.5 to 9.6, then when we run vacuum
> > analyze on database, it is taking more than 5 hours and still running.
> >
> > Any suggestions for making the process(vacuum analyze) faster are
> welcome.
>
> Yes, upgrade to PG13.
>
> Kind regards, Martin,
>
>
> --
> Martín Marqués
> It’s not that I have something to hide,
> it’s that I have nothing I want you to see
>

vacuumdb not letting me connect to db

[Atul Kumar]

Hi,

I have 160 GB of RAM, postgres 9.6 is running on the server.

after upgrade I ran the below command:

"/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
--analyze-only

after running that  command I was not able to connect the database
using psql for few minutes.

After 20-30 minutes i was able to connect to the db and at that time I
checked the pg_stst_activity, the active connections was reduced to
27.

my max_connections is set to 700.

I tried to find out the reason for not being abled to connect the db
(when 300 jobs were running) but still not got the answer. So
suggestions are welcome.

Re: vacuumdb not letting me connect to db

[Atul Kumar]

Hi,

The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) but
I don’t understand one thing here that if max_connections is set to 700
then why I am not able to connect the db.

As the running jobs (300) are lesser than half of max_connections.


Regards
Atul


On Thursday, February 4, 2021, Laurenz Albe 
wrote:

> On Thu, 2021-02-04 at 16:56 +0530, Atul Kumar wrote:
> > I have 160 GB of RAM, postgres 9.6 is running on the server.
> >
> > after upgrade I ran the below command:
> >
> > "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> > --analyze-only
> >
> > after running that  command I was not able to connect the database
> > using psql for few minutes.
>
> That is to be expected.
>
> If you have 300 processes performing I/O and using CPU, your machine
> will vertainly be overloaded.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: vacuumdb not letting me connect to db

[Atul Kumar]

There is no error message, when I try to connect the database while running
vacuumdb with 300 jobs, it gets stuck.

On Thursday, February 4, 2021, Ravi Krishna  wrote:

> >The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption)
> >but I don’t understand one thing here that if max_connections is set to
> 700 then
> >why I am not able to connect the db. As the running jobs (300) are lesser
> than
> >half of max_connections.
>
> Please paste the error message
>
>

Re: vacuumdb not letting me connect to db

[Atul Kumar]

There is no error message, when I try to connect the database while running
vacuumdb with 300 jobs, it gets stuck.

On Thursday, February 4, 2021, Ron  wrote:

> On 2/4/21 5:26 AM, Atul Kumar wrote:
>
> Hi,
>
> I have 160 GB of RAM, postgres 9.6 is running on the server.
>
> after upgrade I ran the below command:
>
> "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> --analyze-only
>
> after running that  command I was not able to connect the database
> using psql for few minutes.
>
>
> What's the exact error message?
>
> After 20-30 minutes i was able to connect to the db and at that time I
> checked the pg_stst_activity, the active connections was reduced to
> 27.
>
> my max_connections is set to 700.
>
> I tried to find out the reason for not being abled to connect the db
> (when 300 jobs were running) but still not got the answer. So
>
>
> Connect to the cluster *before* running "vacuumdb -j300", and start
> looking at pg_stst_activity while vacuumdb is running.
>
> --
> Angular momentum makes the world go 'round.
>

Re: vacuumdb not letting me connect to db

[Atul Kumar]

ok, How do I resolve it ?

Any suggestions ?











On 2/5/21, Ron  wrote:
> Your problem screams "IO saturation".
>
> On 2/4/21 12:07 PM, Atul Kumar wrote:
>> There is no error message, when I try to connect the database while
>> running vacuumdb with 300 jobs, it gets stuck.
>>
>> On Thursday, February 4, 2021, Ravi Krishna > <mailto:rkrishna...@aol.com>> wrote:
>>
>> >The CPU and RAM are normal even on 300 jobs ( only 1-4% of
>> consumption)
>> >but I don’t understand one thing here that if max_connections is set
>> to 700 then
>> >why I am not able to connect the db. As the running jobs (300) are
>> lesser than
>> >half of max_connections.
>>
>> Please paste the error message
>>
>
> --
> Angular momentum makes the world go 'round.
>

Re: vacuumdb not letting me connect to db

[Atul Kumar]

Hi Gavan,

Thanks for providing the details, I need more clarification on this as how
should I analyze that what should be ideal no. of connections should we set
to avoid IO overhead based on the available hardware resources.

How to do this calculation ?

Note: even during 300 threads, my RAM utilisation is totally normal.


Regards
Atul






On Saturday, February 6, 2021, Gavan Schneider 
wrote:

> On 6 Feb 2021, at 3:37, Ron wrote:
>
> On 2/5/21 10:22 AM, Rob Sargent wrote:
>>
>>>
>>>
>>> On 2/5/21 9:11 AM, Ron wrote:
>>>
 Obviously... don't use 300 threads.

 No, no Ron.  Clearly the answer is more CPUs
>>>
>>
>> I hope you're being sarcastic.
>>
>> A reasonable conjecture… though there is the consideration that 300 CPU
> intensive tasks spread across a given number of CPUs is going to waste some
> resources with context switching., i.e., need more CPUs :)
>
> Basically if there is plenty of wait time for I/O completion then CPU task
> switching can get more total work done.  So far so obvious. In this thread
> I can see where it is disappointing to have a system considered capable of
> 700 connections getting saturated by a “mere” 300 threads. But this is only
> a “problem” if connections are equated to threads. PG max connection count
> is about external users having access to resources needed to get a task
> done. Like all resource allocations this relies on estimated average usage,
> i.e., each connection only asks for a lot of CPU in brief bursts and then
> the result is transmitted with a time lag before the connection makes
> another CPU demand. The system designer should use estimations about usage
> and load to budget and configure the system, and, monitor it all against
> actual performance in the real world. Of course estimates are a standing
> request for outliers and the system will show stress under an unexpected
> load.
>
> So far I have not seen an analysis of where the bottle neck has occurred:
> CPU RAM HD and/or the data bus connecting these. Some of these hardware
> resources maxed out to the extent the system would not immediately pick up
> an additional work unit. As I see it OP started 300 CPU intensive tasks on
> hardware intended for 700 connections. If the connection count was designed
> with say 50% CPU intensive time per connection you would expect this
> hardware to be fully saturated with 300 CPU intensive tasks. More than
> that, doing the task with 300 threads would probably take longer than (say)
> 200 threads as the increased CPU context swapping time is just wasted
> effort.
>
> OP now has a choice: decrease threads or (seriously) upgrade the hardware.
> We in the gallery would love to see a plot of total time to completion as a
> function of threads invoked (50-300 increments of 50) assuming the starting
> conditions are the same :)
>
> Gavan Schneider
> ——
> Gavan Schneider, Sodwalls, NSW, Australia
> Explanations exist; they have existed for all time; there is always a
> well-known solution to every human problem — neat, plausible, and wrong.
> — H. L. Mencken, 1920
>

delete old cluster after pg_upgrade with -k option

[Atul Kumar]

Hi,

I have upgrade the postgres cluster from 9.5 to 9.6 using pg_upgarde
utility with -k option.

Now I just wanted to be confirmed that is it safe to run
delete_old_cluster.sh file as we have used -k option that must created
hard links with old cluster.


Suggestions are welcome.

Streaming replication between different OS

[Atul Kumar]

Hi,


I have postgres 9.6 cluster running on Centos 6.8, so I just wanted to know
that can I configure streaming replication with same postgres version i.e
9.6 running on centos 7.

Suggestions are welcome as the Centos versions are different one is 6.8 and
second one is 7.

Also please let me know if there will be any challenge in case of failover.

Re: Streaming replication between different OS

[Atul Kumar]

Hi Tom,

As I am new to postgres, could you help me to in how to check collation and
what is de_DE locale ?


Regards






On Monday, February 22, 2021, Tom Lane  wrote:

> Ganesh Korde  writes:
> > On Mon, 22 Feb 2021, 11:48 am Atul Kumar,  wrote:
> >> I have postgres 9.6 cluster running on Centos 6.8, so I just wanted to
> >> know that can I configure streaming replication with same postgres
> version
> >> i.e 9.6 running on centos 7.
>
> > Should not be a problem if both OS archtecture (32 bit/ 64 bit) are same.
>
> The other thing you have to worry about is whether the collations you
> use sort the same on both systems ... if they don't, you'll have
> effectively-corrupt indexes on text columns on the standby.
>
> According to
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> you should be okay for rhel/centos 6 to 7 migration unless you
> use de_DE locale.  But I don't know how thorughly that's been
> checked.
>
> Using logical not physical replication might be safer.
>
> regards, tom lane
>

getting tables list of other schema too

[Atul Kumar]

Hi,

I have postgres 9.6 cluster running on centos 7 machine.

when I set search_path to any user made schema with below command

[enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb
edb=# \c test

set search_path to college;

and after listing the tables with command \dt, we should get list of
tables of schema college only.

but here I am getting list of tables of schema college and list of
tables of schema sys along with it.


Why is it happening, please suggest.


test=# \dt
List of relations
 Schema |  Name   | Type  |Owner
+-+---+--
 college | ta_rule_error   | table | college
 college | team_import | table | college
 college | test_24022021   | table | enterprisedb
 sys| callback_queue_table| table | enterprisedb
 sys| dual| table | enterprisedb
 sys| edb$session_wait_history| table | enterprisedb
 sys| edb$session_waits   | table | enterprisedb
 sys| edb$snap| table | enterprisedb
 sys| edb$stat_all_indexes| table | enterprisedb
 sys| edb$stat_all_tables | table | enterprisedb
 sys| edb$stat_database   | table | enterprisedb
 sys| edb$statio_all_indexes  | table | enterprisedb
 sys| edb$statio_all_tables   | table | enterprisedb
 sys| edb$system_waits| table | enterprisedb
 sys| plsql_profiler_rawdata  | table | enterprisedb
 sys| plsql_profiler_runs | table | enterprisedb
 sys| plsql_profiler_units| table | enterprisedb
 sys| product_component_version   | table | enterprisedb
 sys| scheduler_0100_component_name   | table | college
 sys| scheduler_0200_program  | table | college
 sys| scheduler_0250_program_argument | table | college
 sys| scheduler_0300_schedule | table | college
 sys| scheduler_0400_job  | table | college
 sys| scheduler_0450_job_argument | table | college

Re: getting tables list of other schema too

[Atul Kumar]

yes I know that, but my doubt is why \dt is showing tables of other
schemas even I  am setting the search_path.


Regards,
Atul
















On 2/24/21, Thomas Boussekey  wrote:
> Hello Atul,
>
> You can use set a filter to limit the tables returned, i.e:
>
> \dt college.*
>
> HTH,
> Thomas
>
> Le mer. 24 févr. 2021 à 08:54, Atul Kumar  a écrit :
>
>> Hi,
>>
>> I have postgres 9.6 cluster running on centos 7 machine.
>>
>> when I set search_path to any user made schema with below command
>>
>> [enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb
>> edb=# \c test
>>
>> set search_path to college;
>>
>> and after listing the tables with command \dt, we should get list of
>> tables of schema college only.
>>
>> but here I am getting list of tables of schema college and list of
>> tables of schema sys along with it.
>>
>>
>> Why is it happening, please suggest.
>>
>>
>> test=# \dt
>> List of relations
>>  Schema |  Name   | Type  |Owner
>> +-+---+--
>>  college | ta_rule_error   | table | college
>>  college | team_import | table | college
>>  college | test_24022021   | table | enterprisedb
>>  sys| callback_queue_table| table | enterprisedb
>>  sys| dual| table | enterprisedb
>>  sys| edb$session_wait_history| table | enterprisedb
>>  sys| edb$session_waits   | table | enterprisedb
>>  sys| edb$snap| table | enterprisedb
>>  sys| edb$stat_all_indexes| table | enterprisedb
>>  sys| edb$stat_all_tables | table | enterprisedb
>>  sys| edb$stat_database   | table | enterprisedb
>>  sys| edb$statio_all_indexes  | table | enterprisedb
>>  sys| edb$statio_all_tables   | table | enterprisedb
>>  sys| edb$system_waits| table | enterprisedb
>>  sys| plsql_profiler_rawdata  | table | enterprisedb
>>  sys| plsql_profiler_runs | table | enterprisedb
>>  sys| plsql_profiler_units| table | enterprisedb
>>  sys| product_component_version   | table | enterprisedb
>>  sys| scheduler_0100_component_name   | table | college
>>  sys| scheduler_0200_program  | table | college
>>  sys| scheduler_0250_program_argument | table | college
>>  sys| scheduler_0300_schedule | table | college
>>  sys| scheduler_0400_job  | table | college
>>  sys| scheduler_0450_job_argument | table | college
>>
>>
>>
>

Re: getting tables list of other schema too

[Atul Kumar]

I am sorry but I am not clear from your response, as I have created
another instance with same version 9.6 but there no system schema or
its tables are visible.

Please help.






On 2/24/21, Francisco Olarte  wrote:
> On Wed, Feb 24, 2021 at 12:12 PM Francisco Olarte
>  wrote:
>> AFAIK dt list "tables", not "tables in the schemas in search path".
>> It states " By default, only user-created objects are shown; supply a
>> pattern or the S modifier to include system objects.", but these sys
>> schema does not seem to be a system one.
>
> Zap it, after (incorrectly after posting) searching for "schema" in
> the docs I found, in a paragraph far, far, away, \d* uses search path.
>
> Francisco Olarte.
>

Re: How to know how much CPU, RAM is used by existing 1 database

[Atul Kumar]

I hope the below link helps...

https://www.enterprisedb.com/blog/monitor-cpu-and-memory-percentage-used-each-process-postgresqlppas-91

On 10/26/22, Rob Sargent  wrote:
> On 10/26/22 08:26, Yi Sun wrote:
>>
>>
>> On Wed, 26 Oct 2022 at 18:10, jian he 
>> wrote:
>>
>>
>>
>> On Wed, Oct 26, 2022 at 11:07 AM Yi Sun  wrote:
>>
>> Hi Guys,
>>
>> Who can help me with this please? I researched but still no
>> result yet, thank you
>>
>> On Tue, 25 Oct 2022 at 16:30, Yi Sun  wrote:
>>
>> Hi,
>>
>> There are many databases in our production patroni cluster
>> and it seems it is overloaded, so we decide to migrate the
>> busiest database to a new patroni cluster.
>>
>> pgwatch2 is implemented, how to know how much CPU, RAM is
>> used by the database please? Then we can use it to prepare
>> the new patroni cluster hardware. Thank you
>>
>> Best regards
>> Dennis
>>
>>
>> manual:
>> https://www.postgresql.org/docs/current/runtime-config-resource.html|
>> |
>> |except |min_dynamic_shared_memory| (|integer|)|
>> |all other parameters are used to cap the memory. almost all
>> parameters mentioned "database server" which means it's on cluster
>> level.
>> |
>> |
>> |
>> |
>> |
>> --
>>  I recommend David Deutsch's <>
>>
>>   Jian
>>
>>
>>
>>  Hi Jian he
>>
>> Thank you for your reply
>>
>> The parameters are on cluster level, so we still can not know how much
>> memory is used in a specific database, for example, total memory is 64GB
>> 1. How to get how much memory is used on cluster level? For example 40GB
>> 2. How to get how much memory is used in a specific database? For
>> example 30GB, then we can prepare the new patroni cluster 32GB is enough
>>
>> Thank you
>> Dennis
> You can see connection with pg*backend* functions.  You can log
> connections to see which db is most commonly accessed. You can log sql
> to see which table are being touched.  You'll have to assume a
> correlation to CPU/disc usage.  What have you tried?

postgres large database backup

[Atul Kumar]

Hi,

I have a 10TB database running on postgres 11 version running on centos 7
"on premises", I need to schedule the backup of this database in a faster
way.

The scheduled backup will be used for PITR purposes.

So please let me know how I should do it in a quicker backup for my 10TB
database ? Is there any tool to take backups and subsequently incremental
backups in a faster way and restore it for PITR in a faster way when
required.

What should be the exact approach for scheduling such backups so that it
can be restored in a faster way ?



Regards.

confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

[Atul Kumar]

Hi,

Could someone help me in telling the difference between these
three parameters
1. max_standby_archive_delay
2. max_standby_streaming_delay
3. recovery_min_apply_delay

My basic motive is to make the standby database server to be delayed to
apply the changes on itself,  if any data has been accidentally
deleted/updated/ truncated from the primary server.

Which parameter do I need to configure to serve this purpose ? And
When will the remaining two parameters be used ?

It would be great if anyone can explain them with a brief example.



Regards.

Backup schema without data

[Atul Kumar]

Hi,

Please help me in telling that how I can take the backup of one single
schema without its data using pg_dump utility ?


So far, I could not find anything suitable for doing so.

Regards,
Atul

Replicate data from one standby server to another standby

[Atul Kumar]

Hi,

I have Configured "sync" streaming replication  to replicate the data from
one primary node to one slave node.

Now I want one to add one more node as slave that will replicate the data
from previously created slave replica (not from primary replica as we do
traditionally).

So please let me know whether do we have any such flexibility where we can
replicate the data from one slave replica to another slave replica ?



Regards.

Query to find RDS endpoint

[Atul Kumar]

Hi,

Could someone help me in sharing a postgresql query to fetch the RDS
endpoint ?

Postgres version 14.6.

I tried to search but couldn't find it.



Regards.

strange behavior of .pgpass file

[Atul Kumar]

Hi,

I found some strange behaviour of postgres superuser password in my
existing cluster, below is the basic outline of the setup.
1. version - postgres 12
2. replication - streaming replication async
3. OS- centos7
4. One Master, One Standby

I have identical pgpass files on both server postgres home directory.

So when I execute below command on slave node:
psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
pg_is_in_recovery"

I get error of password authentication:

psql: error: FATAL:  password authentication failed for user "postgres"

password retrieved from file "/homedirectorypath/.pgpass"


But when I run the same command on master node:

psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
pg_is_in_recovery"


I don't get any errors and get the expected output as "t".


*Note: the passwords in and path of both pgpass files are identical.*


then why am I getting errors on the slave node for the same command even
after having everything the same ?


Also, I tried the -W to enforce the password of postgres user but got the
same issue on slave and no issue on master although the password is the
same.


What am I missing here ? Please suggest.




Regards,
Atul

Re: strange behavior of .pgpass file

[Atul Kumar]

Please suggest.

On Wed, 21 Jun 2023, 02:02 Atul Kumar,  wrote:

> Th both pgpass files contains details as below:
>
> *:5432:*:postgres:
>
> I couldn't find anything wrong there in pgpass.
>
> and if the issue would have been with .pgpass file only then I would not
> have got the same error with -W option.
>
> But I am getting the same issue when I use -W option as well on standby
> side only.
>
> While on master node I don't get any error while using the -W option along
> with standby host name.
>
>
> What could be the reason for this? As same passwords are replicating to
> standby.
>
>
>
> Regards
> Atul
>
>
>
> On Wed, 21 Jun 2023, 01:38 Adrian Klaver, 
> wrote:
>
>> On 6/20/23 11:59, Atul Kumar wrote:
>> > Hi,
>> >
>> > I found some strange behaviour of postgres superuser password in my
>> > existing cluster, below is the basic outline of the setup.
>> > 1. version - postgres 12
>> > 2. replication - streaming replication async
>> > 3. OS- centos7
>> > 4. One Master, One Standby
>> >
>> > I have identical pgpass files on both server postgres home directory.
>> >
>> > So when I execute below command on slave node:
>> > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
>> > pg_is_in_recovery"
>> >
>> > I get error of password authentication:
>> >
>> > psql: error: FATAL:  password authentication failed for user "postgres"
>> >
>> > password retrieved from file "/homedirectorypath/.pgpass"
>>
>> I'm going to say this is failing because per:
>>
>> https://www.postgresql.org/docs/15/libpq-pgpass.html
>>
>> hostname:port:database:username:password
>>
>> and when you are running it the hostname is not matching what you think
>> it is and the wrong password is being returned. Whereas the example
>> below is matching correctly.
>>
>> >
>> >
>> > But when I run the same command on master node:
>> >
>> > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
>> > pg_is_in_recovery"
>> >
>> >
>> > I don't get any errors and get the expected output as "t".
>> >
>> >
>> > *_Note: the passwords in and path of both pgpass files are identical._*
>> >
>> >
>> > then why am I getting errors on the slave node for the same command
>> even
>> > after having everything the same ?
>> >
>> >
>> > Also, I tried the -W to enforce the password of postgres user but got
>> > the same issue on slave and no issue on master although the password is
>> > the same.
>> >
>> >
>> > What am I missing here ? Please suggest.
>> >
>> >
>> >
>> >
>> > Regards,
>> > Atul
>> >
>> >
>> >
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>

2 master 3 standby replication

[Atul Kumar]

Hi,

Do we have any solution to Configure an architecture of replication having
2 master nodes and 3 standby nodes replicating the data from any of the 2
master ?


Please let me know if you have any link/ dedicated document.



Regards,
Atul

Re: 2 master 3 standby replication

[Atul Kumar]

Hi,

Please help me with the query I raised.


Regards.

On Fri, 23 Jun 2023, 00:12 Atul Kumar,  wrote:

> Hi,
>
> Do we have any solution to Configure an architecture of replication having
> 2 master nodes and 3 standby nodes replicating the data from any of the 2
> master ?
>
>
> Please let me know if you have any link/ dedicated document.
>
>
>
> Regards,
> Atul
>
>
>
>
>

connect postgres using url

[Atul Kumar]

HI,

Could you please help me in telling how I can connect my postgres database
using http url ?

What steps need to be taken care of ?

Please share any referral link, if possible.



Regards,
Atul

Wrong passwords allowed

[Atul Kumar]

Hi,

I am having a strange issue that I have a postgres intance running on
version 12 in centos 7.

So whenever I am trying to login in the database using psql -W option, I am
entering the wrong passwords for that user, even after that I am logging in
the database.

So could you help me in telling why is this strange behavior happening.

It is taking any passwords for any user be it right password or wrong
password.

What could be the problem?


Regards.

password error in batch script

[Atul Kumar]

Hi,

I have a password Vl=SO*CIz%A83FQF that is working fine like that in the
command prompt but when it is being used in a batch file it is giving me an
error of wrong password, the password is something looks like that in
batch script.

set "PGPASSWORD=Vl=SO*CIz%A83FQF"

Could you please help me in getting this password read by command prompt in
the batch file.



Regards.

log_statement vs log_min_duration_statement

[Atul Kumar]

Hi,

I have a query about parameters  and log_statement

my postgres version is 12 and running on centos 7

my log_statement is set to "DDL".

and log_min_duration_statement is set to "1ms"

so technically it should log "ONLY DDLs" that take more than 1ms.

but to my surprise, postgres is logging DML as well which is taking more
than 1ms.

What am I missing here to understand, because as per my understanding
log_statement and log_min_duration_statement are correlated, postgres
should log according to log_statement parameter.

Please advise me on this behavior.



Regards,
Atul

log wal file transfer in error logs

[Atul Kumar]

Hi,

I need to log all wal files that are getting transferred to all standby
servers in the postgresql error logs.

I also need to log those wal files that are being applied in standbys.

My postgres version is 12 and running on centos 7.

I am unable to understand the proper parameters in the postgresql.conf
file, please help me in achieving this.



Regards,
Atul

Re: log wal file transfer in error logs

[Atul Kumar]

Yes, I meant streamed, I need to log those wal files that are getting
streamed and replayed on standby servers in error logs of primary as well
as standby servers.

So that I can cross check that whichever file is streamed has been replayed
on standby.

I need to keep a track of these wal files in db error logs.



Regard

On Wed, Oct 11, 2023 at 10:29 PM Atul Kumar  wrote:

> Yss, I meant streamed, I need to log those wal files that are getting
> streamed and replayed on standby servers in error logs of primary as well
> as standby servers.
>
> So that I can cross check that whichever file is streamed has been
> replayed on standby.
>
> I need to keep a track of these wal files in db error logs.
>
>
>
> Regards,
> Atul
>
>
>
> On Wed, Oct 11, 2023 at 10:21 PM Laurenz Albe 
> wrote:
>
>> On Wed, 2023-10-11 at 22:15 +0530, Atul Kumar wrote:
>> > I need to log all wal files that are getting transferred to all standby
>> servers in the postgresql error logs.
>>
>> WAL files are not transferred to standby servers...
>>
>> WAL is streamed, so what would you want to log?
>>
>> Yours,
>> Laurenz Albe
>>
>

logs are not getting logged in error logs

[Atul Kumar]

Hi,

I have postgres 12 running on centos 7.

i have configured streaming replication having one primary and one standby.

It is strange to see that the logs are not getting generated on standby.

I have checked the log directory using show command and it shows the
correct output.

I also intentionally created a few errors on standby to log and they are
also getting logged.

But apart from these error logs I can't see any additional logs on standby.

Please help me what exactly is happening and how can I troubleshoot more ?



Regards.

purpose of an entry in pg_hba.conf file

[Atul Kumar]

Hi,

As you know already,  pg_hba.conf file always has the below entry after
successful installation of postgres.

hostall all 127.0.0.1/32trust


Please let me know what is the exact purpose of this entry and what would
be the impact of removing it on other tools/processes like pgbouncer, pem,
replication etc  ?



Regards,

Atul

issue with remote backup

[Atul Kumar]

Hi,

I have the postgres12 version running on centos7.

In pg_hba.conf file I have an entry like below:

hostall all /32md5

So from the above entry, I understand that all users can connect to all
databases from the remote ip.


But when I use pg_basebackup from remote server using below command I get
the below error:

pg_basebackup -h DB_server_hostname -p 5444 -U postgres -D
/var/lib/edb/backups/10262023_1 -Fp -Xs -c fast -P

Error that I got:

pg_basebackup: error: FATAL:  no pg_hba.conf entry for replication
connection from host "remote_ip", user "postgres", SSL off

So please help me understand why I am getting this error even after having
a related entry in the pg_hba.conf file.




 Regards.

Re: purpose of an entry in pg_hba.conf file

[Atul Kumar]

as per response

"It allows anyone/anything on the local machine to connect to the database
without authentication.  Whether that impacts any particular one/thing
depends on your personal setup."

There is already one line to serve your stated purpose
local   all  alltrust


That's why I specifically raised this question for below from postgresql
experts
hostall all 127.0.0.1/32trust

So still I am not able to find a valid reason for keeping this entry. So
please help me in explaining the same.


Regards,

On Thu, Oct 26, 2023 at 11:56 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> Always reply to the list, it is ok to CC individuals.  Also, the
> convention here is to inline post (or bottom if you must) as in my first
> reply; not top-post as you and I have done here.
>
> I'd suggest also putting into your own words what you believe the entry is
> providing/enabling.  Read the relevant documentation for aid in formulating
> such a description.
>
> It allows anyone/anything on the local machine to connect to the database
> without authentication.  Whether that impacts any particular one/thing
> depends on your personal setup.
>
> David J.
>
> On Thu, Oct 26, 2023 at 11:04 AM Atul Kumar  wrote:
>
>> Hi,
>>
>> Could you elaborate more as it seems that your response doesn't satisfy
>> my query which is "what is the exact purpose of this entry and what
>> would be the impact of removing it on other tools/processes like pgbouncer,
>> pem, replication etc  ?"
>>
>> I am yet to understand the impact of removing this entry.
>>
>>
>> Regards.
>>
>> On Thu, Oct 26, 2023 at 5:52 AM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Wed, Oct 25, 2023 at 5:11 PM Atul Kumar 
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> As you know already,  pg_hba.conf file always has the below entry after
>>>> successful installation of postgres.
>>>>
>>>> hostall all 127.0.0.1/32trust
>>>>
>>>>
>>>> Please let me know what is the exact purpose of this entry and what
>>>> would be the impact of removing it on other tools/processes like pgbouncer,
>>>> pem, replication etc  ?
>>>>
>>>>
>>>>
>>> While that may be a true statement for installation from source I'm
>>> pretty certain most packagers have a more tightly controlled setup that
>>> doesn't involve "trust" authentication.
>>>
>>> The reason behind choosing to include that specific line is to minimize
>>> the amount of post-install effort needed for one to connect to the server
>>> from the local machine, which is often a personal machine with only the
>>> "DBA" having access to it.
>>>
>>> The better and more widely implemented default is requiring a password
>>> for host while accepting peer for local.
>>>
>>> All external tools should be told what credentials to use to connect to
>>> the server and those credentials added to the system and a more restrictive
>>> pg_hba.conf entry added to permit those connections.  All trust connections
>>> in pg_hba.conf should be removed from it as quickly as possible.
>>>
>>> David J.
>>>
>>>
>>>

Re: purpose of an entry in pg_hba.conf file

[Atul Kumar]

Please share the required link having such information in detail, It would
be more helpful to me.



Regards.

On Fri, Oct 27, 2023 at 12:16 AM Christophe Pettus  wrote:

>
>
> > On Oct 26, 2023, at 11:44, Atul Kumar  wrote:
> > There is already one line to serve your stated purpose
> > local   all  alltrust
> >
> >
> > That's why I specifically raised this question for below from postgresql
> experts
> > hostall all 127.0.0.1/32trust
>
> The first line applies to local sockets; the second to connections over
> the local loopback network port.

meaning of CIDR mask in pg_hba.conf

[Atul Kumar]

Hi,

I have postgres version 12 running on centos 7.

I found an entry in my pg_hba.conf entry as given below under IPV4
connections:

host   all   all  /0  md5

I could not understand the meaning of "/0" here.

as I know that each IPV4 there are total 4 octets and each octet will be
read based on given CIDR mask (/8, /16, /24 or /32) but  I am watching
first time "/0" that I couldn't understand, So please help me in explaining
its prompt meaning and how IP will be read with /0?



Regards,
Atul

missing client_hostname

[Atul Kumar]

Hi,

I have postgres 12 running in centos7.

I recently have configured streaming replication from master to standby
using below command:

/usr/bin/pg_basebackup -h  -p 5432 -U replication -D
/path/of/data/directory/ -Fp -R -Xs -P -c fast

It was successfully configured but when I query pg_stat_replication I don't
get the hostname in output:

postgres=# select * from pg_stat_Replication;
-[ RECORD 1 ]+--
pid  | 3692075
usesysid | 16384
usename  | replication
application_name | walreceiver
client_addr  | slave_ip
*client_hostname*  |
client_port  | 52500
backend_start| 2023-11-01 08:26:45.373297-07
backend_xmin |
state| streaming
sent_lsn | 2E5/41C0
write_lsn| 2E5/41C0
flush_lsn| 2E5/41C0
replay_lsn   | 2E5/41C0
write_lag|
flush_lag|
replay_lag   |
sync_priority| 0
sync_state   | async
reply_time   | 2023-11-01 08:41:47.60122-07


So please help me understand the reason for this and how I will fix this
issue of having a respective hostname in this catalog.



Regards,
Atul

Re: missing client_hostname

[Atul Kumar]

I have already enabled log_hostname, still  *client_hostname is not showing
up.*

Do you think that just because I use ip instead of hostname while using
pg_basebackup could be the reason for it ?




Regards,
Atul

On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder 
wrote:

> ## Atul Kumar (akumar14...@gmail.com):
>
> > It was successfully configured but when I query pg_stat_replication I
> don't
> > get the hostname in output:
>
> I Recommend The Fine Manual:
>
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW
> "... and only when log_hostname is enabled".
>
> Regards,
> Christoph
>
> --
> Spare Space
>

Re: missing client_hostname

[Atul Kumar]

Yes, I have already tried that as log_hostname was already enabled by me.

By the way, just to inform you that the log_hostname is used for logging
the hostname instead of ip addresses in error log files only, Please
correct me if I am wrong.

Could you help me in telling my query that Iasked in my trial mail:

"Do you think that just because I use ip_address instead of hostname while
using pg_basebackup on standby side could be the reason for not showing
client_hostname in pg_stat_replication ?" I used below command in standby
node:

/usr/bin/pg_basebackup -h  -p 5432 -U replication -D
/path/of/data/directory/ -Fp -R -Xs -P -c fast


Regards.




On Wed, Nov 1, 2023 at 11:46 PM Mateusz Henicz 
wrote:

> Did you reconnect your replica after enabling log_hostname? If not, then
> do it and check again.
>
> śr., 1 lis 2023, 19:03 użytkownik Atul Kumar 
> napisał:
>
>> I have already enabled log_hostname, still  *client_hostname is not
>> showing up.*
>>
>> Do you think that just because I use ip instead of hostname while using
>> pg_basebackup could be the reason for it ?
>>
>>
>>
>>
>> Regards,
>> Atul
>>
>> On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder <
>> c...@burggraben.net> wrote:
>>
>>> ## Atul Kumar (akumar14...@gmail.com):
>>>
>>> > It was successfully configured but when I query pg_stat_replication I
>>> don't
>>> > get the hostname in output:
>>>
>>> I Recommend The Fine Manual:
>>>
>>> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW
>>> "... and only when log_hostname is enabled".
>>>
>>> Regards,
>>> Christoph
>>>
>>> --
>>> Spare Space
>>>
>>

strange behavior of pg_hba.conf file

[Atul Kumar]

Hi,

I have postgres 12 running in centos 7, recently I changed the
authentication of entries of pg_hba.conf to scram-sh-256 for localhost.

Since then I have started getting the below error:

no pg_hba.conf entry for host "::1", user "postgres", database "postgres




The entry of pg_hba.conf is like below:

# TYPE  DATABASEUSERADDRESS METHOD



# "local" is for Unix domain socket connections only

local   all   all
scram-sha-256

# IPv4 local connections:

hostall   postgres 127.0.0.1/32   scram-sha-256



What I am missing here, please suggest.




Regards,

Atul

Re: strange behavior of pg_hba.conf file

[Atul Kumar]

The entries that I changed were to replace the md5 with scram-sha-256 and
remove unnecessary remote IPs.

But it has nothing to do with connecting the server locally with "psql -d
postgres -U postgres -h localhost"

But when I try to connect it locally I get this error. So it is related to
local connections only and when I pass the hostname or ip of the server it
works fine without any issue.


Regards.


On Wed, Nov 22, 2023 at 10:31 PM Atul Kumar  wrote:

> The entries that I changed were to replace the md5 with scram-sha-256 and
> remove unnecessary remote IPs.
>
> But it has nothing to do with connecting the server locally with "psql -d
> postgres -U postgres -h localhost"
>
> But when I try to connect it locally I get this error. So it is related to
> local connections only and when I pass the hostname or ip of the server it
> works fine without any issue.
>
>
> Regards.
>
>
> On Wed, Nov 22, 2023 at 9:55 PM Ron Johnson 
> wrote:
>
>> On Wed, Nov 22, 2023 at 11:22 AM Atul Kumar 
>> wrote:
>>
>>> Hi,
>>>
>>> I have postgres 12 running in centos 7, recently I changed the
>>> authentication of entries of pg_hba.conf to scram-sh-256 for localhost.
>>>
>>>
>> I think you changed something else, at the same time.
>>
>>
>>> Since then I have started getting the below error:
>>>
>>> no pg_hba.conf entry for host "::1", user "postgres", database "postgres
>>>
>>>
>>>
>>>
>>> The entry of pg_hba.conf is like below:
>>>
>>> # TYPE  DATABASEUSERADDRESS METHOD
>>>
>>>
>>>
>>> # "local" is for Unix domain socket connections only
>>>
>>> local   all   all
>>> scram-sha-256
>>>
>>> # IPv4 local connections:
>>>
>>> hostall   postgres 127.0.0.1/32
>>> scram-sha-256
>>>
>>>
>>>
>>> What I am missing here, please suggest.
>>>
>>
>> A definition for host "::1", user "postgres", database "postgres".  It's
>> right there in the error message.
>>
>

Re: strange behavior of pg_hba.conf file

[Atul Kumar]

I am giving this command
psql -d postgres -U postgres -p 5432 -h localhost
Then only I get that error.

but when I  pass ip or hostname of the local server then I don't get such
error message
1. psql -d postgres -U postgres -p 5432 -h 
2. psql -d postgres -U postgres -p 5432 -h 


I don;t get that error while using the above two commands.


Regards.


On Wed, Nov 22, 2023 at 10:45 PM Adrian Klaver 
wrote:

> On 11/22/23 09:03, Atul Kumar wrote:
> > The entries that I changed were to replace the md5 with scram-sha-256
> > and remove unnecessary remote IPs.
>
> FYI from:
>
> https://www.postgresql.org/docs/current/auth-password.html
>
> md5
>
>  The method md5 uses a custom less secure challenge-response
> mechanism. It prevents password sniffing and avoids storing passwords on
> the server in plain text but provides no protection if an attacker
> manages to steal the password hash from the server. Also, the MD5 hash
> algorithm is nowadays no longer considered secure against determined
> attacks.
>
>  The md5 method cannot be used with the db_user_namespace feature.
>
>  To ease transition from the md5 method to the newer SCRAM method,
> if md5 is specified as a method in pg_hba.conf but the user's password
> on the server is encrypted for SCRAM (see below), then SCRAM-based
> authentication will automatically be chosen instead.
>
> >
> > But it has nothing to do with connecting the server locally with "psql
> > -d postgres -U postgres -h localhost"
>
> The error:
>
> no pg_hba.conf entry for host "::1", user "postgres", database "postgres
>
>
> says it does and the error is correct as you do not have an IPv6 entry
> for localhost in pg_hba.conf. At least in the snippet you showed us.
>
>
> >
> > But when I try to connect it locally I get this error. So it is related
>
> When you say connect locally do you mean to localhost or to local(socket)?
>
> > to local connections only and when I pass the hostname or ip of the
> > server it works fine without any issue.
> >
> >
> > Regards.
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: strange behavior of pg_hba.conf file

[Atul Kumar]

Please can you share any command  for due diligence whether ip is resolved
to ipv6 ?.

On Wed, Nov 22, 2023 at 11:25 PM Andreas Kretschmer 
wrote:

>
>
> Am 22.11.23 um 18:44 schrieb Atul Kumar:
> > I am giving this command
> > psql -d postgres -U postgres -p 5432 -h localhost
> > Then only I get that error.
>
> so localhost resolved to an IPv6 - address ...
>
> >
> > but when I  pass ip or hostname of the local server then I don't get
> > such error message
> > 1. psql -d postgres -U postgres -p 5432 -h 
> > 2. psql -d postgres -U postgres -p 5432 -h 
>
> resolves to an IPv4 - address. you can see the difference?
>
> localhost != iv4-address != hostname with ipv4 address
>
> Andreas
>
> >
> >
> > I don;t get that error while using the above two commands.
> >
> >
> > Regards.
> >
> >
> > On Wed, Nov 22, 2023 at 10:45 PM Adrian Klaver
> >  wrote:
> >
> > On 11/22/23 09:03, Atul Kumar wrote:
> > > The entries that I changed were to replace the md5 with
> > scram-sha-256
> > > and remove unnecessary remote IPs.
> >
> > FYI from:
> >
> > https://www.postgresql.org/docs/current/auth-password.html
> >
> > md5
> >
> >  The method md5 uses a custom less secure challenge-response
> > mechanism. It prevents password sniffing and avoids storing
> > passwords on
> > the server in plain text but provides no protection if an attacker
> > manages to steal the password hash from the server. Also, the MD5
> > hash
> > algorithm is nowadays no longer considered secure against determined
> > attacks.
> >
> >  The md5 method cannot be used with the db_user_namespace
> feature.
> >
> >  To ease transition from the md5 method to the newer SCRAM
> > method,
> > if md5 is specified as a method in pg_hba.conf but the user's
> > password
> > on the server is encrypted for SCRAM (see below), then SCRAM-based
> > authentication will automatically be chosen instead.
> >
> > >
> > > But it has nothing to do with connecting the server locally with
> > "psql
> > > -d postgres -U postgres -h localhost"
> >
> > The error:
> >
> > no pg_hba.conf entry for host "::1", user "postgres", database
> > "postgres
> >
> >
> > says it does and the error is correct as you do not have an IPv6
> > entry
> > for localhost in pg_hba.conf. At least in the snippet you showed us.
> >
> >
> > >
> > > But when I try to connect it locally I get this error. So it is
> > related
> >
> > When you say connect locally do you mean to localhost or to
> > local(socket)?
> >
> > > to local connections only and when I pass the hostname or ip of the
> > > server it works fine without any issue.
> > >
> > >
> > > Regards.
> > >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
> >
>
> --
> Andreas Kretschmer - currently still (garden leave)
> Technical Account Manager (TAM)
> www.enterprisedb.com
>
>
>
>

IPV6 issue

[Atul Kumar]

I have postgres 12 running in centos 7 on my two machines, recently I
changed the authentication of entries of pg_hba.conf to scram-sha-256 for
localhost.

Since then in my one machine, I have started getting the below error when I
use command "psql postgres"

no pg_hba.conf entry for host "::1", user "postgres", database "postgres


I found that localhost is resolve to IPV6 by using below command:

getent hosts localhost

::1 localhost localhost.localdomain localhost6
localhost6.localdomain6


Then I tested the same issue in my second machine

getent hosts localhost

::1 localhost localhost.localdomain localhost6
localhost6.localdomain6


but in my second machine I didn't face any such issue while using command
"psql postgres", I was able to login into the database without such error.


The pg_hba.conf on both machines are identical without having any IPV6
entry in it.


The entry of pg_hba.conf is like below:

# TYPE  DATABASEUSERADDRESS METHOD



# "local" is for Unix domain socket connections only

local   all   all
scram-sha-256

# IPv4 local connections:

hostall   postgres 127.0.0.1/32   scram-sha-256


I am not able to understand that my both machines are resolved to IPV6 then
why is my first machine is throwing this error ?


Please help.




Regards.

Re: IPV6 issue

[Atul Kumar]

Hi,

I found that localhost was set to .bash_profile and when I removed it and
then re-attempted to connected the database using "psql postgres", I got
this new error:

psql postgres -p 5432
psql: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?

So DO I need to restart the postgres service or is there any other
workaround?


Regards.

On Fri, Nov 24, 2023 at 1:08 AM Ron Johnson  wrote:

> On Thu, Nov 23, 2023 at 2:18 PM Atul Kumar  wrote:
>
>> I have postgres 12 running in centos 7 on my two machines, recently I
>> changed the authentication of entries of pg_hba.conf to scram-sha-256 for
>> localhost.
>>
>> Since then in my one machine, I have started getting the below error when
>> I use command "psql postgres"
>>
>> no pg_hba.conf entry for host "::1", user "postgres", database "postgres
>>
>>
>> I found that localhost is resolve to IPV6 by using below command:
>>
>> getent hosts localhost
>>
>> ::1 localhost localhost.localdomain localhost6
>> localhost6.localdomain6
>>
>>
>> Then I tested the same issue in my second machine
>>
>> getent hosts localhost
>>
>> ::1 localhost localhost.localdomain localhost6
>> localhost6.localdomain6
>>
>>
>> but in my second machine I didn't face any such issue while using command
>> "psql postgres", I was able to login into the database without such error.
>>
>>
>> The pg_hba.conf on both machines are identical without having any IPV6
>> entry in it.
>>
>>
>> The entry of pg_hba.conf is like below:
>>
>> # TYPE  DATABASEUSERADDRESS METHOD
>>
>>
>>
>> # "local" is for Unix domain socket connections only
>>
>> local   all   all
>> scram-sha-256
>>
>> # IPv4 local connections:
>>
>> hostall   postgres 127.0.0.1/32
>> scram-sha-256
>>
>>
>> I am not able to understand that my both machines are resolved to IPV6
>> then why is my first machine is throwing this error ?
>>
>
> That *is *curious.
>
> Have you exported PGHOST on either server (in, for example,
> .pgsql_profile, .bash_profile or .bashrc)?  If it is set to localhost on
> the primary server then psql will complain like you noticed.  If it is
> unset on the secondary server, then psql will use the domain socket and not
> complain.
>
>

Re: IPV6 issue

[Atul Kumar]

listen_address is set to '*',
version=  psql (12.15, server 12.15.19)


Regards.

On Tue, Nov 28, 2023 at 2:01 AM Adrian Klaver 
wrote:

> On 11/27/23 12:11, Atul Kumar wrote:
> > Hi,
> >
> > I found that localhost was set to .bash_profile and when I removed it
> > and then re-attempted to connected the database using "psql postgres", I
> > got this new error:
> >
> > psql postgres -p 5432
> > psql: error: could not connect to server: No such file or directory
> >  Is the server running locally and accepting
> >  connections on Unix domain socket
> > "/var/run/postgresql/.s.PGSQL.5432"?
> >
> > So DO I need to restart the postgres service or is there any other
> > workaround?
> >
> >
>
> In postgresql.conf or any include *.conf it points to does
>
> port = 5432
>
> and what is
>
> listen_addresses
>
> set to?
>
> How did you install Postgres?
>
> Do you have more then one version of psql installed?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: IPV6 issue

[Atul Kumar]

Hi,

unix_socket_directories is set to default i.e. /tmp and I could see the
socket in /tmp directory.


Regards.




On Tue, Nov 28, 2023 at 2:11 AM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 11/27/23 12:11, Atul Kumar wrote:
> >> I found that localhost was set to .bash_profile and when I removed it
> >> and then re-attempted to connected the database using "psql postgres",
> I
> >> got this new error:
> >>
> >> psql postgres -p 5432
> >> psql: error: could not connect to server: No such file or directory
> >> Is the server running locally and accepting
> >> connections on Unix domain socket
> >> "/var/run/postgresql/.s.PGSQL.5432"?
>
> > Do you have more then one version of psql installed?
>
> Yeah, that.  You're apparently using a version of psql/libpq that
> thinks the default Unix socket location is /var/run/postgresql;
> but the postmaster you are using did not create a socket there.
> (Probably it put one in /tmp instead, which is the out-of-the-box
> default location.  But some distros consider that insecure so they
> override it, typically to /var/run/postgresql/.)
>
> The easiest workaround if you have a mishmash of Postgres libraries
> is to tell the postmaster to create sockets in both places.
> See "unix_socket_directories" parameter.
>
> regards, tom lane
>

Re: IPV6 issue

[Atul Kumar]

I Don't know how postgres was installed,

How do I check if I have more than one version of psql installed ?


Regards.

On Tue, Nov 28, 2023 at 6:26 AM Adrian Klaver 
wrote:

> On 11/27/23 16:42, Atul Kumar wrote:
> > Hi,
> >
> > unix_socket_directories is set to default i.e. /tmp and I could see the
> > socket in /tmp directory.
>
> You have not answered:
>
> How did you install Postgres?
>
> Do you have more then one version of psql installed?
>
>
> Though I am pretty sure I know the answer to the second question.
>
>
> >
> >
> > Regards.
> >
> >
> >
> >
> > On Tue, Nov 28, 2023 at 2:11 AM Tom Lane  > <mailto:t...@sss.pgh.pa.us>> wrote:
> >
> > Adrian Klaver  > <mailto:adrian.kla...@aklaver.com>> writes:
> >  > On 11/27/23 12:11, Atul Kumar wrote:
> >  >> I found that localhost was set to .bash_profile and when I
> > removed it
> >  >> and then re-attempted to connected the database using "psql
> > postgres", I
> >  >> got this new error:
> >  >>
> >  >> psql postgres -p 5432
> >  >> psql: error: could not connect to server: No such file or
> directory
> >  >> Is the server running locally and accepting
> >  >> connections on Unix domain socket
> >  >> "/var/run/postgresql/.s.PGSQL.5432"?
> >
> >  > Do you have more then one version of psql installed?
> >
> > Yeah, that.  You're apparently using a version of psql/libpq that
> > thinks the default Unix socket location is /var/run/postgresql;
> > but the postmaster you are using did not create a socket there.
> > (Probably it put one in /tmp instead, which is the out-of-the-box
> > default location.  But some distros consider that insecure so they
> > override it, typically to /var/run/postgresql/.)
> >
> > The easiest workaround if you have a mishmash of Postgres libraries
> > is to tell the postmaster to create sockets in both places.
> > See "unix_socket_directories" parameter.
> >
> >  regards, tom lane
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

replication strange behavior

[Atul Kumar]

Hi,

I have postgres 12 running in centos 7.

I have configured streaming replication between one master and one standby
server.

In the pg_hba.conf file of the master server, I have put the standby
server's hostname instead of IP and due to which replication got broken and
I started getting below error.

FATAL:  no pg_hba.conf entry for replication connection from host
"10.20.8.22", user "replication", SSL off

2023-11-30 12:00:25 PST|pid=24096|FATAL:  XX000: could not connect to the
primary server: FATAL:  no pg_hba.conf entry for replication connection

from host "10.20.8.22", user "replication", SSL on


I put the ip of standby back in pg_hba.conf file of the master server and
replication got started, though in the standby server I mentioned the
hostname of master in primary_conninfo on the standby side.


So why is the master not reading the hostname of standby ? What am I
missing here ?



Regards,

Atul

Re: replication strange behavior

[Atul Kumar]

Hi,

In the master pg_hba.conf file, standby server hostname name is not being
read even with the domain name, but server ip is working fine.

output of /etc/hosts is given below

127.0.0.1   localhost localhost.localdomain localhost4
localhost4.localdomain4
::1 localhost localhost.localdomain localhost6
localhost6.localdomain6
 .domainname hostname


Regards.

On Fri, Dec 1, 2023 at 3:33 AM Ron Johnson  wrote:

> On Thu, Nov 30, 2023 at 3:41 PM Atul Kumar  wrote:
>
>> Hi,
>>
>> I have postgres 12 running in centos 7.
>>
>> I have configured streaming replication between one master and one
>> standby server.
>>
>> In the pg_hba.conf file of the master server, I have put the standby
>> server's hostname instead of IP and due to which replication got broken and
>> I started getting below error.
>>
>> FATAL:  no pg_hba.conf entry for replication connection from host
>> "10.20.8.22", user "replication", SSL off
>>
>> 2023-11-30 12:00:25 PST|pid=24096|FATAL:  XX000: could not connect to the
>> primary server: FATAL:  no pg_hba.conf entry for replication connection
>>
>> from host "10.20.8.22", user "replication", SSL on
>>
>>
>> I put the ip of standby back in pg_hba.conf file of the master server and
>> replication got started, though in the standby server I mentioned the
>> hostname of master in primary_conninfo on the standby side.
>>
>>
>> So why is the master not reading the hostname of standby ? What am I
>> missing here ?
>>
>
> You'd have to show us pg_hba.conf and /etc/hosts.
>
> My guess, though, is that you're not taking the domain name into account.
>
>

Parameter value in RDS

[Atul Kumar]

Hi,

I am new to RDS postgres, I have version 14 running on it with m7g.large

I found that lots of parameters has DBInstanceClassMemory written, so what
exactly is the value of this variable ?

How should I calculate it?


Regards.

unbale to list schema

[Atul Kumar]

Hi,

I am not able to find any solution to list all schemas in all databases at
once, to check the structure of the whole cluster.

As I need to give a few privileges to a user to all databases, their
schemas and schemas' objects (tables sequences etc.).

Please let me know if there is any solution/ query that will serve the
purpose.


Regards.

permission denied on socket

[Atul Kumar]

Hi,

I have postgres 13 running on centos 7.

I am facing an issue while trying to connect the cluster using the below
command.

-bash-4.2$ psql postgres

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
Permission denied

Is the server running locally and accepting connections on that
socket?


but when I am trying to connect using the below command I am able to login
to the cluster successfully.

-bash-4.2$ psql postgres -h localhost

postgres=#


I am not sure what is causing this issue and what needs to be done to
resolve it ?


Regards.

Re: permission denied on socket

[Atul Kumar]

I checked the permissions on /tmp directory and it shows "drwxrwxrwt."
already, do I need to check something else as well ?

Regards.

On Fri, Jan 26, 2024 at 3:00 AM Tom Lane  wrote:

> Ron Johnson  writes:
> > On Thu, Jan 25, 2024 at 3:32 PM Adrian Klaver  >
> > wrote:
> >> Best guess is you are using a version of psql that is expecting the
> >> socket to be somewhere else then where it actually is.
>
> > Is "permission denied" really the error you get when the socket does not
> > exist?
>
> Nope, that should mean either that /tmp is not readable, or that the
> socket file is there but has restrictive permissions.
>
> That doesn't necessarily make Adrian's answer wrong though.
>
> regards, tom lane
>

issue with reading hostname

[Atul Kumar]

Hi,

I have postgresql  version 15 running on centos7.

I have below query that reads hostname from /tmp directory:

psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();'


so below are my questions:

1. Is the psql client reading the socket file that resides in the /tmp
directory to fetch the hostname ?

2. I saw the socket file in /tmp and it is empty. Then how is the psql
client still reading the socket file successfully for hostname ?


this is my socket looks ( the size is 0 as the file is empty):

srwxrwxrwx.  1 postgres postgres  0 Apr 22 12:47 .s.PGSQL.5432


Please help me clarify these doubts.



Regards.

Re: issue with reading hostname

[Atul Kumar]

Can we edit the socket to change the hostname in it ?

Regards.

On Tue, Apr 23, 2024 at 2:41 AM Ron Johnson  wrote:

> On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar  wrote:
>
>> Hi,
>>
>> I have postgresql  version 15 running on centos7.
>>
>> I have below query that reads hostname from /tmp directory:
>>
>> psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();'
>>
>
> If you installed from the PGDG repository (possibly also the CENTOS repos,
> but I'm not sure), then the domain socket also lives in :
> /var/run/postgresql
>
> * I find that more expressive than /tmp.
> * No need to specify the host when using sockets.
> * Using a socket name makes parameterizing the hostname easier in scripts.
>
>
>

Re: issue with reading hostname

[Atul Kumar]

I mean, Once I change the hostname then how will the socket read the new
hostname ? Does it require a postgres service restart ?



On Tue, Apr 23, 2024 at 3:19 AM Adrian Klaver 
wrote:

> On 4/22/24 14:37, Atul Kumar wrote:
> > Can we edit the socket to change the hostname in it ?
>
> On Ubuntu 22.04 install, given:
>
> srwxrwxrwx  1 postgres postgres0 Apr 22 14:01 .s.PGSQL.5432=
> -rw---  1 postgres postgres   68 Apr 22 14:01 .s.PGSQL.5432.lock
>
> The contents of .s.PGSQL.5432.lock(the file that indicates a Postgres
> instance has a lock on the socket) are:
>
> 862
> /var/lib/postgresql/15/main
> 1713795311
> 5432
> /var/run/postgresql
>
> There is no hostname to be changed as you are working with a local socket.
>
> >
> > Regards.
> >
> > On Tue, Apr 23, 2024 at 2:41 AM Ron Johnson  > <mailto:ronljohnso...@gmail.com>> wrote:
> >
> > On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar  > <mailto:akumar14...@gmail.com>> wrote:
> >
> > Hi,
> >
> > I have postgresql  version 15 running on centos7.
> >
> > I have below query that reads hostname from /tmp directory:
> >
> > psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT
> > pg_is_in_recovery();'
> >
> >
> > If you installed from the PGDG repository (possibly also the CENTOS
> > repos, but I'm not sure), then the domain socket also lives in :
> > /var/run/postgresql
> >
> > * I find that more expressive than /tmp.
> > * No need to specify the host when using sockets.
> > * Using a socket name makes parameterizing the hostname easier in
> > scripts.
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

default privileges are npt working

[Atul Kumar]

Hi,

I have a postgres instance running on version 15 in centos7.

I have created a custom database and revoked all public privileges from
that database.

Then I have created a custom schema in that custom database.

Now I have created one writer *user* and one reader *user *by postgres
superuser and then granted connect privileges on the database.

Then I have given all privileges of schema level and table level to the
writer *user *so that it can create tables and insert data in the tables in
that schema.

And for reader *user * I have granted usage only privileges on schema level
and select privileges on table level so that it can only read the data of
tables.

Then I granted default "select" privileges to reader *user *to read data of
all tables created by writer *user* using below command:

alter default privileges in schema  grant select on tables
to .

but when I am connected to the reader user I am not able to read the data
inserted by the writer *user* and getting permission denied error.

I can only see the list of tables created by the writer user, not the data.

Am I missing something here? Please let me know.

*My Goal: To read the data by reader user inserted by writer user.*


Regards.

Re: ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist

[Atul Kumar]

As per your error, it seems replication slot has been dropped.

On Saturday, March 27, 2021, FOUTE K. Jaurès  wrote:

> Hello EveryOne,
>
> How can I solve Issue. ???
>
>
>
> *ERROR:  could not start WAL streaming: ERROR:  replication slot "XXX"
> does not exist2021-03-27 11:48:33.012 WAT [1090] LOG:  background worker
> "logical replication worker" (PID 8458) exited with exit code 12021-03-27
> 11:48:38.019 WAT [8461] LOG:  logical replication apply worker for
> subscription "XXX has started*
>
> All working fine a few days ago but this morning I have this issue.
>
> --
> Jaurès FOUTE
>

trigger impacting insertion of records

[Atul Kumar]

Hi,

I have simple table having structure like given below:

\d bp_ach_trans
  Table "bonzipay.bp_ach_trans"
   Column   |  Type  |
Modifiers
++---
bptransid  | integer| not null default
nextval('bp_ach_trans_bptransid_seq1'::regclass)

filename   | character varying(50)  |
 payment_status | character varying(30)  |
 settledate | character varying(15)  |
 payment_pastransid | bigint |
 tname  | character varying(250) |
 code   | character varying(5)   |
 error_txt  | character varying(200) |
 routingnumber  | character varying(15)  |
 tracenumber| character varying(10)  |
 accountnumber  | character varying(15)  |
 bankaccountnumber  | character varying(17)  |
 type   | character varying(1)   |
 amount | numeric|
 site   | character varying(30)  |
 accountype | character varying(2)   |
 tranid | character varying(15)  |

Triggers:
ins_ussf_rec AFTER INSERT ON bp_ach_trans FOR EACH ROW EXECUTE
PROCEDURE ussf_accountnumber_update()



the function definition is like below:

CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$ BEGIN update bonzipay.bp_ach_trans set
accountnumber=replace(accountnumber,'_',' ') where
left(accountnumber,3) = 'US_'; RETURN NEW; END; $function$


my query is:

when I am inserting around 1000 records in the table having
accountnumber not having value 'US_', I am getting only 300 records
insertion. remaining around 700 values are not getting inserted.

why this strange behavior is happening, as I am not inserting any
record having value 'US_' even after that all records are not
inserting.

Any suggestions are welcome.



Regards,
Atul

idle_in_transaction_session_timeout

[Atul Kumar]

Hi,

I have postgres 9.5 version running on my machine.

When I am trying to find out the parameter
idle_in_transaction_session_timeout it is showing me below error:

postgres=# show idle_in_transaction_session_timeout;
ERROR:  unrecognized configuration parameter
"idle_in_transaction_session_timeout"


I also checked postgresql.conf but even in this file there is no such parameter.

Please help me to find this parameter.




Regards,
Atul