Re: [GENERAL] repmgr won't update witness after failover

2015-08-14 Thread Aviel Buskila 
Hey,
yes I did .. and still it wont fail back..

2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen :

> Hi, did you make the old master follow the new one using repmgr?
>
> It doesn't update itself automatically...
> From the looks of it repmgr thinks you have 2 masters - the old one
> offline and the new one online.
>
> Regards,
>  Jony
> Sent from my iPhone
>
> On 13 באוג׳ 2015, at 15:43, Aviel Buskila  wrote:
>
> Hey,
> I have just tried to start the repmgrd on the new standby after I have
> fixed it as a standby and still this goes the same way.
>
> from the message given in the repmgrd log in the witness server it seems
> that he is not able to elect a new master because he can't see anyone .
>
> I have check in the repl_nodes table in the witness and it shows:
> witnessnode3
> master node2
> master node1
>
> is there a way update the witness after the first failover?
>
> 2015-08-13 15:06 GMT+03:00 Jony Cohen :
>
>> Hi Aviel,
>> you can use the 'show cluster' command to see the repmgr state before you
>> do the 2nd failover - make sure the node1 is indeed marked as replica.
>> After a failover the Master doesn't automatically attach to the new
>> master - you need to point him as a slave (standby follow - if possible...)
>> did you start the repmgrd on node1 after making it a replica of the new
>> master? (it needs 2 daemons to decide what to promote)
>>
>> Regards,
>>  - Jony
>>
>
> On Thu, Aug 13, 2015 at 1:29 PM, Aviel Buskila  wrote:
>
>> Hey,
>> I have set up three nodes of postgresql 9.4 with repmgr in this way:
>> 1. master - node1
>> 2. standby - node2
>> 3. witness - node3
>>
>> Now I have set up the replication and the witness as it says here:
>> https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst
>>
>> Now when I do 'kill -9 $(pidof postmaster)' The witness detects that
>> something went wrong and fails over from node1 to node2
>> But when I setup the replication now to work from node2 to node1 and I
>> kill the postgresql process it doesn't failover and the repmgrd log shows
>> the following  message:
>> unable to determine a valid master server; waiting 10 seconds to retry...
>>
>> it seems that the witness doesn't know about the new standby server..
>>
>> Has anyone got any idea about what am I doing wrong here?
>>
>>
>> Best regards,
>> Aviel Buskila
>>
>
>

Re: [GENERAL] Foreign Keys as first class citizens at design time?

2015-08-14 Thread Stephen Feyrer 

Hi,

I agree with each of the points you've made.  The idea here is meant as an  
extension of what is already available.  So yes, this is intended to  
answer the questions of the designer's original model.  The consideration  
being that you design your database and the underlying logic of your  
decision are already built in.  Then when querying that database to  
perform mundane day to day business tasks your query set is simple and  
easy to build.


I don't see why this should detract from the idea of a free form query  
being built using the existing tools to answer a new question which may be  
entirely unrelated to the original models purpose.  This just couldn't  
work independently of the current SQL feature set.


Additionally, this something which has not really been touched on, this  
allows a form of iterative structure in a one line query.



On Fri, 14 Aug 2015 03:52:28 +0100, David G. Johnston  
 wrote:


On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer  
 wrote:
When we design databases, invariably, normally we design the queries at  
the same time.


​Well this may be true to an extent well implemented models have the  
ability to answer questions (queries) the original designer never  
thought of or that >were not important at the time.​


​As for the rest - ​invest in a good graphical query builder (or write  
one if the existing choices are insufficient).


David J.





--
Kind regards


Stephen Feyrer

Re: [GENERAL] Migrations

2015-08-14 Thread Martín Marqués 
El 13/08/15 a las 23:17, Adrian Klaver escribió:
> On 08/13/2015 05:37 PM, Martín Marqués wrote:
>> El 13/08/15 a las 21:23, Guyren Howe escribió:
>>>
>>> I also think migrations ought to be a first-class feature…
>>
>> What do you mean with "migrations ought to be a first-class feature"?
>>
>> There have been, and there still are efforts for making upgrading as
>> smooth and simple as possible, but I'm not really sure where you want to
>> get with this.
> 
> I think Guyren is talking about something like Django or Rails
> migrations, Alembic, Sqitch, etc. A way to do changes to database
> objects either whole or as incremental changes. Basically a schema
> versioning method.

OK, I misunderstood the statement.

In that case, he should take a look at sqitch. Very, very nice IMO.


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] repmgr won't update witness after failover

2015-08-14 Thread Martín Marqués 
El 14/08/15 a las 04:14, Aviel Buskila escribió:
> Hey,
> yes I did .. and still it wont fail back..

Can you send over the output of "repmgr cluster show" before and after
the failover process?

The output of SELECT * FROM repmgr_schema.repl_nodes; after the failover
(you need to change repmgr_schema with what you have configured).

