Error
I have upgraded my postgres database to 9.2 to 10.9. After upgrade getting below error : Java.lang.illegalstateexception: 3 rows retrieved for single property server is.Checksum. Can some one please help on this issue?
Re: Error
On Mon, Jul 29, 2019 at 2:22 PM Sonam Sharma wrote: > Java.lang.illegalstateexception: 3 rows retrieved for single property server > is.Checksum. This has nothing to do with PostgreSQL, at least I suspect you have got a query that is not strict and is returning more than a row. You need to provide to us (and yourself) some more information. A good starting point could be to enable logging of queries. Luca
Reproducing query plans in empty database: can I just copy stats and settings?
I work with Protected Health Information so have restricted access to an important database. I would like to look into index and query changes for that database. It occurs to me that if I was able to reproduce the statistics and cost settings for the database, then Explain would produce identical results to the production database. Can do this? Any other advice?
Re: Reproducing query plans in empty database: can I just copy stats and settings?
On 7/29/19 12:07 PM, Guyren Howe wrote: I work with Protected Health Information so have restricted access to an important database. I would like to look into index and query changes for that database. It occurs to me that if I was able to reproduce the statistics and cost settings for the database, then Explain would produce identical results to the production database. Can do this? Any other advice? So what defines restricted access? If you can't see/use the data in the production database in your test database I'm not sure how copying the statistics/cost settings is going to help. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Reproducing query plans in empty database: can I just copy stats and settings?
On Jul 29, 2019, at 12:25 , Adrian Klaver wrote: > > On 7/29/19 12:07 PM, Guyren Howe wrote: >> I work with Protected Health Information so have restricted access to an >> important database. >> I would like to look into index and query changes for that database. It >> occurs to me that if I was able to reproduce the statistics and cost >> settings for the database, then Explain would produce identical results to >> the production database. >> Can do this? Any other advice? > > So what defines restricted access? > > If you can't see/use the data in the production database in your test > database I'm not sure how copying the statistics/cost settings is going to > help. Isn’t that the entirety of what the query plan is based on? I was hoping I could then run Explain as if I had the data.
Re: Reproducing query plans in empty database: can I just copy stats and settings?
Guyren Howe writes: > On Jul 29, 2019, at 12:25 , Adrian Klaver wrote: >> If you can't see/use the data in the production database in your test >> database I'm not sure how copying the statistics/cost settings is going to >> help. > Isn’t that the entirety of what the query plan is based on? No; physical sizes of the tables and indexes also matter. (The planner scales some cost values by those numbers to account for the likelihood that tables have grown since they were last analyzed.) You could deal with that by bloating them with dummy data, of course. I think though that Adrian's point is a bit different: if you're not allowed to see the original data, you should not be allowed to see the statistics either. The histogram values and most-common-values lists represent a pretty sizable information leak for such cases. regards, tom lane
Re: Reproducing query plans in empty database: can I just copy stats and settings?
On 7/29/19 3:08 PM, Tom Lane wrote: Guyren Howe writes: On Jul 29, 2019, at 12:25 , Adrian Klaver wrote: If you can't see/use the data in the production database in your test database I'm not sure how copying the statistics/cost settings is going to help. Isn’t that the entirety of what the query plan is based on? No; physical sizes of the tables and indexes also matter. (The planner scales some cost values by those numbers to account for the likelihood that tables have grown since they were last analyzed.) You could deal with that by bloating them with dummy data, of course. I think though that Adrian's point is a bit different: if you're not allowed to see the original data, you should not be allowed to see the statistics either. The histogram values and most-common-values lists represent a pretty sizable information leak for such cases. Yeah that and access to the actual data. Not sure how you could run a query that replicated the production(even with cloned stats) if the table(s) are missing data. I could see throwing in dummy data, but it would need to correlate with the actual data and you don't know what that is. Then there is, what happens if you manually or autovacuum/analyze the test data? regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
How do I create a Backup Operator account ?
Hi all. I need to create a PostgreSQL account , that only do a Backup and Restore operations over a PGSQL Database . My main difficulty is to only assign Backup and Restore capability to one user account, unlike the ability to perform DQL and DML operations for ordinary user accounts. In POSTGRESQL there is no backup / restore privilege because these operations are performed by server specific utilities. One possibility that I thought would be: I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account with a specific menu setting of a client tool. Could someone give me any ideas or directions?
Re: How do I create a Backup Operator account ?
> On Jul 29, 2019, at 7:49 PM, Marcos Aurelio Nobre > wrote: > > Hi all. > > I need to create a PostgreSQL account , that only do a Backup and Restore > operations over a PGSQL Database . > > My main difficulty is to only assign Backup and Restore capability to one > user account, unlike the ability to perform DQL and DML operations for > ordinary user accounts. I’m not sure I understand what you are really trying to do. If this Backup/Restore users is going to backup and restore the complete database then they will have to have essentially superuser permissions. Otherwise they will not have access to all of the data. They will have to have permission for DDL and DML as the restore has the potential for dropping and creating a bunch of objects, and at least DELETE, TRUNCATE, COPY and/or INSERT data. What are you trying to protect against? > > In POSTGRESQL there is no backup / restore privilege because these operations > are performed by server specific utilities. > > One possibility that I thought would be: > > I was wondering if it was possible to configure the pgAdmin4 menus to be > available for a particular login, only Backup & Restore items. But I'm not > sure how to associate a bank login account with a specific menu setting of a > client tool. > > Could someone give me any ideas or directions? Neil https://www.fairwindsoft.com
Re: How do I create a Backup Operator account ?
On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre wrote: > I was wondering if it was possible to configure the pgAdmin4 menus to be > available for a particular login, only Backup & Restore items. But I'm not > sure how to associate a bank login account with a specific menu setting of a > client tool. I think that hiding features from a GUI is a poor idea to protect your data (from what?). If you are talking of a single database (or a restricted set of), you can provide a role with the less privileges, like only SELECT and use such account to do the backup. But you need all the grants on another account to restore the backup. Anyway, I think you should rethink about your aim: what's the point of having a restricted user who can backup ALL the data? Luca
