Redacting params in PostgreSQL logs

Hi, I have a project of sending postgres logs to ELK stack. It is working fine. Now there are concerns that logs have query statements with parameters. and then GDPR and other PII issues. Is there a way I can redact the params in statements in the logs by some config or extension ? Pls note: I

Re: Redacting params in PostgreSQL logs

https://github.com/lfittl/pg_query/blob/master/README.md#parsing-a-normalized-query OK found this :) Regards, Vijay From: Vijaykumar Jain Sent: Friday, March 30, 2018 11:24:30 PM To: pgsql-general@lists.postgresql.org Subject: Redacting params in PostgreSQL logs

Re: Redacting params in PostgreSQL logs

FYI This is what I had tried wrt logstash. https://gist.github.com/cabecada/dd765a30f6946fdbf0bec0eb31fba047 From: Vijaykumar Jain Date: Friday, March 30, 2018 at 11:33 PM To: "pgsql-general@lists.postgresql.org" Subject: Re: Redacting params in PostgreSQL logs https://github.

Re: [External] Merging two database dumps

You can try one option, although just a thought in the air 😊 Use postgres FDW ex. https://robots.thoughtbot.com/postgres-foreign-data-wrapper Create foreign tables in the relevant server schema And then union/union all 😊 or your custom constraint on the destination table where you dump the ro

Re: [External] Re: Schema/Data conversion opensource tools from MySQL to PostgreSQL

I have not tested this, but have read about this in somewhere. https://github.com/the4thdoctor/pg_chameleon if it helps good else ignore Thanks, Vijay From: Pavan Teja Date: Monday, June 25, 2018 at 10:25 PM To: chiru r Cc: "pgsql-gene...@postgresql.org >> PG-General Mailing List" Subject

Re: [External] How to revoke privileged from PostgreSQL's superuser

I am not sure superuser can be selectively restricted via queries, but I am not sure, have not tried. But maybe you can try restricting the super user access to the db from all hosts via the pg_hba.conf. Fore eg. I have a user monitor| Superuser and in my /etc/postgresql/10/main/pg

Re: [External] Re: ERROR: cache lookup failed for function 125940

Hey Tom, Had a small query here. If we have streaming replication enabled, and if we have corruption like this due to power loss etc, is it safe to assume we failover to standby and we should be good (atleast in most cases) I wanted to understand if the system catalog is corrupted, for some re

Re: [External] Multiple COPY on the same table

Hey Ravi, What is the goal you are trying to achieve here. To make pgdump/restore faster? To make replication faster? To make backup faster ? Also no matter how small you split the files into, if network is your bottleneck then I am not sure you can attain n times the benefit my simply sending

Re: [External] Multiple COPY on the same table

I guess this should help you, Ravi. https://www.postgresql.org/docs/10/static/populate.html On 8/20/18, 10:30 PM, "Christopher Browne" wrote: On Mon, 20 Aug 2018 at 12:53, Ravi Krishna wrote: > > What is the goal you are trying to achieve here. > > To make pgdump/restore

Re: [External] RE: Estimate time without running the query

explain analyze would *run* the query and it can be dangerous if it is a DML statement like insert/update/delete 😊 If you still want to go with explain analyze, You can do begin; explain analyze ; rollback; thanks, Vijay From: Johnes Castro Date: Friday, September 14, 2018 at 3:12 AM To: Neto

Re: [External] Slot issues

I guess max_replication_slots has to > current total slots in use. (and not >= ) https://www.postgresql.org/docs/10/static/runtime-config-replication.html https://github.com/postgres/postgres/blob/d6e98ebe375e115c29028f9bd090f0f7e07e2527/src/backend/replication/slot.c#L1506 from the doc, it says

Re: [External] Slot issues

| 13720 | | | 2/D3D0 | (2 rows) postgres=# show max_replication_slots; max_replication_slots --- 2 (1 row) yep it should work with 2. Regards, Vijay On Mon, Oct 15, 2018 at 1:02 AM Vijaykumar Jain wrote: > I guess max_replication_sl

Re: [External] Slot issues

from your master, can you give us the output of select * from pg_replication_slots; maybe some stray slot exists that you may not be aware of ? Regards, Vijay On Mon, Oct 15, 2018 at 1:08 AM Vijaykumar Jain wrote: > ok my bad. > > i just set one of the 9.6.9 version

Re: [External] Re: Slot issues

