Re: [GENERAL] postgresql server crash on windows 7 when using plpython

[c k]

I am not going to do stack trace right now, because I am using wireless
broadband and is much costlier to download.
But as other functions are working, may be it is related with other thing.
I have forgotten to told that I am using virtual machine for windows. For
even this the normal python interpreter does not have any problems. So it
seems that the problem is occurring due to crash in python package.

Chaitanya Kulkarni

On Thu, Aug 11, 2011 at 5:37 AM, Craig Ringer  wrote:

> On 11/08/2011 1:00 AM, c k wrote:
>
>> I have renamed uuid-ossp.dll to 0uuid-ossp.dll in postgresql's lib
>> directory. But it is also found that postgresql's bin directory is not
>> included in the path.
>>
>
> It doesn't have to be. On Windows, the directory containing the current
> executable is implicitly the first path entry. Since postgres.exe lives in
> the same directory as uuid-ossp.dll, that's the copy of the DLL that'll be
> called.
>
>
>  Then started the postgresql again, called the same plpython function
>> again and again server crashed without any details in the log.
>> Also searched for the above dll or similar in python's installation but
>> not founf, one available was not dll but was .py file.
>>
>
> OK, so maybe Python doesn't use uuid-ossp but its own implementation in
> pure Python. I'll pull out a Windows box and check.
>
>
>  Recent log contains following lines.
>>
>
>  *Fatal Python error: PyThreadState_Get: no current thread*
>>
>
> Hmm, that's interesting. Thanks for supplying the error message; looks like
> I was probably on entirely the wrong track because I was forced to guess
> with not enough information.
>
> If I get a chance I'll have a play with the function you posted and see if
> I can reproduce the crash on my Win7 box.
>
> In the mean time, if you want you can try to collect some more information
> about the crash according to these instructions:
>
> http://wiki.postgresql.org/**wiki/Getting_a_stack_trace_of_**
> a_running_PostgreSQL_backend_**on_Windows
>
> --
> Craig Ringer
>

[GENERAL] Using Postgresql as application server

[c k]

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as
application server? As postgresql supports many languages like pl/perl,
pl/python etc, supports dblink like functions to connect to other postgresql
servers and now features are in development to use external data. Postgresql
works well on many operating systems and has a stable and good quality code.
As many users are using plpython or plperl to work on many types of data and
to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web
application in other servers.
4. data and logic/processing can be separated. One postgresql can be used as
application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

[George MacKerron]

> On 12/08/2011 17:26, George MacKerron wrote:
>> 
>> The point of the function is that you can pass it any table name
>> (along with some other parameters) and it returns rows from that
>> named table.
> 
> OK, fair enough but what exactly are you trying to do that a simple
> SELECT won't do?
> 
> Ray.
> 
> -- 
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie

I was writing myself an easy-to-use generic nearest-neighbour function for 
PostGIS that returns whole rows from the target table, using expanding search 
radii to avoid calculating distances for every geometry in the table. Of 
course, this will hopefully become redundant in the near future with the 
introduction of k-nearest-neighbour GiST indices in PG 9.1.

The function is as follows (I'm no PL/pgSQL guru -- comments welcome):

create or replace function 
  nnrecords(
  nearTo   geometry
, initialDistance  real
, distanceMultiplier   real 
, maxPower integer
, nearThings   text
, nearThingsGeometryField  text
, numWantedinteger)
returns setof record as $$
declare 
  i   integer;
  sql text;
  enough  boolean;
begin
  i := 0;
  while i <= maxPower loop
sql := ' select count(1) >= $5 from ' || quote_ident(nearThings)
|| ' where st_dwithin($1, ' ||  quote_ident(nearThingsGeometryField) || 
', $2 * ($3 ^ $4))';
execute sql into enough using 
nearTo  -- $1
  , initialDistance -- $2
  , distanceMultiplier  -- $3
  , i   -- $4
  , numWanted;  -- $5
if enough or i = maxPower then
  sql := ' select * from ' || quote_ident(nearThings)
  || ' where st_expand($1, $2 * ($3 ^ $4)) && ' || 
quote_ident(nearThingsGeometryField)
  || ' order by st_distance($1, ' ||  
quote_ident(nearThingsGeometryField) || ')'
  || ' limit $5';
  return query execute sql using 
  nearTo  -- $1
, initialDistance -- $2
, distanceMultiplier  -- $3
, i   -- $4
, numWanted;  -- $5
  return;
end if;
i := i + 1;
  end loop;
end
$$ language 'plpgsql' stable;


Please access the attached hyperlink for an important electronic communications 
disclaimer: http://lse.ac.uk/emailDisclaimer

-- 
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] Functions returning setof record -- can I use a table type as my return type hint?

