Re: Methods to quickly spin up copies of an existing databases
On Fri, Mar 01, 2019 at 04:51:32PM -0500, Arjun Ranade wrote: > Pre-copying is not really an option since we could potentially need 1-X > instances so it needs to be scalable. Would it work to have a PG instance with a ready-to-use cluster/DB inside a VM, and make a copy of that as needed ? The copies then get thrown away. Same here, pre-copying a bunch of VMs would help. Disk space is (apparently) cheaper than time (for your use case). Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: cannot execute VACUUM during recovery
On Wed, Feb 27, 2019 at 12:39 PM Stephen Eilert wrote: > Are you running Vacuum on the slave node? It has to run on the master. > > Thanks, > > – Stephen > Thanks Stephen for your advice Iam running the VACUUM command from 2 of the linux instances using the below command. We were running some scripts from these linux boxes and surprised to see 1 works the other does not so wondering why does it happen. * Command:* *VACUUM VERBOSE myTableTest;* 1) Running from Linux Instance1 Command Iam running -> psql -h clusterName -U myUserName -d myPostgresDB There is no .pgpass setup on this linux instance so I had to manually enter the password here. VACUUM on the table Iam running does not work and throws an error as ERROR: cannot execute VACUUM during recovery 2) Running from Linux Instance2 Command Iam running -> psql -d myPostgresDB -h clusterName -U myUserName This option had a .pgpass file at the root in this linux instance and I did not pass any password here, the *VACUUM on the table Iam running works.* *pgpass is setup here and contains -> clusterName:5432:myPostgresDB:myUserName:myDBPassword* Thanks - Kran. > On Feb 27, 2019, 6:43 AM -0800, github kran , wrote: > > Hello Team, > > We are using a PostgreSQL 9.6 and seeing the below error while trying to > run a VACUUM on one of our live tables running in Production. We wanted to > clean up some DEAD tuples on the table. > > > *Command*: VACUUM (ANALYZE,VERBOSE) table_name. > > ERROR: cannot execute VACUUM during recovery > > Thanks > Kran > >
Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2
On Sat, Mar 2, 2019 at 5:02 AM Ray O'Donnell wrote: > On 01/03/2019 15:01, Nicola Contu wrote: > > Hello, > > is there any reason why I am getting worse results using pgsql11.2 in > > writing comparing it with pgsql 10.6? > > > > I have two Instances, both just restored, so no bloats. > > Running read queries I have pretty much same results, a little bit > > better on pg11- Running writes the difference is in favour of 10. > > Did you run ANALYZE on the databases after restoring? If you can rule out different query plans, and if you compiled them both with the same compiler and optimisation levels and without cassert enabled (it's a long shot but I mentioned that because you showed a path in /usr/local so perhaps you're hand-compiling 11, but 10 came from a package?), then the next step might be to use a profiler like "perf" (or something equivalent on your OS) to figure out where 11 is spending more time in the write test? -- Thomas Munro https://enterprisedb.com
Re: Where **not** to use PostgreSQL?
For small, lightweight, portable SQLite is quite nice for times you don't want to create a full PG installation and still prefer SQL for lookups. -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222 lemb...@wrkhors.com+1 888 359 3508
support for JSON Web Token
Hi, I would like to advocate forJSON web token support in PostgreSQL. JWT tokens are used in a lot of web applications and I think there are some very nice use cases for passing the token down to PostgreSQL. Some of the first thing that comes to mind is pairing it with row level security to implement authorization policies and data access directly in DB. I've did a bit of research and I found some prior work in [pgjwt] and [postgres-jwt-prototype] . It seems to be pretty easy to support since most of the work is done: PostgreSQL has crypto support via pgcrypto and has native JSON handling functions. JWT is a widely used technology, especially in web applications. This is why I think PostgreSQL should adopt a JWT extension in contrib. I believe PostgreSQL will benefit, especially in the web apps use. What do you think? What would it take for a JWT extension to be added to postgresql contrib or the official postgresql extensions ? Thanks, Eugen [pgjwt] https://github.com/michelp/pgjwt [postgres-jwt-prototype] https://github.com/larsar/postgres-jwt-prototype
regex DoS in postgresql?
I would like to deploy a web application with a Postgresql backend that allows a user to both add a record containing arbitrary text, and to search that text column with an arbitrary regular expression. My question is, is the app subject to a regex denial of service attack? I would have presumed so but I have been unable produce any problems when testing using the "evil" regexs described in the Wikipedia article on the subject: https://en.wikipedia.org/wiki/ReDoS Is there something in the way Postgresql implements its RE engine that makes it immune to this attack? Thanks for any enlightenment...
Re: regex DoS in postgresql?
Stuart McGraw writes: > I would like to deploy a web application with a Postgresql backend > that allows a user to both add a record containing arbitrary text, > and to search that text column with an arbitrary regular expression. > My question is, is the app subject to a regex denial of service attack? Possibly. We've run into such problems before (eg [1][2][3]), and generally been able to find mitigations, but I wouldn't promise that somebody who was intent on making a query run a long time couldn't do so. However, I don't really think that this is a showstopper reason not to allow users to give regexp patterns --- just make sure you use a statement timeout to limit the runtime. > I would have presumed so but I have been unable produce any problems > when testing using the "evil" regexs described in the Wikipedia > article on the subject: https://en.wikipedia.org/wiki/ReDoS > Is there something in the way Postgresql implements its RE engine > that makes it immune to this attack? PG's RE engine is based on Henry Spencer's third regex library (also used by Tcl), which is quite different from most others out there such as Perl's. That wikipedia entry doesn't seem to go into much detail, but I suspect the examples it gives are intended to cause problems for Perl's engine. Perl's engine does a lot more backtracking than ours does, and consequently it's easier to send it down the rabbit hole. regards, tom lane [1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commit&h=3bb3f42f3749d40b8d4de65871e8d828b18d4a45 [2] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commit&h=48789c5d23a7f382e3cb721547d5e0af7aaa4585 [3] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commit&h=98f27aaef34291246c09ce5d0e0fba4f4477467a
Re: Future Non-server Windows support???
Bill Haught wrote: > My main concern is that Microsoft has Enterprise versions of Windows and > versions for everything else which makes me wonder if at some point > Windows versions for desktop use may not have features needed by some > database applications or differences between the versions may be enough > to necessitate slight tweaks to code and compiling additional versions. Speaking as a semi-ignorant, I had the impressions that all Windows versions are pretty similar under the hood (with occasional annoying behavior changes), and most of the differences are on the GUI level, while the C API is pretty much the same. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
