Re: verify checksums online

https://github.com/google/pg_page_verification Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada> On Sat, Apr 19, 2025, 9:00 PM Greg Sabino Mullane wrote: > On Fri, Apr 18, 2025 at 5:18 PM Jeremy Schneider > wrote: > >> but i wanted to confirm with so

Fwd: A million users

-- Forwarded message - From: Kaare Rasmussen <> Hi A simple question before design. Would there be performance issues having perhaps a million users, each having several roles? I could imagine a user would have on average 10-20 roles. I tried to grant select permissions to 5000

Re: Fwd: A million users

On Wed, Nov 13, 2024, 4:42 PM Achilleas Mantzios - cloud < a.mantz...@cloud.gatewaynet.com> wrote: > > Exactly! In the later versions, security gets more and more refined and > strengthened. So ppl should think about moving away from "public" , and > start implementing finer grained schemes of sec

Re: Fwd: A million users

On Wed, Nov 13, 2024, 5:00 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Wed, Nov 13, 2024, 4:42 PM Achilleas Mantzios - cloud < > a.mantz...@cloud.gatewaynet.com> wrote: > >> >> Exactly! In the later versions, security gets more a

Re: Bash function from psql (v14)

On Sat, 9 Nov 2024 at 23:11, Murthy Nunna wrote: > I am trying to code bash function and call it from psql. But it is failing. > How can I get this to work. Creating a separate script instead of a function > works, but I do not want to do that. I have too many variables to be passed > back and

Re: Random memory related errors on live postgres 14.13 instance on Ubuntu 22.04 LTS

On Sat, 2 Nov 2024 at 12:50, Ian J Cottee wrote: > As the previous errors (thankfully) are not showing now I can't really do any > more debugging but I'll report back on the results of the memtest. just for the sake of this thread, i wanted to make a mention of Summary of Errors reported early

Re: Random memory related errors on live postgres 14.13 instance on Ubuntu 22.04 LTS

t crash even if that is the case, but just asking ... lastly is it possible to memcheck run on the machine just to ensure no memory scares ... if this is running on a vm, or bare metal any hardware errors around that time ? most likely it looks like a h/w issue, we used to see things like this on bare metals which only happened occasionally and then frequently till we moved away from that setup. also, does it happen only when the optimiser picks a plan involving parallel workers for a query? If you set max_parallel_workers_per_gather to 0, to not parallelize anything , do you still see the issue ? Just insights, if not useful, pls ignore. > Best regards > > > Ian Cottee > > > > > > > > > > > > > > > -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: Delays between "connection received" and "connection authenticated" because of localhost entries in hba

o issues in the past. https://unix.stackexchange.com/questions/290987/resolving-hostname-takes-5-seconds maybe disabling ipv6 completely resolves this for the time being. -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: Delays between "connection received" and "connection authenticated" because of localhost entries in hba

but then if a connection established is logged in the log, then name resolution is ok ? or the syscall to md5 is slow , does it work faster for trust ? just saying :) -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: Delays between "connection received" and "connection authenticated" because of localhost entries in hba

On Tue, 29 Oct 2024 at 23:43, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Tue, 29 Oct 2024 at 22:00, Daniel Westermann (DWE) < > daniel.westerm...@dbi-services.com> wrote: > >> Delays between "connection received" and "c

Re: Delays between "connection received" and "connection authenticated" because of localhost entries in hba

missed the mailing list.

Re: explain vs auto_explain

ng rows, thereby killing the console session or something ? i tried to check the code for auto_explain , there is nothing that helps understand why it was provided as a separate . -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 23:48, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > ok, it makes sense for the reason of having auto_explain. but maybe i did > ask correctly, > why do we not have the extended flags in auto_explain , in , explain wrt > nested_

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 23:31, Tom Lane wrote: > "David G. Johnston" writes: > > On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain < > > vijaykumarjain.git...@gmail.com> wrote: > >> i tried to check the code for auto_explain , there is nothing that helps

explain vs auto_explain