Also, which version of repmgr are you running?

> 2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen :
> 
>> Hi, did you make the old master follow the new one using repmgr?
>>
>> It doesn't update itself automatically...
>> From the looks of it repmgr thinks you have 2 masters - the old one
>> offline and the new one online.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgresql jsonb

2015-08-14 Thread Deepak Balasubramanyam 
Hi,

I have a table (20 million rows) in Postgresql 9.4 that contains a bigint
id as the primary key and another column that contains jsonb data. Queries
run on this table look like so...


## Query

select ... from table
WHERE table.column ->'item'->> 'name' = 'value'


I'd like to make an effort to get Postgresql to keep all data available in
this table and any index on this table in memory. This would ensure that
sequence or index scans made on the data are fairly fast.

Research into this problem indicates that there is no reliable way to get
Postgresql to run off of RAM memory completely (
http://stackoverflow.com/a/24235439/830964). Assuming the table and its
indexes amount to 15 gb of data  on the disk and the machine contains 64GB
of RAM with shared buffers placed at anywhere from 16-24 GB, here are my
questions...

1. When postgresql returns data from this query, how can I tell how much of
the data was cached in memory?

2. I'm aware that I can tweak the shared buffer so that more data is
cached. Is there a way to monitor this value for its effectiveness?

3. Is there a reliable way / calculation (or close to it), to determine a
point after which Postgresql will ask the disk for data Vs the caches?

Thank you for taking the time to read my question.

- Deepak

Re: [GENERAL] Postgresql jsonb

2015-08-14 Thread Bill Moran 
On Fri, 14 Aug 2015 17:39:49 +0530
Deepak Balasubramanyam  wrote:
> 
> I have a table (20 million rows) in Postgresql 9.4 that contains a bigint
> id as the primary key and another column that contains jsonb data. Queries
> run on this table look like so...
> 
> 
> ## Query
> 
> select ... from table
> WHERE table.column ->'item'->> 'name' = 'value'
> 
> 
> I'd like to make an effort to get Postgresql to keep all data available in
> this table and any index on this table in memory. This would ensure that
> sequence or index scans made on the data are fairly fast.
> 
> Research into this problem indicates that there is no reliable way to get
> Postgresql to run off of RAM memory completely (
> http://stackoverflow.com/a/24235439/830964). Assuming the table and its
> indexes amount to 15 gb of data  on the disk and the machine contains 64GB
> of RAM with shared buffers placed at anywhere from 16-24 GB, here are my
> questions...
> 
> 1. When postgresql returns data from this query, how can I tell how much of
> the data was cached in memory?

I'm not aware of any way to do that on a per-query basis.

> 2. I'm aware that I can tweak the shared buffer so that more data is
> cached. Is there a way to monitor this value for its effectiveness?

Install the pg_buffercache extension and read up on what it provides. It
gives a pretty good view into what PostgreSQL is keeping in memory.

> 3. Is there a reliable way / calculation (or close to it), to determine a
> point after which Postgresql will ask the disk for data Vs the caches?

It will ask the disk for data if the data is not in memory. As long as the
data it needs is in memory, it will never talk to the disk unless it needs
to write data back.

The cache is a cache. So there are only 2 reasons your data wouldn't all be
in memory all the time:

1) It doesn't all fit
2) Some of that memory is needed by other tables/indexes/etc

As far as when things get evicted from memory, you'll have to look at the
source code, but it's your typical "keep the most commonly needed data in
memory" algorithms.

What problem are you seeing? What is your performance requirement, and what
is the observed performance? I ask because it's unlikely that you really
need to dig into these details like you are, and most people who ask
questions like this are misguided in some way.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql jsonb

2015-08-14 Thread David Rowley 
On 15 August 2015 at 00:09, Deepak Balasubramanyam 
wrote:

> Hi,
>
> I have a table (20 million rows) in Postgresql 9.4 that contains a bigint
> id as the primary key and another column that contains jsonb data. Queries
> run on this table look like so...
>
> 
> ## Query
> 
> select ... from table
> WHERE table.column ->'item'->> 'name' = 'value'
> 
>
> I'd like to make an effort to get Postgresql to keep all data available in
> this table and any index on this table in memory. This would ensure that
> sequence or index scans made on the data are fairly fast.
>
> Research into this problem indicates that there is no reliable way to get
> Postgresql to run off of RAM memory completely (
> http://stackoverflow.com/a/24235439/830964). Assuming the table and its
> indexes amount to 15 gb of data  on the disk and the machine contains 64GB
> of RAM with shared buffers placed at anywhere from 16-24 GB, here are my
> questions...
>
> 1. When postgresql returns data from this query, how can I tell how much
> of the data was cached in memory?
>
>
It depends which memory you're talking about. If you mean pages that are in
the shared buffers then you can just

EXPLAIN (ANALYZE, BUFFERS) select ... from table;

You'll see Buffers: shared read=N if any buffers were "read from disk" but
keep in mind they still might not be coming from disk, they could be cached
by the operating system in memory.

Regards

David Rowley

--
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] I am unable to install PostgreSql

