Re: I have ansible for postgres-etcd-patroni

[Vijaykumar Jain]

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 patroni replication
> and in two of them it install haproxy and keepalived
> finally it became so stable idea for database with a SQL and also etcd
> NOSQL
>
> with this single command
> ansible-playbook -i inventory/db-servers.ini postgres.yml --become
> --become-method=sudo --tags "preinstall,postgres,haproxy" --ask-become-pass
>
> i just want to share this with others and your community
> and also i am living in Iran right now and eager to accept job offer for
> emigration or any job recommendations
> Here is my resume
> https://gitea.com/imanbakhtiari/resume
> Thank you
>

Re: Backup_Long Running

[Vijaykumar Jain]

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 ?
generally faster hardware i.e striped and or nvme disks along with a robust
network link and capacity should help get the backup done quickly.
where are you taking the backup from? is the server busy doing other work
or it is a dedicated machine for backups ?
basically monitor for resource saturation, if all looks good, we could take
basebackup of a 10tb db in 8 hours, and in another case on a slow remote
storage, backup of 2tb took 1 day.

now, pgbackrest can speedup backup processes by spawning more workers for
archiving and stuff. we have taken backup on nvme disks striped of 28tb in
3 hours, bare metals servers with powerful cpu.

so , it's hardware  else switch to pgbackrest which can take
incremental/differential/full backups.
there are other tools too, I used only these two.

>
>

Re: constant crashing

[Vijaykumar Jain]

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 connection expectantly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> error: connection to server was lost
>>
>> PostgreSQL 16.2
>>
>> I also believe it is a resource issue which can be rectified with a
>> setting, but which setting?
>> If you were updating 100 million records what settings would you adjust?
>>
>> Here are the updates I am performing on the 100 million records:
>> UPDATE table SET category_modified = UPPER(category);
>> UPDATE table SET category_modified =
>> REGEXP_REPLACE(REPLACE(REPLACE(category_modified, ,'-'), '`', '-'),
>> '\s{2,}', ' ', 'g') WHERE  AND LENGTH(category_modified)>1 AND
>> POSITION('--' IN category_modified)>0;
>> UPDATE table SET category_modified = REPLACE(category_modified,' ','-');
>> UPDATE table SET category_modified = CASE WHEN category_modified IS NOT
>> NULL THEN regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE NULL END;
>> UPDATE table SET category_modified = regexp_replace(category_modified,
>> '-{2,}', '-', 'g');
>> UPDATE table SET category_modified = SUBSTRING(category_modified FROM 1
>> FOR LENGTH(category_modified) - 1) WHERE LENGTH(category_modified)>1 AND
>> category_modified LIKE '%-';
>>
>>
> independent of best practices, i just want to check if there is a leak.
> I created a sample table with text data and ran updates like yours and I
> could not see mem growth, but I have a small vm and ofc your
> category_modified field might be more complex than simple text fields for
> 30-40 chars.
>
> can you grab the pid of your psql backend and (if you have pidstat
> installed) monitor resource usage for that pid
>
> postgres@pg:~/udemy/16$ psql
> psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
> Type "help" for help.
>
> postgres=# select pg_backend_pid();
>   pg_backend_pid
> 
>1214
> (1 row)
>
> # pidstat 2 100 -rud -h -p 1214
> (get all stats for that pid) that might help to figure out if there is a
> leak or the server has other things competing for memory and your updates
> were picked by the killer.
>
> Linux 5.15.0-101-generic (pg)   04/15/24_x86_64_(1 CPU)
>
> # TimeUID   PID%usr %system  %guest   %wait%CPU   CPU
> minflt/s  majflt/s VSZ RSS   %MEM   kB_rd/s   kB_wr/s kB_ccwr/s
> iodelay  Command
> 00:40:25  113  12140.000.000.000.000.00 0
> 0.00  0.00  354112  220940  24.18  0.00  0.00  0.00
>0  postgres
>
> # TimeUID   PID%usr %system  %guest   %wait%CPU   CPU
> minflt/s  majflt/s VSZ RSS   %MEM   kB_rd/s   kB_wr/s kB_ccwr/s
> iodelay  Command
> 00:40:27  113  12140.000.000.000.000.00 0
> 0.00  0.00  354112  220940  24.18  0.00  0.00  0.00
>0  postgres
> 
>
> ofc, if there is a genuine leak , then there might be more digging needed 
> Finding
> memory leaks in Postgres C code (enterprisedb.com)
> <https://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

[Vijaykumar Jain]

On Sun, 14 Apr 2024 at 21:50, jack  wrote:

> The full error reads:
> server closed the connection expectantly
> This probably means the server terminated abnormally
> before or while processing the request.
> error: connection to server was lost
>
> PostgreSQL 16.2
>
> I also believe it is a resource issue which can be rectified with a
> setting, but which setting?
> If you were updating 100 million records what settings would you adjust?
>
> Here are the updates I am performing on the 100 million records:
> UPDATE table SET category_modified = UPPER(category);
> UPDATE table SET category_modified =
> REGEXP_REPLACE(REPLACE(REPLACE(category_modified, ,'-'), '`', '-'),
> '\s{2,}', ' ', 'g') WHERE  AND LENGTH(category_modified)>1 AND
> POSITION('--' IN category_modified)>0;
> UPDATE table SET category_modified = REPLACE(category_modified,' ','-');
> UPDATE table SET category_modified = CASE WHEN category_modified IS NOT
> NULL THEN regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE NULL END;
> UPDATE table SET category_modified = regexp_replace(category_modified,
> '-{2,}', '-', 'g');
> UPDATE table SET category_modified = SUBSTRING(category_modified FROM 1
> FOR LENGTH(category_modified) - 1) WHERE LENGTH(category_modified)>1 AND
> category_modified LIKE '%-';
>
>
independent of best practices, i just want to check if there is a leak.
I created a sample table with text data and ran updates like yours and I
could not see mem growth, but I have a small vm and ofc your
category_modified field might be more complex than simple text fields for
30-40 chars.

can you grab the pid of your psql backend and (if you have pidstat
installed) monitor resource usage for that pid

postgres@pg:~/udemy/16$ psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# select pg_backend_pid();
  pg_backend_pid

   1214
(1 row)

# pidstat 2 100 -rud -h -p 1214
(get all stats for that pid) that might help to figure out if there is a
leak or the server has other things competing for memory and your updates
were picked by the killer.

Linux 5.15.0-101-generic (pg)   04/15/24_x86_64_(1 CPU)

# TimeUID   PID%usr %system  %guest   %wait%CPU   CPU
minflt/s  majflt/s VSZ RSS   %MEM   kB_rd/s   kB_wr/s kB_ccwr/s
iodelay  Command
00:40:25  113  12140.000.000.000.000.00 0
0.00  0.00  354112  220940  24.18  0.00  0.00  0.00
   0  postgres

# TimeUID   PID%usr %system  %guest   %wait%CPU   CPU
minflt/s  majflt/s VSZ RSS   %MEM   kB_rd/s   kB_wr/s kB_ccwr/s
iodelay  Command
00:40:27  113  12140.000.000.000.000.00 0
0.00  0.00  354112  220940  24.18  0.00  0.00  0.00
   0  postgres


ofc, if there is a genuine leak , then there might be more digging
needed Finding
memory leaks in Postgres C code (enterprisedb.com)
<https://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

[Vijaykumar Jain]

On Fri, 22 Mar 2024 at 15:39, Laurenz Albe  wrote:

> On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote:
> > We are unable to take the backup of our database. While taking backup we
> are getting the same error.
> >
> > psql: error: connection to server at "localhost" (::1), port 5014
> failed: FATAL:  pg_attribute catalog is missing 1 attribute(s) for relation
> OID 2662
>
> Then you got severe data corruption.  This is the index
> "pg_class_oid_index",
> and corrupted metadata make recovery difficult.
>
> If I were you, I would seek professional help.
> But first, stop working with this database immediately.
> Stop the server and take a backup of all the files in the data
> directory.


Do we have an option that op has a replica running and the bug has not
propagated to the replica , and the op can failover/ take a backup off the
replica.
i mean i could simulate a corruption of pg_catalog on my local vm using dd,
but ofc that is hardware level corruption that did not propagate to the
replica, so i could failover and backup from the replica just fine.
PS : if the bug propagates to the replica or does corruption on the replica
too, then idk the solution. if you could login and get the oid of the
objects (and have field types ready externally), then you can run a
pg_filedump and copy the data.
First contact with the pg_filedump - Highgo Software Inc.
<https://www.highgo.ca/2021/07/14/first-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 understand this, then ignore)


> Vijay
>
LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

Re: postgres large database backup

[Vijaykumar Jain]

>  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 different when snapshot is taken on
live systems which generate a lot of delta.

>

Re: postgres large database backup

[Vijaykumar Jain]

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 am not sure that such statement holds water. When a snapshot is taken,
> the amount of necessary I/O requests goes up dramatically. For every block
> that snapshot points to, it is necessary to read the block, write it to the
> spare location and then overwrite it, if you want to write to a block
> pointed by snapshot. That gives 3 I/O requests for every block written.
> NetApp is trying to optimize it by using 64MB blocks, but ZFS on Linux
> cannot do that, they have to use standard CoW because they don't have the
> benefit of their own hardware and OS. And the standard CoW is tripling the
> number of I/O requests for every write to the blocks pointed to by the
> snapshot, for every snapshot. CoW is a very expensive animal, with horns.
>

I am not an expert in this area, but we have zfs for specific instances
which have timeseries/event log data, and we also need compression.
One day, there was a need to snapshot a 35tb zfs pool and send it across
the network to a relplica, coz both the disks in the mirror degraded around
same time, I do not recall zfs snapshots took anything resource intensive,
and it was quick.ill ask around for actual time though.

We have more than 500 of these type of nodes with zfs (each having 20 disks
in mirror each 8tb) for event log with compression, and zfs works just
fine. This is a special setup where the data is assumed to be cold storage,
hence compression, so it was designed for heavy writes and occasional reads
queries only for debugging.

>

Re: postgres large database backup

[Vijaykumar Jain]

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 how I should do it in a quicker backup for my 10TB
> database ? Is there any tool to take backups and subsequently incremental
> backups in a faster way and restore it for PITR in a faster way when
> required.
>
> What should be the exact approach for scheduling such backups so that it
> can be restored in a faster way ?
>


We had a 96 cpu, 385gb ram, nvme storage and 10g network baremetal server.
We used pgbackrest for full backup.
It supports pitr and differential backup.

28tb db took 2.5 hours for backup on remote storage, and restore from the
remote storage  took 3.5 hours when immediately restored (a lot of time is
later due to wal replay to catch up)

pg_basebackup took 9 hours.

Re: Multi master disjoint cluster

[Vijaykumar Jain]

Actually, pls ignore my email.

re reading my mail makes it look like I did not research it throughly and
just asked without actual implementation of both options and having a clear
goal on what can incompromise along with no read downtime.
I'll write better next time.

On Wed, Oct 26, 2022, 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
> setup has the ability to make use of kafka consumer groups (like two
> replication slots each having their own lsn offsets)  to write to both db
> node pairs in parallel via the application layer.
>
> The churn of data is really high, there is a lot of wals generated, around
> 500gb/hr.
>
> If I go with primary/replica, (lr not feasible)
>
> I need to ensure both are on the same major version. Upgrades are tricky
> (we don't have qa) so we just have option to schema dump for upgrade
> compatibility. Data, we trust postgresql for that :). (I wish we had zfs
> everywhere but no )
>
> Any excl table blocking operations, (although with later versions there
> are very less blocking operations) can impact queries on replica as well
> (excluding delay settings).
>
> Corruption can cause downtime (we have tons of them and raids to protect
> them) so if replica is having issues, we can zero the pages on the replica
> and do some operations if we isolate the problem pages, else resync the
> replica from primary. But if primary is having some issues, we copy data
> from replica to disk and copy in to primary after truncating etc. Some
> downtime but not a lot. (I am not expert at data recovery) and mostly rely
> on amcheck, dd, and raid checks.
>
> We don't use pitr (too many wals × 58) or delayed replication as we can't
> afford more servers.
>
> ddl deploys are guaranteed by replication. So no need to try 2pc like
> stuff at app layer. (Although apps use deploy tools to ensure eventually
> the ddls are consistent and idempotent)
>
> Basically primary/replica relieves the app to think what is there on the
> primary is also on the replica eventually, so there can be source of truth.
>
> But with multi writers, any app mishandling like bug in catching exception
> etc can result in diversion and no more mirrored setup.
> We need to have checks/reconciliation to ensure both write nodes in pair
> have almost similar data at the end of the day so we can trust this setup
> independent of any app mistakes.
>
> But if app layer gets robust, we have almost no downtime in reads and
> writes, we can have both nodes on different versions, (w/o logical
> replication) can query both nodes real time, no real replication lag issues
> , conflicts etc, can upgrade like blue green, canary test some changes on
> one if needed etc.
>
> Am I making sense at all? Or I am sounding confused, and I don't know the
> difference between primary/replica vs multi writer. This is not bdr like
> thing, they don't really need each other unless we are into some recovery.
>
> My point is, we have 58 such primary/replica shards (each 10tb+)
> (consistent hashing at app layer, no fdw)  and there is no scope of
> downtime for reads, so any issue like post upgrade performance degradation
> (if any) gives me chills. and we have no qa to test real data.
>
> There are too many dimensions to shard on and aggregations need to run
> across the shards (Yes there is no scope of data isolation).
>
>

Multi master disjoint cluster

[Vijaykumar Jain]

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 lsn offsets)  to write to both db
node pairs in parallel via the application layer.

The churn of data is really high, there is a lot of wals generated, around
500gb/hr.

If I go with primary/replica, (lr not feasible)

I need to ensure both are on the same major version. Upgrades are tricky
(we don't have qa) so we just have option to schema dump for upgrade
compatibility. Data, we trust postgresql for that :). (I wish we had zfs
everywhere but no )

Any excl table blocking operations, (although with later versions there are
very less blocking operations) can impact queries on replica as well
(excluding delay settings).

Corruption can cause downtime (we have tons of them and raids to protect
them) so if replica is having issues, we can zero the pages on the replica
and do some operations if we isolate the problem pages, else resync the
replica from primary. But if primary is having some issues, we copy data
from replica to disk and copy in to primary after truncating etc. Some
downtime but not a lot. (I am not expert at data recovery) and mostly rely
on amcheck, dd, and raid checks.

We don't use pitr (too many wals × 58) or delayed replication as we can't
afford more servers.

ddl deploys are guaranteed by replication. So no need to try 2pc like stuff
at app layer. (Although apps use deploy tools to ensure eventually the ddls
are consistent and idempotent)

Basically primary/replica relieves the app to think what is there on the
primary is also on the replica eventually, so there can be source of truth.

But with multi writers, any app mishandling like bug in catching exception
etc can result in diversion and no more mirrored setup.
We need to have checks/reconciliation to ensure both write nodes in pair
have almost similar data at the end of the day so we can trust this setup
independent of any app mistakes.

But if app layer gets robust, we have almost no downtime in reads and
writes, we can have both nodes on different versions, (w/o logical
replication) can query both nodes real time, no real replication lag issues
, conflicts etc, can upgrade like blue green, canary test some changes on
one if needed etc.

Am I making sense at all? Or I am sounding confused, and I don't know the
difference between primary/replica vs multi writer. This is not bdr like
thing, they don't really need each other unless we are into some recovery.

My point is, we have 58 such primary/replica shards (each 10tb+)
(consistent hashing at app layer, no fdw)  and there is no scope of
downtime for reads, so any issue like post upgrade performance degradation
(if any) gives me chills. and we have no qa to test real data.

There are too many dimensions to shard on and aggregations need to run
across the shards (Yes there is no scope of data isolation).

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

[Vijaykumar Jain]

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, 2022, 1:03 PM Ivan N. Ivanov 
wrote:

> Thank you, people. The big problem in my case, which I have not mentioned,
> is that I think the network is a bottleneck, because I am running
> pg_basebackup through internet from local country to Amazon instance in
> Germany and the speed in copying is around 50 MB/sec max, that is why it
> takes 2 days for copying.
>
> I will try using high compression for the basebackup to reduce the time.
>
> pgbackrest is an alternative, too
>
> Thank you again!
>
>
>
> On Wed, Aug 17, 2022 at 11:13 PM Ivan N. Ivanov 
> wrote:
>
>> Thank you for your answer! I have found this tool and I will try it
>> tomorrow to see if this "read-ahead" feature will speed up the process.
>>
>> On Wed, Aug 17, 2022 at 11:09 PM Christophe Pettus 
>> wrote:
>>
>>>
>>>
>>> > On Aug 17, 2022, at 13:06, Ivan N. Ivanov 
>>> wrote:
>>> >
>>> > How to speed up recovering of WAL files?
>>>
>>> Since you are running on your own hardware, you might take a look at:
>>>
>>> https://github.com/TritonDataCenter/pg_prefaulter
>>>
>>

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

[Vijaykumar Jain]

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/configuration.html#section-archive/option-archive-get-queue-max
to speed up pulls too.
Everything is on prem, no cloud FYI and gentoo.



On Thu, Aug 18, 2022, 11:23 AM Ron  wrote:

> pg_backrest will certainly backup your data faster. It might be able to be
> used as a seed instead of pg_basebackup.
>
> On 8/17/22 15:06, Ivan N. Ivanov wrote:
> > I have a large database (~25 TB) and I want to set up streaming
> > replication for the first time.
> >
> > My problem is that after completion of the pg_basebackup (which
> completed
> > for 2 days with --wal-method=none) now PG is replaying the WAL files
> from
> > the WAL archive directory but it can not keep up. The replaying of WAL
> > files is the same as the physical time, for example:
> >
> > 2022-08-17 22:42:57 EEST [13507-6] [] DETAIL:  Last completed
> transaction
> > was at log time 2022-08-15 18:24:02.155289+03.
> > 2022-08-17 22:48:35 EEST [13507-12] [] DETAIL:  Last completed
> transaction
> > was at log time 2022-08-15 18:29:54.962822+03.
> > 2022-08-17 22:54:35 EEST [13507-16] [] DETAIL:  Last completed
> transaction
> > was at log time 2022-08-15 18:34:20.099468+03.
> >
> > From ~22:43 to ~22:48 there are 5 minutes. And completed transactions
> are
> > at ~18:24 and ~18:29 (5 minutes).
> >
> > I have even put all WAL files from the archive directly in the pg_wal
> > directory of the replica and now PostgreSQL skips the cp command from
> > restore_command, i.e. I have removed the restore_command and now the WAL
> > files are only recovering, this is the only operation, but it is slow:
> >
> > postgres: startup   recovering 00010003FC790013
> > postgres: startup   recovering 00010003FC790014
> > postgres: startup   recovering 00010003FC790015
> > ...
> >
> > And it cannot keep up and my replication cannot start since it is 2 days
> > behind the master... The replica has the same SSD disks as the master.
> >
> > Is there a better way to do this? How to speed up recovering of WAL
> files?
> > I have increased shared_buffers as much as I can... Is there something
> > that I miss from the recovery process?
> >
> > I do not have problems setting up replications for the first time for
> > small database (10 GB - 100 GB), but for 25 TB I can not set the
> > replication, because of this lag.
> >
>
> --
> Angular momentum makes the world go 'round.
>
>
>

