Posgresql Log: lots of parse statements

Hi All, I need help to understand this please. I was looking to do performance tuning on slow queries so have stated logging queries taking more than 15 secs. In the postgresql log I can see a query which appears only as "parse" while others appear as execute. 2018-01-21 14:01:16 GMT LOG: durat

Re: Posgresql Log: lots of parse statements

statement occurances about 400 times in a day and everytime taking longer than 15 secs. Regards Vikas On Mar 1, 2018 15:30, "David G. Johnston" wrote: > On Thu, Mar 1, 2018 at 8:23 AM, Vikas Sharma wrote: > >> Hi All, >> >> I need help to understand this pleas

Postgresql Split Brain: Which one is latest

Hi, We have postgresql 9.5 with streaming replication(Master-slave) and automatic failover. Due to network glitch we are in master-master situation for quite some time. Please, could you advise best way to confirm which node is latest in terms of updates to the postgres databases. Regards Vikas

Re: Postgresql Split Brain: Which one is latest

2018 at 15:26, Adrian Klaver wrote: > On 04/10/2018 06:50 AM, Vikas Sharma wrote: > >> Hi, >> >> We have postgresql 9.5 with streaming replication(Master-slave) and >> automatic failover. Due to network glitch we are in master-master situation >> for quite some

Re: Postgresql Split Brain: Which one is latest

which could incur data loss as well. Correct me if wrong please? Thanks and Regards Vikas On Tue, Apr 10, 2018, 17:29 Adrian Klaver wrote: > On 04/10/2018 08:04 AM, Vikas Sharma wrote: > > Hi Adrian, > > > > This can be a good example: Application server e.g. tomcat havi

Re: Postgresql Split Brain: Which one is latest

Max count is one way (vague I agree), before confirming I will ask the application owner to have a look on data in tables as well. Regards On Tue, Apr 10, 2018, 17:55 Adrian Klaver wrote: > On 04/10/2018 09:47 AM, Vikas Sharma wrote: > > Thanks Adrian and Edison, I also think s

Postgresql database encryption

Hello Guys, Could someone throw light on the postgresql instance wide or database wide encryption please? Is this possible in postgresql and been in use in production?. This is a requirement in our production implementation. Many Thanks Vikas Sharma

Re: Postgresql database encryption

Thanks a lot for the valuable information and apologies I didn't provide specify that the requirement is to encrypt data at rest and in transit. Regards Vikas On Fri, Apr 20, 2018, 21:56 Vick Khera wrote: > On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma wrote: > >> Hello

DB size growing exponentially when materialized view refreshed concurrently (postgres 9.6)

Hi All, I am looking for advice in a issue where two materialized views are being refreshed concurrently and dbsize has grown to 150gb from 4gb in two days. We use two materialized views to keep processed data for faster query results for a search function. Earlier materialized views were refresh

Re: DB size growing exponentially when materialized view refreshed concurrently (postgres 9.6)

ncurrent refresh but still it's not working as intended. On Mon, Jun 25, 2018, 20:02 Alban Hertroys wrote: > > > On 25 Jun 2018, at 19:21, Vikas Sharma wrote: > > > > I am looking for advice in a issue where two materialized views are > being refreshed concurren

Postgresql & PGPool packages minor version different on Standby server

Hi All, We have Postgresql 9.5 Cluster with streaming replication and pgpool. The version of Postgres is 9.5.5 and Pgpool-II version 3.2.15. There is now hardware issue with the Standby Machine and it won't startup so we are building new Standby machine. My question is about the minor version of

Copy over large data Postgresql 9.5

Hello Experts, I need to transfer pgsql 9.5 data of 90GB from one Cloud provider to other. We have plenty of downtime to do this. I will be copying over data directory after shutting down pgsql services on the source. The problem is how can I confirm that the data has been copied over correctly.

Which version to upgrade upto

Hi All, We are using postgres 9.5.9 in streaming replication with repmgr. The project is now considering to update postgreSQL instances to latest versions. I am looking for which version to upgrade to. I can see the current version in postgres 11 is 11.4, and 10.9 in 10. How to decide on which

