Re: [GENERAL] PostgreSQL-related legal question

One of my sons was hired by Google last year after spending the past several years working on various open-source projects, it took 2 days of back-and-forth with Google's legal department before he was satisfied with the restrictions in their offer. -- Mike Nolan On Wed, Mar 11, 2015 at 4:46 PM, J

[GENERAL] Consistent state for pg_dump and pg_dumpall

The documentation for pg_dump says that dump files are created in a consistent state. Is that true across multiple tables in the same pg_dump command? (Obviously it would not be true if I dumped tables using separate pg_dump commands. But if I put the database into a backup state using 'pg_start_

Re: [GENERAL] Consistent state for pg_dump and pg_dumpall

On Wed, May 20, 2015 at 12:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > Yes. The entire dump is performed within a single transaction. > > On Wed, May 20, 2015 at 9:24 AM, Michael Nolan wrote: > >> The documentation for pg_dump says that dum

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 12:23 PM, Casey Deccio wrote: > I have a database in which one table references the primary key of > another. The type of the primary key was initially int, but I changed it > to bigint. However, I forgot to update the type of a column that > references it. So, I've init

Re: [GENERAL] Average New Users Per DOW

On 7/6/15, Robert DiFalco wrote: > I'm not sure how to create a result where I get the average number of new > users per day of the week. My issues are that days that did not have any > new users will not be factored into the average, giving an overinflated > result. > > This is what I started wit

Re: [GENERAL] Average New Users Per DOW

On Mon, Jul 6, 2015 at 5:50 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan wrote: > >> > But you can see it wont give correct results since (for example) >> Monday's >> > with no new user

Re: [GENERAL] Average New Users Per DOW

Here's a minor refinement that doesn't require knowing the range of dates in the users table: (select created, created as created2, count(*) as total from users group by 1, 2 union (select generate_series( (select min(created)::timestamp from users), (select max(created)::timestamp from users), '1

Re: [GENERAL] A table of magic constants

On Sat, Jul 11, 2015 at 4:53 PM, Vincent Veyron wrote: > On Sat, 11 Jul 2015 16:55:44 -0400 > Dane Foster wrote: > . After a while, you'll find your way around the documentation. > > I've been doing it almost every day for years, still learning every time. > I highly recommend reading the d

Re: [GENERAL] postgres zeroization of dead tuples ? i.e scrubbing dead tuples with sensitive data.

On Wed, Nov 18, 2015 at 4:38 PM, Adrian Klaver wrote: > >> Alright, I was following you up to this. Seems to me deleted data would > represent stale/old data and would be less valuable. > >> >> It may depend on WHY the data was deleted. If it represented, say, Hillary Clinton's deleted email, rec

Re: [GENERAL] Regex help again (sorry, I am bad at these)

On Mon, Dec 28, 2015 at 2:08 PM, Christopher Molnar wrote: > Hello all! > > Sorry to have to ask the experts here for some regex assistance again. I > am admittadly awful with these and could use some help. > > Any suggestions? > I have found over the years that it is far easier to write a short

[GENERAL] Bypassing NULL elements in row_to_json function

I'm looking at the possibility of using JSON as a data exchange format with some apps running on both PCs and Macs. . The table I would be exporting has a lot of NULL values in it. Is there any way to skip the NULL values in the row_to_json function and include only the fields that are non-null

Re: [GENERAL] Bypassing NULL elements in row_to_json function

It looks like json_strip_nulls() may be what I need, I'm currently on 9.3, which doesn't have that function but may be in a position to upgrade to 9.5 this summer. I think the apps that would be receiving the data can deal with any resulting 'holes' in the data set by just setting them to null. -

Re: [GENERAL] Bypassing NULL elements in row_to_json function

I was able to try it on a test server, the combination of row_to_json() and json_strip_nulls() worked exactly as I had hoped. Stripping nulls reduced the JSON file by over 50%. (The data I needed to export has around 900,000 rows, so it gets quite large.) I've got a test file I can make available

Re: [GENERAL] Bypassing NULL elements in row_to_json function

2nd Followup: It turns out that loading a table from a JSON string is more complicated than going from a table to JSON, perhaps for good reason. There does not appear to be a direct inverse to the row_to_json() function, but it wasn't difficult for me to write a PHP program that takes the JSON fil

Re: [GENERAL] Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan wrote: > >> >> 2nd Followup: It turns out that loading a table from a JSON string is >> more complicated than going from a t

Re: [GENERAL] Bypassing NULL elements in row_to_json function

Here's what I did: \d gold1604_test Table "uscf.gold1604_test" Column | Type | Modifiers +--+--- data | json | Some sample data: {"id":"1001","name":"MISNER, J NATHAN","st":"NY","exp":"2012-05-31","sts": "A"} + {"id":"1002","name":"MISNER, JUDY","st":"TN","exp

Re: [GENERAL] Bypassing NULL elements in row_to_json function

In case it wasn't clear, the sample data was 3 rows of data. (There are actually around 890K rows in the table pgfutter built from the JSON file.) - Mike Nolan

Re: [GENERAL] Build in function to verify email addresses

On Thu, Nov 16, 2017 at 1:56 AM, Nick Dro wrote: > I beleieve that every information system has the needs to send emails. > Currently PostgreSQL doesn't have a function which gets TEXT and return > true if it's valid email address (x...@yyy.com / .co.ZZ) > Do you believe such function should exis

[GENERAL] SOLVED: Corrupted index file after restoring WAL on warm spare server

Problem solved! It turns out the warm spare server wasn't the same release of Linux, though that wasn't quite the problem. However, it also an older version of the tar utility. When I restored the index file with the problem, it came out as 32768 bytes long rather than 40960 bytes long. Upgrad

Re: [GENERAL] Corruption of files in PostgreSQL

On 6/2/07, Paolo Bizzarri <[EMAIL PROTECTED]> wrote: What we are trying to understand is, first and foremost, if there are known cases under which PostgreSQL can truncate a file. I think it's somewhat more likely that whatever is sending the file to PG is the cause, either in how it handles

Re: [GENERAL] Date style handling changes between 7.4.12 and 8.2.4

What year would your example choose? The following works in 8.2.4: select 'Wed Jul 11 2007 10:51:14 GMT+01:00'::timestamp with time zone timestamptz 2007-07-11 06:51:14-05 It appears to ignore the day of the week, though: select 'Mon Jul 11 2007 10:51:14 GMT+01:00'

[GENERAL] What's the logical counterpart of the to_hex function?

I have data (from an external source) which is in text format as a hex number (it's actually an IP address, but that's probably not relevant.) to_hex gets from integer to hex, I can cast a hex constant to integer (ie x'12a7'31'::int, but how do I get a database column from string/hex to integer?

Re: [GENERAL] What's the logical counterpart of the to_hex function?

On 7/16/07, Steve Atkins <[EMAIL PROTECTED]> wrote: On Jul 16, 2007, at 11:36 AM, Michael Nolan wrote: > I have data (from an external source) which is in text format as a > hex number (it's actually an IP address, but that's probably not > relevant.) It likely is r

[GENERAL] Detailed logging, but only for one user?

Is there any way to enable detailed logging (ie, at the SQL statement level) but only for one user? -- Mike Nolan

Re: [GENERAL] DBMS upgrade and backups

On Mon, Mar 14, 2011 at 1:38 PM, Vick Khera wrote: > On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov wrote: > > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 > to > > 9.1) I'll loose my backups (base backups and wal files will be useless)? > So > > to go to past a

Re: [GENERAL] Merge rows based on Levenshtein distance

I don't think you've defined your problem very clearly. Suppose you have 1000 names in your database. Are you planning to compare each name to the other 999 names to see which is closest? What if two names are equally close to a third name but not to each other, how do you decide which is better

Re: [GENERAL] Merge rows based on Levenshtein distance

Have you considered using a soundex function to sort names into similarity groups? In my experience it works fairly well with Western European names, not quite as well with names from other parts of the world. It also doesn't deal well with many nicknames (Mike instead of Michael, etc.) -- Mike

[GENERAL] Blocking access by remote users for a specific time period

I have several web apps that access our Postgresql database that I'd like to lock out of the database for about an hour during a weekly maintenance interval. (There are some internal users that do not get locked out, because they're running the maintenance tasks.) There are no time-of-day access l

Re: [GENERAL] Blocking access by remote users for a specific time period

Yeah, a cron job to swap pg_hba.conf files is the best solution I've come up with so far. It's not one web app, it's closer to two dozen of them, on multiple sites. -- Mike Nolan On Sat, Dec 13, 2014 at 11:10 PM, Adrian Klaver wrote: > > On 12/13/2014 08:13 PM, Michael Nol

[GENERAL] How to analyze a slowdown in 9.3.5?

I'm running 9.3.5 on a virtual machine with 5 cores and 24 GB of memory. Disk is on a SAN. I have a task that runs weekly that processes possibly as many as 120 months worth of data, one month at a time. Since moving to 9.3.5 (from 8.2!!) the average time for a month has been 3 minutes or less.

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

data so it'll be an even longer run than this week's was.) -- Mike Nolan On Sat, Jan 10, 2015 at 12:55 PM, Andy Colson wrote: > On 01/09/2015 07:52 PM, Tomas Vondra wrote: > >> On 9.1.2015 23:14, Michael Nolan wrote: >> >>> I'm running 9.3.5 on a virtual m

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

On Fri, Jan 9, 2015 at 7:52 PM, Tomas Vondra wrote: > On 9.1.2015 23:14, Michael Nolan wrote: > > I'm running 9.3.5 on a virtual machine with 5 cores and 24 GB of > > memory. Disk is on a SAN. > > > > I have a task that runs weekly that processes possibly as many

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

On Sat, Jan 10, 2015 at 8:54 PM, Melvin Davidson wrote: > Just curious. Have you checked that the tables are being vacuum/analyzed > periodically and that the statistics are up to date? Try running the > following query to verify: > > A vacuum analyze runs every night and there would not have bee

Re: [GENERAL] Re: Stuck trying to backup large database - best practice? How about a cloud service?

On Mon, Jan 12, 2015 at 7:46 PM, Bob Futrelle wrote: > You should be able to find a cloud provider that could give you many TB. > Or so they like to claim. > > > Nope, but you probably find one willing to SELL you access to many TB. -- Mike Nolan

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

For what it's worth, this week's run covered even more months than last week's did, and ran in about 5 1/2 hours, with no slowdowns, under a similar system load. So, it could have been a one-time thing or some combination of factors that will be difficult to reproduce. -- Mike Nolan -- Sent via

Re: [GENERAL] Temporarily suspend a user account?

Might not do what you want, but I just change the password. -- Mike Nolan On Fri, Feb 6, 2015 at 4:11 PM, Melvin Davidson wrote: > Possibly, > > To disble: > ALTER USER name RENAME TO xname; > > To enable > ALTER USER xname RENAME TO name; > > ??? > > > On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gas

Re: [GENERAL] Temporarily suspend a user account?

On 2/6/15, David G Johnston wrote: > On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] < > ml-node+s1045698n5836989...@n5.nabble.com> wrote: > >> Might not do what you want, but I just change the password. >> >> > ​How do you do that and r

Re: [GENERAL] query by partial timestamp

On 1/8/13, Gavan Schneider wrote: > 2. SELECT ... WHERE > '2011-01-01'::TIMESTAMP <= col_of_type_timestamp > ANDcol_of_type_timestamp <= > '2011-12-31'::TIMESTAMP; This won't quite work, because '2011-12-31'::TIMESTAMP is the same as 2011-12-31 00:00:0

Re: [GENERAL] query by partial timestamp

It is probably not the most efficient, but I often use this syntax, which reads better. Select . where col_type_timestamp::date between '2011-01-01' and '2011-12-31' This will use a timestamp index. -- Mike Nolan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] .pgpass and root: a problem

