[GENERAL] why is pg_dump so much smaller than my database?
I've got a pretty big database (~30 gigs) and when I do a pg_dump, it ends up only being 2 gigs. The database consists mainly of one very large table (w/ a few varchar columns) which, according to pg_relation_size() is 10 gigs and pg_total_relation_size() is 26 gigs (we need to drop some indexes there.) I'm just trying to get my head around the pg_dump being an order of magnitude smaller than the darned database itself. I would thing that the db would offer more efficient encoding for a lot of stuff vs. an ascii file. Thanks, Carson
[GENERAL] PG Log
I have queries regarding columns in Postgres CSV Log. Following is a sample Logline 2012-03-28 19:25:47.968 IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET",2012-03-28 19:25:47 IST,2/0,0,LOG,0,"QUERY STATISTICS","! system usage stats: ! 0.047000 elapsed 0.00 user 0.00 system sec ! [0.078125 user 0.031250 sys total]","Select * from stock_apals" ,,"ShowUsage, .\src\backend\tcop\postgres.c:4305","" I am aware of all the data segments except the following "! system usage stats: ! 0.047000 elapsed 0.00 user 0.00 system sec ! [0.078125 user 0.031250 sys total]", What do the number mean, it seems to appear only with a Logline for SQL statements thank you arvind
Re: [GENERAL] scripted 'pg_ctl start' hangs and never finishes, goes
Interestingly enough, when using a perl system() vs the qx() or backticks, it doesn't have this behavior. So I've got it working now with some modifications. I'm still interested as why I've seen this behavior, a. from a perl qx() call and b. from an ssh connection from another server. - Brian F On 03/28/2012 04:31 PM, Brian Fehrle wrote: Hi all, OS: Linux 64bit PostgreSQL Version: 9.0.5 installed from source. - Brian F
[GENERAL] scripted 'pg_ctl start' hangs and never finishes, goes
Hi all, OS: Linux 64bit PostgreSQL Version: 9.0.5 installed from source. I'm writing up a process that will bring down a warm standby cluster, tarball the data directory, then bring the warm standby back up. I'm having an issue where starting the database with pg_ctl results in the command never exiting. The warmstandby does come back online and starts recovering WAL files (evident in the log), however the command just does not exit. When I ctl -c from the script, the database receives a "fast shutdown". Basic script logic: pg_ctl -D /path/to/datadir stop -m fast cd /path/to/datadir/ tar -czvf /backups/mydatabase.tar.gz * pg_ctl -D /path/to/datadir start Originally, I was performing the 'pg_ctl start' over ssh from another box, but I ran into this issue and just assumed it had something to do with doing it over ssh. Now I'm doing it on the actual database box from a perl script and I've started having the same issue. I'm testing this on a very small database, 2 megs in size. When I execute each event manually, it works just fine. Actual perl code: my $output = qx(/bin/pg_ctl -D $dataDir start 2>&1); The last thing, while the command is 'hung', I search for a running pg_ctl process and come back with: [postgres@gridpoint_4 bin]$ ps aux | grep pg_ctl postgres 601 0.0 0.0 0 0 pts/3Z+ 15:26 0:00 [pg_ctl] postgres 619 0.0 0.0 61180 748 pts/2S+ 15:26 0:00 grep pg_ctl Below is the log from the warmstandby as the actions take place. LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down in recovery at 2012-03-28 15:02:43 PDT LOG: starting archive recovery LOG: restored log file "00010057" from archive LOG: redo starts at 0/57000240 LOG: consistent recovery state reached at 0/5800 LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down Any thoughts on what could be the issues? This has happened on the same environment whether I'm doing it from within perl on the actual cluster, or over an ssh command such as ssh user@standby "pg_ctl -D /path/to/data/ start". What's in common is that the pg_ctl becomes a child process of something other than my own shell, could that be the issue? Thanks in advance, - Brian F
[GENERAL] could not read block... how could I identify/fix
There was a hardware crash. Since then INSERT to one table is failing with the following message: ERROR: could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes ERROR: could not read block 11805 of relation base/16396/3720450: read only 0 of 8192 bytes Similar error was fixed by doing re-indexing or identifying corrupted data by COPY command and remove the row etc. However, the issue hasn't been resolved yet after taking the following actions: REINDEXed entire table. It was successful. pg_dump was also successful then restore was successful. COPY corrupted table to file was successful with no error. Analyze was also successful with no error. Do you think this should be the next step I might take? Could you give me an advice of how I could identify corrupted error. Version: "PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370), 32-bit" Thank you very much for your time in advance. -- Naoko
Re: [GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
On 03/28/2012 09:54 AM, leaf_yxj wrote: For oracle, the normal user can't see all the system catalog. but for postgresql, it looks like all the user can see the system catalog. Should we limit the user read privilege to system catalog? In oracle, the system privilege has create table, create view,create function. For postgresql database, how to control the user who only can create table but can't create view. Based on the test I did, once the user has the create privilege on the schema, the user will have any create privilege on that schema. In postgresql, Rule is used to control that ??? very confused! Path to unconfusion:): http://www.postgresql.org/docs/9.0/interactive/sql-grant.html You can grant CREATE on a schema and then restrict CREATE within the schema for different objects types. In recent versions you are looking for ALL * IN SCHEMA schema_name where * is the object type. Thanks. Regards. Grace -- -- 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] How return a row from a function so it is recognized as such by caller?
On Wed, Mar 28, 2012 at 4:02 PM, Merlin Moncure wrote: > On Wed, Mar 28, 2012 at 2:49 PM, Kenneth Tilton wrote: > > Well then I have the other error. With this code: > > > >execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW; > > try this: > select * from now_plus_30(NEW) into bpa; > Yer a genius. Thx! -kt
Re: [GENERAL] How return a row from a function so it is recognized as such by caller?
On Wed, Mar 28, 2012 at 2:49 PM, Kenneth Tilton wrote: > Well then I have the other error. With this code: > > execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW; try this: select * from now_plus_30(NEW) into bpa; :-D 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] How return a row from a function so it is recognized as such by caller?
>
>
>
On Wed, Mar 28, 2012 at 3:40 PM, Merlin Moncure wrote:
> On Wed, Mar 28, 2012 at 2:08 PM, Kenneth Tilton wrote:
> > Thanks, Merlin. Maybe I have some subtle detail wrong. When
> > NEW.warn_time_init is 'now_plus_30' and I have this as my execute
> statement:
> >
> > execute NEW.warn_time_init || '($1)' into bpa using NEW;
> >
> > ...I get:
> >
>
> your'e missing a 'select' in there. functions must be called through
> select (just like you can't call a function by name in psql without
> it).
>
>
Well then I have the other error. With this code:
execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW;
I get:
NOTICE: bpa inbound (,now_plus_30)
CONTEXT: SQL statement "select now_plus_30($1)"
PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
NOTICE: warn time in input row = ("2012-04-27
19:04:37.793835+00",now_plus_30)
CONTEXT: SQL statement "select now_plus_30($1)"
PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
ERROR: invalid input syntax for type timestamp with time zone:
"("2012-04-27 19:04:37.793835+00",now_plus_30)"
CONTEXT: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
** Error **
ERROR: invalid input syntax for type timestamp with time zone:
"("2012-04-27 19:04:37.793835+00",now_plus_30)"
SQL state: 22007
Context: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
I guess you are right, though. This is the error I have to resolve.
Thx,
-kt
Re: [GENERAL] How return a row from a function so it is recognized as such by caller?
On Wed, Mar 28, 2012 at 2:08 PM, Kenneth Tilton wrote: > Thanks, Merlin. Maybe I have some subtle detail wrong. When > NEW.warn_time_init is 'now_plus_30' and I have this as my execute statement: > > execute NEW.warn_time_init || '($1)' into bpa using NEW; > > ...I get: > your'e missing a 'select' in there. functions must be called through select (just like you can't call a function by name in psql without it). 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] How return a row from a function so it is recognized as such by caller?
>
>
>> On Wed, Mar 28, 2012 at 2:36 PM, Merlin Moncure wrote:
> On Wed, Mar 28, 2012 at 1:11 PM, Kenneth Tilton wrote:
> >
> >
> > On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton
> wrote:
> >>
> >> First, apologies for being too succinct. I should have reiterated the
> >> message subject to provide the context: I am just trying to return a row
> >> from a function and have the caller understand it. Oh, and I am a nooby
> so
> >> it is probably something daft.
> >>
> >> Second, I just tried returning the row as an out variable and got the
> same
> >> result. I'll try messing with the caller...
> >
> >
> > OK, this works in re getting the row back:
> >
> > bpa := now_plus_30(NEW);
> >
> > But I need to execute an arbitrary function passed in as text, and I now
> > realize EXECUTE is for SQL and I am trying to use it to "eval" plpgsql
> and
> > those are different animals.
> >
> > I see no plpgsql equivalent of EXECUTE, ie where I can build up a plpgsql
> > statement like this:
> >
> >execute 'bpa := ' || function_name || '($1)' using NEW into bpa;
>
> If all you are doing is assignment into a variable, you can use
> EXECUTE...INTO...USING. That should work.
>
>
Thanks, Merlin. Maybe I have some subtle detail wrong. When
NEW.warn_time_init is 'now_plus_30' and I have this as my execute statement:
execute NEW.warn_time_init || '($1)' into bpa using NEW;
...I get:
ERROR: syntax error at or near "now_plus_30"
LINE 1: now_plus_30($1)
^
QUERY: now_plus_30($1)
CONTEXT: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
> ** Error **
> ERROR: syntax error at or near "now_plus_30"
SQL state: 42601
Context: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
Using a more conventional syntax I am back to where I started:
execute 'select ' || NEW.warn_time_init || '($1)' into bpa using NEW;
produces:
NOTICE: bpa inbound (,now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30($1)"
>
> PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
>
> NOTICE: warn time in input row = ("2012-04-27
>> 19:04:37.793835+00",now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30($1)"
>
> PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
>
>
>>
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "("2012-04-27 19:04:37.793835+00",now_plus_30)"
>
> CONTEXT: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
>
>
>> ** Error **
>
>
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "("2012-04-27 19:04:37.793835+00",now_plus_30)"
>
> SQL state: 22007
>
> Context: PL/pgSQL function "bp_alert_init" line 6 at EXECUTE statement
>
>
>>
cheers, ken
Re: [GENERAL] How return a row from a function so it is recognized as such by caller?
On Wed, Mar 28, 2012 at 1:11 PM, Kenneth Tilton wrote: > > > On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton wrote: >> >> First, apologies for being too succinct. I should have reiterated the >> message subject to provide the context: I am just trying to return a row >> from a function and have the caller understand it. Oh, and I am a nooby so >> it is probably something daft. >> >> Second, I just tried returning the row as an out variable and got the same >> result. I'll try messing with the caller... > > > OK, this works in re getting the row back: > > bpa := now_plus_30(NEW); > > But I need to execute an arbitrary function passed in as text, and I now > realize EXECUTE is for SQL and I am trying to use it to "eval" plpgsql and > those are different animals. > > I see no plpgsql equivalent of EXECUTE, ie where I can build up a plpgsql > statement like this: > > execute 'bpa := ' || function_name || '($1)' using NEW into bpa; If all you are doing is assignment into a variable, you can use EXECUTE...INTO...USING. That should work. 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] Limit the normal user to see system catalog or not??? And create privilege???
On Wed, Mar 28, 2012 at 10:54 AM, leaf_yxj wrote: > For oracle, the normal user can't see all the system catalog. but for > postgresql, it looks like all the user can see the system catalog. Should > we limit the user read privilege to system catalog? Yeah, postgresql tends to focus on controlling what the user can DO not so much on what they can SEE about the schema. However... > In oracle, the system privilege has create table, create view,create > function. For postgresql database, how to control the user who only can > create table but can't create view. Based on the test I did, once the user > has the create privilege on the schema, the user will have any create > privilege on that schema. In postgresql, Rule is used to control that ??? > very confused! PostgreSQL just doesn't have the fine grained control that Oracle has. If you can create a table, you can create a view. OTOH, since a view is basical an empty table with a rule on top, it's not like it's all that different. -- 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 return a row from a function so it is recognized as such by caller?
On Wed, Mar 28, 2012 at 1:52 PM, Kenneth Tilton wrote:
> First, apologies for being too succinct. I should have reiterated the
> message subject to provide the context: I am just trying to return a row
> from a function and have the caller understand it. Oh, and I am a nooby so
> it is probably something daft.
>
> Second, I just tried returning the row as an out variable and got the same
> result. I'll try messing with the caller...
>
OK, this works in re getting the row back:
bpa := now_plus_30(NEW);
But I need to execute an arbitrary function passed in as text, and I now
realize EXECUTE is for SQL and I am trying to use it to "eval" plpgsql and
those are different animals.
I see no plpgsql equivalent of EXECUTE, ie where I can build up a plpgsql
statement like this:
execute 'bpa := ' || function_name || '($1)' using NEW into bpa;
Ideas welcome.
--kt
>
> -kt
>
>
> On Wed, Mar 28, 2012 at 12:54 PM, Kenneth Tilton wrote:
>
>> On version:
>>
>> PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
>> 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
>>
>> I get this error (all code at end of post) in pgAdmin:
>>
>> NOTICE: bpa inbound (,now_plus_30)
>>>
>>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>>
>>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>> NOTICE: warn time in input row = ("2012-04-27
16:41:20.338239+00",now_plus_30)
>>>
>>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>>
>>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>>
ERROR: invalid input syntax for type timestamp with time zone:
"("2012-04-27 16:41:20.338239+00",now_plus_30)"
>>>
>>> CONTEXT: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>>
** Error **
>>>
>>>
ERROR: invalid input syntax for type timestamp with time zone:
"("2012-04-27 16:41:20.338239+00",now_plus_30)"
>>>
>>> SQL state: 22007
>>>
>>> Context: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>>
>> Note that I have eliminated the complexity of the callback and simply
>> call the desired initializer directly. FWIW, using the desired EXECUTE
>> statement produces exactly the same error.
>>
>> If I declare the receiving variable to be a record, pgAdmin shows me this:
>>
>> NOTICE: bpa inbound (,now_plus_30)
>>>
>>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>>
>>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>> NOTICE: warn time in input row = ("2012-04-27
16:46:22.62478+00",now_plus_30)
>>>
>>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>>
>>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>>
>>> NOTICE: caller got bpa ("(""2012-04-27
16:46:22.62478+00"",now_plus_30)") <-
>>>
>>>
ERROR: record "bpa" has no field "warn_time"
>>>
>>> CONTEXT: SQL statement "SELECT bpa.warn_time"
>>>
>>> PL/pgSQL function "bp_alert_init" line 9 at RAISE
>>>
>>>
>> So it looks as if I have to "unwrap" or eval the return value (or change
>> the way I am returning it). But the callee is declared as returning a
>> bp_alert and returns a variable of type bp_alert, so I am not sure what
>> more I can do in the callee. The caller is selecting into a variable of
>> type bp_alert, so that too seems clear.
>>
>> Hints welcome, code next.
>>
>> ken
>>
>> -- code starts here
>> ---
>>
>> set search_path to public;
>> drop table if exists bp_alert cascade;
>>
>> CREATE TABLE bp_alert (
>> warn_time timestamp WITH TIME ZONE,
>> warn_time_init text
>> )
>>
>> CREATE OR REPLACE FUNCTION now_plus_30(bpa bp_alert)
>> RETURNS bp_alert AS
>> $BODY$
>>
>> declare
>> begin
>>raise notice 'bpa inbound %', bpa;
>>bpa.warn_time = now() + interval '30 days';
>>raise notice 'warn time in input row = %', bpa;
>>return bpa;
>> end;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE;
>>
>> CREATE OR REPLACE FUNCTION bp_alert_init()
>> RETURNS trigger AS
>> $BODY$
>>
>> declare
>>bpa bp_alert; -- make this a record and the "warn time in caller"
>> raise fails on bpa not having warn_time
>> begin
>>-- no difference: execute 'select ' || NEW.warn_time_init || '($1)'
>> using NEW into bpa;
>>select now_plus_30(NEW) into bpa;
>>raise notice 'caller got bpa %', bpa;
>>raise notice 'warn time in caller now %', bpa.warn_time;
>>return bpa;
>> end;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE;
>>
>> drop trigger if exists bp_alert on bp_alert;
>>
>> CREATE TRIGGER bp_alert
>> BEFORE INSERT
>> ON bp_alert
>> FOR EACH ROW
>> EXECUTE PROCEDURE bp_alert_init();
>>
>> insert into bp_alert (warn_time_init) values ('now_plus_30');
>>
>>
>
Re: [GENERAL] How return a row from a function so it is recognized as such by caller?
First, apologies for being too succinct. I should have reiterated the
message subject to provide the context: I am just trying to return a row
from a function and have the caller understand it. Oh, and I am a nooby so
it is probably something daft.
Second, I just tried returning the row as an out variable and got the same
result. I'll try messing with the caller...
-kt
On Wed, Mar 28, 2012 at 12:54 PM, Kenneth Tilton wrote:
> On version:
>
> PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
>
> I get this error (all code at end of post) in pgAdmin:
>
> NOTICE: bpa inbound (,now_plus_30)
>>
>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>
>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>> NOTICE: warn time in input row = ("2012-04-27
>>> 16:41:20.338239+00",now_plus_30)
>>
>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>
>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>>
>>>
>>> ERROR: invalid input syntax for type timestamp with time zone:
>>> "("2012-04-27 16:41:20.338239+00",now_plus_30)"
>>
>> CONTEXT: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>>
>>> ** Error **
>>
>>
>>> ERROR: invalid input syntax for type timestamp with time zone:
>>> "("2012-04-27 16:41:20.338239+00",now_plus_30)"
>>
>> SQL state: 22007
>>
>> Context: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>>
>>>
> Note that I have eliminated the complexity of the callback and simply call
> the desired initializer directly. FWIW, using the desired EXECUTE statement
> produces exactly the same error.
>
> If I declare the receiving variable to be a record, pgAdmin shows me this:
>
> NOTICE: bpa inbound (,now_plus_30)
>>
>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>
>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>> NOTICE: warn time in input row = ("2012-04-27
>>> 16:46:22.62478+00",now_plus_30)
>>
>> CONTEXT: SQL statement "select now_plus_30(NEW)"
>>
>> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>>
>> NOTICE: caller got bpa ("(""2012-04-27
>>> 16:46:22.62478+00"",now_plus_30)") <-
>>
>>
>>>
>>> ERROR: record "bpa" has no field "warn_time"
>>
>> CONTEXT: SQL statement "SELECT bpa.warn_time"
>>
>> PL/pgSQL function "bp_alert_init" line 9 at RAISE
>>
>>
> So it looks as if I have to "unwrap" or eval the return value (or change
> the way I am returning it). But the callee is declared as returning a
> bp_alert and returns a variable of type bp_alert, so I am not sure what
> more I can do in the callee. The caller is selecting into a variable of
> type bp_alert, so that too seems clear.
>
> Hints welcome, code next.
>
> ken
>
> -- code starts here ---
>
> set search_path to public;
> drop table if exists bp_alert cascade;
>
> CREATE TABLE bp_alert (
> warn_time timestamp WITH TIME ZONE,
> warn_time_init text
> )
>
> CREATE OR REPLACE FUNCTION now_plus_30(bpa bp_alert)
> RETURNS bp_alert AS
> $BODY$
>
> declare
> begin
>raise notice 'bpa inbound %', bpa;
>bpa.warn_time = now() + interval '30 days';
>raise notice 'warn time in input row = %', bpa;
>return bpa;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE;
>
> CREATE OR REPLACE FUNCTION bp_alert_init()
> RETURNS trigger AS
> $BODY$
>
> declare
>bpa bp_alert; -- make this a record and the "warn time in caller" raise
> fails on bpa not having warn_time
> begin
>-- no difference: execute 'select ' || NEW.warn_time_init || '($1)'
> using NEW into bpa;
>select now_plus_30(NEW) into bpa;
>raise notice 'caller got bpa %', bpa;
>raise notice 'warn time in caller now %', bpa.warn_time;
>return bpa;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE;
>
> drop trigger if exists bp_alert on bp_alert;
>
> CREATE TRIGGER bp_alert
> BEFORE INSERT
> ON bp_alert
> FOR EACH ROW
> EXECUTE PROCEDURE bp_alert_init();
>
> insert into bp_alert (warn_time_init) values ('now_plus_30');
>
>
[GENERAL] system catalog privilege and create privilege ??? how to control them?? thanks
For oracle, the normal user can't see all the system catalog. but for postgresql, it looks like all the user can see the system catalog. Should we limit the user read privilege to system catalog? In oracle, the system privilege has create table, create view,create function. For postgresql database, how to control the user who only can create table but can't create view. Based on the test I did, once the user has the create privilege on the schema, the user will have any create privilege on that schema. In postgresql, Rule is used to control that ??? very confused! Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5601150.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] How return a row from a function so it is recognized as such by caller?
On version:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
I get this error (all code at end of post) in pgAdmin:
NOTICE: bpa inbound (,now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30(NEW)"
>
> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
> NOTICE: warn time in input row = ("2012-04-27
>> 16:41:20.338239+00",now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30(NEW)"
>
> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
>
>>
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "("2012-04-27 16:41:20.338239+00",now_plus_30)"
>
> CONTEXT: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
>
>> ** Error **
>
>
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "("2012-04-27 16:41:20.338239+00",now_plus_30)"
>
> SQL state: 22007
>
> Context: PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
>
>>
Note that I have eliminated the complexity of the callback and simply call
the desired initializer directly. FWIW, using the desired EXECUTE statement
produces exactly the same error.
If I declare the receiving variable to be a record, pgAdmin shows me this:
NOTICE: bpa inbound (,now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30(NEW)"
>
> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
> NOTICE: warn time in input row = ("2012-04-27
>> 16:46:22.62478+00",now_plus_30)
>
> CONTEXT: SQL statement "select now_plus_30(NEW)"
>
> PL/pgSQL function "bp_alert_init" line 7 at SQL statement
>
> NOTICE: caller got bpa ("(""2012-04-27 16:46:22.62478+00"",now_plus_30)")
>> <-
>
>
>>
>> ERROR: record "bpa" has no field "warn_time"
>
> CONTEXT: SQL statement "SELECT bpa.warn_time"
>
> PL/pgSQL function "bp_alert_init" line 9 at RAISE
>
>
So it looks as if I have to "unwrap" or eval the return value (or change
the way I am returning it). But the callee is declared as returning a
bp_alert and returns a variable of type bp_alert, so I am not sure what
more I can do in the callee. The caller is selecting into a variable of
type bp_alert, so that too seems clear.
Hints welcome, code next.
ken
-- code starts here ---
set search_path to public;
drop table if exists bp_alert cascade;
CREATE TABLE bp_alert (
warn_time timestamp WITH TIME ZONE,
warn_time_init text
)
CREATE OR REPLACE FUNCTION now_plus_30(bpa bp_alert)
RETURNS bp_alert AS
$BODY$
declare
begin
raise notice 'bpa inbound %', bpa;
bpa.warn_time = now() + interval '30 days';
raise notice 'warn time in input row = %', bpa;
return bpa;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION bp_alert_init()
RETURNS trigger AS
$BODY$
declare
bpa bp_alert; -- make this a record and the "warn time in caller" raise
fails on bpa not having warn_time
begin
-- no difference: execute 'select ' || NEW.warn_time_init || '($1)'
using NEW into bpa;
select now_plus_30(NEW) into bpa;
raise notice 'caller got bpa %', bpa;
raise notice 'warn time in caller now %', bpa.warn_time;
return bpa;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
drop trigger if exists bp_alert on bp_alert;
CREATE TRIGGER bp_alert
BEFORE INSERT
ON bp_alert
FOR EACH ROW
EXECUTE PROCEDURE bp_alert_init();
insert into bp_alert (warn_time_init) values ('now_plus_30');
[GENERAL] Limit the normal user to see system catalog or not??? And create privilege???
For oracle, the normal user can't see all the system catalog. but for postgresql, it looks like all the user can see the system catalog. Should we limit the user read privilege to system catalog? In oracle, the system privilege has create table, create view,create function. For postgresql database, how to control the user who only can create table but can't create view. Based on the test I did, once the user has the create privilege on the schema, the user will have any create privilege on that schema. In postgresql, Rule is used to control that ??? very confused! Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/Limit-the-normal-user-to-see-system-catalog-or-not-And-create-privilege-tp5601146p5601146.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] Re: how postgresql passes the parameter to the function. syntax error near or at "$1".
Pavel, Thanks a lot. Regards. Grace At 2012-03-29 00:27:12,"Pavel Stehule [via PostgreSQL]" wrote: Hello parameter - $n cannot be used on table name or column name position. Some statements - DROP, VACUUM, ANALYZE doesn't support parameters ever. You have to use dynamic SQL in these cases. CREATE OR REPLACE FUNCTION foo(tablename text, value text) RETURNS void AS $$ BEGIN EXECUTE 'insert into ' || quote_ident(tablename) || ' VALUES($1)' USING value; END; $$ LANGUAGE plpgsql Regards Pavel Stehule 2012/3/28 leaf_yxj <[hidden email]>: > I want to create a function which use to truncate the table specified by the > caller. I very confused how postgresql pass this parameter into the function > : > > as a superuser to execute : > 1) > create or replace function d() returns void as $$ > analyze; > $$ language sql; > > > - this works when i issue select d() > > 2) this doesn't work > > create or replace function v(text) returns void as $$ > analyze $1; > $$ language sql; > > why and how to correct it? > > > I tried another function for insert > > 1) this works. > > create or replace function insert_f(integer) returns void as $$ > insert into t1 values($1); > $$ language sql; > > this works when i issue select insert_f(20); > > 2) this doesn't work. > > create or replace function insert_f(text,integer) returns void as $$ > insert into $1 values($2); > $$ language sql; > > it failed to create the function and give me error : syntax error at or > near "$1". > > Please help. > > Thanks. > > Regards. > > Grace > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list ([hidden email]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601077.html To unsubscribe from how postgresql passes the parameter to the function. syntax error near or at "$1"., click here. NAML T -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601106.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] how to pass the function caller's parameter to inside the function. syntax error at or near "$1"
On Wed, Mar 28, 2012 at 11:18 AM, leaf_yxj wrote: > I want to create a function which use to truncate the table specified by the > caller. I very confused how postgresql pass this parameter into the function > : > > as a superuser to execute : > 1) > create or replace function d() returns void as $$ > analyze; > $$ language sql; > > > - this works when i issue select d() > > 2) this doesn't work > > create or replace function v(text) returns void as $$ > analyze $1; > $$ language sql; > > why and how to correct it? > > > I tried another function for insert > > 1) this works. > > create or replace function insert_f(integer) returns void as $$ > insert into t1 values($1); > $$ language sql; > > this works when i issue select insert_f(20); > > 2) this doesn't work. > > create or replace function insert_f(text,integer) returns void as $$ > insert into $1 values($2); > $$ language sql; > > it failed to create the function and give me error : syntax error at or > near "$1". > > Please help. you're not allowed to parameterize table names. workaround is plpgsql's EXECUTE. 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] how postgresql passes the parameter to the function. syntax error near or at "$1".
Hello parameter - $n cannot be used on table name or column name position. Some statements - DROP, VACUUM, ANALYZE doesn't support parameters ever. You have to use dynamic SQL in these cases. CREATE OR REPLACE FUNCTION foo(tablename text, value text) RETURNS void AS $$ BEGIN EXECUTE 'insert into ' || quote_ident(tablename) || ' VALUES($1)' USING value; END; $$ LANGUAGE plpgsql Regards Pavel Stehule 2012/3/28 leaf_yxj : > I want to create a function which use to truncate the table specified by the > caller. I very confused how postgresql pass this parameter into the function > : > > as a superuser to execute : > 1) > create or replace function d() returns void as $$ > analyze; > $$ language sql; > > > - this works when i issue select d() > > 2) this doesn't work > > create or replace function v(text) returns void as $$ > analyze $1; > $$ language sql; > > why and how to correct it? > > > I tried another function for insert > > 1) this works. > > create or replace function insert_f(integer) returns void as $$ > insert into t1 values($1); > $$ language sql; > > this works when i issue select insert_f(20); > > 2) this doesn't work. > > create or replace function insert_f(text,integer) returns void as $$ > insert into $1 values($2); > $$ language sql; > > it failed to create the function and give me error : syntax error at or > near "$1". > > Please help. > > Thanks. > > Regards. > > Grace > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how postgresql passes the parameter to the function. syntax error near or at "$1".
I want to create a function which use to truncate the table specified by the caller. I very confused how postgresql pass this parameter into the function : as a superuser to execute : 1) create or replace function d() returns void as $$ analyze; $$ language sql; - this works when i issue select d() 2) this doesn't work create or replace function v(text) returns void as $$ analyze $1; $$ language sql; why and how to correct it? I tried another function for insert 1) this works. create or replace function insert_f(integer) returns void as $$ insert into t1 values($1); $$ language sql; this works when i issue select insert_f(20); 2) this doesn't work. create or replace function insert_f(text,integer) returns void as $$ insert into $1 values($2); $$ language sql; it failed to create the function and give me error : syntax error at or near "$1". Please help. Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.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] how to pass the function caller's parameter to inside the function. syntax error at or near "$1"
I want to create a function which use to truncate the table specified by the caller. I very confused how postgresql pass this parameter into the function : as a superuser to execute : 1) create or replace function d() returns void as $$ analyze; $$ language sql; - this works when i issue select d() 2) this doesn't work create or replace function v(text) returns void as $$ analyze $1; $$ language sql; why and how to correct it? I tried another function for insert 1) this works. create or replace function insert_f(integer) returns void as $$ insert into t1 values($1); $$ language sql; this works when i issue select insert_f(20); 2) this doesn't work. create or replace function insert_f(text,integer) returns void as $$ insert into $1 values($2); $$ language sql; it failed to create the function and give me error : syntax error at or near "$1". Please help. Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-pass-the-function-caller-s-parameter-to-inside-the-function-syntax-error-at-or-near-1-tp5601045p5601045.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] oracle linux
On 28 Březen 2012, 17:44, Thom Brown wrote:
> On 28 March 2012 16:30, Tom Lane wrote:
>> "Tomas Vondra" writes:
>>> On 28 Březen 2012, 16:38, Gregg Jaskiewicz wrote:
They seem to claim up to 70% speed gain.
Did anyone proved it, tested it - with PostgreSQL in particular ?
>>
>>> I really don't expect such difference just due to switching to a
>>> different
>>> kernel. There's a space for infinite number of tweaks there (using a
>>> different default fs parameters, adding better support for the new
>>> Niagara
>>> T4 CPU not available to RedHat yet etc.).
>>
>> AFAIK, Oracle Linux is still just rebranded RHEL, with some very minimal
>> amount of additional engineering effort put in. It's not likely that
>> they are so much smarter than everybody else who works on Linux that
>> they can find huge across-the-board speedups that nobody else has found.
>
> Reminds me of when Oracle claimed a 70x speed increase in MySQL
> cluster ("Delivers up to 70x More Performance for Complex Queries"),
> and the ability to process a billion queries per minute. Upon closer
> inspection, the tables used in the "billion tables" benchmark were all
> in-memory tables with no joins and distributed across 8 servers. And
> the increases over the previous version weren't fair either because
> the tests were using different hardware *and* one of them was
> virtualised. They also didn't appear to want to disclose any further
> details of the hardware differences.
>
> So basically setting up unrealistic scenarios to get the highest
> hype-making numbers, and ensuring the important context of those
> numbers is in the footnotes somewhere.
Not to mention they actually prohibit independent benchmarking in their
license. They claim they do that to prevent "unfair" benchmarks executed
by people lacking the necessary knowledge, but considering the benchmarks
they publish ...
Tomas
--
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] oracle linux
On 28 March 2012 16:30, Tom Lane wrote:
> "Tomas Vondra" writes:
>> On 28 Březen 2012, 16:38, Gregg Jaskiewicz wrote:
>>> They seem to claim up to 70% speed gain.
>>> Did anyone proved it, tested it - with PostgreSQL in particular ?
>
>> I really don't expect such difference just due to switching to a different
>> kernel. There's a space for infinite number of tweaks there (using a
>> different default fs parameters, adding better support for the new Niagara
>> T4 CPU not available to RedHat yet etc.).
>
> AFAIK, Oracle Linux is still just rebranded RHEL, with some very minimal
> amount of additional engineering effort put in. It's not likely that
> they are so much smarter than everybody else who works on Linux that
> they can find huge across-the-board speedups that nobody else has found.
Reminds me of when Oracle claimed a 70x speed increase in MySQL
cluster ("Delivers up to 70x More Performance for Complex Queries"),
and the ability to process a billion queries per minute. Upon closer
inspection, the tables used in the "billion tables" benchmark were all
in-memory tables with no joins and distributed across 8 servers. And
the increases over the previous version weren't fair either because
the tests were using different hardware *and* one of them was
virtualised. They also didn't appear to want to disclose any further
details of the hardware differences.
So basically setting up unrealistic scenarios to get the highest
hype-making numbers, and ensuring the important context of those
numbers is in the footnotes somewhere.
--
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] what's difference between vacuum analyze and analyze?
On 03/28/2012 08:24 AM, leaf_yxj wrote: I was asked to write a security definer function to make other user can issue vacuum and analyze command? Friends in this community said vacuum doesn't work inside the function or multi command. How about analyze. I know vacuum is something much like oracle shrink which is used to reorganize the space in the disk. Analyze is used to collect the statistic info of the table. Does analyze works in Function or mulit-commands? test=> BEGIN ; BEGIN test=> ANALYZE ; WARNING: skipping "pg_authid" --- only superuser can analyze it WARNING: skipping "pg_database" --- only superuser can analyze it WARNING: skipping "pg_db_role_setting" --- only superuser can analyze it WARNING: skipping "pg_tablespace" --- only superuser can analyze it WARNING: skipping "pg_pltemplate" --- only superuser can analyze it WARNING: skipping "pg_auth_members" --- only superuser can analyze it WARNING: skipping "pg_shdepend" --- only superuser can analyze it WARNING: skipping "pg_shdescription" --- only superuser can analyze it ANALYZE test=> COMMIT ; COMMIT test=> BEGIN ; BEGIN test=> VACUUM ANALYZE ; ERROR: VACUUM cannot run inside a transaction block test=> For details: http://www.postgresql.org/docs/9.1/interactive/sql-vacuum.html http://www.postgresql.org/docs/9.1/interactive/sql-analyze.html Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/what-s-difference-between-vacuum-analyze-and-analyze-tp5600887p5600887.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] oracle linux
"Tomas Vondra" writes: > On 28 BÅezen 2012, 16:38, Gregg Jaskiewicz wrote: >> They seem to claim up to 70% speed gain. >> Did anyone proved it, tested it - with PostgreSQL in particular ? > I really don't expect such difference just due to switching to a different > kernel. There's a space for infinite number of tweaks there (using a > different default fs parameters, adding better support for the new Niagara > T4 CPU not available to RedHat yet etc.). AFAIK, Oracle Linux is still just rebranded RHEL, with some very minimal amount of additional engineering effort put in. It's not likely that they are so much smarter than everybody else who works on Linux that they can find huge across-the-board speedups that nobody else has found. 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] what's difference between vacuum analyze and analyze?
On Thu, Mar 29, 2012 at 2:24 AM, leaf_yxj wrote: > I was asked to write a security definer function to make other user can issue > vacuum and analyze command? Friends in this community said vacuum doesn't > work inside the function or multi command. How about analyze. I know vacuum > is something much like oracle shrink which is used to reorganize the space > in the disk. Analyze is used to collect the statistic info of the table. > Does analyze works in Function or mulit-commands? There's an easy way to find out. Try it, and see if you get an error back! http://www.catb.org/~esr/faqs/smart-questions.html You may find that it isn't possible. If so, you may want to consider a non-SQL solution; for instance, a shell script that invokes vacuum/analyze. Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what's difference between vacuum analyze and analyze?
I was asked to write a security definer function to make other user can issue vacuum and analyze command? Friends in this community said vacuum doesn't work inside the function or multi command. How about analyze. I know vacuum is something much like oracle shrink which is used to reorganize the space in the disk. Analyze is used to collect the statistic info of the table. Does analyze works in Function or mulit-commands? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/what-s-difference-between-vacuum-analyze-and-analyze-tp5600887p5600887.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] oracle linux
On 28 Březen 2012, 16:38, Gregg Jaskiewicz wrote: > They seem to claim up to 70% speed gain. > Did anyone proved it, tested it - with PostgreSQL in particular ? They do claim a lot of things, and most of the time it's along the lines "Let's take this very specific case, let's assume these rather unusual facts, let's run the benchmark on a slightly different hardware. And then we'll choose the best of the results." I've noticed that claim too (actually they claim 75%) and I've been looking for the benchmark at http://www.oracle.com/us/solutions/performance-scalability/index.html but no luck :-( I really don't expect such difference just due to switching to a different kernel. There's a space for infinite number of tweaks there (using a different default fs parameters, adding better support for the new Niagara T4 CPU not available to RedHat yet etc.). > They seem to run the same way as RHEL do, ie - you can download it for > free, but pay for repo access. (thus updates). Well, and they can change that any time they want ... Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] oracle linux
They seem to claim up to 70% speed gain. Did anyone proved it, tested it - with PostgreSQL in particular ? They seem to run the same way as RHEL do, ie - you can download it for free, but pay for repo access. (thus updates). -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query regarding submission on To Do item for psql client "psql : Allow processing of multiple -f (file) options "
Hi,Would like to submit patch on this TO Do list item which deals with psql client, "psql : Allow processing of multiple -f (file) options ".The code base which I am working on is from postgres 9.1.3 release.But when I diff the code base from git repository, the changes are far different from the postgres 9.1.3 release source code directory.Can I submit the patch(created out of git diff) w.r.t my working directory associated with git after pulling the changes to my working directory, this changes are done and tested against postgres 9.1.3 release source code directory.Shall I go ahead and submit patch for it to pgsql-hack...@postgresql.org.Please advice on the way to go ahead.Thanks,Vikash=-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
Re: [GENERAL] Multiple Slave Failover with PITR
Ken Brush wrote: > I notice that the documentation at: > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial > > Doesn't contain steps in a Multiple Slave setup for re-establishing > them after a slave has become the new master. > > Based on the documentation, here are the most fail-proof steps I came up with: > > 1. Master dies :( > 2. Touch the trigger file on the most caught up slave. > 3. Slave is now the new master :) > 4. use pg_basebackup or other binary replication trick (rsync, tar > over ssh, etc...) to bring the other slaves up to speed with the new > master. > 5. start the other slaves pointing to the new master. > > But, that can take time (about 1-2 hours) with my medium sized DB > (580GB currently). > > After testing a few different ideas that I gleaned from posts on the > mail list, I came up with this alternative method: > > 1. Master dies :( > 2. Touch the trigger file on the most caught up slave > 3. Slave is now the new master. > 4. On the other slaves do the following: > 5. Shutdown postgres on the slave > 6. Delete every file in /data/pgsql/data/pg_xlog > 7. Modify the recovery.conf file to point to the new master and > include the line "recovery_target_timeline='latest'" > 8. Copy the history file from the new master to the slave (it's the > most recent #.history file in the xlog directory) > 9. Startup postgres on the slave and watch it sync up to the new > master (about 1-5 minutes usually) > > My question is this. Is the alternative method adequate? I tested it a > bit and couldn't find any problems with data loss or inconsistency. That sounds like it should work fine. Yours, Laurenz Albe -- 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] user get notification when postgresql database updated
On 28/03/12 12:40, Albert wrote: I am using javaScript app and PostgreSQL database. I have car_alert as a table contains id (FK of cars table) and userid (FK of users table) each car has a status ( status column in cars table ). car_alert is updating and having new records (car id and userid ) every time user choose car to be alerted about. my database information is updating every 10 min and the user should get notification message every time status for its car is changed. ( of course every user has choosed his interested cars and inserted them to the car_alert table by car id ) . what is the best method to do that ? Depends on what you mean by "javaScript app" in this context - browser-based or standalone? If the latter, the NOTIFY/LISTEN mechanism may help, see http://www.postgresql.org/docs/9.1/static/sql-notify.html, and if it's in the browser, might as well just poll the server every 10 minutes, that's unlikely to put much load on the server or network and there's not much point doing it more often if the updates only happen every 10 minutes anyway. cheers, Tom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] user get notification when postgresql database updated
I am using javaScript app and PostgreSQL database. I have car_alert as a table contains id (FK of cars table) and userid (FK of users table) each car has a status ( status column in cars table ). car_alert is updating and having new records (car id and userid ) every time user choose car to be alerted about. my database information is updating every 10 min and the user should get notification message every time status for its car is changed. ( of course every user has choosed his interested cars and inserted them to the car_alert table by car id ) . what is the best method to do that ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/user-get-notification-when-postgresql-database-updated-tp5600187p5600187.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] How to tell if server is in backup mode?
Hi, I can start and stop backup mode with pg_start_backup() and pg_stop_backup(). Is there any function like pg_is_in_backup() to tell if the mode has been enabled? (I can't find anything in the 9.1 docs) Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
