Re: [BUGS] postgres 9 streaming replication
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
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"
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"
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
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
