Re: [GENERAL] Full text search on a complex schema - a classic problem?

[Ivan Voras]

On 05/23/10 18:03, Andy Colson wrote:
> On 05/22/2010 09:40 PM, Ivan Voras wrote:
>> Hello,
>>
>> I have a schema which tracks various pieces of information which would
>> need to be globally searchable. One approach I came up with to make all
>> of the data searchable is to create a view made of UNION ALL queries
>> that would integrate different tables into a common structure which
>> could be uniformly queried by using tsearch2 functions. This would work,
>> up to the point where it would be practically unavoidable (for
>> performance reasons) to create indexes on this view, which cannot be
>> done. I would like to avoid using a "hand-made" materialized view (via
>> triggers, etc.) because of administrative overhead and because it would
>> duplicate data, of which there is potentially a lot.
>>
>> I think this looks like a fairly common problem with full text searches
>> on a large-ish schemas, so I'm wondering what are the best practices
>> here, specifically with using tsearch2?
>>
> 
> I have something like this, but with PostGIS layers.  When a person
> clicks I search all the different layers (each a table) for
> information.  I use a stored proc.  Each table has its own index so each
> table is fast.  It also lets me abstract out differences between the
> layers (I can search each a little differently).
> 
> If each of your tables had its own full text fields and indexes, then
> write a stored proc to search them all individually, it should be pretty
> quick.

This looks like an interesting solution. And it could be done
generically in our case by having a separate table describing which
tables need to be searched and by what fields.


-- 
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] Full text search on a complex schema - a classic problem?

[Ivan Voras]

On 05/23/10 07:17, Craig Ringer wrote:
> On 23/05/10 10:40, Ivan Voras wrote:
>> Hello,
>>
>> I have a schema which tracks various pieces of information which would
>> need to be globally searchable.
> 
> If systems that exist outside the database its self are acceptable,
> check out Apache Lucerne, and tools that use it like Hibernate Search.

We are currently using such an external system and while it works fast
enough it has two problems:

1) the need to periodically refresh it (via cron)
2) it complicates deployment a bit by adding dependencies

so we're moving away from it.


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

[GENERAL] Apache authorization using postgres

[John Gage]

Is this the latest on this subject?



Re: Apache2 PostgreSQL http authentication
• From: "Jeffrey Brower" 
• To: 
• Subject: Re: Apache2 PostgreSQL http authentication
• Date: Sun, 7 Oct 2007 20:29:27 -0400

First you need to make sure that the blastwave package for apache  
development is on your machine.


Use the blastwave command: pkg-get -i apache2_devel This gives you the  
headers you are missing from the apache binary install (as well as  
loading the libtool etc that the apxs command will use.


Now go to http://www.giuseppetanzilli.it/mod_auth_pgsql2/ and download  
the source. I put it in /Documents/mod_auth_pgsql2/mod_auth_pgsql-2.0.3


[snip]



Thanks,

John Gage
--
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] returning ref cursor

[Merlin Moncure]

On Mon, May 24, 2010 at 1:44 AM, Ravi Katkar  wrote:
> Please let me know what’s wrong with below code
> create or replace FUNCTION test_call()
>
> RETURNS VOID
>
>    AS $procedure$
>
>    DECLARE
>
>    c_cursor  REFCURSOR;

give your cursor a name:
c_cursor  REFCURSOR default 'a_name';

this name is also how you will use the refcursor from non-plpgsql
fetches later in the transaction if you needed to (in this case you
would likely return the cursor name from the function).

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] can't install postgres 8.4 on windows 2003 server

[ritas]

Hi,

When I try to install postgres 8.4 on windows server 2003, I get the
following error: "A non-fatal error occurred whilst loading database
modules. Please check the installation logs in C:/docs&setttings/user/local
settings/temp for details". The log file doesn't have any error, but it
looks like it stopped in the middle. Also, it doesn't install the postgres
service. I have attached the log file. Any help would be greatly
appreciated. Thanks.

http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log
bitrock_installer_1004serverlog.log 
-- 
View this message in context: 
http://old.nabble.com/can%27t-install-postgres-8.4-on-windows-2003-server-tp28657511p28657511.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] cleaning wal files from postgres

[erobles]

oops, sorry, my question was very ambiguous ...
What I want to know is:
The correct procedure to remove the wal files that have been used  in 
the recovery of a postgres server.