i guess he is trying to confirm if the value of max_replication_slot is the same on all the master and the standby nodes. also, I am trying to shoot in the dark. can you give the output of (on master) ls /var/lib/postgresql//main/pg_replslot/* also the value of max_replication_slot on the maste

Re: [External] Re: Slot issues

ok wait, you data directory path may be different :) i mean wherever your pg data dir is, from there find pg_replslot folder and list the contents of it. Regards, Vijay On Mon, Oct 15, 2018 at 2:10 AM Vijaykumar Jain wrote: > i guess he is trying to confirm if the value

Re: [External] Re: Slot issues

Sorry Bhargav. I tried to simulate the scenarios, 3 node cluster 1primary, 2hot standby given: max_replication_slots = 2 on primary, and i try to create 2 hot standby -> runs fine. max_replication_slots = 1 on primary, and i try to create 2 hot standby -> gives PANIC, increase replication slots.

Re: [External] Re: Slot issues

ah that explains. Thanks andres. I do not use rsync, hence was not able to reproduce i guess :) Regards, Vijay On Mon, Oct 15, 2018 at 2:46 AM bhargav kamineni wrote: > Yeah i have used rsync , Got it now will increase the > max_replication_slots to high enough , Thank you Andres Freund :-) >

Re: [External] Re: Slot issues

What was the whole point of this divergence :) We use replication slots and it is for this reason we are able to scale our applications by redirecting reads and writes backed by haproxy and pgbouncer. with the health check of replication lag. It works awesome and at a much much lower cost to what

Re: [External] Change in db size

I guess the restore cleared the bloat from the table. \dt+ \di+ If you run the above commands from the terminal, you would see diff in sizes of the. objects. Also querying pg_stat_all_tables you might see “dead” tuples in old tables which would have cleaned up now. Also if there were any large tem

wal_level logical for streaming replication

Hello Team, wal_level = logical wal_level = replica As per docs, wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal remov

Re: [External] Re: wal_level logical for streaming replication

