Re: [GENERAL] postgresql standby using pg_archivecleanup don't work

[Simon Riggs]

On Fri, Apr 27, 2012 at 7:14 AM, leo xu  wrote:

> i have one parimary ,two standby. one standby using stream replication

pg_archivecleanup doesn't work with more than one standby feeding from
a single archive

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

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

Re: [GENERAL] problems after restoring from a pg_basebackup

[Albe Laurenz]

Lonni J Friedman wrote:
> I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
> matters) system.  I noticed the existence of pg_basebackup starting in
> 9.1, and figured I'd try it out and see if it would simplify our
> backup & management processes.
> 
> $ pg_basebackup -P -v -D /tmp/backup -x -Ft -z -U postgres
> xlog start point: C6/6420
> 135733616/135733616 kB (100%), 1/1 tablespace
> xlog end point: C6/64A0
> pg_basebackup: base backup completed
> 
> So after running through this, I tried to use (restore) the backup
> that was generated.  While everything appears to be working ok from a
> functional perspective, in the server log I saw the following:
> ##
> LOG:  creating missing WAL directory "pg_xlog/archive_status"
> LOG:  database system was not properly shut down; automatic recovery in 
> progress
> LOG:  redo starts at C6/6678
> LOG:  could not open file "pg_xlog/000100C60067" (log file
> 198, segment 103): No such file or directory
> LOG:  redo done at C6/66A0
> FATAL:  the database system is starting up
> LOG:  autovacuum launcher started
> LOG:  database system is ready to accept connections
> #
> 
> Just to be clear, here's what I did after pg_basebackup had completed
> successfully:
> 0) shutdown postgresql gracefully, and verified that it was fully shutdown
> 1) moved $PGDATA to $PGDATA.old
> 2) created $PGDATA as postgres user
> 3) extracted the basebackup tarball as postgres user
> cd $PGDATA && tar xzvpf /tmp/backup/base.tar.gz
> 4) started postgresql up
> 
> I would have expected that I wouldn't have gotten the 'not properly
> shutdown' warning, or the 'could not open file' warning by following
> this process.  Am I doing something wrong?

From what you quote it looks like everything went well and as expected.

An online backup is not a consistent state of the database, so WAL
files will have to be applied to recover the database.  See
http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-PITR-RECOVERY

Your method worked because you used the -x flag to copy WAL files
along with the backup.  You were lucky because all the necessary WAL
files were still there (see the note in pg_basebackup's
documentation).

If there is activity on the database while you take your backup and
wal_keep_segments is not high enough, you method will not work.
In general, you should also copy the archived WAL files and follow
the procedure in the above link to restore the database.

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] postgresql log indicate too many execute S_2:COMMIT

[Albe Laurenz]

leo xu wrote:
> I test postgresql database performance recently,i set
> log_min_duration_statement=1000;
> i monitor pg_log file,i find too many execue S_2:commit
> 10.0.44.21:30170:2012-04-28 11:38:46.340 CSTLOG:  duration: 1050.679
ms
> execute S_2: COMMIT
> 
> 10.0.44.21:30208:2012-04-28 11:38:46.340 CSTLOG:  duration: 1050.576
ms
> execute S_2: COMMIT
> 
> 10.0.44.21:30318:2012-04-28 11:38:46.340 CSTLOG:  duration: 1028.171
ms
> execute S_2: COMMIT
> 
> 
> 
> please explain what meaning,how to cause it ,how to deal with it?thank
you !

It would be helpful to know *how* you test performance.
Also, the PostgreSQL version, operating system, software and hardware
configuration might be interesting.

If COMMIT takes a long time, my first guess would be that it takes a
long time to write the transaction log (the WAL files in pg_xlog) to
disk.

You can try to verify that with a disk performance measuring tool for
your operating system.

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] Question on notifications

[Albe Laurenz]