Consider the following;

1.-  A main server in production.
2.- A  secondary server that is normally off.
3.-  By connecting the secondary server, the primary server must copy 
the  directory data as well as wal files generated during the copying 
process.


4.- start up  the postgres on the secondary server.

5o.Delete wal  files no longer will be used by postgres to save disk space.

 how can i know which  wal file is not required by postgres and
delete   wal files from  main and secondary server, to save space disk 
on the servers ? (after a succesfully start up on secondary server)



regards eddie.

On 05/21/2010 03:43 PM, Andreas Schmitz wrote:


maybe I don't understand the problem. there is no need to clean WAL 
files after recovery. where exactly is the problem ?


regards

andreas


erobles wrote:


 PITR  recovery

On 05/21/2010 03:04 PM, Andreas Schmitz wrote:

erobles wrote:

which is the right procedure to clean wal files after a  recovery ??

what kind of recovery ?

regards

andreas








--
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] ROLLBACK in a function

[Ken Winter]

Craig ~

Your rollback solution works!  For anyone who wants to use Craig's solution,
the new version of my example (with the test code omitted to clarify the
structural pattern) is:

CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: 
BEGIN
-- Test code: 
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;


By contrast, the old version (with the test code omitted) was:

CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: 
BEGIN
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
-- Test code: 
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;

To clarify the purpose of this structure, for anyone who may want to do the
same thing:  

This is a framework for running test code whose job is to run one or more
tests against the database.  Every test that fails is supposed to simply add
a diagnostic message to the "errs" string, which is then returned.  Every
test that is passed is to do nothing.  All tests need to be designed so that
they do not cause an exception that causes the function to abort.  And then,
at the end, the framework of the function returns errs and rolls back all
data changes it has made.

~ Thanks!
~ Ken


> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Craig Ringer
> Sent: Sunday, May 23, 2010 8:48 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] ROLLBACK in a function
> 
> On 24/05/10 02:51, Ken Winter wrote:
> > How can I write a PL/PgSQL function that rolls back every database
> > change it has done?
> 
> Have it raise an exception, causing the surrounding transaction to
> terminate with an error.
> 
> Another function calling yours can still catch the exception and handle
> it, so it's possible for your function's changes to be applied, but only
> if someone explicitly and intentionally catches and ignores the error.
> 
> > BEGIN
> >
> > RAISE EXCEPTION '';
> >
> > EXCEPTION
> >
> > WHEN raise_exception THEN
> >
> > SET search_path TO public, tests;
> >
> > -- Test code:
> >
> > SELECT count(*) INTO old_count FROM person_i;
> >
> > INSERT INTO person (last_name) VALUES ('_test');
> >
> > SELECT count(*) INTO new_count FROM person_i;
> >
> > IF NOT new_count = old_count + 1 THEN
> >
> > errs := errs || ': Failed to insert into ''person''';
> >
> > END IF;
> 
> 
> > The function runs OK, but it does not roll back the actions it did (in
> > this case, the INSERT).
> 
> You're on the right track, but you've got the code that changes the
> database in the exception handler _after_ the exception is thrown.
> 
> You want it in the BEGIN block _before_ the exception is thrown.
> However, you're also doing this quite an expensive way.
> 
> An INSERT/UPDATE/DELETE  can only fail to have any effect without
> throwing an exception if if has a WHERE clause, is intercepted by a
> BEFORE trigger that returns NULL, or is rewritten by a rule. If none of
> those are possible, your INSERT/UPDATE/DELETE is guaranteed to work or
> throw an exception.
> 
> eg (untested code, please verify before relying on it):
> 
>BEGIN
>  SELECT count(*) INTO old_count FROM person_i;
>  INSERT INTO person (last_name) VALUES ('_test');
>  SELECT count(*) INTO new_count FROM person_i;
>  IF NOT old_count + 1 = new_count THEN
>  RAISE EXCEPTION 'Update failed';
>  END IF;
>EXCEPTION WHEN raise_exception THEN
>  errs := errs || ': Failed to insert into ''person''';
>END;
> 
> 
> But ... please consider whether you really need this check with two
> expensive count(*) queries. Unless you're using BEFORE triggers that
> return NULL or rewrite rules, you can trust that an INSERT without a
> WHERE clause will always succeed or throw an exception.
> 
> If you *are* using triggers or rules, you should be able to use GET
> DIAGNOSTICS to see the affected row count, saving yourself those
> count(*) queries. See:
> 
> http://www.postgresql.org/docs/current/static/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
> 
> eg (untested code, should give you the idea):
> 
> DECLARE
> rowcount INTEGER;
> BEGIN
>-- function body here
>BEGIN
>  INSERT INTO person (last_name) VALUES ('_test');
>  GET DIAGNOSTICS rowcount = ROW_COUNT;
>  IF NOT rowcount = 1 THEN
> RAISE EXCEPTION 'Update failed';
>  END IF;
>EXCEPTION WHEN raise_exception THEN
>  errs := errs || ': Failed to insert into ''person''';
>END;
> END;
> 
> 
> You *can* trick Pg into not seeing that an update has happened even when
> it really has. For example, a BEFORE trigger can do its own  INSERT then
> return NULL to tell Pg to ignore the INSERT that invoked the trigger.
> Stor

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

