Re: [GENERAL] windows vista and windows 7

[Toby Corkindale]

On 07/06/11 09:00, Craig Ringer wrote:

On 06/07/2011 04:18 AM, Heine Ferreira wrote:

Hi

Does Postgres run on the Starter Edition of Windows Vista and Windows 7?


As far as I know PostgreSQL is not explicitly tested on Windows ...
Starter Edition. Whether it will work depends on how crippled those
editions of Windows are.

Why would you want to run PostgreSQL on a crippled OS like that anyway?
What's your use case?


Apparently it's quite common on cheaper Netbooks and on PCs in "emerging 
markets".


Looking at the Windows 7 Starter FAQ, I don't see why it wouldn't work..
You're limited to 2 GB of memory, but that'll be enough to get by on for 
local development and stuff.


-Toby

--
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] windows vista and windows 7

[John R Pierce]

On 06/07/11 12:35 AM, Toby Corkindale wrote:

Looking at the Windows 7 Starter FAQ, I don't see why it wouldn't work..
You're limited to 2 GB of memory, but that'll be enough to get by on 
for local development and stuff. 


the user management is even more crippled than it is in win7 Home, 
making managing a service account like postgres uses problematic.


and yeah, 32bit only and 2gb max supported memory.


--
john r pierceN 37, W 123
santa cruz ca mid-left coast


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

[Vincent Veyron]

Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :

> how do you store recurring events in a database?
> 
> Selecting all events in a week/month should be fast (comming from an index).
> 
> My solution looks like this:
> 
> Table event:
> 
> Columns: id, name, recurring, start_datetime, end_datetime
> 
> recurring is weekly, monthly, yearly or NULL.
> 

Maybe you could try something like what is used in cron, the scheduling
program for GNU/Linux

The crontab file looks like this 

# m h dom mon dow user  command
17 ** * *   rootrun-parts --report /etc/cron.hourly
25 6* * *   rootrun-parts --report /etc/cron.daily )
47 6* * 7   rootrun-parts --report /etc/cron.weekly )
52 61 * *   rootrun-parts --report /etc/cron.monthly )

m is minutes, h hours, dom day of month, mon month, dow day of week.
line 1 executes all scripts in /etc/cron.hourly at 17 minutes after each
hour; line 4 launches all scripts in /etc/cron.weekly every sunday at
06H47

using a similar structure for your table, you could then query for
events occuring weekly, monthly (by finding a value in the proper
column).

You would have to add fields such as first_event_date, last_event_date,
duration...


> end_datetime can be NULL (open end).
> 

Then the event is not recurring, it just keeps going; I would consider
that a special case.


-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


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

[GENERAL] Estimate for 9.1 release

[wstrzalka]

Hi

   Is there any estimate where 9.1 potentially could be released?

-- 
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] windows vista and windows 7

[Craig Ringer]

On 7/06/2011 3:35 PM, Toby Corkindale wrote:

On 07/06/11 09:00, Craig Ringer wrote:

On 06/07/2011 04:18 AM, Heine Ferreira wrote:

Hi

Does Postgres run on the Starter Edition of Windows Vista and Windows 7?


As far as I know PostgreSQL is not explicitly tested on Windows ...
Starter Edition. Whether it will work depends on how crippled those
editions of Windows are.

Why would you want to run PostgreSQL on a crippled OS like that anyway?
What's your use case?


Apparently it's quite common on cheaper Netbooks and on PCs in "emerging
markets".


Sure, it exists and is in use. But why would you want to put PostgreSQL 
on a gutless little netbook?


What I'm angling for from the OP is why they care about running on 
Starter Edition. I suspect the answer will be "because I want to bundle 
PostgreSQL with my application and some of my users will have Starter 
Edition" - at which point alternative database choices that're lighter 
weight, app-embeddable and lower-admin may need to be pointed out.


--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] maximum size limit for a query string?

[AI Rumman]

Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.

Re: [GENERAL] windows vista and windows 7

[Peter Geoghegan]

On 6 June 2011 21:18, Heine Ferreira  wrote:
> Hi
>
> Does Postgres run on the Starter Edition of Windows Vista and Windows 7?

Starter edition can only run 3 programs at a time. I'm not sure how
that's counted, and how it will roll with PG's multi-process
architecture, but my guess would be not well.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and 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] Recurring events

[Thomas Guettler]

On 07.06.2011 09:57, Vincent Veyron wrote:
> Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :
> 
>> how do you store recurring events in a database?
>>
>> Selecting all events in a week/month should be fast (comming from an index).
>>
>> My solution looks like this:
>>
>> Table event:
>>
>> Columns: id, name, recurring, start_datetime, end_datetime
>>
>> recurring is weekly, monthly, yearly or NULL.
>>
> 
> Maybe you could try something like what is used in cron, the scheduling
> program for GNU/Linux

