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

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 ?

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

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

irst-contact-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

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

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

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

Re: Multi master disjoint cluster

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

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

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,

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

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, >>> >>&g

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

Re: Could not read block 0 in file

cenarios, 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

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

ut 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

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

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

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

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

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

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

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

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

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. >&g

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 somet

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

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,

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

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

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,

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

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

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

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 blo

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_cl

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?

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:

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

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

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

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 >

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

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

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

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.

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

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

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.

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 stats ar

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

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

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

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

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

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

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

> > Hm. It's intentional that we reconnect after applying the database > properties, so that they are in effect during the restore. It seems > likely that failing to do so could result in misbehaviors. > > Hence, the only way to make this scenario work would be for the > restore script to

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:49, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert > wrote: > >> Dear all, >> >> I am testing the pg_restore of a database with >> default_transaction_read_on

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert wrote: > Dear all, > > I am testing the pg_restore of a database with > default_transaction_read_only=on. > > It would seem the restore script lacks a > > SET default_transaction_read_only TO 'off'; > > in the setup section after

Re: pgTAP installation

> > > This was a wrong suggestion. sorry. > i'll try to reproduce what resulted in functions not getting loaded in > your case. > > ok i guess you have a typo there. the function name is *no_plan()*, but you called *noplan() * tt=# select * from noplan(); ERROR: function noplan() does not exist

Re: pgTAP installation

test psql (14beta1) Type "help" for help. test=# create extension pgtap; CREATE EXTENSION test=# SELECT * FROM no_plan(); no_plan - (0 rows) On Sun, 20 Jun 2021 at 18:36, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > I think you need to load

Re: pgTAP installation

On Sun, 20 Jun 2021 at 17:18, Ray O'Donnell wrote: > > > gfc_booking6_dev=# select * from noplan(); > ERROR: function noplan() does not exist > LINE 1: select * from noplan(); >^ > HINT: No function matches the given name and argument types. You might > need to add

Re: compute_query_id

On Thu, 17 Jun 2021 at 20:20, Julien Rouhaud wrote: > > On Thu, Jun 17, 2021 at 08:09:54PM +0530, Vijaykumar Jain wrote: > > how is the compute_query_id actually calculated? > > > why does it show 0 in logs for random sql queries. > > log_line_prefix = '%Q :' > >

compute_query_id

hi, I noticed this new param compute_query_id in pg14beta. it is interesting as I was long wanting to identify a query with a unique id like we have for http requests etc so that we can trace the query all the way to shards via FDW etc. but i cannot see them in the logs even after setting

Re: Streaming replication: PANIC on tertiary when secondary promoted

What is your recovery_target_timeline set to on replicas ? I just did a primary -> replica -> cascading replica setup. and then promoted replica as new primary. cascading replica was working fine, no restarts required. for me recovery_target_timeline was set to 'latest' i have pg14beta

Re: clear cache in postgresql

On Wed, 16 Jun 2021 at 12:09, Julien Rouhaud wrote: > > On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote: > > > > Sometimes I run a Postgres query it takes 30 seconds. Then, I > > immediately run the same query and it takes 2 seconds. It appears that > > Postgres has some sort of

Re: immutable function querying table for partitioning

On Tue, 15 Jun 2021 at 18:21, David G. Johnston wrote: > You probably avoid the complications by doing the above, but the amount of bloat you are causing seems excessive. > > I’d suggest an approach where you use the table data to build DDL in a form that does adhere to the limitations described

immutable function querying table for partitioning

hi, I was playing around with a setup of having a lookup table for partitioning. Basically, I wanted to be able to rebalance partitions based on my lookup table. -- create a lookup and assign shard nos to ids test=# create table pt(id int, sh int); CREATE TABLE test=# insert into pt select x, 1

Re: How to generate file from postgres data

correction, i kind of recollected this as my own problem of a client wanting 100k rows worth data downloadable. we finally decided with csv dump and upload to object store, from where the client would download the file. all other options like web ui with pagination etc were resulting in memory

Re: How to generate file from postgres data

test=# create table t(id int, value text); CREATE TABLE test=# insert into t select x, x::text from generate_series(1, 1000) x; INSERT 0 1000 test=# COPY (select * from t where id < 50 order by id desc) TO '/tmp/report.csv' DELIMITER ',' CSV HEADER; copy out the results via a view or sql

Re: Implicit table removal from logical replication publication

pg_subscription_rel pg_publication_rel have the relation part of the publication concerned. OP has an issue to figure out if publication has a list of tables not in sync in subscription and has subscription broken. there may be ways to query tables on subscriber dbs via dblink or fdw, but there

Re: Implicit table removal from logical replication publication

Wow, the drop table silently removes entry from publication without any logs. I could not find any monitoring view to help me figure out if the publication is broken due to ddl change. pg_stat_replication on publisher, and pg_stat_subscription on subscriber only help with lsn based lag. unless

Re: order by

> Any hint? you can run an explain analyze to check what is going on, when you provide a table in query in the order by clause, it is ordered by cols of that table in that order. create table t(id int, value int); postgres=# explain (analyze,verbose) select * from t order by t;

Re: PostgreSQL replication lag - Suggestions and questions

> My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication. I am not sure of the difference between read replica and a hot standby. At least with later versions hot standby allows read queries. I mean unless you do not want

Re: JDBC error: Unexpected packet type: 25

" java.io.IOException: Unexpected packet type: 25" char 25 is EM. ( End of Medium) I do not see it being part of the wire protocol, as for the same reason, not in pgjdbc handling i think. PostgreSQL: Documentation: 13: 52.7. Message Formats

bottom / top posting

I hear a lot of suggestions wrt bottom / top posting. only when i read this, PostgreSQL - general - Top posting | Threaded View (postgresql-archive.org) I got a feeling it sounded rude to the top post, despite me not even

Re: Symbolic link breaks for postgresql.auto.conf

Yes, I learnt it the hard way as well :) I made changes via the alter system, and did a pg_restore, and the changes were lost :) use the alter system for ad hoc changes, but make sure those changes are added back to the postgresql.conf file immediately (or however the main conf file is managed).

Re: Ideas for building a system that parses medical research publications/articles

http://tika.apache.org/ To get started with collecting doc metadata. It looks this tool can help you started. postgres does support fuzzy text search, so I do think dumping meta data /abstract in postgresql and then using trigram tsearch etc like extensions it should work well for a POC. this

  1   2   >