Re: Could not read block 0 in file

[Vijaykumar Jain]

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,
>>>
>>> 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 shutdown due to critical power failure
>>>
>>> Current Issue: The file base/16509/17869 is zero bytes in size.
>>>
>>> The error message when running this query is:
> ERROR:  could not read block 0 in file "base/16509/17869": read only 0 of
> 8192 bytes
>
> i guess it is maybe page header/ metadata not the whole file is zero
> bytes. the data can be recovered then from the blogs?
> i may be corrected :)
>
>
My bad, sorry did not read the email properly.

*The file does exist on the file system, with zero bytes, as do the
associated fsm and vm files.*

As Magnus suggests, then.

-- 
Thanks,
Vijay
LinkedIn - Vijaykumar Jain
<https://www.linkedin.com/in/vijaykumarjain/>

Re: Could not read block 0 in file

[Vijaykumar Jain]

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 shutdown due to critical power failure
>>
>> Current Issue: The file base/16509/17869 is zero bytes in size.
>>
>> The error message when running this query is:
ERROR:  could not read block 0 in file "base/16509/17869": read only 0 of
8192 bytes

i guess it is maybe page header/ metadata not the whole file is zero bytes.
the data can be recovered then from the blogs?
i may be corrected :)

Re: Could not read block 0 in file

[Vijaykumar Jain]

On Fri, 8 Apr 2022 at 14:36,  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 shutdown due to critical power failure
>
> Current Issue: The file base/16509/17869 is zero bytes in size.
>
> Additional Information:
> Platform: Windows Server
> PostGres Version: 10.16 (64-bit)
>
> The database does start, and is otherwise functioning and working aside
> from a particular application feature that relies on the lookup of the
> values in the table that was held in the currently zero-bytes data file.
>
> The non-functioning table (ApprovalStageDefinition) is a relatively simple
> table with 5 rows of static data. The contents can easily be recovered with
> a query such as the following for each of the 5 records:
> insert into ApprovalStageDefinition values (1, 'Stage One', 'Stage One');
>
> The error message when running this query is:
> ERROR:  could not read block 0 in file "base/16509/17869": read only 0 of
> 8192 bytes
>
> The file does exist on the file system, with zero bytes, as do the
> associated fsm and vm files.
>
> PostGres does allow us to describe the table:
>  \d ApprovalStageDefinition;
>Table "public.approvalstagedefinition"
>   Column   |  Type  | Collation | Nullable | Default
> ---++---+--+-
>  stageid   | bigint |   | not null |
>  stagename | citext |   | not null |
>  internalstagename | citext |   | not null |
> Indexes:
> "approvalstagedef_pk" PRIMARY KEY, btree (stageid)
> "approvalstagedefinition_uk1" UNIQUE CONSTRAINT, btree (stagename)
> "approvalstagedefinition_uk2" UNIQUE CONSTRAINT, btree
> (internalstagename)
> Check constraints:
> "approvalstagedefinition_internalstagename_c" CHECK
> (length(internalstagename::text) <= 100)
> "approvalstagedefinition_stagename_c" CHECK (length(stagename::text)
> <= 100)
> Referenced by:
> TABLE "approvaldetails" CONSTRAINT "approvaldetails_fk5" FOREIGN KEY
> (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
> TABLE "currentapprovalstage" CONSTRAINT "currentapprovalst_fk1"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
> TABLE "serviceapprovermapping" CONSTRAINT "serviceapprovermapping_fk4"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
>
> Desired Solution:
> A way to recreate the data file based on the existing schema so that we
> can then insert the required records.
>
> Challenges/Apprehensions:
> I am a PostGres novice, and reluctant to try dropping the table and
> recreating it based on the existing schema as I don’t know what else it may
> break, especially with regards to foreign keys and references.
>
> Any constructive advice would be appreciated.
>
> Thank you
>
>

in the order of steps

1) Corruption - PostgreSQL wiki
<https://wiki.postgresql.org/wiki/Corruption>
2) PostgreSQL: Documentation: 14: F.2. amcheck
<https://www.postgresql.org/docs/current/amcheck.html>
3) df7cb/pg_filedump: pg_filedump provides facilities for low-level
examination of PostgreSQL tables and indexes (github.com)
<https://github.com/df7cb/pg_filedump>
4) Physical recovery with pg_filedump (alexey-n-chernyshov.github.io)
<https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html>
(example usage)
5) Pgopen-Recovery_damaged_cluster(1).pdf (postgresql.org)
<https://wiki.postgresql.org/images/3/3f/Pgopen-Recovery_damaged_cluster%281%29.pdf>
 (using zero_damaged_pages  option to skip/zero error pages and move on)

Although I never really dealt with disk corruption, so i am not 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

[Vijaykumar Jain]

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 many orders of magnitude:
>
> Nested Loop  (cost=1.01..27584834.53 rows=59608439 width=164) (actual
> time=0.047..2.723 rows=854 loops=1)
>
> It completely ignores the "limit" in SQL statement when doing the row
> estimates - the estimates do not change when I change it. The business
> logic really needs only 1000 rows in the result, I never need the
> whole table in the result. The query always runs fast if the limit is
> <= 46, when the limit >=47, it sometimes chooses to do a merge join on
> 2 tables with hundreds of millions of rows  instead of using the
> indexes. The runtime difference is 4000x.
>
> I migrated the data to the latest postgres 14.1. Both versions run
> either the slow plan or the fast plan seemingly at random. The only
> reliable way to make it choose the fast plan is to decrease the
> effective_cache_size to a value 20x lower than the memory available in
> the system. Dropping and creating the statistics on the join table
> makes no difference in the estimated row counts and query plans.
> PostgreSql seems to be caching something internally and choosing the
> query plans at random - sometimes it is fast, sometimes 4000x slower
> without any changes in configuration or statistics. The runtime
> difference is 4000x.  Such randomness is clearly unacceptable. I think
> I would have to try to use the "hint" extension suggested by Imre to
> make it consistently choose the fast plan.
>

there was a similar concern raised on slack, where on shared plan (where
less effective_cache_size resulted in faster execution)

*"*
*Hey all, I have been diagnosing a slow running query and found that it
speeds up when the effective_cache_size is reduced.  It's set at ~21.5gb,
and lowering to 10gb or under massively improves the query.  Plans are here
 and here
.  You can see the difference is 33 -
42, where the order of execution results in a bunch more rows having to be
filtered out later in the first plan.  Everything I've read suggests that a
bigger effective_cache_size  should be better (assuming it's within the
recommended boundaries of total memory of course).  Can anyone offer any
insight into why this might happen, and what a good workaround would be?*
*"*

https://explain.depesz.com/s/VsaY
https://explain.depesz.com/s/nW3d

I am not sure, though effective_cache_size is the culprit. if there are bad
estimates, even a simple query like below produces a poor plan.
i did try to check the code postgres/costsize.c at master ·
postgres/postgres (github.com)

(I am not a developer but try to read the english from code) and tried to
understand how effective_cache_sizealters the cost and changes the path,
but could not simulate on my laptop with small tables and less resources.
but yeah, i know 1TB is a huge setup, but can run vaccumdb and analyze all
the tables in question (maybe reindex concurrently if required) and check
if the row stats are still off in the plan and reducing effective_cache_size
improves the execution time? I am not an expert, but just try to figure out
if this is a red herring.

i also play with some gucs as in PostgreSQL: Documentation: 13: 19.7. Query
Planning   to
check if enabling/disabling  some of gucs results in a big difference in
execution and then try to zero in what needs immediate vaccum or reindex or
join count etc or bumping resources if required to use more cache.


postgres=# create table t(id int primary key, col1 int);
CREATE TABLE
postgres=# alter table t set (autovacuum_enabled = false,
toast.autovacuum_enabled = false);
ALTER TABLE

postgres=# insert into t select x, 1 from generate_series(1, 100) x;
INSERT 0 100

postgres=# \x
Expanded display is off.

postgres=# do $$
declare i int;
begin
for i in 1..10 loop
update t set col1 = i::int;
commit;
end loop;
end; $$;
DO
---this is the default plan picked up by optimizer (tables still not
analyzed)

postgres=# explain analyze select * from t where id < 100;
  QUERY PLAN
--
 Seq Scan on t  (cost=0.00..1472242.50 rows=28995800 width=8) (actual
time=198.740..4849.556 rows=99 loops=1)
   Filter: (id < 100)
   Rows Removed by Filter: 01
 Planning Time: 1.296 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming
true
   Timing: Generation 1.194 ms, Inlining 4.383 ms, Optimization 13.051 ms,
Emission 6.954 ms, 

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

[Vijaykumar Jain]

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
> query (Like Sum, Average, etc.) is answered in real time, where the result
> becomes more and more accurate as Tuples are consumed.
>
> I thought that I could maybe use a custom sampling routine to consume
> table samples until I have seen the whole table with no duplicate tuples.
>
>

I am not sure if I understand correctly, but if this is referring to
faceted search, then then the below may be of some help.

 https://github.com/citusdata/postgresql-hll
https://github.com/hyperstudio/repertoire-faceting

Performance may vary, but it would help you get an idea of the
implementation.
And you also have rollups and cubes, but they get slow over large tables
and require more resources to speed up.

https://www.cybertec-postgresql.com/en/postgresql-grouping-sets-rollup-cube/

If this is not what you wanted, feel free to ignore.

>

Re: Subscription stuck at initialize state

[Vijaykumar Jain]

uot;pg_16406_sync_16391_7060540926182153512"
2022-02-03 23:18:31.135 IST [3431] DETAIL:  Streaming transactions
committing after 0/1722838, reading WAL from 0/1722800.
2022-02-03 23:18:31.135 IST [3431] STATEMENT:  START_REPLICATION SLOT
"pg_16406_sync_16391_7060540926182153512" LOGICAL 0/1722838 (proto_version
'2', publication_names '"mypub1"')
2022-02-03 23:18:35.718 IST [3432] LOG:  logical decoding found consistent
point at 0/1722838
2022-02-03 23:18:35.718 IST [3432] DETAIL:  There are no running
transactions.
2022-02-03 23:18:35.718 IST [3432] STATEMENT:  CREATE_REPLICATION_SLOT
"mysub2" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2022-02-03 23:18:40.737 IST [3434] LOG:  starting logical decoding for slot
"mysub2"
2022-02-03 23:18:40.737 IST [3434] DETAIL:  Streaming transactions
committing after 0/1722870, reading WAL from 0/1722838.
2022-02-03 23:18:40.737 IST [3434] STATEMENT:  START_REPLICATION SLOT
"mysub2" LOGICAL 0/0 (proto_version '2', publication_names '"mypub2"')
2022-02-03 23:18:40.737 IST [3434] LOG:  logical decoding found consistent
point at 0/1722838
2022-02-03 23:18:40.737 IST [3434] DETAIL:  There are no running
transactions.
2022-02-03 23:18:40.737 IST [3434] STATEMENT:  START_REPLICATION SLOT
"mysub2" LOGICAL 0/0 (proto_version '2', publication_names '"mypub2"')
2022-02-03 23:18:40.857 IST [3435] LOG:  logical decoding found consistent
point at 0/1722870
2022-02-03 23:18:40.857 IST [3435] DETAIL:  There are no running
transactions.
2022-02-03 23:18:40.857 IST [3435] STATEMENT:  CREATE_REPLICATION_SLOT
"mysub3" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2022-02-03 23:18:46.091 IST [3437] LOG:  logical decoding found consistent
point at 0/17228A8
2022-02-03 23:18:46.091 IST [3437] DETAIL:  There are no running
transactions.
2022-02-03 23:18:46.091 IST [3437] STATEMENT:  CREATE_REPLICATION_SLOT
"mysub4" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2022-02-03 23:18:51.941 IST [3439] LOG:  logical decoding found consistent
point at 0/17228E0
2022-02-03 23:18:51.941 IST [3439] DETAIL:  There are no running
transactions.
2022-02-03 23:18:51.941 IST [3439] STATEMENT:  CREATE_REPLICATION_SLOT
"mysub5" LOGICAL pgoutput NOEXPORT_SNAPSHOT



*# solution*
so i bump the  max_logical_replication_workers = 10 and restart the db
servers.
as there are workers available, replication catches up and tables in sync.

# on subscriber
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# select count(1) from t_bytea;
 count
---
 0
(1 row)

postgres=# table pg_subscription_rel;
 srsubid | srrelid | srsubstate | srsublsn
-+-++---
   16406 |   16391 | r  | 0/1722838
   16407 |   16394 | r  | 0/2000110
   16408 |   16397 | r  | 0/2000148
   16409 |   16397 | r  | 0/2000148
   16410 |   16384 | r  | 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?

[Vijaykumar Jain]

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 the primary
> server, where its value was 300.
>
> and the standby does not start until I raise also its max_connections.
> Why is PostgreSQL requiring the max_connections to be aligned between
> the primary and the standby?
>
> Thanks,
> Luca
>
>
>
> No. iirc, It has to be the same on all nodes in the physical replication
> setup.



But if vis pgbouncer, you can maintain the same max_connection but alter
> active front-end backend connections.
>


>

Re: Subscription stuck at initialize state

[Vijaykumar Jain]

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 doesn't work either.
> However, when I try to subscribe it on some other DB than the one
> mentioned above, it works.
> By which I am deducing that publication and the source DB are okay, the
> problem is on the target DB and it's subscription.
> Maybe I will have to restart the DB as a last resort, but I am not sure if
> that will solve the problem either.
>
>
its a very verbose mail, so if it noisy, kindly ignore.

else,

can you check basic connectivity from the subscriber to publisher using
psql  and run a simple query ?
can you share your "create publication" and "create subscription"
commands/statements too please?

i am attaching a general logical replication setup on a single server and
put some scenarios where replication breaks and how to monitor and how to
resume.
and how that is monitored.


postgres@controller:~$ tail db1/postgresql.conf db2/postgresql.conf
==> db1/postgresql.conf <==

# Add settings for extensions here
wal_level=logical
archive_mode = on
archive_command = '/bin/true'
max_wal_size = 48MB
min_wal_size = 32MB
shared_buffers = 32MB
port = 5001
max_logical_replication_workers = 10

==> db2/postgresql.conf <==
# Add settings for extensions here
wal_level=logical
archive_mode = on
archive_command = '/bin/true'
max_wal_size = 48MB
min_wal_size = 32MB
shared_buffers = 32MB
port = 5002
max_logical_replication_workers = 10

postgres@controller:~$ pg_ctl -D db1 -l db1.log start
waiting for server to start done
server started
postgres@controller:~$ pg_ctl -D db2 -l db2.log start
waiting for server to start done
server started
postgres@controller:~$ psql -p 5001
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# create table t1(id int primary key);
CREATE TABLE
postgres=# create table t2(id int); -- this will throw error on delete, no
replica identity
CREATE TABLE
postgres=# insert into t1 select x from generate_series(1, 100) x;
INSERT 0 100
postgres=# insert into t2 select x from generate_series(1, 100) x;
INSERT 0 100
postgres=# checkpoint;
CHECKPOINT
postgres=# \q
postgres@controller:~$ psql -p 5002
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=#  create table t1(id int primary key);
CREATE TABLE
postgres=# create table t2(id int);
CREATE TABLE
postgres=# \q
postgres@controller:~$ ps aux | grep -i postgres:
postgres1116  0.0  0.4 113632  8232 ?Ss   13:24   0:00
postgres: checkpointer
postgres1117  0.0  0.2 113496  5868 ?Ss   13:24   0:00
postgres: background writer
postgres1118  0.0  0.3 113496  6964 ?Ss   13:24   0:00
postgres: walwriter
postgres1119  0.0  0.4 114032  8432 ?Ss   13:24   0:00
postgres: autovacuum launcher
postgres1120  0.0  0.2 113496  4132 ?Ss   13:24   0:00
postgres: archiver
postgres1121  0.0  0.2  72112  4896 ?Ss   13:24   0:00
postgres: stats collector
postgres1122  0.0  0.3 113928  6732 ?Ss   13:24   0:00
postgres: logical replication launcher
postgres1128  0.0  0.3 113496  5956 ?Ss   13:24   0:00
postgres: checkpointer
postgres1129  0.0  0.3 113496  5916 ?Ss   13:24   0:00
postgres: background writer
postgres1130  0.0  0.3 113496  6952 ?Ss   13:24   0:00
postgres: walwriter
postgres1131  0.0  0.4 114032  8384 ?Ss   13:24   0:00
postgres: autovacuum launcher
postgres1132  0.0  0.2 113496  4160 ?Ss   13:24   0:00
postgres: archiver
postgres1133  0.0  0.2  72112  4868 ?Ss   13:24   0:00
postgres: stats collector
postgres1134  0.0  0.3 113928  6804 ?Ss   13:24   0:00
postgres: logical replication launcher
postgres1186  0.0  0.0   8164   724 pts/0S+   13:26   0:00 grep -i
postgres:
postgres@controller:~$ psql -p 5001
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create publication mypub for all tables

postgres=# create publication mypub for all tables with
(publish='insert,update,delete,truncate');
CREATE PUBLICATION
postgres=# \q
postgres@controller:~$ psql -p 5002
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create subscription mysub connection 'port=5001' publication
mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
postgres=# select count(1) from t1;
 count
---
   100
(1 row)

postgres=# select count(1) from t2;
 count
---
   100
(1 row)

postgres=# \q

postgres@controller:~$ ps aux | grep postgres:  # strace the below pids for
movement
postgres1195  0.0  0.7 114800 14744 ?Ss   13:27   0:00
postgres: logical replication worker for subscription 16392
postgres1196  0.0  0.7 114728 14676 ?

Re: Subscription stuck at initialize state

