TutorialDBA - Support | Training | Consultant

if you want to perform synchronous_commit parameter before that you have to set "synchronous_standby_names" parameter synchronous_standby_names: synchronous_standby_names means "wait for acknowledgement until my standby replies". should be specify application_name on standby recovery.conf file in primary_conninfo lines ,default application name is walreceiver If any of the current synchronous standbys disconnects for whatever reason, it will be replaced immediately with the next-highest-priority standby. Specifying more than one standby name can allow very high availability. If synchronous_standby_names specified as empty synchronous_commit will behave as local even though you set synchronous_commit to on, remote_write or remote_apply anything. setting synchronous_standby_names = '*' #defalt standby name setting synchronous_standby_names = '2 (*)' #will make synchronous commit wait for reply from any 2 standby servers. The synchronous commit...

A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as all tuples modified by this transaction are not available for vacuum until the transaction is either committed or aborted. In PostgreSQL 9.6 there will be a way to minimize this risk. As always with PostgreSQL: If someone wants to add something to PostgreSQL core it starts with a mail tread. The result was a new parameter called idle_in_transaction_session_timeout. Lets see how this works. The default value of idle_in_transaction_session_timeout is 0 , which means disabled: (postgres@[local]:5432) [postgres] > show idle_in_transaction_session_timeout; idle_in_transaction_session_timeout ------------------------------------- 0 (1 row) I’ll set it to one minute… (postgres@[...

These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a  temporary  solution is to use one of these configuration parameters to force the optimizer to choose a different plan. Better ways to improve the quality of the plans chosen by the optimizer include adjusting the planer cost constants ,running  ANALYZE  manually, increasing the value of the  default_statistics_target  configuration parameter, and increasing the amount of statistics collected for specific columns using  ALTER TABLE SET STATISTICS . enable_bitmapscan  ( boolean ) Enables or disables the query planner's use of bitmap-scan plan types. The default is  on . enable_hashagg  ( boolean ) Enables or disables the query planner's use of hashed aggregation plan types. The default is  on . enable_hashjoin  ( boo...

The pg_prewarm module provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache. pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8 Function Of Prewarm: The first argument is the relation to be prewarmed. The second argument is the prewarming method to be used, as further discussed below; the third is the relation fork to be prewarmed, usually main. The fourth argument is the first block number to prewarm (NULL is accepted as a synonym for zero). The fifth argument is the last block number to prewarm (NULL means prewarm through the last block in the relation). The return value is the number of blocks prewarmed. There are three available prewarming methods. prefetch issues asynchronous prefetch requests to the operating system, if this is supported, or throws an error otherwise. r...

Postgres as a cross platform database, relies heavily on the operating system for its caching. The shared_buffers is actually duplicating what the OS does.A typical picture of how the data flows through postgres is given below. This is confusing at first, since caching is managed by both the OS and postgres as well, but there are reasons to this. Talking about operating system cache requires another post of its own, but there are many resources on the net which can leveraged. Keep in mind that the OS caches data for the same reason we saw above, i.e why do we need a cache ? We can classify the I/O as two types, i.e reads and writes. To put it even more simpler, data flows from disk to memory for reads and flows from memory to disk for writes. Data Reads: For reads, when you consider the flow diagram above, the data flows from disk to OS cache and then to shared_buffers. We have already discussed how the pages will get pinned on to the shared_buffers until they get dirty/u...

A sequential scan i.e when there is no index and postgres has to fetch all the data from disk are a problem area for a cache like this. Since a single seq scan can wipe all of the data from a cache, it is handled differently. Instead of using a normal LRU/clock sweep algorithm, it uses a series of buffers of total 256 K.B in size. The below plan shows how it is handled. test=# explain (analyze,buffers) select count(*) from emp; Aggregate (cost=48214.95..48214.96 rows=1 width=0) (actual time=3874.445..3874.445 rows=1 loops=1) Buffers: shared read=35715 -> Seq Scan on emp (cost=0.00..45714.96 rows=999996 width=0) (actual time=6.024..3526.606 rows=1000000 loops=1) Buffers: shared read=35715 Planning time: 0.114 ms Execution time: 3874.509 ms Executing the above query again. test=# explain (analyze,buffers) select count(*) from emp; Aggregate (cost=48214.95..48214.96 rows=1 width=0) (actual time=426.385..426.385 rows=1 loops=1) Buff...

Keyword: Relation -Table,Pages - blocks,Records -Tubles,Row -Item,Datafiles -Files There are two separate access control mechanisms for shared disk buffers: 1.Reference counts (a/k/a pin counts) 2.Buffer content locks. 3.Actually,there's a third level of access control: one must hold the appropriate kind of lock on a relation before one can legally access any page belonging to the relation.  Relation-level locks are not discussed here. 1.Reference count:  one must "hold a pin on" a buffer (increment its reference count) before being allowed to do anything at all with it.  An unpinned buffer is subject to being reclaimed and reused for a different page at any instant, so touching it is unsafe.  Normally a pin is acquired via ReadBuffer and released via ReleaseBuffer.  It is OK and indeed common for a single backend to pin a page more than once concurrently; the buffer manager handles this efficiently.  It is considered OK to hold a pin for long interv...

One of the easiest ways to monitor slave lag when using streaming replication is to turn hot standby on your slave and use pg_last_xact_replay_timestamp() and/or the other recovery information functions. Here’s an example query to run on the slave systems to get the number of seconds behind it is: SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS slave_lag The issue with this query is that while your slave(s) may be 100% caught up, the time interval being returned is always increasing until new write activity occurs on the master that the slave can replay. This can cause your monitoring to give false positives that your slave is falling behind if you have things set up to ensure your slaves are no more than a few minutes behind. A side affect of this monitoring query can also give you an indication that writes to your master have stopped for some reason. One of our clients has a smaller sized database that doesn’t get quite as much write traffic as our typ...