Thanks Laurenz, Regards, Vijay On Thu, Aug 29, 2019 at 2:07 AM Laurenz Albe wrote: > > On Wed, 2019-08-28 at 21:44 +0530, Vijaykumar Jain wrote: > > If I change wal_level back to replica, will it corrupt wal? coz it > > will then be having diff information ( r format of data

Profile a db connection time?

Sorry if this is silly , but I have series of network outages and wanted to understand if I can profile a psql connection to various parts of it initialling a connection Like when using hostname, resolving dns the time to actually establish a db connection and then the time to parse the query and r

Re: Could not read block 0 in file

hands on with the scenarios, I have tried to replicate some scenarios by injecting disk faults using dmsetup local disk. which may/may not be the same the power failure/ RAID controller problems especially on windows. but the above would be helpful to atleast get the data (if possible) from the corrupt pages and also scan through the entire db to find out more problems. -- Thanks, Vijay LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

Re: Could not read block 0 in file

On Fri, 8 Apr 2022 at 15:24, Magnus Hagander wrote: > > > On Fri, Apr 8, 2022 at 11:06 AM wrote: > >> Hi, >> >> While we are looking for a suitable backup to recover from, I hope this >> community may have some other advice on forward steps in case we cannot >> restore. >> >> RCA: Unexpected sh

Re: Could not read block 0 in file

On Fri, 8 Apr 2022 at 15:31, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Fri, 8 Apr 2022 at 15:24, Magnus Hagander wrote: > >> >> >> On Fri, Apr 8, 2022 at 11:06 AM wrote: >> >>> Hi, >>> >>> Wh

Re: Setting up streaming replication on large database (20+ TB) for the first time

I just did a backup and restore of a replica using pgbackrest. db size 28tb nvme/ssd storage 96 cpu, 380 gb mem zst compression, 24 workers (backup, 12 workers restore) 2.5 hours to backup 2 hours to restore. Wal replay is something I forgot to tune, but I could now use https://pgbackrest.org/co

Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Sorry for top posting, from phone. But pgbackrest exactly helped with that. With compression and parallel process in backup, the backup and restore was quick. I used this, where I took a backup and immediately did a restore so less wals to replay, else wal replay is indeed slow. On Thu, Aug 18, 2

Multi master disjoint cluster

Hi all, tl;dr I have a simple question, given a choice if I can write the same data to two databases in parallel, should I opt for primary / replica setup or multi writer/master setup. This setup has the ability to make use of kafka consumer groups (like two replication slots each having their own

Re: Multi master disjoint cluster

10:04 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Hi all, > > tl;dr > I have a simple question, > given a choice if I can write the same data to two databases in parallel, > should I opt for primary / replica setup or multi writer/master setup. This > s

Re: postgres large database backup

On Wed, Nov 30, 2022, 9:10 PM Atul Kumar wrote: > 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

Re: postgres large database backup

On Thu, Dec 1, 2022, 7:11 PM Mladen Gogala wrote: > On 11/30/22 20:41, Michael Loftis wrote: > > > ZFS snapshots don’t typically have much if any performance impact versus > not having a snapshot (and already being on ZFS) because it’s already doing > COW style semantics. > > Hi Michael, > > I a

Re: postgres large database backup

> I do not recall zfs snapshots took anything resource intensive, and it > was quick.ill ask around for actual time. > Ok just a small note, out ingestion pattern is write anywhere, read globally. So we did stop ingestion while snapshot was taken as we could afford it that way. Maybe the story is

incomplete startup packet messages in logs

Hi Team, I know this is has been answered a lot on the internet wrt ignoring, but i am unable to figure out why I get these messages in logs "incomplete startup packet" I have a server running postgresql 10.5 on ubuntu16.04 i do not have any active external connections other than streaming postg

Re: incomplete startup packet messages in logs

27;ll check why there is an attempt to make a connection. Regards, Vijay On Thu, Nov 1, 2018 at 6:57 PM Vijaykumar Jain wrote: > > Hi Team, > > I know this is has been answered a lot on the internet wrt ignoring, > but i am unable to figure out why I get these messages in logs &

Re: incomplete startup packet messages in logs

n Thu, Nov 1, 2018 at 7:17 PM Vijaykumar Jain wrote: > > ok i enabled log_connections > > 2018-11-01 13:44:18 UTC LOG: connection received: host=::1 port=47574 > 2018-11-01 13:44:18 UTC LOG: incomplete startup packet > > i see this in my /etc/hosts > > ::1 loca

Re: incomplete startup packet messages in logs

rtup packet so indeed it is monitoring as it is on the internet :) but thanks for helping out :) Regards, Vijay On Thu, Nov 1, 2018 at 7:20 PM Vijaykumar Jain wrote: > > i have disabled all monitoring i am aware of for postgresql. ( it is > sensu/collectd/ and occasional queries via ha

simple query on why a merge join plan got selected

Hey Guys, I was just playing with exploring joins and plans i came across this create table t1(a int); create table t2(a int); insert into t1 select (x % 10) from generate_series(1, 10) x; insert into t2 select (x % 100) from generate_series(1, 10) x; pgtesting=> analyze t1; ANALYZE pgt

Re: [External] Re: simple query on why a merge join plan got selected

, 16 Dec 2018 at 5:52 AM Tom Lane wrote: > Vijaykumar Jain writes: > > I was just playing with exploring joins and plans i came across this > > create table t1(a int); > > create table t2(a int); > > insert into t1 select (x % 10) from generate_series(1, 10) x; > &g

Re: [External] Re: Geographical multi-master replication

I do not know the use case but we did try the following. We had a small requirement wrt some regional data written to tables but needs to be available to all regions. We made use of logical replication to replicate/publish each local table to all the other regions ( like a many to many) In theory,

FDW, too long to run explain

Hi, with pg v10.1 I have a setup enabled as below. 7 shards ( 1RW, 2 RO ) they all are fronted by FDW talking to each other. we use writes directly to shards, and reads via FDW from all shards (RO) our DB size is ~ 500GB each shard, and tables are huge too. 1 table ~ 200GB, 1 ~55GB, 1 ~40GB and

Re: [External] logical replication

Yes we already do that, provided you take care restrictions of logical replication as mentioned in the doc. On Thu, 14 Feb 2019 at 3:25 PM suganthi Sekar wrote: > > Hi Team , > > > Is it possible to do the logical replication in 2 way (Synchronization) > > > Example : > > > server1 : 5 tabl

Re: [External] logical replication

ds > > Suganthi Sekar > ------ > *From:* Vijaykumar Jain > *Sent:* 14 February 2019 17:15:28 > *To:* suganthi Sekar > *Cc:* pgsql-general@lists.postgresql.org > *Subject:* Re: [External] logical replication > > Yes we already do that, provided you take care restrictions of logical

Re: [External] Re: FDW, too long to run explain

I am yet to figure out the reason, what we have done is implement fake columns to represent samples and giving them random numbers and keeping other bulls to fake limit. Most of the queries that were impacted were the ones that did not push order by and limit to foreign servers. I am also trying t

Re: [External] Re: FDW, too long to run explain

t happened. Regards, Vijay On Sun, Feb 17, 2019 at 11:11 PM Jeff Janes wrote: > On Mon, Feb 4, 2019 at 2:15 AM Vijaykumar Jain > wrote: > >> >> now we have some long running queries via FDW that take minutes and get >> killed explain runs as idle in transaction on

Re: [External] Re: FDW, too long to run explain

Hey Jeff, yes, we now relaxed the idle in transaction setting to 15 mins. i was hesitant to increase the settings as it blocked auto vaccum. We use hot_standby_feedback = true also as we split reads/writes and allow long running queries on read replicas, this too affects auto vaccum. so overall,

Re: [External] Re: FDW, too long to run explain

Regards, Vijay On Mon, Feb 18, 2019 at 12:56 AM Jeff Janes wrote: > On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain > wrote: > >> Assuming your questions as 1,2,3, please find my answers below. >> >> 1)"explain" on foreign servers run as "idle in tr