Alexander Reichstadt wrote:
> Thanks, I had checked the example before but couldn't make sense out
of it in terms of wrapping it in
> Objective-C. I left it in C now and it works fine.
>
> The trigger I am using now looks like this:
> 
> CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
> 
> 
> DECLARE
> 
> 
> BEGIN
> IF ( TG_OP = 'INSERT' ) THEN
> execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid;
> ELSE
> execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid;
> END IF;
> return NULL;
> END;
> 
> 
> $$ LANGUAGE plpgsql;
> 
> it works  if I remove transmission of the OID. If I do transmit the
OID I get
> 
> 
> DETAIL:  The tuple structure of a not-yet-assigned record is
indeterminate.
> CONTEXT:  PL/pgSQL function "notify_trigger" line 1 at EXECUTE
statement
> 
> 
> The trigger is firing above function AFTER the TG_OP took place. The
error is thrown upon insertion.
> Doesn't the record exist given I trigger AFTER and not BEFORE the
operation took palce?

Right, and I cannot reproduce the error you get.

Here's what I do (on PostgreSQL 9.1.3):

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   EXECUTE 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', '''
  || CASE WHEN TG_OP = 'INSERT' THEN NEW.oid ELSE OLD.oid END ||
;
   RETURN NEW;
END$$;

Observe that the second argument to NOTIFY is a string.

CREATE TABLE t1(val text, PRIMARY KEY (oid)) WITH OIDS;

CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1
   FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

INSERT INTO t1 (val) VALUES ('test');
UPDATE t1 SET val=NULL;
DELETE FROM t1;

A second session subscribed to the events gets:

Asynchronous notification "t1_insert" with payload "46728" received from
server process with PID 18687.
Asynchronous notification "t1_update" with payload "46728" received from
server process with PID 18687.
Asynchronous notification "t1_delete" with payload "46728" received from
server process with PID 18687.

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] Question on notifications

[Alexander Reichstadt]

Thank you. You don't get the error, because you called FOR EACH ROW EXECUTE, 
which I didn't. I fixed it, it all works now!

Am 30.04.2012 um 14:58 schrieb Albe Laurenz:

> CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1
>   FOR EACH ROW EXECUTE PROCEDURE notify_trigger();



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [GENERAL] QUestion on notifications

[Alexander Reichstadt]

Thanks, I had checked the example before but couldn't make sense out of it in 
terms of wrapping it in Objective-C. I left it in C now and it works fine.

The trigger I am using now looks like this:


CREATE FUNCTION notify_trigger() RETURNS trigger AS $$

DECLARE

BEGIN
IF ( TG_OP = 'INSERT' ) THEN
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid;
ELSE
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid;
END IF;
return NULL;
END;

$$ LANGUAGE plpgsql;

it works  if I remove transmission of the OID. If I do transmit the OID I get 

DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "notify_trigger" line 1 at EXECUTE statement

The trigger is firing above function AFTER the TG_OP took place. The error is 
thrown upon insertion. Doesn't the record exist given I trigger AFTER and not 
BEFORE the operation took palce?


Am 30.04.2012 um 09:53 schrieb Albe Laurenz:

> Alexander Reichstadt wrote:
>> From the documentation I was able to build a trigger firing upon
> deletion of a record a function that
>> delivers tablename_operation as a notification one needs to subscribe
> to. So in terminal I can say
>> LISTEN persons_delete and instantly will receive
>> 
>>  Asynchronous notification "persons_delete" received from server
> process with PID 54790.
>> 
>> if there was a delete. But what I don't fully understand is how to do
> this with PQnotifies. Following
>> the docu I get no notifications even though I subscribe to them after
> successfully connecting to the
>> server the same way I do using terminal.
>> 
>> Googling didn't give me examples I was able to use. Please, can
> someone help?
> 
> Did you look at the example in the documentation?
> http://www.postgresql.org/docs/current/static/libpq-example.html#LIBPQ-E
> XAMPLE-2
> 
> Can you post relevant parts of your code?
> 
> Yours,
> Laurenz Albe

Re: [GENERAL] Question on notifications

[Alexander Reichstadt]

Thanks, I had checked the example before but couldn't make sense out of it in 
terms of wrapping it in Objective-C. I left it in C now and it works fine.

The trigger I am using now looks like this:


CREATE FUNCTION notify_trigger() RETURNS trigger AS $$

DECLARE

BEGIN
IF ( TG_OP = 'INSERT' ) THEN
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || NEW.oid;
ELSE
execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP || ', ' || OLD.oid;
END IF;
return NULL;
END;

$$ LANGUAGE plpgsql;

