Re: [BUGS] postgres 9 streaming replication

2011-03-23 Thread Alex Lai

Khadtare, Sharad wrote:

Hi,

Problem solved after removing trigger entry from recovery.conf file

Thx for help

Regards,
Sharad K 


-Original Message-
From: Fujii Masao [mailto:masao.fu...@gmail.com] 
Sent: Tuesday, January 25, 2011 5:55 PM

To: Khadtare, Sharad
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] postgres 9 streaming replication

On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad  
wrote:
  

Pls find below logfile of standby and recovery.conf in standby data directory.

bash-3.2$ cat logfile
LOG:  database system was interrupted while in recovery at log time 
2011-01-25 05:28:35 EST

HINT:  If this has occurred more than once some data might be corrupted and you 
might need to choose an earlier recovery target.
LOG:  entering standby mode
LOG:  restored log file "00010037" from archive
LOG:  redo starts at 0/3720
LOG:  consistent recovery state reached at 0/3800
LOG:  database system is ready to accept read only connections
cp: cannot stat `/db/postgres/archive/00010038': No 
such file or directory
LOG:  unexpected pageaddr 0/3000 in log file 0, segment 56, offset 
0
cp: cannot stat `/db/postgres/archive/00010038': No 
such file or directory

LOG:  trigger file found: /db/postgres/trigger



The above message means that you created the trigger file and promoted the 
standby server to the master. Since the standby was running as the master, 
walreceiver was not invoked in the standby.

To start replication, you need to create the standby (taking the base backup 
from the master is required) and start it after you
*ensure* that there is no trigger file in the standby.

I hope this helps..

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center

  
I have a same problem trying to get slave replication using 9.0 
streaming replication.

After the solution, I still not able to get through.
First, I scp the whole data directory from master to slave server.
In master server, I added the following lines in pg_hba.conf.

host replication my_user_name slave_ip/32  trust
host all all x.x.x.x/32  trust
Note: my_user_name replaced with my real super user name and slave_ip 
replaced with slave server's ip.


In master server, I also added the following lines in postgresql.conf.

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
listen_addresses='*'

In slave server, I added the following line in postgresql.conf.
hot_standby = on

In slave server, I added the following line in recovery.conf.
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=my_user_name'
trigger_file = '/path_to/pgsql.trigger'
Note: my_user_name replaced with my real super user name and master_ip 
replaced with master server's ip.
 I removed trigger_file from recovery.conf and restart the 
slave.  It still have the same problem.


I then started master and slave servers.
I got the error message from slave pg_log.
...
cp: cannot stat `/path_to/arch_replicate/0001004A001F': No 
such file or directory
cp: cannot stat `/path_to/arch_replicate/0001004A001F': No 
such file or directory
107602011-03-22 09:42:03 EDTFATAL:  could not connect to the primary 
server: FATAL: 
 no pg_hba.conf entry for replication connection from host 
"slave_server_ip", user "my_super_user_name"


I got the error message from master pg_log.
...
1380902011-03-22 09:37:51 EDTLOG:  connection received: 
host=slave_server_ip port=51686
1380902011-03-22 09:37:51 EDTauthenticationFATAL:  no pg_hba.conf entry 
for replication connection from host "slave_server_ip", user 
"my_super_user_name"
1381002011-03-22 09:37:56 EDTLOG:  connection received: 
host=slave_server_ip port=51687
1381002011-03-22 09:37:56 EDTauthenticationFATAL:  no pg_hba.conf entry 
for replication connection from host "slave_server_ip", user 
"my_super_user_name"
1381102011-03-22 09:38:00 EDTLOG:  connection received: 
host=slave_server_ip port=51688
1381102011-03-22 09:38:00 EDTauthenticationFATAL:  no pg_hba.conf entry 
for replication connection from host "slave_server_ip", user 
"my_super_user_name"


I was able to psql connect  from slave to master or from master to slave 
by using.
From slave server, I can connect from slave to master by the following 
command.

psql database_name -f master_ip -p 5432
From master server, I can connect from master to slave by the following 
command.

psql database_name -f slave_ip -p 5432

I have no idea why I keep getting the message:\
 could not connect to the primary server: FATAL: 
 no pg_hba.conf entry for replication connection from host 
"slave_server_ip", user "my_super_user_name"


Your help will be highly appreciated.


Alex Lai

--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 
75

Re: [BUGS] postgres 9 streaming replication

2011-03-25 Thread Alex Lai

Fujii Masao wrote:

On Wed, Mar 23, 2011 at 9:07 PM, Alex Lai  wrote:
  

I have no idea why I keep getting the message:\
 could not connect to the primary server: FATAL:  no pg_hba.conf entry for
replication connection from host "slave_server_ip", user
"my_super_user_name"



Can you connect from slave to master by the following command?

psql "replication=1" -h  -p 5432 -U 

If successful, the settings in recovery.conf would be wrong.
Otherwise, I guess that the setting in pg_hba.conf of the master would
be wrong. Did you reload the configuration files? What happens if you
use "all" keyword instead of specifying the real user name? What happens
if you use "0.0.0.0/0" instead of specifying the slave's ip? You would need
to do trial and error approach.

Regards,

  
After changed to all for -U in pg_hba.conf, it connected without 
problem.  Thanks for your help.


--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 
7515 Mission Drive, 
Suite A100 Lanham, MD 20706 
301-352-4657 (phone) 
301-352-0437 (fax) 
a...@sesda2.com



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


Re: [BUGS] BUG #6426: Complex query runs 10 times longer with "LIMIT 20"

