Re: PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers

Any ideas on this guys? Thank you. On Fri, Aug 14, 2020 at 1:39 AM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > Hello Postgres community. > In a few words I'm figuring out this stats from the background writer > where you can see buffers_clean is 0 and buffers_backen

Re: PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers

| checkpoint_flush_after | 32 | 8kB checkpoint_timeout | 300 | s checkpoint_warning | 30 | s max_wal_size | 4096| MB min_wal_size | 2048| MB Thank you. On Fri, Aug 14, 2020 at 1:39 AM Rene Romero Benavides

PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers

Hello Postgres community. In a few words I'm figuring out this stats from the background writer where you can see buffers_clean is 0 and buffers_backend = 44849371, I would like it to be the other way around, that's more efficient, right? -[ RECORD 1 ]-+--

Re: ansible modules for postgresql installation/config

I've had good experiences when working with roles published by geerlingguy, he's written several Ansible books also: https://galaxy.ansible.com/geerlingguy/postgresql On Thu, Jun 11, 2020 at 1:28 PM Chris Stephens wrote: > it looks like there are a number of roles available through ansible

Re: gdal, proj and other postgis dependencies missing in postgres repos

Thank you very much David. On Sun, Jun 7, 2020 at 5:48 PM David G. Johnston wrote: > On Sunday, June 7, 2020, Rene Romero Benavides > wrote: > >> On Sun, Jun 7, 2020 at 5:37 PM Rene Romero Benavides < >> rene.romer...@gmail.com> wrote: >> >>> Hi ev

Re: gdal, proj and other postgis dependencies missing in postgres repos

On Sun, Jun 7, 2020 at 5:37 PM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > Hi everybody, do you know what happened to gdal and other postgis > dependencies like proj in the official postgres repos? > they appear to be missing in these repos > > https://yum.pos

gdal, proj and other postgis dependencies missing in postgres repos

Hi everybody, do you know what happened to gdal and other postgis dependencies like proj in the official postgres repos? they appear to be missing in these repos https://yum.postgresql.org/12/redhat/rhel-7-x86_64/ https://yum.postgresql.org/11/redhat/rhel-7-x86_64/

Re: Suggestion to Monitoring Tool

Give pgwatch2 a try: https://pgwatch.com/ On Wed, May 27, 2020 at 11:46 AM postgann2020 s wrote: > Hi Team, > > Thanks for your support. > > Environment Details: > OS: RHEL 7.2 > Postgres: 9.5.15 > Master-Slave with Streaming replication > > We are planning to implement the monitoring tool for

Re: Log Unique Queries without Params?

Also try pg_stat_statements, does that on the fly and it will give you very interesting execution stats too. On Mon, Apr 13, 2020 at 12:37 AM Chris Morris wrote: > Thx! > > On Sat, Apr 11, 2020 at 11:55 PM Julien Rouhaud > wrote: > >> On Sun, Apr 12, 2020 at 6:51 AM Chris Morris >> wrote: >>

Re: Postgresql Data corruption

Can't help you personally right now due to work, but try these guys: https://pgexperts.com/services/emergency_help/ On Tue, Jan 14, 2020 at 4:08 PM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov < > tulqin.navru...@fi

Re: Postgresql Data corruption

On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov < tulqin.navru...@finnetlimited.com> wrote: > > > Hi team > > Could you help us to sort this out please? > > We had some hardware problems with Datacenter and could not using > postgresql from restored snapshots it showing "Structure needs to be >

Re: Tuple concurrency issue in large objects

Hi Shalini. The usual diagnostic info is your postgresql server version, major and minor version, such as in 12.1 , the major version is 12 and the minor version (patch version) is 1. On Fri, Dec 6, 2019 at 9:26 AM Shalini wrote: > Hi all, > > I am working on a project which allows multiple

Re: Pg_auto_failover

It seems it does, only 10 and 11 as of this: "We support Postgres 10 and Postgres 11. " https://cloudblogs.microsoft.com/opensource/2019/05/06/introducing-pg_auto_failover-postgresql-open-source-extension-automated-failover-high-availability/ On Sat, Sep 28, 2019 at 8:44 AM Sonam Sharma wrote:

Re: pgq is one of the most underrated pg related stuff

pgq is great, I use it to ship particular records to kafka, but I'm planning on switching to debezium to take advantage of wal logical decoding with wal2json. Cheers. On Wed, Sep 25, 2019 at 5:18 AM Миша Тюрин wrote: > > Ok! How are you doing on (with)without pgq? > > // another underrated was

Re: terminating walsender process due to replication timeout

To detect network issues maybe you could monitor replication delay. On Mon, May 13, 2019 at 6:42 AM wrote: > Hello PostgreSQL Community! > > I faced an issue on my linux machine using Postgres 11.3 . > I have 2 nodes in db cluster: master and standby. > I tried to perform a plenty of

Re: stale WAL files?