I know cron very well. But I need to get all events on day X between time1 and 
time2 very quickly.
If I build a crontab like table, I need to check all entries before I can
know which crontab lines get executed during this period.

  Thomas


-- 
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

-- 
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] maximum size limit for a query string?

[Vibhor Kumar]

On Jun 7, 2011, at 4:09 PM, AI Rumman wrote:

> Is there any maximum size limit for a query string in Postgresql 9.0.1?
> If yes, what is it ?.


track_activity_query_size parameter.
http://www.postgresql.org/docs/9.0/static/runtime-config-statistics.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhorkumar.wordpress.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] maximum size limit for a query string?

[Pavel Stehule]

Hello

no, it means some different.

we tested a SQL about 20MB with success.

The maximum of varlena is 1GB - so it is necessary to be possible send
a query longer 1GB. But you need a free RAM 3-5x larger then query
size.

Regards

Pavel Stehule


2011/6/7 Vibhor Kumar :
>
> On Jun 7, 2011, at 4:09 PM, AI Rumman wrote:
>
>> Is there any maximum size limit for a query string in Postgresql 9.0.1?
>> If yes, what is it ?.
>
>
> track_activity_query_size parameter.
> http://www.postgresql.org/docs/9.0/static/runtime-config-statistics.html
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor.ku...@enterprisedb.com
> Blog:http://vibhorkumar.wordpress.com
>
>
> --
> 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] How to log query's from servers ?

[Condor]

On Tue, 07 Jun 2011 06:44:02 +0800, Craig Ringer wrote:

On 06/06/2011 09:26 PM, Condor wrote:

Can you explain little more how i can use database-level or 
user-level

SET commands to set log_statement for only one of them ?


http://www.postgresql.org/docs/current/static/sql-set.html

http://www.postgresql.org/docs/current/static/runtime-config-logging.html
http://www.postgresql.org/docs/current/static/sql-alteruser.html
http://www.postgresql.org/docs/current/static/sql-alterdatabase.html

You might, for example:

ALTER USER user1 SET log_statement = 'all';
ALTER USER user2 SET log_statement = 'none';

or do the same with ALTER DATABASE ... SET if you wanted to log on a
per-database level.

Probably, a little filter or patch on postgresql source code file 
which

manage logs to write log file
only if ip is the ip that i want will save me.



Rather than patching PostgreSQL I would recommend configuring
PostgreSQL to log through a smarter syslog daemon like rsyslogd or
syslogd-ng . You should then be able to use regular expression 
filters
in the syslog daemon to discard log messages you are not interested 
in

before they are written to disk.

--
Craig Ringer



Thank you to you and Scott for help.
--
Regards,
Condor

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

[Thomas Guettler]

Hi Craig and mailing list

On 07.06.2011 00:54, Craig Ringer wrote:
> On 06/06/2011 06:59 PM, Thomas Guettler wrote:
>> Hi,
>>
>> how do you store recurring events in a database?
> 
> I use two tables: one table that stores the recurring event, and another
> that's essentially a materialized view containing instances of the event.
> 
> It's not ideal, but performs better than using generate_series to
> produce and filter the event series on the fly.
> 
>> end_datetime can be NULL (open end).
> 
> PostgreSQL has an ideal solution to this: the timestamp value
> 'infinite'. Using it dramatically simplified my interval-related
> queries. I initially used it in my design, only to discover that JDBC
> doesn't support infinite dates (argh!) and neither do many languages. I
> find this exceptionally frustrating.

I use Python (and Django ORM) to access Postgres. Infinite is not supported. 
But this
is no problem.

I look at "Materialized Views" in the wiki:
http://wiki.postgresql.org/wiki/Materialized_Views

The view gets updated by a trigger. But if the date is infinite, you
need to constrain the trigger to the next N years. I guess this is the
best solution. Monthly you need to update the view from an external
event (maybe cron), to create the missing events for N years + one month...

 Thank you for your answer Craig,

  Thomas

-- 
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

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

[GENERAL] perl and php connect problems to pgsql all of a sudden

[Shad Keene]

I'm getting some errors all of a sudden when using PHP and Perl to connect to 
my postgresql database.

I'm running Red Hat Enterprise Linux Client release 5.6 (Tikanga)
And php-5.1.6-27.el5_5.3
perl-5.8.8-32.el5_5.2


Here's the php error when trying to connect:
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib64/php/modules/pdo_pgsql.so' - libpq.so.4: cannot open shared object 
file: No such file or directory in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib64/php/modules/pgsql.so' - libpq.so.4: cannot open shared object file: 
No such file or directory in Unknown on line 0


