Suggestion to improve query performance.

Hi Team, Thanks for your support. We are using below environment: Application : Programming Language : JAVA Geoserver Database Stack: PostgreSQL : 9.5.15 Postgis We have 3 geoserver queries and are getting some performance issues after changing the GeoServer queries.I have posted the queries

Re: How to recover from compressed wal archieve in windows

Hi! I'm guessing: restore_command='copy "D:\\wallog\\%f.gz" "%p"' will get you the file. The next problem is that I'm pretty sure a WAL file with *.gz extension will not be able to be processed directly by the server. So you are going to have to uncompress it at some point before it gets

Re: How to recover from compressed wal archieve in windows

On 5/20/20 8:08 AM, Andrus wrote: In windows 10 pg_receivewal --directory="d:\wallog" --verbose --compress=9 is used to archieve WAL. This creates .gz files For restore restore_command='copy "D:\\wallog\\%f" "%p"' I'm guessing: restore_command='copy "D:\\wallog\\%f.gz" "%p"' will get you

Re: PostgreSQLBook

Perfect! Thank you! -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

SET ROLE and search_path

Hi, I'm trying to implement a PostgreSQL multi-tenant database that will be accessed by a Web Application. The users that will login will belong to different companies and a schema was created in the database for each company. However, I would like the Web Application to connect with a single

Re: PostgreSQLBook

Hi edavis, yes, there is: https://www.postgresql.org/docs/manuals/ Saves paper. :-) If you want paper, then you can download the PDF and print what you need. Cheers, Paul > On 20. May, 2020, at 18:03, edavis wrote: > > Can someone tell me if there's a postgres book that's equivalent to

PostgreSQLBook

Can someone tell me if there's a postgres book that's equivalent to "Oracle Database 11g The Complete Reference"? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Huge tables, trying to delete OID's taking 6+hours per table

On Tue, May 19, 2020 at 10:06 AM Ron wrote: > On 5/19/20 11:51 AM, Tory M Blue wrote: > > > > On Tue, May 19, 2020 at 6:40 AM Tom Lane wrote: > >> Tory M Blue writes: >> > The command i'm using is >> > ALTER TABLE tablename SET WITHOUT OIDS; >> > Would a drop column oid be better? >> >>

Re: SET ROLE and search_path

On 5/20/20 10:36 AM, Patrick FICHE wrote: Hi, I’m trying to implement a PostgreSQL multi-tenant database that will be accessed by a Web Application. The users that will login will belong to different companies and a schema was created in the database for each company. However, I would

Re: How to recover from compressed wal archieve in windows

Hi Andrus, hi Adrian, see: 25.3.6.2. Compressed Archive Logs https://www.postgresql.org/docs/12/continuous-archiving.html#BACKUP-TIPS Hope this helps in dealing with compressed WAL files. Cheers, Paul > On 20. May, 2020, at 20:36, Adrian Klaver wrote: > > On 5/20/20 8:08 AM, Andrus wrote:

Re: Huge tables, trying to delete OID's taking 6+hours per table

On 5/20/20 11:22 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 10:06 AM Ron > wrote: On 5/19/20 11:51 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 6:40 AM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Tory M Blue mailto:tmb...@gmail.com>>

Re: Logical replication troubles

On 2020-05-19 09:22, Anders Bøgh Bruun wrote: I have run into a (to me) weird issue with logical replication. We are running Zalandos postgres-operator in our Kubernetes clusters and have recently had a use-case where we wanted to start doing logical replication of select tables to a data

How to recover from compressed wal archieve in windows

In windows 10 pg_receivewal --directory="d:\wallog" --verbose --compress=9 is used to archieve WAL. This creates .gz files For restore restore_command='copy "D:\\wallog\\%f" "%p"' is used. Restore shows "file not found" errors in console. Thi sis probably because %f argument is WAL file

Re: Logical replication troubles

Fair question, I should have been more elaborate. When I have the creation of a replication slot, before I insert any data into the table in my example (the SQL-file named "broken" in my gist), after I then create the subscription on the receiving side, it does the initial sync of data and then

Re: SET ROLE and search_path

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Rob Sargent writes: > > Am I (again) alone in finding this a bit hokey?  That a user name just > > happens to be a schema name ... > > That's actually strongly encouraged by the SQL spec, if memory serves. ... and all-but-required by some

Re: SET ROLE and search_path

Thanks a lot I will try it again. I probably missed something in my configuration. Téléchargez Outlook pour iOS De : Adam Brusselback Envoyé : Wednesday, May 20, 2020 9:28:21 PM À : Patrick FICHE Cc : pgsql-general@lists.postgresql.org

Re: How to recover from compressed wal archieve in windows

On 5/20/20 12:09 PM, Andrus wrote: Hi! I'm guessing: restore_command='copy "D:\\wallog\\%f.gz" "%p"' will get you the file. The next problem is that I'm pretty sure a WAL file with *.gz extension will not be able to be processed directly by the server. So you are going to have to uncompress

Re: SET ROLE and search_path

On 5/20/20 1:28 PM, Adam Brusselback wrote: I have this exact setup, and I use roles / schema names that match so the $user var works with the search path when I set role as my application user. > When search_path contains “$user”, does it refer to session_user or current_user ? It uses