What's your current max_wal_size parameter? SHOW max_wal_size; If it's 8GB as your configuration's previous value, you would get a constant share of 512 WAL files. If it's a development environment set it to the desired size, the smaller the value, the more frequent the checkpoints, but your

Re: Reg: Pg_Ctl command help

How did you install it? It should be installed as a service. On Wed, Apr 3, 2019 at 4:24 AM Nadeem Akbar basha wrote: > Hello, > > > > I have a query regarding starting the Postgres server using the ‘pg_ctl’ > command in the command prompt (Windows OS). > > > > I try to start the postgres

Re: stale WAL files?

On Wed, Apr 3, 2019 at 1:05 PM Rob Sargent wrote: > > > On Apr 1, 2019, at 9:20 PM, Rene Romero Benavides > wrote: > > > On Mon, Apr 1, 2019 at 6:30 PM Rene Romero Benavides < > rene.romer...@gmail.com> wrote: > >> >> On Sat, Mar 30, 2019 at 5:0

Re: Gigantic load average spikes

On Mon, Apr 1, 2019 at 10:35 AM rihad wrote: > On 04/01/2019 08:30 PM, Michel Pelletier wrote: > > > > On Sun, Mar 31, 2019 at 10:49 PM David Rowley < > david.row...@2ndquadrant.com> wrote: > >> >> Perhaps a bunch of processes waiting on the access exclusive lock on >> the materialized view

Re: Table Export & Import

Hi Sathish, as Michel Pelletier, pointed out, a trigger based approach (i.e slony-I, bucardo) or the pg_logical plugin (requires server restart) is the way to go, personally, I've worked with slony-I, the initial setup is somewhat tricky, but it works, depending on how transactional the table,

Re: stale WAL files?

On Mon, Apr 1, 2019 at 6:30 PM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > > On Sat, Mar 30, 2019 at 5:03 PM Gmail wrote: > >> >> >> > On Mar 30, 2019, at 10:54 AM, Gmail wrote: >> > >> > >> >>>> On Mar 29, 2

Re: stale WAL files?

On Sat, Mar 30, 2019 at 5:03 PM Gmail wrote: > > > > On Mar 30, 2019, at 10:54 AM, Gmail wrote: > > > > > On Mar 29, 2019, at 6:58 AM, Michael Paquier > wrote: > >>> > >>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote: > >>> This is pg10 so it's pg_wal. ls -ltr > >>> > >>>

Re: PostgreSQL temp table blues

In conjunction with some parameter to renew idle connections and those that have been opened for too long will help you prevent this in the future, this also helps prevent server processes from becoming too big memory wise. On Wed, Mar 13, 2019 at 4:32 PM Rene Romero Benavides < rene.ro

Re: PostgreSQL temp table blues

Wow, thanks for sharing your experience. What kind of connection pooling are we talking about? some connection pools implement a DISCARD ALL statement after a session close, that may help if possible to configure. On Wed, Mar 13, 2019 at 4:21 AM Jahwan Kim wrote: > Hi all, > > > I'd like to

Re: Hot to model data in DB (PostgreSQL) for SNMP-like multiple configurations

