Re: Migration to PGLister - After
On 11/20/2017 6:45 AM, Stephen Frost wrote: The changes which we expect to be most significant to users can be found on the wiki here:https://wiki.postgresql.org/wiki/PGLister_Announce the current version of which is also included below. A peeve on this new configuration: if someone does a reply-all and sends both direct and list responses, the direct response ends up in my regular inbox because it doesn't have the List-ID... Previously, the direct reply would still have the Subject: [listname] that I filtered on, so it too would end up in my 'postgres' folder, where I want it. I realize why this was done, and yada yada, what a mess. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/20/2017 11:45 AM, Joshua D. Drake wrote: Perhaps one thing that could be done is a header (for a temporary time period) that says: The mailing list software of Postgresql.org has changed. Please see this page on instructions on how to manage your subscription and filters. vast majority of users never see the full headers, they just see From, To, Date, Subject, and everything else is hidden. on a phone or a web mail system like gmail, its very hard to see that stuff. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/20/2017 11:44 AM, Mengxuan Xia wrote: A common feature in modern Email client is to allow users to unsubscribe in one line. This works by sending a email titled "unsubscribe" to the listserv, which most listserv these days are able to understand and unsubscribe the user. thats a feature of many email list SERVERS, not the email clients. Mailman in particular looks for the major command keywords in the first few lines of the email, and will respond to them, even if they are sent to the regular list address instead of the command address. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/20/2017 12:07 PM, Joshua D. Drake wrote: I use Thunderbird which I imagine most people on the lists are using. I can't find where these would work to unsubscribe. In Thunderbird, ctrl-U shows the full headers, but the List-Unsubscribe links are NOT shown as hotlinks, so you have to copy/paste them to a browser. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/20/2017 12:20 PM, Magnus Hagander wrote: Not even remotely. People use gmail. See https://blog.hagander.net/mail-agents-in-the-postgresql-community-233/ <https://blog.hagander.net/mail-agents-in-the-postgresql-community-233/>. its rather hard to tell some of those colors apart on my monitor. Sigh, not surprised about gmail, its amazing how many .com's and .org's use it now. I'm not at ALL a fan of how the web client handles a bunch of stuff like replies, & hiding your own messages from yourself. I mostly prefer to use gmail as an imap server w/ thunderbird. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/20/2017 12:49 PM, John R Pierce wrote: Sigh, not surprised about gmail, its amazing how many .com's and .org's use it now. I'm not at ALL a fan of how the web client handles a bunch of stuff like replies, & hiding your own messages from yourself. oh and my biggest gripe, gmail seems to ignore the References header AND replies often don't seem to have that header, so if you're using a traditional threaded email client like Thunderbird, replies from gmail users don't seem to get threaded correctly and show up as new threads. gmail itself seems to thread purely based on Subject. -- john r pierce, recycling bits in santa cruz
Re: [MESSAGE PUBLICITAIRE- VIGILANCE] RE: Unsubscribe
On 11/20/2017 1:42 PM, Keith wrote: The link to this simple form below is right at the top of the other link where it says "How to Subscribe or Unsubscribe, Fill out this form." https://www.postgresql.org/community/lists/subscribe/ that form is for the legacy system. for the new system, it appears you must have an postgres community account to unsubscribe, at least per https://lists.postgresql.org/manage/ *HOWEVER*, there's a simpler way. Do whatever your email program requires to view full headers, in my case its 'ctrl-U' while viewing a message, and find the List-Unsubscribe: header, copy/paste that link to your browser, and voila, away you go. -- john r pierce, recycling bits in santa cruz
Re: unsubscribe
On 11/20/2017 7:40 PM, Tom Lane wrote: Depending on what mail software you use, you might have to use some command like "View raw headers" or "View original message" to see commonly-hidden headers like List-Unsubscribe. in gmail, its Show Original, on the down-tick menu next to the 'Reply' button. -- john r pierce, recycling bits in santa cruz
Re: To all who wish to unsubscribe
On 11/21/2017 10:39 AM, Andrew Sullivan wrote: On Mon, Nov 20, 2017 at 02:46:08PM -0800, Steve Atkins wrote: That's poor practice, for several reasons - replay attacks with added content and it being an extremely rare practice that's likely to trigger bugs in DKIM validation are two. The latter is the much bigger deal. It also doesn't help much for most MIME encoded mail (including base64 encoded plain text, like the mail I'm replying to). Pretending those paragraphs aren't there is the right thing to do. Yes. Also the DMARC and forthcoming ARC mechanisms -- super important for people behind gmail and yahoo and so on -- make that feature not really work, AFAICT. I think that part of DKIM is busted, and the authors of it I've talked to seem to agree. it seems to *ME* like a simpler solution to the original problem would have been to simply STRIP any DKIM out of the original messages, and continue to munge headers and footers like mail list reflectors have been doing for decades. -- john r pierce, recycling bits in santa cruz
Re: migrations (was Re: To all who wish to unsubscribe)
On 11/21/2017 10:59 AM, Andrew Sullivan wrote: All I, at least, was trying to say was that there were some of us who were entirely surprised, and I'm kind of amazed that a bunch of database people didn't announce a planned migration weeks in advance. they did. you must not have noticed it. -- john r pierce, recycling bits in santa cruz
Re: migrations (was Re: To all who wish to unsubscribe)
On 11/21/2017 11:42 AM, Joshua D. Drake wrote: I am not sure if that is sarcasm but I think the reason is pretty self explanatory. -Hackers have all the people that understand how all this works, -general has all the people that don't. rotfl, and ain't that the truth. -- john r pierce, recycling bits in santa cruz
Re: How clear the cache on postgresql?
On 11/24/2017 11:43 AM, Michael Nolan wrote: There are so many different levels of caching going on--within Postgresql, within the OS, within a disk array or SAN, and at the individual drive--that there may no longer be a meaningful way to perform this measurement. generally, power cycling the server will flush all the hardware caches AND the OS cache. -- john r pierce, recycling bits in santa cruz
Re: Roles and security
On 11/25/2017 12:03 PM, nikhil raj wrote: check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission those permissions can be assigned on a table by table basis, except create database, thats a special permission. if you own a database (the creator owns it by default) then you can drop it, if you don't, you can't. The users should not have these permission drop database , delete table permission generally the owners of objects can grant/revoke permissions on said objects. **(Example --like in MS SQL we have like DB_roles-- ddladmin,data writer,data reader ,etc like these do we have in postgres).*** you could create roles like these, make users members of these roles,a nd grant permissions to the roles. for more information, see https://www.postgresql.org/docs/current/static/user-manag.html https://www.postgresql.org/docs/current/static/sql-grant.html https://www.postgresql.org/docs/current/static/sql-revoke.html https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html -- john r pierce, recycling bits in santa cruz
Re: equalant of msdb in sql server
On 11/26/2017 8:10 PM, chandra sekhar wrote: What is the query to get list of the archive log files to capture start date and time of each archive log ls -l nfsserver:/path/to/archive -- john r pierce, recycling bits in santa cruz
Re: pg data backup from vps
On 12/1/2017 12:44 PM, basti wrote: Replication is no backup. Its more like a RAID. That mean tubles that are delete on master by a mistake there are also delete on slave. correct me if i'am wrong. a wal archive plus occasional basebackups lets you restore to any point in time (PITR) covered since the oldest basebackup. think of a base backup as a 'full' backup, and the wal logs in the archive as incrementals. one such approach might be a weekly basebackup, where you keep the last 4 weeks, and keep all wal files since the start of oldest basebackup. yes, this will take quite a bit of space -- john r pierce, recycling bits in santa cruz
Re: building a server
On 12/3/2017 12:47 PM, Gmail wrote: So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf Near the end (page 24) I spotted: Heavy use of server-side functions might generate significant CPU load Just want to confirm that this referring to workload on the mainly-single-threaded server not an advocacy for more cores. well, if you have significant concurrency with high CPU usage, you want both more AND faster cores -- john r pierce, recycling bits in santa cruz
Re: building a server
On 12/3/2017 1:39 PM, Rob Sargent wrote: Granted! I suppose I'm looking for confirmation/correction on believing that for the server to make use of multiple cores is more dependent on the nature of the queries handled. Concurrency should not be a huge problem for this project, though I've put pieces in place to mitigate that issue. a single connection will only ever use more than one core if its explicitly doing concurrent query. -- john r pierce, recycling bits in santa cruz
Re: JSON out of memory error on PostgreSQL 9.6.x
On 12/3/2017 3:18 PM, Yuri Budilov wrote: Posted on Stack Overflow, sadly no replies, so trying here ... ,,, why did you email me personally ? -- john r pierce, recycling bits in santa cruz
Re: JSON out of memory error on PostgreSQL 9.6.x
On 12/3/2017 3:18 PM, Yuri Budilov wrote: |CREATETABLEX ASSELECTjson_array_elements(json_rmq ->'orders'::text)ASorderFROMtable_name WHEREblah;| I get out of memory error. are you sure thats a postgres error ? are you doing this in psql, or what sort of application environment ? how many rows does 'blah' match ? what is... SELECT pg_column_size(json_array_elements(json_rmq -> 'orders'::text)) FROM table_name WHERE blah; ? -- john r pierce, recycling bits in santa cruz
Re: transaction wrap around
On 12/4/2017 2:21 PM, chris kim wrote: How would I investigate if my database is nearing a transaction wrap around. it would be screaming bloody murder in the log, for one. -- john r pierce, recycling bits in santa cruz
Re: Feature idea: Dynamic Data Making
On 12/5/2017 1:11 PM, Karsten Hilbert wrote: On Tue, Dec 05, 2017 at 09:59:22PM +0100, Riccardo Bassani wrote: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking There's already at least 4 mechanismus that come to mind which can do things like that: on SELECT RULEs VIEWs column GRANTs RLS yeah, but its so much cooler if you invent a bunch of proprietary methods of doing the same thing, and give it new marketing buzzwords !!! -- john r pierce, recycling bits in santa cruz
Re: a back up question
On 12/5/2017 2:09 PM, Martin Mueller wrote: Time is not really a problem for me, if we talk about hours rather than days. On a roughly comparable machine I’ve made backups of databases less than 10 GB, and it was a matter of minutes. But I know that there are scale problems. Sometimes programs just hang if the data are beyond some size. Is that likely in Postgres if you go from ~ 10 GB to ~100 GB? There isn’t any interdependence among my tables beyond queries I construct on the fly, because I use the database in a single user environment another factor is restore time. restores have to create indexes. creating indexes on multi-million-row tables can take awhile. (hint, be sure to set maintenance_work_mem to 1GB before doing this!) -- john r pierce, recycling bits in santa cruz
Re: Does Postgresql 10 query partitions in parallel?
On 12/6/2017 5:11 AM, Maltsev Eduard wrote: I'm curious if the new feature of Postgresql allows to take advantage of multiple cpus on server, and multiple servers (fdw), for larger read only queries (Data mining). In general there should be some worker that queries partitions and merges the results, and I expect it to be done in parallel. This becomes critical when foreign tables are used, I suppose. PostgreSQL 10 does indeed have a parallel query feature that will use multiple cores. you have to explicity invoke it. As this is a first implementation, its fairly limited as to the sorts of queries that can be parallized, but this will be enhanced in future versions. https://www.postgresql.org/docs/current/static/parallel-query.html (read the whole chapter) -- john r pierce, recycling bits in santa cruz
Re: Does Postgresql 10 query partitions in parallel?
On 12/6/2017 11:33 AM, Andres Freund wrote: PostgreSQL 10 does indeed have a parallel query feature that will use multiple cores. you have to explicity invoke it. "you have to explicitly invoke it" - huh? oops, I meant, enable. -- john r pierce, recycling bits in santa cruz
Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??
On 12/5/2017 10:59 AM, Eugene Poole wrote: 2. I don't want to use a RPM because I like controlling where software is installed then why are you using CentOS/RHEL ? those are by design RPM managed distribution, and rpms with their service scripts etc tend to be pretty fixed about where they need to be. things like selinux all need to know where the components live. the yum.postgresql.org RPMs all install the binary code to /usr/pgsql-X.Y/ and default the database location to /var/lib/pgsql/X.Y/data... if I need the data on a dedicated volume, I tend to mount said volume to /var/lib/pgsql before I initialize the database. reason I do the pgsql dir and not the version specific data directory is so pg_upgrade can use link mode to upgrade a cluster. -- john r pierce, recycling bits in santa cruz
Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??
On 12/5/2017 10:59 AM, Eugene Poole wrote: 3. Would I use ora2pg do do the move? Is ora2pg still maintained? 4. Is there a better conversion package? there are a variety of "ETL" tools out there that can extract data from one database and load it into another. I was going to suggest pgloader, but that doesn't support oracle, only mysql and ms sql server. a full scale conversion of a complex application suite, however, can be a several year project. Our old Oracle application was very heavy on plsql. We chose to reimplement the vast majority of the business logic in appserver modules outside the database and only used plpgsql for things where performance mattered significantly. the more heavily your apps use Oracle specific features, the more work the conversion will be. -- john r pierce, recycling bits in santa cruz
Re: Windows XP to Win 10 migration issue
On 12/9/2017 1:03 PM, Scott Mead wrote: I’m guessing that the old machine was 32 but and the New is 64? They have to match. You could always setup a 32 bit VM. It must be 32 bit windows (if that’s what the original was ) you should be able to run 32 bit postgresql on 64 bit windows. its a bit trickier than just the same version, however... they should be from the same distribution too... a version built with GCC and Cygwin might not be binary compatible with a version built with Microsoft Visual C (such as the EnterpriseDB versions) due to differences in runtime libraries. -- john r pierce, recycling bits in santa cruz
Re: PG Schema to be used as log and monitoring store
On 12/9/2017 5:46 PM, Stefan Keller wrote: Below I re-modeled it to a relational schema as you suggested and also tried to utilize the INHERITS feature. Does that look better? I believe I would use boolean, not bit. -- john r pierce, recycling bits in santa cruz
Re: PostgreSQL Client on SUSE Enterprise Linux Server 11.
On 12/13/2017 9:58 AM, PAWAN SHARMA wrote: How to Install PostgreSQL Client on SUSE Enterprise Linux Server 11. yum install postgresql ^ I believe that should do it, at least thats what you do on RHEL/CentOS... postgresql-server is the server package, plain postgresql is the client package, and includes/depends on postgresdql-libs which are the runtime libaries. -- john r pierce, recycling bits in santa cruz
Re: Can postgresql ignore DST ?
On 12/14/2017 9:17 PM, Venkata B Nagothi wrote: On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin <mailto:b...@ausvet.com.au>> wrote: I'd be a little worried that if you set timezone = 11 for Australia/Sydney you are embedding the daylight savings value, not the standard time value (UTC+10) Totally agree. We have a weird situation where-in i had to do this and i would like to learn the impact on the data, i hope it would not fiddle the existing data. We are currently experimenting this. in PostgreSQL, fields that are type TIMESTAMP WITH TIME ZONE convert all input time values to UTC, and store it in an internal representation, and on output, they are converted to the client's current TIMEZONE. -- john r pierce, recycling bits in santa cruz
Re: pgbench
On 12/15/2017 7:37 AM, Olga Lytvynova-Bogdanova wrote: Is there a way to integrate pgbench with TeamCity? If yes, could you share very briefly how to do this? I would suspect this is a question for TeamCity, not for postgresql. I don't even know what TeamCity actually is (google says 'Continuous Integration' but thats just a buzz phrase to me). Can it run shell scripts? Which volumes of data does pgbench support? I'm unclear what you mean by 'which volumes of data' ? do you mean, how large of a dataset does pgbench generate and use? thats totally configurable with commandline parameters, from a few dozen kilobytes to many gigabytes. -- john r pierce, recycling bits in santa cruz
Re: Foreign Data Wrapper
On 12/21/2017 5:14 AM, Virendra Shaktawat - Quipment India wrote: odbc_fdw please don't top post, and please don't use graphics and HTML in your email, this is a text based mailling list adhering to bottom/interleaved posting standards. While I'm not directly familiar with odbc_fdw, I suspect its a 'read only' FDW is that this odbc_fdw? https://github.com/ZhengYang/odbc_fdw that was developed for pg 9.1 when ALL fdw's were read only as it was brand new technology. since this defines the foreign table via a query, well, there's no WAY it could be used to do an update. this updated fork, https://github.com/CartoDB/odbc_fdw runs on newer postgres, but also is based on a select statement, you can't UPDATE a resultset... -- john r pierce, recycling bits in santa cruz