[Vijaykumar Jain]

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_replication_origin_status ;
>  local_id |  external_id  |   remote_lsn   |   local_lsn
> --+---++
> 1 | pg_3615756798 | 9E96/37152C80  | 1518C/9014BD98
> 2 | pg_3616584803 | 0/0   | 0/0
> (2 rows)
>
> Would really appreciate it if someone could help me with this.
>
>
>
> On Thu, Feb 3, 2022 at 9:53 AM Abhishek Bhola <
> abhishek.bh...@japannext.co.jp> wrote:
>
>> Update: Tried dropping and recreating publication on the source DB and
>> subscription still won't move ahead.
>> Not sure what I am missing.
>>
>> On Wed, Feb 2, 2022 at 1:20 PM Abhishek Bhola <
>> abhishek.bh...@japannext.co.jp> wrote:
>>
>>> The only statement I see on the target DB log is
>>> "logical replication apply worker for subscription
>>> ""sub_omx_archive_tci"" has started",""
>>>
>>> I don't see the logical replication table synchronization worker started
>>> for any of the tables in this subscription as I see in the other one.
>>>
>>

This might help you track subscription state along with
pg_stat_subscription.
https://www.postgresql.org/docs/10/catalog-pg-subscription-rel.html

It might be a huge table being 'copy' ed and writes would be slow due to
too many indexes etc.

https://postgresteam.slack.com/files/UQMFAU01W/F02V69YK59P/untitled.sql

also if you see nothing is moving, you may want to login to the server and
strace the worker processes pid to see if you see any movement at all or it
keeps looping on some errors.

Re: Query on postgres_fdw extension

[Vijaykumar Jain]

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 was responsible for all rebalancing incase of hotspots.
other api sharding examples ...
Database Sharding: Solving Performance in a  Multi-Tenant Restaurant Data
Analytics System (gotenzo.com)



although
it worked really well, when you are maintaining it on your own, it gets
really painful, much beyond id generation globally.

i will not go into the details, but in short, sharded setup is not the same
as local setup. there would be many more things that would not work as
expected
which would otherwise work really well on a standalone setup.

writes over shard may work, but you realize it is over the network, so you
can lock you table for a much longer duration and cause a much more serious
outage,
if you really wanted to have distributed writes with unique keys, you can
go with uuid i think or have your own seq generator globally (see below).


*Move ID generation out of the database to an ID generation service outside
of the database… As soon as a piece of work enters their system, an ID gets
assigned to it… and that ID generated in a way that is known to be globally
unique within their system*

A Better ID Generator For PostgreSQL | robconery

Index of /shard_manager/shard_manager-0.0.1/ (pgxn.org)
  (pretty old
but if you can use your coordinator server as a id_generator(), then you
can generate ids which are globally unique)
Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by
Instagram Engineering | Instagram Engineering (instagram-engineering.com)


imho, do not try sharding manually, unless you have enough dbas to maintain
the shards, try using citus, it would make a lot of the manual stuff easier.

also, the below work arounds are bad, incase you just want to rush through

postgres=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=#
localdb=# \dt
Did not find any relations.
localdb=# \det
 List of foreign tables
 Schema | Table |Server
+---+---
 public | t | remote_server
(1 row)

localdb=# \det+ t
List of foreign tables
 Schema | Table |Server |  FDW options   |
Description
+---+---++-
 public | t | remote_server | (schema_name 'public', table_name 't') |
(1 row)

localdb=# \det t
 List of foreign tables
 Schema | Table |Server
+---+---
 public | t | remote_server
(1 row)

localdb=# create or replace function getnext() returns int as $_$ select id
FROM dblink ('dbname = remotedb', $$ select nextval('t_id_seq') $$  ) as
newtable(id int); $_$ language sql;
CREATE FUNCTION
localdb=# \c remotedb
You are now connected to database "remotedb" as user "postgres".
remotedb=# \dt t
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | t| table | postgres
(1 row)

remotedb=# \ds t_id_seq
List of relations
 Schema |   Name   |   Type   |  Owner
+--+--+--
 public | t_id_seq | sequence | postgres
(1 row)

remotedb=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=# insert into t values (getnext(), 100);
INSERT 0 1
localdb=# insert into t values (getnext(), 100);
INSERT 0 1
localdb=# select * from t;
 id | col1
+--
 11 |4
 12 |5
 13 |  100
 14 |  100
(4 rows)

just my opinion, ignore it not useful.

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

[Vijaykumar Jain]

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 possible to snapshot the vm, and and set it up in a controlled
environment where you can play around with incoming connections at the
network layer
beyond the vm.
With that, is it possible for you to use gdb and debug a connection to the
postmaster.
you can setup using below,
Getting a stack trace of a running PostgreSQL backend on Linux/BSD -
PostgreSQL wiki


and then you can put a breakpoint at this function and check the input
lines it gets for parsing.
https://github.com/postgres/postgres/blob/6ab42ae36713b1e6f961c37e22f99d3e6267523b/src/backend/libpq/hba.c#L779

postgres/hba.c at 6ab42ae36713b1e6f961c37e22f99d3e6267523b ·
postgres/postgres (github.com)


but maybe this helps identify why other ips are being allowed.
to be more paranoid, you can all reject from the ip you are trying to make
a connection, and trace that specific rule.

this might be an overkill and maybe a waste of effort given you already can
query the hba view, but incase you want to try out.

Re: Debugging features needed

[Vijaykumar Jain]

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 same feature/action (applicationName is set to this)
> even using connection pooling. Before, we had different requests reusing
> the same pid of course and if they happened to have the same appName, we
> didn't know it was a new process on app side.
>


While researching options,  I did see crunchy proxy using annotations to
split read/write. But I was not sure if any of these options fit well with
multiple toolings , drivers etc.

>
> https://github.com/CrunchyData/crunchy-proxy/blob/master/docs/crunchy-proxy-user-guide.asciidoc#annotations
>

The other option was
https://github.com/wgliang/pgproxy

or writing your custom plug-in for envoyproxy filter for postgresql

https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/other_protocols/postgres

So basically it was adding one more layer in between the already connection
pooling layers via pgbouncer.
The biggest concern is when we use ssl, these session params cannot be read
unless we terminate ssl at the proxy, and then decode,  so I gave up :)

Re: Debugging features needed

[Vijaykumar Jain]

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 other system view, table or function) I
> would be more than happy.
>
> Well, I have described what I see as a partial solution to my problem, the
> problem being: how to track a specific set of actions at database level of
> a given application user in the context of session pooling (so the user
> activity can jump from one session to another of the same pool).
>
> If I would be able to set a "value" to a custom variable, then the app
> would simply need to set the value at each session usage, but I definitely
> understand that other mechanisms might help solving the problem (but I do
> not know any of these).
>

I had been trying something similar to play with tracing. I was wondering
if the traceid or requestid could make its way into the query logs to be
able to debug.
The only thing I could find was play around with param application_name
that could be set for each session with the traceid of my downstream and
enable logging to log application name via log_line_prefix.

I'll be interested to know as well if there are any other parameters.

https://www.enterprisedb.com/blog/getting-most-out-applicationname

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Vijaykumar Jain]

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
> to get the expected results but got no luck,
>
> Parameter setting unit
> enable_parallel_append on
> enable_parallel_hash on
> force_parallel_mode off
> max_parallel_maintenance_workers 2
> max_parallel_workers 8
> max_parallel_workers_per_gather 2
> min_parallel_index_scan_size 64 8kB
> min_parallel_table_scan_size 1024 8kB
> parallel_leader_participation on
> parallel_setup_cost 1000
> parallel_tuple_cost 0.1
> effective_cache_size 4GB
> shared_buffers 128MB
> work_mem 4MB
>
>


I am pretty sure there will be questions on why you want to do that or why
you think this would solve any problem.

anyways,
This is just to force a parallel run, but do not do this in production.
The way we try this here, is to trick the optimizer by saying there is no
cost of making use of parallel setup, so this is always the best
path.(which is wrong, but..)

postgres=# select name,setting from pg_settings where name like '%para%';
   name| setting
---+-
 enable_parallel_append| on
 enable_parallel_hash  | on
 force_parallel_mode   | off
 log_parameter_max_length  | -1
 log_parameter_max_length_on_error | 0
 max_parallel_maintenance_workers  | 2
 max_parallel_workers  | 8
 max_parallel_workers_per_gather   | 2
 min_parallel_index_scan_size  | 64
 min_parallel_table_scan_size  | 1024
 parallel_leader_participation | on
 parallel_setup_cost   | 1000
 parallel_tuple_cost   | 0.1
 ssl_dh_params_file|
(14 rows)

postgres=# set force_parallel_mode to on;
SET
postgres=# set parallel_setup_cost to 0;
SET
postgres=# set parallel_tuple_cost to 0;
SET
postgres=# explain analyze select * from t where id > 0;
   QUERY PLAN

 Gather  (cost=0.00..3.76 rows=80 width=12) (actual time=2.900..5.996
rows=80 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..3.76 rows=34 width=12) (actual
time=0.002..0.009 rows=27 loops=3)
 ->  Parallel Seq Scan on t2 t_2  (cost=0.00..1.23 rows=18
width=12) (actual time=0.005..0.009 rows=31 loops=1)
   Filter: (id > 0)
 ->  Parallel Seq Scan on t1 t_1  (cost=0.00..1.21 rows=17
width=12) (actual time=0.004..0.006 rows=29 loops=1)
   Filter: (id > 0)
 ->  Parallel Seq Scan on t3 t_3  (cost=0.00..1.15 rows=12
width=12) (actual time=0.001..0.003 rows=20 loops=1)
   Filter: (id > 0)
 Planning Time: 0.568 ms
 Execution Time: 6.022 ms
(12 rows)

postgres=# set seq_page_cost to 10; --- since we do not want seq scan
but index scan
SET
postgres=# explain analyze select * from t where id > 0;
QUERY
PLAN
---
 Gather  (cost=0.14..37.65 rows=80 width=12) (actual time=0.232..5.326
rows=80 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.14..37.65 rows=34 width=12) (actual
time=0.007..0.020 rows=27 loops=3)
 ->  Parallel Index Only Scan using t2_ts_id_idx on t2 t_2
(cost=0.14..12.55 rows=18 width=12) (actual time=0.005..0.012 rows=31
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 31
 ->  Parallel Index Only Scan using t1_ts_id_idx on t1 t_1
(cost=0.14..12.53 rows=17 width=12) (actual time=0.007..0.013 rows=29
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 29
 ->  Parallel Index Only Scan using t3_ts_id_idx on t3 t_3
(cost=0.14..12.41 rows=12 width=12) (actual time=0.019..0.025 rows=20
loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 20
 Planning Time: 0.095 ms
 Execution Time: 5.351 ms
(15 rows)


Again, do not do this in production. This is only for debugging purposes
using 0 cost.
You can try looking at pg_hint_plan (osdn.jp)
   if you want to force a
plan.
Also, be ready to answer, why do you want to do this or what makes you
think the parallel option will work magic.

also with TB sized dbs, pls ensure your disk io/latency etc are not a
problem.
maybe also bump memory and tune accordingly, to absorb disk io.

Re: Can db user change own password?

[Vijaykumar Jain]

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 session_user = current_user was
> set before.
>
> I'm not understanding. You will need to sketch this out:
>
> 1) Connection parameters for log in with .
>
> 2) On log in the output from: select session_user, current_user;
>
> 3) Define '...set as owner of database automatically'.
>
>
Toomas,
things work for me as expected.

I guess as asked, you may want to show an example for your below statement
to help understand better.

*"The issue was that when a user logged into the database his session_user
user was set as the owner of the database automatically."*


postgres@u1:~$ psql
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \du
   List of roles
 Role name | Attributes |
Member of
---++---
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role vijay login nosuperuser password '1234';
CREATE ROLE
postgres=# grant CONNECT on database postgres to vijay;
GRANT
postgres=# \q
postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

postgres=> \password
Enter new password:
Enter it again:
postgres=> \q
postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1  # old
password
Password for user vijay:
psql: error: FATAL:  password authentication failed for user "vijay"
FATAL:  password authentication failed for user "vijay"
postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 # new password
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

postgres=> \q

postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

postgres=> select session_user, current_user;
 session_user | current_user
--+--
 vijay| vijay
(1 row)

postgres=> \password
Enter new password:
Enter it again:
postgres=> alter role vijay password '666'; -- trying both ways, works
ALTER ROLE
postgres=> \q
postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

postgres=> select session_user, current_user;
 session_user | current_user
--+--
 vijay| vijay
(1 row)


postgres@u1:~$ psql
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# create database vijay owner vijay;
CREATE DATABASE
postgres=# \q
postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

vijay=> select session_user, current_user;
 session_user | current_user
--+--
 vijay| vijay
(1 row)

vijay=> alter role vijay password '999'; -- trying both ways, works
ALTER ROLE
vijay=> \q
postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1
Password for user vijay:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

vijay=> \q

Re: Connection queuing by connection pooling libraries

[Vijaykumar Jain]

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:
>>
>>> 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 which can get exhausted on
>>> large number of requests.
>>>
>>> I know db connection pooling can be used to reuse the connections but it
>>> will not help when there are large number of active concurrent connections.
>>> My queries are already optimised and short living.
>>>
>>> For that i need some queuing mechanism like pgbouncer for postgres
>>> https://www.percona.com/blog/2021/02/26/connection-queuing-in-pgbouncer-is-it-a-magical-remedy/
>>>
>>> pgbounder i understand is a proxy which needs to be separately installed
>>> on the web or db server.
>>>
>>> I was thinking if the normal client side db connection pooling libraries
>>> like Apache DBCP , can also provide similar connection queuing while
>>> running in the application runtime.
>>>
>>
>>
>
also pls checkout, i forgot to link early on
Number Of Database Connections - PostgreSQL wiki
<https://wiki.postgresql.org/wiki/Number_Of_Database_Connections>
it explains the reasons, too many direct connections may result in
performance issues.

Re: Connection queuing by connection pooling libraries

[Vijaykumar Jain]

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.
>>
>> 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 which can get exhausted on
>> large number of requests.
>>
>> I know db connection pooling can be used to reuse the connections but it
>> will not help when there are large number of active concurrent connections.
>> My queries are already optimised and short living.
>>
>> For that i need some queuing mechanism like pgbouncer for postgres
>> https://www.percona.com/blog/2021/02/26/connection-queuing-in-pgbouncer-is-it-a-magical-remedy/
>>
>> pgbounder i understand is a proxy which needs to be separately installed
>> on the web or db server.
>>
>> I was thinking if the normal client side db connection pooling libraries
>> like Apache DBCP , can also provide similar connection queuing while
>> running in the application runtime.
>>
>
>
btw there are other options as well to pgbouncer which are in active
development, incase you wish to explore.

yandex/odyssey: Scalable PostgreSQL connection pooler (github.com)
<https://github.com/yandex/odyssey>
<https://github.com/yandex/odyssey>pgagroal
<https://agroal.github.io/pgagroal/>

<https://agroal.github.io/pgagroal/>also,
some people ( like myself :)  split read/writes and use primary and a set
of replicas for connections to handle scale.
so a pretty common architecture makes use of
Scaling PostgreSQL using Connection Poolers and Load Balancers for an
Enterprise Grade environment - Percona Database Performance Blog
<https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/>

Re: Connection queuing by connection pooling libraries

[Vijaykumar Jain]

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 which can get exhausted on
> large number of requests.
>
> I know db connection pooling can be used to reuse the connections but it
> will not help when there are large number of active concurrent connections.
> My queries are already optimised and short living.
>
> For that i need some queuing mechanism like pgbouncer for postgres
> https://www.percona.com/blog/2021/02/26/connection-queuing-in-pgbouncer-is-it-a-magical-remedy/
>
> pgbounder i understand is a proxy which needs to be separately installed
> on the web or db server.
>
> I was thinking if the normal client side db connection pooling libraries
> like Apache DBCP , can also provide similar connection queuing while
> running in the application runtime.
>

DBCP – BasicDataSource Configuration (apache.org)


From
what i see here, this seems to be similar to other client side connection
libraries/ poolers, which works, but the major benefit from pgbouncer for
server side connection
pooling is something like transaction level pooling. which would work
pretty well in your case, if you say you have short lived connections.

creating a new connection on postgresql directly is expensive (resources)
as it is spawning a new process.
pgbouncer provides a workaround, where it manages opening and closing of
connections on the server side for you, and then allocating the already
opened connections to the frontend connections. when your frontend
connection is done with the transaction, it will close the frontend
connection, but does not do it at the backend "yet". if there is a new
request for a connection, it will reuse the old connection, and allocate
the same to the new connection.
Hence the caveat, you cannot use prepared statements and there is no
guarantee which backend connection gets mapped to which frontend connection.

also, it is important to note, having too many connections while in a
transaction can result in bloating, as due to mvcc, it will not do any dead
rows cleanup even if it can as it cannot see those "dead" rows if any,
that can result in bloating and eventually slowdown and other problems.

The best use case I have seen with pgbouncer is not just connection
pooling, but also being able to handle auto scaling to a large effect.
Especially with microservices, if you app has a connection pool of 10, and
you scale your app to 10 instances, without pgbouncer it would use all 100
backend connections, but if the connections are short lived transactions,
it might work well even with 50 connections in the backend.

there are other administrative benefits of pgbouncer too, beyond just
connection pooling,

The biggest drawback I have had with pgbouncer with enterprise auth support
like for ldap/kerberos etc and it masks the ip from the client with its own
ip when you look at pg_stat_activity.
but since we use one db per app, we are able to handle the above issues
with less unknowns.














> --
>
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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
> else" - with spaces in it. Or "badlyNamedSchema".
>
>
Yeah, my bad. I ran that casually, which was wrong. Thanks for correcting
it.


postgres=# \dn
   List of schemas
   Name|  Owner
---+--
 my Schema | postgres
 public| postgres
(2 rows)

-- the problem with my original dynamic sql
postgres=# 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 postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA my Schema TO postgres
ERROR:  syntax error at or near "Schema"
LINE 1: GRANT USAGE ON SCHEMA my Schema TO postgres
 ^
QUERY:  GRANT USAGE ON SCHEMA my Schema TO postgres
CONTEXT:  PL/pgSQL function inline_code_block line 7 at EXECUTE

-- the solution
postgres=# 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 ' || quote_ident(sch) || ' TO postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO


/*
-- OR using format
postgres=# 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 = format('GRANT USAGE ON SCHEMA %I TO postgres', sch);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO
*/







> Generally you'd want to use:
>
> execute format('GRANT USAGE ON SCHEMA %I TO readonlyuser_role', sch);
>
> and it will take care of it.
>
> > also,
> > in case you like, I have kind of liked this
> > you can try running meta commands using psql -E to get the query that you
> > would like to run for DO block.
>
> while in psql, you can simply:
> select format() ... from ...;
> make sure that it returns list of correct sql queries, with no mistakes,
> and with ; at the end of each command, and then rerun it like:
> select format() ... from ... \gexec
>
> depesz
>
>

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 '%', stmt;
execute stmt;