Try partitioning your table based on your device_id, that will give you a considerable boost for queries which the where clause includes it. for 9.6 (that's the one your using right?) there's pg_partman for that kind of thing, in this case you would partition by ranges, if the id's are sequential

Re: Table Replication

You could also use Slony-I On Tue, Jan 29, 2019 at 12:24 PM Thomas Endres wrote: > > On Jan 29, 2019, at 8:22 AM, Fabrízio de Royes Mello < > fabri...@timbira.com.br> wrote: > > > > Em ter, 29 de jan de 2019 às 10:24, Sathish Kumar > escreveu: > > > > Hi, > > > > We are trying to replicate few

Re: type int2vector

What's the question? On Mon, Jan 28, 2019 at 4:25 AM 吉成恒 wrote: > > select * from pg_partition where 2 =all(pg_partition.paratts); > > -- > > 吉成恒 > > 光大证券股份有限公司 信息技术总部（数据中心） > > 地址：静安区新闸路1508号7楼 > > 电话：021-22169287 > > 手机：18717772189 > >

Re: Function `set_config` doesn't work in with query?

On Fri, Jan 4, 2019 at 3:37 AM Zexuan Luo wrote: > For instance: > ``` > with t as ( > select set_config('blah', '1', false) > ) > select current_setting('blah'); > select current_setting('blah'); > ``` > > Execute queries above gets these error messages: > psql:test-query-dump.sql:4:

Re: Amazon Aurora

It might be and Aurora issue, but AFAIK, the only difference with the mainstream PostgreSQL project is their storage implementation. What do you mean by slow? each and every query takes longer than it should? have you analyzed particular execution plans? On Thu, Dec 20, 2018 at 3:03 PM Ravi

Re: shared_buffers on Big RAM systems

This topic seems to be always open to discussion. In my opinion, it depends on how big your work dataset is, there's no use in sizing shared_buffers beyond that size. I think, the most reasonable thing is analyzing each case as proposed here:

Re: What is the tuplestore?

B free, which is 74% of the volume. > > On 12/10/2018 04:50 PM, Rene Romero Benavides wrote: > > Maybe the temp space got released right after the failure? > > https://grokbase.com/t/postgresql/pgsql-general/02ag7k8gcr/tuplestore-write-failed > do you have space usage charts

Re: What is the tuplestore?

e's certainly a problem with the application, but the error is in the > pg_log, not the application log. > > On 12/10/2018 03:21 PM, Rene Romero Benavides wrote: > > What if this error message pertains to something happening on the > application side? > > Am Mo., 10. Dez. 20

Re: What is the tuplestore?

What if this error message pertains to something happening on the application side? Am Mo., 10. Dez. 2018 um 09:56 Uhr schrieb Ron : > Hi, > > v9.6.6 > > > 2018-12-07 06:21:55.504 EST 10.140.181.89(35868) CDS CDSLBXW 13748 SELECT > PostgreSQL JDBC Driver 53100 ERROR: could not write to

Re: order of reading the conf files

Why do you need to know that ? Am Do., 6. Dez. 2018 um 01:21 Uhr schrieb bhargav kamineni < kbn98...@gmail.com>: > > Hi, > > may i know the order in which postgres reads the configuration files like > conf , auto.conf , hba ? > and how does postmaster forks postgres , can we see that forking

Re: Moving large table between servers: logical replication or postgres_fdw

I tend to believe that a backup (pg_dump) in custom format (-F c) using multiple jobs (parallel) -> restore (pg_restore) also with multiple concurrent jobs would be better. Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb Rhys A.D. Stewart < rhys.stew...@gmail.com>: > Greetings Folks, > > I have a

Re: Unused indexes

I think your assumption is correct, as long as the statistics collector is working correctly (I've never seen this not being the case), and the setting "track_counts" is set to on. Am Sa., 1. Dez. 2018 um 05:24 Uhr schrieb Ron : > For indexes that existed before the cluster was last started, and

Re: Vacuum and Materialized view refresh slow

Have you benchmarked the new infrastructure's IO and network performance? why did you switch providers? was it because of cost?

Re: replication lag despite corrective config

How big have been the delays after the new settings? I guess significantly lower than before, right? how much have they decreased? Am Mi., 21. Nov. 2018 um 13:18 Uhr schrieb Rene Romero Benavides < rene.romer...@gmail.com>: > You're welcome. > Since last Saturday when you addressed

Re: replication lag despite corrective config

measuring bloat? if so, do you notice a significant increase? Am Di., 20. Nov. 2018 um 23:08 Uhr schrieb Wyatt Alt : > Hi Rene, > On 11/19/18 8:46 PM, Rene Romero Benavides wrote: > > Not sure about the root cause but I can make these observations and raise > some questions: > 1) 9.6

Re: replication lag despite corrective config

Not sure about the root cause but I can make these observations and raise some questions: 1) 9.6.6 is five bug fix versions behind 2) 300GB is so big a table, wouldn't make sense to you to partition it ? 2a) or if it's partitioned, doesn't the time of creation or dropping of new partitions match

Re: dblink: give search_path

What about setting the search path at the user level? ALTER ROLE act SET search_path = act,logger; Best. 2018-04-11 1:44 GMT-05:00 Thiemo Kellner : > Hi all > > I try to execute a function not in the Schema I connect to with dblink. Is > there way to tell dblink to

Re: Help troubleshooting SubtransControlLock problems

For such issues, I find this view very useful (the first one): https://wiki.postgresql.org/wiki/Lock_Monitoring Examine blocking_pid's , and tell us what kind of operation is blocking the other processes . Also, are there many long running transactions in your server? 2018-03-06 21:24

Re: Performance issues during backup

What about sending the backup to a different server? through ssh / rsync or something, that would save lots of IO activity 2018-02-20 2:02 GMT-06:00 Laurenz Albe : > Dylan Luong wrote: > > We perform nighty base backup of our production PostgreSQL instance. We > have a

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

My guess is that the transaction doing: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2 updates ticket before reaching that point And update ticket set unread = true where ticketid = $1 updates planscheduleitem before that Does it make sense to you? Btw, do the transactions

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

Hi. Does any of the two tables have triggers? What's the database / transaction isolation level? Do the updates run in a transaction among other read / write operations within the same transaction ? Regards. 2018-02-18 23:28 GMT-06:00 David Wheeler : > Hi, > > We’re seeing

Re: pgstattuple free_percent to high

Check for long running transactions modifying (update, insert) on those tables ,using pg_stat_activity. Tweak these storage parameters for such tables: autovacuum_vacuum_cost_delay : decrease it (the autovacuum daemon goes to sleep less often ) autovacuum_vacuum_threshold : decrease it (to