it works  if I remove transmission of the OID. If I do transmit the OID I get 

DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "notify_trigger" line 1 at EXECUTE statement

The trigger is firing above function AFTER the TG_OP took place. The error is 
thrown upon insertion. Doesn't the record exist given I trigger AFTER and not 
BEFORE the operation took palce?


Am 30.04.2012 um 09:53 schrieb Albe Laurenz:

> Alexander Reichstadt wrote:
>> From the documentation I was able to build a trigger firing upon
> deletion of a record a function that
>> delivers tablename_operation as a notification one needs to subscribe
> to. So in terminal I can say
>> LISTEN persons_delete and instantly will receive
>> 
>>  Asynchronous notification "persons_delete" received from server
> process with PID 54790.
>> 
>> if there was a delete. But what I don't fully understand is how to do
> this with PQnotifies. Following
>> the docu I get no notifications even though I subscribe to them after
> successfully connecting to the
>> server the same way I do using terminal.
>> 
>> Googling didn't give me examples I was able to use. Please, can
> someone help?
> 
> Did you look at the example in the documentation?
> http://www.postgresql.org/docs/current/static/libpq-example.html#LIBPQ-E
> XAMPLE-2
> 
> Can you post relevant parts of your code?
> 
> Yours,
> Laurenz Albe



signature.asc
Description: Message signed with OpenPGP using GPGMail

[GENERAL] Listen and Notify

[Alexander Reichstadt]

Hi,

>From the documentation I was able to build a trigger firing upon deletion of a 
>record a function that delivers tablename_operation as a notification one 
>needs to subscribe to. So in terminal I can say LISTEN persons_delete and 
>instantly will receive

Asynchronous notification "persons_delete" received from server process 
with PID 54790.

if there was a delete. But what I don't fully understand is how to do this with 
PQnotifies. Following the docu I get no notifications even though I subscribe 
to them after successfully connecting to the server the same way I do using 
terminal.

Googling didn't give me examples I was able to use. Please, can someone help?

Thanks
Alex

-- 
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] Question on notifications

[Albe Laurenz]

Alexander Reichstadt wrote:
> From the documentation I was able to build a trigger firing upon
deletion of a record a function that
> delivers tablename_operation as a notification one needs to subscribe
to. So in terminal I can say
> LISTEN persons_delete and instantly will receive
> 
>   Asynchronous notification "persons_delete" received from server
process with PID 54790.
> 
> if there was a delete. But what I don't fully understand is how to do
this with PQnotifies. Following
> the docu I get no notifications even though I subscribe to them after
successfully connecting to the
> server the same way I do using terminal.
> 
> Googling didn't give me examples I was able to use. Please, can
someone help?

Did you look at the example in the documentation?
http://www.postgresql.org/docs/current/static/libpq-example.html#LIBPQ-E
XAMPLE-2

Can you post relevant parts of your code?

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

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

[leaf_yxj]

Hi Guys, I got the support from Greenplum. I will give a feedback to
everybody after I test. Thanks. 

They told me to add in /etc/rc.d. 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5676185.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] How to update a newly added column with sub selects?

[J.V.]

Here are additional details (in addition to the original post) on what I 
am trying to do with a single update statement:


Suppose three tables:
1) person
2) person_home
3) group

1) table:  person   3) table: group
column:person_id  column: group_id
*column: zip_code column: zip_code  ( for each 
row in person, I need to get the zip_code here and put into 
person.zip_code)


2) table:person_home
column: person_home_id
column: person_id
column: group_id




This is not the real example, but demonstrates what I am trying to do.

I want to add a new column "zip_code" to the "person" table, and 
ultimately make it a foreign key to group(zip_code).


So I must:
1. Add the column "zip_code" to "person" with no constraints
2. populate the column (by ultimately looking it up in the "group" 
table with a subselect)
3. add a not null and fk constraint to person(zip_code) referencing 
group(zip_code)



---
Part number 2 is what I am having problem with.

For each person.person_id (for each row), I need to do a sub select to 
get to group_id (in the real example, I have to go through many other 
tables, but this

is the simplest example)

To get the zip_code for person.person_id=1, I would do:
select zip_code from group where group_id= (select group_id from 
PersonHome where person_id=1)
save that in a variable and do an update on that person row with 
the zip_code returned.