And the perl error:
install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (@INC contains: 
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi 
/usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl 
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi 
/usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl 
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at 
(eval 3) line 3.
Perhaps the DBD::Pg perl module hasn't been fully installed,
or perhaps the capitalisation of 'Pg' isn't right.
Available drivers: DBM, ExampleP, File, Proxy, Sponge, mysql.
 at pg_connect.pl line 9


This is strange because the same exact scripts worked about a month ago.  And I 
do have DBD/pg.pm installed and pgsql.so and pdo_pgsql.so are also installed.

Is there a simple fix to this?  I have read some ideas about installed 
libpq.so.4 to fix this, but I wanted to run this specific problem by some 
experts before making any changes.

Thanks for any help,

S


  

Re: [GENERAL] perl and php connect problems to pgsql all of a sudden

[Condor]

  

On Tue, 7 Jun 2011 08:21:27 -0700, Shad Keene wrote: 

> I'm
getting some errors all of a sudden when using PHP and Perl to connect
to my postgresql database.
> 
> I'm running Red Hat Enterprise Linux
Client release 5.6 (Tikanga)
> And php-5.1.6-27.el5_5.3
>
perl-5.8.8-32.el5_5.2
> 
> Here's the php error when trying to
connect:
> PHP Warning: PHP Startup: Unable to load dynamic library
'/usr/lib64/php/modules/pdo_pgsql.so' - libpq.so.4: cannot open shared
object file: No such file or directory in Unknown on line 0
> PHP
Warning: PHP Startup: Unable to load dynamic library
'/usr/lib64/php/modules/pgsql.so' - libpq.so.4: cannot open shared
object file: No such file or directory in Unknown on line 0
> 
> And the
perl error:
> install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC
(@INC contains:
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .)
at (eval 3) line 3.
> Perhaps the DBD::Pg perl module hasn't been fully
installed,
> or perhaps the capitalisation of 'Pg' isn't right.
>
Available drivers: DBM, ExampleP, File, Proxy, Sponge, mysql.
> at
pg_connect.pl line 9
> 
> This is strange because the same exact scripts
worked about a month ago. And I do have DBD/pg.pm installed and pgsql.so
and pdo_pgsql.so are also installed.
> 
> Is there a simple fix to this?
I have read some ideas about installed libpq.so.4 to fix this, but I
wanted to run this specific problem by some experts before making any
changes.
> 
> Thanks for any help,
> 
> S

It's seems library libpq.so.4
missing on your server. This can happened if you upgrade your
postgresql. 

For perl use: 

perl -MCPAN -e shell 

and then: 

install
DBD::Pg 

For perl this is happened when you update your perl version or
postgresql library is missing or is not installed. 

--

Regards,
Condor
  

Re: [GENERAL] Non returning Transactions/Many Locks in Postgres 9.0.4 and 9.0.1

[Tarabas]

Hi Scott,

SM> Snip.  Those are ALL either AccessShareLock (which is very low level
SM> and non-blocking) or virtual tx locks, which again don't block
SM> anything but their own transaction.  Nothing there screams "locks!"
SM> for a better view of locks and how they're blocking things you can use
SM> the queries from here: http://wiki.postgresql.org/wiki/Lock_Monitoring

Thank you, I will try that. I am currently using Hibernate 3.2 as a
Database-Layer but with an older JDBC for 8.2, i will try updating
these components to more recent versions.

If the problem arises again i will hopefully be able to better
analyze, where the locking in my application occurs. It is in fact a
multi-threaded environment.

Best regards
Manuel


-- 
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] maximum size limit for a query string?

[andreas]

Zitat von AI Rumman :


Is there any maximum size limit for a query string in Postgresql 9.0.1?
If yes, what is it ?.



Not sure, but maybe 16 MByte, see
http://www.phpbuilder.com/board/archive/index.php/t-10250064.html


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

[GENERAL]

[Юрий EGO]

Hello!

I have a code (libpq):