-> Index Scan using t_pkey on public.t (cost=0.42..8.50 rows=4 width=6) (actual time=0.006..0.008 rows=4 loops=1) Output: ctid Index Cond: (t.col1 < 5) Buffers: shared hit=4 Trigger RI_ConstraintTrigger_a_16475 for constraint r_col1_fkey: time=46.274 calls=4 */ -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: how to know if the sql will run a seq scan

> > > > I don't have time now to create an example, but I can point you at: > > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE > > 5.12.2.2. Partition Maintenance > > "As an alternative to creating a new partition, it is sometimes more > convenient to c

Re: how to know if the sql will run a seq scan

On Wed, 16 Oct 2024 at 02:59, Adrian Klaver wrote: > On 10/15/24 13:50, Vijaykumar Jain wrote: > > Sorry top posting, coz Gmail app on phone. > > > > Yeah, my point was for example we have a large table and we are > > attaching a table as a partition. Now it w

Re: how to know if the sql will run a seq scan

then validating constraint to reduce blocking. But yeah monitoring locks for the statement should give me good enough hint of what will happen. Thanks for your reply. It helps. On Wed, Oct 16, 2024, 1:54 AM Adrian Klaver wrote: > On 10/15/24 12:50, Vijaykumar Jain wrote: > &g

how to know if the sql will run a seq scan

q_tup_read from pg_stat_user_tables where relname = 't'; -[ RECORD 1 ]-+-- relname | t seq_scan | 8 last_seq_scan | 2024-10-15 19:41:50.931282+00 age | -00:05:14.066944 seq_tup_read | 500 but how do i log this seq scan here for this sq

Re: how to use trace_lock_oidmin config correctly

gclass::oid; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done oid --- 16401 (1 row) postgres=# drop table t; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done DROP TABLE */ On Mon, 14 Oct 2024 at 16:30, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: >