[George MacKerron]

On 12 Aug 2011, at 19:17, Merlin Moncure wrote:

>>> you can't have it both ways. at the time the function call is
>>> executed, the return type/fields must be known.  you can do this by
>>> either a. explicitly defining the function return type or b.
>>> describing the function return type in the function call, or c. use a
>>> generic type to hold the output record structure which can be
>>> parsed/dealt with later, like text or hstore.
>> 
>> I'm trying to do your option (a) -- defining the function return type.
>> 
>> But I want to do this by referring to an existing table type -- which I know 
>> the returned fields must match -- rather than laboriously retype the field 
>> definition list for that table.
>> 
>> The problem is that I can't make the database accept the table type as a 
>> field definition list, when that seems like a perfectly sensible (and in 
>> this case much more convenient) way to define the fields that will be 
>> returned.
> 
> yes -- you should be able to do this but you can't since there is no
> way to transform the return type from record to type t outside the
> function call.  

OK, I think that answers my question. Slightly disappointing. Presumably it 
wouldn't be too hard to implement (?), but perhaps it's not needed often enough 
to be worth it.

> your best bet is to apply method c. above:
> 
> postgres=# create type t as (a int, b int, c int);
> CREATE TYPE
> 
> postgres=# create or replace function r() returns setof text as $$
> begin
>  return query select row(1,2,3)::t::text;
> end; $$ language plpgsql;
> CREATE FUNCTION
> 
> postgres=# select r();
>r
> -
> (1,2,3)
> (1 row)
> 
> Time: 18.000 ms
> postgres=# select r()::t;
>r
> -
> (1,2,3)
> (1 row)
> 
> Time: 0.000 ms
> postgres=# select (r()::t).*;
> a | b | c
> ---+---+---
> 1 | 2 | 3
> (1 row)
> 
> as a bonus you can call the function more flexibly since it returns a scalar.
> 
> merlin

Thanks -- this looks like a decent workaround.


Please access the attached hyperlink for an important electronic communications 
disclaimer: http://lse.ac.uk/emailDisclaimer

-- 
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] Using Postgresql as application server

[David Johnston]

On Aug 13, 2011, at 11:57, c k  wrote:

> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used as 
> application server? As postgresql supports many languages like pl/perl, 
> pl/python etc, supports dblink like functions to connect to other postgresql 
> servers and now features are in development to use external data. Postgresql 
> works well on many operating systems and has a stable and good quality code. 
> As many users are using plpython or plperl to work on many types of data and 
> to implement logic that can be useful in web application management. 
> So i am thinking if I can use postgresql as web application server. 
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.
> 2. can connect to other postgresql servers or databases
> 3. it is easy to manage stored procs or triggers than managing web 
> application in other servers.
> 4. data and logic/processing can be separated. One postgresql can be used as 
> application server and another as database. 
> 5. stable, mature and open codebase.
> 
> I request to users and developers give your suggestions and opinions.
> Waiting for your replies.
> 
> Thanks and regards,
> 
> Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web 
browser and outputs in HTML using only PostgreSQL.  IF you can actually do that 
simple task you will then be in a better position to decide if such an 
architecture is worth expanding upon.

The better question to ask is why wouldn't you want to use something like 
Tomcat or Apache+Programming Language?

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] Functions returning setof record -- can I use a table type as my return type hint?

[Pavel Stehule]

Hello

