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
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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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/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: 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 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 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