stmt = 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || sch || ' TO
readonlyuser_role';
raise notice '%', stmt;
execute stmt;

end loop;
end; $$;


also,
in case you like, I have kind of liked this
you can try running meta commands using psql -E to get the query that you
would like to run for DO block.


postgres@db1:~$ psql -E
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

postgres=# \dn *.*
* QUERY **
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
ORDER BY 1;
**

List of schemas
Name|  Owner
+--
 information_schema | postgres
 pg_catalog | postgres
 pg_temp_1  | postgres
 pg_toast   | postgres
 pg_toast_temp_1| postgres
 public | postgres
(6 rows)





On Wed, 13 Oct 2021 at 15:22, hubert depesz lubaczewski 
wrote:

> On Wed, Oct 13, 2021 at 11:04:42AM +0200, celati Laurent wrote:
> > Good morning,
> >
> > I work on Postgresql 13 (windows) and Postgis.
> > For some "basic USERS", i have to grant select/read for all tables of the
> > 12 schemas of my  db ?
> >
> > With Postgresql 13, i am obliged to write :
> > *GRANT SELECT ON ALL TABLES IN SCHEMA TO username  ?*
>
> Yes. For each schema.
>
> You could write a DO query, or even get psql to run it automaticaly-ish
> for every schema, but it will still be separate query for each schema.
>
> depesz
>
>
>

-- 
Thanks,
Vijay
Mumbai, India

Re: vacuum full

[Vijaykumar Jain]

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 when there are space issues.
>
>
ok ignore.
I think querying the disk for available space may be shell access from the
client, a security issue.
Also, a 10GB table with all dead tuples is 100% bloat, and would not really
need additional space for table rebuilds.
I think we can just put out some queries like bloat estimation  of
relations, to get an idea of wasted space and used space
and estimate based on that on how much would be taken up from the disk
before being cleaned up.

ref queries: PostgresQL Automating VACUUM FULL for bloated tables - Stack
Overflow
<https://stackoverflow.com/questions/13931989/postgresql-automating-vacuum-full-for-bloated-tables>



-- 
Thanks,
Vijay
Mumbai, India

vacuum full

[Vijaykumar Jain]

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,
but i was just thinking
vacuumdb and vacuumlo  can make use of options like --dry-run, like
pg_repack allows,
but also just abort incase there is no disk space available.

i mean, i have seen confident replies from earlier mailing threads, where
assume 2x disk required per object that is full vacuumed at least.

can this be converted into an estimate of sorts on objects that would be
vacuumed and free disk that would be required before any operation starts.

maybe this is wishful thinking, but just asking.

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 expecting debezium when i saw cdc and java, but
DBZ-1596 Fix open resources while initializing publication by Naros · Pull
Request #1120 · debezium/debezium (github.com)


[DBZ-1596] AWS RDS Performance Insights screwed a little by non-closed
statement in "SELECT COUNT(1) FROM pg_publication" - Red Hat Issue Tracker


I may be wrong, but just in case, FYI.

-- 
Thanks,
Vijay
Mumbai, India

Re: user creation time for audit

[Vijaykumar Jain]

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 allowed on global objects.
PostgreSQL: Documentation: 13: 39.2. Event Trigger Firing Matrix



 test=# create function test_event_trigger() returns event_trigger as $$
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$ language plpgsql;
CREATE FUNCTION
Time: 7.621 ms
test=# create event trigger regress_event_trigger2 on ddl_command_start
   when tag in ('create table', 'create role')
   execute procedure test_event_trigger();
*ERROR:  event triggers are not supported for create role*
Time: 0.214 ms
test=# create table x(id int); drop table x;
CREATE TABLE
Time: 7.932 ms
DROP TABLE
Time: 2.002 ms
test=# create event trigger regress_event_trigger2 on ddl_command_start
   when tag in ('create table')
   execute procedure test_event_trigger();
CREATE EVENT TRIGGER
Time: 8.744 ms
test=# create table x(id int); drop table x;
NOTICE:  test_event_trigger: ddl_command_start CREATE TABLE
CREATE TABLE
Time: 7.878 ms
DROP TABLE
Time: 3.489 ms

ref: postgres/event_trigger.sql at master · postgres/postgres (github.com)


Anyways, I think the options were using external mechanisms to role audits,
or pgaudit via statement logging ?

-- 
Thanks,
Vijay
Mumbai, India

Re: PostgreSQL Automatic Failover Windows Server

[Vijaykumar Jain]

>
>
> 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 list of awesome PostgreSQL
software, libraries, tools and resources, inspired by awesome-mysql
(github.com)

These are a few solutions for HA.
There are detailed docs for each and/or blogs, you will have to read them
to figure out which one suits you best.


-- 
Thanks,
Vijay
Mumbai, India

Re: Single mater replica setup for an existing DB

[Vijaykumar Jain]

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, synchronously ideally though
> async might be OK.
>

hands on using katakoda:
CrunchyData Interactive Learning Portal


blogs:
Setting up Streaming Replication in PostgreSQL 13 and Streaming Replication
Internals - MigOps

There are many blogs for versions 11, 12 also.

-- i moved from manual to patroni, so found patroni simple to set up. ymmv
postgres HA using patroni.
There is an option to add an existing node to the patroni cluster.
Convert a Standalone to a Patroni Cluster — Patroni 2.1.0 documentation


official reference (especially for tuning config):
PostgreSQL: Documentation: 12: Part III. Server Administration


--
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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

if we have range partitions based on year, 2018 -2019, 2019-2020, etc and
for
any one year we see uneven growth in data, we simply split that partition
by 2 and move only 6 months worth of data
to another new partition.
If we see similar issues not solved by the first split, we split each again
by 2 and only move the remainder of data to all four partitions.
So at any point of time, if the range partition which was planned earlier
does not work well for some periods, we split the partitions.
and if we see the trend growing with increased volume, the new partitions
are created qtry etc.

I have not done hash partitioning rebuild, but i think this video explains
how we could best do it.
Ways of sharding and resharding in a distributed system - YouTube
<https://www.youtube.com/watch?v=7KxPlw2NXb4=LL=1>


Thanks,
Vijay
Mumbai, India

Re: Multi-master replication

[Vijaykumar Jain]

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
> quite high. It does seem that multi-master has risks (everyone seems to
> talk about conflict resolution!) so I wonder whether the sensible route is
> simply to scale vertically on the master?
>
>
>


http://bdr-project.org/docs/stable/conflicts.html

There are claims from other databases that they offer multi master
(cockroachdb, yugabyte, etc) out of the box, but there is little clarity on
what would one has to compromise with.

You can always try sharding, postgres fdw can help you with that setup. In
my implementation,  we agreed on write locally, read globally. That way
writes could scale without compromise, but reads would be scatter gather
(fdw support for parallel scan in pg14 is promising)

Then, there is citusdb that can help you shard seamlessly and rebalance too.

https://www.citusdata.com/


https://about.gitlab.com/handbook/engineering/development/enablement/database/doc/fdw-sharding.html


https://blog.timescale.com/blog/building-a-distributed-time-series-database-on-postgresql/

As you read more, multi master is not so simple, from what I read.
I think there needs to be a good amount of thoughts for long term growth of
apps, is sharding does not scale well.

In out case, apps were broken down from monoliths to small microservi es to
deal with growth issues, and now it works well as single db as growth is
not exponential ( also small dbs, less tuning, defaults work) but then one
has to upgrade all the dbs periodically to catch up on releases.

As this is a postgresql list, I should not be posting mongo, but never the
less a good read.
https://www.mongodb.com/developer/article/active-active-application-architectures/

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

[Vijaykumar Jain]

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 would be the default and would be done
> automatically!
>

no worries, it seems you have  a fair idea how things would grow. so
partitioning would be of help.
My biggest worry around partitioning is around moving data around
partitions/ rebalancing partitions.
It requires making use of triggers, syncing data and then attach/remove
partitions if it is simple as
list/range and use binary split for large partitions, to avoid large row
movements.

But when it comes to hash, it requires rebuilding the entire setup either
via triggers if there is significant space on the db,
or logically replicating to another server
with a new hash scheme. because i do not have lot of exp around
sharding/partitioning,
nor about how others dbs on how they claim automatic movements of data
between
partitions, I think i wore myself out due to poor design.

-- 
Thanks,
Vijay
Mumbai, India

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Vijaykumar Jain]

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 pg_basebackup for backups but mostly replica setup),
so i never ran this procedure.
although, i see a reference in test lib file to help atleast confirm the
source repo has a reference to it.
postgres/PostgresNode.pm at 856de3b39cf6041338b286a99257c324ce647f4e ·
postgres/postgres (github.com)


i can link the c source file, but that would not be your answer.
somehow, this procedure is all over the internet, via blogs etc, but i do
not know where it is in the docs.


> Or, wouldn't it even be better that the files in
> pg_wal.tar.gz would have the dir pg_wal in front?
>
>
I am not sure, I am the best person to answer this question.

-- 
Thanks,
Vijay
Mumbai, India

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Vijaykumar Jain]

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 online tutorial to help you get started.
CrunchyData Interactive Learning Portal


not wanting to divert from your original query, but just in case.

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Vijaykumar Jain]

>
>
> 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 -c 'create table t(id int primary key);
insert into t select x from generate_series(1, 1) x;'
INSERT 0 1
postgres@db:~/playground/demo$ pg_basebackup -U postgres -Ft -z -D
basebackup
postgres@db:~/playground/demo$ echo $?
0
postgres@db:~/playground/demo$ cd basebackup
postgres@db:~/playground/demo/basebackup$ ls
backup_manifest  base.tar.gz  pg_wal.tar.gz
postgres@db:~/playground/demo/basebackup$ tar xzf base.tar.gz
postgres@db:~/playground/demo/basebackup$ cd pg_wal/
postgres@db:~/playground/demo/basebackup/pg_wal$ mv ../pg_wal.tar.gz . #
pg_wal.tar.gz file has to uncompressed in pg_wal dir
postgres@db:~/playground/demo/basebackup/pg_wal$ tar xzf pg_wal.tar.gz
postgres@db:~/playground/demo/basebackup/pg_wal$ cd ../..
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz'
basebackup/base.tar.gz
basebackup/pg_wal/pg_wal.tar.gz
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz' -delete
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz'
postgres@db:~/playground/demo$ pg_verifybackup basebackup
backup successfully verified

Backup and Restore a PostgreSQL Cluster With Multiple Tablespaces Using
pg_basebackup - Percona Database Performance Blog




-- 
Thanks,
Vijay
Mumbai, India

Re: Getting pg_stat_database data takes significant time

[Vijaykumar Jain]

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 the planner to get the estimates ,
> and hence delay the query at whole.
> but that was a wild guess.
>


> --pg_class not locked
> postgres@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null
>
> real0m0.016s
> user0m0.003s
> sys 0m0.000s
>
> -- pg_class locked and the query completed when tx it unlocks
> postgres@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null
>
> real0m7.269s
> user0m0.003s
> sys 0m0.000s
>
>
ok pls ignore, i think this was wrong.
the delay while pg_class was locked on psql connection, not
pg_stat_database query.

strace -r psql -c 'select * from pg_stat_database;' >/dev/null

 0.000612 poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3,
revents=POLLIN}])
 4.819832 recvfrom(3,
"R\0\0\0\10\0\0\0\0S\0\0\0\32application_name\0p"..., 16384, 0, NULL, NULL)
= 396 -- the time till table was locked

I think i'll back off here, do some gdb tracing myself before i make
assumptions.

Re: Getting pg_stat_database data takes significant time

[Vijaykumar Jain]

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 and  check the query again , or do you see
> > pg_class bloated ?
>
> pg_class is large, but vacuuming it didn't help for time of query on
> pg_stat_database.
>
> 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 the planner to get the estimates ,
and hence delay the query at whole.
but that was a wild guess.
--pg_class not locked
postgres@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null

real0m0.016s
user0m0.003s
sys 0m0.000s

-- pg_class locked and the query completed when tx it unlocks
postgres@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null

real0m7.269s
user0m0.003s
sys 0m0.000s


> > The other option would be gdb backtrace I think that would help.
>
> backtrace from what? It doesn't *break*, it just takes strangely long time.
>
> I could envision attaching gdb to pg process and getting backtrace, but
> when?
> before running the query? after?
>
>
backtrace of the running query, maybe you might have to run this multiple
times against the raw query directly via psql, since you get this delay
occasionally,
why i say this, --
the backtrace would probably show if it is hanging in a normal plan
execution, or something else.
or maybe perf/strace to trace syscall timings
but i know you know more than me :) , just asking if the backtrace helps
expose something helpful.

-- 
Thanks,
Vijay
Mumbai, India

Re: Getting pg_stat_database data takes significant time

[Vijaykumar Jain]

 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)

[Vijaykumar Jain]

>
>
>
> 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 it look problematic?

How would your table grow on either side of the join ? Append only, static
data or too frequently updated etc, or dropped periodically, so that delete
based bloating can be skipped completely.

How distributed is the data based on smallint keys, equally or unequally.
What kind of queries would be run and results returned ? Oltp or olap like
? Quick queries with few rows retuned or heavy queries with lot of rows
returned.

Partitioning has been ever improving, so the best option if possible would
be to use the latest pg version is possible,.
Also is there any scope of normalisation of that table, I mean I know
theoretically it is possible, but I have not seen any design with that wide
table( of 1000 cols), so would be good to know.

Just asking, maybe partitioning would the best option but wanting to
know/see the benefit pre and post partitioning.

>
>
-- 
Thanks,
Vijay
Mumbai, India

Re: PostgreSQL general set of Questions.

[Vijaykumar Jain]

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: Chapter 8. Data Types

PostgreSQL: Documentation: 13: CREATE TYPE

postgres/create_type.sql at master · postgres/postgres (github.com)




> 2) How may I get PostgreSQL to output the create table statement(s) for
> one or more tables inside one database, without issuing instructions via
> the command line, but only inside a database login, as a query or pl/sql?
> If not possible, what can you do at the command line, outside
> of PostgreSQL?
>

postgres/describe.c at f68b609230689f9886a46e5d9ab8d6cdd947e0dc ·
postgres/postgres (github.com)

what is your goal? you can clone a table schema using  *create table*
create table y (like x INCLUDING ALL);
PostgreSQL: Documentation: 13: CREATE TABLE



>
> 3) I know that I can use COPY to import or export one database table
> between it and a *.csv file. Can I use it to do this with multiple TABLES
> and *.csv files specified in one COPY COMMAND, or not?
>

it should be fairly easy to script it though,

do $$
declare tbl text; stmt text;
begin
for tbl in select table_name from information_schema.tables where
table_name in ('aa', 'bb', 'cc') loop
stmt = format($_$ copy %I TO '/tmp/%s'; $_$, tbl, tbl);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:   copy aa TO '/tmp/aa';
NOTICE:   copy bb TO '/tmp/bb';
NOTICE:   copy cc TO '/tmp/cc';
DO
postgres=# \! cat /tmp/aa
1
postgres=# \! cat /tmp/bb
1
postgres=# \! cat /tmp/cc
1



> 4) In the absence of OS command line instructions, is there an internal
> PostgreSQL way, via COPY or another function for example, to backup an
> entire database, with all it's create table statements and all insert
> statements, and any other associated objects as well, in one hit?
> Or is this ill-advised?
>
>
i saw some tools doing schema sync, but they too used pg_dump cmd line
ankane/pgsync: Sync data from one Postgres database to another (github.com)


also, its all there in the source though,
postgres/pg_dump.c at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)

If you really want that bad, you can enable log all statements,
and run a pg_dump or psql \d+ and see what sql is generated for that pid,
and start building up on it.




> 5) When setting up communication to remote databases on remote machines, I
> need to use the OPTIONS() function. It seems to require as its first
> function parameter, the schema of the table (the second parameter) that it
> wants to access. Can I supply a null schema, and still be able to reference
> the remote table, or must I also make use of IMPORT FOREIGN SCHEMA?
>
I think this is wrt fdw. What is your goal here?
I am not sure what you want would make sense. there can be 100s of schema
on remote server. there might be same table on diff schemas.
how would your import know, which one to use etc.
postgres/postgres_fdw.sql at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)




> 6) How may I access, via a query, the log for the details of a normal
> table, or similar?
>
What log? an example of what you want would be more helpful. even if it
does not work or exists, you can hypothetically run some command and say
when i `run this command|query`
i should `get this output`
but i  `get this output`



> 7) I have found that the native trigonometry functions, namely the radians
> versions, do produce error results around key trigonometry input values. I
> have discovered that these errors persist, even if I cast the input
> parameter away from DOUBLE PRECISION and into DECIMAL. I would like to know
> if there are any freely available scripts out there that include Arbitrary
> Precision mathematical functions support that calls on DECIMAL and not on
> DOUBLE PRECISION, that do not produce any error values or amounts around
> key inputs? Could someone refer me to a website that has a script that is
> such?
>

imho, your questions where you suggest you have found something not as
expected. It would be best if you could simulate the same on
DB Fiddle - 

Re: autovacuum worker started without a worker entry

[Vijaykumar Jain]

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://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c#L1678
> >,
> the launcher forked a worker and in the meantime the launcher decided
> the worker is no more useful. If that is right, I'm guessing why the
> worker should not be useful anymore: since a single worker could work
> on a table, the only reason I can find is that someone run manually
> vacuum within that database/table.
>
> postgres/autovacuum.c at master · postgres/postgres (github.com)

I think this is around this block, where a worker is assigned some task to
do.
so if there is nothing to vacuum, no dead tuples or etc, or dead tuples in
tx,
it might not get assigned any work, and hence would exit.




> Moreover, I've a question about emergency autovacuum (wraparound).
> Since autovacuum workers are started by postmaster on a signal
> received by the autovacuum launcher, and since with autovacuum = off
> the latter is not running, how does the postmaster decide to start and
> emergency autovacuum?
> I only found what seems to me a normal autovacuum start at
> <
> https://github.com/postgres/postgres/blob/master/src/backend/postmaster/postmaster.c#L5263
> >.
>
>
postgres/varsup.c at master · postgres/postgres (github.com)