---

The person_id is hard coded (or could be substituted by a variable:

To do an update to populate one row, after the new "zip_code" column has 
been added, I would do:


update person set zip_code = (select zip_code from group where 
group_id = (select group_id from person_home where person_id=1))


---
now I *do not* want to:
select person_id from person; <= select all id's from the table at 
once, and put the entire result set into an array
iterate through each element of the array and put the update inside 
a loop substituting each person_id from the array at each iteration,

and updating each row one by one.

Ideally would simply like one single update statement to do it all, but 
not sure if there is a facility for this, or

how it could be done.

thanks for your  help.

J.V.

On 4/28/2012 2:23 PM, David Johnston wrote:

On Apr 27, 2012, at 17:22, "J.V."  wrote:


I need to add a new column to a table (nullable), then populate and then add a 
not null constraint.

The value of the new column is obtained by doing three or more nested 
sub-selects to get the id that should go into this column.  At this point I can 
add a not null and foreign key constraint.

Ideally would like to do this with a single updated statement, but not sure how:

So for example, given a table, I have to select the id from that table, and for 
each id, pull id's from the next table, and from there use that id for the next 
and so on.

select id from table; is the id I am starting with, so this might show

1
2
3
4

update table set new_column_id = (select id2 from join_table2 where 
new_column_id=2);

but I do not want to write a loop and iterate through this stament passing 
1,2,3,4 to the above statement, just a single statement.

Is this possible?

thanks


J.v.


Try an update of this form:

UPDATE table SET col = s.newvalue
FROM ( SELECT id, newvalue FROM ... ) s
WHERE s.id = table.id;

I would expect simple joins to work but if not you can always try WITH 
RECURSIVE instead of a procedural loop.  You give to few details to provide 
more specific help.

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] Listen and Notify

[Tom Lane]

Alexander Reichstadt  writes:
> But what I don't fully understand is how to do this with PQnotifies. 
> Following the docu I get no notifications even though I subscribe to them 
> after successfully connecting to the server the same way I do using terminal.

Maybe you forgot PQconsumeInput?  There's a working example in the
source tree:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/examples/testlibpq2.c

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] Listen and Notify

[Merlin Moncure]

On Mon, Apr 30, 2012 at 1:47 AM, Alexander Reichstadt  wrote:
> Hi,
>
> From the documentation I was able to build a trigger firing upon deletion of 
> a record a function that delivers tablename_operation as a notification one 
> needs to subscribe to. So in terminal I can say LISTEN persons_delete and 
> instantly will receive
>
>        Asynchronous notification "persons_delete" received from server 
> process with PID 54790.
>
> if there was a delete. But what I don't fully understand is how to do this 
> with PQnotifies. Following the docu I get no notifications even though I 
> subscribe to them after successfully connecting to the server the same way I 
> do using terminal.
>
> Googling didn't give me examples I was able to use. Please, can someone help?

I'm suspecting operator error because using PQnotifies is exactly what
psql does.  Maybe you could post a small test program?  Are you using
a mixed case notification name?

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] Test ODBC connection failed. Pleae help me to take a look. Thanks.

[leaf_yxj]

These odbc drivers (psqlodbc-08.02.0400  psqlodbc-08.03.0400 
psqlodbc-09.00.0200 psqlodbc-08.02.0500  psqlodbc-08.04.0200) were
installed. But I can't fine where the odbc.ini. After I set up my .odbc.ini.
And setup the driver and driver manager environment variables, I still can't
get the isql command. It still give me the error message that the isql
command can't be found. Please help. Thanks. Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Test-ODBC-connection-failed-Pleae-help-me-to-take-a-look-Thanks-tp5676587.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] Database upgrading: upgrade server first or client first?

[roy hills]

I'm running a postgresql 8.1 database on a single server which is queried from 
a number of client systems.
Both server and clients are running Debian Linux.

I need to upgrade from 8.1 to 8.3, but it's difficult for me to upgrade 
everything at once. Is it possible to
run different versions of the client and server, and if so is it better to 
upgrade the client first or the server first?

When I say "the client", I mean the psql client and the libpq library (I 
actually use Perl DBI, but I think that ends
up calling functions in libpq).