2011/8/13 George MacKerron :
>> On 12/08/2011 17:26, George MacKerron wrote:
>>>
>>> The point of the function is that you can pass it any table name
>>> (along with some other parameters) and it returns rows from that
>>> named table.
>>
>> OK, fair enough but what exactly are you trying to do that a simple
>> SELECT won't do?
>>
>> Ray.
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> r...@iol.ie
>
> I was writing myself an easy-to-use generic nearest-neighbour function for 
> PostGIS that returns whole rows from the target table, using expanding search 
> radii to avoid calculating distances for every geometry in the table. Of 
> course, this will hopefully become redundant in the near future with the 
> introduction of k-nearest-neighbour GiST indices in PG 9.1.
>
> The function is as follows (I'm no PL/pgSQL guru -- comments welcome):
>
> create or replace function
>  nnrecords(
>      nearTo                   geometry
>    , initialDistance          real
>    , distanceMultiplier       real
>    , maxPower                 integer
>    , nearThings               text
>    , nearThingsGeometryField  text
>    , numWanted                integer)
> returns setof record as $$
> declare
>  i       integer;
>  sql     text;
>  enough  boolean;
> begin
>  i := 0;
>  while i <= maxPower loop
>    sql := ' select count(1) >= $5 from ' || quote_ident(nearThings)
>        || ' where st_dwithin($1, ' ||  quote_ident(nearThingsGeometryField) 
> || ', $2 * ($3 ^ $4))';
>    execute sql into enough using
>        nearTo              -- $1
>      , initialDistance     -- $2
>      , distanceMultiplier  -- $3
>      , i                   -- $4
>      , numWanted;          -- $5
>    if enough or i = maxPower then
>      sql := ' select * from ' || quote_ident(nearThings)
>          || ' where st_expand($1, $2 * ($3 ^ $4)) && ' || 
> quote_ident(nearThingsGeometryField)
>          || ' order by st_distance($1, ' ||  
> quote_ident(nearThingsGeometryField) || ')'
>          || ' limit $5';
>      return query execute sql using
>          nearTo              -- $1
>        , initialDistance     -- $2
>        , distanceMultiplier  -- $3
>        , i                   -- $4
>        , numWanted;          -- $5
>      return;
>    end if;
>    i := i + 1;
>  end loop;
> end
> $$ language 'plpgsql' stable;
>

* move non necessary rows from cycle.
* use a statement FOR instead WHILE
* flag STABLE is wrong, your function is VOLATILE

Regards

Pavel Stehule



>
> Please access the attached hyperlink for an important electronic 
> communications disclaimer: http://lse.ac.uk/emailDisclaimer
>
> --
> 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

[GENERAL] Deleting Multiple Rows Based on Multiple Columns

[Rich Shepard]

  Thanks to David J. I have a working script to locate multiple rows having
the same values in three candidate columns. I used an enhanced version of
this script to copy those duplicate (and triplicate) records to a clone of
the original table.

  Now I would like to delete those duplicates from the original table in
either of two ways, but my Google searches have not produced hits where the
selection criteria for the DELETE has multiple columns.

  If it is possible to leave one row with specific values in the columns
(loc_name, sample_date, param) and delete the additional ones, I'd like to
learn how to do so. I know that I'll have use for these techniques with
future data.

  Else, I'd like to delete all those rows with multiple copies. Then I'll
manually remove the extra rows in emacs, and insert the remainder in the
original table.

  The script I tried to to the latter is:

DELETE FROM chemistry
  WHERE
  (SELECT lab_nbr, loc_name, sample_date, param, quant, units, qa_qc,
   easting, northing, remark
   FROM chemistry
   Natural Inner join (
   SELECT loc_name, sample_date, param, Count(*) as duplicate_count
 FROM chemistry
 GROUP BY loc_name, sample_date, param) grouped
   WHERE duplicate_count > 1);

  But postgres wants a single column in the first SELECT.

TIA,

Rich

--
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] [ADMIN] Using Postgresql as application server

[c k]

Yes, I know that I can not create a simple web application using only
postgresql because we need a web server to server the html content. I not
going to add web server functionalities to postgresql, but just want to use
existing  features. I would like to generate content dynamically. I want
minimum developers to be required, simple and powerful security and
administration, and most importantly ability to respond to changes.
As s...@bestmx.ru replied above, I want to go through this way at least to
test the application. What I am going to do is
use python for a simple test web server.
Use xml to define static content for each page.
Use posgtresql to generate dynamic content using stored procs.
static content of the page and dynamic content both will make final web
page.
Use javascript for client UI.

For my application the most important part is generating dynamic content.
Scaling, concurrency etc are not the issues at this time. We have to adopt
govt. rules and change the business logic accordingly and it must be fast
enough to save penalties.

Regards,

Chaitanya Kulkarni

On Sat, Aug 13, 2011 at 10:54 PM, s...@bestmx.ru  wrote:

