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

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] 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] 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] 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

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

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

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

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. -

[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] 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

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] 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] 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] 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

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] 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] 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

[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] 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

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] 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] 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] 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?

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] 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?

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

[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] 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] 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] 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

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] 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] 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] 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] 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] 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] 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] 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] 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 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] 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] 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] 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] 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] 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] 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] 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] .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] 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] 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

[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] 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

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] 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, 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, 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] 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

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] 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] 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] Streaming replication: sequences on slave seemingly ahead of sequences on master

On Mon, May 7, 2012 at 4:01 AM, Vincent de Phily < vincent.deph...@mobile-devices.fr> wrote: > On Sunday 06 May 2012 10:29:17 Simon Riggs wrote: > > On 4 May 2012 14:55, Vincent de Phily > > wrote: > > > Would be nice to see it added to the documentation (unless I just didn't > find > it ?), as

Re: [GENERAL] Lost one tablespace - can't access whole database

On Sat, May 5, 2012 at 4:19 PM, Stefan Tzeggai wrote: > Hi > > postgresql 9.1 on Ubuntu 10.04 > > All important information is in the other tablespaces. I would be > totally happy to just loose all relations in that lost tablespace. It's > just indexes. Is there any way to tell PG to drop/ignore t

Re: [GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master

This is due to how sequences are pre-allocated in blocks to sessions running on the master. Since the slave is updated via the WALs, and not via 'nextval' function calls in queries, the sequences that are actually used will remain in sync with the master. -- Mike Nolan

Re: [GENERAL] Issue with rsync based incremental backup : postgres: startup process waiting for 0000000100000001000000D2

On Thu, May 3, 2012 at 11:49 AM, Samba wrote: > Hi, > > > Please advise me if what i'm doing is makes sense and is an accepted > mechanism for taking backups or if there is any other procedure that i can > emplpoy to avoid unnecessarily archiving gigabytes of WAL logs which may be > growing many

Re: [GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

Your options range from doing something simple to something complex. A simple option on a Linux server would be placing a command like this in /etc/rc/rc.local: su - postgres -C "/usr/local/pgsql/bin/pg_ctl -D ;/usr/local/pgsql/data -l /usr/local/pgsql/logfile start" However, that might not be t

Re: [GENERAL] Backups using Solaris ZFS Snapshots

On Tue, Apr 24, 2012 at 4:08 PM, Yunong J Xiao wrote: > I am currently backing up my postgres instances using ZFS snapshots > instead of the sanctioned pg_dump utility mainly because I am running on > Solaris and it offers a copy-on-write file system. Anecdotally this has > been working fine for

Re: [GENERAL] PostgreSQL 9.1 Hot Backup Error: the database system is starting up

On Thu, Apr 19, 2012 at 1:07 PM, Michael Nolan wrote: > > > On Thu, Apr 19, 2012 at 12:46 PM, Jen wrote: > >> I have been working on a hot backup for Postgres 9.1 for awhile and have >> run >> into a consistent issue. >> > > The instructions in the Binar

Re: [GENERAL] PostgreSQL 9.1 Hot Backup Error: the database system is starting up

On Thu, Apr 19, 2012 at 12:46 PM, Jen wrote: > I have been working on a hot backup for Postgres 9.1 for awhile and have > run > into a consistent issue. > The instructions in the Binary Replication Tutorial work well for me, I suggest you read through the '10 minute' version. Specifically, look

Re: [GENERAL] LOCK TABLE is not allowed in a non-volatile function

On Tue, Apr 17, 2012 at 5:20 PM, Eliot Gable wrote: > > > > I cannot find a single non-volatile function in the call path; so I am > baffled on where this error message is coming from. I would be thankful for > any ideas anyone might have on where this error message might be coming > from or how

Re: [GENERAL] Searchable chess positions in a Postgress DB

On Thu, Apr 12, 2012 at 4:50 PM, Gavin Flower wrote: > On 11/04/12 21:24, Gavin Flower wrote: > > On 11/04/12 19:15, Sidney Cadot wrote: > > Dear all, > > As a hobby project, I am toying around with a database containing > about 5 million chess games. On average, these games have about 80 > posi

Fwd: [HACKERS] [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?

-- Forwarded message -- From: Michael Nolan Date: Wed, 11 Apr 2012 14:48:18 -0400 Subject: Re: [HACKERS] [GENERAL] [streaming replication] 9.1.3 streaming replication bug ? To: Robert Haas On Wed, Apr 11, 2012 at 2:14 PM, Robert Haas wrote: > > > We've talked

Re: [HACKERS] [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?

On 4/11/12, Fujii Masao wrote: > On Wed, Apr 11, 2012 at 3:31 PM, 乔志强 wrote: >> So in sync streaming replication, if master delete WAL before sent to the >> only standby, all transaction will fail forever, >> "the master tries to avoid a PANIC error rather than termination of >> replication." but

Re: [HACKERS] [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?

On 4/11/12, Kevin Grittner wrote: > Michael Nolan wrote: >> On 4/11/12, 乔志强 wrote: > >>> But when a transaction larger than 1GB... >> >> Then you may need WAL space larger than 1GB as well. For >> replication to work, it seems likely that you may need to

Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?

On 4/11/12, 乔志强 wrote: > >> Yes, increase wal_keep_segments. Even if you set wal_keep_segments to 64, >> the amount of disk space for WAL files is only 1GB, so there is no need to >> worry so much, I think. No? > > But when a transaction larger than 1GB... Then you may need WAL space larger than

Fwd: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?

-- Forwarded message -- From: Michael Nolan Date: Tue, Apr 10, 2012 at 9:47 PM Subject: Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ? To: Fujii Masao On Tue, Apr 10, 2012 at 9:09 PM, Fujii Masao wrote: > On Wed, Apr 11, 2012 at 10:06 AM,

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

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

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] 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 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 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

[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] 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] 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] 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

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: [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] 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: [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-

[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] 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] 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] 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

  1   2   >