Re: SELECT query results are different depending on whether table statistics are available.
Thanks for the help David, the query was missing a column in a PARTITION BY expression. On Thu, May 28, 2020 at 1:14 PM David G. Johnston wrote: > > On Wed, May 27, 2020 at 8:09 PM James Brauman > wrote: >> >> -- Run select query (involving several CTEs). >> SELECT ...; >> >> I haven't generated a minimal test case yet, but I did notice that if >> all CTEs in the SELECT query are defined using AS NOT MATERIALIZED the >> results are always the same regardless of whether the table has been >> ANALYZED yet. >> >> Could anyone share knowledge about why this is happening? > > > A likely scenario is you are missing an ORDER BY in a location where you are > depending on deterministic row ordering and its changing out from underneath > you. > > David J. >
Re: SELECT query results are different depending on whether table statistics are available.
On Wed, May 27, 2020 at 8:09 PM James Brauman wrote: > -- Run select query (involving several CTEs). > SELECT ...; > > I haven't generated a minimal test case yet, but I did notice that if > all CTEs in the SELECT query are defined using AS NOT MATERIALIZED the > results are always the same regardless of whether the table has been > ANALYZED yet. > > Could anyone share knowledge about why this is happening? > A likely scenario is you are missing an ORDER BY in a location where you are depending on deterministic row ordering and its changing out from underneath you. David J.
SELECT query results are different depending on whether table statistics are available.
I've ran into a bit of a head scratching situation and was hoping that someone with more knowledge that I could help me understand the behaviour I'm seeing. I'm running on PostgreSQL 12.2. I have a SELECT query that returns different results depending on whether statistics for the table have been collected or not.The query uses several CTEs and returns a single integer. This integer changes depending on whether the table has been analyzed. As far as I can tell I am not using any 'volatile' functions in my SELECT query. It took me a while to find a way to reproduce the issue. How I eventually reproduced it was: -- Delete all statistics. DELETE FROM pg_statistic; -- Truncate table and insert values into table. TRUNCATE TABLE target_table; INSERT INTO target_table (...) VALUES (...); -- The results of the SELECT are different depending on whether ANALYZE is called. ANALYZE target_table; -- Run select query (involving several CTEs). SELECT ...; I haven't generated a minimal test case yet, but I did notice that if all CTEs in the SELECT query are defined using AS NOT MATERIALIZED the results are always the same regardless of whether the table has been ANALYZED yet. Could anyone share knowledge about why this is happening? Thanks, James Brauman
Re: Suggestion to Monitoring Tool
Hi I also suggest Nagios (for immediate monitoring) layered with OPM (longer term and historical analysis), followed by PGBadger for stats snapshots (if your logging format policy permits), and also PG_activity for the Operations team (if security policies permit), the latter is great when handling locking amongst other things. Cheers Peter Goodwin On 5/27/2020 at 6:35 PM, "soumitra bhandary" wrote: > >You can try nagios > >Sent from my iPhone > >> On 27-May-2020, at 10:16 PM, postgann2020 s > wrote: >> >> >> Hi Team, >> >> Thanks for your support. >> >> Environment Details: >> OS: RHEL 7.2 >> Postgres: 9.5.15 >> Master-Slave with Streaming replication >> >> We are planning to implement the monitoring tool for our >environment. >> >> Could someone please suggest the Monitoring Tool based on your >experience. >> >> We are looking to cover the below areas. >> >> 1. Monitoring metrics and alerting. >> 2. Monitoring events and alerting. >> 3. Consolidate all the PROD DB logs and provide insights on log >data. >> 4. logging explain plan and insights on explain plans. >(Something like store explain plan and compare plans and send >alerts on deviations) >> 5. Logging audit data and insights from audit data. >> >> >> Thanks & Regards, >> Postgann.
Re: GPG signing
As it's not well documented yet (sorry) I'm following up to add signing is done with `crypto_sign()` and `crypto_sign_open()` https://github.com/michelp/pgsodium/blob/master/test.sql#L73 On Wed, May 27, 2020 at 2:42 PM Michel Pelletier wrote: > Hi Marc, > > You can sign content with pgsodium: > > https://github.com/michelp/pgsodium > > On Tue, May 26, 2020 at 12:21 PM Marc Munro wrote: > >> On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote: >> > On 5/26/20 12:01 PM, Marc Munro wrote: >> > > I need to be able to cryptographically sign objects in my database >> > > using a public key scheme. >> > > [ . . . ] >> > > Any other options? Am I missing something? >> > >> > https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7 >> >> I looked at that but I must be missing something. In order to usefully >> sign something, the private, secret, key must be used to encrypt a >> disgest of the thing being signed (something of a simplification, but >> that's the gist). This can then be verified, by anyone, using the >> public key. >> >> But the pgcrypto functions, for good reasons, do not allow the private >> (secret) key to be used in this way. Encryption and signing algorithms >> are necessarily different as the secret key must be protected; and we >> don't want signatures to be huge, and it seems that pgcrypto has not >> implemented signing algorithms. >> >> What am I missing? >> >> __ >> Marc >> >> >>
Re: GPG signing
Hi Marc, You can sign content with pgsodium: https://github.com/michelp/pgsodium On Tue, May 26, 2020 at 12:21 PM Marc Munro wrote: > On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote: > > On 5/26/20 12:01 PM, Marc Munro wrote: > > > I need to be able to cryptographically sign objects in my database > > > using a public key scheme. > > > [ . . . ] > > > Any other options? Am I missing something? > > > > https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7 > > I looked at that but I must be missing something. In order to usefully > sign something, the private, secret, key must be used to encrypt a > disgest of the thing being signed (something of a simplification, but > that's the gist). This can then be verified, by anyone, using the > public key. > > But the pgcrypto functions, for good reasons, do not allow the private > (secret) key to be used in this way. Encryption and signing algorithms > are necessarily different as the secret key must be protected; and we > don't want signatures to be huge, and it seems that pgcrypto has not > implemented signing algorithms. > > What am I missing? > > __ > Marc > > >
Re: Suggest the Schedular for activities
On 5/27/20 9:06 AM, postgann2020 s wrote: Hi Team, Thanks for your support. Currently, we are using tomcat for scheduling and want to replace it with DB specific schedulers. Could someone please suggest the Schedular for application activities instead of creating tomcat schedulers. Also scheduler for DB specific activities as well instead of corn. Maybe?: https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/ Thanks & Regards, Postgann. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Can't remove default permissions entry
On 5/27/20 10:06 AM, Christophe Pettus wrote: On RDS (thus, no superuser) we are trying to drop a user. The only remaining item that the user owns is an "empty" default permissions entry, but we can't seem to get rid of it so that the user can be dropped: I'm sure I'm missing something obvious! Have you tried?: https://www.postgresql.org/docs/12/sql-alterdefaultprivileges.html "If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges ^ entry for the role." So: https://www.postgresql.org/docs/12/sql-drop-owned.html Logged in as xyuser: db=> \ddp+ Default access privileges Owner|Schema | Type |Access privileges +---+--+-- xyuser | | table| db=> ALTER DEFAULT PRIVILEGES FOR USER xyuser REVOKE ALL ON TABLES FROM xyuser; ALTER DEFAULT PRIVILEGES db=> \ddp+ Default access privileges Owner|Schema | Type |Access privileges +---+--+-- xyuser | | table| db=> -- -- Christophe Pettus x...@thebuild.com -- Adrian Klaver adrian.kla...@aklaver.com
Changing wal segment size on existing database cluster
Hi all, I have a high traffic database, where I'm interested in changing the wal segment size to a larger value. I haven't found much documentation about how to change the segment size of an existing database. The obvious, safe solution would be to create a new database cluster and dump/reload. This isn't ideal for a large database though. Pg_resetwal has a wal-segsize option, but the documentation doesn't provide much guidance beyond that it's there. The pg_resetwal manpage also has big warnings all over it about how the tool can corrupt your database cluster. So my question is, is it safe to change wal-segsize using pg_resetwal following a clean shutdown of the database? Just reading the docs, it seems like the corruption issues are more around non-graceful shutdowns or crash scenarios, with incomplete transactions being wiped out by a wal reset. If the database was shutdown cleanly this doesn't *seem* like it would be an issue. Has anyone had experience doing this? I assume this would break any physical replication standbys. Any other gotchas I should be looking out for? I've tested on a trivial (empty) database cluster, and everything seems okay. But corruption might be difficult to detect until it's too late. Thanks, James Lucas
Re: suggestion the process to sync the data from PROD DB to Staging environment
If possible you can connect staging server as asynchronous slave node to one of the asynchronous node already in production Sent from my iPhone On 27-May-2020, at 9:26 PM, postgann2020 s wrote: Hi Team, Thanks for your support. Could someone please suggest the process to sync the data from PROD DB to the Staging environment with minimal manual intervention or automatically. Thanks & Regards, Postgann.
Re: Suggestion to Monitoring Tool
You can try nagios Sent from my iPhone > On 27-May-2020, at 10:16 PM, postgann2020 s wrote: > > > Hi Team, > > Thanks for your support. > > Environment Details: > OS: RHEL 7.2 > Postgres: 9.5.15 > Master-Slave with Streaming replication > > We are planning to implement the monitoring tool for our environment. > > Could someone please suggest the Monitoring Tool based on your experience. > > We are looking to cover the below areas. > > 1. Monitoring metrics and alerting. > 2. Monitoring events and alerting. > 3. Consolidate all the PROD DB logs and provide insights on log data. > 4. logging explain plan and insights on explain plans. (Something like store > explain plan and compare plans and send alerts on deviations) > 5. Logging audit data and insights from audit data. > > > Thanks & Regards, > Postgann.
Re: suggestion the process to sync the data from PROD DB to Staging environment
On Wed, May 27, 2020 at 9:31 AM postgann2020 s wrote: > Thanks, David, > > Please find the environment details. > > Environment: > PROD: > OS: RHEL 7.1 > Postgres: 9.5.15 > > Staging: > OS: RHEL 7.1 > Postgres: 9.5.15 > Ok...not particularly helpful though I do see you are not keeping up with minor and major releases. Maybe the terms metrics and goals would have been better...like how big is the database and what kind of network do the two machines exist in and how would they communicate data from one to the other... David J.
Can't remove default permissions entry
On RDS (thus, no superuser) we are trying to drop a user. The only remaining item that the user owns is an "empty" default permissions entry, but we can't seem to get rid of it so that the user can be dropped: I'm sure I'm missing something obvious! Logged in as xyuser: db=> \ddp+ Default access privileges Owner|Schema | Type |Access privileges +---+--+-- xyuser | | table| db=> ALTER DEFAULT PRIVILEGES FOR USER xyuser REVOKE ALL ON TABLES FROM xyuser; ALTER DEFAULT PRIVILEGES db=> \ddp+ Default access privileges Owner|Schema | Type |Access privileges +---+--+-- xyuser | | table| db=> -- -- Christophe Pettus x...@thebuild.com
Re: Suggestion to Monitoring Tool
On Wed, May 27, 2020 at 10:15:49PM +0530, postgann2020 s wrote: > Environment Details: > OS: RHEL 7.2 > Postgres: 9.5.15 > Master-Slave with Streaming replication > > We are planning to implement the monitoring tool for our environment. > > Could someone please suggest the Monitoring Tool based on your experience. I suggest you read up on the fine manual first. It covers a lot of ground already. And to stick to one major project at a time. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Suggestion to Monitoring Tool
Hi Team, Thanks for your support. Environment Details: OS: RHEL 7.2 Postgres: 9.5.15 Master-Slave with Streaming replication We are planning to implement the monitoring tool for our environment. Could someone please suggest the Monitoring Tool based on your experience. We are looking to cover the below areas. 1. Monitoring metrics and alerting. 2. Monitoring events and alerting. 3. Consolidate all the PROD DB logs and provide insights on log data. 4. logging explain plan and insights on explain plans. (Something like store explain plan and compare plans and send alerts on deviations) 5. Logging audit data and insights from audit data. Thanks & Regards, Postgann.
Re: suggestion the process to sync the data from PROD DB to Staging environment
Thanks, David, Please find the environment details. Environment: PROD: OS: RHEL 7.1 Postgres: 9.5.15 Staging: OS: RHEL 7.1 Postgres: 9.5.15 Thanks, PostgAnn. On Wed, May 27, 2020 at 9:51 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, May 27, 2020 at 8:56 AM postgann2020 s > wrote: > >> Could someone please suggest the process to *sync the data from PROD DB >> to the Staging environment* with minimal manual intervention or >> automatically. >> > > Read up on the general purpose "bash" scripting language, the PostgreSQL > "pg_dump" and "pg_restore" commands, "ssh", and "cron". > > "cron" and "bash" provide for the "automatically" requirement. > > It is possible to assemble something functional with those tools. Whether > it will actually work in your specific situation is impossible to say since > you provide zero information about your environment. > > David J. > >
Re: suggestion the process to sync the data from PROD DB to Staging environment
On Wed, May 27, 2020 at 8:56 AM postgann2020 s wrote: > Could someone please suggest the process to *sync the data from PROD DB > to the Staging environment* with minimal manual intervention or > automatically. > Read up on the general purpose "bash" scripting language, the PostgreSQL "pg_dump" and "pg_restore" commands, "ssh", and "cron". "cron" and "bash" provide for the "automatically" requirement. It is possible to assemble something functional with those tools. Whether it will actually work in your specific situation is impossible to say since you provide zero information about your environment. David J.
Re: Install PostgreSQL on windows 10 home 64-bit machine
Thank you Olivier for your reply. It's just for me to practice. Thank you, Prashanth. On Wed, May 27, 2020, 2:12 AM Olivier Gautherot wrote: > Hi Prashanth, > > On Wed, May 27, 2020 at 8:59 AM Prashanth Talla > wrote: > >> Hi, >> I'm trying to install PostgreSQL database on my personal laptop that is >> running on Windows 10 Home 64-bit operating system. >> >> I found the s/w @ https://www.postgresql.org/download/windows/ >> >> The webpage shows that PostgreSQL v10 (latest is v12) is available for >> Windows 10 64-bit, but it doesn't tell if its suitable for Windows 10 home >> or not. >> >> Did anyone installed this database s/w on their personal laptop that is >> running on Windows 10 home 64-bit? If so, can you please point me to the >> right version (latest) of PostgreSQL DB that I can install for practice? >> > > It works fine on Windows 10 Home. If it is a personal laptop (and probably > not dedicated to the database), just make sure you don't load huge > datasets. That being said, it is fully functional. > > Happy learning! > -- > Olivier Gautherot > >
Re: suggestion the process to sync the data from PROD DB to Staging environment
Hi Luan, Thanks for your email. How frequently do you sync *hourly/daily/weekly*? > looking for a daily basis. Do you have other processes writing data into Staging env? > Yes, we have old PROD data. Thanks & Regards, Postgann. On Wed, May 27, 2020 at 9:33 PM Luan Huynh wrote: > Hi Postgann, > > How frequently do you sync *hourly/daily/weekly*? Do you have other > processes writing data into Staging env? > > Regards, > > On Wed, May 27, 2020 at 5:56 PM postgann2020 s > wrote: > >> Hi Team, >> >> Thanks for your support. >> >> Could someone please suggest the process to *sync the data from PROD DB >> to the Staging environment* with minimal manual intervention or >> automatically. >> >> Thanks & Regards, >> Postgann. >> >
Suggest the Schedular for activities
Hi Team, Thanks for your support. Currently, we are using tomcat for scheduling and want to replace it with DB specific schedulers. Could someone please suggest the Schedular for application activities instead of creating tomcat schedulers. Also scheduler for DB specific activities as well instead of corn. Thanks & Regards, Postgann.
Re: suggestion the process to sync the data from PROD DB to Staging environment
Hi Postgann, How frequently do you sync *hourly/daily/weekly*? Do you have other processes writing data into Staging env? Regards, On Wed, May 27, 2020 at 5:56 PM postgann2020 s wrote: > Hi Team, > > Thanks for your support. > > Could someone please suggest the process to *sync the data from PROD DB > to the Staging environment* with minimal manual intervention or > automatically. > > Thanks & Regards, > Postgann. >
suggestion the process to sync the data from PROD DB to Staging environment
Hi Team, Thanks for your support. Could someone please suggest the process to *sync the data from PROD DB to the Staging environment* with minimal manual intervention or automatically. Thanks & Regards, Postgann.
Re: Install PostgreSQL on windows 10 home 64-bit machine
Hi Prashanth, On Wed, May 27, 2020 at 8:59 AM Prashanth Talla wrote: > Hi, > I'm trying to install PostgreSQL database on my personal laptop that is > running on Windows 10 Home 64-bit operating system. > > I found the s/w @ https://www.postgresql.org/download/windows/ > > The webpage shows that PostgreSQL v10 (latest is v12) is available for > Windows 10 64-bit, but it doesn't tell if its suitable for Windows 10 home > or not. > > Did anyone installed this database s/w on their personal laptop that is > running on Windows 10 home 64-bit? If so, can you please point me to the > right version (latest) of PostgreSQL DB that I can install for practice? > It works fine on Windows 10 Home. If it is a personal laptop (and probably not dedicated to the database), just make sure you don't load huge datasets. That being said, it is fully functional. Happy learning! -- Olivier Gautherot
Re: pg_basebackup + delta base backups
On 26-May-20 10:20, Stephen Frost wrote: [...] "out of sync" is a bit of an odd concept, but having a replica fall behind a long way is certainly something that can happen and may require a rebuild from a backup (or from a new sync off of the primary in some other way, as you suggest below). In a situation where there's async replication happening and you promote a replica to take over, that's definitely a case where you might also have to rebuild the former primary. Hi Stepehen, Yes, a common case with async streaming is when primary (A) goes down and replica is promoted as a new master (B). Then A comes back and has some data that was not streamed to B so pg_rewind is useless. I wonder if there is some option to just discard this branched data from A in order to start as a new replica. I noticed that pg_rewind is useless even when both DBs are identical (according to pg_dumpall | md5sum). [...] As you said, all the pieces are there and it would be quite easy to write a new "pg_basebackup_delta" script that could be executed on the standby host to: 1) setup a pgBackRest repo on the primary host (via SSH) 2) create a backup on the primary host (via SSH) 3) do a delta restore on the standby Even when the repository on the primary host is only created temporarily (and require double storage, resources, etc), it may still be worth considering the traffic that can be saved by doing a delta restore on a standby host in a different region, right? So... There's actually a way to do this with pgbackrest, but it doesn't support the delta capability. If I understood correctly the method you described, you were basically doing a "backup" between A (primary) and B (repo) and in such a way the repo is then compatible with the pg_data structure, but without delta support (ie. transfering the whole database)? Delta support is critical for VLDBs, so I see two alternatives to replace pg_basebackup with pgbackrest to rebuild a replica: 1) Create a temporary repo on the primary 2) Create a temporary repo on the replica All configurations would be undone after the replica has been rebuilt and both alternatives would be using delta over the wire. In your opinion, which alternative is better considering network traffic? Thanks, Christopher
Install PostgreSQL on windows 10 home 64-bit machine
Hi, I'm trying to install PostgreSQL database on my personal laptop that is running on Windows 10 Home 64-bit operating system. I found the s/w @ https://www.postgresql.org/download/windows/ The webpage shows that PostgreSQL v10 (latest is v12) is available for Windows 10 64-bit, but it doesn't tell if its suitable for Windows 10 home or not. Did anyone installed this database s/w on their personal laptop that is running on Windows 10 home 64-bit? If so, can you please point me to the right version (latest) of PostgreSQL DB that I can install for practice? I really appreciate your help. Thank you, Prashanth.