char textbuffer[120];
res = PQexec(conn, "COPY 
table_name(\"serial_column",\"int_column",\"bytea_column\") FROM STDIN");
if(PQresultStatus(res) == PGRES_COPY_IN)
{
for(int n=0; n < 10; n++) {
sprintf(textbuffer, "1\t%i\t'text'\n", n);
int copydatares = PQputCopyData(conn, textbuffer, strlen(textbuffer));
}
PQputCopyEnd(conn, NULL);
} 

For text data it's ok, but binary data may have '\n' or '\t' characters.
How  correctly write int STDIN binary data?

Help me please! 
Thanks in advance.

Re: [GENERAL]

[Merlin Moncure]

On Tue, Jun 7, 2011 at 7:46 AM, Юрий EGO  wrote:
> Hello!
>
> I have a code (libpq):
>
> char textbuffer[120];
> res = PQexec(conn, "COPY
> table_name(\"serial_column",\"int_column",\"bytea_column\") FROM STDIN");
> if(PQresultStatus(res) == PGRES_COPY_IN)
> {
> for(int n=0; n < 10; n++) {
> sprintf(textbuffer, "1\t%i\t'text'\n", n);
> int copydatares = PQputCopyData(conn, textbuffer, strlen(textbuffer));
> }
> PQputCopyEnd(conn, NULL);
> }
>
> For text data it's ok, but binary data may have '\n' or '\t' characters.
> How  correctly write int STDIN binary data?

the binary format doesn't use delimiters like that.  you have to
basically study the format if you want to craft data that wasn't
produced by the server :(.

merlin

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

[Vincent Veyron]

Le mardi 07 juin 2011 à 13:28 +0200, Thomas Guettler a écrit :
> 
> On 07.06.2011 09:57, Vincent Veyron wrote:
> > Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :
> > 
> >> how do you store recurring events in a database?
> >>
> >> Selecting all events in a week/month should be fast (comming from an 
> >> index).
> >>
> >> My solution looks like this:
> >>
> >> Table event:
> >>
> >> Columns: id, name, recurring, start_datetime, end_datetime
> >>
> >> recurring is weekly, monthly, yearly or NULL.
> >>
> > 
> > Maybe you could try something like what is used in cron, the scheduling
> > program for GNU/Linux
> 
> I know cron very well. But I need to get all events on day X between time1 
> and time2 very quickly.
> If I build a crontab like table, I need to check all entries before I can
> know which crontab lines get executed during this period.
> 

Well, this would require some thoughts and a study of the data, but the
idea is to do :


CREATE TABLE event (
id serial primary key, 
name text, 
dow integer,
dom integer,
h_start time);

insert into event (name, dow, h_start) 
values ('event1', 1, '09:45');

insert into event (name, dow, h_start) 
values ('event2', 2, '09:45');

select * from event where dow=(
SELECT EXTRACT(DOW FROM current_date));


 id |  name  | dow | dom | h_start  
++-+-+--
  2 | event2 |   2 | | 09:45:00

You would have to build the proper indexes. There are many possible
variations for the model, depending on what your data is like, how it is
generated, etc...

Another possibility, which might or might not apply, is to use a
structure inspired from a subscription database :

CREATE TABLE event_2 (
id serial primary key, 
name text, 
last_event_date timestamp,
delay_before_next_occurence interval);

insert into event_2 (name, last_event_date,
delay_before_next_occurence) 
values ('event1', current_date - interval '1 day', '1 day');

insert into event_2 (name, last_event_date,
delay_before_next_occurence) 
values ('event1', current_date - interval '2 days', '1 day');

select * from event_2 where last_event_date +
delay_before_next_occurence=current_date;

 id |  name  |   last_event_date   | delay_before_next_occurence 
++-+-
  1 | event1 | 2011-06-06 00:00:00 | 1 day

A lot depends on the data and its distribution, which requires quite a
bit of study. I find it pays off rather well in application development
time afterwards, though. 



-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


-- 
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] Estimate for 9.1 release

[Andreas Kretschmer]

wstrzalka  wrote:

> Hi
> 
>Is there any estimate where 9.1 potentially could be released?

Sure. When it's ready ;-)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] maximum size limit for a query string?

[Pavel Stehule]

2011/6/7  :
>
> Zitat von AI Rumman :
>
>> Is there any maximum size limit for a query string in Postgresql 9.0.1?
>> If yes, what is it ?.
>>
>
> Not sure, but maybe 16 MByte, see
> http://www.phpbuilder.com/board/archive/index.php/t-10250064.html

isn't it limit for PHP?

Pavel
>
>
> --
> 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] Need suggestion

[Merlin Moncure]

On Fri, Jun 3, 2011 at 5:09 PM, Esmin Gracic  wrote:
> another option is using sqlite for storing images. All data is in single
> file. (or files if you organize it that way) easier backup etc... you have
> some db benefits and retaining solid speed vs file system. Haven't used
> this, but seems as viable option to explore.

My postgres database is stored on a single file as well...on my
workstation it's /dev/sda2.  Using a loopback device I could create a
classic file.  Point being, having a single file doesn't eliminate or
simplify fragmentation and sync issues -- it just moves them from one
place to another.

sqlite has fundamentally different operational characteristics due to
it's architecture. It is unsuited for problems where a multi-user
database is typically the tool of choice for a number of reasons.  For
example, sqlite's locking model is exceptionally crude by comparison,
and intentionally so.  Being able to run inside an applications's
process is a huge asset though.

merlin

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

[GENERAL] replication problems 9.0

[Owen Marinas]

Hi,

I configured replication in 2 AWS instances.
I have shell scripts to dynamically configure the Ips after the
instances are UP.
the Postgres versions are the same in master&  slave, also the
architecture of instances.


Added the lines in the Master
root@ip-172-19-1-37:~# cat  /etc/postgresql/9.0/main/pg_hba.conf |grep trust
host all postgres 172.19.1.101/32 trust

root@ip-172-19-1-37:~# tail -n6  /etc/postgresql/9.0/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /nfsdata/postgres_wal/main_cware/%f'


And in the slave
root@ip-172-19-1-101:~# cat /var/lib/postgresql/9.0/main/recovery.conf
standby_mode = 'on'
trigger_file = '/path_to/trigger'
restore_command = 'cp /nfsdata/postgres_wal/main_cware/%f "%p"'
primary_conninfo = 'host=172.19.1.37 port=5432 user=postgres'

root@ip-172-19-1-101:~# cat /etc/postgresql/9.0/main/postgresql.conf
|grep hot
hot_standby = on



I have in the master log
2011-06-07 17:53:04 UTC FATAL:  no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off
2011-06-07 17:53:09 UTC FATAL:  no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off

and in the slave
cp: cannot stat
`/nfsdata/postgres_wal/main_cware/0001002E': No such
file or directory
2011-06-07 17:53:34 UTC FATAL:  could not connect to the primary server:
FATAL:  no pg_hba.conf entry for replication connection from host
"172.19.1.101", user "postgres", SSL off

__

I'm rsync the files from master
rsync -a --delete --exclude postmaster.pid --exclude pg_xlog
/data/postgresql/main/  /nfsdata/postgres_wal/main_cware/bkp/

and restoring the DB in slave
rsync -a --exclude '.conf' /nfsdata/postgres_wal/main_cware/bkp/
/var/lib/postgresql/9.0/main


replication is not working since the records added to te master car not
present in the slave.
Any help will be appreciated.

regards
Owen


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

[GENERAL] index issues with generate_series.....or why this index os not working

[Rhys A.D. Stewart]

Greetings!!

I'm not sure why the query is not using the gist index in the table
base.parishes. Any suggestions?
__
CREATE TABLE base.parishes
(
  gid serial NOT NULL,
  parish text,
  "COUNT" integer,
  "SUM_AREA" double precision,
  "SUM_ELECTO" double precision,
  the_geom geometry,
  CONSTRAINT parishes_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3448)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE base.parishes OWNER TO postgres;

-- Index: base.gfd7ir67uftyujrth7ji68t678jttyrdgd

-- DROP INDEX base.gfd7ir67uftyujrth7ji68t678jttyrdgd;

CREATE INDEX gfd7ir67uftyujrth7ji68t678jttyrdgd
  ON base.parishes
  USING gist
  (the_geom);
__


The query:
__
with mnme as (
select generate_series(60,84,2) mn, 
generate_series(61,71,2)me
),
points as
(select st_makepoint(mn,me), parish from mnme inner join
base.parishes on
st_intersects(setsrid(st_makepoint(mn,me),3448),the_geom))

select * from points
__

-- 
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] maximum size limit for a query string?

[Andreas Kretschmer]

Pavel Stehule  wrote:

> 2011/6/7  :
> >
> > Zitat von AI Rumman :
> >
> >> Is there any maximum size limit for a query string in Postgresql 9.0.1?
> >> If yes, what is it ?.
> >>
> >
> > Not sure, but maybe 16 MByte, see
> > http://www.phpbuilder.com/board/archive/index.php/t-10250064.html
> 
> isn't it limit for PHP?

Maybe...

Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen  
queries larger than that limit, but i can't find the link, sorry.
(maybe depesz.com, dunno, can't remember, i'm sorry)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

[GENERAL] replication problems

[Owen Marinas]

Hi,

I configured replication in 2 AWS instances.
I have shell scripts to dynamically configure the Ips after the 
instances are UP.
the Postgres versions are the same in master & slave, also the 
architecture of instances.



Added the lines in the Master
root@ip-172-19-1-37:~# cat  /etc/postgresql/9.0/main/pg_hba.conf |grep trust
host all postgres 172.19.1.101/32 trust

root@ip-172-19-1-37:~# tail -n6  /etc/postgresql/9.0/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /nfsdata/postgres_wal/main_cware/%f'


And in the slave
root@ip-172-19-1-101:~# cat /var/lib/postgresql/9.0/main/recovery.conf
standby_mode = 'on'
trigger_file = '/path_to/trigger'
restore_command = 'cp /nfsdata/postgres_wal/main_cware/%f "%p"'
primary_conninfo = 'host=172.19.1.37 port=5432 user=postgres'

root@ip-172-19-1-101:~# cat /etc/postgresql/9.0/main/postgresql.conf 
|grep hot

hot_standby = on



I have in the master log
2011-06-07 17:53:04 UTC FATAL:  no pg_hba.conf entry for replication 
connection from host "172.19.1.101", user "postgres", SSL off
2011-06-07 17:53:09 UTC FATAL:  no pg_hba.conf entry for replication 
connection from host "172.19.1.101", user "postgres", SSL off


and in the slave
cp: cannot stat 
`/nfsdata/postgres_wal/main_cware/0001002E': No such 
file or directory
2011-06-07 17:53:34 UTC FATAL:  could not connect to the primary server: 
FATAL:  no pg_hba.conf entry for replication connection from host 
"172.19.1.101", user "postgres", SSL off


__

I'm rsync the files from master
rsync -a --delete --exclude postmaster.pid --exclude pg_xlog 
/data/postgresql/main/  /nfsdata/postgres_wal/main_cware/bkp/


and restoring the DB in slave
rsync -a --exclude '.conf' /nfsdata/postgres_wal/main_cware/bkp/ 
/var/lib/postgresql/9.0/main



replication is not working since the records added to te master car not 
present in the slave.

Any help will be appreciated.

regards
Owen

--
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] replication problems 9.0