2015-08-14 Thread Melvin Davidson 
Shashank,

as stated before, you need to install on a _supported_ O/S.

Windows 2003 is NO LONGER SUPPORTED.

So install on Windows 7, Windows 8 or the just released Windows 10.

On Thu, Aug 13, 2015 at 11:42 PM, Shashank  wrote:

> Hi Melvin
>
> Thanks for responding. I am using postgresql-9.4.4-3 version to install.
> However I am getting the same issue with postgresql-9.3.9-3 version as
> well. Kindly suggest, what needs to be done.
>
> Regards
> Shashank
>
>
>
> 
> From: Melvin Davidson 
> Date: 13 August 2015 at 23:09
> Subject: Re: [GENERAL] I am unable to install PostgreSql
> To: Shashank 
> Cc: "pgsql-general@postgresql.org" 
>
>
>
> I hope you realize that support for Windows 2003 ended July 2015
> http://www.microsoft.com/en-us/server-cloud/products/windows-server-2003/
>
> Plus you never mentioned WHICH VERSION of PostgreSQL you are trying to
> install.
>
> Perhaps you should try a supported version of PostgreSQL on a supported
> version of Windows.
> That might work a little better.
>
> On Tue, Aug 11, 2015 at 11:43 PM, Shashank 
> wrote:
>
>> Hi
>>
>> I am unable to install Postgresql on window 2003. I have already
>> installed previously. As, I forgot the password, I have uninstalled and
>> trying to install again.
>> I got error as " The database cluster initialization failed ".
>>
>> The solution to this problem I got as "During the installation, you are
>> asked to choose a location to store the postgres data. The default location
>> is C:\Program Files\Postgres\data. Choosing a location outside of the
>> Program Files directory (e.g. C:\Postgres Data) can often overcome the
>> problem described above. "  I got the solution from
>> http://www.maplesoft.com/support/faqs/detail.aspx?sid=33315
>>
>> After doing the above steps, the setup is stuck at the final stage. It is
>> just showing as "Installing the database cluster (this make take few
>> minutes)".
>>
>>
>> Kindly help me on this. Thanks in advance.
>>
>>
>>
>> Regards
>> Shashank
>> Mumbai, India
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] I am unable to install PostgreSql

2015-08-14 Thread Adrian Klaver 

On 08/14/2015 06:00 AM, Melvin Davidson wrote:

Shashank,

as stated before, you need to install on a _supported_ O/S.

Windows 2003 is NO LONGER SUPPORTED.


Not sure that is relevant.

So:

1) Where are you getting the Postgres installer from?

2) When you uninstalled are you sure it actually uninstalled everything?

3) Are you doing the install as Administrator?

4) Where are you trying to install the Postgres data directory if not in 
the default location?


5) What does the system log when the install is hanging?




So install on Windows 7, Windows 8 or the just released Windows 10.

On Thu, Aug 13, 2015 at 11:42 PM, Shashank mailto:shashank.i...@gmail.com>> wrote:

Hi Melvin

Thanks for responding. I am using postgresql-9.4.4-3 version to
install. However I am getting the same issue with postgresql-9.3.9-3
version as well. Kindly suggest, what needs to be done.

Regards
Shashank




From: *Melvin Davidson* mailto:melvin6...@gmail.com>>
Date: 13 August 2015 at 23:09
Subject: Re: [GENERAL] I am unable to install PostgreSql
To: Shashank mailto:shashank.i...@gmail.com>>
Cc: "pgsql-general@postgresql.org
" mailto:pgsql-general@postgresql.org>>



I hope you realize that support for Windows 2003 ended July 2015
http://www.microsoft.com/en-us/server-cloud/products/windows-server-2003/

Plus you never mentioned WHICH VERSION of PostgreSQL you are trying
to install.

Perhaps you should try a supported version of PostgreSQL on a
supported version of Windows.
That might work a little better.

On Tue, Aug 11, 2015 at 11:43 PM, Shashank mailto:shashank.i...@gmail.com>> wrote:


Hi

I am unable to install Postgresql on window 2003. I have
already installed previously. As, I forgot the password, I
have uninstalled and trying to install again.
I got error as " The database cluster initialization failed ".

The solution to this problem I got as "During the
installation, you are asked to choose a location to store the
postgres data. The default location is C:\Program
Files\Postgres\data. Choosing a location outside of the
Program Files directory (e.g. C:\Postgres Data) can often
overcome the problem described above. "  I got the solution
from http://www.maplesoft.com/support/faqs/detail.aspx?sid=33315

After doing the above steps, the setup is stuck at the final
stage. It is just showing as "Installing the database cluster
(this make take few minutes)".