Re: Which version to upgrade upto

The architects and developers have perception that the latest release always will have bugs and others might be using in production. They feel 11.2 will be better bet than 11.4. On Wed, Jul 31, 2019, 16:24 Luca Ferrari wrote: > On Wed, Jul 31, 2019 at 4:55 PM Vikas Sharma wrote: > >

pg_xlog on slaves has grown to 200GB

more than a year. Best Regards Vikas Sharma

pg_receivexlog or archive_command

Hi, I am wondering which one is the best way to archive the xlogs for Backup and Recovery - pg_receivexlog or archive_command. pg_receivexlog seems best suited because the copied/archived file is streamed as it is being written to in xlog while archive_command only copies when the WAL is fully wr

PGPool version 4.0.6-1

Hi All, I have a confusion about the Pgpool -ii version. Can you let the know the difference between pgpool-II-pg11-4.0.6-1 and pgpool-II-96-4.0.6-1 ? To me, it looks like it is the same version of pgpool-II i.e. 4.0.6 but compiled with postgresql 11 or postgresql 9.6. otherwise they both have t

Partitioning large table (140GB)

Hi There, In our production, we use postgres 9.5 with streaming replication using repmgr, there is a large table of 140GB size which receives lots of inserts, Is it possible to partition this table in this version of postgres? and if so, please, can someone let me know the best way to accomplish

pgpool-II 3.7.5 with ssl

Greetings, We use postgresql 9.6 and pgpool 3.7.5 and we are now asked to enable ssl for 'in transit'. I have setup the ssl server side on the postgresql dbs ( master and slave) and can see in pg_stat_ssl that the master slave communication and connections from the application are showing ssl = 't

Best opensource Postgresql monitoring tool

Hi there, Could you please advise on the best opensource monitoring tool for Postgresql? Thanks & Best Regards Vikas

Re: Best opensource Postgresql monitoring tool

: > pgAdmin is one of the most popular tool. You can see all necessary metrics > such as qps, tps, and interaction with table. If you've Prometheus setup > then you can scrap necessary metrics. > > On Tue, Jan 10, 2023, 7:59 PM Vikas Sharma wrote: > >> Hi there, >

Vacuum and Materialized view refresh slow

Hello There, We have postgres 9.6 with 3 slaves replicated in synchronous commit. we recently migrated to to another cloud provider. Though we have the same configuration for VMs and postgres, we are experiencing systemwide slowness with writes to database. I am more concerned about a scheduled

Out of memory: Kill process nnnn (postmaster) score nn or sacrifice child

nn or sacrifice child [4331093.890225] Killed process n (postmaster) total-vm:18905944kB, anon-rss:1747460kB, file-rss:4kB, shmem-rss:838220kB Thanks & Best Regards Vikas Sharma

Re: Out of memory: Kill process nnnn (postmaster) score nn or sacrifice child

but I can see out of memory happened and Postmaster invoked OOM. Regards Vikas Sharma On Tue, 12 Feb 2019 at 16:39, Adrian Klaver wrote: > On 2/12/19 8:20 AM, Vikas Sharma wrote: > > Hello All, > > > > I have a 4 node PostgreSQL 9.6 cluster with streaming replication. we &g

SQL query

r the rows where col2 was either 1 or 2, rest of rows were also updated for col1 to 'false'. The above was run without where clause. Could the experts throw some light on this? Regards Vikas Sharma

pgcrypto - real life examples to encrypt / decrypt

Dear Experts, Could you please share some real life examples of using pgcrypto in production? I am planning to use it in our environment and wondering what could be the best practice for its use. Thank you. Regards Vikas S

Re: pgcrypto - real life examples to encrypt / decrypt

Thanks Adrian, I will check them out. Regards Vikas S On Mon, 2 Aug 2021 at 22:22, Adrian Klaver wrote: > On 8/2/21 2:14 PM, Vikas Sharma wrote: > > Dear Experts, > > > > Could you please share some real life examples of using pgcrypto in > > production? > > &