[Rodrigo Gonzalez]

On 06/07/2011 03:05 PM, Owen Marinas wrote:

Added the lines in the Master
root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep trust
host all postgres 172.19.1.101/32 trust



You need in pg_hba.conf

hostreplication repuser 172.19.1.101/32  md5

Database must be replication for the replication user

Regards

Rodrigo Gonzalez

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

[GENERAL] Postgres or Greenplum

[Simon Windsor]

Hi

 

I have been using Postgres for many years and have recently discover
Greenplum, which appears to be a heavily modify Postgres based, multi node
DB that is VERY fast.

 

All the tests that I have seen suggest that Greenplum when implemented on a
single server, like Postgres, but with several  separate installations can
be many time times faster than Postgres. This is achieved by using multiple
DBs to store the data and using multiple logger  and writer processes to
fully use the all the resources of the server.

 

Has the Postgres development team ever considered using this technique to
split the data into separate sequential files that can be accessed by
multiple writers/reader processes? If so, what was the conclusion?

 

Finally,  thanks for all the good work over the years!

 

Simon 

 

Simon Windsor

Eml:   simon.wind...@cornfield.org.uk

Tel: 01454 617689

Mob: 07590 324560

 

"There is nothing in the world that some man cannot make a little worse and
sell a little cheaper, and he who considers price only is that man's lawful
prey."

 