Kindly help me on this. Thanks in advance.


Regards
Shashank
Mumbai, India




--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Count of non-null values per table column

2015-08-14 Thread David Nelson 
Hello list,Apologies if this has been asked before. My search only 
turned up ways to list the total non-null values for all columns as a single 
number. I want the count for each column by column.I have inherited a 
database consisting of two related huge monolithic tables that lack referential 
integrity between them, or even basic data constraints. One of the problems 
these tables have is every single non-PK column is NULLable. I am trying to 
understand the information that is actually stored and used so that I can 
implement a (hopefully) better design. Towards that end I would like to know 
the count of non-null values in each column per column. In other words I would 
like to get the following output from a table (the numbers are totally made 
up):column_name | 
num_values+---col1   
 |   
5787+---col2   
 | 
17+---col3   
 |    
567+---col4   
 |   
5787+---col5   
 |    
143+---col6   
 |  
1+---...Is this possible through one or 
more of the system views, or will I need to write a function to do this? 
Obviously I can just issue multiple SELECT COUNT(column)... statements, but I'd 
rather not.Thanks,David  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread David Nelson 
Well it is certainly nice to see that my choice to send my question using plain 
text was honored by this email service. Apologies for that mess. The output I 
am looking for is a series of rows with two columns, one the name of the table 
column, and the other the count of non-null values in a table's column of that 
same name, for all column names in the 
table.Thanks> 
From: dlnelson77...@outlook.com> To: 
pgsql-general@postgresql.org> Subject: [GENERAL] Count of non-null 
values per table column> Date: Fri, 14 Aug 2015 14:32:36 
+>> Hello list,

Apologies if this has been 
asked before. My search only turned up ways to list the total non-null values 
for all columns as a single number. I want the count for each column by 
column.

I have inherited a database consisting of two 
related huge monolithic tables that lack referential integrity between them, or 
even basic data constraints. One of the problems these tables have is every 
single non-PK column is NULLable. I am trying to understand the information 
that is actually stored and used so that I can implement a (hopefully) better 
design. Towards that end I would like to know the count of non-null values in 
each column per column. In other words I would like to get the following output 
from a table (the numbers are totally made up):

column_name 
| 
num_values
+---
col1       
 |       
5787
+---
col2       
 
|        
 
17
+---
col3       
 |        
567
+---
col4       
 |       
5787
+---
col5       
 |        
143
+---
col6       
 
|         
 1
+---
...

Is this 
possible through one or more of the system views, or will I need to write a 
function to do this? Obviously I can just issue multiple SELECT 
COUNT(column)... statements, but I'd rather 
not.

Thanks,
David>> --> Sent 
via pgsql-general mailing list (pgsql-general@postgresql.org)> To make 
changes to your subscription:> 
http://www.postgresql.org/mailpref/pgsql-general
   

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread David Rowley 
On 15 August 2015 at 02:32, David Nelson  wrote:

> Hello list,Apologies if this has been asked before. My search only
> turned up ways to list the total non-null values for all columns as a
> single number. I want the count for each column by column.I have
> inherited a database consisting of two related huge monolithic tables that
> lack referential integrity between them, or even basic data constraints.
> One of the problems these tables have is every single non-PK column is
> NULLable. I am trying to understand the information that is actually stored
> and used so that I can implement a (hopefully) better design. Towards that
> end I would like to know the count of non-null values in each column per
> column. In other words I would like to get the following output from a
> table (the numbers are totally made up):column_name |
> num_values+---col1   
> |  
> 5787+---col2   
> |
> 17+---col3   
> |   
> 567+---col4   
> |  
> 5787+---col5   
> |   
> 143+---col6   
> | 
> 1+---...Is this possible through one or
> more of the system views, or will I need to write a function to do this?
> Obviously I can just issue multiple SELECT COUNT(column)... statements, but
> I'd rather not.Thanks,David
>
>
I didn't quite catch a Postgres version number in that mess :)

I assume the tables are quite large if you don't want to just issue a:
SELECT COUNT(a) FILTER(WHERE a IS NULL),COUNT(b) FILTER(WHERE b IS NULL)
... (assuming you're on a version new enough to support agg FILTER)

On the other hand if you were happy with just an approximation then you
could look at pg_stats;

create table abc(a int, b int,c int);
insert into abc values(1, 1, NULL),(1, NULL, NULL),(NULL, NULL, NULL);
analyze abc;

select attname, null_frac from pg_stats where tablename = 'abc';

 attname | null_frac
-+---
 a   |  0.33
 b   |  0.67
 c   | 1

Keep in mind though that this is an *approximation* and possibly could be
inaccurate. If you want exact do the COUNT(col).

If you're not on a version new enough for COUNT(col) FILTER WHERE ..., you
could just SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END)

Regards

David Rowley

--
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread John McKown 
David,

It still came through as junk. But I reconstructed it below