I think, this block when it is about to assign the next xid, it does the
math, and triggers an autolauncher start.
I might be wrong, I did not run a backtrace though :)

 * Check to see if it's safe to assign another XID. This protects against
 * catastrophic data loss due to XID wraparound. The basic rules are:
 * If we're past xidVacLimit, start trying to force autovacuum cycles.
 * If we're past xidWarnLimit, start issuing warnings.
 * If we're past xidStopLimit, refuse to execute transactions, unless
 * we are running in single-user mode (which gives an escape hatch
 * to the DBA who somehow got past the earlier defenses).

-- 
Thanks,
Vijay
Mumbai, India

Re: Unexpected block ID found when reading data

[Vijaykumar Jain]

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 no issues
> yet.
>

Did this complete without issues ? or did it throw the same errors ?

Re: Unexpected block ID found when reading data

[Vijaykumar Jain]

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.
>
> pgrestore command:
> pg_restore -U myuser -j8 -d mydb dbdump.backup
>
> I’ve tried to restore to postgre 9.6, 12.1 and 12.5
>
> do you remember which patch version 9.6.x did you take the dump from ?


9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
maybe your restoration to the latest minor version is failing.
can you run a pg_restore -f  ... so it creates a sql dump file
and check if that is clean ?
and then check the version in the dump file, and compile that version from
source (or git) and try if the restore worked fine there.
just saying, in case you are out of options, else ignore.

Re: Lazy View's Column Computing

[Vijaykumar Jain]

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" computing it?
>
> If the function is not volatile, and you're using a moderately recent PG
> version, I'd expect the planner to do that for you.


something like this ?

postgres=# table t;
-[ RECORD 1 ]
col1 | 100
col2 | 100
col3 | 100

-- the sleep is intentional to
postgres=#  create or replace function demo(int) returns int as $$
begin
perform pg_sleep(10);
return $1::int;
end; $$ language plpgsql immutable;
CREATE FUNCTION
Time: 7.253 ms

-- we create a view where col2 is a result of an immutable function call
from demo
postgres=# create or replace view myview as select col1, demo(col2)::int as
col2 from t;
CREATE VIEW
Time: 7.952 ms

postgres=# \x
Expanded display is off.
postgres=# explain (analyze,verbose) select col1, col2 from myview;
  QUERY PLAN
---
 Seq Scan on public.t  (cost=0.00..540.40 rows=2040 width=8) (actual
time=10010.231..10010.236 rows=1 loops=1)
   Output: t.col1, demo(t.col2)
 Query Identifier: 291510593965093899
 Planning Time: 0.027 ms
 Execution Time: 10010.250 ms  -- the function demo was called which
resulted in slow exec time
(5 rows)

Time: 10010.648 ms (00:10.011)
postgres=# explain (analyze,verbose) select col1 from myview;
  QUERY PLAN
--
 Seq Scan on public.t  (cost=0.00..30.40 rows=2040 width=4) (actual
time=0.005..0.006 rows=1 loops=1)
   Output: t.col1
 Query Identifier: 8513308368843926789
 Planning Time: 0.030 ms
 Execution Time: 0.015 ms  -- no function call as col2 not part of select
from view
(5 rows)

Time: 0.222 ms


> --
>
Thanks,
Vijay
Mumbai, India

Re: postgres vacuum memory limits

[Vijaykumar Jain]

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

[Vijaykumar Jain]

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 should be limited
> to use the memory limits set by these parameters. But I am seeing more
> memory being used than these limits by autovacuum sessions, any reason why
> this would happen?
> >
> > Please see below examples, where maintenance_work_mem is set to 20mb and
> shared_buffers is 128mb. When I see the memory for this session in top, it
> shows 162mb. But when default_statistics_target is increased to 3000, the
> session usage is 463mb, which is way more than 20mb maintenance_work_mem
> and 128mb shared_buffer. Shouldn't the process memory be capped to 20+128mb?
> >
> >
> > postgres=# show maintenance_work_mem ;
> >  maintenance_work_mem
> > --
> >  20MB
> > (1 row)
> > postgres=# vacuum analyze mdm_context;
> > VACUUM
> > postgres=# show shared_buffers;
> >  shared_buffers
> > 
> >  128MB
> > (1 row)
> >
> >PID USER  PR  NIVIRTRESSHR S %CPU %MEM TIME+
> COMMAND
>
> >  62246 postgres  20   0  422892 165996 139068 R 57.1 15.7  25:06.34
> postgres: postgres postgres [local] VACUUM
> >
>
> your assumption may be right, but i am not sure of the interpretation from
> top.
> I have to admit I am not great at understanding top output (RES vs VIRT)
> in general when it comes to limits.
>
>
i just tried a couple of more things, maybe it helps.

every connection ( like psql in interactive mode ) has an overhead of
around 10MB.


postgres=# set maintenance_work_mem TO 1024;
SET
postgres=# -- 11284 this was showing in my RES mem on a fresh connection
postgres=# do $$
begin
for i in 1..20 loop
 update t set col1 = col1 || i::text;
commit;
end loop;
end; $$;
DO
postgres=# -- 394924 this was  showing in my RES mem on a connection that
did large updates, adding to connection cache ?
postgres=# vacuum t;
VACUUM
postgres=# -- 395852  this was  showing in my RES mem on a connection that
did vacuum, although the value is around 395M,
   -- but vacuum only took around  ~ 1M when
maintenance_work_mem was set to 1024 (1MB)

PostgreSQL connections are process based, and a lot goes into what is held
into the memories right since init, i did a pmap and lsof on the process id,
it touches a lot of files in datadir/base and datadir/global, basically the
pages touched during the session activities.

also there is a huge chunk allocated to
7f233b839000 2164816K rw-s- /dev/zero (deleted)
which I think is mmap to /dev/zero that contents have been deleted, but the
connection has to be closed to reclaim that space. idk

Re: postgres vacuum memory limits

[Vijaykumar Jain]

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 by autovacuum sessions, any reason why
this would happen?
>
> Please see below examples, where maintenance_work_mem is set to 20mb and
shared_buffers is 128mb. When I see the memory for this session in top, it
shows 162mb. But when default_statistics_target is increased to 3000, the
session usage is 463mb, which is way more than 20mb maintenance_work_mem
and 128mb shared_buffer. Shouldn't the process memory be capped to 20+128mb?
>
>
> postgres=# show maintenance_work_mem ;
>  maintenance_work_mem
> --
>  20MB
> (1 row)
> postgres=# vacuum analyze mdm_context;
> VACUUM
> postgres=# show shared_buffers;
>  shared_buffers
> 
>  128MB
> (1 row)
>
>PID USER  PR  NIVIRTRESSHR S %CPU %MEM TIME+
COMMAND

>  62246 postgres  20   0  422892 165996 139068 R 57.1 15.7  25:06.34
postgres: postgres postgres [local] VACUUM
>

your assumption may be right, but i am not sure of the interpretation from
top.
I have to admit I am not great at understanding top output (RES vs VIRT) in
general when it comes to limits.

I did a demo cgroup setup with limit max memory to 5MB, started psql using
cgexec and ran vacuum with maintenance_work_mem = 1024 (1MB)
it ran fine. I am not sharing the results, it may divert the convo.

The vacuum process seems to get dead tuples as a function of
maintenance_work_mem, and if it is small, it will loop many times (the run
may take longer)
but respect that limit (+- tolerance). also, vacuum verbose only prints CPU
usage per iteration of removing dead tupes from pages, but no mem usage. so
idk.

DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  vacuuming "public.t"
DEBUG:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
DEBUG:  scanned index "t_col1_idx" to remove 174518 row versions
DETAIL:  CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  starting parallel vacuum worker for bulk delete
DEBUG:  scanned index "t_col1_idx1" to remove 174518 row versions
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
DEBUG:  CommitTransaction(1) name: unnamed; blockState:
PARALLEL_INPROGRESS; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  "t": removed 174518 dead item identifiers in 1424 pages


i can be corrected, as i could not really get values from the source to
profile mem usage per function call.
from the source,
i have a feeling, shared_mem has nothing to do with vacuum, but i may be
wrong.

I think someone who is more aware of the process/code can throw more light.
but thanks for asking. will learn something figuring this out.

FROM THE CODE COMMENTS:
 * The major space usage for LAZY VACUUM is storage for the array of dead
tuple
 * TIDs.  We want to ensure we can vacuum even the very largest relations
with
 * finite memory space usage.  To do that, we set upper bounds on the
number of
 * tuples we will keep track of at once.
 *
 * We are willing to use at most maintenance_work_mem (or perhaps
 * autovacuum_work_mem) memory space to keep track of dead tuples.  We
 * initially allocate an array of TIDs of that size, with an upper limit
that
 * depends on table size (this limit ensures we don't allocate a huge area
 * uselessly for vacuuming small tables).  If the array threatens to
overflow,
 * we suspend the heap scan phase and perform a pass of index cleanup and
page
 * compaction, then resume the heap scan with an empty TID array.
 *
 * If we're processing a table with no indexes, we can just vacuum each page
 * as we go; there's no need to save up multiple tuples to minimize the
number
 * of index scans performed.  So we don't use maintenance_work_mem memory
for
 * the TID array, just enough to hold as many heap tuples as fit on one
page.
 *
 * Lazy vacuum supports parallel execution with parallel worker processes.
In
 * a parallel vacuum, we perform both index vacuum and index cleanup with
 * parallel worker processes.  Individual indexes are processed by one
vacuum
 * process.  At the beginning of a lazy vacuum (at lazy_scan_heap) we
prepare
 * the parallel context and initialize the DSM segment that contains shared
 * information as well as the memory space for storing dead tuples.  When
 * starting either index vacuum or index cleanup, 

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

[Vijaykumar Jain]

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-12/bin/
> -d //9.6/data.old -D //12/data/ -j 12 -r -v -k
>
> But, I am getting multiple errors during the "Restoring database schemas
> in the new cluster" step:
>
> pg_restore: creating TABLE "schema1.table1"
> pg_restore: creating TABLE "schema1.table2"
> pg_restore: creating TABLE "schema1.table3"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 358; 1259 793395 TABLE table3 postgres
> pg_restore: error: could not execute query: FATAL:  terminating connection 
> due to administrator command
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> Command was:-- For binary upgrade, must preserve pg_type oidSELECT 
> pg_catalog.binary_upgrade_set_next_pg_type_oid('793397'::pg_catalog.oid);
>
> -- For binary upgrade, must preserve pg_type array oidSELECT 
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('793396'::pg_catalog.oid);
>
> -- For binary upgrade, must preserve pg_class oidsSELECT 
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('793395'::pg_catalog.oid);
> CREATE TABLE "schema1"."table3" (
> "date_key" integer NOT NULL,
> "col1" character varying(32),
> "col2" character varying(32),
> "col3" character varying(32) NOT NULL
> );
> -- For binary upgrade, set
>
>
> ```
> LOG: background worker "logical replication launcher" (PID 35687) exited
> with exit code 1
> LOG: shutting down
> LOG: database system is shut down
> LOG: database system was shut down at 2021-07-30 01:33:51 GMT
> LOG: database system is ready to accept connections ERROR: relation
> "sequence2" already exists
> ```
>
>
> I am getting an error even after only initDB and running upgrade. Any
> suggestions on what could be going wrong?
>
> Thanks in advance!
>

Just asking, is it possible for you to a schema dump from old db and
restore on the newer db, does it throw any errors.
If the db is not really big, maybe dump db with data and restore it on the
newer db server ( use the 12.x postgresql cli for dump restore)

If both these go through fine, then it would be easy to take any unrelated
ddl, data issues out to focus on pg_upgrade.

Re: DO like block for for anonymous procedures

[Vijaykumar Jain]

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);
raise notice 'trying to insert %', i; commit;
end loop;
end; $$;
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function inline_code_block line 6 at SQL statement
postgres=# table t;
 id

  1
  2
  3
  4
  5
(5 rows)


sorry.

DO like block for for anonymous procedures

[Vijaykumar Jain]

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 x bigint;
begin
for i in 1..10 loop
select txid_current()::bigint into x;
raise notice '%', x;
end loop;
end
$$;
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
DO


 -- is it possible for a DO block to execute multiple txs
postgres=# create or replace procedure pp() as $$
declare x bigint;
begin
for i in 1..10 loop
select txid_current()::bigint into x;
commit;
raise notice '%', x;
end loop;
end; $$ language plpgsql;
CREATE PROCEDURE
postgres=# call pp();
NOTICE:  781
NOTICE:  782
NOTICE:  783
NOTICE:  784
NOTICE:  785
NOTICE:  786
NOTICE:  787
NOTICE:  788
NOTICE:  789
NOTICE:  790
CALL

one of the use case would be batch inserts, but from within a single psql
session

create table t(id int primary key);
postgres=# 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);
raise notice 'trying to insert %', i;
end loop;
end; $$;
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function inline_code_block line 6 at SQL statement

--- so everything got rolled back, as duplicate key, table t empty

postgres=# create or replace procedure proc_ins() as $$
declare valuelist int[] := ARRAY[1,2,3,4,5,1];
declare i int;
begin
for i in select k from unnest(valuelist) p(k) loop
insert into t values(i);
raise notice 'trying to insert %', i;
commit; -- explict commit, every insert in a new tx.
end loop;
end; $$ language plpgsql;
CREATE PROCEDURE

postgres=# call proc_ins();
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function proc_ins() line 6 at SQL statement
--only the erroneous data insert failed, but earlier committed data was
successful.

postgres=# table t;
 id

  1
  2
  3
  4
  5
(5 rows)


Ok, there might be better ways to do this using insert on conflict,
handling exceptions etc, but I hope you got my point.

I would go on and say DO block to  waste transactions to simulate
wraparound with minimum concurrent connections, but that would divert the
discussion,
hence just keeping it simple.
you can point me to docs, if i am missing the obvious reference for what a
DO serves  or what it was created for.

-- 
Thanks,
Vijay
Mumbai, India

Re: I need another option.

[Vijaykumar Jain]

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. Only then, one can provide some
helpful information.

also, look at original docs for pg_dump(all) and pg_restore  command line
options.

>

Re: pg-audit extension

[Vijaykumar Jain]

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 pgaudit logs on a different location/ separate
mount?

> pgaudit would log audit stuff to the same log file to which postmaster is
configured to log.
https://www.postgresql.org/docs/14/runtime-config-logging.html

https://github.com/pgaudit/pgaudit/blob/master/README.md

> I can be corrected, but I am not sure you can log to a standalone location
different from postgresql logs.
But if your concern is, if, incase of a crash you would lose your audit
logs, them in my personal opinion, you can either use rsyslog or use
something like filebeat to monitor changes to log file and send changes to
a centralized setup.
If I misunderstood you, ignore my suggestions.

Re: pg_restore (fromuser -> touser)

[Vijaykumar Jain]

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 is not flag in command line, but you always run the alter
 rename to   for role and schema post restore.
i can be corrected, if i am missing anything. i tried with pagilla db, but
just keeping it simple here.
PS: pg_hba.conf needs to be updated with new role/user post restore, or
else user will not be able to connect.

postgres@db:~/playground/demo$ createdb demo
postgres@db:~/playground/demo$ createuser demo
postgres@db:~/playground/demo$ pg_restore -d demo demo.db  -- some dummy
restore which has objects owned by demo user in demo schema

postgres@db:~/playground/demo$ psql demo
psql (14beta1)
Type "help" for help.

demo=# \dt demo.*
   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 demo   | t| table | demo
(1 row)

demo=# \ds demo.t_id_seq
  List of relations
 Schema |   Name   |   Type   | Owner
+--+--+---
 demo   | t_id_seq | sequence | demo
(1 row)

demo=# \df demo.*
 List of functions
 Schema |   Name| Result data type | Argument data types | Type
+---+--+-+--
 demo   | trig_func | trigger  | | func
(1 row)

demo=# \dnS demo
List of schemas
 Name | Owner
--+---
 demo | demo
(1 row)


---the below to sql statements will probably do what you want in your
restore command line flag, you need not edit dump file by hand imho

*demo=# alter schema demo rename to production;*
*ALTER SCHEMA*
*demo=# alter role demo rename to production;*
*ALTER ROLE*

--validate
demo=# \dnS demo  -- no more demo schema
List of schemas
 Name | Owner
--+---
(0 rows)

demo=# \dnS
 List of schemas
Name|   Owner
+
 information_schema | postgres
 pg_catalog | postgres
 pg_toast   | postgres
 production | production
 public | postgres
(5 rows)

demo=# \dt production.*
   List of relations
   Schema   | Name | Type  |   Owner
+--+---+
 production | t| table | production
(1 row)

demo=# \df production.trig_func
   List of functions
   Schema   |   Name| Result data type | Argument data types | Type
+---+--+-+--
 production | trig_func | trigger  | | func
(1 row)

demo=# \ds production.t_id_seq
   List of relations
   Schema   |   Name   |   Type   |   Owner
+--+--+
 production | t_id_seq | sequence | production
(1 row)

Re: regarding sql password auth

[Vijaykumar Jain]

> 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://www.postgresqltutorial.com/install-postgresql/
>

Re: Obsolete or dead serverconnections after reboot

[Vijaykumar Jain]

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 are rebuilt.
else queries may have some bad plans due to lack of estimates.
my assumption was, stats were corrupt, but it should have been there in the
logs
or the stats collector was broken.
autovacuum would do it, but it would take its own time.


> I added some interesting rows after two reboots (which have been
> complete power cycles)
>
> artea=# select pid ,application_name , client_addr ,client_port
> ,backend_start ,query_start,state from pg_stat_activity;
>pid  |  application_name   |  client_addr   |
> client_port | backend_start | query_start  | state
>
> ---+-+---+-+---+---+
>   6 | |   | | 2021-07-21
> 12:38:06.76295+02  |  |
>9320 | |   | | 2021-07-21
> 12:38:06.77185+02  |  |
>   11292 | psql|
> 2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59545 | 2021-07-22
> 07:52:20.110569+02 | 2021-07-22 07:52:24.727718+02 | idle
>9624 | arteasubartlt15wolleartlt34 |
> 2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59574 | 2021-07-22
> 07:56:16.235684+02 | 2021-07-22 07:56:16.278479+02 | active
>   11396 | psql| 192.168.2.49   |   59550
> | 2021-07-22 07:54:03.736197+02 | 2021-07-22 07:54:06.488585+02 | idle
>   10448 | psql|
> 2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59589 | 2021-07-22
> 07:58:14.715886+02 | 2021-07-22 07:59:01.652215+02 | active
>   15788 | |   | | 2021-07-21
> 12:38:06.736352+02 |  |
>   11216 | |   | | 2021-07-21
> 12:38:06.722957+02 |  |
>   14092 | |   | | 2021-07-21
> 12:38:06.739031+02 |  |
> (9 Zeilen)
>
> (Sorry for bad formatting)
>