Re: [GENERAL] Postgres or Greenplum

[Tom Lane]

"Simon Windsor"  writes:
> I have been using Postgres for many years and have recently discover
> Greenplum, which appears to be a heavily modify Postgres based, multi node
> DB that is VERY fast.

Very fast on a very narrow set of use cases ...

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] Postgres or Greenplum

[Simon Riggs]

On Tue, Jun 7, 2011 at 10:26 PM, Simon Windsor
 wrote:

> I have been using Postgres for many years and have recently discover
> Greenplum, which appears to be a heavily modify Postgres based, multi node
> DB that is VERY fast.
>
> All the tests that I have seen suggest that Greenplum when implemented on a
> single server, like Postgres, but with several  separate installations can
> be many time times faster than Postgres. This is achieved by using multiple
>  DBs to store the data and using multiple logger  and writer processes to
> fully use the all the resources of the server.
>
> Has the Postgres development team ever considered using this technique to
> split the data into separate sequential files that can be accessed by
> multiple writers/reader processes? If so, what was the conclusion?
>
> Finally,  thanks for all the good work over the years!

Yes, I've looked at implementing parallel query a number of times. My
estimate was that its about 2 man years effort to do something
worthwhile there, and so far nobody has offered funding for such a
task. There was some recent discussion about obtaining funding
recently, so we'll see how that goes. It is of course reasonably
straightforward to achieve trivial parallelism, but that's mostly
useless in the real world. So its on the roadmap, but some way off
yet.

Many commercial implementations exist, and IMHO the Greenplum solution
is the best general purpose DW solution currently available for
PostgreSQL-like environments. Greenplum does have a community edition
that is free to use and your stated performance results match my
experience. We've worked with a number of data warehouse customers
hitting the limits and moving up to Greenplum. Once people give up the
Oracle mantra, it frees them to consider a range of alternatives.