=== original message ===
Apologies if this has been asked before. My search only turned up ways to
list the total non-null values for all columns as a single number. I want
the count for each column by column.

I have inherited a database consisting of two related huge monolithic
tables that lack referential integrity between them, or even basic data
constraints. One of the problems these tables have is every single non-PK
column is NULLable. I am trying to understand the information that is
actually stored and used so that I can implement a (hopefully) better
design. Towards that end I would like to know the count of non-null values
in each column per column. In other words I would like to get the following
output from a table (the numbers are totally made up):

column_name | num_values
+---
col1|   5787
+---
col2| 17
+---
col3|567
+---
col4|   5787
+---
col5|143
+---
col6|  1
+---



​I assume what "num_values" contains is the _distinctly different_ number
of values in "column_name" for each and every column name in a table.​ E.g.
if "col1" contains value 'x' twice and 'y' ten times,then "num_values"
would be 2, not 12. Or do you really want the 12? I'm unsure.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread Tom Lane 
David Rowley  writes:
> On 15 August 2015 at 02:32, David Nelson  wrote:
>> Hello list,Apologies if this has been asked before. My search only
>> turned up ways to list the total non-null values for all columns as a
>> single number. I want the count for each column by column.

> I assume the tables are quite large if you don't want to just issue a:
> SELECT COUNT(a) FILTER(WHERE a IS NULL),COUNT(b) FILTER(WHERE b IS NULL)
> ... (assuming you're on a version new enough to support agg FILTER)

AFAIK this should work in any version, or indeed any SQL-compliant DBMS:

  select count(col1), count(col2), ... from table;

COUNT with an argument counts the non-null values of that argument.

> On the other hand if you were happy with just an approximation then you
> could look at pg_stats;

Yeah; you might want to ANALYZE the table first to be sure the stats are
up to date.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I am unable to install PostgreSql

2015-08-14 Thread Shashank 
Hi Melvin

Thanks for responding. I am using postgresql-9.4.4-3 version to install.
However I am getting the same issue with postgresql-9.3.9-3 version as
well. Kindly suggest, what needs to be done.

Regards
Shashank




From: Melvin Davidson 
Date: 13 August 2015 at 23:09
Subject: Re: [GENERAL] I am unable to install PostgreSql
To: Shashank 
Cc: "pgsql-general@postgresql.org" 


I hope you realize that support for Windows 2003 ended July 2015
http://www.microsoft.com/en-us/server-cloud/products/windows-server-2003/

Plus you never mentioned WHICH VERSION of PostgreSQL you are trying to
install.

Perhaps you should try a supported version of PostgreSQL on a supported
version of Windows.
That might work a little better.

On Tue, Aug 11, 2015 at 11:43 PM, Shashank  wrote:

> Hi
>
> I am unable to install Postgresql on window 2003. I have already installed
> previously. As, I forgot the password, I have uninstalled and trying to
> install again.
> I got error as " The database cluster initialization failed ".
>
> The solution to this problem I got as "During the installation, you are
> asked to choose a location to store the postgres data. The default location
> is C:\Program Files\Postgres\data. Choosing a location outside of the
> Program Files directory (e.g. C:\Postgres Data) can often overcome the
> problem described above. "  I got the solution from
> http://www.maplesoft.com/support/faqs/detail.aspx?sid=33315
>
> After doing the above steps, the setup is stuck at the final stage. It is
> just showing as "Installing the database cluster (this make take few
> minutes)".
>
>
> Kindly help me on this. Thanks in advance.
>
>
>
> Regards
> Shashank
> Mumbai, India
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] PostgreSQL - The Best Overall Database

2015-08-14 Thread Sachin Srivastava 
Congrats to all PostgreSQL DBA’s for this achievement..


HERE ARE THE WINNERS OF THE 2015 DBTA READERS' CHOICE AWARDS FOR BEST DATABASE 
(OVERALL):
Winner:
PostgreSQL

Finalists:
Oracle Database

Microsoft SQL Server


Regards,
Sachin Srivastava
Consultant (Oracle/PostgreSQL) | Technical Support Group | DNO – T & N Tom Tom 
Cyient | www.cyient.com

Mobile: +91 981 114 9139 | 
sachin.srivast...@cyient.com
Direct: +91 120 669 1078 | Board: +91 120 669  2000 - 10



From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson
Sent: 13 August, 2015 6:34 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL - The Best Overall Database


This should put a smile on all PostgreSQL DBA's faces.

The Best Overall 
Database

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you. 
[http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]



DISCLAIMER:

This email may contain confidential information and is intended only for the 
use of the specific individual(s) to which it is addressed. If you are not the 
intended recipient of this email, you are hereby notified that any unauthorized 
use, dissemination or copying of this email or the information contained in it 
or attached to it is strictly prohibited. If you received this message in 
error, please immediately notify the sender at Cyient and delete the original 
message.