[Sachin Srivastava]

On 5/24/10 7:52 PM, ritas wrote:

Hi,

When I try to install postgres 8.4 on windows server 2003, I get the
following error: "A non-fatal error occurred whilst loading database
modules. Please check the installation logs in C:/docs&setttings/user/local
settings/temp for details". The log file doesn't have any error, but it
looks like it stopped in the middle. Also, it doesn't install the postgres
service. I have attached the log file. Any help would be greatly
appreciated. Thanks.

http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log
bitrock_installer_1004serverlog.log
   
There must be a log file with name install-postgresql.log in the %TEMP% 
directory.


--
Regards,
Sachin Srivastava
EnterpriseDB , the Enterprise Postgres 
 company.

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

[ritas]

Thanks Sachin for your response. I have attached the install-postgresql.log.
Also, if it helps, I have the following error in my event viewer: pg_ctl:
could not register service "postgresql-8.4": error code 0
The postgres service is not listed under services.

Thanks.

http://old.nabble.com/file/p28660078/install-postgresql.log
install-postgresql.log 

Sachin Srivastava-2 wrote:
> 
> On 5/24/10 7:52 PM, ritas wrote:
>> Hi,
>>
>> When I try to install postgres 8.4 on windows server 2003, I get the
>> following error: "A non-fatal error occurred whilst loading database
>> modules. Please check the installation logs in
>> C:/docs&setttings/user/local
>> settings/temp for details". The log file doesn't have any error, but it
>> looks like it stopped in the middle. Also, it doesn't install the
>> postgres
>> service. I have attached the log file. Any help would be greatly
>> appreciated. Thanks.
>>
>> http://old.nabble.com/file/p28657511/bitrock_installer_1004serverlog.log
>> bitrock_installer_1004serverlog.log
>>
> There must be a log file with name install-postgresql.log in the %TEMP% 
> directory.
> 
> -- 
> Regards,
> Sachin Srivastava
> EnterpriseDB , the Enterprise Postgres 
>  company.
> 
> 

-- 
View this message in context: 
http://old.nabble.com/can%27t-install-postgres-8.4-on-windows-2003-server-tp28657511p28660078.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] Hiding data in postgresql

[Hector Beyers]

Hi guys,

does ANYONE have any tips on hiding data on a database server? This means
that data is stored in places that is not necessarily picked up in the
schema of the database. I am doing some research on databases and need some
direction.

Any help or direction will be highly appreciated.

Kind regards

Hector

Re: [GENERAL] Hiding data in postgresql

[Peter Hunsberger]

On Mon, May 24, 2010 at 2:16 PM, Hector Beyers  wrote:
>
> Hi guys,
> does ANYONE have any tips on hiding data on a database server? This means
> that data is stored in places that is not necessarily picked up in the
> schema of the database. I am doing some research on databases and need some
> direction.
> Any help or direction will be highly appreciated.

Let me guess: an attempt at security by obscurity?

I suppose you could always create a couple of columns such that some
function applied over them produced the real result (.  You could even
actually store this in an index, so although you could never see the
result directly (except in a dump) queries to get at it might perform
half reasonably.

-- 
Peter Hunsberger

-- 
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] Hiding data in postgresql

[Thom Brown]