you can use \x (extended mode on) on psql. it will dump the results in a
mode that can be pasted as text fine.


>
> So you can see we have two idle connections, which are those from before
> the reboots (one had a IPv4 name resolution and two did it by IPv6, psql
> commandline was the same). The backend_start is the same before and
> after reboot, so they are the same instances of connections.
>
>
I just installed EDB 13.3  on windows. It is managed as a windows service
(set as automatic), when I rebooted.

shutdown
the machine, it shutdown and terminated connections fine, and restarted
back fine.
2021-07-22 14:27:19.171 IST [4636] LOG:  disconnection: session time:
0:03:10.662 user=postgres database=postgres host=::1 port=53494
2021-07-22 14:27:21.805 IST [16120] ERROR:  canceling statement due to user
request
2021-07-22 14:27:21.810 IST [8080] LOG:  background worker "logical
replication launcher" (PID 16120) exited with exit code 1
2021-07-22 14:27:21.811 IST [8080] LOG:  received fast shutdown request
2021-07-22 14:27:21.813 IST [8080] LOG:  aborting any active transactions
2021-07-22 14:27:21.821 IST [11884] LOG:  shutting down
2021-07-22 14:27:21.841 IST [8080] LOG:  database system is shut down

startup
2021-07-22 14:28:01.373 IST [7268] LOG:  starting PostgreSQL 13.3, compiled
by Visual C++ build 1914, 64-bit
2021-07-22 14:28:01.376 IST [7268] LOG:  listening on IPv6 address "::",
port 5432
2021-07-22 14:28:01.378 IST [7268] LOG:  listening on IPv4 address
"0.0.0.0", port 5432
2021-07-22 14:28:01.505 IST [8228] LOG:  database system was shut down at
2021-07-22 14:27:21 IST
2021-07-22 14:28:01.550 IST [7268] LOG:  database system is ready to accept
connections

i do not see new connections open, until explicitly do so
then
2021-07-22 14:30:20.733 IST [15240] LOG:  connection received: host=::1
port=64579
2021-07-22 14:30:20.745 IST [15240] LOG:  connection authorized:
user=postgres database=postgres application_name=psql


the connections i created early on before reboot were terminated, and did
not show up in pg_stat_activity after reboot.
The event logs (i am not pasting screenshots) also show normal shutdown and
restart.
I do not see new psql based connections automatically created unless i open
psql manually.
can you try setting
log_connections = on
log_disconnections = on
in the postgresql.conf file (this would require a restart)
then open a few psql connections, and do a reboot,
does it log connection states in logs?

I scanned 

Re: Obsolete or dead serverconnections after reboot

[Vijaykumar Jain]

select pg_stat_reset();

Can you run above function, to check if stats are reset and things are fine
without a pg restart?
I not able to understand how new connection stats are added, along with old
stale states, if I assume stats collector process is having issues , or the
stats folder is corrupt 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 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 the machine is rebooted?
>
> You get the same pid and query, does it also has the same age( time since
> it started),
> I mean is the stats table updated with new data for stale connections or
> they remain static.
>
> Do you see the same issue when the machine is power cycled.
>
> Maybe windows might be preserving the memory state on disk and reading it
> back on reboot (like sleep) unless there are instructions to shutdown the
> db server on reboot. Idk.
>
> What are the state of the connections in pg_stat_activity abd process
> explorer before and after reboot. The sockets exists and active,  or are
> residual in pg stats only.
>
>
>

Re: Obsolete or dead serverconnections after reboot

[Vijaykumar Jain]

> 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 the machine is rebooted?

You get the same pid and query, does it also has the same age( time since
it started),
I mean is the stats table updated with new data for stale connections or
they remain static.

Do you see the same issue when the machine is power cycled.

Maybe windows might be preserving the memory state on disk and reading it
back on reboot (like sleep) unless there are instructions to shutdown the
db server on reboot. Idk.

What are the state of the connections in pg_stat_activity abd process
explorer before and after reboot. The sockets exists and active,  or are
residual in pg stats only.

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

[Vijaykumar Jain]

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
you to run manually

`vacuumdb  -a -v`  from the terminal, each time before you run your test
suite for some runs, do you still get the same issue?

I have a feeling repeated runs may have caused a lot of bloat on some
tables which might have not been reclaimed by autovacuum runs.

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

[Vijaykumar Jain]

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 seems the client is waiting on more data to arrive from the
> PostgreSQL server. A thread dump shows it waiting on the socket.
>
> On the server, I see the connection from the client. Looking at
> pg_stat_activity, I see it is in state 'active'. I have seen query_start
> be as old as eight hours ago. The documentation states that 'active'
> means a query is being executed. The query in question is:
>
> Looking at pg_locks, I only see locks with granted = true for the PID.
>

Is this reproducible, I mean this happens multiple times?
can you please run the below query in a separate session and share the
result, feel free to anonymize sensitive stuff.

SELECT db.datname, locktype, relation::regclass, mode, transactionid
AS tid, virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l
LEFT JOIN pg_catalog.pg_database db ON db.oid = l.database
WHERE NOT pid = pg_backend_pid();



> There is nothing in the logs as far as I can see. Configuration
> variables log_min_messages and log_min_error_statement are on 'debug1'.
> This is a snippet of the logs:
>
> 2021-07-09 20:35:16.374 CEST [30399] STATEMENT:  START TRANSACTION
> 2021-07-09 20:35:18.703 CEST [30399] WARNING:  there is already a
> transaction in progress
>
Are you having multiple BEGIN tx not matching commit/rollbacks ? not sure
related, but fixing this will help reduce noise.


>
> I am a bit out of ideas - does anyone have any tips where I should look
> to see what is causing the query to hang?
>

Although I am not an expert at this, I am exploring myself.
Can you collect backtrace for this pid?

Getting a stack trace of a running PostgreSQL backend on Linux/BSD -
PostgreSQL wiki


You can try enabling core dumps, then run a core dump using `gcore` on the
hanging pid (if you do not want to attach to a live process)
and generate a backtrace on that pid using `gdb` and share the trace.
I think that might help.

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 tables
> remain in the old format.
>
> Maybe in some cases where the type was changed it may be able to work with
> source tables of different types, but what if column was renamed in one
> source table but the column remained with the old name in the other source
> table?  What column name should the destination table have?  Do I need to
> duplicate the column to have both old and new names?
>

ADD NEW column: [1]
add a new column to the destination first and then to the source(s).
the WALs would be retained by the publisher till it can start publishing
again, so no data loss.

ALTER COL: [2]
imho, add a new column of the to be modified datatype to both destination
and source as above in [1]. then write a trigger for source tables to sync
the columns which need to have the type changed to the new column added in
the same source tables. let those changes get published to destination.
(for ex. check the int to bigint migration in PG, like
int_to_bigint_migration

and then drop the old column at source and destination.

DROP COL [3]:
dropping-columns

if there are apps making use of that column, first you would have to
ensure, the column is no longer in use in queries. then you can start by
dropping the col at source(s) and then the destination.

I have a setup that I have used with three source dbs and 1 dest db for
above. but things can get more complicated with FK constraints etc, so i am
not sure i have the best answer to this as i have not done it in production.
I have only used LR for migration and was thinking of (federated setup /to
unshard) where many shards -> LR -> one shard and when sharding was a bad
decision, but in both cases did not allow DDL changes at source till it was
completed.

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

[Vijaykumar Jain]

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.  Let's assume, for example, a column type needs to be
> changed (but solution needs to work for column addition, deletion, rename
> etc.).  What is the preferable approach:
>
>
>
>1. To add another column to the destination table where its type will
>be the new type.  Source tables, that have the new column type, will write
>to the new column.  While source tables with old column type will write to
>the old column.  A view will do the proper casting and will show a single
>column to user.
>2. Add a new table (versioned: table_name_v2) where source tables that
>have a new structure will write to the new destination table, while old
>source tables will write to the old destination table.   A view with UNION
>and casting will combine all tables.
>3. A better way?
>
>
Does the below not work for all alter table changes on publisher.
I have been testing on a demo setup pg14beta, and subscribers are able to
catch up fine.

on publisher (port 5001)
postgres=# alter table t alter COLUMN name type bigint using name::bigint;

on subscriber (port 5002)
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# alter subscription mysub refresh publication;

this should work for all the cases for ddl changes right.

-- demo

-- create table on pub and sub
postgres@db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# create table t(id int primary key, name text);
CREATE TABLE
postgres=# \q

postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# create table t(id int primary key, name text);
CREATE TABLE
postgres=# \q

-- insert dummy data to check sub rx changes
postgres@db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# insert into t values (1, 1::text);
INSERT 0 1
postgres=# \q

postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
 id | name
+--
(0 rows)
postgres=# alter subscription mysub refresh publication; -- this is because
i dropped table with publication enabled
ALTER SUBSCRIPTION
postgres=# table t;
 id | name
+--
  1 | 1
(1 row)
postgres=# \q

-- alter table alter column change type on pub
postgres@db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# \q

-- alter table alter column change type on sub
postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# \q

-- insert new data based on new column type
postgres@db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# insert into t values (2, 100);
INSERT 0 1
postgres=# \q

-- check new data on sub
postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
 id |  name
+-
  1 |   1
  2 | 100
(2 rows)
postgres=# \q


--alter table alter col type on pub and insert data
postgres@db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type text using name::text;
ALTER TABLE
postgres=# insert into t values (3, 'three');
INSERT 0 1
postgres=# \q

--alter table alter col type on sub, changes will not come till refresh pub
postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
 id |  name
+-
  1 |   1
  2 | 100
(2 rows)
postgres=#  alter table t alter COLUMN name type text using name::text;
ALTER TABLE
postgres=# table t;
 id |  name
+-
  1 | 1
  2 | 100
(2 rows)
postgres=# alter subscription mysub refresh publication; -- refresh would
get back changes
ALTER SUBSCRIPTION
postgres=# table t;
 id |  name
+-
  1 | 1
  2 | 100
  3 | three
(3 rows)

or
have I misunderstood the question?

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 not need to access the snap
> files. Is there a way to fine tune this behaviour?
>
> Thank you.
>
> Regards,
> Mike Yeap
>
> I guess the best people to ask this question is the core team, I am just
over a year in pg, I am not in a position to call this an issue with
postgres unless I understand the underlying design and possible data
corruption if any if it does not abort etc.
I'll leave it to the core team, then to give incorrect workaround.

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

[Vijaykumar Jain]

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 are there any related extensions or tools doing this?
>
>
postgresql has external  tools like barman that ship WALs to a different
location for point in time recovery.
That way, you can restore the db to any point in the past since the time
you were collecting WALs.

Barman Manual (pgbarman.org) 
if this is not the same, then please ignore the above :)


-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 well as some logical replication slots (for AWS
>> Database Migration Service (DMS)). This morning, the server went panic with
>> the following messages found in the log file:
>>
>> 2021-06-22 04:56:35.314 +08 [PID=19457 application="[unknown]"
>> user_name=dms database=** host(port)=**(48360)] PANIC:  could not open file
>> "pg_logical/snapshots/969-FD606138.snap": Operation not permitted
>>
>> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
>> host(port)=] LOG:  server process (PID 19457) was terminated by signal 6:
>> Aborted
>>
>> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
>> host(port)=] LOG:  terminating any other active server processes
>>
>
>
I just tried the below case, when a running logical replication is denied
access to the snapshot folder via chattr +i  to make the pg crash at
publisher.
Hence I was speculating, something was occured at the filesystem, this may
not be related, but just trying to get a scenario to simulate a repeatable
crash.

A (port 5001 publisher) -> logical replication all tables -> B(port 5002
subscriber), all working fine.

postgres@db:~/playground/logical_replication$ psql -p 5001 -c 'select
count(1) from t;'
 count
---
  1000
(1 row)

postgres@db:~/playground/logical_replication$ psql -p 5002 -c 'select
count(1) from t;'
 count
---
  1000
(1 row)

now i change file attributes of pg_logical folder  at the publisher

root@db:/home/postgres/playground/logical_replication/db1/pg_logical# cd
/home/postgres/playground/logical_replication/db1/pg_logical
root@db:/home/postgres/playground/logical_replication/db1/pg_logical# lsattr
--e- ./mappings
--e- ./snapshots
--e- ./replorigin_checkpoint
root@db:/home/postgres/playground/logical_replication/db1/pg_logical#
chattr -R +i *   # do not allow mod
root@db:/home/postgres/playground/logical_replication/db1/pg_logical# lsattr
i-e- ./mappings
i-e- ./snapshots
i-e- ./replorigin_checkpoint

 psql -p 5001 -c 'delete from t; checkpoint;'  # and crash

2021-06-24 00:22:36.998 IST [2899] LOG:  could not remove file
"pg_logical/snapshots/0-16CE9D8.snap": Operation not permitted
2021-06-24 00:22:36.998 IST [2899] LOG:  could not remove file
"pg_logical/snapshots/0-16E72B8.snap": Operation not permitted
2021-06-24 00:22:36.998 IST [2899] LOG:  could not remove file
"pg_logical/snapshots/0-16CE9A0.snap": Operation not permitted
2021-06-24 00:22:36.998 IST [2899] LOG:  could not remove file
"pg_logical/snapshots/0-16E7398.snap": Operation not permitted
2021-06-24 00:22:37.003 IST [2899] PANIC:  could not open file
"pg_logical/replorigin_checkpoint": Operation not permitted
2021-06-24 00:22:37.092 IST [2897] LOG:  checkpointer process (PID 2899)
was terminated by signal 6: Aborted
2021-06-24 00:22:37.092 IST [2897] LOG:  terminating any other active
server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
2021-06-24 00:22:37.093 IST [2897] LOG:  all server processes terminated;
reinitializing
postgres@db:~/playground/logical_replication$ 2021-06-24 00:22:37.100 IST
[2920] LOG:  database system was interrupted; last known up at 2021-06-24
00:20:54 IST
2021-06-24 00:22:37.100 IST [2921] FATAL:  the database system is in
recovery mode
2021-06-24 00:22:37.210 IST [2920] LOG:  could not open file
"./pg_logical/snapshots/0-16CE9D8.snap": Operation not permitted
2021-06-24 00:22:37.210 IST [2920] LOG:  could not open file
"./pg_logical/snapshots/0-16E72B8.snap": Operation not permitted
2021-06-24 00:22:37.210 IST [2920] LOG:  could not open file
"./pg_logical/snapshots/0-16CE9A0.snap": Operation not permitted
2021-06-24 00:22:37.210 IST [2920] LOG:  could not open file
"./pg_logical/snapshots/0-16EF1E0.snap": Operation not permitted
2021-06-24 00:22:37.210 IST [2920] LOG:  could not open file
"./pg_logical/snapshots/0-16E7398.snap": Operation not permitted
2021-06-24 00:22:37.2

Re: www.postgresql-archive.org content deleted

[Vijaykumar Jain]

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

Re: www.postgresql-archive.org content deleted

[Vijaykumar Jain]

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

[Vijaykumar Jain]

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 open any old archive thread, it says content deleted by the author.
*CONTENTS DELETED*
The author has deleted this message.

Is
this due to some activity/ intentional ?


-- 
Thanks,
Vijay
Mumbai, India

Re: second CTE kills perf

[Vijaykumar Jain]

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 row_number()
> over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32;
>
>
A simplified setup of the above issue.
I was able to reproduce this
postgres=# -- create table t(id int primary key, name text);
postgres=# -- insert into t select x, x::text from generate_series(1,
100) x;


--pushdown
postgres=# explain (analyze,buffers) with cte as (select * from t) select
*, row_number() over () from cte where id = 10;
  QUERY PLAN
-
 WindowAgg  (cost=0.42..8.46 rows=1 width=18) (actual time=0.014..0.016
rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
(actual time=0.010..0.011 rows=1 loops=1)
 Index Cond: (id = 10)
 Buffers: shared hit=4
 Planning Time: 0.074 ms
 Execution Time: 0.029 ms
(7 rows)

--no pushdown
postgres=# explain (analyze,buffers) with cte as (select * from t), cte2 as
(select *, row_number() over () from cte) select * from cte2 where id = 10;
   QUERY PLAN
-
 Subquery Scan on cte2  (cost=0.00..40405.00 rows=1 width=18) (actual
time=0.017..224.461 rows=1 loops=1)
   Filter: (cte2.id = 10)
   Rows Removed by Filter: 99
   Buffers: shared hit=609 read=4796
   ->  WindowAgg  (cost=0.00..27905.00 rows=100 width=18) (actual
time=0.012..185.554 rows=100 loops=1)
 Buffers: shared hit=609 read=4796
 ->  Seq Scan on t  (cost=0.00..15405.00 rows=100 width=10)
(actual time=0.007..45.168 rows=100 loops=1)
   Buffers: shared hit=609 read=4796
 Planning Time: 0.068 ms
 Execution Time: 224.479 ms
(10 rows)

-- without aggregate, pushdown works even with multiple ctes
(analyze,buffers) with cte as (select * from t), cte2 as (select * from cte
where id < 100) select * from cte2 where id = 10;
 QUERY PLAN
---
 Index Scan using t_pkey on t  (cost=0.42..8.45 rows=1 width=10) (actual
time=0.005..0.006 rows=1 loops=1)
   Index Cond: ((id < 100) AND (id = 10))
   Buffers: shared hit=4
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.074 ms
 Execution Time: 0.015 ms
(7 rows)

--with window aggregate, even at the top cte, predicate is not applied
 explain (analyze,buffers) with cte as (select *, row_number() over () from
t), cte2 as (select * from cte where id < 100)  select * from cte2 where id
= 10;
   QUERY PLAN
-
 Subquery Scan on cte  (cost=0.00..42905.00 rows=1 width=18) (actual
time=0.013..226.454 rows=1 loops=1)
   Filter: ((cte.id < 100) AND (cte.id = 10))
   Rows Removed by Filter: 99
   Buffers: shared hit=673 read=4732
   ->  WindowAgg  (cost=0.00..27905.00 rows=100 width=18) (actual
time=0.009..187.550 rows=100 loops=1)
 Buffers: shared hit=673 read=4732
 ->  Seq Scan on t  (cost=0.00..15405.00 rows=100 width=10)
(actual time=0.005..44.613 rows=100 loops=1)
   Buffers: shared hit=673 read=4732
 Planning Time: 0.055 ms
 Execution Time: 226.468 ms

--without cte predicate is applied before window aggregate ?
postgres=# explain (analyze,buffers) select *, row_number() over () from t
where id = 10;
   QUERY PLAN
-
 WindowAgg  (cost=0.42..8.46 rows=1 width=18) (actual time=0.018..0.020
rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
(actual time=0.013..0.014 rows=1 loops=1)
 Index Cond: (id = 10)
 Buffers: shared hit=4
 Planning Time: 0.053 ms
 Execution Time: 0.037 ms
(7 rows)


Thank you for raising this, I hope i'll gain something here.

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 following messages found in the log file:
>
> 2021-06-22 04:56:35.314 +08 [PID=19457 application="[unknown]"
> user_name=dms database=** host(port)=**(48360)] PANIC:  could not open file
> "pg_logical/snapshots/969-FD606138.snap": Operation not permitted
>
> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
> host(port)=] LOG:  server process (PID 19457) was terminated by signal 6:
> Aborted
>
> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
> host(port)=] LOG:  terminating any other active server processes
>