Re: [GENERAL] Extension to rewrite queries before execution

2015-08-14 Thread Jeff Janes 
> On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson 
 wrote:


> On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes  wrote:
>
>> I am looking for an extension or a technique that will allow me to
>> intercept a query by the exact query text, and replace that query with a
>> different one.
>>
>> The context is running a third-party app which issues queries I have no
>> control over.  I'd like to intercept a specific query (which has no bind
>> parameters) and either replace the query text with a different text which,
>> for example, swaps out an "in list" clause to instead be an "exists
>> (subquery)".
>>
>> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
>> before and a reset of it after.
>>
>> Is there anything out there like this?  This would be for 9.4.
>>
>> I'm willing to put the query text, and its replacement, directly into the
>> extension source code and compile it, but of course something more flexible
>> would be ideal.
>>
>>

You have not stated which Version or PostgreSQL, nor the O/S involved. That
> being said, depending on what the specific query is, you might consider
> using a Rule or Trigger to handle it. If you use a Trigger ( which is the
> preferred method) you can also embed "set" commands the associated function.
>

Sure I did, 9.4.  OS would be Linux, although I would hope a solution would
be work.   If you mean the built-in-to-core rules or triggers, those
wouldn't work.  Neither one allows you to rewrite a where clause as far as
I can tell.  Rules allows you add one, but not more than that.  And
triggers don't exists for select queries.

Cheers,

Jeff

[GENERAL] stack depth

2015-08-14 Thread Michael H 

Hi All,

I'm tuning up my database and need to increase the max_stack_depth 
parameter in postgresql.conf.


I've edited the /etc/security/limits.conf and added
*   softstack   12288
*   hardstack   12288

but I noticed the comments at the top of the file -
#This file sets the resource limits for the users logged in via PAM.
#It does not affect resource limits of the system services.

After saving my changes and rebooting postgresql will not start, I know 
that the configuration change I have made will only affect users 
authenticated by PAM but I need to amend the stack depth for ALL system 
processes.


Can anybody point me in the right direction?

I'm also asking on the CentOS mailing list but have been pointed to the 
file above which is not the setting I require.


while logged in I can run ulimit -s 12288 but this only affects the 
current shell.


Thanks for your time,

Michael


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Extension to rewrite queries before execution

2015-08-14 Thread Jeff Janes 
On Thu, Aug 13, 2015 at 2:02 PM, Adrian Klaver 
wrote:

> On 08/13/2015 12:49 PM, Jeff Janes wrote:
>
>> I am looking for an extension or a technique that will allow me to
>> intercept a query by the exact query text, and replace that query with a
>> different one.
>>
>
> What is sending the query?
>
> In other words what library is the app using to communicate with the
> Postgres server?


I believe it is some ancient version of Hibernate.

Cheers,

Jeff

Re: [GENERAL] Extension to rewrite queries before execution

2015-08-14 Thread Adrian Klaver 

On 08/14/2015 08:56 AM, Jeff Janes wrote:

On Thu, Aug 13, 2015 at 2:02 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 08/13/2015 12:49 PM, Jeff Janes wrote:

I am looking for an extension or a technique that will allow me to
intercept a query by the exact query text, and replace that
query with a
different one.


What is sending the query?

In other words what library is the app using to communicate with the
Postgres server?


I believe it is some ancient version of Hibernate.
Cheers,


So JDBC.

Don't know how much control you have over the setup, but this looks 
interesting:


https://github.com/ttddyy/datasource-proxy



Jeff



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Extension to rewrite queries before execution

2015-08-14 Thread Melvin Davidson 
Jeff, although it would be tedious, you could write a trigger of the form
BEFORE INSERT ON yatta_yatta
EXECUTE PROCEDURE check_rewrite;

Then in check_rewrite, you could examine the contents of
pg_stat_activity.query column for the current process
and act accordingly.

I acknowledge that it will impact performance, but I see no other way to
accomplish what you wish.

At the very least, you would be able to issue the "set enable_* =off;" or
"set work_mem=*; inside the procedure.

That is the best I can suggest.


On Fri, Aug 14, 2015 at 11:53 AM, Jeff Janes  wrote:

> > On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson 
>  wrote:
>
>
>> On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes  wrote:
>>
>>> I am looking for an extension or a technique that will allow me to
>>> intercept a query by the exact query text, and replace that query with a
>>> different one.
>>>
>>> The context is running a third-party app which issues queries I have no
>>> control over.  I'd like to intercept a specific query (which has no bind
>>> parameters) and either replace the query text with a different text which,
>>> for example, swaps out an "in list" clause to instead be an "exists
>>> (subquery)".
>>>
>>> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
>>> before and a reset of it after.
>>>
>>> Is there anything out there like this?  This would be for 9.4.
>>>
>>> I'm willing to put the query text, and its replacement, directly into
>>> the extension source code and compile it, but of course something more
>>> flexible would be ideal.
>>>
>>>
>
> You have not stated which Version or PostgreSQL, nor the O/S involved.
>> That being said, depending on what the specific query is, you might
>> consider using a Rule or Trigger to handle it. If you use a Trigger ( which
>> is the preferred method) you can also embed "set" commands the associated
>> function.
>>
>
> Sure I did, 9.4.  OS would be Linux, although I would hope a solution
> would be work.   If you mean the built-in-to-core rules or triggers, those
> wouldn't work.  Neither one allows you to rewrite a where clause as far as
> I can tell.  Rules allows you add one, but not more than that.  And
> triggers don't exists for select queries.
>
> Cheers,
>
> Jeff
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] stack depth