> c k wrote:
>
>  from last few months I am reading and searching for can postgresql
>> used as application server?
>> So i am thinking if I can use postgresql as web application server.
>>
>> I request to users and developers give your suggestions and opinions.
>> Waiting for your replies.
>>
>
> Yes! Yes And Yes, my friend!
> You should!
>
> i am using postgresql exactly as application server.
> (stored procedures in PLpgsql)
> at http://platzcart.com i managed to minimize server side scripting:
> Stored procedures run entire "business logic" + PHP envelop results into
> HTML code.
>
> and in the next project (yet under construction)
> i managed to ELIMINATE server side scripting at all
> (except captcha server, which is standalone (written in perl))
> PLpgsql "business logic" again + client side JS is building presentation.
>
> most important point to support you is
> *This is the originally intended usage of a DBMS*
>
> All the troubles, all the NoSQL whining caused by unnecessary middleware.
>
> P.S.
> i have held a report to NLUUG Spring Conference 2011
> about platzcart.com implementation
> it is located at 
> http://platzcart.com/lib/**platzcart.pdf
> it clearly describes advantages of using postgresql as an application
> server.
> and i am sure it explains the good practice (maybe the best one)
>
> P.P.S.
> now i am using nginx_http_postgres_module
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin
>

Re: [GENERAL] [ADMIN] Using Postgresql as application server

[s...@bestmx.ru]

c k wrote:
Yes, I know that I can not create a simple web application using only 
postgresql because we need a web server to server the html content.

u r wrong.
u CAN!

there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.

--
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] [ADMIN] Using Postgresql as application server

[c k]

can u please give me it's link.
I found ngx_postgres module.

Chaitanya Kulkarni

On Sun, Aug 14, 2011 at 12:14 AM, s...@bestmx.ru  wrote:

> c k wrote:
>
>> Yes, I know that I can not create a simple web application using only
>> postgresql because we need a web server to server the html content.
>>
> u r wrong.
> u CAN!
>
> there is nginx_htttp_postgresql_module
> exactly to connect webserver directly to postgresql
> and to OUTPUT query result to a browser.
>

Re: [GENERAL] [ADMIN] Using Postgresql as application server

[s...@bestmx.ru]

can u please give me it's link.
I found ngx_postgres module.



there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.


http://wiki.nginx.org/3rdPartyModules
http://labs.frickle.com/nginx_ngx_postgres/

--
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] [ADMIN] Using Postgresql as application server

[Rich Shepard]

On Sun, 14 Aug 2011, c k wrote:


I would like to generate content dynamically. I want minimum developers to
be required, simple and powerful security and administration, and most
importantly ability to respond to changes.

For my application the most important part is generating dynamic content.


Chaitanya,

  There are three major components of a database application: the dbms back
end (postgres in this case), middleware (in the language of your choice),
and the UI. The UI will use apache to display pages and accept user input.
The middleware contains business logic and is the interface between the user
and the back end.

  Each (scripting) language has application development frameworks. My
partner (a highly experienced software engineer) is using Ruby on Rails for
one of our projects. It is powerful but has a long learning curve and you
need to do everything the Rails way. In python (my scripting language of
choice) there's django, turbogears, pylon, and probably many more.

  Depending on the approach you select you may be able to meet all your
desired attributes as above. What you might consider doing is list those
requirements in order of importance and focus on meeting the most important
ones. This may mean droping other preferences (e.g., minumum number of
developers unless you have a long lead time for this project).

Rich

--
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 server crash on windows 7 when using plpython

[Adrian Klaver]

On Saturday, August 13, 2011 8:35:08 am c k wrote:
> I am not going to do stack trace right now, because I am using wireless
> broadband and is much costlier to download.
> But as other functions are working, may be it is related with other thing.
> I have forgotten to told that I am using virtual machine for windows. For
> even this the normal python interpreter does not have any problems. So it
> seems that the problem is occurring due to crash in python package.

My suspicion is it has to do with this from your original post:

"First I installed python 2.7 and then added python installation
path to windows PATH variable. ..." 

" Then I go to create plpythonu in a database. But it failed.
After searching, I found that it needs python 2.6. So I again 
installed python 2.6 and then dropped plpython from database 
and created again."

Unmentioned is whether you uninstalled Python 2.7 or removed the PATH reference 
to Python 2.7

> 
> Chaitanya Kulkarni
> 


-- 
Adrian Klaver
adrian.kla...@gmail.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] Deleting Multiple Rows Based on Multiple Columns

[David Johnston]