My gut feeling is that later versions of the client should detect and work with 
earlier versions of the server, so
I should upgrade the clients first. But I can't see anything definitive on this.

Any advice or pointers to FAQ items that I've missed would be appreciated.

  

Re: [GENERAL] Database upgrading: upgrade server first or client first?

[Steve Crawford]

On 04/30/2012 11:34 AM, roy hills wrote:
I'm running a postgresql 8.1 database on a single server which is 
queried from a number of client systems.

Both server and clients are running Debian Linux.

I need to upgrade from 8.1 to 8.3, but it's difficult for me to 
upgrade everything at once. Is it possible to
run different versions of the client and server, and if so is it 
better to upgrade the client first or the server first?


Yes it is.

The advice I've received upon asking this question in the past is 
clients first then server. We are currently running many 9.1 clients 
against some servers awaiting upgrade - the most ancient of which is 
7.4. The only problem (annoyance, really) is that in interactive psql 
sessions, a few of the "\" commands don't work with the old servers due 
to differences in the system tables and similar internal changes between 
versions.


As always, test your setup.

Also, if putting in the effort to upgrade, consider moving to 9.1 to get 
all the current feature and performance benefits.


Cheers,
Steve

Re: [GENERAL] Database upgrading: upgrade server first or client first?

[Andy Colson]

On 4/30/2012 1:34 PM, roy hills wrote:

I'm running a postgresql 8.1 database on a single server which is
queried from a number of client systems.
Both server and clients are running Debian Linux.

I need to upgrade from 8.1 to 8.3, but it's difficult for me to upgrade
everything at once. Is it possible to
run different versions of the client and server, and if so is it better
to upgrade the client first or the server first?

When I say "the client", I mean the psql client and the libpq library (I
actually use Perl DBI, but I think that ends
up calling functions in libpq).

My gut feeling is that later versions of the client should detect and
work with earlier versions of the server, so
I should upgrade the clients first. But I can't see anything definitive
on this.

Any advice or pointers to FAQ items that I've missed would be appreciated.



The client (libpq.dll) is remarkably compatible.  I've used old client's 
on new server's and visa-versa, and only had one problem.


The new 9 server supports an application_name property in the connect 
string, which I have started using, and the old client didnt like it. 
The client side was a really old version, I'm not even sure what it was, 
this is the first time I've updated it in a while.  When I sent out my 
new program I sent along the new PG 9 libpq along with it.



In my case, I have updated the server several times and never updated 
the client till now.


-Andy

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

Re: [GENERAL] Test ODBC connection failed. Pleae help me to take a look. Thanks.

[Scott Marlowe]

On Mon, Apr 30, 2012 at 12:32 PM, leaf_yxj  wrote:
> These odbc drivers (psqlodbc-08.02.0400  psqlodbc-08.03.0400
> psqlodbc-09.00.0200 psqlodbc-08.02.0500  psqlodbc-08.04.0200) were
> installed. But I can't fine where the odbc.ini. After I set up my .odbc.ini.
> And setup the driver and driver manager environment variables, I still can't
> get the isql command. It still give me the error message that the isql
> command can't be found. Please help. Thanks. Grace

you want psql not isql.

-- 
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] Test ODBC connection failed. Pleae help me to take a look. Thanks.

[yxj]

Hi Scott,
 
I amn't farmiliar with isql. Why I said isql is because that I did some 
research. they said something related to isql. No matter it's isql or psql.
 
1)How can I test the odbc function?
2) what's isql, in which circumstances , we use isql.
 
Thanks.
Regards.
 
Grace





At 2012-05-01 03:11:40,"Scott Marlowe"  wrote:
>On Mon, Apr 30, 2012 at 12:32 PM, leaf_yxj  wrote:
>> These odbc drivers (psqlodbc-08.02.0400  psqlodbc-08.03.0400
>> psqlodbc-09.00.0200 psqlodbc-08.02.0500  psqlodbc-08.04.0200) were
>> installed. But I can't fine where the odbc.ini. After I set up my .odbc.ini.
>> And setup the driver and driver manager environment variables, I still can't
>> get the isql command. It still give me the error message that the isql
>> command can't be found. Please help. Thanks. Grace
>
>you want psql not isql.

Re: [GENERAL] Test ODBC connection failed. Pleae help me to take a look. Thanks.