2015-08-14 Thread Chris Mair 
> Hi All,
> 
> I'm tuning up my database and need to increase the max_stack_depth
> parameter in postgresql.conf.
> 
> I've edited the /etc/security/limits.conf and added
> *   softstack   12288
> *   hardstack   12288
> 
> but I noticed the comments at the top of the file -
> #This file sets the resource limits for the users logged in via PAM.
> #It does not affect resource limits of the system services.
> 
> After saving my changes and rebooting postgresql will not start, I know
> that the configuration change I have made will only affect users
> authenticated by PAM but I need to amend the stack depth for ALL system
> processes.
> 
> Can anybody point me in the right direction?
> 
> I'm also asking on the CentOS mailing list but have been pointed to the
> file above which is not the setting I require.
> 
> while logged in I can run ulimit -s 12288 but this only affects the
> current shell.

Hi,

on a CentOS 6.7 box I can confirm that adding those lines to
/etc/security/limits.conf DOES increase the limit to 12288
(after a reboot).

I don't see the PAM line, though. What version of CentOS are
you using?

Bye,
Chris.











-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL - The Best Overall Database

2015-08-14 Thread Ravi Krishna 
is there any details available on this poll ?

thanks.

On Thu, Aug 13, 2015 at 11:05 PM, Sachin Srivastava <
sachin.srivast...@cyient.com> wrote:

> Congrats to all PostgreSQL DBA’s for this achievement..
>
>
>
>
> HERE ARE THE WINNERS OF THE 2015 DBTA READERS' CHOICE AWARDS FOR BEST
> DATABASE (OVERALL):
>
> *Winner: *
>
> PostgreSQL 
> 
>
> *Finalists:*
>
> Oracle Database 
>
> Microsoft SQL Server 
>
>
>
>
>
> *Regards,*
>
> *Sachin Srivastava*
> *Consultant (Oracle/PostgreSQL) **| **Technical Support Group **| **DNO –
> T & N Tom Tom* *C**yient** |* *www.cyient.com  *
>
> *Mobile: **+91 981 114 9139** |*
> *sachin.srivast...@cyient.com  **Direct:**
> +91 **120 669 1078 **|* *Board:** +91 120 669  2000 - 10*
>
>
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Melvin Davidson
> *Sent:* 13 August, 2015 6:34 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] PostgreSQL - The Best Overall Database
>
>
>
>
> This should put a smile on all PostgreSQL DBA's faces.
>
>
> The Best Overall Database
> 
>
>
> *Melvin Davidson*
>
> *I reserve the right to fantasize.  Whether or not you wish to share my
> fantasy is entirely up to you. *
>
> --
>
> DISCLAIMER:
>
> This email may contain confidential information and is intended only for
> the use of the specific individual(s) to which it is addressed. If you are
> not the intended recipient of this email, you are hereby notified that any
> unauthorized use, dissemination or copying of this email or the information
> contained in it or attached to it is strictly prohibited. If you received
> this message in error, please immediately notify the sender at Cyient and
> delete the original message.
>

Re: [GENERAL] PostgreSQL - The Best Overall Database

2015-08-14 Thread John R Pierce 

On 8/14/2015 11:06 AM, Ravi Krishna wrote:

is there any details available on this poll ?


the linked article links to this report, which requires registration to 
download.

http://www.dbta.com/DBTA-Downloads/ResearchReports/The-Real-World-of-the-Database-Administrator-5237.aspx



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL - The Best Overall Database

2015-08-14 Thread Melvin Davidson 
Yes.

>From my original post of
CANu8FixZ=-o3nybdma5pxjsyzxqy1n0t4kekg0-1kkvs7fe...@mail.gmail.com


Here is the associated url:

http://www.dbta.com/Editorial/Trends-and-Applications/Best-Database-Overall%C2%AD-105362.aspx

It seems others like to copy me :)

On Fri, Aug 14, 2015 at 2:47 PM, John R Pierce  wrote:

> On 8/14/2015 11:06 AM, Ravi Krishna wrote:
>
>> is there any details available on this poll ?
>>
>
> the linked article links to this report, which requires registration to
> download.
>
> http://www.dbta.com/DBTA-Downloads/ResearchReports/The-Real-World-of-the-Database-Administrator-5237.aspx
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Extension to rewrite queries before execution