>  If it is possible to leave one row with specific values in the columns
> (loc_name, sample_date, param) and delete the additional ones, I'd like to
> learn how to do so. I know that I'll have use for these techniques with
> future data.
> 
>  Else, I'd like to delete all those rows with multiple copies. Then I'll
> manually remove the extra rows in emacs, and insert the remainder in the
> original table.

It will be easiest to delete all and add back the single desired record later.

The general form will be:

DELETE FROM chemistry c WHERE EXISTS (SELECT 1 FROM chem_too ct WHERE c.field 
=ct.field AND c.field2=ct.field2 etc...)

Not tested so minor syntax tweaks by be needed.  Pretty sure docs cover this 
use case.  You can also do:

DELETE FROM chemistry
USING chem_too
WHERE chemistry.fields =AND chem_too.fields;

The other way to group multiple columns in a single "row" column.

E.g.,  WHERE (field1, field2, field3) =/IN (SELECT field1, field2, field3 FROM 
...)

Note the parenthesis grouping the three columns into a single unit for 
comparison.  It is useful shorthand for the ( field1=field1 AND field2=field2 
etc... ) construct.  You can readily use this form in the FROM/USING form's 
WHERE clause.

In your chem_too table you can use the ROW_NUMBER window function over the 
desired key columns to number the duplicate rows (in a sub-query) and then, in 
the outer query, remove any rows that have a ROW_NUMBER > 1.  Try to write the 
query yourself and post your best effort if you get stumped.  Using this query 
on the chem_too table you can select a single record per key to insert back 
into the main table.  You can also use ORDER BY in the WINDOW definition to 
sort on secondary (non-partition by) fields if desired.

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] Using Postgresql as application server

[Andreas Joseph Krogh]

On 08/13/2011 05:57 PM, c k wrote:
> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used
as application server? As postgresql supports many languages like
pl/perl, pl/python etc, supports dblink like functions to connect to
other postgresql servers and now features are in development to use
external data. Postgresql works well on many operating systems and has a
stable and good quality code. As many users are using plpython or plperl
to work on many types of data and to implement logic that can be useful
in web application management.
> So i am thinking if I can use postgresql as web application server.
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.
> 2. can connect to other postgresql servers or databases
> 3. it is easy to manage stored procs or triggers than managing web
application in other servers.
> 4. data and logic/processing can be separated. One postgresql can be
used as application server and another as database.
> 5. stable, mature and open codebase.
>
> I request to users and developers give your suggestions and opinions.
> Waiting for your replies.
>
> Thanks and regards,
>
> Chaitanya Kulkarni

No, PG has never, and will never, act as an application-server. Are you
really sure you need a full-blown application-server?

If you're familiar with Scala (a language which runs on the JVM) I
really recommend Lift (www.liftweb.net) with PostgreSQL. Definitely the
best. Although Lift has a history of having a steep learning-curve I
always favour maintainability, type-safety and robustness over RAD and
time-to-market. Having said that I honestly think a skilled Lift team is
at least 2x more productive than with any other web-based framework for
building modern UI.

I'm about to post a Lift+JPA+Spring+PostgreSQL example soon which
demonstrates how to use Lift with Spring+JPA+XA(2 phase commit, aka.
distributed transactions). Follow the mailing-list and you'll see it
there soon (I hope): http://groups.google.com/group/liftweb

-- 
Andreas Joseph Krogh 
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
Org.nr: NO 981 479 076  | |
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

Re: [GENERAL] PD_ALL_VISIBLE flag warnings

[MirrorX]

thx a lot for the answers

we will upgrade to 8.4.8 and i will monitor the situation :)

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PD-ALL-VISIBLE-flag-warnings-tp4692473p4697106.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] backup-strategies for large databases

[MirrorX]

hello to all

i am trying to find an "acceptable" solution for a backup strategy on one of
our servers (will be 8.4.8 soon, now its 8.4.7). i am familiar with both
logical (dump/restore) and physical backups (pg_start_backup, walarchives
etc) and have tried both in some other cases. 

the issue here is that the server is heavily loaded. the daily traffic is
heavy, which means the db size is increasing every day (by 30 gb on average)
and the size is already pretty large (~2TB). so the mentioned above
techniques are not very suitable here. the pg_dump beside the fact that it
would take about a month to be completed is not acceptable also b/c of the
the fact that there will be no PITR then. 