On 24 May 2010 20:16, Hector Beyers  wrote:
>
> Hi guys,
> does ANYONE have any tips on hiding data on a database server? This means
> that data is stored in places that is not necessarily picked up in the
> schema of the database. I am doing some research on databases and need some
> direction.
> Any help or direction will be highly appreciated.
> Kind regards
> Hector
>

Why don't role permissions do what you need?

Thom

-- 
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] Hiding data in postgresql

[Scott Marlowe]

On Mon, May 24, 2010 at 1:16 PM, Hector Beyers  wrote:
>
> Hi guys,
> does ANYONE have any tips on hiding data on a database server? This means
> that data is stored in places that is not necessarily picked up in the
> schema of the database. I am doing some research on databases and need some
> direction.

Uhhh,  initdb -D /dev/null ???

-- 
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] Hiding data in postgresql

[Bill Moran]

In response to Scott Marlowe :

> On Mon, May 24, 2010 at 1:16 PM, Hector Beyers  wrote:
> >
> > Hi guys,
> > does ANYONE have any tips on hiding data on a database server? This means
> > that data is stored in places that is not necessarily picked up in the
> > schema of the database. I am doing some research on databases and need some
> > direction.

On a more serious note, I would think the only way to do that would be
to have columns that are misnamed and don't do what you expect.

For example, have an image column that stores image data with your
secret data hidden via steganography.  You could do something similar
with some scheme to hide data in text fields or numeric fields, but I
don't know of any specific technique.

Assuming you mean _hide_ and not _secure_.  To secure it, encrypt it
(using something like pgcrypto).

But in the schema?  I doubt it.  PG's data storage is pretty transparent,
you'd probably have to hack the source to pull that off.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Hiding data in postgresql

[Hector Beyers]

Wow, this is really an idea I can work with.

I know this is getting really brainstorming, but do you think it is possible
to apply steganography (hiding data in pictures) tactics to the columns of a
database?

Regards
Hector




On Mon, May 24, 2010 at 9:30 PM, Peter Hunsberger <
peter.hunsber...@gmail.com> wrote:

> On Mon, May 24, 2010 at 2:16 PM, Hector Beyers  wrote:
> >
> > Hi guys,
> > does ANYONE have any tips on hiding data on a database server? This means
> > that data is stored in places that is not necessarily picked up in the
> > schema of the database. I am doing some research on databases and need
> some
> > direction.
> > Any help or direction will be highly appreciated.
>
> Let me guess: an attempt at security by obscurity?
>
> I suppose you could always create a couple of columns such that some
> function applied over them produced the real result (.  You could even
> actually store this in an index, so although you could never see the
> result directly (except in a dump) queries to get at it might perform
> half reasonably.
>
> --
> Peter Hunsberger
>

Re: [GENERAL] Hiding data in postgresql

[Hector Beyers]

Yes, I mean hide. I am approaching the problem out of the perspective of a
malicious user / hacker.


On Mon, May 24, 2010 at 10:08 PM, Bill Moran wrote:

> In response to Scott Marlowe :
>
> > On Mon, May 24, 2010 at 1:16 PM, Hector Beyers 
> wrote:
> > >
> > > Hi guys,
> > > does ANYONE have any tips on hiding data on a database server? This
> means
> > > that data is stored in places that is not necessarily picked up in the
> > > schema of the database. I am doing some research on databases and need
> some
> > > direction.
>
> On a more serious note, I would think the only way to do that would be
> to have columns that are misnamed and don't do what you expect.
>
> For example, have an image column that stores image data with your
> secret data hidden via steganography.  You could do something similar
> with some scheme to hide data in text fields or numeric fields, but I
> don't know of any specific technique.
>
> Assuming you mean _hide_ and not _secure_.  To secure it, encrypt it
> (using something like pgcrypto).
>
> But in the schema?  I doubt it.  PG's data storage is pretty transparent,
> you'd probably have to hack the source to pull that off.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>

Re: [GENERAL] Hiding data in postgresql

[Peter Hunsberger]

On Mon, May 24, 2010 at 3:10 PM, Hector Beyers  wrote:
> Wow, this is really an idea I can work with.
> I know this is getting really brainstorming, but do you think it is possible
> to apply steganography (hiding data in pictures) tactics to the columns of a
> database?

If by that, you mean a variation on my original reply, then yes, but
you'd need at least one column that did not necessarily have contents
that looked like normal text.  This column would work as some form of
offset pointer into the other columns.  The result wouldn't
necessarily require much in the way of a reconstruction algorithm, but
if not it would likely also be fairly easy to figure out if someone
stumbled across your scheme.

If you want the truly secure variation on this then use some public
key / private key scheme in conjunction with this (in particular, ECC
could be pretty fast and have a small key size), so that some portion
of this is encrypted.  However, if you're doing that, you might as
well just encrypt the data directly...

-- 
Peter Hunsberger

-- 
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] Hiding data in postgresql