2015-08-14 Thread Andy Colson 

On 8/13/2015 2:49 PM, Jeff Janes wrote:

I am looking for an extension or a technique that will allow me to
intercept a query by the exact query text, and replace that query with a
different one.

The context is running a third-party app which issues queries I have no
control over.  I'd like to intercept a specific query (which has no bind
parameters) and either replace the query text with a different text
which, for example, swaps out an "in list" clause to instead be an
"exists (subquery)".

Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
before and a reset of it after.

Is there anything out there like this?  This would be for 9.4.

I'm willing to put the query text, and its replacement, directly into
the extension source code and compile it, but of course something more
flexible would be ideal.

Thanks,

Jeff


Have you looked at pg_bouncer?  You'd have to make changes to the 
source, but it's got a bunch of what you'd need.  It sits in the middle 
and could swap out text pretty easy.  I'd guess.


-Andy




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread David Nelson 
On Fri, Aug 14, 2015 at 9:59 AM, John McKown 
wrote:
>
> David,
>
> It still came through as junk. But I reconstructed it below
>
> === original message ===
> Apologies if this has been asked before. My search only turned up ways to
list the total non-null values for all columns as a single number. I want
the count for each column by column.
>
> I have inherited a database consisting of two related huge monolithic
tables that lack referential integrity between them, or even basic data
constraints. One of the problems these tables have is every single non-PK
column is NULLable. I am trying to understand the information that is
actually stored and used so that I can implement a (hopefully) better
design. Towards that end I would like to know the count of non-null values
in each column per column. In other words I would like to get the following
output from a table (the numbers are totally made up):
>
> column_name | num_values
> +---
> col1|   5787
> +---
> col2| 17
> +---
> col3|567
> +---
> col4|   5787
> +---
> col5|143
> +---
> col6|  1
> +---
>
> 
>
> I assume what "num_values" contains is the _distinctly different_ number
of values in "column_name" for each and every column name in a table. E.g.
if "col1" contains value 'x' twice and 'y' ten times,then "num_values"
would be 2, not 12. Or do you really want the 12? I'm unsure.
>

Thanks John for fixing that which microsoft screwed up, and I've switched
to my gmail account for this.

For my purposes 12 would be fine. I just want to know of the total number of
rows in the table, how many have a value in each column. I guess I'm
actually trying to get the complement of the number of NULLs per column in
the end.

BTW, aplologies for omitting basic info:
SELECT version();
version


 PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit

>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread David Nelson 
On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane  wrote:
>
> David Rowley  writes:
> > On 15 August 2015 at 02:32, David Nelson 
wrote:
> >> Hello list,Apologies if this has been asked before. My search
only
> >> turned up ways to list the total non-null values for all columns as a
> >> single number. I want the count for each column by column.
>
> > I assume the tables are quite large if you don't want to just issue a:
> > SELECT COUNT(a) FILTER(WHERE a IS NULL),COUNT(b) FILTER(WHERE b IS NULL)
> > ... (assuming you're on a version new enough to support agg FILTER)
>
> AFAIK this should work in any version, or indeed any SQL-compliant DBMS:
>
>   select count(col1), count(col2), ... from table;

Thanks Tom (I've switched to my gmail account for this conversation). Tthat
is the way I would do it for a table with a small number of columns, but
these have several dozen so this would get tedious. Although I just
realized I could output the list of column names from the pg_stat view to a
file and whip up some vi find and replace to create the entire statement
pretty quickly. I was just wondering if that was the only way or not.

BTW, aplologies for omitting basic info:
SELECT version();
version


 PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit

>
> COUNT with an argument counts the non-null values of that argument.
>
> > On the other hand if you were happy with just an approximation then you
> > could look at pg_stats;
>
> Yeah; you might want to ANALYZE the table first to be sure the stats are
> up to date.
>
> regards, tom lane
>

Re: [GENERAL] Count of non-null values per table column

2015-08-14 Thread Ken Tanzer 
On Fri, Aug 14, 2015 at 6:35 PM, David Nelson 
wrote:

> On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane  wrote:
> >
> > David Rowley  writes:
>


> Tthat is the way I would do it for a table with a small number of columns,
> but these have several dozen so this would get tedious. Although I just
> realized I could output the list of column names from the pg_stat view to a
> file and whip up some vi find and replace to create the entire statement
> pretty quickly. I was just wondering if that was the only way or not.
>

You could use this to generate the SQL:

\set my_table my_real_table_name

 SELECT 'SELECT COUNT(*) AS total_rows, '||array_to_string(array(SELECT
'COUNT('||column_name::text ||') AS ' || column_name::text FROM
information_schema.columns WHERE table_name=:'my_table'),E',\n') || ' FROM
' || :'my_table' || ';';

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.