On Tue, Feb 5, 2013 at 1:57 PM, Scott Mead wrote: > > > I would love to see pgpass storing encrypted stuff here, that'd be > great... in the meantime... > > I would suggest going one step further, and making encrypted pgpass authorization something that has to be specifically enabled in pg_hba.co

Re: [GENERAL] Money casting too liberal?

On 3/27/13, Steve Crawford wrote: > Somewhat more worrisome is the fact that it automatically rounds input > (away from zero) to fit. > > select '123.456789'::money; >money > - > $123.46 So does casting to an integer: select 1.25::integer ; int4 1 And then there's this:

Re: [GENERAL] How large can a PostgreSQL database get?

On 4/17/13, Scott Marlowe wrote: > My experience, doing production and dev dba work on both postgresql > and oracle, is that either works well, as long as you partition > properly or even break things into silos. Oracle isn't magic pixie > dust that suddenly gets hardware with 250MB/s seq read arr

Re: [GENERAL] How to clone a running master cluster?

On 5/11/13, Moshe Jacobson wrote: > I have a master database cluster on one server, and it is configured to > ship logs via scp to an archive directory on my slave server. The slave > server is configured for streaming replication, and also is configured to > delete the archived xlogs when they ar

Re: [GENERAL] incremental dumps

On 8/1/13, haman...@t-online.de wrote: > Hi, > I want to store copies of our data on a remote machine as a security > measure. > Wolfgang 2 questions: 1. How secure is the remote site? 2. How much data are we talking about? -- Mike Nolan -- Sent via pgsql-general mailing list (pgsql-gener

Re: [GENERAL] incremental dumps

On 8/10/13, haman...@t-online.de wrote: > currently the source uses some 20 GB in a database partition and about 700 > GB > in a general data partition. For the database, a diff -e grows to about 10% > of the size > of a full dump in a week > The remote site is a raid box at a hosting center, wit

Re: [GENERAL] question about age()

On 8/29/13, Andreas Kretschmer wrote: > is there a bug in age()? > > test=*# select *, age(birthday), age (current_date-1, birthday) from > birthday ; > id | birthday | age | age > ++-+- > 1 | 2010-08-29 | 3 years | 2 years 11 mons

Re: [GENERAL] question about age()

On 8/29/13, Michael Nolan wrote: > On 8/29/13, Andreas Kretschmer wrote: > >> I'm using 9.2.4. > > > What is the content of the field 'birthday''? My guess is there's a > null value for the field, in which case you are comparing two nulls. Oo

Re: [GENERAL] Nice to have features: Percentage function

I also have some pre-defined percentage functions, they check the denominator and return null if it is zero, to avoid 'divide by zero' errors. -- Mike Nolan On Sun, Apr 16, 2017 at 11:37 AM, Melvin Davidson wrote: > > > On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver > wrote: > >> On 04/15/2017

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

On Mon, Oct 23, 2017 at 3:14 AM, Martin Moore wrote: > Same server. I tried a few times. > > I didn’t move the db separately, but did a ‘dd’ to copy the disk to an > imagefile which was converted and loaded into VMWare. > > I ‘believed’ that this should keep the low level disk structure the same,

Re: [GENERAL] How to switch file systems with least downtime?

Have you considered setting up a synchronously replicated slave database on the new file system (using a port other than 5432), getting it in sync, then shutting both databases down (master first), switching the slave over to become the master and restarting just that database on port 5432? -- Mike

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

Assuming the database hasn't changed much since the failover, doing a fsync from the new primary back to the old primary should be fairly quick. -- Mike Nolan On 9/19/13, Vick Khera wrote: > On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com > > wrote: > >> I use PG 9.2.4 with streaming repli

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

On 9/19/13, John R Pierce wrote: > On 9/19/2013 1:29 PM, Vick Khera wrote: >> >> On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com >> > > wrote: >> >> I use PG 9.2.4 with streaming replication. What will be the >> manual proc

Re: [GENERAL] pg_dumpall from a script

You could write a plperlul function that runs a shell script to back up your database, you can even pass it parameters and put a call to that in a trigger. BUT, this could result in multiple backups running at the same time and become a performance drag. -- Mike Nolan On Tue, Oct 22, 2013 at 9:1

Re: [GENERAL] Junk date getting uploaded into date field

On 11/5/13, bsreejithin wrote: > > I am not sure why : select to_date('33-OCT-2013', 'dd-mon-') > > is returning 2013-11-02. > > For cases like the issue I am facing, where we need to raise an error > saying > the data is wrong, DB manipulating the data is not proper. Try using a cast to date

Re: [GENERAL] to_date() and invalid dates

Thomas, try this: '2013-02-31'::date -- Mike Nolan On Mon, Jan 20, 2014 at 7:44 AM, Thomas Kellerer wrote: > Hi, > > I asked this a while back already: > >select to_date('2013-02-31', '-mm-dd'); > > will not generate an error (unlike e.g. Oracle) > > > However in the release notes of 9

Re: [GENERAL] Moving data from M$ JetDB file to Postgres on Linux

I think that PHP has modules (eg, PEAR) that can read MS Access database files, and once you have it in an array you can create INSERT statements for PostgreSQL, including cleaning up any data format issues (eg, dates of 00-00-) -- Mike Nolan On Fri, Feb 28, 2014 at 6:21 PM, Rich Shepard wrot

Re: [GENERAL] Mysterious DB reset

On 3/6/14, Israel Brewster wrote: > LOG: received smart shutdown request > LOG: autovacuum launcher shutting down > LOG: shutting down > LOG: database system is shut down > > However, there are no timestamps on any of the entries (can I fix that?) Yes, change the log_line_prefix in the pos

Re: [GENERAL] Problem with query

On 4/11/14, Chris Curvey wrote: > On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy < > susan.cass...@decisionsciencescorp.com> wrote: > >> I have a query with several joins, where I am searching for specific data >> in certain columns. Have you tried running each of your joins separately to see if

Re: [GENERAL] SSDs with Postgresql?

On Thu, Apr 21, 2011 at 10:33 AM, Florian Weimer wrote: > * Greg Smith: > > > The fact that every row update can temporarily use more than 8K means > > that actual write throughput on the WAL can be shockingly large. The > > smallest customer I work with regularly has a 50GB database, yet they >

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On Wed, Apr 27, 2011 at 10:42 AM, Thomas Harold wrote: > On 4/27/2011 9:16 AM, Thomas Harold wrote: > >> - SELinux is running, but there are no denied messages in >> /var/log/audit/audit.log and no setroubleshooting alerts in >> /var/log/messages either. >> >> > Well, interestingly enough it is SE

Re: [GENERAL] Query to return every 1st Sat of a month between two dates

On Wed, May 11, 2011 at 10:22 AM, Alex - wrote: > Hi, > is there an easy way to return the date of every first Saturday of a month > in a data range i.e. 2011-2013 > > This is one way to do it:, there are others: select '2011-01-01'::date + s.a as dates from generate_series(0,1095) as s(a) wher

[GENERAL] postgresql and encfs?

Has anyone successfully used encfs with postgresq recently? I'm not sure if this is specifically a postgresql problem, but I'm trying to get postgresql to run on an encrypted file system that is a copy of my live data directory tree (after a shutdown, of course) and am getting the following errors

Re: [GENERAL] postgresql and encfs?

On Wed, Jun 22, 2011 at 3:48 PM, Tom Lane wrote: > Michael Nolan writes: > > Has anyone successfully used encfs with postgresq recently? > > > PANIC: could not open file "pg_xlog/00010009000D" (log file > 9, > > segment 13): Invalid argume

[GENERAL] An amusing MySQL weakness--not!

Earlier today I was working on a MySQL database (not by choice, I assure you), and I typed a statement like this: Update tablexyz set field1 = '15' where field2 - 20; The '-' was supposed to be an equal sign, but MySQL executed it anyway. (Field2 is an integer.) I was not amused. PostgreSQL rep

Re: [GENERAL] Contrib source

On Thu, Jun 30, 2011 at 10:17 AM, wrote: > D'oh! I didn't recall that it was packaged together, but the contrib > source isn't in src, where I looked. Oh well. > IIt's not a separate file, there should be a contrib subdirectory in the source code file. However, if you're using a packaged pre-

Re: [GENERAL] Performance Monitoring of PostGRE

On Wed, Jul 6, 2011 at 3:57 AM, BangarRaju Vadapalli < bangarraju.vadapa...@infor.com> wrote: > Hi Everybody, > > ** ** > >We want to monitor the performance of PostGRE database. Could anyone > please suggest any tools tried/working successfully… > > ** ** > > **1. **We want AWR

Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions

On Fri, Jul 8, 2011 at 10:27 PM, Robert Haas wrote: > > But if that's what you want, just don't put your data in different > databases in the first place. That's what schemas are for. > Sadly, DBAs don't always have the ability to put all their data in one database, even if that is what schemas

Re: [GENERAL] Slow query with sub-select

2011/7/16 - - > > The weird thing is that before I updated my server the query was about 5 > times faster. > Updated it from what to what, and how? -- Mike Nolan no...@tssi.com

Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?

On Mon, Jul 25, 2011 at 7:18 AM, Tomas Vondra wrote: > On 25 Červenec 2011, 11:39, Yan Chunlu wrote: > > I am using debian ant apt-get to install postgresql, dpkg list shows > > they are the same? is there anyway to tell what's version it is > > compiled from? thanks! > > AFAIK there's no way to

[GENERAL] Suggested enhancement to pg_restore

I suggest adding the following parameter to pg_restore: --rename-table= When used in conjunction with the --data-only, --schema and -t options (all three of which would be necessary), it would allow restoring a table (without indexes) to a different table name (which would need to already exi

Re: [GENERAL] Suggested enhancement to pg_restore

On Tue, Jul 26, 2011 at 6:10 PM, Chris Travers wrote: > On Tue, Jul 26, 2011 at 3:48 PM, Michael Nolan wrote: > > I suggest adding the following parameter to pg_restore: > > > > --rename-table= > > > > When used in conjunction with the --data-only, --schema a

[GENERAL] using xmin in a query?

Why does this query succeed: select count(*) from tablename where xmin = 2 while this query fails: select count(*) from tablename where xmin != 2 The latter will generate an error message (using 9.0.4, but it does not seem to be version specific): ERROR: operator does not exist: xid <> intege

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 12:23 PM, Andy Colson wrote: > On 7/28/2011 11:40 AM, Michael Nolan wrote: > >> Why does this query succeed: >> >> select count(*) from tablename where xmin = 2 >> >> while this query fails: >> >> select count(*) from tab

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 2:27 PM, Tom Lane wrote: > Michael Nolan writes: > > Why does this query succeed: > > select count(*) from tablename where xmin = 2 > > > while this query fails: > > > select count(*) from tablename where xmin != 2 > > It told you w

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 5:09 PM, Tom Lane wrote: > Michael Nolan writes: > > It seems like we're being inconsistent here in allowing 'where xid = > > integer' but not allowing 'where xid != integer'. > > Well, if you look into pg_operator you'

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 5:36 PM, Tom Lane wrote: > Michael Nolan writes: > > It also appears you cannot group on a column of type xid. > > You can in 8.4 and up. Previous versions only know how to GROUP BY > sortable columns, which requires a btree opclass, which xid doesn&#

Re: [GENERAL] securing the sql server ?

On Mon, Aug 22, 2011 at 3:40 AM, Condor wrote: > Hello ppl, > any one can tell me how I can secure linux server with database postgres > for example ? > Im thinking to make a cryptfs file system and to deploy database over the > cryptfs. The problem > here may will be when front end need any data

Fwd: [GENERAL] Disable Streaming Replication without restarting either master or slave

-- Forwarded message -- From: Michael Nolan Date: Tue, May 29, 2012 at 1:37 PM Subject: Re: [GENERAL] Disable Streaming Replication without restarting either master or slave To: Fujii Masao On Tue, May 29, 2012 at 1:15 PM, Fujii Masao wrote: > On Tue, May 29, 2012 at 10

Re: [GENERAL] Procedural Languages

On Thu, May 31, 2012 at 10:36 AM, John Townsend < jtowns...@advancedformulas.com> wrote: > There are least 10 Procedural > Languagesavailable for PostGreSQL. The > one that comes with the installation is > PL/pgSQL. > > Which ones do you use and why? > > T

Fwd: [GENERAL] Procedural Languages

-- Forwarded message -- From: Michael Nolan Date: Thu, May 31, 2012 at 2:49 PM Subject: Re: [GENERAL] Procedural Languages To: Darren Duncan On Thu, May 31, 2012 at 2:23 PM, Darren Duncan wrote: > Michael Nolan wrote: > >> PL/pgSQL and PL/perlu are the only on

Re: [GENERAL] Hot standby streaming replication doesn't work

On Sun, Jun 24, 2012 at 1:57 AM, Tim Uckun wrote: > I am following the instructions on the wiki > > https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#PITR.2C_Warm_Standby.2C_Hot_Standby.2C_and_Streaming_Replication > using the "10 minute" version of the setup. > > What version of postg

Re: [GENERAL] Replication/cloning: rsync vs modification dates?

On 7/16/12, Chris Angelico wrote: > I'm speccing up a three-node database for reliability, making use of > streaming replication, and it's all working but I have a bit of a > performance concern. > > > Can the individual files' modification timestamps be relied upon? If > so, it'd potentially mean

Re: [GENERAL] Replication/cloning: rsync vs modification dates?

On 7/16/12, Chris Angelico wrote: > On Tue, Jul 17, 2012 at 1:40 AM, Michael Nolan wrote: >> I did several weeks of tests on 9.1.3 using mod time and file size >> rather than checksumming the files, that did not appear to cause any >> problems >> and it sped up the rsy

Re: [GENERAL] Replication/cloning: rsync vs modification dates?

On 7/16/12, Sergey Konoplev wrote: > On Mon, Jul 16, 2012 at 8:01 PM, Chris Angelico wrote: >> On Tue, Jul 17, 2012 at 1:58 AM, Michael Nolan wrote: >>> As I understand the docs for rsync, it will use both mod time and file >>> size >>> if told not to do chec

Re: [GENERAL] Replication/cloning: rsync vs modification dates?

On 7/16/12, Steven Schlansker wrote: > I think it's pretty easy to show that timestamp+size isn't good enough to do > this 100% reliably. That may not be a problem if the slave server synchronization code always starts to play back WAL entries at a time before the worst case for timestamp precisi

Re: [GENERAL] Streaming replication and high query cancellation values

On 8/1/12, Christophe Pettus wrote: > I have a couple of questions about how streaming replication works in the > presence of a high timeout for query cancellation: Are you referring to queries on the slave? The master doesn't know what the slave is doing, so it would keep on shipping streaming

[GENERAL] Any experience with Drobo SAN and PG?

I'm looking to spec a new production server for a small client and have been looking at the Drobo SAN units. Has anybody run PG on one of these yet? It looks like only the B1200i supports Linux operating systems. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make cha

Re: [GENERAL] Equivalent to "use database" in postgre

On Fri, Jun 25, 2010 at 10:00 AM, Tom Lane wrote: > Scott Marlowe writes: > > On Fri, Jun 25, 2010 at 7:10 AM, javijava > wrote: > >> How can I say "use name_database" on postgre sql? > > > You have to re-connect to use a different db in pgsql. > > But keep in mind that mysql databases are more

Re: [GENERAL] Trigger for modification timestamp column

On Wed, Jul 7, 2010 at 8:48 AM, Johan Andersson wrote: > > Hello! > > I am trying to write a trigger for updating a modification column and am > having some trouble getting it to behave as I want. > > The trigger should set the column to the supplied value if it is set in the > UPDATE statement a

Re: [GENERAL] PITR - base backup question

I have what I have sometimes called a 'tepid spare' backup. Once a week I copy the physical files over to another system (actually to two of them) and every few hours I make sure the archived WAL log files are in sync (using rsync.) Anyway, here's the cookbook guide I wrote for updating one of th

Re: [GENERAL] PITR - base backup question

On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > 3. Shut down the Postgresql server running on the backup server, if any > > pg_ctl stop > > (Use 'ps ax' to make sure the server is stopped.) > > probably pg_ctl -m fast stop or -m immediate...since we a

Re: [GENERAL] Oracle and Postgresql

Oracle handles connecting to multiple databases (even on multiple/remote computers) fairly seamlessly, PG does not (yet.) Oracle's toolsets (for things like forms and reports) are much further developed than PG's too, though there are 3rd party tools that work with both. Back in the old mainframe

[GENERAL] Returning NEW in an on-delete trigger

Recently I discovered a coding error of mine in a trigger that is called only for deletes. I was returning NEW instead of OLD. Since NEW is undefined when deleting a row, it was failing and the row wasn't being deleted. However, it was failing silently. Shouldn't this have recorded an error or

Re: [GENERAL] Recommendations for SSDs in production?

On Thu, Nov 3, 2011 at 4:15 AM, Allan Kamau wrote: > > > How about SSDs on Raid 1+0 (I have no experience on SSD and RAID > though) and have replication to another server having the same setup > and still do frequent backups. The Crucial m4 SSDs seem to be > reasonably priced and perform well. >

Re: [GENERAL] How to isolate the result of SELECT's?

On Sun, Mar 18, 2012 at 6:33 PM, Andre Lopes wrote: > Hi, > > I need to do an operation that I will use some SELECT's and get the > results, but I want to have sure that those tables have not been > changed with INSERT's or UPDATES during the operation. > > Example: > > BEGIN OPERATION > Select f

Re: [GENERAL] huge price database question..

> > right now I am having about 7000 tables for individual stock and I use > perl to do inserts, it's very slow. I would like to use copy or other > bulk loading tool to load the daily raw gz data. but I need the split > the file to per stock files first before I do bulk loading. I consider > this

Re: [GENERAL] Problems with Binary Replication

On Sat, Mar 31, 2012 at 6:58 PM, Andreas wrote: > > > Now what could one do to prevent those sequence gaps? > There might be scenarios where it's important not to have gaps in the > numbering even when one has to switch to the standby if there is a failiour > on the master. > E.g. numbers of invo

Re: [GENERAL] 9.1.3: launching streaming replication

On Mon, Apr 2, 2012 at 4:21 PM, Welty, Richard wrote: I got similar messages the first few times I tried to start up my slave server, I never did figure out exactly what caused it. You can either delete all the files on the slave and try again, or do what I did, write a script that handles trans

Re: [GENERAL] 9.1.3: launching streaming replication

On Mon, Apr 2, 2012 at 6:19 PM, Michael Nolan wrote: > > > > I got similar messages the first few times I tried to start up my slave > server, I never did figure out exactly what caused it. > > One possibility is that I may not have restarted the master server after changin

Re: [GENERAL] 9.1.3 Standby catchup mode

On Thu, Apr 5, 2012 at 12:35 PM, hans wulf wrote: > I am wondering how the catchup mode of a hot synchron slave server works > on 9.1.3 if there is no WAL archive. > Why would you not want to maintain a WAL archive? Are you depending on the slave server(s) as your only form of backup? It isn't

  1   2   >