Advice on Best BCP Plan

[Naresh g]

Dear Team,

We have a production database with two hot standby's at two different
locations.

Let's say A (production) has two standby's (B&C).

Now we are planning to do a BCP activity.

What would be the best plan to do this activity?

As I have two standby's configured, is shutting down one standby site
throughout the activity creates any problem? or any other best way ?

1. A ( Standby) and B(production) and C being in shutdown mode.
2. A (Standby) and B(production) and C(standby for B).

or like any other plan?

Please let me know.

Thank you.
Naresh.

Re: work_mem and shared_buffers question

[Naresh g]

1. Yes,  by default it is advisable to set 25% of RAM to shared buffers
because community version postgres highly depends on OS cache,  which means
when you fetch something from disk, it is first copied to OS cache (75%)
then to shared buffers(25%).
Two copies of data will be there in your system RAM.
The copy is called buffered IO.

Amazon Aurora eliminates this buffered IO,  hence it is not required you
stick to restrict 25% of RAM.

2. Work_mem is just setting,  if you sort something out your session uses
than memory other wise it just lies at OS.

On Mon, 10 Feb, 2020, 1:34 PM Ayub M,  wrote:

>
>1. shared_buffers - In a regular PostgreSQL installation, say I am
>allocating 25% of my memory to shared_buffers that means it leaves 75% for
>rest such as OS, page cache and work_mems etc. Is my understanding correct?
>If so, AWS Aurora for Postgres uses 75% of memory for shared_buffers, then
>it would leave just 25% for other things?
>2. Does the memory specified for work_mem, fully gets allocated to all
>sessions irrespective of whether they do any sorting or hashing operations?
>
>
> --
> Regards,
> Ayub
>

Re: work_mem and shared_buffers question

[Naresh g]

No,  as I said,  as per my observation your connection will not use your
work mem unless there is a sort happening,

but


Yes,  every connection uses certain amount of RAM,  some documents say it 5
MB and some say it is 1.9 MB.

Expert review may be needed on my observation.

On Mon, 10 Feb, 2020, 4:00 PM Ayub M,  wrote:

> Thanks, for q2 - if work_mem is a limit before spilling onto disk, is
> there a min amount of memory which gets allocated to each session when it
> starts?
>
> On Mon, Feb 10, 2020 at 3:51 AM Naresh g  wrote:
>
>> 1. Yes,  by default it is advisable to set 25% of RAM to shared buffers
>> because community version postgres highly depends on OS cache,  which means
>> when you fetch something from disk, it is first copied to OS cache (75%)
>> then to shared buffers(25%).
>> Two copies of data will be there in your system RAM.
>> The copy is called buffered IO.
>>
>> Amazon Aurora eliminates this buffered IO,  hence it is not required you
>> stick to restrict 25% of RAM.
>>
>> 2. Work_mem is just setting,  if you sort something out your session uses
>> than memory other wise it just lies at OS.
>>
>> On Mon, 10 Feb, 2020, 1:34 PM Ayub M,  wrote:
>>
>>>
>>>1. shared_buffers - In a regular PostgreSQL installation, say I am
>>>allocating 25% of my memory to shared_buffers that means it leaves 75% 
>>> for
>>>rest such as OS, page cache and work_mems etc. Is my understanding 
>>> correct?
>>>If so, AWS Aurora for Postgres uses 75% of memory for shared_buffers, 
>>> then
>>>it would leave just 25% for other things?
>>>2. Does the memory specified for work_mem, fully gets allocated to
>>>all sessions irrespective of whether they do any sorting or hashing
>>>operations?
>>>
>>>
>>> --
>>> Regards,
>>> Ayub
>>>
>>
>
> --
> Regards,
> Ayub
>

Re: Shared memory error

[Naresh g]

Hi,

If the dynamic shared memory segment type is POSIX then *dynamic shared
memory control segment in your case it is **PostgreSQL.1511679208* is
stored in */dev/shm*

 The file you mentioned will be created at the time of PostgreSQL startup
and cleared automatically on successful shutdown.

Complete case study depicted your error is found here


https://postgreshelp.com/postgresql-dynamic-shared-memory-posix-vs-mmap/

Regards

On Thu, Jun 4, 2020 at 12:39 PM Sonam Sharma  wrote:

> Can someone please help on this
>
> On Wed, Jun 3, 2020, 11:27 PM Sonam Sharma  wrote:
>
>> I have set the dynamic_shared_memory_type to none which was set as POSIX.
>> For now we are not seeing the error... Can someone please explain what is
>> the impact of setting it as none .
>>
>> On Wed, Jun 3, 2020, 8:28 PM Sonam Sharma  wrote:
>>
>>> Hi Team,
>>>
>>> Getting below error when any query is running from jdbc side :
>>>
>>> Error : could not open memory segment "/PostgreSQL.1511679208" : No such
>>> file or directory
>>>
>>> Yesterday I have extended the /dev/shm filesystem and the query ran. But
>>> today again the issue is repeating.
>>>
>>> Can someone please help
>>>
>>