[Chris Curvey]

>you want psql not isql.
On Mon, Apr 30, 2012 at 3:19 PM, yxj  wrote:

> Hi Scott,
>
> I amn't farmiliar with isql. Why I said isql is because that I did some
> research. they said something related to isql. No matter it's isql or psql.
>
> 1)How can I test the odbc function?
> 2) what's isql, in which circumstances , we use isql.
>
> Thanks.
> Regards.
>
> Grace
>

I think Grace is trying to use the "isql" that comes with unixodbc in order
to test her ODBC connection.

Grace, my isql is in /usr/bin/isql, but I'm on Ubuntu, and i think you're
on CentOS.  (try "locate isql")

[GENERAL] Streaming Replication Error

[Andrew Hannon]

Hello,

We were auditing our logs on one of our PG 9.0.6 standby servers that we use 
for nightly snapshotting. The high-level process is:

1. Stop PG
2. Snapshot
3. Start PG

Where "Snapshot" includes several steps to ensure data/filesystem integrity. 
The archive command on the master continues throughout this process, so the 
standby does have all of the log files. When we restart the cluster, we see the 
typical startup message about restoring files from the archive. However, we 
have noticed that occasionally the following occurs:

LOG:  restored log file "00014456007F" from archive
LOG:  restored log file "000144560080" from archive
cp: cannot stat `/ebs-raid0/archive/000144560081': No such file or 
directory
LOG:  unexpected pageaddr 4454/7400 in log file 17494, segment 129, offset 0
cp: cannot stat `/ebs-raid0/archive/000144560081': No such file or 
directory
LOG:  streaming replication successfully connected to primary
FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 
000144560091 has already been removed