Main reasons for deferring work on parallel query has been that other
techniques have been easier to achieve useful gains with. For example,
partitioning allowed PostgreSQL to dramatically reduce scan times with
less complexity. Synchronous scans can also achieve good efficiencies
for cases where total throughput is important. I expect to do more
work on improving decision support query performance in the next
release (9.2), so if anybody wishes to partially fund development that
would be much appreciated.

-- 
 Simon Riggs   http://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] maximum size limit for a query string?

[Vibhor Kumar]

On Jun 7, 2011, at 5:18 PM, Pavel Stehule wrote:

> Hello
> 
> no, it means some different.
> 
> we tested a SQL about 20MB with success.
> 
> The maximum of varlena is 1GB - so it is necessary to be possible send
> a query longer 1GB. But you need a free RAM 3-5x larger then query
> size.
> 

Thanks. my bad :(. I understood this question wrong.

> Regards
> 
> Pavel Stehule
> 
> 
> 2011/6/7 Vibhor Kumar :
>> 
>> On Jun 7, 2011, at 4:09 PM, AI Rumman wrote:
>> 
>>> Is there any maximum size limit for a query string in Postgresql 9.0.1?
>>> If yes, what is it ?.
>> 
>> 
>> track_activity_query_size parameter.
>> http://www.postgresql.org/docs/9.0/static/runtime-config-statistics.html
>> 
>> Thanks & Regards,
>> Vibhor Kumar
>> EnterpriseDB Corporation
>> The Enterprise PostgreSQL Company
>> vibhor.ku...@enterprisedb.com
>> Blog:http://vibhorkumar.wordpress.com
>> 
>> 
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>> 

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhorkumar.wordpress.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] maximum size limit for a query string?

[Josh Kupershmidt]

On Tue, Jun 7, 2011 at 2:38 PM, Andreas Kretschmer
 wrote:
> Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen
> queries larger than that limit, but i can't find the link, sorry.
> (maybe depesz.com, dunno, can't remember, i'm sorry)

The thread linked seems to mainly be talking about MySQL, which has a
max_allowed_packet limit of something like 16 MB by default, but can
be adjusted upwards[1]. For Postgres, we can have COPY statements sent
over the network, limited only by your disk space, since these are
sent (and parsed, I think) buffer-by-buffer. If you do
  pg_dump --table=big_table | pg_restore
you should see a giant COPY statement constructed; these can obviously
be arbitrarily large.

As for SQL statements that have to be constructed in-memory on the
client, and parsed/planned on the server all-at-once, I don't know of
any limits, other than maybe running out of memory somewhere.

Josh

--
[1] http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

-- 
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] maximum size limit for a query string?

[David Johnston]

> -Original Message-
> Maybe...
> 
> Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen queries
larger
> than that limit, but i can't find the link, sorry.
> (maybe depesz.com, dunno, can't remember, i'm sorry)
> 

What kind of use-case would generate that large of a query?  

Also, are we talking simply about the query as passed to PostgreSQL or the
fully re-written query?  If your query is that long you better already be
using views or it is likely to be impossible to read.  Further, unless you
have way too many columns the query planner and rewriter are more likely to
choke on the query than any kind of simple IO or memory constraint.

Oh, and if you need 16MB because you are using "one table to rule them all"
with 500 self-joins then a database crash is really doing you favor...

I guess I could see a query of the form:

INSERT INTO table (a) VALUES (16mb of content); 

In fact, that is probably more of what is being asked...as opposed to 16MB
select query...anyway...

David J.




-- 
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] replication problems 9.0

[owen marinas]

Thx, Merci, Gracias Rodrigo
it worked indeed, Im wondering why replication is not included in "All"

regards
Owen


On Tue, Jun 7, 2011 at 2:40 PM, Rodrigo Gonzalez
 wrote:
> On 06/07/2011 03:05 PM, Owen Marinas wrote:
>>
>> Added the lines in the Master
>> root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep
>> trust
>> host all postgres 172.19.1.101/32 trust
>>
>
> You need in pg_hba.conf
>
> host    replication     repuser         172.19.1.101/32          md5
>
> Database must be replication for the replication user
>
> Regards
>
> Rodrigo Gonzalez
>

-- 
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] replication problems 9.0

[Alan Hodgson]

On June 7, 2011 04:38:16 PM owen marinas wrote:
> Thx, Merci, Gracias Rodrigo
> it worked indeed, Im wondering why replication is not included in "All"
> 

Probably because it gives access to all the data being written to the 
database.

-- 
Obama has now fired more cruise missiles than all other Nobel Peace prize 
winners combined.

-- 
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] replication problems 9.0

[Rodrigo Gonzalez]