how to use trace_lock_oidmin config correctly

ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID) ALTER TABLE */ -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

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: 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: 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: 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: 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: 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: 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

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

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: 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

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: [(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

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: 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: 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

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: increasing effective_cache_size slows down join queries by a factor of 4000x

On Fri, 4 Feb 2022 at 01:03, A Shaposhnikov wrote: > I made a mistake yesterday claiming that the created statistics > changed the row counts in the estimates - it did not - I looked at > the wrong query yesterday. In the correct query plan the row estimate > still differs from the actual by man

Re: Using the indexing and sampling APIs to realize progressive features

On Thu, Feb 3, 2022, 8:55 PM wrote: > Hi, > > > > I have some questions regarding the indexing and sampling API. > > My aim is to implement a variant of progressive indexing as seen in this > paper (link). To summarize, > > I want to implement a variant of online aggregation, where an aggregate >

Re: Subscription stuck at initialize state

| 0/20001B8 (5 rows) postgres=# select count(1) from t_bytea; count --- 1000 (1 row) so two things, can you check logs for warning messages of out of *logical replication worker slots *on subscribers ? can you try bumping them, and check if it catches up (srsubstate should either be *r(READY)* or *d (INITIAL COPY))* also monitor logs for both publisher and subscriber. I also figured out all the debugging steps I requested earlier were useless. -- Thanks, Vijay LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

Re: max_connections different between primary and standby: is it possible?

On Thu, Feb 3, 2022, 3:07 PM Luca Ferrari wrote: > Hi all, > running PostgreSQL 14, physical replication with slot, after changing > (increasing) the max_connections on the primary, I had this message at > a restart from the standby: > > DETAIL: max_connections = 100 is a lower setting than on t

Re: Subscription stuck at initialize state

On Thu, 3 Feb 2022 at 12:44, Abhishek Bhola wrote: > Hi Vijaykumar, > > I checked the pg_subscription_rel and all the tables in that subscription > are in the state - i (initialize). > I also tried creating a new publication on the source DB with just one > table and tried to subscribe it, it doe

Re: Subscription stuck at initialize state

On Thu, Feb 3, 2022, 10:32 AM Abhishek Bhola wrote: > So far I figured out that the problem is on the subscriber side. > The same publication, when subscribed to on another DB, works fine. > Also noticed that the remote_lsn value on the target DB is still 0/0. > > targetdb=# select * from pg_repl

Re: Query on postgres_fdw extension

On Thu, 20 Jan 2022 at 21:29, Duarte Carreira wrote: > Hello everyone. > > I don't know... realistically what do you guys see as a best/simple > approach? > We implemented a custom sharding (directory sharding with lookup tables) layer of 10 shards, but it was write local, read global. the api w

Re: How to confirm the pg_hba.conf service is correctly working

On Thu, 23 Dec 2021 at 15:45, shing dong wrote: > I have tested this feature , only had >> > > host VJ VJ_USER 10.10.10.1/32 md5 > > in the pg_hba.conf file > I may be a bit off , but can you try a couple of things, other than a fresh install, incase you have time to debug more. is it

Re: Debugging features needed

On Fri, Nov 5, 2021, 7:46 PM Michael Lewis wrote: > For my purposes, app name isn't long enough so we put a comment at the > start of every SQL that has a unique ID generated in the application. This > ensures that we can tell one connection apart from another even when both > are coming from the

Re: Debugging features needed

On Fri, Nov 5, 2021, 4:58 PM Boboc Cristi wrote: > Hello! > I need a feature that would me allow to debug "live" the work of an > application together with a PostgreSQL database. > > I think that if I would be able to define a session variable that is > visible in pg_stat_activity (or in othe

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

On Tue, 26 Oct 2021 at 11:39, Vivekk P wrote: > Hi Team, > > Please have a look on the below problem statement and suggest us if there > are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL > APPEND > > > 1. We have tried fine-tuning the below parameters with all possible values

Re: Can db user change own password?

On Wed, 20 Oct 2021 at 20:52, Adrian Klaver wrote: > On 10/20/21 08:07, Toomas wrote: > > Hi Adrian, > > > > Thank you for your help. The issue was that when user logged into > database his session_user user was set as owner of database automatically. > User had success to change password when se

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 23:20, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 19 Oct 2021 at 23:09, Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> >> On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar >> wrote: >

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 23:09, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar > wrote: > >> Hi All, >> >> >> A basic question on handling large number of concurrent requests on DB. >>

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar wrote: > Hi All, > > > A basic question on handling large number of concurrent requests on DB. > > I have a cloud service which can get large of requests which will > obviously trigger the db operations. > > Every db will have some max connection limit

Re: Grant select for all tables of the 12 schemas of my one db ?

On Wed, 13 Oct 2021 at 16:30, hubert depesz lubaczewski wrote: > On Wed, Oct 13, 2021 at 03:33:20PM +0530, Vijaykumar Jain wrote: > > something like this ? > > Like, but not exactly. > > Consider what will happen if you have schema named "whatever something >

Re: Grant select for all tables of the 12 schemas of my one db ?

something like this ? do $$ declare sch text; stmt text; begin for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest stmt = 'GRANT USAGE ON SCHEMA ' || sch || ' TO readonlyuser_role'; raise notice

Re: vacuum full

On Mon, 30 Aug 2021 at 23:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Just keeping it in a separate email, incase this is thrashed down. > vacuum full has a lot of problem stories, not just because the db gets > locked, but also because it is mostly (mis)used

vacuum full

Just keeping it in a separate email, incase this is thrashed down. vacuum full has a lot of problem stories, not just because the db gets locked, but also because it is mostly (mis)used when there are space issues. of course, there are strong warnings in docs and wiki about using a vacuum full, bu

Re: Pg stuck at 100% cpu, for multiple days

On Mon, 30 Aug 2021 at 19:15, hubert depesz lubaczewski wrote: > Hi, > We hit a problem with Pg 12.6 (I know, we should upgrade, but that will > take long time to prepare). > > > The other end of the connection was something in kubernetes, and it no > longer exists. > > related? i was kind of exp

Re: user creation time for audit

On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud wrote: > > The easy way around that is to track those events yourself with the > rules that suit your needs, which can be done easily using an event > trigger. > Please correct me if I am missing anything, but the doc said, event triggers are not allo

Re: PostgreSQL Automatic Failover Windows Server

> > > I want to know in detail about PostgreSQL Automatic Failover solutions > without third party tools. > > There is no automatic failover solution provided with core postgresql. (maybe EDB provides in windows but ...) there are third party OSS solutions, dhamaniasad/awesome-postgres: A curated

Re: Single mater replica setup for an existing DB

On Mon, 16 Aug 2021 at 00:47, Digimer wrote: > Hi all, > > Could I get recommendations on how to convert an existing database to a > single-master replica setup? > > I'm running on RHEL 8 (pgsql version 10.17). I'd like to replicate the > existing database to 1 (maybe 2) other hosts, synchron

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 21:07, Michael Lewis wrote: > On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> ... use binary split for large partitions, to avoid large row movements. >> > > Would you expound on this? >

Re: Multi-master replication

On Fri, Aug 13, 2021, 5:05 PM Zahir Lalani wrote: > Confidential > > Thx Laura > > So here is the dilemma - everything in the cloud world tends toward > horizontal scaling. We do that with PG using single master and multiple > slaves. But we are write heavy and of course the load on the master is

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 14:13, Pól Ua Laoínecháin wrote: > Thanks again for your questions - they gave me pause for thought and I > will try to apply them in future partitioning scenarios. (Unfortunatly > :-) ) there is no magic number of partitions for, say, a given size of > table - otherwise it

Re: (13.1) pg_basebackups ./. pg_verifybackup

On Thu, 12 Aug 2021 at 01:48, Matthias Apitz wrote: > This is exactly the point of my question (and I figured it out too): > Where is this explained that «pg_wal.tar.gz file has to uncompressed in > pg_wal dir»? > > indeed, I am not able to find or search relative reference in docs (i never used

Re: (13.1) pg_basebackups ./. pg_verifybackup

btw, you can also use pgbackrest for backups. i'll let docs do the talking, pgBackRest - Reliable PostgreSQL Backup & Restore but it can help you manage incremental and differential and full backups along with parallel support. (so fast and less bandwidth) also a quick on

Re: (13.1) pg_basebackups ./. pg_verifybackup

> > > Could some kind sol acknowledge me that this is the correct procedure to > use pg_verifybackup? Thanks in advance > > postgres@db:~/playground/demo$ initdb -D db 2>/dev/null 1>&2 postgres@db:~/playground/demo$ pg_ctl -D db -l logfile start 2>/dev/null 1>&2 postgres@db:~/playground/demo$ psql

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 19:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > >> ok my guess here was, since pg_class is updated every now and then with > stats, it might require some lock while adding the data. > so if it were bloated, that would block t

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 18:59, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote: > > Just taking a shot, as I have seen in some previous issues? Ignore is > not > > relevant. > > > > Can you run vacuum on pg_class a

Re: Getting pg_stat_database data takes significant time

Just taking a shot, as I have seen in some previous issues? Ignore is not relevant. Can you run vacuum on pg_class and check the query again , or do you see pg_class bloated ? The other option would be gdb backtrace I think that would help.

Re: Partitioning a table by integer value (preferably in place)

> > > > I have a 400GB joining table (one SMALLINT and the other INTEGER - > Primary Keys on other tables) with 1000 fields on one side and 10M on > the other, so 10,000M (or 10Bn) records all told. > My queries: > > Do you have any explain analyze,buffers results with the existing setup? Does

Re: PostgreSQL general set of Questions.

On Mon, 9 Aug 2021 at 12:14, A Z wrote: > 1) Are there free scripts for CREATE TYPE (native type), more advanced, > or sorts of types out there, online, free for commercial > use? With function support, too? Can someone reply with a link or a > suggestion? > > PostgreSQL: Documentation: 13: Cha

Re: autovacuum worker started without a worker entry

I am attempting to dive into code using english, not c, if i am misguiding, pls ignore. On Thu, 5 Aug 2021 at 11:38, Luca Ferrari wrote: > Hi all, > I occasionally see the message "WARNING: autovacuum worker started > without a worker entry" in the logs. > From what I can see here > < > https:/

Re: Unexpected block ID found when reading data

On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar wrote: > I’m not sure which patch version i used to dump, but i was using postgre > 12.5 for pg_dump back then. > > I’m running pg_restore -f dbdump.backup right now, I think it will take > some times because it has a large size (around 9 GB). There are

Re: Unexpected block ID found when reading data

On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar wrote: > Hi, Adrian > > Thanks in advance. > > pdgump command: > pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup > > I'm not sure which pg_dump version did i use before, but I used psql 12.5 > to dump and the db version is postgresql 9.6. > > pgrestor

Re: Lazy View's Column Computing

On Mon, 2 Aug 2021 at 19:53, Tom Lane wrote: > Avi Weinberg writes: > > Is there a way to compute a column in a view only if it is referenced in > the query? I have a view's column that its value is computed by a > function. If in the query that column is not used at all, can Postgres > "skip"

Re: postgres vacuum memory limits

https://rhaas.blogspot.com/2012/01/linux-memory-reporting.html?m=1 I think this awesome blog will clear a lot of 'understanding of top' output in postgresql context of memory growth.

Re: postgres vacuum memory limits

On Sun, 1 Aug 2021 at 20:04, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Sun, 1 Aug 2021 at 10:27, Ayub M wrote: > > > > Hello, when maintenance_work_mem and autovacuum_work_mem are set, my > understanding is that the vacuum and autovacuum sessions

Re: postgres vacuum memory limits

On Sun, 1 Aug 2021 at 10:27, Ayub M wrote: > > Hello, when maintenance_work_mem and autovacuum_work_mem are set, my understanding is that the vacuum and autovacuum sessions should be limited to use the memory limits set by these parameters. But I am seeing more memory being used than these limits

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Sat, Jul 31, 2021, 4:00 AM Dhanush D wrote: > I am currently upgrading a Postgres cluster running on a Centos machine > from version 9.6.22 to 12.7. Post the install of 12.7 binaries, I am > running the pg_upgrade command: > > /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-1

Re: DO like block for for anonymous procedures

please ignore, i overlooked the obvious. truncate table t; TRUNCATE TABLE postgres=# do $$ declare valuelist int[] := ARRAY[1,2,3,4,5,1]; -- purposely inserting duplicate that would rollback everything declare i int; begin for i in select k from unnest(valuelist) p(k) loop insert into t values(i);

DO like block for for anonymous procedures

Hi, PostgreSQL: Documentation: 13: DO Is it possible to run a DO block for multiple transactions ? I am not sure if i'll be able explain it more verbally, but -- the entire DO block like a function block is a single tx postgres=# do $$ declare

Re: I need another option.

On Wed, Jul 28, 2021, 11:10 AM obi reddy wrote: > Hello Everyone . "c --clean (drop) database objects before recreating", > is there any other option for this. please let me know > You'll have to give more information on what you see and what you want or does not work the way you would want. On

Re: pg-audit extension

On Mon, Jul 26, 2021, 11:54 PM Olagoke Akinyemi wrote: > Hello, > > > Could someone please, give me a quick guide? I am trying to install > pgaudit extension on an existing PostgreSQL instance but I want to place it > on a different this mount. How can i do this? > > Are you trying to save pgaudi

Re: pg_restore (fromuser -> touser)

On Sun, 25 Jul 2021 at 21:09, Mayan wrote: > Thanks for your reply. Is this something that I can request as a feature > add? I don't think it should be too much of effort (based on my limited > source code knowledge), but I'm not familiar with the process to request a > feature. > although there

Re: regarding sql password auth

> I have just downloaded the postgresql .In the SQLshell i am not able to go > ahead as password is not authenticated . > I am not too well versed with windows installer, but I know during installation it asks for admin username and password. The same cred should work with initial login. https://

Re: Obsolete or dead serverconnections after reboot

On Thu, 22 Jul 2021 at 12:41, WR wrote: > Hello Vijaykumar Jain, > > at first: select pg_stat_reset(); doesn't help, the pg_stat_activity is > the same after it. > one thing, i forgot to mention. After a pg_stat_reset(), I would run, *vacuum analyze* on the dbs, so that sta

Re: Obsolete or dead serverconnections after reboot

etc. That would also mean, all table stats would be off or not updated too? Is that so? Or analyse works fine on tables without a restart? On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > >> Is there a way to avoid this (without r

Re: Obsolete or dead serverconnections after reboot

> Is there a way to avoid this (without restarting the service after every > reboot). Is this a bug or a normal behavior? > I have less knowledge of windows. https://www.enterprisedb.com/blog/postgresql-shutdown Do you see shutdown/termination messages in the db logs or windows event logs when

Re: How to debug a connection that's "active" but hanging?

On Mon, 12 Jul 2021 at 23:16, Tom Lane wrote: > > The backtraces you captured look like the query is not "hung", it's > just computing away. > > He mentioned earlier that the query was hung as 'active' for 8 hours and on. incase this is due to bad plan, @Jurrie Overgoor is it also possible for

Re: How to debug a connection that's "active" but hanging?

On Sat, 10 Jul 2021 at 00:29, Jurrie Overgoor < postgresql-mailingl...@jurr.org> wrote: > Hi everyone, > > We are in the process of upgrading from PostgreSQL 9.6 to 13. When our > database gets created in our regression tests, we run some unit tests > first. We see one of those tests hang. > > It

Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

On Mon, 5 Jul 2021 at 14:29, Avi Weinberg wrote: > Thanks for the reply, > > > > My question was, what will happen if I have one destination table which > gets data from many source tables. What is the best way to handle changes > in the structure of SOME of the source tables, while other source

Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

On Sun, 4 Jul 2021 at 15:53, Avi Weinberg wrote: > I'm using logical replication to copy data from multiple tables to a > single destination table. At times the structure of the source table needs > to change. However, not all source table will have their structure updated > at the same time.

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Thu, Jun 24, 2021, 9:28 AM Mike Yeap wrote: > Hi Vijay, thanks for the tests, it's very helpful. > > Just that, isn't it too extreme when one of the processes having problems > accessing a snap file, it causes all other processes to be terminated? > After all, most of the other processes do no

Re: Is there something similar like flashback query from Oracle planned for PostgreSQL

On Thu, 24 Jun 2021 at 00:24, Dirk Krautschick < dirk.krautsch...@trivadis.com> wrote: > Hi, > Is there something planned to get a behaviour like Oracle's flashback > query based on the old values > before deleted by vacuum? > > So a feature to recreate old versions of rows if still there? > > Or

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Tue, 22 Jun 2021 at 14:34, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 22 Jun 2021 at 13:32, Mike Yeap wrote: > >> Hi all, >> >> I have a Postgres version 11.11 configured with both physical replication >> slots (for repmgr) as

Re: www.postgresql-archive.org content deleted

i get it now, thanks., anyways, it does seem to be under serious attack.

Re: www.postgresql-archive.org content deleted

On Tue, 22 Jun 2021 at 23:31, Magnus Hagander wrote: This site is not affiliated with the PostgreSQL project in any way, > it's an independent third party. > > The official PostgreSQL archives are on https://www.postgresql.org/list/ > > coola, many thanks. that's good to hear.

www.postgresql-archive.org content deleted

Ok, i am not sure if this is the right place to post this, I am seeing a lot of messages from archives, shown as deleted. PostgreSQL - performance - Estimating wal_keep_size | Threaded View (postgresql-archive.org) if i

Re: second CTE kills perf

On Tue, 22 Jun 2021 at 13:50, Nicolas Seinlet wrote: > Hello, > > oversimplified example: > 10 seconds version: > | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z > FROM cte1 WHERE x=32; > > 10 minutes version: > | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT r

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Tue, 22 Jun 2021 at 13:32, Mike Yeap wrote: > Hi all, > > I have a Postgres version 11.11 configured with both physical replication > slots (for repmgr) as well as some logical replication slots (for AWS > Database Migration Service (DMS)). This morning, the server went panic with > the follow

  1   2   3   >