Re: pgcrypto - real life examples to encrypt / decrypt

till researching how to encrypt a column with sensitive data as a best practice to use in OLTP production with minimal impact on performance. Regards Vikas S On Tue, 3 Aug 2021 at 11:03, Luca Ferrari wrote: > On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma wrote: > > > > Dear

Re: pgcrypto - real life examples to encrypt / decrypt

Thanks you Guys, These are very helpful pointers. I will go away and see how much depth I do need. Regards Vikas S. On Tue, 3 Aug 2021 at 14:36, Joe Conway wrote: > On 8/3/21 8:43 AM, Luca Ferrari wrote: > > On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma wrote: > >> My ques

Cluster fencing tool/software for PostgreSQL cluster with streaming replication

Hi, I am planning for an enterprise grade PostgreSQL cluster and so looking for the tool/softwares which will do the cluster management or fencing to avoid split brain. Please, could you let me know if there are some tools/software which can do that so it can be used in a production environment.

how to get value of parameter set in session for other user

Hi There, Is it possible in postgres to get the value of the parameter currently set in the session of the other user? The other user might have set the value using SET . If the other user has set the value as "alter role" then it should appear in select * from pg_roles. Thank you -Vikas

Re: how to get value of parameter set in session for other user

Hi Tom, Thanks for the clarification. Regards Vikas On Tue, 7 Dec 2021 at 14:45, Tom Lane wrote: > Vikas Sharma writes: > > Is it possible in postgres to get the value of the parameter currently > set > > in the session of the other user? > > No. That informatio

pgbouncer: Warning xbuf_connect failed: no such or file directory

Hi There, Could someone shed some light on this error please? I am trying to set up pgbouncer on a postgres Paas instance. Regards Vikas

Table and Index bloating

Hi All, Could someone please provide the query/queries to find table and Index bloating in PgSql 9.3 onwards? and are there any other maintenance tasks inPgSql except vacuum & analyse. Regards Vikas

Re: Table and Index bloating

Thank you Stefan for the queries. Thanks you Carlos for pg_repack suggestion, Can we use pg_repack with standard community edition of postgresql? and can it be used on the databases with streaming replication? Regards Vikas Sharma On 27 November 2017 at 14:58, Carlos Martinez wrote: >

PGPool encrypted connections from Application

Hi All, Does PGPool allow encrypted connections from Application? i.e. ssl encrypted? My company wants to encrypt all traffic in the environment and so enabled https connections between applications/load balance. I am not sure about the PGPool. If application sends encrypted connection to PGP

Materialized views in PGSQL 9.3 with replication on.

r and when the new data visible in standbys for the MV? I think it's the changed data replicated from Master to Standby's so changed data is streamed from master to Standbys. Regards Vikas Sharma

PgSql 9.4 onwards

Hi, We are using PgSql 9.4 & 9.5 with repmgr 3.5. Repmgr works properly and great in promoting a standby to Master when automatic failover is enabled. The issue we have is with the old master, if master was unavailable due to network problem, though pgSql was up and running on it we end up with 2

FATAL: failed to create a backend connection

Hi There, We are using Postgresql 9.3 with pgpool-II-93-3.5.10. One Master replicating to 3 Standbys using streaming replication. Pgpool is used for load balancing only. Lately we are seeing below on application servers. Caused by: org.postgresql.util.PSQLException: FATAL: failed to create a bac

Multiple postmasters running from same directory

Hi, We are running Postgresql 9.4 with streaming replication and repmgr. Operating system is RHEL6.8 On the master I can see multiple postmaster processes from the same data directory. ps -ef |grep -i postgres|grep postm postgres 81440 1 0 Jan31 ?00:11:37 /usr/pgsql-9.4/bin/postma

Re: Multiple postmasters running from same directory

urenz Albe writes: > > Vikas Sharma wrote: > >> On the master I can see multiple postmaster processes from the same > data directory. > >> ps -ef |grep -i postgres|grep postm > >> postgres 81440 1 0 Jan31 ?00:11:37 > /usr/pgsql-9.4/bin/postmaster -D /va