2012-02-01 Thread Alex Lai
7 width=12) (actual time=0.358..2.251 rows=43 loops=1)
  ->  Bitmap Heap Scan on filemeta_l3 b  
(cost=209.87..12077.66 rows=7727 width=12) (actual time=0.262..0.685 
rows=108 loops=1)
Recheck Cond: esdt)::text = 
'ROLPT'::text) AND (starttime >= '2012-01-28 08:14:57'::timestamp 
without time zone) AND (starttime <= '2012-05-07 07:41:2
7'::timestamp without time zone)) OR (((esdt)::text = 'ROLPT'::text) AND 
(endtime >= '2012-01-28 08:14:57'::timestamp without time zone) AND 
(endtime <= '2012-05-07 07:41:27'::timestamp without

time zone)))
Filter: ((source)::text = 
'OMPS-NPP'::text)
->  BitmapOr  (cost=209.87..209.87 
rows=9895 width=0) (actual time=0.223..0.223 rows=0 loops=1)
  ->  Bitmap Index Scan on 
ak_filemeta_l3_esdt_starttime_endtime  (cost=0.00..107.72 rows=4961 
width=0) (actual time=0.127..0.127 rows=106 loops=1)
Index Cond: 
(((esdt)::text = 'ROLPT'::text) AND (starttime >= '2012-01-28 
08:14:57'::timestamp without time zone) AND (starttime <= '2012-05-0

7 07:41:27'::timestamp without time zone))
  ->  Bitmap Index Scan on 
ak_filemeta_l3_esdt_endtime  (cost=0.00..98.29 rows=4934 width=0) 
(actual time=0.093..0.093 rows=108 loops=1)
Index Cond: 
(((esdt)::text = 'ROLPT'::text) AND (endtime >= '2012-01-28 
08:14:57'::timestamp without time zone) AND (endtime <= '2012-05-07 07

:41:27'::timestamp without time zone))
  ->  Index Scan using 
pk_filemeta_archiveset on filemeta_archiveset a  (cost=0.00..5.21 rows=1 
width=4) (actual time=0.011..0.011 rows=0 loops=108)
    Index Cond: ((a.fileid = b.fileid) 
AND (a.archiveset = 61000))
->  Index Scan using pk_filemeta_l3 on filemeta_l3 b  
(cost=0.00..3.37 rows=1 width=14) (actual time=0.015..0.015 rows=1 loops=1)

  Index Cond: (b.fileid = a.fileid)
  ->  Index Scan using ak_filemeta_archiveset_fileid on 
filemeta_archiveset a  (cost=0.00..2.52 rows=6 width=4) (actual 
time=0.012..0.012 rows=1 loops=1)

Index Cond: (a.fileid = b.fileid)
Total runtime: 2.711 ms

Hope this help.


Best Regards,
Alex Lai

--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 
7515 Mission Drive, 
Suite A100 Lanham, MD 20706 
301-352-4657 (phone) 
301-352-0437 (fax) 
a...@sesda2.com



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


Re: [BUGS] BUG #6426: Complex query runs 10 times longer with "LIMIT 20"

2012-02-01 Thread Alex Lai

Andrew Schetinin wrote:

Hi Alex,

Thank you. I played with subqueries, but in this case I did not see a 
good enough improvement.


In my specific case, what I've seen from the query execution plans, is 
that without LIMIT the query uses Hash Joins, but once I add LIMIT, it 
starts using Nested Loop Joins almost everywhere.


Regards,

Andrew

On Wed, Feb 1, 2012 at 9:42 PM, Alex Lai <mailto:a...@sesda2.com>> wrote:


Hi Andrew,
I posted for another post, its may give you a workaround.
I still not fully understand how PG choose execute plan that slow
down so much.

I had the same situation in one of my query.
Use the subquery can speed up almost by 100 times faster.

..

 


Hope this help.


Best Regards,
Alex Lai

-- 
Best regards,



Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100
Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax)
a...@sesda2.com <mailto:a...@sesda2.com>




--
--
Andrew Schetinin

Hi Andrew,

I am interested in see your query.

--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 
7515 Mission Drive, 
Suite A100 Lanham, MD 20706 
301-352-4657 (phone) 
301-352-0437 (fax) 
a...@sesda2.com



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


[BUGS] Unable to handle error in plperl

2013-07-10 Thread Alex Lai

Dear all,

I have a situation.  I am unable to pass control back to the function 
once it hit the "undefined_column" error code.

I am not sure there's a way to return '123' instead exit from the function.

Here is my code

CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
  my $sql = "";
  my $status = "";
  my $r = "";
  $sql = 'SELECT non_exist_column from a_table limit 1';
  eval { spi_exec_query($sql);};
  if ($@) {
 $status = 'invalid: '.$@;
 elog(ERROR, $status);
 return '123';
 } else {
$status = 'valid';
 }
 return $status;
$$ LANGUAGE plperl;

When I run it

select foo();

ERROR: invalid: column "non_exist_column" does not exist at line 6.
CONTEXT:  PL/Perl function "foo"

When I select from the valid column
CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
  my $sql = "";
  my $status = "";
  my $r = "";
  $sql = 'SELECT exist_column from a_table limit 1';
  eval { spi_exec_query($sql);};
  if ($@) {
 $status = 'invalid: '.$@;
 elog(ERROR, $status);
 return '123';
 } else {
$status = 'valid';
 }
 return $status;
$$ LANGUAGE plperl;

When I run it

select foo();

   foo
--
valid
(1 row)

--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
m...@sesda3.com



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