LOG:  restored log file "000144560091" from archive
LOG:  restored log file "000144560092" from archive
LOG:  restored log file "000144560093" from archive
…
LOG:  restored log file "000144570092" from archive
cp: cannot stat `/ebs-raid0/archive/000144570093': No such file or 
directory
LOG:  streaming replication successfully connected to primary

--

The concerning bit here is that we receive the FATAL message "requested WAL 
segment 000144560091 has already been removed" after streaming 
replication connects successfully, which seems to trigger an additional 
sequence of log restores.

The questions we have are:

1. Is our data intact? PG eventually starts up, and it seems like once the 
streaming suffers the FATAL error, it falls back to performing log restores.
2. What triggers this error? Too much time between log recovery, streaming 
startup and a low wal_keep_segments value (currently 128)?

Thank you very much,

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

[GENERAL] errors on restoring postgresql binary dump to glusterfs

[Liang Ma]

Hi There,

While trying to restore a ~700GM binary dump by command

pg_restore -d dbdata < sampledbdata-20120327.pgdump

I encountered following errors repeatedly

pg_restore: [archiver (db)] Error from TOC entry 2882463; 2613
10267347 BLOB 10267347 sdmcleod
pg_restore: [archiver (db)] could not execute query: ERROR:
unexpected data beyond EOF in block 500 of relation base/16386/11743
HINT:  This has been seen to occur with buggy kernels; consider
updating your system.
   Command was: SELECT pg_catalog.lo_create('10267347');



pg_restore: [archiver (db)] could not execute query: ERROR:  large
object 10267347 does not exist
   Command was: ALTER LARGE OBJECT 10267347 OWNER TO sdmcleod;


pg_restore: [archiver (db)] Error from TOC entry 2882464; 2613
10267348 BLOB 10267348 sdmcleod
pg_restore: [archiver (db)] could not execute query: ERROR:
unexpected data beyond EOF in block 500 of relation base/16386/11743
HINT:  This has been seen to occur with buggy kernels; consider
updating your system.
   Command was: SELECT pg_catalog.lo_create('10267348');



pg_restore: [archiver (db)] could not execute query: ERROR:  large
object 10267348 does not exist
   Command was: ALTER LARGE OBJECT 10267348 OWNER TO sdmcleod;


..
..


pg_restore: [archiver (db)] Error from TOC entry 53398; 0 16503 TABLE
DATA l1aaux_sci sdmcleod
pg_restore: [archiver (db)] COPY failed for table "l1aaux_sci": ERROR:
 unexpected data beyond EOF in block 9391 of relation base/16386/17043
HINT:  This has been seen to occur with buggy kernels; consider
updating your system.
CONTEXT:  COPY l1aaux_sci, line 319329: "1854661        \N
1.05156717906094999     1378796678.44843268     2012-02-01
07:04:39.5+00        2012-02-01 07:04:38.4484..."
pg_restore: [archiver (db)] Error from TOC entry 53399; 0 16528 TABLE
DATA l1afts_dbl sdmcleod
pg_restore: [archiver (db)] COPY failed for table "l1afts_dbl": ERROR:
 unexpected data beyond EOF in block 10097 of relation
base/16386/17068
HINT:  This has been seen to occur with buggy kernels; consider
updating your system.
CONTEXT:  COPY l1afts_dbl, line 454411: "459755 2012-03-23
05:31:02.185562+00   ace.sr45190     52867958        299     2591429
FTS     1.1.0   1376321941.75799..."


The server runs Ubuntu server 10.04 LTS with postgresql upgraded to
version 9.1.3-1~lucid. The postgresql data directory is located in a
glusterfs mounted directory to a replicated volume vol-2

192.168.244.101:/vol-2
                    5731222400 3041313920 2398779136  56% /mnt/gluster-2

Here is the gluster info for vol-2:

Volume Name: vol-2
Type: Replicate
Status: Started
Number of Bricks: 2
Transport-type: tcp
Bricks:
Brick1: 192.168.244.101:/data/glbrk-2
Brick2: 192.168.244.102:/data/glbrk-2

The version of glusterfs is 3.2.6.

I think this may have someting to do with glusterfs, because when I
restore the same dump to a same ubuntu 10.04 server with postgresql
upgraded to the same 9.1.3-1~lucid located in a local ext4 filesystem,
the pg_restore went well without a single error.

Has anyone seen something similar before?

Thank you in advance.

Liang Ma

-- 
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] Test ODBC connection failed. Pleae help me to take a look. Thanks.

[yxj]

Hi Chris,
 
Actually I only want to test ODBC. But I didn't have any idea to test. We have 
pgsqlodbc installed, but I don't how to test it. Please give me some clue.
 
Thanks.
 
Grace
 
At 2012-05-01 03:54:36,"Chris Curvey"  wrote:
>you want psql not isql.

On Mon, Apr 30, 2012 at 3:19 PM, yxj  wrote:

Hi Scott,
 
I amn't farmiliar with isql. Why I said isql is because that I did some 
research. they said something related to isql. No matter it's isql or psql.
 
1)How can I test the odbc function?
2) what's isql, in which circumstances , we use isql.
 
Thanks.
Regards.
 
Grace



I think Grace is trying to use the "isql" that comes with unixodbc in order to 
test her ODBC connection.


Grace, my isql is in /usr/bin/isql, but I'm on Ubuntu, and i think you're on 
CentOS.  (try "locate isql")

[GENERAL] Re: Test ODBC connection failed. Pleae help me to take a look. Thanks.

[leaf_yxj]

Hi Guys:

I installed this odbc driver :psqlodbc-08.02.0400

and datadirect-x_xx. I want to test this two things work or not. 

Please give me some idea.

Thanks.
Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Test-ODBC-connection-failed-Pleae-help-me-to-take-a-look-Thanks-tp5676587p5677250.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] PL/R install, no pgxs available

[Daniel Cole]

Hi Guys,

Have 9.1.3 running with Postgis 2.0 .  Trying to add PR/R and getting some
errors about my postgres install.  I found some similar messages, but none
with a workable solution for me.  Any thoughts are very appreciated.

I am running* USE_PGXS=1 make* in my PLR directory and it returns:

*Makefile:36: /usr/lib/postgresql/9.1/lib/pgxs/src/makefiles/pgxs.mk: No
such file or directory*
*make: *** No rule to make target
`/usr/lib/postgresql/9.1/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.*