Are you sure there is nothing else, do you see anything in
/var/log/kern.log or dmesg logs.
 i just did a small simulation of logical replication from A -> B, i
deleted one of the snapshots live, i also changed permissions to make it RO
my server did not crash at all. (pg14beta though) although i can try other
things to check at pg layer, but if something else externally has happened,
it would be difficult to reproduce.
pardon me for speculating, but
Is it network storage? did the underlying storage layer have a blip of some
kind?
are the mounts fine? are they readonly or were temporarily readonly ?
no bad hardware ?
If none of the above, did the server restart solve the issue? or is it
broken still, unable to start?


> The PG server then terminates all existing PG processes.
>
> The process with 19457 is from one of the DMS replication tasks, I have no
> clue why it suddenly couldn't open a snapshot file. I checked the server
> load and file systems and didn't find anything unusual at that time.
>
> Appreciate if you can give me some guidance on troubleshooting this issue
>
> Thanks
>
> Regards,
> Mike Yeap
>

is it crashing and dumping cores?
can you strace the postmaster on its startup to check what it going on ?

I can share my demo setup, but it would be too noisy in the thread, but can
do it later if you want.
the above assumptions are based on repmgnr and AWS do not interfere in your
primary server internals, just failover and publication.


-- 
Thanks,
Vijay
Mumbai, India

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

[Vijaykumar Jain]

>
> 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 explicitly override default_transaction_read_only.
> That seems like a darn bad idea, really.  If pg_dump thinks it's
> authorized to ignore that, why shouldn't every other application?
>
> Also, doing so would result in ignoring default_transaction_read_only
> no matter what the source of that was.  I think it's probably not
> hard to construct scenarios where someone would really not be happy
> about such behavior.
>
> In short, I'm inclined to say "don't do that".  Maybe it'd be
> a better idea to apply default_transaction_read_only to particular
> roles (and not run pg_restore under such a role).
>
> regards, tom lane
>

is the below usecase relevant to this setup

i have db example which i want to migrate from one version to another.
to do that i disable writes on the db by setting the tx flag, so that i can
be sure it is still serving reads, but no additional data is written.
i can take a pg_dump, the db is still serving the reads.
now when i do a restore on a higher verison, this fails, (ofcourse i can
edit the dump and remove the setup),

but is this above usecase logical for doing it.




-- 
Thanks,
Vijay
Mumbai, India

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

[Vijaykumar Jain]

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_only=on.
>>
>> It would seem the restore script lacks a
>>
>> SET default_transaction_read_only TO 'off';
>>
>> in the setup section after re-connecting to the DB following
>> the ALTER DATABASE section ?
>>
>> Thanks,
>> Karsten
>> --
>> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>>
>>
>>
> i just tested as below.
>
> postgres@db:~/playground/logical_replication$ rm -rf example
> postgres@db:~/playground/logical_replication$ initdb -D example
> 2>/dev/null >/dev/null
> postgres@db:~/playground/logical_replication$ vim example/postgresql.conf
> postgres@db:~/playground/logical_replication$ printf
> "default_transaction_read_only=on\n" >> example/postgresql.conf  #global
> config setting as on
> postgres@db:~/playground/logical_replication$ pg_ctl -D example -l
> logfile start
> waiting for server to start done
> server started
> postgres@db:~/playground/logical_replication$ psql
> psql (14beta1)
> Type "help" for help.
>
> postgres=# show default_transaction_read_only;  -- validate it is on
>  default_transaction_read_only
> ---
>  on
> (1 row)
>
> postgres=# \q
> postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql
> postgres@db:~/playground/logical_replication$ grep
> default_transaction_read_only dump.sql -- check what gets dumped
> SET default_transaction_read_only = off;
>
> but this is 14beta1.
>
> Did I simulate your concern correctly ?
>
>
ok i do not know if that is a bug ?
but i am able to reproduce your concern now alter database

postgres@db:~/playground/logical_replication$ rm -rf example
postgres@db:~/playground/logical_replication$ initdb -D example 2>/dev/null
>/dev/null
postgres@db:~/playground/logical_replication$ pg_ctl -D example -l logfile
start
waiting for server to start done
server started
postgres@db:~/playground/logical_replication$ createdb example
postgres@db:~/playground/logical_replication$ psql example -c 'create table
t(id int); insert into t select 1; '
INSERT 0 1
postgres@db:~/playground/logical_replication$ psql example -c 'show
default_transaction_read_only;'
 default_transaction_read_only
---
 off
(1 row)

postgres@db:~/playground/logical_replication$ psql example -c 'ALTER
DATABASE example SET default_transaction_read_only TO ''on'';'
ALTER DATABASE
postgres@db:~/playground/logical_replication$ psql example -c 'show
default_transaction_read_only;'
 default_transaction_read_only
---
 on
(1 row)

postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres@db:~/playground/logical_replication$ grep
default_transaction_read_only dump.sql
SET default_transaction_read_only = off;
ALTER DATABASE example SET default_transaction_read_only TO 'on';
postgres@db:~/playground/logical_replication$ dropdb example
postgres@db:~/playground/logical_replication$ psql < dump.sql

ERROR:  cannot execute CREATE TABLE in a read-only transaction
ERROR:  cannot execute ALTER TABLE in a read-only transaction
ERROR:  relation "public.t" does not exist
invalid command \.
You are now connected to database "postgres" as user "postgres".
ERROR:  syntax error at or near "1"

so the table did not get restored, as default_transaction_read_only = on.
so this is the same in 14 as well.

you can load sections via pg_restore and skip this TOC
 --section=SECTIONrestore named section (pre-data, data, or
post-data)

maybe you know that, and are just asking if this is a feature or a bug.




-- 
Thanks,
Vijay
Mumbai, India

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

[Vijaykumar Jain]

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-connecting to the DB following
> the ALTER DATABASE section ?
>
> Thanks,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>
i just tested as below.

postgres@db:~/playground/logical_replication$ rm -rf example
postgres@db:~/playground/logical_replication$ initdb -D example 2>/dev/null
>/dev/null
postgres@db:~/playground/logical_replication$ vim example/postgresql.conf
postgres@db:~/playground/logical_replication$ printf
"default_transaction_read_only=on\n" >> example/postgresql.conf  #global
config setting as on
postgres@db:~/playground/logical_replication$ pg_ctl -D example -l logfile
start
waiting for server to start done
server started
postgres@db:~/playground/logical_replication$ psql
psql (14beta1)
Type "help" for help.

postgres=# show default_transaction_read_only;  -- validate it is on
 default_transaction_read_only
---
 on
(1 row)

postgres=# \q
postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres@db:~/playground/logical_replication$ grep
default_transaction_read_only dump.sql -- check what gets dumped
SET default_transaction_read_only = off;

but this is 14beta1.

Did I simulate your concern correctly ?


-- 
Thanks,
Vijay
Mumbai, India

Re: pgTAP installation

[Vijaykumar Jain]

>
>
> 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
LINE 1: select * from noplan();
  ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
tt=# SELECT * FROM no_plan();
 no_plan
-
(0 rows)

-- 
Thanks,
Vijay
Mumbai, India

Re: pgTAP installation

[Vijaykumar Jain]

ignore,
I was wrong.
the script gets loaded by enabling the extension.

postgres@db:~/postgresql-14beta1/contrib/pgtap$ dropdb test
postgres@db:~/postgresql-14beta1/contrib/pgtap$
postgres@db:~/postgresql-14beta1/contrib/pgtap$ createdb test
postgres@db:~/postgresql-14beta1/contrib/pgtap$ psql 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 the script to make use of the functions.
> pgTAP: Documentation <https://pgtap.org/documentation.html#installation>
> where it says
> -- Load the TAP functions.
>
>
This was a wrong suggestion. sorry.
i'll try to reproduce what resulted in functions not getting loaded in your
case.

-- 
Thanks,
Vijay
Mumbai, India

Re: pgTAP installation

[Vijaykumar Jain]

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 explicit type casts.
>
> Am I missing a step somewhere?
>
> Many thanks in advance,
>
> Ray.
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
>
>
postgres@db:~/postgresql-14beta1/contrib/pgtap$ psql test
psql (14beta1)
Type "help" for help.

test=# drop extension pgtap;
DROP EXTENSION
test=#
test=# \df
   List of functions
 Schema | Name | Result data type | Argument data types | Type
+--+--+-+--
(0 rows)

test=# \i './sql/pgtap.sql'  -- Load the TAP functions.
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

test=# \df  -- check functions loaded

 List of functions
 Schema | Name  |Result data type|
 Argument data types
| Type
+---++--
+--
 public | _add  | integer| text,
integer
| func
 public | _add  | integer| text,
integer, text


test=# SELECT * FROM no_plan();
 no_plan
-
(0 rows)



I think you need to load the script to make use of the functions.
pgTAP: Documentation 
where it says
-- Load the TAP functions.



-- 
Thanks,
Vijay
Mumbai, India

Re: compute_query_id

[Vijaykumar Jain]

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 :'
> > 0 :LOG:  statement: select * from pg_stat_activity;
>
> It means that you haven't enabled it:
>
> 2021-06-17 22:46:16.231 CST [11246] queryid=0 LOG:  duration: 4.971 ms  
> statement: select * from pg_stat_activity ;
> 2021-06-17 22:46:25.383 CST [11246] queryid=0 LOG:  duration: 0.284 ms  
> statement: set compute_query_id = on;
> 2021-06-17 22:46:28.744 CST [11246] queryid=941978042436931562 LOG:  
> duration: 1.725 ms  statement: select * from pg_stat_activity ;
>

 psql test
psql (14beta1)
Type "help" for help.

test=# show log_line_prefix;
log_line_prefix

 [timestamp=%t] [query_id=%Q] :
(1 row)

test=# show compute_query_id;
 compute_query_id
--
 on
(1 row)

test=# show log_statement;
 log_statement
---
 all
(1 row)

test=# select query_id, query, pid from pg_stat_activity where pid =
pg_backend_pid();
-[ RECORD 1 
]-
query_id | -4293879703199833131
query| select query_id, query, pid from pg_stat_activity where pid
= pg_backend_pid();
pid  | 2640


from the logs, i get the id for some queries, but not all
[timestamp=2021-06-17 20:37:59 IST] [query_id=488416992746849793]
:ERROR:  relation "t" already exists
[timestamp=2021-06-17 20:37:59 IST] [query_id=488416992746849793]
:STATEMENT:  create table t(id int);
[timestamp=2021-06-17 20:38:22 IST] [query_id=0] :LOG:  statement:
prepare qq(int) as select count(1) from t where id = $1;
[timestamp=2021-06-17 20:38:29 IST] [query_id=0] :LOG:  statement:
execute qq(1);
[timestamp=2021-06-17 20:38:29 IST] [query_id=0] :DETAIL:  prepare:
prepare qq(int) as select count(1) from t where id = $1;
[timestamp=2021-06-17 20:38:32 IST] [query_id=0] :LOG:  statement:
execute qq(2);
[timestamp=2021-06-17 20:38:32 IST] [query_id=0] :DETAIL:  prepare:
prepare qq(int) as select count(1) from t where id = $1;
[timestamp=2021-06-17 20:39:25 IST] [query_id=0] :LOG:  statement:
select query_id, query, pid from pg_stat_activity where pid = 0;
[timestamp=2021-06-17 20:40:36 IST] [query_id=0] :LOG:  statement:
select count(1) from t;
[timestamp=2021-06-17 20:40:47 IST] [query_id=0] :LOG:  statement:
select count(1) from t where id < 100;

test=# explain (analyze,verbose) select * from t where id <
floor((random() * 100)::int);
QUERY PLAN
---
 Append  (cost=0.00..3.10 rows=3 width=4) (actual time=0.009..0.014
rows=3 loops=1)

 Query Identifier: 1051405225525186795
 Planning Time: 0.090 ms
 Execution Time: 0.030 ms
(13 rows)

test=# select query_id, query, pid from pg_stat_activity where pid =
pg_backend_pid();
   query_id   |  query
 | pid
--+-+--
 -4293879703199833131 | select query_id, query, pid from
pg_stat_activity where pid = pg_backend_pid(); | 2671
(1 row)

but in logs
[timestamp=2021-06-17 20:46:47 IST] [query_id=0] :LOG:  statement:
explain select query_id, query, pid from pg_stat_activity where pid =
pg_backend_pid();
[timestamp=2021-06-17 20:46:54 IST] [query_id=0] :LOG:  statement:
explain select query_id, query, pid from pg_stat_activity where pid >
100;
[timestamp=2021-06-17 20:46:58 IST] [query_id=0] :LOG:  statement:
explain analyze select query_id, query, pid from pg_stat_activity
where pid > 100;
[timestamp=2021-06-17 20:47:25 IST] [query_id=0] :LOG:  statement:
explain analyze select * from t where id <  floor((random() *
100)::int);
[timestamp=2021-06-17 20:48:16 IST] [query_id=0] :LOG:  statement:
explain (analyze,verbose) select * from t where id <  floor((random()
* 100)::int);
[timestamp=2021-06-17 20:48:38 IST] [query_id=0] :LOG:  statement:
select query_id, query, pid from pg_stat_activity where pid =
pg_backend_pid();

not sure, if i am missing something obvious?

> I'm not sure that I understand that question.  The pid will identify a 
> backend,
> and that backend can execute 0, 1 or a lot of different queries.  The query_id
> will uniquely identify statements after some normalization and removing the
> constant parts (so for instance "select 1;" and "Select 2  ;" will have the
> same identifier).  Having only that information in the log can be useful on 
> its
> own, but you usually get way more benefit using additional modules like
> pg_stat

compute_query_id

[Vijaykumar Jain]

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 compute_query_id on.
i also read
compute_query_id_query
  for
the same.
how is the compute_query_id actually calculated?
why does it show 0 in logs for random sql queries.
log_line_prefix = '%Q :'
0 :LOG:  statement: select * from pg_stat_activity;

i mean pid already was doing the job to identify the query and its children
even it logs,
but i know pid will get recycled.
queryjumble.c


tldr;
how is compute_query_id different from pid to identify some query running ?
can it be passed on to FDW queries ? for tracing etc ?

am i totally getting its use case totally wrong :)


-- 
Thanks,
Vijay
Mumbai, India

Re: Streaming replication: PANIC on tertiary when secondary promoted

[Vijaykumar Jain]

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

initdb -D primary
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

postgres@db:~/playground$ pg_ctl -D primary -l logfile start
waiting for server to start done
server started
postgres@db:~/playground$ psql
psql (14beta1)
Type "help" for help.

postgres=# select pg_create_physical_replication_slot('replica');
 pg_create_physical_replication_slot
-
 (replica,)
(1 row)

postgres=# create table t(id int primary key);
CREATE TABLE
postgres=# insert into t select x from generate_series(1, 100) x; checkpoint;
INSERT 0 100
postgres=# \q

-- create a replica
postgres@db:~/playground$ pg_basebackup -D replica -R -X stream -S
replica -v -d "dbname=postgres port=5432" -U postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/228 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim replica/postgresql.conf

--start the replica (port 5433)
postgres@db:~/playground$  pg_ctl -D replica -l replicalog start
waiting for server to start done
server started
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
---
   100
(1 row)

postgres@db:~/playground$ psql -p 5433
psql (14beta1)
Type "help" for help.


-- create a replica slot for cascading streaming replication
postgres=# select pg_create_physical_replication_slot('cascading_replica');
 pg_create_physical_replication_slot
-
 (cascading_replica,)
(1 row)

postgres=# \q

-- create a cascading replica off replica

postgres@db:~/playground$ pg_basebackup -D cascading_replica -R -X
stream -S cascading_replica -v -d "dbname=postgres port=5433" -U
postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/328 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/3D8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim cascading_replica/postgresql.conf
postgres@db:~/playground$ pg_ctl -D cascading_replica -l creplica start
waiting for server to start done
server started

-- validate receiving data fine.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
---
   100
(1 row)


-- stop primary
postgres@db:~/playground$ pg_ctl -D primary -l logfile stop
waiting for server to shut down done
server stopped


-- promote replica to new primary
postgres@db:~/playground$ psql -p 5433
psql (14beta1)
Type "help" for help.

postgres=# select pg_promote();
 pg_promote

 t
(1 row)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
---
 f
(1 row)

postgres=# \q

--do some dml, validate changes replayed to new replica.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
---
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
---
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'delete from t where id < 50;'
DELETE 49
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
---
51
(1 row)

postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
---
51
(1 row)


in all my cases. recovery_timeline was set to 'latest'.
i did not rx any panic messages in logs.

Re: clear cache in postgresql

[Vijaykumar Jain]

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 caching. Can I somehow see what that cache
> > is holding?
>
> You can use pgbuffercache for that:
> https://www.postgresql.org/docs/current/pgbuffercache.html
>
pgbuffercache is useful, but you also need to check the explain plan to
help understand if it just cache issue or something else.
i mean for cases where change in buffers hit vs buffers read, then maybe
cache is helping.
when you have time, you can find this very helpful.
pg internals 

test=# create table t(id int, name text, primary key(id));
CREATE TABLE
test=# insert into t select x, md5(x::text) from generate_series(1,
100) x;
INSERT 0 100
test=# explain (analyze,buffers) select * from t where id < 1;
QUERY PLAN
--
 Bitmap Heap Scan on t  (cost=6394.67..19138.75 rows=352806 width=36)
(actual time=0.433..1.750 rows= loops=1)
   Recheck Cond: (id < 1)
   Heap Blocks: exact=84
   Buffers: shared hit=1 read=113 dirtied=84
   ->  Bitmap Index Scan on t_pkey  (cost=0.00..6306.47 rows=352806
width=0) (actual time=0.408..0.408 rows= loops=1)
 Index Cond: (id < 1)
 Buffers: shared hit=1 read=29
 Planning:
   Buffers: shared hit=1 read=8
 Planning Time: 0.226 ms
 Execution Time: 2.233 ms