Re: [External] Re: FDW, too long to run explain

Ok. I’ll try to work on it this week and see if i am able to reproduce anything. On Mon, 18 Feb 2019 at 2:30 AM Jeff Janes wrote: > > > On Sun, Feb 17, 2019 at 2:37 PM Vijaykumar Jain > wrote: > >> >> Ok, i raked this from the logs where enabled log_min_duration_st

Re: [External] Re: FDW, too long to run explain

:) in pg11. Regards, Vijay On Mon, Feb 18, 2019 at 3:07 AM Jeff Janes wrote: > On Sun, Feb 17, 2019 at 6:32 AM Vijaykumar Jain > wrote: > >> I am yet to figure out the reason, what we have done is implement fake >> columns to represent samples and giving them random

Re: [External] LIMIT not showing all results

Can you run both the queries with “explain analyze select ” and paste the output. On Tue, 5 Mar 2019 at 9:41 PM Casey Deccio wrote: > Okay, the subject is a little misleading because of course LIMIT isn't > supposed to all results, but I've got an issue where LIMIT isn't showing > the numbe

Re: [External] LIMIT not showing all results

Regards, Vijay On Tue, Mar 5, 2019 at 10:16 PM Tom Lane wrote: > > Casey Deccio writes: > >> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain wrote: > >> Can you run both the queries with > >> “explain analyze select ” and paste the output. > > > dnsviz=&g

xmin and very high number of concurrent transactions

I was asked this question in one of my demos, and it was interesting one. we update xmin for new inserts with the current txid. now in a very high concurrent scenario where there are more than 2000 concurrent users trying to insert new data, will updating xmin value be a bottleneck? i know we sho

Re: [External] Re: xmin and very high number of concurrent transactions

oken value i need to give to next person? i do not know if am explaining it correctly, pardon my analogy, Regards, Vijay On Wed, Mar 13, 2019 at 1:10 AM Adrian Klaver wrote: > > On 3/12/19 12:19 PM, Vijaykumar Jain wrote: > > I was asked this question in one of my demos, and it was in

Re: [External] Re: xmin and very high number of concurrent transactions

019 at 9:50 AM Laurenz Albe > wrote: > > > > Vijaykumar Jain wrote: > > > I was asked this question in one of my demos, and it was interesting > one. > > > > > > we update xmin for new inserts with the current txid. > > > now in a ver

Re: [External] Re: PostgreSQL temp table blues

May be I am wrong here, but is it not the classic case of connections open too long idle in TX and xid wraparound ? How is connection pool (and which one ?) adding to the woes? I mean the same can be a problem with direct connections too right ? We use pgbouncer with mostly TX level pooling which

postgresql-11 installation errors via deb package on ubuntu 16