It is correct, I have no pgxs folder under lib, must less the src and the
makefiles.   I have read 35.16 in the 9.1 documentation on building the
makefile for pgxs, but I really don't understand it and not sure it even
relates to what I am doing.  (
http://www.postgresql.org/docs/9.1/static/extend-pgxs.html)  I can't
understand if I am suppose to create those directories and build up the
makefiles myself (I sure hope not), or install some sort of pg dev kit or
what.

*Here is my pg_config:*
BINDIR = /usr/lib/postgresql/9.1/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.1
HTMLDIR = /usr/share/doc/postgresql-doc-9.1
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.1/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/9.1/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.1/man
SHAREDIR = /usr/share/postgresql/9.1
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.1/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-krb5' '--with-gssapi' '--with-openssl' '--with-libxml'
'--with-libxslt' '--with-ldap' '--with-tclconfig=/usr/lib/tcl8.5'
'--with-tkconfig=/usr/lib/tk8.5' '--with-includes=/usr/include/tcl8.5'
'PYTHON=/usr/bin/python' '--mandir=/usr/share/postgresql/9.1/man'
'--docdir=/usr/share/doc/postgresql-doc-9.1'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/9.1'
'--bindir=/usr/lib/postgresql/9.1/bin' '--libdir=/usr/lib/'
'--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/'
'--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety'
'--enable-debug' '--disable-rpath' '--with-ossp-uuid' '--with-gnu-ld'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'CFLAGS=-g
-O2 -fPIC -DLINUX_OOM_ADJ=0 -fPIC -DLINUX_OOM_ADJ=0'
'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed'
'CPPFLAGS='
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5
CFLAGS = -g -O2 -fPIC -DLINUX_OOM_ADJ=0 -fPIC -DLINUX_OOM_ADJ=0 -Wall
-Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement
-Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed
-Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err
-lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm
VERSION = PostgreSQL 9.1.3


Please tell me any ideas you have.  I am at a stand still on this.

Thanks,

Daniel



---
Daniel Cole

Re: [GENERAL] PL/R install, no pgxs available

[John R Pierce]

On 04/30/12 8:04 PM, Daniel Cole wrote:
Have 9.1.3 running with Postgis 2.0 .  Trying to add PR/R and getting 
some errors about my postgres install.  I found some similar messages, 
but none with a workable solution for me.  Any thoughts are very 
appreciated.


I am running/*USE_PGXS=1 make*/ in my PLR directory and it returns:

/Makefile:36: /usr/lib/postgresql/9.1/lib/pgxs/src/makefiles/pgxs.mk 
: No such file or directory/
/make: *** No rule to make target 
`/usr/lib/postgresql/9.1/lib/pgxs/src/makefiles/pgxs.mk 
'.  Stop./


It is correct, I have no pgxs folder under lib, must less the src and 
the makefiles.


if this was a Fedora/Redhat/CentOS YUM install of postgres,

yum install postgresql91-devel





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


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

Re: [GENERAL] PL/R install, no pgxs available

[John R Pierce]

On 04/30/12 8:22 PM, John R Pierce wrote:

On 04/30/12 8:04 PM, Daniel Cole wrote:
Have 9.1.3 running with Postgis 2.0 .  Trying to add PR/R and getting 
some errors about my postgres install.  I found some similar 
messages, but none with a workable solution for me.  Any thoughts are 
very appreciated.


I am running/*USE_PGXS=1 make*/ in my PLR directory and it returns:

/Makefile:36: /usr/lib/postgresql/9.1/lib/pgxs



oops, on the RHEL6 yum install, thats

/usr/lib/postgresql/9.1/lib/pgxs

but as I said, that stuff is all in the -devel package, so maybe on your 
system, its something similar.





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


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

Re: [GENERAL] PL/R install, no pgxs available

[Joe Conway]

On 04/30/2012 06:31 PM, John R Pierce wrote:
>> On 04/30/12 8:04 PM, Daniel Cole wrote:
>>> I am running/*USE_PGXS=1 make*/ in my PLR directory and it returns:
>>>
>>> /Makefile:36: /usr/lib/postgresql/9.1/lib/pgxs
> 
> 
> oops, on the RHEL6 yum install, thats
> 
> /usr/lib/postgresql/9.1/lib/pgxs
> 
> but as I said, that stuff is all in the -devel package, so maybe on your
> system, its something similar.

Agreed -- he is missing the development package. It isn't clear to me
how he installed postgres in the first place nor which distribution
(maybe I missed it though...), but given this I would guess that the R
development package is missing as well (needed for R headers and libR.so
I believe...)

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

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