(11 rows)

test=# explain (analyze,buffers) select * from t where id < 1;
QUERY PLAN
--
 Bitmap Heap Scan on t  (cost=6394.67..19138.75 rows=352806 width=36)
(actual time=0.419..1.447 rows= loops=1)
   Recheck Cond: (id < 1)
   Heap Blocks: exact=84
   Buffers: shared hit=114
   ->  Bitmap Index Scan on t_pkey  (cost=0.00..6306.47 rows=352806
width=0) (actual time=0.399..0.400 rows= loops=1)
 Index Cond: (id < 1)
 Buffers: shared hit=30
 Planning Time: 0.145 ms
 Execution Time: 1.921 ms
(9 rows)


and then check buffercache


SELECT n.nspname, c.relname, count(*) AS buffers
 FROM pg_buffercache b JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
  WHERE datname = current_database()))
 JOIN pg_namespace n ON n.oid = c.relnamespace
 GROUP BY n.nspname, c.relname
 ORDER BY 3 DESC
 LIMIT 5;
  nspname   |  relname  | buffers
+---+-
 public | t |3004
 public | t_pkey| 816
 pg_catalog | pg_proc   |  20
 pg_catalog | pg_depend_reference_index |  13
 pg_catalog | pg_attribute  |  13


> > Can I force all caches to be cleared for tuning purposes?
> > So I need to clear the cache without restarting/rebooting the postgres
> > server to check the correct execution plan of my query.
>
> No, cleaning postgres cache can only be done with a service restart.  That
> being said, tuning shouldn't be done assuming that there's no cache.  On
the
> opposite maybe what you should do is use pg_prewarm
> (https://www.postgresql.org/docs/current/pgprewarm.html) to make sure
that your
> cache isn't empty after a restart.
>


yes, flushing cache requires a restart. but you can also flush cache, by
repeatedly querying huge tables which are much larger than your buffers and
not relevant to your query,
that way the cache contents would be replaced by new content.

also if you can play around by logging onto your machine (do not do in
production, it will flush all cache which may be in use elsewhere)  as root.

stop postgresql
sync
echo 3 > /proc/sys/vm/drop_caches
start postgresql

you can try by keeping the shared_buffers/effective_cache_size value low,
to ensure not a lot of pages are pulled up into the buffers.


Thanks,
Vijay
Mumbai, India

Re: immutable function querying table for partitioning

[Vijaykumar Jain]

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 in the documentation
instead of building you system upon a lie that your immutable function is
actually a stable one.
>
> David J.

Yes I do understand that. That was just  a simple example.
I am just playing with approaches, this is not a work requirement.
This would be a simulation for sharded setup across servers.
In reality I would be moving only the relevant data and run a truncate to
get rid of bloat once data is moved. but that was not the point.

I wanted sharding to be an iterative setup. as I will make bad decisions.
I want to be able to avoid hotspots as well as, too much scatter gather.
I tried the fdw approach, the problem with it is , it opens a cursor, then
runs through all the shards sequentially. when we have too many shards, it
suffers linear degradation.

I also tried dblink to run an async scatter gather where aggregation/ limit
could not be pushed down.

and it has a lot counting on fetch_size across shards. I mean if it too
low, it takes forever to get data that does not get reduced at remote
server.
it is too high the transfer is fast, but  random query result transferring
a lot of data results in oom. I know i have to tune these things, but i
cannot anticipate data growth.

I was thinking of a way where I could have all related data in one
partition.
i mean if i am having a setup with highly normalized tables, i could use a
custom function to ensure all related data remains in one shard.

while studying approaches to how others shard, i saw this in oracle. when
all related data is in one shard which uses partition by reference,
since we do not have that in postgres, i thought i could use a custom
function.
sharding-schema-design.html

but these are experiments. I am not losing anything if I fail :)
I want to figure out  ways to rebalance without downtime.


-- 
Thanks,
Vijay
Mumbai, India

immutable function querying table for partitioning

[Vijaykumar Jain]

 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 from generate_series(1, 10) x;
INSERT 0 10
test=# insert into pt select x, 2 from generate_series(10, 20) x;
INSERT 0 11
test=# insert into pt select x, 3  from generate_series(20, 30) x;
INSERT 0 11

test=# table pt;
 id | sh
+
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

-- create a function that would lookup the id and return sh number

test=# create or replace function get_shard(t_id int) returns int as $$
test$# begin
test$# declare shno int;
test$# select distinct sh into shno from pt where id = t_id;
test$# return shno;
test$# end; $$ language plpgsql;
CREATE FUNCTION
test=# select get_shard(10);
 get_shard
---
 1
(1 row)

test=# select get_shard(30);
 get_shard
---
 3
(1 row)

-- ok it seems the function has to be immutable, so i am just making it one
-- despite querying a lookup table that would have contents changed later

test=# create table t (id int) partition by list ( get_shard(id) );
ERROR:  functions in partition key expression must be marked IMMUTABLE
test=# create or replace function get_shard(t_id int) returns int as $$
declare shno int;
begin
select distinct sh into shno from pt where id = t_id;
return shno;
end; $$ language plpgsql IMMUTABLE;
CREATE FUNCTION

-- partition table using the lookup function
test=# create table t (id int) partition by list ( get_shard(id) );
CREATE TABLE

-- if function returns 1 then partition t1 and so on
test=# create table t1 partition of t for values in ( 1 );
CREATE TABLE
test=# create table t2 partition of t for values in ( 2 );
CREATE TABLE
test=# create table t3 partition of t for values in ( 3 );
CREATE TABLE

-- validate partitioning is working by inserting data
test=# insert into t select x from generate_series(1, 25) x;
INSERT 0 25
test=# \dt t1
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | t1   | table | postgres
(1 row)

test=# table t1
test-# ;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t1;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id

 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
(10 rows)

test=# table t3;
 id

 21
 22
 23
 24
 25
(5 rows)

-- now we want to move all data from sh=3 to sh=2, so we update lookup table
test=# table pt;
 id | sh
+
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

test=# update pt set sh = 2 where sh = 3;
UPDATE 11

--- now since the lookup table is update, a noop update would get new
shards for ids and rebalance them accordingly.

test=# update t set id = id ;
UPDATE 25

-- validate tupes moved to respective new shards.
test=# table t1;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id

 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
(15 rows)

test=# table t3;
 id

(0 rows)



now,

"IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its argument list. If this option is given, any call of
the function with all-constant arguments can be immediately replaced with
the function value."

"IMMUTABLE must be a pure function, whose results depend only on its
inputs. This is a very strict requirement; they cannot call other
non-immutable functions, they cannot access tables, they cannot access the
value of configuration properties, etc"

I am making a table query in an immutable function, which is used for
partitioning.
although it does not result in error and it works, what is it that might
cause trouble.
ignore the performance issues, i understand having an access exclusive
table lock on pt would block partition forever.


Also, I plan to update shard lookup only when there are not DML on
partition tables.
What am I doing wrong.

ignore, as always, if it does not make sense :)



-- 
Thanks,
Vijay
Mumbai, India

Re: How to generate file from postgres data

[Vijaykumar Jain]

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
issues in the api.
so if it is how to render 1gb  db worth data on a web browser, then ignore
my answer.

Re: How to generate file from postgres data

[Vijaykumar Jain]

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 directly
COPY 49
test=# \q
postgres@db:~/playground$ head -5 /tmp/report.csv
id,value
49,49
48,48
47,47
46,46

and send the csv tar zip compressed etc.

How does your client intend to load 1gb report in a viewer ? paginated ?
all at once ?

Re: Implicit table removal from logical replication publication

[Vijaykumar Jain]

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 is no view of metric for individual relation level subscription
to help figure out if it is broken.

I mean like for replication slots, we have an active flag to easily figure
out issues with replication, but not with pub/sub.
I only used logical replication for upgrades, so not have little idea wrt
ddl changes issues.

Re: Implicit table removal from logical replication publication

[Vijaykumar Jain]

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 this is not an issue but a feature ?
i'll check more on the better part of monitoring logical replication stuff.

but for your case,
you can set up an event trigger that would avoid dropping the table.
basically any drop of a table or any ddl that would break publication.

functions-event-triggers

event-trigger-definition

how-use-event-triggers-postgresql


you can have a custom query filter that would prevent dropping of objects
part of publication accidentally.

and then you want to exclusively drop the table, once not part of
publication, you have to first remove the table from publication and then
drop.

I have not run this in production, so I believe others may chime in, but
logical replication issues from logs are not the best.
I am happy to be corrected.
I'll update on more scenarios.
 more logical_replication_example.sh
initdb -D 12/rep1 >/dev/null 2>&1
initdb -D 12/rep2 >/dev/null 2>&1

printf "port = 6111 \nwal_level = logical\n log_statement='all'\n" >> 
12/rep1/postgresql.auto.conf
printf "port = 7111 \nwal_level = logical\n log_statement='all'\n" >> 
12/rep2/postgresql.auto.conf

pg_ctl -l rep1.log -D 12/rep1 start
pg_ctl -l rep2.log -D 12/rep2 start

psql -p 6111 -c "create database source_rep;"

cat > source_rep.sql < target_rep.sql <

Re: order by

[Vijaykumar Jain]

> 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;
 QUERY PLAN
-
 Sort  (cost=158.51..164.16 rows=2260 width=40) (actual
time=0.005..0.006 rows=0 loops=1)
   Output: id, value, t.*
   Sort Key: t.*
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on public.t  (cost=0.00..32.60 rows=2260 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
 Output: id, value, t.*
 Planning Time: 0.033 ms
 Execution Time: 0.044 ms
(8 rows)

postgres=# truncate table t;
TRUNCATE TABLE
postgres=# insert into t values (100, 1);
INSERT 0 1
postgres=# insert into t values (50, 2);
INSERT 0 1
postgres=# select * from t order by t;
 id  | value
-+---
  50 | 2
 100 | 1
(2 rows)

postgres=# select * from t order by t.id, t.value;
 id  | value
-+---
  50 | 2
 100 | 1
(2 rows)

Re: PostgreSQL replication lag - Suggestions and questions

[Vijaykumar Jain]

> 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 queries to go to AZ2 node, both of them
should be able to server the queries.
with libpq 10 onwards, you can provide multiple nodes in the connection
string, which would "automatically" figure out next available node in to
query if one is not responding.
See
PostgreSQL: Documentation: 13: 33.1. Database Connection Control Functions



> All read-only queries are sent to the read slave. Sometimes the
replication lag between the master and the slaves reaches up to 10
minutes.I understand that the lag is expected in any replication scenario,
and below you can find some suggestions that I think would help to minimize
the lag time.

lags can be a result of multiple reasons like,
Intermittent connection issues,
network unable to keep up with WAL changes. A typical example would be any
action that performs a table rewrite of a huge table.
this can result in query getting cancelled on the replica and it may think
you might query stale data.
you can tune the replica with these params if you want to avoid query
cancellations but are ok with little stale data.
also if the application can initiate a retry for the cancelled query
exception if they can handle at app layer itself.
RUNTIME-CONFIG-REPLICATION-STANDBY


If you cannot deal with stale data at all, then you have two options.
1) synchronous replication
synchronous replication in very simple terms would mean, if you have a
cluster like
Primary -> ( Replica R1, Replica R2)  the primary would wait for
acknowledgement from at least one of the replicas. this may impact your
tps, but you get consistency.
but geo setups with synchronous replication would be problematic.
2) read from the primary alone.
this is always the safest option, as no intermediate network, but this
would also mean all the read load on the primary will impact the db
performance and the housekeeping work like
vacuuming. and incase the server goes down, till the time you failover, you
cannot perform any read or writes depending on the catch up time.
SYNCHRONOUS-REPLICATION



> Having the read slave in the same AZ as its master - for better network
throughput;
AZ spread is meant for dealing with availability. It may be ok to have some
read replica in some AZ, but also have some in another AZ in case of a
major outage.
if you can use a load balancer, you can provide more weight to local
replicas then the remote replicas etc and use a health check like
replication lag to ensure read request
are served by nodes with tolerable lag only.
scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/


> Having the latest PostgreSQL version to get its best performance
mostly always. not just improvements, but a lot of new features like
partitioning, logical replication, stored procedures etc are now available
in newer versions.
PostgreSQL: Release Notes 

> Having the latest Operational System behind PostgreSQL to get its best IO
performance
yes at the software layer, latest versions would be more performant
"mostly".
but still the hardware layer plays an important role.
If you want to really validate improvements, you can run pgbench/sysbench
stress tests on the interested versions and see the changes yourself.

upgrade is always needed. not just for performance reasons, but security
reasons as well.
Also,  keeping the upgrade on hold for a time, may mean jumping across
multiple versions which although might work,
but you would need to read the change log across the versions to ensure
nothing would be broken,
Hence regular upgrades avoid the pain.


-- 
Thanks,
Vijay
Mumbai, India

Re: JDBC error: Unexpected packet type: 25

[Vijaykumar Jain]

" 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

pgjdbc/QueryExecutorImpl.java at REL42.2.10 · pgjdbc/pgjdbc (github.com)


I may be diverting that may not be related but a few things, just in case.
1) we once had a scenario of a bad nic driver e1000, that resulted in a lot
of pkt losses.
We figured out there were too many pkt drops and upgraded the driver to
vmnet or something.

2)You should also raise the same with pgjdbc. I see they have resolved
issues with other mismatched packet types like 52 at their end.

3) I have had some arguments wrt pgjdbc/npgsql having issues with pgbouncer
and not with postgresql directly. I counter by running the same queries
using psql  with pgbouncer. never got an error. But I am not an expert with
Java so I keep an open mind and also raise this with the driver owners just
in case as well as pgbouncer. else it is a deadlock :)

Unexpected packet type during stream replication · Issue #1466 ·
pgjdbc/pgjdbc (github.com) 
although this one is different issue, can you reproduce this case like
above ?

bottom / top posting

[Vijaykumar Jain]

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 having
an iota of intention to think that way. I was mostly used to tickets or
chat.
but as with chats, using CAPS was not known to me as shouting for a long
time, when someone pointed out to me this is what others feel when they
read.

Will keep this in mind, for me as well as others who are not aware of the
same.

-- 
Thanks,
Vijay
Mumbai, India

Re: Symbolic link breaks for postgresql.auto.conf

[Vijaykumar Jain]

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


the below confirms the file is touched with alter system.

root@go:~# ps -aef | grep post
root18731493  0 22:22 pts/100:00:00 su - postgres
postgres18741873  0 22:22 pts/100:00:00 -bash
postgres1916   1  0 22:24 ?00:00:00
/opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/data
postgres19181916  0 22:24 ?00:00:00 postgres: checkpointer
postgres19191916  0 22:24 ?00:00:00 postgres: background
writer
postgres19201916  0 22:24 ?00:00:00 postgres: walwriter
postgres19211916  0 22:24 ?00:00:00 postgres: archiver
postgres19221916  0 22:24 ?00:00:00 postgres: stats
collector
postgres19231916  0 22:24 ?00:00:00 postgres: logical
replication launcher
postgres19251874  0 22:24 pts/100:00:00 psql
postgres19261916  0 22:24 ?00:00:00 postgres: postgres
postgres [local] idle   -*--the psql session where i run alter system*
root19291594  0 22:24 pts/000:00:00 grep --color=auto post

root@go:~# strace  -e trace=%file -p 1916 -p 1918 -p 1919 -p 1926
strace: Process 1916 attached
strace: Process 1918 attached
strace: Process 1919 attached
strace: Process 1926 attached
[pid  1926] stat("postgresql.auto.conf", {st_mode=S_IFREG|0664, st_size=0,
...}) = 0
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDONLY) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf.tmp",
O_RDWR|O_CREAT|O_TRUNC, 0600) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf.tmp", O_RDWR) = 24
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDWR) = 24
[pid  1926] rename("postgresql.auto.conf.tmp", "postgresql.auto.conf") = 0
[pid  1926] openat(AT_FDCWD, "postgresql.auto.conf", O_RDWR) = 24
[pid  1926] openat(AT_FDCWD, ".", O_RDONLY) = 24
[pid  1926] +++ exited with 0 +++
[pid  1916] --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=1926,
si_uid=1001, si_status=0, si_utime=0, si_stime=0} ---



On Sun, 6 Jun 2021 at 22:17, Tom Lane  wrote:

> RAJAMOHAN  writes:
> > I am trying to keep a symbolic link for the *postgresql.auto.conf*
> outside
> > the data directory. The link gets created but whenever I issue an alter
> > system statement it gets broken.
>
> This is not supported.  Don't do it.
>
> The right way to keep the hand-maintained config files outside the
> data directory is not the way you've done it here, either.  It might
> accidentally work, but the preferred way is to put "data_directory =
> whatever" in postgresql.conf and then start the postmaster with -D
> pointing at where the config files are.
>
> But in any case, postgresql.auto.conf is not hand-maintained; it
> is part of the cluster data, so it belongs in the data directory.
>
> regards, tom lane
>
>
>

-- 
Thanks,
Vijay
Mumbai, India

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

[Vijaykumar Jain]

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 being a pg mailing list :) what would be your expectation of type of
data and growth of data would be your queries.
If you store data to support multiple lingual papers, will postgresql be
able to handle ?
Ideally the docs would be stored somewhere on a object storage etc and the
link of the same would be stored in the db when someone would request to
read the whole paper.
Long before I read this
https://www.citusdata.com/blog/2017/04/20/analyzing-postgresql-email-archives/

So if this could work, your POC should too :) with postgresql.


On Sat, 5 Jun 2021 at 5:14 PM Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:

>
>
>
> Sent with ProtonMail Secure Email.
>
> ‐‐‐ Original Message ‐‐‐
> On Saturday, 5 June 2021 12:14, Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>
> >
> > I know its a huge work, but you are missing a point. Nobody wishes to
> > compete with anyone. This is a about a project, a parent-advocacy
> > non-profit that ONLY aims to save the sick children (or maybe also
> > very young adults) of a certain spectrum . So the goal is to make the
> > right tools for researchers, clinicians and parents. This market is too
> > small to even consider making any money out of it, but the research is
> > still very expensive and the progress slower than optimum.
>
>
> Unfortunately I'm not "missing a point", your final paragraph summarises
> your position.
>
> You have been taken in by the very charitable goal of saving sick children.
>
> Unfortunately your head has been disconnected from your heart.
>
> If we put the charitable purpose to one side and take a purely objective
> view at what you want to do, my original statement still stands, i.e. the
> certainty that you are grossly underestimating the technical and practical
> complexities of what you want to achieve.
>
>
> --
Thanks,
Vijay
Mumbai, India