[Hector Beyers]

Dear Peter,

can you elaborate on what you mean by storing 'this' in the index. Are you
referring to the function that is applied over the data?

How would you be able to see the result with a dump?

Thanks, your ideas are really helping...

Regards
Hector

On Mon, May 24, 2010 at 9:30 PM, Peter Hunsberger <
peter.hunsber...@gmail.com> wrote:

> On Mon, May 24, 2010 at 2:16 PM, Hector Beyers  wrote:
> >
> > Hi guys,
> > does ANYONE have any tips on hiding data on a database server? This means
> > that data is stored in places that is not necessarily picked up in the
> > schema of the database. I am doing some research on databases and need
> some
> > direction.
> > Any help or direction will be highly appreciated.
>
> Let me guess: an attempt at security by obscurity?
>
> I suppose you could always create a couple of columns such that some
> function applied over them produced the real result (.  You could even
> actually store this in an index, so although you could never see the
> result directly (except in a dump) queries to get at it might perform
> half reasonably.
>
> --
> Peter Hunsberger
>

Re: [GENERAL] Hiding data in postgresql

[Peter Hunsberger]

On Mon, May 24, 2010 at 3:24 PM, Hector Beyers  wrote:
> Dear Peter,
> can you elaborate on what you mean by storing 'this' in the index. Are you
> referring to the function that is applied over the data?
> How would you be able to see the result with a dump?

Yes, you would the function to create the index, something like:

create index gunk on tbl1 ( hidefunc( a, b, c )  )

The data created in this index might be viewable in a database dump by
simple inspection with something like an  editor.  How meaningful it
might be is another question...

-- 
Peter Hunsberger

-- 
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] Hiding data in postgresql

[Scott Marlowe]

Acutally, that's probably the best way to this.  The key to effective
steganography is having a large amount of data to store a small amount
of data.  So, if you don't mind having a db that's 10 to 100 times
bigger than it has to be to store the original data it should work.

On Mon, May 24, 2010 at 2:10 PM, Hector Beyers  wrote:
> Wow, this is really an idea I can work with.
> I know this is getting really brainstorming, but do you think it is possible
> to apply steganography (hiding data in pictures) tactics to the columns of a
> database?
> Regards
> Hector
>
>
>
> On Mon, May 24, 2010 at 9:30 PM, Peter Hunsberger
>  wrote:
>>
>> On Mon, May 24, 2010 at 2:16 PM, Hector Beyers  wrote:
>> >
>> > Hi guys,
>> > does ANYONE have any tips on hiding data on a database server? This
>> > means
>> > that data is stored in places that is not necessarily picked up in the
>> > schema of the database. I am doing some research on databases and need
>> > some
>> > direction.
>> > Any help or direction will be highly appreciated.
>>
>> Let me guess: an attempt at security by obscurity?
>>
>> I suppose you could always create a couple of columns such that some
>> function applied over them produced the real result (.  You could even
>> actually store this in an index, so although you could never see the
>> result directly (except in a dump) queries to get at it might perform
>> half reasonably.
>>
>> --
>> Peter Hunsberger
>
>



-- 
When fascism comes to America, it will be intolerance sold as diversity.

-- 
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] Hiding data in postgresql

[Merlin Moncure]

On Mon, May 24, 2010 at 3:16 PM, Hector Beyers  wrote:
>
> Hi guys,
> does ANYONE have any tips on hiding data on a database server? This means
> that data is stored in places that is not necessarily picked up in the
> schema of the database. I am doing some research on databases and need some
> direction.
> Any help or direction will be highly appreciated.

First question: Have you considered 1. encrypting data when you put it
in the database and 2. decrypting it when you pull it out?

Let me humbly state that the #1 problem that beginners face with
security and encryption is focusing too much on the mechanics and not
enough on the 'big picture' issues:

*) What data is to remain secret?
*) Who is allowed to see the secret data?
*) When do they see it?
*) What sacrifices are you willing to make to keep the data secret?
*) Where are you going to store the key?

Answers to those questions should get you more helpful answers.
Postgres has a lot of features to hide data, some obvious (pgcrypto,
grant/revoke) and some not so obvious (revoking permissions from
pg_proc).  Judging from your question you may be interested in some
extra-special techniques...please be more specific!

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

[Dennis Gearon]

is there anyway with a running instance of postgres to find out if it was 
compiled with:

   BIGINT
vs
   DOUBLE PRECISION

timestamps?


Dennis Gearon

Signature Warning

EARTH has a Right To Life,
  otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php

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

[Osvaldo Kussama]

2010/5/25 Dennis Gearon :
> is there anyway with a running instance of postgres to find out if it was 
> compiled with:
>
>   BIGINT
> vs
>   DOUBLE PRECISION
>
> timestamps?
>


SHOW INTEGER_DATETIMES;

Osvaldo

-- 
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] cleaning wal files from postgres

[Kenichiro Tanaka]

Hello.

When you execute pg_stop_backup,postgresql makes a special file, like this
00010015.0064.backup.

This means you can delete wal 00010014 and before.

The PostgreSQL manual also says following.

http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-ARCHIVING-WAL

24.3.2. Making a Base Backup

To make use of the backup, you will need to keep around all the WAL 
segment files
 generated during and after the file system backup. To aid you in doing 
this,
the pg_stop_backup function creates a backup history file that is 
immediately
stored into the WAL archive area. This file is named after the first WAL 
segment
file that you need to have to make use of the backup. For example, if 
the starting
WAL file is 0001123455CD the backup history file will be 
named something
like 0001123455CD.007C9330.backup. (The second part of the 
file name
stands for an exact position within the WAL file, and can ordinarily be 
ignored.)
Once you have safely archived the file system backup and the WAL segment 
files used
during the backup (as specified in the backup history file), all 
archived WAL segments
with names numerically less are no longer needed to recover the file 
system backup and
 can be deleted. However, you should consider keeping several backup 
sets to be

absolutely certain that you can recover your data.


Thank you.



oops, sorry, my question was very ambiguous ...
What I want to know is:
The correct procedure to remove the wal files that have been used  in 
the recovery of a postgres server.



Consider the following;

1.-  A main server in production.
2.- A  secondary server that is normally off.
3.-  By connecting the secondary server, the primary server must copy 
the  directory data as well as wal files generated during the copying 
process.


4.- start up  the postgres on the secondary server.

5o.Delete wal  files no longer will be used by postgres to save disk 
space.


 how can i know which  wal file is not required by postgres and
delete   wal files from  main and secondary server, to save space disk 
on the servers ? (after a succesfully start up on secondary server)



regards eddie.

On 05/21/2010 03:43 PM, Andreas Schmitz wrote:


maybe I don't understand the problem. there is no need to clean WAL 
files after recovery. where exactly is the problem ?


regards

andreas


erobles wrote:


 PITR  recovery

On 05/21/2010 03:04 PM, Andreas Schmitz wrote:

erobles wrote:

which is the right procedure to clean wal files after a  recovery ??

what kind of recovery ?

regards

andreas











--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.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] timestamp configuration

[Dennis Gearon]

for those walking up to a new server and who want to know the same information:

shell script invoked by:

script_filename database user

#!/bin/sh
psql -c "show INTEGER_DATETIMES;" -d $1 -U $2 -W



Dennis Gearon

Signature Warning

EARTH has a Right To Life,
  otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php


--- On Mon, 5/24/10, Osvaldo Kussama  wrote:

> From: Osvaldo Kussama 
> Subject: Re: [GENERAL] timestamp configuration
> To: "Dennis Gearon" 
> Cc: pgsql-general@postgresql.org
> Date: Monday, May 24, 2010, 9:02 PM
> 2010/5/25 Dennis Gearon :
> > is there anyway with a running instance of postgres to
> find out if it was compiled with:
> >
> >   BIGINT
> > vs
> >   DOUBLE PRECISION
> >
> > timestamps?
> >
> 
> 
> SHOW INTEGER_DATETIMES;
> 
> Osvaldo
> 

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