Hey Guys, I do not know if this list is also for asking ubuntu package related queries. We have been recently getting a lot of below errors, as a result of which the entire postgresql installation gets broken. the config folder /etc/postgresql/* is empty, initdb fails to initialize the db and we

Re: [External] Re: postgresql-11 installation errors via deb package on ubuntu 16

s native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation. . This package provides the database server for PostgreSQL 11. Homepage: http://www.postgresql.org/ Postgresql-Catversion: 201809051 let me know if you need more info.

Re: [External] Re: postgresql-11 installation errors via deb package on ubuntu 16

at 8:15 PM Tom Lane wrote: > > Vijaykumar Jain writes: > > I do not know if this list is also for asking ubuntu package related > > queries. > > Not really; you'd be better off filing a bug with ubuntu where their > packager(s) will see it. > > The symptoms

Re: [External] postgres 9.5 DB corruption: invalid byte sequence for encoding "UTF8"

i think the experts will chime in soon, but why do you think this as db corruption and not just a bad input? https://github.com/postgres/postgres/blob/master/src/pl/plperl/expected/plperl_lc_1.out or it may also be encoding issue. https://pganalyze.com/docs/log-insights/app-errors/U137 can you d

Re: [External] Re: Import Database

Yes. I do bump up maintenance_work_mem temporarily during a restore. it helps in rebuilding on indexes a little faster. Turning fsync off during restore will help the restore a little fast too but in case of any crash you may have to restart the restore from scratch. Also do have the option to take

logical replication initiate via manual pg_dump

Hey Guys, tl;dr, but incase i missed something, i can follow up on this with more details. I have a setup where i try to upgrade a cluster from pg10 to pg11 via logical replication with minimum downtime. its a database that is 500GB with 1 table having 350GB of data (+ bloat) and 100GB of indexe

Re: logical replication initiate via manual pg_dump

was the "too many indexes" slowed down copy way too much. anyways, i got to explore pg_replication_origin_advance which was a cool thing. Regards, Vijay On Fri, May 10, 2019 at 8:59 PM Vijaykumar Jain wrote: > > Hey Guys, > > tl;dr, but incase i missed something, i can follo

multiple nodes in FDW create server statement

All, We are glad that we have this feature that allows us to load balance reads. that has helped us a lot. https://paquier.xyz/postgresql-2/postgres-10-multi-host-connstr/ I would like to know if it is possible to request a similar enhancement to FDWs too? https://www.postgresql.org/docs/11/sql-c

Re: [External] Re: multiple nodes in FDW create server statement

awesomeness. Thanks Laurenz. Regards, Vijay On Wed, Jul 3, 2019 at 12:48 PM Laurenz Albe wrote: > Vijaykumar Jain wrote: > > We are glad that we have this feature that allows us to load balance > reads. > > that has helped us a lot. > > https://paquier.xyz/postgresql-

Re: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen

act-with-the-pg_filedump/> i tried an example, but i had a lot of info for that. <https://www.highgo.ca/2021/07/14/first-contact-with-the-pg_filedump/>corruption demo for blogs. (github.com) <https://gist.github.com/cabecada/8024d98024559e9fc97ccfcb5324c09f> (if you dont un

Re: constant crashing

www.enterprisedb.com/blog/finding-memory-leaks-postgres-c-code> just kill the process requesting more mem than available Memory context: how PostgreSQL allocates memory - CYBERTEC (cybertec-postgresql.com) <https://www.cybertec-postgresql.com/en/memory-context-for-postgresql-memory-management/> -- Thanks, Vijay LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

Re: constant crashing

Ignore my thread, I guess there might be a bug given it segfaulted. On Mon, Apr 15, 2024, 12:48 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Sun, 14 Apr 2024 at 21:50, jack wrote: > >> The full error reads: >> server closed the connectio

Re: Backup_Long Running

On Wed, Apr 24, 2024, 12:33 PM jaya kumar wrote: > Hi Team, > > > > Production database Backup is running very long hours. Any option to > reduce backup time? Kindly advise me. > > > > DB size: 793 GB > > > > We are taking pg_basebackup backup. > > do you see network saturation, io saturation ? g

Re: I have ansible for postgres-etcd-patroni

Hi Iman. Thank you for sharing. On Sun, May 5, 2024, 1:42 AM Iman Bakhtiari wrote: > Hi i have written this ansible with https://github/sudoix together > https://github.com/imanbakhtiari/postgres-ansible.git > This ansible needs 5 virtual machine > in 3 of them it install postgresql with patr

Re: AI for query-planning?

On Sat, Jun 22, 2024, 5:20 PM Andreas Joseph Krogh wrote: > Hi, are there any plans for using some kind of AI for query-planning? > > Can someone with more knowledge about this than I have please explain why > it might, or not, be a good idea, and what the challenges are? > https://github.com/os

Re: -1/0 virtualtransaction

Hi, I am just trying to jump in, but ignore if not relevant. when you said*Eventually this results in an "out of shared memory" error * Can you rule out the below two scenarios (wrt /dev/shm too low in docker or query requesting for too many locks either due to parallellism/partition involv

Re: client backwards compatible with older servers

I am not sure which postgresql client is being referred to. If it is psql command line then from the notes section of the docs below says https://www.postgresql.org/docs/current/app-psql.html psql works best with servers of the same or an older major version. Backslash commands are particularly

Re: Streaming replica failure

Were there any issues with hardware ? Memory/storage ? I am not sure but it look like data loss to me. If you have admin access? Can you run dmesg -a, system logs, hyperion logs etc and see if there are any errors related to memory corruption. Coz if there are hardware issues, this will happen a

Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?

This wiki page. It has PR references for mysql and mongo for the fsycnc issue. Fsync Errors - PostgreSQL wiki I'd leave the more intellectual brainstorming to the experts. Also, ask for concrete references / reproducible scenarios for opinions if y

Re: trigger impacting insertion of records

just simplified, but it works fine for me. create table example(id int primary key, value text); create or replace function trg_fn() returns trigger language plpgsql as $$ begin RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;

Re: Optimizing search query with sorting by creation field

What is your baseline expectation? With what size of db table, what query should take how much time How much server resources can be used? If this seems to be a timeseries db, Are the rows append only and random insertion order ? You are create partitions based on time and sub partitions on som

Re: idle_in_transaction_session_timeout

Why do you want to increase that timeout ? I hope you are aware long idle in transactions connections would delay vacuuming and result in much larger bloats and slow down the db over period of time. You need to upgrade and push using the reasons that these are not supported. asking for workarounds

Copyright vs Licence

Hi All, I have been playing around with the pg_auto_failover extension by citus and have really enjoyed playing chaos with it. citusdata/pg_auto_failover: Postgres extension and service for automated failover and high-availability (github.com) Now I

Re: force partition pruning

I do not know how to put this in words, but see below when the predicate is explicitly applied to the main table with partition. postgres=# \d+ prt1 Partitioned table "public.prt1" Column | Type| Collation | Nullable | Default | Storage | Stats ta

Re: force partition pruning

..2.47 rows=13 width=4) (never executed) Index Cond: (col1 = tbl1.col1) Heap Fetches: 0 Planning Time: 0.211 ms Execution Time: 0.083 ms (28 rows) So, in short , i do not know how pruning works during execution time. I hope someone else might be able to help

Re: force partition pruning

Ok. maybe you are in a rush. But I would keep the thread open, to understand what I am not understanding or else, it'll become a habit of converting sql to plpgsql :) Big Guys, It seems, when the table is partitioned by range, it makes use of a nested loop which helps in partition pruning. if the

Re: force partition pruning

ork as the outer table was the partitioned table selected. On Tue, 11 May 2021 at 22:42, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Ok. maybe you are in a rush. > > But I would keep the thread open, to understand what I am not > understanding or else, it'll

Re: force partition pruning

oops=1) Filter: (col2 = ANY ('{1,2}'::integer[])) Rows Removed by Filter: 1 Planning Time: 0.233 ms Execution Time: 22.428 ms (16 rows) so i guess it works. I should not try to be smarter than the optimizer :) On Tue, 11

Re: force partition pruning

Fair enough. Many thanks for taking time out to follow up and clear my misunderstanding. I’ll not pollute the thread , since OP got what he wanted. But I’ll have to spend more time trying to simulate it with data and reread what you want to say :). But thanks again for clearing that up. On Wed,

Postgresql fdw tracing

Hello Gentlemen, I am trying to setup a shard array of pg clusters behind fdw which is fronted by haproxy/envoyproxy to load balance. Something like this, but including pgbouncer. https://image.slidesharecdn.com/fdw-basedsharding-170321103514/95/fdwbased-sharding-update-and-future-12-638.jpg?cb=1

Re: I have no idea why pg_dump isn't dumping all of my data

Can you try dumping using verbose flag. -v Just want to confirm if the user has relevant permissions. On Fri, May 21, 2021, 3:04 PM Simon Connah wrote: > Hi, > > I'm running the following command to dump my database: > > /usr/bin/pg_dump > --file=/home/simon/nanoscopic_db_127_0_0_1-2021_05_21_

Re: I have no idea why pg_dump isn't dumping all of my data

d if it does not throw any errors, coz any errors wrt permissions are thrown right away to console. maybe someone with more exp would be able to help. On Fri, 21 May 2021 at 15:32, Simon Connah wrote: > ‐‐‐ Original Message ‐‐‐ > On Friday, May 21st, 2021 at 10:55, Vijayku

Re: I have no idea why pg_dump isn't dumping all of my data

/github.com/xmrsoftware/nanoscopic/tree/master/sql/nanoscopic > ‐‐‐ Original Message ‐‐‐ > On Friday, May 21st, 2021 at 11:29, Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > > i just did a dump of a db which was owned by postgres but some tables > owned by

Re: I have no idea why pg_dump isn't dumping all of my data

copic IS 'Database requirements for the Nanoscopic blogging platform'; -- Completed on 2021-05-21 17:03:34 IST -- -- PostgreSQL database dump complete -- the dump only refers to creation of extension. so when you load the extension via restore, it would create the extension and cr

Re: I have no idea why pg_dump isn't dumping all of my data

new version of the extension that might contain more or different objects than the old version. Note however that you must have the extension's control, script, and other files available when loading such a dump into a new database. On Fri, 21 May 2021 at 17:07, Vijaykumar Jain <

Re: Setting up replication

core ref: PostgreSQL: Documentation: 13: Part III. Server Administration although this is a lot verbose, but you would keep coming back to this to tune your setup. to understand basic setups. some are How to Set Up Streaming Replication in PostgreSQ

Re: TRUNCATE memory leak with temporary tables?

Yes, I too see growth when text type is used, but not when int or even fixed size char(10) is used. I always thought truncate was similar to delete + vacuum full, but checking for your scenarios, I did not see an update on pg_stat_user_table on truncate for vacuums. then i checked PostgreSQL Inte

Re: TRUNCATE memory leak with temporary tables?

memory allocation repeatedly. I am not a C developer :), please ignore if i am diverting. On Fri, 28 May 2021 at 18:52, Tom Lane wrote: > Vijaykumar Jain writes: > > I too see growth when text type is used, but not when int or even fixed > > size char(10) is used. > > ...

Re: WARNING: oldest xmin is far in the past

If the replication slot is still inactive, It will prevent vacuum to do the cleanup no matter how much vacuum is run manually. did the slot show as active after the restart of the collector ? If it is active then may be increase maintenance_work_mem to a aggresive value and lower nap time for aut

Re: WAL accumulating, Logical Replication pg 13

I am not too sure with 9.3 i tried an upgrade from 9.6 to 11 using logical replication (pg_logical extension) one thing to note. logical replication initiates a copy from a snapshot, then changes from then on. I had a very high insert rate on my source tables (v9.6) and the destination (v11) coul

Re: WAL accumulating, Logical Replication pg 13

and start > over. But since I'm trying out logical replication, I would like to be more > in control than that. It's there anything that I can dig into to find out > why the WAL is accumulating? > > Op vr 28 mei 2021 22:20 schreef Vijaykumar Jain < > vijaykumarjain.git...@g

max_connections

I have a two dumb questions. 1) I know the max_connections value change requires a restart. I also read a thread, which says why it is the case, assuming it still holds true. Jean Arnaud writes: > I'm looking for a way to change the "max_connections" parameter without > restarting the PostGreSQ

Re: WAL accumulating, Logical Replication pg 13

: Thanks for the suggestion. But first > I'd like to get some better grip on what is going on before searching for > bugs. > > Still, any help will be much appreciated > > On Sat, May 29, 2021 at 5:16 PM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: >

Re: WAL accumulating, Logical Replication pg 13

This part. If you can read perl :), https://github.com/postgres/postgres/tree/master/src/test/subscription/t On Mon, May 31, 2021, 9:02 PM Willy-Bas Loos wrote: > > > On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> So

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

if you are not using it concurrently, can you confirm the there are *no active* queries on the mv. refresh requires AccessExclusiveLock and will wait, till it gets one. just asking if you can rule out the extended time is not due to waiting for lock. also, can you share the plans where you see th

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

ok i see this. i may be wrong, but even when i force parallel cost to 0, i only get workers to create mv, but refresh mv plan does not use workers for the same conf params. *** postgres=# create table if not exists t( id int primary key, value int ); CREATE TABLE postgres=# insert

  1   2   3   >