at the moment, the copy of the PGDATA folder (excluding pg_xlog folder), the
compression of it and the storing of it in a local storage disk takes about
60 hours while the file size is about 550 GB. the archives are kept in a
different location so that not a problem. so, i dont want even to imagine
how much time the uncompress and copy will take in 'disaster' scenario. 

soon, another server will be added there, a fail-over one. but again, with
this technique, to send there the PGDATA and the wals doesnt seem very
efficient.

plus, we cannot keep the PGDATA in an older version and just replicate the
wals b/c due to the heavy load they are about 150GB/day. so, even though
that we can suppose that we have unlimited disk storage its not reasonable
to use 5 TB for the wals (if the PGDATA is sent once a month) and
furthermore a lot of time will be needed for the 2nd server to recover since
it will have to process all this huge amount of wals.

so, in conclusion, given the fact that the size of the db is large, a very
big amount of storage is at our disposal and a new server will be up soon,
what options are there to reduce to minimum the down-time in a disaster
scenario? any (file-system) solutions that keep the disks at sync like DRDB
are suitable?so that the disk of the 2nd server would be at sync with the
1st. even if that works, i would still like to have a 3rd backup in the
storage disks so my question remains.

thx in advance for any suggestions and sorry for my long post...




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/backup-strategies-for-large-databases-tp4697145p4697145.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Using Postgresql as application server

[Chris Travers]

Hi CK:

First, it depends on what you mean by an application server.  There
are people who in fact do very similar things with PostgreSQL,
essentially having it take on roles traditionally served by
middleware.

On Sat, Aug 13, 2011 at 8:57 AM, c k  wrote:
> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used as
> application server?

Kinda.

> As postgresql supports many languages like pl/perl,
> pl/python etc, supports dblink like functions to connect to other postgresql
> servers and now features are in development to use external data. Postgresql
> works well on many operating systems and has a stable and good quality code.
> As many users are using plpython or plperl to work on many types of data and
> to implement logic that can be useful in web application management.
> So i am thinking if I can use postgresql as web application server.
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.

> 2. can connect to other postgresql servers or databases

Best used sparingly.

> 3. it is easy to manage stored procs or triggers than managing web
> application in other servers.

I agree, but

> 4. data and logic/processing can be separated. One postgresql can be used as
> application server and another as database.

Bad idea.  You don't really gain anything except complexity and
headache by trying to separate like this.

> 5. stable, mature and open codebase.

Probably better than give some general feedback I will share how we do
this with LedgerSMB.  Some of our deployments using this approach are
decent-sized.

1:  Thus far all our stored procs are all in SQL and PLPGSQL.  We do
not currently use PL/Perl or PL/Python, or any other stored procedure
language.

2:  We pay close attention to semantics in order to ensure, to the
extent possible, that catalog data allows for discovery of stored
procedure interfaces.  We then do a lot of query building in the
"client" app (a web app) to discover these interfaces and call them
properly.  We do not include SQL code in most perl modules.  Instead
virtually all calls go through a generic discovery interface.

3:  All logic required to store, retrieve, or present (to the
application) the data goes through these stored procedures.

4:  The web app is a fairly thin Perl glue that binds HTML templates
written in Template Toolkit to these stored procedures.  It's rare to
find Perl functions more than about 5-10 lines long and when that
happens most of the logic is usually taking data and putting it into a
tabular structure for a report template.

This could be done with a desktop app as well.  The primary business
logic and security is thus reusable between applications, making
PostgreSQL essentially a middleware server.

Observations from my experience:  Design is critical and difficult.
There aren't a lot of people doing this sort of thing and so there is
a LOT of trial and error.  Also, it is quite possible to do a heck of
a lot in this area in SQL and PLPGSQL.  Focus on writing unified,
maintainable queries and semantically meaningful interfaces (argument
names, function names, etc).

Best Wishes,
Chris Travers

-- 
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] Using Postgresql as application server

[Greg Williamson]

>
>Dear Postgres users,



How about sending these to just one mailing list -- when you cross post 
everybody gets two copies of each response.

-- 
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] backup-strategies for large databases

[Scott Marlowe]

One possible answer to your issues is data partitioning.  By
partitioning your data by date or primary key or some other field, you
can backup individual partitions for incremental backups. I run a
stats database that partitions by day daily and we can just backup
yesterday's partition each night.  Each partition is in the 10 to 20
meg size range.

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