I am not an expert but I will try to explain.

replication user has access to WAL stream, bypassing any revoked 
permission on any database.


I think that is the reason to force a specific user with specific 
permissions for replicationyou don't want user x reading WAL stream 
while you don't grant the user access to all database objects


I hope I am clear in my poor English

Regards

Rodrigo Gonzalez

On 06/07/2011 08:38 PM, owen marinas wrote:

Thx, Merci, Gracias Rodrigo
it worked indeed, Im wondering why replication is not included in "All"

regards
Owen


On Tue, Jun 7, 2011 at 2:40 PM, Rodrigo Gonzalez
  wrote:

On 06/07/2011 03:05 PM, Owen Marinas wrote:


Added the lines in the Master
root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep
trust
host all postgres 172.19.1.101/32 trust



You need in pg_hba.conf

hostreplication repuser 172.19.1.101/32  md5

Database must be replication for the replication user

Regards

Rodrigo Gonzalez






--
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] windows vista and windows 7

[Toby Corkindale]

On 07/06/11 20:45, Peter Geoghegan wrote:

On 6 June 2011 21:18, Heine Ferreira  wrote:

Hi

Does Postgres run on the Starter Edition of Windows Vista and Windows 7?


Starter edition can only run 3 programs at a time. I'm not sure how
that's counted, and how it will roll with PG's multi-process
architecture, but my guess would be not well.


Nah, they got rid of the 3 program limit a while back!

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

[Craig Ringer]

On 8/06/2011 2:04 AM, Owen Marinas wrote:


2011-06-07 17:53:04 UTC FATAL: no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off
2011-06-07 17:53:09 UTC FATAL: no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off


Do you "pg_ctl reload" after modifying pg_hba.conf ?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] Postgres or Greenplum

[Radosław Smogura]

On Tue, 7 Jun 2011 23:04:04 +0100, Simon Riggs wrote:

On Tue, Jun 7, 2011 at 10:26 PM, Simon Windsor
 wrote:


I have been using Postgres for many years and have recently discover
Greenplum, which appears to be a heavily modify Postgres based, 
multi node

DB that is VERY fast.

All the tests that I have seen suggest that Greenplum when 
implemented on a
single server, like Postgres, but with several  separate 
installations can
be many time times faster than Postgres. This is achieved by using 
multiple
 DBs to store the data and using multiple logger  and writer 
processes to

fully use the all the resources of the server.

Has the Postgres development team ever considered using this 
technique to
split the data into separate sequential files that can be accessed 
by

multiple writers/reader processes? If so, what was the conclusion?

Finally,  thanks for all the good work over the years!


Yes, I've looked at implementing parallel query a number of times. My
estimate was that its about 2 man years effort to do something
worthwhile there, and so far nobody has offered funding for such a
task. There was some recent discussion about obtaining funding
recently, so we'll see how that goes. It is of course reasonably
straightforward to achieve trivial parallelism, but that's mostly
useless in the real world. So its on the roadmap, but some way off
yet.

Many commercial implementations exist, and IMHO the Greenplum 
solution

is the best general purpose DW solution currently available for
PostgreSQL-like environments. Greenplum does have a community edition
that is free to use and your stated performance results match my
experience. We've worked with a number of data warehouse customers
hitting the limits and moving up to Greenplum. Once people give up 
the

Oracle mantra, it frees them to consider a range of alternatives.

Main reasons for deferring work on parallel query has been that other
techniques have been easier to achieve useful gains with. For 
example,
partitioning allowed PostgreSQL to dramatically reduce scan times 
with

less complexity. Synchronous scans can also achieve good efficiencies
for cases where total throughput is important. I expect to do more
work on improving decision support query performance in the next
release (9.2), so if anybody wishes to partially fund development 
that

would be much appreciated.

--
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


But, I think GreenPlum is "share nothing", isn't it?

Regards,
Radek

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

[Troy Rasiah]

On 6/06/2011 8:59, Thomas Guettler wrote:

Hi,

how do you store recurring events in a database?

Selecting all events in a week/month should be fast (comming from an index).

My solution looks like this:

Table event:

Columns: id, name, recurring, start_datetime, end_datetime

recurring is weekly, monthly, yearly or NULL.

end_datetime can be NULL (open end).

Can you create an indexed view with infinite rows? I only want to index
the last three year and the next three years.

An other solution would be to fill a table with "serialized" events. The 
recurring
events would be created and inserted into a table. This can only be done in a 
time frame
like above (last three year, next three years). If a recurring event gets 
altered,
all its serialized events need to be updated.

Any feedback?


I have had success using the instructions at

https://github.com/bakineggs/recurring_events_for


--
Troy Rasiah


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