Re: Regarding automatic table partitioning in PostgreSQL 12 version is possible or not

2020-01-17 Thread Stephen Frost
Greetings, * Durgamahesh Manne (maheshpostgr...@gmail.com) wrote: > Please let me know that automatic table partitioning is possible in pgsql > 12 or not without using trigger function The approach I'd recommend for automatically getting new table partitions created, at least today, is to use pg_

Re: calculating the MD5 hash of role passwords in C

2020-02-05 Thread Stephen Frost
Greetings, * Matthias Apitz (g...@unixarea.de) wrote: > If I look into the database I see: > > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis'; > rolname | rolpassword > -+- > sisis | md52f128a1fbbecc4b16462e

Re: policies and extensions

2020-02-18 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Marc Munro writes: > > On Mon, 2020-02-17 at 22:48 -0500, Tom Lane wrote: > >> An RLS policy is a table "subsidiary object" so it only depends indirectly > >> on the extension that owns the table. > > > Yep, I get that, and I see the dependency

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-22 Thread Stephen Frost
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > Breaking userland backwards compatibility is a mortal sin, and one of the > reasons that MS software is so popular is that they work so hard to *not* > break userland backwards compatibility. It's also a reason why it's a mess and not everyone i

Re: Backup & Restore

2020-02-24 Thread Stephen Frost
Greetings, * Dor Ben Dov (dor.ben-...@amdocs.com) wrote: > What is your backup and restore solution in production when working with > Postgres ? > (+ if you can say few words why you picked this X solution instead of others) I'd recommend pgbackrest- https://www.pgbackrest.org, it's got lots of

Re: Backup & Restore

2020-02-25 Thread Stephen Frost
Greetings, * sivapostg...@yahoo.com (sivapostg...@yahoo.com) wrote: > HiCan u suggest a good backup solution for a windows installation ?  Looks > like the suggested two [ pgbarman, pgbackrest ] works only in Linux. While it's certainly something we'd like to do, we haven't ported pgbackrest to

Re: Web users as database users?

2020-03-11 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > I'm not aware that anyone's done formal performance testing, > but if you want to have a lot of roles in the system, my > expectation is that you'd be better off granting privileges > to a small number of group roles and then granting group > rol

Re: How does pg_basebackup manage to create a snapshot of the filesystem?

2020-03-23 Thread Stephen Frost
Greetings, * Dennis Jacobfeuerborn (denni...@conversis.de) wrote: > I'm currently trying to understand how backups work. In the > documentation in section "25.2. File System Level Backup" it says that > filesystem level backups can only be made when the database if offline > yet pg_basebackup seem

Re: Passwordcheck configuration

2020-03-23 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Dave Hughes writes: > > I have a requirement to set some password complexity for our database such > > as length of password, upper case, lower case, special characters, > > expiration limit, reuse, etc. > > Usually, if you have to do something

Re: Passwordcheck configuration

2020-03-23 Thread Stephen Frost
Greetings, * Dave Hughes (dhughe...@gmail.com) wrote: > Thank you for the information! This issue originated from a Department of > Defense STIG (Security Technical Implementation Guides). It's a security > check that applications and databases have to go through. I'll just leave > this one as

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread Stephen Frost
Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > On Tue, 2020-03-24 at 17:50 -0500, Chris Morris wrote: > > Is it even possible to use PG partitioning across a Foreign Server? > > I am not certain what you mean, but you can have foreign tables as partitions > of a partitioned table.

Re: Index selection issues with RLS using expressions

2020-04-01 Thread Stephen Frost
Greetings, We prefer to use in-line responses, please don't top-post on these lists. * Alastair McKinley (a.mckin...@analyticsengines.com) wrote: > Thanks for looking at this! It seems like there are quite a few performance > gotchas around leaky operators and RLS, this is my second encounter w

Re: PostgreSQL native multi-master