Question on removing primary replication node using repmgr?

Hello, I used repmgr extension for Postgresql to setup replication. I want to remove the replication because it appears WAL files are generated even after unregistering the standby instance and putting primary instance in no archivelog mode. Can you help? host:> repmgr -f

Re: SET ROLE and search_path

Rob Sargent writes: > Am I (again) alone in finding this a bit hokey?  That a user name just > happens to be a schema name ... That's actually strongly encouraged by the SQL spec, if memory serves. regards, tom lane

Re: How to recover from compressed wal archieve in windows

On 5/20/20 11:43 AM, Paul Förster wrote: Hi Andrus, hi Adrian, see: 25.3.6.2. Compressed Archive Logs https://www.postgresql.org/docs/12/continuous-archiving.html#BACKUP-TIPS Hope this helps in dealing with compressed WAL files. Yeah, the issue would be getting a version of gunzip that

Re: SET ROLE and search_path

I have this exact setup, and I use roles / schema names that match so the $user var works with the search path when I set role as my application user. > When search_path contains “$user”, does it refer to session_user or current_user ? It uses current_user, not session_user. Works perfectly with

Re: How to recover from compressed wal archieve in windows

At Wed, 20 May 2020 19:25:50 -0700, Adrian Klaver wrote in > On 5/20/20 6:27 PM, Michael Paquier wrote: > > On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote: > >> The next problem is that I'm pretty sure a WAL file with *.gz > >> extension will > >> not be able to be processed

Re: How to recover from compressed wal archieve in windows

Hi Adrian, > On 21. May, 2020, at 01:03, Adrian Klaver wrote: > > Yeah, the issue would be getting a version of gunzip that works on Windows. > In the past I have had luck with: > > https://www.7-zip.org/ > > Not sure how well it works with redirects/pipes. I'm no windoze guru, so I don't

Re: How to recover from compressed wal archieve in windows

At Thu, 21 May 2020 06:55:41 +0200, Paul Förster wrote in > Hi Adrian, > > > On 21. May, 2020, at 01:03, Adrian Klaver wrote: > > > > Yeah, the issue would be getting a version of gunzip that works on Windows. > > In the past I have had luck with: > > > > https://www.7-zip.org/ > > > >

Re: How to recover from compressed wal archieve in windows

On 5/20/20 6:27 PM, Michael Paquier wrote: On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote: The next problem is that I'm pretty sure a WAL file with *.gz extension will not be able to be processed directly by the server. So you are going to have to uncompress it at some point

Re: How to recover from compressed wal archieve in windows

On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote: > The next problem is that I'm pretty sure a WAL file with *.gz extension will > not be able to be processed directly by the server. So you are going to have > to uncompress it at some point before it gets restored. The short answer

Re: How to recover from compressed wal archieve in windows

On Thu, May 21, 2020 at 01:38:11PM +0900, Kyotaro Horiguchi wrote: > Difference from decompression by restore_command? > > A WAL (segment) file is filled with multiple WAL records. The "full > page image", which is described to be compressed by the parameter, is > a part of WAL record. A WAL file

Q: Comparing PostgreSQL and Oracle

Hi colleagues. I'm working in a Java application with some colleagues and we are in doubt wether to use Oracle or PostgreSQL as the data store. It will be a OLTP mainly application. Beside of license terms/costs  which is a clear point in favor of PostgreSQL, could you please help me to

Re: Q: Comparing PostgreSQL and Oracle

st 20. 5. 2020 v 9:34 odesílatel Alfonso napsal: > Hi colleagues. > > > I'm working in a Java application with some colleagues and we are in > doubt wether to use Oracle or PostgreSQL as the data store. It will be a > OLTP mainly application. > > Beside of license terms/costs which is a clear

Re: Q: Comparing PostgreSQL and Oracle

For what it's worth, I used Oracle daily as a DBA and developer for my job from 1983 until around 3 years ago when Postgres was chosen for a project I was assigned to. I became pretty familiar with the workings of Oracle and was somewhat skeptical when told we would be using Postgres, however it

Re: Q: Comparing PostgreSQL and Oracle

On Wed, May 20, 2020 at 12:34 AM Alfonso wrote: > Hi colleagues. > > > I'm working in a Java application with some colleagues and we are in > doubt wether to use Oracle or PostgreSQL as the data store. It will be a > OLTP mainly application. > > Beside of license terms/costs which is a clear

RE: Q: Comparing PostgreSQL and Oracle

Hi, With massive development within OSS PosgreSQL over the years, there is plenty of things it can do when compared to Oracle. The below URLs give you a comprehensive comparison table, I am sure you or your colleagues may have already stumbled upon these URLs. In addition to that, there is

Re: A limit clause can cause a poor index choice

On Tue, 19 May 2020 at 21:56, Mohamed Wael Khobalatte wrote: > I believe a second ordering, by id desc, will get your query to use the right > index, and shouldn't be functionally different from what you would expect. Thanks, that works nicely on our production table, even with much larger

Re: A limit clause can cause a poor index choice

On Tue, 19 May 2020 at 22:15, Michael Lewis wrote: > Increase default_statistics_target, at least on that column, and see if you get a much much better plan. I don't know where I got this query from online, but here ya go. I'd be curious how frac_MCV in this changes when