2020-04-08 Thread Stephen Frost
Greetings, * Vano Beridze (vanua...@gmail.com) wrote: > What are the plans to support multi-master natively? > What solution would you recommend at this point? preferably free. You probably want to look at logical replication, which allows you to replicate data from one PG server to another (with

Re: Using of --data-checksums

2020-04-08 Thread Stephen Frost
Greetings, * BGoebel (b.goe...@prisma-computer.de) wrote: > initdb --data-checksums "... help to detect corruption by the I/O system" > There is an (negligible?) impact on performance, ok. > > Is there another reason NOT to use this feature ? Not in my view. > Has anyone had good or bad exper

Re: full text

2020-04-09 Thread Stephen Frost
Greetings, * Roberto Della Pasqua (roberto.dellapas...@live.com) wrote: > Please sorry because I'm newbie of PGSQL We all were, once upon a time. :) > I need the best performing and overall quality full-text search, can be > possible to have the data stored in pgsql and the index to elasticsear

Re: Using of --data-checksums

2020-04-10 Thread Stephen Frost
Greetings, * Bruce Momjian (br...@momjian.us) wrote: > On Wed, Apr 8, 2020 at 11:54:34AM -0400, Stephen Frost wrote: > > * BGoebel (b.goe...@prisma-computer.de) wrote: > > > initdb --data-checksums "... help to detect corruption by the I/O system" > > >

Re: Lock Postgres account after X number of failed logins?

2020-05-05 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > "Wolff, Ken L" writes: > > I do understand what you described about locking down access through > > pg_hba.conf, so only authorized users/applications can connect. That makes > > a lot of sense and I’m going to take it forward to our Informat

Re: Lock Postgres account after X number of failed logins?

2020-05-06 Thread Stephen Frost
Greetings, * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > On Wed, 6 May 2020 at 00:05, Tim Cross wrote: > > Where Tom's solution fails is with smaller companies that cannot afford > > this level of infrastructure. > > Is there an objection to openldap? It's lightweight (so could > reasonably be

Re: EXTERNAL: Re: Lock Postgres account after X number of failed logins?

2020-05-06 Thread Stephen Frost
Greetings, * Wolff, Ken L (ken.l.wo...@lmco.com) wrote: > Thanks again, everyone, for all the responses and ideas. I'm still getting > caught up on the various responses but with respect to LDAP/AD, I truly wish > it were an option. I agree with the various sentiments that AD > authentication

Re: Lock Postgres account after X number of failed logins?

2020-05-06 Thread Stephen Frost
Greetings, * Peter J. Holzer (hjp-pg...@hjp.at) wrote: > On 2020-05-06 09:28:28 -0400, Stephen Frost wrote: > > LDAP-based authentication in PG involves passing the user's password to > > the database server in the clear (or tunneled through SSL, but that > > doesn'

Re: pg_basebackup inconsistent performance

2020-05-06 Thread Stephen Frost
Greetings, * Jasen Lentz (jle...@sescollc.com) wrote: > Where are the machines you are backing up from/to relative to each on the > network? > Direct ethernet connection between 10G network interfaces Is the backup server shared among other systems..? > Is there increased activity on the databa

Re: Lock Postgres account after X number of failed logins?

2020-05-07 Thread Stephen Frost
Greetings, * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > On Wed, 6 May 2020, 14:28 Stephen Frost, wrote: > > * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > > > On Wed, 6 May 2020 at 00:05, Tim Cross wrote: > > > > Where Tom's solution fails is with s

Re: New Role drop with Grant/Revokes stop working after subsequent runs

2020-05-07 Thread Stephen Frost
Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, May 6, 2020 at 5:05 PM AC Gomez wrote: > > I suppose the main question is, why would a bunch of grant and revoke > > commands run and not do anything, not even throw an error? > > Maybe its a bug? - I doubt this kind of

Re: Unique index on hash of jsonb value - correct solution?

2020-05-19 Thread Stephen Frost
Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > On Mon, 2020-05-18 at 18:43 +0200, Albrecht Dreß wrote: > > in a database I have a table with a text, a jsonb and a bytea column, which > > together shall be unique, like: > > > > > > Column | Type | Collation | Nullable |

Re: SET ROLE and search_path

2020-05-20 Thread Stephen Frost
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 oth

Re: pg_basebackup + incremental base backups

2020-05-21 Thread Stephen Frost
Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > >>In case of big databases, can we do incremental backups with pg_basebackup? > >pg_basebackup doesn't support incremental backups, though there's been > >discussion of adding some kind of support for it, check -hackers if > >you're

Re: pg_basebackup + incremental base backups

2020-05-21 Thread Stephen Frost
Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > On 21-May-20 08:43, Stephen Frost wrote: > >* Christopher Pereira (krip...@imatronix.cl) wrote: > >>Is there some way to rebuild the standby cluster by doing a differential > >>backup of the primary

Re: pg_basebackup + incremental base backups

2020-05-24 Thread Stephen Frost
Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > Ok, we want to use pgbackrest to *rebuild a standby that has fallen behind* > (where pg_rewind won't work). After reading the docs, we believe we should > use this setup: > > a) Primary host: primary cluster > > b) Repository host:

Re: pg_basebackup + incremental base backups

2020-05-26 Thread Stephen Frost
Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > On 24-May-20 15:48, Stephen Frost wrote: > >That really shouldn't be possible. I'm very curious as to exactly what > >happened that resulted in your primary/replica being 'out of sync', as > &

Re: When to use PARTITION BY HASH?

2020-06-02 Thread Stephen Frost
Greetings, Please don't cross post to multiple lists without any particular reason for doing so- pick whichever list makes sense and post to that. * Oleksandr Shulgin (oleksandr.shul...@zalando.de) wrote: > I was reading up on declarative partitioning[1] and I'm not sure what could > be a possibl

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 6/2/20 4:59 AM, Grigory Smolkin wrote: > >On 6/2/20 11:22 AM, Ron wrote: > >>The inability to do a point-in-time restoration of a *single* database > >>in a multi-db cluster is a serious -- and fundamental -- missing feature > >>(never to be i

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 6/2/20 1:30 PM, Stephen Frost wrote: > >* Ron (ronljohnso...@gmail.com) wrote: > >>On 6/2/20 4:59 AM, Grigory Smolkin wrote: > >>>On 6/2/20 11:22 AM, Ron wrote: > >>>>The inability to do a poi

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Ravi Krishna (sravikris...@comcast.net) wrote: > > Generally speaking, I discourage having lots of databases under one PG > > cluster for exactly these kinds of reasons. PG's individual clusters > > are relatively lightweight, after all. > > Plus PG does not directly support cross d

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Ravi Krishna (sravikris...@comcast.net) wrote: > > Eh, that's something that I think we should be looking at supporting, by > > using FDWs, but I haven't tried to figure out how hard it'd be. > > How good will that be in performance. > > In db2 you can do it using dblinks and that k

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Guyren Howe (guy...@gmail.com) wrote: > On Jun 2, 2020, at 14:16 , Stephen Frost wrote: > > I'm sure there's things we can do to improve the performance of the FDW. > > Not sure we'll get to a point where we are actually cacheing information > >

Re: Oracle vs. PostgreSQL - a comment

2020-06-02 Thread Stephen Frost
Greetings, * Adam Brusselback (adambrusselb...@gmail.com) wrote: > > How good will that be in performance. > > In my experience, not great. It's definitely better than not having it at > all, but it does not make for quick queries and caused serious > connection overhead when a query referenced

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > I use pg_backrest, but it does not look promising for running on BSD: > https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html That's an unfortunately ancient post, really, considering that pgbackrest has now been fully rewri

Re: troubleshooting postgresql ldap authentication

2020-06-09 Thread Stephen Frost
Greetings, * Chris Stephens (cstephen...@gmail.com) wrote: > yes, shortly after i sent this out to the list, one of our security > administrators suggested ldapscheme. I just tested and ldapurl works as > well. > > the security admin explained it like this: > > "since we are using port 636 I kn

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 6/9/20 4:15 AM, Stephen Frost wrote: > >* Adrian Klaver (adrian.kla...@aklaver.com) wrote: > >>I use pg_backrest, but it does not look promising for running on BSD: > >>https://flu

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > This professional backup solution also offers support for postgres. > Sadly, it only covers postgres up to Rel.9, and that piece of software > wasn't touched in the last 6 or 7 years. Then it certainly doesn't work with the changes in v12

Re: Something else about Redo Logs disappearing

2020-06-09 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote: > ! * Peter (p...@citylink.dinoex.sub.org) wrote: > ! > This professional backup solution also offers support for postgres. > ! > Sadly, it only covers po

Re: Something else about Redo Logs disappearing

2020-06-10 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote: > ! > And then 90% of the things offered here become superfluous, because > ! > they are already handled site-wide. And then you will have to > ! > co

Re: Something else about Redo Logs disappearing

2020-06-11 Thread Stephen Frost
Greetings, * Peter (p...@citylink.dinoex.sub.org) wrote: > On Wed, Jun 10, 2020 at 08:32:22AM -0400, Stephen Frost wrote: > ! > What repo?? I seem to have missed that at first glance. > ! > ! Yes, pgbackrest has a repo, like most other tools (though they call them >

Re: Something else about Redo Logs disappearing

2020-06-12 Thread Stephen Frost
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 6/9/20 4:15 AM, Stephen Frost wrote: > >* Adrian Klaver (adrian.kla...@aklaver.com) wrote: > >>I use pg_backrest, but it does not look promising for running on BSD: > >>https://flu

Re: Netapp SnapCenter

2020-06-18 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 18. Jun, 2020, at 16:19, Magnus Hagander wrote: > > I don't know specifically about SnapCenter, but for snapshots in general, > > it does require backup mode *unless* all your data is on the same disk and > > you have an atomic s

Re: Netapp SnapCenter

2020-06-19 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > When I wrote our backup mode script I read the deprecation note about the > exclusive mode backup. This is why I decided to go with non-exclusive to be > ready when exclusive backup mode is finally removed some day. Yet, I don't > see

Re: Netapp SnapCenter

2020-06-21 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 21. Jun, 2020, at 21:35, Magnus Hagander wrote: > > One not uncommon case is for example being able to provision a new replica > > while a backup is running. Since replicas are provisioned starting off a > > base backup, being ab

Re: Netapp SnapCenter

2020-06-22 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 22. Jun, 2020, at 13:08, Magnus Hagander wrote: > > It does not work off *that* base backup. But if you start from the *prior* > > be backup (one that did complete with a successful pg_stop_backup) then you > > can still use the

Re: Netapp SnapCenter

2020-06-22 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 22. Jun, 2020, at 17:46, Wolff, Ken L wrote: > > So apologies if this is a stupid question but there's obviously been a lot > > of discussion on this issue. Was a consensus ever reached on the following? > > > > If a Postgres da

Re: scram-sha-256 encrypted password in pgpass

2020-06-22 Thread Stephen Frost
Greetings, * Pavan Kumar (pavan.db...@gmail.com) wrote: > > What would be the point of storing the encrypted password instead of the > > plaintext one? > As per our organization security policies, we can 't keep any passwords in > plain text format. Then you need to *actually* encrypt the passwo

Re: Need help with PITR for PostgreSQL 9.4.5

2020-06-25 Thread Stephen Frost
Greetings, * Sri Linux (srilinu...@gmail.com) wrote: > Please find the method used. Please recommend me if I have done > something wrong... Yes, you are using 'cp' which is *not* recommended for an archive command. > Performing a hot backup using pg_basebackup: > Create a new folder as the postg

Re: EXTERNAL: Re: Netapp SnapCenter

2020-06-29 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 26. Jun, 2020, at 12:29, Magnus Hagander wrote: > > I believe NetApp does atomic snapshots across multiple volumes, if you have > > them in the same consistency group. (If you don't then you're definitely in > > for a world of pa

Re: PG13 Trusted Extension usability issue

2020-06-29 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > "Brad Nicholson" writes: > > Tom Lane wrote on 2020/06/26 02:47:25 PM: > >> I'm confused about your point here. postgresql_fdw has intentionally > >> *not* been marked trusted. That's partly because it doesn't seem like > >> outside-the-datab

Re: libpq pipelineing

2020-06-29 Thread Stephen Frost
Greetings, * Samuel Williams (space.ship.travel...@gmail.com) wrote: > Here is a short example: > > https://gist.github.com/ioquatix/2f08f78699418f65971035785c80cf18 > > It makes 10 queries in one "PQsendQuery" and sets single row mode. But > all the results come back at once as shown by the tim

Re: EXTERNAL: Re: Netapp SnapCenter

2020-06-29 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 29. Jun, 2020, at 15:32, Stephen Frost wrote: > > > > Presumably they mean 'quiesce', except that that *isn't* what PG's > > yes, sorry, "quiece" was a typo on my part. I

Re: EXTERNAL: Re: Netapp SnapCenter

2020-06-29 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 29. Jun, 2020, at 16:43, Stephen Frost wrote: > > I certainly don't blame you, particularly given all the changes > > regarding how restore is done which went into v12- obviously anything > > that has

Re: restore_command for postgresql streaming replication

2020-07-02 Thread Stephen Frost
Greetings, * Brajendra Pratap Singh (singh.bpratap...@gmail.com) wrote: > Please help us to configure the restore_command on postgresql replication > server's recovery.conf file incase of ERROR: requested wal segment has > already been removed on primary and when we have implemented pgbackrest on

Re: Safe switchover

2020-07-10 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > I wouldn't work out the procedure myself, especially since there is a free > working solution already. It's dangerous if you do it yourself and make a > mistake. > > In our company, we rely on Patroni (https://github.com/zalando/patro

Re: Safe switchover

2020-07-10 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 10. Jul, 2020, at 17:29, Stephen Frost wrote: > > Patroni also has the option to use pgbackrest instead of pg_rewind. > > right. Sorry, I forgot about that. We use pg_rewind which works great. Sure, if you kn

Re: Safe switchover

2020-07-13 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 10. Jul, 2020, at 17:45, Stephen Frost wrote: > > Sure, if you know exactly why the former primary failed and have > > confidence that nothing actually bad happened then pg_rewind can work > > (though

Re: Safe switchover

2020-07-13 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 13. Jul, 2020, at 17:47, Stephen Frost wrote: > > > > Sure, Patroni will handle the failover fine- but that's not what I was > > referring to. If the server crashes and you have no idea why or what &

Re: Safe switchover

2020-07-13 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 13. Jul, 2020, at 18:00, Stephen Frost wrote: > > Ah, yes, if you rebuild the replica from a backup (or from the primary), > > then sure, that's pretty similar to the pgbackrest delta restore, except &g

Re: privileges oddity

2020-08-06 Thread Stephen Frost
Greetings, * Scott Ribe (scott_r...@elevated-dev.com) wrote: > when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", > it triggers this error: > > 2020-08-06 17:27:27.664 UTC [15914]: [3] > user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: > permis

Re: UUID or auto-increment

2020-08-10 Thread Stephen Frost
Greeitngs, * Ron (ronljohnso...@gmail.com) wrote: > On 8/10/20 11:38 AM, Ravi Krishna wrote: > >Finally UUID results in write amplication in wal logs.  Keep that in mind > >if your app does lot of writes. > > Because UUID is 32 bytes, while SERIAL is 4 bytes? and because it's random and so will

Re: UUID or auto-increment

2020-08-10 Thread Stephen Frost
Greetings, * Israel Brewster (ijbrews...@alaska.edu) wrote: > > On Aug 10, 2020, at 8:53 AM, Stephen Frost wrote: > > * Ron (ronljohnso...@gmail.com) wrote: > >> On 8/10/20 11:38 AM, Ravi Krishna wrote: > >>> Finally UUID results in write amplication in wal logs.

Re: pg_basebackup + delta base backups

2020-08-15 Thread Stephen Frost
Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > 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 rebui

Re: "Go" (lang) standard driver

2020-08-18 Thread Stephen Frost
Greetings, * Olivier Gautherot (ogauthe...@gautherot.net) wrote: > Le mar. 18 août 2020 à 09:36, Tony Shelver a écrit : > > -- Forwarded message - > > From: Tony Shelver > > Date: Tue, 18 Aug 2020 at 09:33 > > Subject: Re: "Go" (lang) standard driver > > To: Edson Richter > > >

Re: Point in time recovery

2020-08-18 Thread Stephen Frost
Greetings, Please don't top-post. * Ron (ronljohnso...@gmail.com) wrote: > Search the log file for errors? Sadly, this doesn't actually work if you're really doing PITR- you need to look for the specific message saying "recovery stopping before commit" or similar, eg: 2020-08-18 12:55:31.240 UT

Re: Database logins taking longer and longer, showing up as "authentication" in ps(1)

2020-08-18 Thread Stephen Frost
Greetings, * Adam Sjøgren (a...@koldfront.dk) wrote: > We have a PostgreSQL 11.3¹ running on an Ubuntu 16.04 server, which > sometimes exhibits a behaviour I can't quite understand: simply logging > into the database starts to take minutes to complete. > > We have 60 processes (workers) running o

Re: has_database_privilege is true?

2020-08-23 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > I create a role "test" and it can't (intentionally) login. But why does > has_database_privilege() return true if "test" can't connect to the cluster > and can't use any database? Is this a bug or am I doing something strange > here?

Re: has_database_privilege is true?

2020-08-23 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 23. Aug, 2020, at 16:28, Stephen Frost wrote: > > > > The role attribute system (where you see 'cannot login') is largely > > independent from the GRANT system (which is what has_database_pri

Re: SSL between Primary and Seconday PostgreSQL DBs

2020-09-03 Thread Stephen Frost
Greetings, * Susan Joseph (sandajos...@verizon.net) wrote: >- primary_conninfo = 'user=rep_user passfile=''/data/.pgpass'' > host= port=5432 sslmode=verify-ca sslcert=client.crt > sslkey=client.key sslcompression=0 target_session_attrs=any' You really should be using sslmode=verify-full, ot

Re: SSL between Primary and Seconday PostgreSQL DBs

2020-09-03 Thread Stephen Frost
Greetings, We really prefer it if you don't top-post on these lists but instead reply in-line, as I did. * Susan Joseph (sandajos...@verizon.net) wrote: > This is great, thanks.  I have not heard of verify-full but I will try that > and let you know if I have issues. > So I will remove the passf

Re: SSL between Primary and Seconday PostgreSQL DBs

2020-09-03 Thread Stephen Frost
Greetings, * Susan Joseph (sandajos...@verizon.net) wrote: > So I made the changes on the secondary to change the sslmode to verify-fullI > removed the clientcert=1 in pg_hba.conf and removed any connections other > than sslI removed the passfile info from recovery.confand now I am getting > th

Re: Autovacuum of independent tables

2020-09-09 Thread Stephen Frost
Greetings, * Michael Holzman (michaelholz...@gmail.com) wrote: > I have two applications A and B. A runs SELECT statements only and only on > tableA. B actively updates tableB, A never looks into tableB. B has nothing > to do with tableA. In an ideal world, such distinct applications would probab

Re: Handling time series data with PostgreSQL

2020-10-07 Thread Stephen Frost
Greetings, * Jayaram (jairam...@gmail.com) wrote: > So, Do we need the timescaleDB as mandatory to handle time series data? Is > there any way to handle hourly to days,months,yearly data with PGSQL alone > without timescale addon? Certainly there is and a lot of people do it- what isn't clear is

Re: Handling time series data with PostgreSQL

2020-10-07 Thread Stephen Frost
Greetings, * Mark Johnson (remi9...@gmail.com) wrote: > I think the OP may be referring to Oracle's Temporal Validity feature. Perhaps, but that's not the only way to manage time series data. > [ ... ] In earlier releases of each DBMS we tried to accomplish > the same by adding pairs of timestam

Re: UUID with variable length

2020-10-16 Thread Stephen Frost
Greetings, * Christophe Pettus (x...@thebuild.com) wrote: > > On Oct 15, 2020, at 13:49, Dirk Krautschick > > wrote: > > Or do you have some other ideas how to use a primary key datatype like UUID > > but with variable length? > > You're probably best off storing it as a VARCHAR() with a check

Re: Hot backup in PostgreSQL

2020-10-23 Thread Stephen Frost
Greetings, * Mark Johnson (remi9...@gmail.com) wrote: > User managed backups in PostgreSQL work very similar to what you know from > Oracle. You first place the cluster in backup mode, then copy the database > files, and lastly take the cluster out of backup mode. The first and last > steps are

Re: "Required checkpoints occurs too frequently"

2020-12-11 Thread Stephen Frost
Greetings, * Atul Kumar (akumar14...@gmail.com) wrote: > Please help me in optimizing the same to avoid this alert. Please don't post the same question to multiple lists, nor post the same question over and over to the same list with little time between them. Thanks, Stephen signature.asc Des

Re: Problem with ssl and psql in Postgresql 13

2020-12-23 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > I wrote: > > Gustavsson Mikael writes: > >> So if i set gssencmode=disable on my pgsql-13 to postgres 13 server > >> connection i get an SSL connection. > > > It looks like, if there is a credentials cache and gssencmode isn't > > explicitly d

Re: Problem with ssl and psql in Postgresql 13

2020-12-23 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> However: it is true (and undocumented, so we have at least a docs bug > >> to fix) that v12-and-later libpq will try for GSS encryption first, >

Re: Problem with ssl and psql in Postgresql 13

2020-12-30 Thread Stephen Frost
Greetings, First, thanks a lot for working on all of this and improving things! * Tom Lane (t...@sss.pgh.pa.us) wrote: > I've got one last complaint about the backend GSS code: we are doing > things randomly differently in the two places that install > krb_server_keyfile as the active KRB5_KTNAME

Re: LDAP(s) doc misleading

2021-01-06 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > I found this because I'm in the process of making our Linux LDAP servers > obsolete by reconfiguring PostgreSQL to use our company Windows Active > Directory LDAPS service. When in an Active Directory environment, it's far more secure

Re: Using more than one LDAP?

2021-01-08 Thread Stephen Frost
Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > Ok, since LDAP doesn't work that way, I either need to build GSSAPI packages > and have the AD admins to provide me with the keytab file or make the > transition a "hard" one, i.e. no transition phase. Though I'd rather have > liked t

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

2021-02-11 Thread Stephen Frost
Greetings, * Jagmohan Kaintura (jagmo...@tecorelabs.com) wrote: > Yup right now data is being accessed in this manner only. > application access using tenant user only who have specific tenantId in > that session and can see its own data only. It doesn't know about anyone > else's data and neither

Re: PG12: Any drawback of increasing wal_keep_segments

2022-03-29 Thread Stephen Frost
Greetings, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > On 2022-Mar-22, Shukla, Pranjal wrote: > > Are there any disadvantages of increasing the “wal_keep_segments” to a > > higher number say, 500? Will it have any impact on performance of > > streaming replication, on primary or secondary

Re: PG12: Any drawback of increasing wal_keep_segments

2022-03-31 Thread Stephen Frost
Greetings, On Thu, Mar 31, 2022 at 12:58 Marc wrote: > On 29 Mar 2022, at 17:17, Stephen Frost wrote: > > Greetings, > >- Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > > On 2022-Mar-22, Shukla, Pranjal wrote: > > Are there any disadvantages of increasing t

Re: Feedback about hybrid SAN snap and rsync'd approach for large systemcloning

2022-04-18 Thread Stephen Frost
Greetings, * Jerry Sievers (gsiever...@comcast.net) wrote: > Suppose we have a DB cluster with an additional tablespace and we are > able to make an atomic SAN snapshot of *only* the main cluster > volume... > > The additional tablespace contains only UNLOGGED relations. > > We cannot snap that

Re: About revoking large number of privileges; And the PUBLIC role.

2022-07-15 Thread Stephen Frost
Greetings, * Dominique Devienne (ddevie...@gmail.com) wrote: > So my goal is to delete all those "db specific" ROLEs, then the DB > with all its schemas. If you want to drop the database anyway.. then why not simply do that first? Nothing can be connected to a DB that's being dropped and we don'

Re: Setting up a server with previous day data

2022-08-23 Thread Stephen Frost
Greetings, * Srinivasa T N (seen...@gmail.com) wrote: >I have a primary postgresql 12 server which is being continuously used > for transaction processing. For reporting purposes, I want to set up a > secondary server which has got previous day data. Everyday night, I want > the data from pr

Re: pgBackRest on MacOS

2022-09-12 Thread Stephen Frost
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 9/10/22 07:31, Marc wrote: > >Has anybody pgbackrest running on MacOS ( Monterey ? ) > > > >If yes are you willing to share the how to ? > > > >Or can anybody guide us to an “easy” how to ? > > It's written in Perl, so installation from sourc

Re: pgbackrest Help Required

2022-09-27 Thread Stephen Frost
Greetings, * Inzamam Shafiq (inzamam.sha...@hotmail.com) wrote: > I am in process of configuring pgbackrest, I have followed documents and > configured backup from backup server. I have a setup of Primary and Hot > Standby, when I configured pgbackrest the standby DB got un-synced and now I > a

Re: PostgreSQL

2023-02-07 Thread Stephen Frost
Greetings, * Joseph Kennedy (joseph.kennedy@gmail.com) wrote: > Thank you Laurenz, I just wanted to make sure. > > Do you know any alternative solution to RLS ? ... to do what? If you want to limit the set of rows that a given user can see, RLS is how to do that. If you want to limit the

Re: can't get psql authentication against Active Directory working

2023-02-20 Thread Stephen Frost
Greetings, * Tomas Pospisek (t...@sourcepole.ch) wrote: > so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via > Active Directory. Looks like you're trying to do a bit more than that as you're using GSSAPI (not SSPI, which would be the more typical method on Windows..) and y

Re: can't get psql authentication against Active Directory working

2023-02-21 Thread Stephen Frost
Greetings, * Tomas Pospisek (t...@sourcepole.ch) wrote: > On 20.02.23 15:17, Stephen Frost wrote: > > * Tomas Pospisek (t...@sourcepole.ch) wrote: > > > so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via > > > Active Directory. > >

Re: RLS without leakproof restrictions?

2023-02-23 Thread Stephen Frost
Greetings, * Tom Dunstan (pg...@tomd.cc) wrote: > I'm currently researching different strategies for retrofitting some > multi-tenant functionality into our existing Postgres-backed application. > One of the options is using RLS policies to do row filtering. This is quite > attractive as I dread t

Re: can't get psql authentication against Active Directory working

2023-02-24 Thread Stephen Frost
Greetings, * Tomas Pospisek (t...@sourcepole.ch) wrote: > again thanks a lot for trying to help me! Sure, would like to see it work for you. > On 21.02.23 16:29, Stephen Frost wrote: > > * Tomas Pospisek (t...@sourcepole.ch) wrote: > > > On 20.02.23 15:17, Stephen Frost w

Re: can't get psql authentication against Active Directory working

2023-03-12 Thread Stephen Frost
Greetings, * Tomas Pospisek (t...@sourcepole.ch) wrote: > On 25.02.23 00:52, Stephen Frost wrote: > > * Tomas Pospisek (t...@sourcepole.ch) wrote: > > > On 21.02.23 16:29, Stephen Frost wrote: > > > > * Tomas Pospisek (t...@sourcepole.ch) wrote: > > > >

Re: Proposal: Shared Work Mem Area

2023-04-06 Thread Stephen Frost
Greetings, * Marco Fortina (marco_fort...@hotmail.it) wrote: > I would like to propose to developers a new feature to replace the private > management of the work mem with a management through shared memory: allocated > at the start of PostgreSQL this area should be shared to all worker processe

<    1   2   3   4   >