Re: [GENERAL] PGPool in Master-Master mode, is it possible?
I am not familiar with swarm cluster nor repmgr. So I wonder why you wouldn't be able to get to the goal you are trying to achieve without repmgr. Can you please elaborate more? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > I am also interested in this set-up > My idea is to run pgpool in a docker swarm cluster and to have two instances > of pgpool on different nodes (but not in watchdog mode). In this case it is > not possible to give the responsablity to pgpool to do the failover otherwise > both instances would try to do the failover (they don't know about each > other, no watchdog mode). So I have to set failover_command parameter to an > empty string and let repmgr do the automatic failover. I have tested and I > believe it is ok: in case of master failure repmgr does the promote, pgpool > will not do the failover but will constantly try to reconnect to a new master > and as soon as repmgr has done the promotion it is OK. > The important think is that when pgpool starts I have to read the state of > the cluster in the repmgr metadata table (repl_nodes) and generate the > /tmp/pgpool_status file based on that. (I even have to do a promote_node in > the edge case that the master is down and was brought down when pgpool also > was down). > But the inconvenient is that if pgpool fails, let's say because the docker > node of pgpool is stopped, then docker swarm restart the service on another > node but this can take a few seconds. So I would prefer to have multiple > pgpool. I did not have the time right now to test such a scenario but I am > interested to know if it is possible. > Pierre > > On Wednesday, November 15, 2017, 11:02:31 AM GMT+1, Vikas Sharma > <shavi...@gmail.com> wrote: > > Thank you Tatsuo for the Reply, I will post this in list you mentioned. > > By Master-Master, I meant two pgpool servers both active accepting > connections from Application, If one Pgpool Server becomes unlavailable other > still accepting connections. At an Ideal time, both Pgpool instances on each > PgPool server working actively. The database in this case will be 1 master > and 2 slaves, replicated by streaming replication. > > Regards > VS > > On 14 November 2017 at 22:56, Tatsuo Ishii <is...@sraoss.co.jp> wrote: > >> Hello There, >> >> I need to setup two PGPool Servers in Master-Master mode, First thing I >> want to know, is it possible? >> >> I know we can setup 2 PGPool servers in master-slave mode using watchdog. >> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ >> master_slave_3.3/en.html >> >> Could anyone please enlighten me and any workaround for this? >> >> Regards >> VS > >> I need to setup two PGPool Servers in Master-Master mode, First thing I >> want to know, is it possible? >> >> I know we can setup 2 PGPool servers in master-slave mode using watchdog. >> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ >> master_slave_3.3/en.html >> >> Could anyone please enlighten me and any workaround for this? > > This is not an appropriate list. You should go to the Pgpool mailing > list: > > https://www.pgpool.net/ mailman/listinfo/pgpool- general > > Anyway... I am not sure what you mean by "Master-Master mode" but if > that means you want to issue write queries by connecting to any of > Pgpool-II port, it's already possible in the set up you have created. > > Best regards, > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_ en.php > Japanese:http://www.sraoss.co. jp > > > -- 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] PGPool in Master-Master mode, is it possible?
> Hello There, > > I need to setup two PGPool Servers in Master-Master mode, First thing I > want to know, is it possible? > > I know we can setup 2 PGPool servers in master-slave mode using watchdog. > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html > > Could anyone please enlighten me and any workaround for this? > > Regards > VS > I need to setup two PGPool Servers in Master-Master mode, First thing I > want to know, is it possible? > > I know we can setup 2 PGPool servers in master-slave mode using watchdog. > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html > > Could anyone please enlighten me and any workaround for this? This is not an appropriate list. You should go to the Pgpool mailing list: https://www.pgpool.net/mailman/listinfo/pgpool-general Anyway... I am not sure what you mean by "Master-Master mode" but if that means you want to issue write queries by connecting to any of Pgpool-II port, it's already possible in the set up you have created. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGConf.ASIA and VISA
Hi all, The largest PostgreSQL conference in Japan "PGConf.ASIA" will be held in December 4th to 6th this year. http://www.pgconf.asia/EN/2017/ If you are planning to join and you need a visa to enter Japan, please contact me as soon as possible. I am in charge of issuing invitation letters etc. as one of the organizers of the conference. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Postgres logs showing error after replication
> Ok thank you, > > I have promoted slave server as master in postgres streaming replication , > howerver application is working fine after promotion , but getting some error > messages in the postgres log (slave which promoted to master) > > what does it mean? > > >>ERROR: could not obtain lock on row in relation "main_instance" > < 2017-09-05 13:08:45.434 UTC >STATEMENT: SELECT "main_instance"."id", > "main_instance"."uuid", "main_instance"."hostname", > "main_instance"."created", "main_instance"."modified", > "main_instance"."version", "main_instance"."capacity" FROM "main_instance" > LIMIT 1 FOR UPDATE NOWAIT Your session (transction) tried to get a row lock on the row but other session (transaction) has already gotten the row lock on the same row. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Postgres logs showing error after replication
> I am not sure if the sending email is the right way to get the forum help. > Could you please let me know how to open forum query to ask postgres > questions. Sending email to this mailing list regarding your questions is the perfect way to get help. Other than this, there's an IRC channel for PostgreSQL. https://www.postgresql.org/community/irc/ Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] PGpool question
> This may be the wrong list, but I am not sure where it needs to go. I am > trying to set up pgpool, and I keeping on getting this message: > > NOTICE: add node from hostname:"xxx" port:9000 pgpool_port: > rejected. > Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid > 10768: DETAIL: verify the other watchdog node configurations > Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid > 10768: LOCATION: watchdog.c:1481 > > I believe I have mismatched settings for the watchdog configuration, but I > cannot find them. I have debugging turned all the way up, but nothing is > telling me what setting is the problem, Is there any way I can figure out > what specific watchdog setting its complaining about? Muhammad Usama kindly followed the question: The message refers to the configuration mismatch of other_pgpool_* config parameter, The one defined in section 5.14.9. Watchdog servers configurations <http://www.pgpool.net/docs/latest/en/html/runtime-watchdog-config.html> of the documentation. So you need to check the other_pgpool_hostname, other_pgpool_port or other_wd_port config values. If you have further questions, please post to pgpool-gene...@pgpool.net. https://www.pgpool.net/mailman/listinfo/pgpool-general Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Protocol 2 and query parameters support
> On 2017-04-24 16:07:01 -0400, Rader, David wrote: >> As Tom mentioned, it sounds like the issue is that Presto expects to only >> use simple query, not extended query (no server-side prepared statements). >> The JDBC driver supports setting the prepare threshold to 0 to disable >> using server-side prepares. >> >> https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int- >> >> "If threshold is zero, server-side prepare will not be used." > > Note that you can use the extended protocol without server side > prepares... Quite correct. Just setting the prepare threshold to 0 does not prevent PostgreSQL from using extended queries. So the only way not using extended queires with JDBC is setting the protocol version to 2. I wonder why PostgreSQL JDBC does not provide similar API to ODBC... Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Protocol 2 and query parameters support
> Andres Freund <and...@anarazel.de> writes: >> On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote: >>> Some systems (Presto) are still using protocol 2, and I need to understand >>> the scope of changes in the middleware to support protocol 2. > >> Could you provide a reference about presto using v2 protocol? A quick >> search didn't turn anything up. Presto seems a bit too new to rely on >> v2, given how long ago v3 has been introduced. > > There's been moderately serious discussion about dropping v2 support > altogether, so I sure hope there isn't anything in the wild that still > depends on it. > > Also, moving from v2 to v3 per se just isn't very hard, if you aren't > worried about making use of new-in-v3 protocol features. I wonder whether > Konstantin is confusing v2 vs v3 with use of simple vs. extended query > protocol within v3. It seems Presto uses v3 protocol only when ODBC driver is used. If JDBC is used, it is required to set "protocolVersion=2" property of JDBC driver, which means v2 protocol is used I think. https://github.com/treasure-data/prestogres#limitation Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] FATAL: remaining connection slots are reserved for non-replication superuser connections
> Something is using too many connections. > > I may be wrong but I'm unaware of a limit on connections from PHP except > when you are using persistent connections. Since each PHP script is it's > own process, it can create one or more connections. I'd check to be sure > that every PHP script you have is, indeed, using pg_pconnect and not > pg_connect. That missing "p" could be hard to spot. I'm assuming, of > course, that you are sure that your PHP script are the only things that can > connect - no scripts, backups, etc. are consuming connections. You can disable persistent connection feature of pg_pconnect by tweaking php.ini. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] pgpool fail to load balance after database restart
Michael, He already posted the identical question to the Pgpool-II mailing list yesterday. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > On Wed, Oct 12, 2016 at 10:04 AM, Dylan Luong <dylan.lu...@unisa.edu.au> > wrote: >> I am new to pgpool. > > You may want to ask directly on the mailing lists of pgpool, even if > Tatsuo-san or anybody involved in it are looking at this mailing list: > http://pgpool.net/mediawiki/index.php/Mailing_lists > -- > Michael > > > -- > 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] PGConf.Asia and visa
To all who are planning to join PGConf.Asia: http://www.pgconf.asia/EN/ The main conference is scheduled from December 2nd and December 3rd, 2016 (plus developer conference/unconference on December 1st). The registration will be open by the end of this October. If you need a visa to enter Japan, please let me know (is...@sraoss.co.jp). This time SRA OSS, Inc. Japan is responsible for taking care of visa things, because in order to take care of visa, a legal entity is required to issue invitation letters. Obtaining visa may take long time. I recommend to request me as soon as possible. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Event trigger and CREATE/ALTER ROLE/USER
Simple question: Is there any reason for event trigger to not support CREATE/ALTER ROLE/USER? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Uber migrated from Postgres to MySQL
> Yes, the VACUUM truncation is still an issue. But statements are > retryable, just like deadlocks. > > Unfo the truncation logic always kicks in or small tables of less than > 16 blocks. It's more forgiving on bigger tables. Oh, I didn't know that. Thanks for the info. > Maybe we could defer the truncation on the standby in some cases. Do we want to add this to the TODO list? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Uber migrated from Postgres to MySQL
>>> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK >> >> I wonder if their problem could be fixed by using >> hot_standby_feedback. I have encountered similar problem but it seems >> hot_standby_feedback was not any help in this case: >> >> https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp > > There have been various bugs and enhancements over the years, not all > of which were backpatched. The paticular case still does not work with PostgreSQL 9.5.3. On primary: create table t1(i int); insert into t1 values(1),(2),(3); On standby: begin; test=# select version(); version PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit (1 row) select * from t1; i --- 1 2 3 (3 rows) On primary: delete from t1; On standby: select * from t1; i --- (0 rows) On primary: test=# vacuum verbose t1; INFO: vacuuming "public.t1" INFO: "t1": removed 3 row versions in 1 pages INFO: "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "t1": truncated 1 to 0 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM After while on standby: test=# select * from t1; FATAL: terminating connection due to conflict with recovery DETAIL: User was holding a relation lock for too long. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Uber migrated from Postgres to MySQL
> On 27/07/16 18:54, Chris Travers wrote: >> Another one I think they obliquely referred to (in the subtle problems >> section) was the fact that if you have longer-running queries on the >> replica with a lot of updates, you can get funny auto-vacuum-induced >> errors (writes from autovacuum on the master can interrupt queries on >> the slave). BTW if there is interest in what could be done for that, >> something which allows autovacuum to decide how long to wait before >> cleaning up dead tuples would be a great enhancement. > > You mean something like hot_standby_feedback? > > https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK I wonder if their problem could be fixed by using hot_standby_feedback. I have encountered similar problem but it seems hot_standby_feedback was not any help in this case: https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] High Availability
> On 07/20/2016 11:53 AM, dangal wrote: >> >> Dear , I have a question for them , in our work we have an environment >> with >> streaming replication and everything works in the best way , we are >> trying >> to implement high availability and for that we try to use pgpool and >> we >> could not get it to work with md5 authentication > > The errors/problems where? > >> The area of systems my company consulted us that we viability of using >> DRBD >> The truth is that looking at your site only see old references to that >> product , without being pgpool recommending us to make high >> availability? > > With out more information this is throwing darts at a board, so: > > What version(s) of Postgres? > > What version(s) of pgpool? > > What is your setup, number of masters/slaves and layout? > > What is your definition of high availability? Also the original poster might want to take a look at FAQ: http://pgpool.net/mediawiki/index.php/FAQ Especially this: http://pgpool.net/mediawiki/index.php/FAQ#I_created_pool_hba.conf_and_pool_passwd_to_enable_md5_authentication_through_pgpool-II_but_it_does_not_work._Why.3F and this: http://pgpool.net/mediawiki/index.php/FAQ#md5_authentication_does_not_work._Please_help Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] MediaWiki + PostgreSQL is not ready for production?
>> My colleague has been working on making the latest version of >> WordPress >> work with PostgreSQL (there used be a PostgreSQL plugin but it has not >> been maintained and does not work with the latest version of >> WordPress). I imagine there are someone who are attacking the classic >> problem and I wonder as PostgreSQL community, what we can do for this. > > IMHO the way to solve this is not running to catch up with the various > projects using a *SIMPLE* SQL backened, but rather create a new > postgresql project providing a mysql compatibility layer, something > like a server side parser that would translate the mysql commands to > real SQL (PostgreSQL) statements. > Then only one (this) project should be maintained, with no work wasted > in specific client software. > So, PostgreSQL can continue to do what it knows to do best, with no > worries of not being natively compatible with simplistic yet > proprietary systems like mysql. I'm not sure that's the best way ever. Sometimes the approach results in lesser performance of PostgreSQL than MySQL because of the SQL is not optimized for PostgreSQL. For toy project, that's fine. But for serious project it might bring bad performance and users will be disappointed and speak like "PostgreSQL is slower than MySQL". I saw that with Zabbix, for example. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] MediaWiki + PostgreSQL is not ready for production?
> On 7/18/2016 9:14 PM, Tatsuo Ishii wrote: >> I found following comment for using PostgreSQL with MediaWiki: >> >> https://www.mediawiki.org/wiki/Compatibility#Database >> >> "Anything other than MySQL or MariaDB is not recommended for >> production use at this point." >> >> This is a sad and disappointed statement for us. Should we help >> MediaWiki community to enhance this? > > the classic problem with any of these sorts of open source projects, > while you can convert the core system to postgres, there's a huge > community of contributed plugins, and many of these authors have zero > interest in anything but their default database, mysql/mariadb. I ran > into this with Drupal, Wordpress, a couple different forum projects. > Drupal even tried to offer a database API so plugin developers > wouldn't touch SQL directly, but too many ignored it. Yeah, that's a classic problem. The reason why I raise the particular problem was, I hoped situations were better with MediaWiki since it has been used for PostgreSQL official site. But the truth is even MediaWiki is not an exception. My colleague has been working on making the latest version of WordPress work with PostgreSQL (there used be a PostgreSQL plugin but it has not been maintained and does not work with the latest version of WordPress). I imagine there are someone who are attacking the classic problem and I wonder as PostgreSQL community, what we can do for this. This may not be the brightest idea but what about starting with creating wiki page listing such that works? This will help users who want to use PostgreSQL with WordPress, MediaWikim Drupal etc. (and many plugins). Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] MediaWiki + PostgreSQL is not ready for production?
I found following comment for using PostgreSQL with MediaWiki: https://www.mediawiki.org/wiki/Compatibility#Database "Anything other than MySQL or MariaDB is not recommended for production use at this point." This is a sad and disappointed statement for us. Should we help MediaWiki community to enhance this? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] High availability and load balancing ...
> Hi, > > I need to do the setup for High availability function. > Also want to implement load balancing for 02 nodes. > I think PGPool will be require for that. Can I use PGPool without cost. Yes, you can. Pgpool-II is an open source product. > Can I get the basic steps to do this setup? http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html It's a little bit out dated but mostly useful for a configuration using the latest pgpool-II version and PostgreSQL 9.1. If you have further questions regarding pgpool-II, I recommend you to move pgpool-II specific mailing list. http://www.pgpool.net/mailman/listinfo/pgpool-general > Database-- Postgresql 9.1 > OS -- Linux 6 > > > Thanks & Regards, > Sunil N Shinde Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] pgpool-II: cannot use serializable mode in a hot standby
> It should not forward transactions which are requested to be > SERIALIZABLE to standbys. If you just suppress the SET statement > (or substitute REPEATABLE READ), queries in that transaction can > return incorrect results. Yes. Once "SET default_transaction_isolation to serializable" (or its variants) are issued, pgpool-II will redirect all subsequent queries to the primary server until the transaction ends. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] pgpool-II: cannot use serializable mode in a hot standby
> On 04/12/2016 16:50, Adrian Klaver wrote: >> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >>> Hi. >>> >>> We have application which explicitly does >>> set default_transaction_isolation to 'serializable' . >>> It is connected to PostgreSQL master/slave cluster through pgpool-II >>> (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with >>> >>> ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: >>> "default_transaction_isolation" is set to "serializable". HINT: You >>> can >>> use "SET default_transaction_isolation = 'repeatable read'" to change >>> the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: >>> GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: >>> DISCARD ALL >>> >>> It seems pgpool sends these statements to the slave server. Is it >>> pgpool/application misconfiguration or pgpool issue? >>> >> >> I would say the above is coming from Postgres not pgpool: > > I understand. I mean perhaps pgpool shouldn't forward these statements > to slaves. Yeah, PostgreSQL used to accept the command on standbys (at least in 9.0). The restriction was added later on. It woule be nice if you send a bug report to the pgpool-II bug tracker to not forget it. http://pgpool.net/mediawiki/index.php/Bug_tracking_system Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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 flavors
> I had no idea PowerGres was still going. > > You know I threw out an entire box of PowerGres 7.3 CDs when I moved? PowerGres 7.3! So old days... > They were in the back of a closet. Probably I still have a GreatBridge CDs in my house :-) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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 flavors
> On 02/23/2016 07:51 AM, Sherrie Kubis wrote: >> Hello, my first post to the list, thank you for this place to ask >> questions and get help. >> >> Our management has tasked me with devising a plan to migrate our >> existing databases from Oracle to PostgreSQL. I$B!G(Bm researching and >> getting familiar with PostgreSQL before getting a Linux box to start >> learning and staging. I have a long way to go, but it will be fun. >> >> Out of the gate, I can see different PostgreSQL products $(Q#|(B >> PostgreSQL, >> PostgreSQLPlus, EnterpriseDB Advanced Server. > > So here's a quick rundown. I'm sure I'm forgetting some, but here's a > lot of them. I've deliberately omitted PostgreSQL forks/versions > which are no longer maintained or not commercially available. > > Open Source > --- > > PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra > open source tools included in the installer. > > GreenPlum: fork of PostgreSQL 8.2, designed for large-scale big data, > data mining and analytics. > > PostgresXC: beta-quality open source fork designed for small clusters > of transaction-processing, ala Oracle RAC. > > PostgresXL: fork of PostgresXC, more stable, and a bit more oriented > towards data analytics. > > Stado: Version of PostgreSQL with java middleware to do big-data > scale-out. At various times called ExtenDB and GridSQL. > > BigSQL: PostgreSQL+Hadoop for big data scale-out. > > PipelineDB: streaming SQL engine built from PostgreSQL. > > > Closed Source > - > > PostgreSQL Plus Advanced Server/EDB Server: EnterpriseDB's fork of > PostgreSQL which has Oracle compatibility and some other tools (like > xDB replication). Sometimes includes features from future versions of > PostgreSQL > > CitusDB: latest/greatest big data scale-out version of PostgreSQL. > Soon to be open-source. > > Aster: prior generation of PostgreSQL MPP and Map/Reduce scale-out. > > Hadapt: proprietary PostgreSQL+Hadoop fusion. Based on HadoopDB, > which was open source. > > Paraccel: Column-oriented in-memory cluster database built from > PostgreSQL 8.2. > > RedShift: Amazon's fork of Paraccel, available only on AWS as a > service. > > Vertica: Another PostgreSQL-based column store. Unclear on how much > PostgreSQL code it uses, but uses a version of the PostgreSQL protocol > and psql client. > > FAST: Fujitsu's spin of PostgreSQL, optimized for high performance on > high-end hardware. PowerGres: Surviving since 2003. Full compatible with PostgreSQL except "PowerGres plus" which has the transparent database encryption and the redundant WAL. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] pgpool II, streaming replication and HA
I don't know anything about repmgr. I neglect the questions to repmgr. > I'd like to setup HA PostgreSQL streaming replication cluster with two > nodes. The main issue which I'd like to protect from is HW node > failure or reboot. I supposed to use repmgr to control PostgreSQL > cluster and two pgpool instances as load balancers/DB proxies. > > The issue which I still don't understand how to handle is temporary > master failure. Let's say, we have host A (master) and host B > (slave). Now A reboots. Pgpool (or repmgr, doesn't matter) promotes B > to master. Now A comes online (and still considers itself master). How > can Pgpool determine that A host is "bad one" and stop routing queries > to A? There's a file called "pool_status" which records the previous status of the DB servers. So the file records that "A is down, B is up". In your case, when A reboots, the old primary server on A may start as a primary, but from the file pgpool knows that the PostgreSQL on A was down and never automatically regards it online. So any query sent to pgpool will be routed to the new primary running on B, never routed to A. To make the old primary on A usable, you need to shutdown the PostgreSQL (if it's running) and turn it to a new standby connecting to the current primary on B. You can do it by either "online recovery" feature of pgpool or do it manually using pg_basebackup etc. In the latter case, you need to "attach" 'the new standby to make it online by using pcp_attach_node. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Trigger function interface
> On 1/6/16 7:03 PM, Tatsuo Ishii wrote: >> Is it possible to get the parse tree in a C trigger function which is >> invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? > > Yes, it's in fcinfo->flinfo->fn_expr. Thanks for the info. But is this the parse tree for the top level query which involves the trigger? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger function interface
Is it possible to get the parse tree in a C trigger function which is invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes
> I noticed, that 9.5 release notes (beta2) do not mention > commit 7e2a18a9161fee7e67642863f72b51d77d3e996. > > > I think this one should be added. +1. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes
> We don't normally document back-patched patches in as-yet-unreleased > branches, since the point of release notes is to tell you what's new > compared to the previous release, and this item isn't (or won't be). That makes sense. > Also, I doubt that this is of any great concern to the average user. > It's not a scenario that would come up in anything I would call a > supported use-case. If we're to reverse the aforementioned policy, > there are probably quite a few patches that would now need to be > documented as "new in 9.5" and are more significant than this. It's possible that novice admins accidentally remove postmaster.pid (I saw this kind of incidents a few times while supporting customers if my memory serves). Anyway I am happy as long as it's clearly documented in the release notes of the next versions. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] pgpool ssl handshake failure
> Hi, > > I am using pgpool-II version 3.4.3 (tataraboshi). > Where my database is Postgresql 8.4. > > I am trying to configure ssl mode from client and between pgpool and > database it is non-ssl. > I configured as document and now I am getting this in my log: > >> >> *2015-10-13 22:17:58: pid 1857: LOG: new connection received* >> *2015-10-13 22:17:58: pid 1857: DETAIL: connecting host=10.0.0.5 >> port=65326* >> *2015-10-13 22:17:58: pid 1857: LOG: pool_ssl: "SSL_read": "ssl handshake >> failure"* >> *2015-10-13 22:17:58: pid 1857: ERROR: unable to read data from >> frontend**2015-10-13 >> 22:17:58: pid 1857: DETAIL: socket read failed with an error "Success"* > > Please let me know what wrong I am doing. Works for me using psql coming with PostgreSQL 9.4.5 and pgpool-II 3.4.3. (This is Ubuntu 14.04. PostgreSQL and pgpool-II are compiled from the source code). $ psql -p 11000 -h localhost test psql (9.4.5) SSL connection (protocol: TLSv1, cipher: AES256-SHA, bits: 256, compression: off) Type "help" for help. I don't think your old PostgreSQL 8.4 server is related to your problem because you are trying to enable SSL between client and pgpool, not pgpool and PostgreSQL server. However psql coming with PostgreSQL 8.4 might be related to the problem. Why don't you try newer version of psql (more precisely, newer libpq). I assume your SSL setting is perfect. If you are not sure, please take a look at FAQ: http://pgpool.net/mediawiki/index.php/FAQ#How_can_I_set_up_SSL_for_pgpool-II.3F Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Extension to rewrite queries before execution
I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. The context is running a third-party app which issues queries I have no control over. I'd like to intercept a specific query (which has no bind parameters) and either replace the query text with a different text which, for example, swaps out an in list clause to instead be an exists (subquery). Or just wrap the query in a set enable_* =off; or set work_mem=*; before and a reset of it after. Is there anything out there like this? This would be for 9.4. I'm willing to put the query text, and its replacement, directly into the extension source code and compile it, but of course something more flexible would be ideal. I don't know if you are interested in using other than extensions, but you could do it by hacking pgpool-II. It has built-in raw parser with some functions to walk through the tree such as raw_expression_tree_walker(). Also you could do the rewriting only for particular applications (of course only if the application set application name). Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] [BDR] vs pgpool-II v3
For your information, I would like to explain a little bit more about pgpool-II own replication (we call it native replication mode to separate from streaming replication or slony replication). In the pgpool-II's native replication mode, for a start pgpool-II sends the query to master PostgreSQL node, which is the first one in the configuration file. After the PostgreSQL returns response, pgpool-II sends the query to the second PostgreSQL node. This is to avoid deadlock between DB nodes. If you have third or fourth DB node, pgpool-II sends the query concurrently with the second node. So in theory pgpool-II's performance in DML/DDL is no less than 50% comparing with single PostgreSQL regardless number of DB nodes. There's one optimization however. If there's only one concurrent sessions (i.e. the number pgpool-II preforked child process is 1), then pgpool-II sends DML/DDL to all the DB nodes at once because there's no possibilty of deadlock. In this case the DML/DDL performance is equivalent to single PostgreSQL in theory. I remember a user deployed inter continental pgpool-II configuration. If my memory serves, the continentals were Australia and North America. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp The question is specifically about the replication feature mentioned here http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of failing over minimizing downtime. On Thu, Aug 13, 2015 at 12:28 PM, Joshua D. Drake j...@commandprompt.com wrote: On 08/13/2015 08:52 AM, Wayne E. Seguin wrote: BDR is only one in Beta compare to stable options: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling . Not sure why this solution would be chosen. Experience with pgpool is that you only needed to change a port and no SQL changes required on an application side. I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this BDR is async multi master. PGPool is largely just a connection pooler with load balancing (although it has other features). They play different sports. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- ~Wayne Wayne E. Seguin wayneeseg...@gmail.com wayneeseguin on irc.freenode.net http://twitter.com/wayneeseguin/ https://github.com/wayneeseguin/ -- 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] Lots of stuck queries after upgrade to 9.4
Heikki, A-ha, I succeeded to reproduce this now on my laptop, with pgbench! It seems to be important to have a very large number of connections: pgbench -n -c400 -j4 -T600 -P5 That got stuck after a few minutes. I'm using commit_delay=100. Because the original reporter seems to have difficulty to execute your test case immediately, I think I would try your test case. However unfortunately I have not succeeded in reproducing the stuck problem by using 9.4.4. I tend to think about increasing commit_delay and/or -c of pgbench and try it out again. Anything I should try other than this? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Setting up HA postgresql
But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? No. The condition (PostgreSQL failure) will be detected by one of pgpool-II (not watchdog process) and the pgpool-II will disconnect the failed PostgreSQL, then execute failover script (if defined) which will usually promote standby PostgreSQL (if failed PostgreSQL was primary) or just ignore the broken PostgreSQL if it was standby. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 4:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 01:21 PM, William Dunn wrote: That's pretty cool! But the intended use of watchdog is so you can have multiple pgpool-II instances and failover among them (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than failure of Postgres. In the configuration described in that wiki what happens when the DBMS goes down but pgpool-II is fine? The watchdog appears to be monitoring the pgpool-II process, not the postgres/postmaster process. From what I see, it has the ability to execute a command/script based on a failed condition which could include but not be limited to a command to fail over a standby. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] 4B row limit for CLOB tables
I'm not sure whether it's mentioned explicitly, but large objects are referenced by an OID, which is a 32 bit value (and a global resource). Large object is not necessarily referenced by OID since 8.1. You can assign arbitrary 32 bit integers as long as they are unique in the pg_largeobject table. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Does anyone user pg-pool II on real production ? Please help me.
Hi, Hello everybody Does anyone user pg-pool II on real production ? Yes, I have customers who are using pgpool-II. How many slave do you have? and how many size of database ? Mostly one slave, some of them use more than one, up to four or so. I am not sure how big their databases are though. I need config my old retail system to support ~ 1500 user with 1000GB over 4 years. I want to try pgpool-II but don't found real system use it. Here it is: http://www.sraoss.co.jp/case_study/daiichihoki_en.php Also the most recent case study of Gengo (http://gengo.com/) will be available soon in English. They are using PostgreSQL and pgpool-II on AWS. (Japanese case study text is already availble. Please take a look at if you like: http://www.sraoss.co.jp/case_study/gengo.php). My system use direct SQL SELECT query and a lot pg procedure. Can pgpool-II support load balance from SELECT my_procedure() ... Yes. pgpool-II can have a list of load-balancing-possible functions (procedures). Please help me Thanks in advance. Sorry for my English. There is a pgpool-II specific mailing list. I suggest you to move to it. http://www.pgpool.net/mailman/listinfo/pgpool-general Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] High Availability Cluster
On Thu, Nov 27, 2014 at 7:26 PM, Maila Fatticcioni mfatticci...@mbigroup.it wrote: I was wondering if there is another way to set up a complete cluster that would manage in case of failure: * the relocation of the VIP (virtual ip address) * the relocation of the main instance of Postgresql Consider trying pgpool. It has an HA mode (and I think the latest version works using a heartbeat, the older one uses watchdog). No. Just the implementation of watchdog has been changed (more precisely new life check mode added). In recent versions (since 3.3) it uses UDP packet for life check of pgpool, which is pretty much similar to heartbeat. If you are up for a commercial product, you may want to consider using something like EnterpriseDB's failover manager. Commercial support for pgpool-II is provided by the way. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] BDR, 9.4 release schedule, PostgreSQL in Azure, pgPool-II
Hi All :) A few questions from a newbie. 1. Use someone BDR in production (https://wiki.postgresql.org/wiki/BDR_Project)? 2. When ~ is expected to release 9.4? I don't think it's in 9.4. 3. Use someone PostgreSQL in Azure and can give any advice? I have no experience with Azure. So no idea. 4. Do I understand correctly that the closest analog of mysql-proxy - it's pgPool-II. The scheme is very simple - every frontend with the code works with own database's instance, and use another one instance of DB only in case of problems with main (own) DB. I have no experience with mysql-proxy. However quick googling suggests that mysql-proxy is resemble to pgpool-II except that pgpool-II has failover functionality, while mysql-proxy not. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp DB under fairly simple application. The desire to use BDR dictated by scaling in breadth on comfortable on the price / quality instances and not be fooled unreliability of individual VM in Azure (ie, at the minimum, we have two VMs with PostgreSQL in one availability set of Azure). Thanks in advance! P.S. Sorry for my bad English ))) -- Savchuk Taras -- 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] Query Plans on Slaves
Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Query Plans on Slaves
We're still considering this architecture - so I don't have any plans. My question is, does the ANALYZE command, which is only executed on the master, mean that the statistics / plans that are used on the master are ALSO used on the slaves? OR does the slave create it's own plan? The statistics is same on master and slave. Plans are made on master and slave independently. I would anticipate very different plans between the two machines, and different postgresql.conf settings as well. I would *want* different query plans between the two. Is that possible? If you have different postgresql.conf settings on slave, you could get different plans on slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp On Sun, Nov 9, 2014 at 8:50 PM, Tatsuo Ishii is...@postgresql.org wrote: Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Re: Synchronous replication + pgPool: not all transactions immediately visible on standby
If you dislike the PostgreSQL's behavior, you may want to try pgpool-II's native replication mode (set replication_mode = on and master_slave_mode = off). In the mode, pgpool-II does not return response to the client until all PostgreSQL returns a commit response. Thus right after the commit, querying to any PostgreSQL should return committed row immediately. OK, we will try that out. The documentation could a bit more specific on what exactly replication mode means. It seems that this only influences the way pgPool distributes queries, it does not actually turn on any kind of replication, right? With replication mode pgpool-II implicitely sends all write queries to all PostgreSQL servers. The idea is, sending identical query will result in same result. Thus some queries having oid, xid, random() or any object which results in different result among PostgreSQL servers will bring different data in replication mode. So you should be very carefull if you want to use such queries. Queries including time/data datatypes, functions (for example now()) are rewritten by pgpool-II so that it uses local time at pgpool-II to avoid the problem. With the mode, PostgreSQL's streaming replication mode should be turn off by the way. From the flow chart[1] it also seems that this will only properly distribute read-only queries if we turn off auto-commit. Did I understand that correctly? Yes, the chart only explains read queries. Write queries are sent to all PostgreSQL servers as stated above. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Synchronous replication + pgPool: not all transactions immediately visible on standby
PostgreSQL's synchronous replication is actually not synchronous Well, that statement is a bit misleading. What is synchronous with the COMMIT request is that data is persisted on at least two targets before the COMMIT request returns an indication of success. It guarantees that much (which some people complain about because if there is only one synchronous replication target the commit request hangs indefinitely if it, or communications to it, goes down) and no more (because some people expect that it is not just about durability, but also about visibility). There have been many discussions about allowing configuration of broader or less strict guarantees, but for now, you have just the one option. (it's confusing but the naming was developer's decision). There was much discussion at the time, and this was the consensus for an initial implementation. I know what PostgreSQL's synchronous replication does. But, as you saw, still many users expect synchronous replication will do visibility synchronous. I'm a little bit tired of making this kind of explanation to users but that's not users fault, I think. Maybe crash safe replication or some such was more appropriate term, but of course this is just a hindsight. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Synchronous replication + pgPool: not all transactions immediately visible on standby
I think your problem is not relevant to pgpool-II. PostgreSQL's synchronous replication is actually not synchronous (it's confusing but the naming was developer's decision). Primary server sends the committed transaction's WAL record to standby and wait for it is written to the standby's WAL file (and synched to the disk if synchronous_commit = on). Then report to the client the transaction has been committed. That means if you send query on the just committed row to the standby, it may returns an old row because WAL record may replay yet. If you dislike the PostgreSQL's behavior, you may want to try pgpool-II's native replication mode (set replication_mode = on and master_slave_mode = off). In the mode, pgpool-II does not return response to the client until all PostgreSQL returns a commit response. Thus right after the commit, querying to any PostgreSQL should return committed row immediately. Note that there's a small updating delay between PostgreSQL A and B. So there's small window where data on A and B are differ. Postgres-XC (or Postgres-XL which is a fork of Postgres-XC) overcomes the issue by using global transaction management technique. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp Hello, we have a setup with Postgres 9.3.4 running on Ubuntu (don't know the exact version) using streaming replication with a hot standby and pgPool 3.3.3 as a loadbalancer in front of the two Postgres servers. While running automated tests we noticed that despite the fact that replication is set to synchronous not all committed transactions are immediately visible on the standby. The tests are Java programs using JPA (EclipseLink) to access the database. The basic pattern is something like this: * tests opens a new JPA sessions * updates a table * commits the transaction * closes the JPA session * opens a new JPA session (this is where it gets transferred to the slave) * selects the modified data to verify everything * closes the JPA session If the commit and the following select are issued too quickly, the select doesn't see the changes. If we either connect directly to the master to run the tests, or artificially sleep inside the tests (e.g. 100ms) then we can see the results of a previous transaction without problems. All connections use the default isolation level (read committed). We enabled statement logging on the master and the server, and these are the relevant parts Log on the on the master: 2014-09-24 09:13:24.774 CEST LOG: Execute unnamed: SET extra_float_digits = 3 2014-09-24 09:13:24.797 CEST LOG: Execute unnamed: BEGIN 2014-09-24 09:13:24.869 CEST LOG: Execute unnamed: UPDATE SET STATUS = $1 WHERE some_col IN (.) 2014-09-24 09:13:24.869 CEST DETAIL: Parameter: $1 = '2', $2001 = '1999' 2014-09-24 09:13:25.060 CEST LOG: Execute S_1: COMMIT 2014-09-24 09:13:25.120 CEST LOG: Anweisung: DISCARD ALL Log on the slave: 2014-09-24 09:13:25.125 CEST LOG: Execute unnamed: SET extra_float_digits = 3 2014-09-24 09:13:25.131 CEST LOG: Execute unnamed: BEGIN 2014-09-24 09:13:25.133 CEST LOG: Execute unnamed: SELECT ... FROM WHERE ... AND (STATUS = $3) LIMIT $4 OFFSET $5 2014-09-24 09:13:25.133 CEST DETAIL: Parameter: $1 = '1000426553', $2 = '2014-09-24 09:14:18.114', $3 = '2', $4 = '2100', $5 = '0' 2014-09-24 09:13:25.137 CEST LOG: Execute S_1: COMMIT 2014-09-24 09:13:25.138 CEST LOG: Anweisung: DISCARD ALL So the new session on the slave was initiated 65ms after the commit on the master was done. But still the SELECT didn't return anything (the where clause includes the new values for the STATUS column updated in the previous transaction). The IN clause of the UPDATE statement contains 2000 values. If we reduce the number of updated rows (e.g. to 20) things are working fine. Everything sounds as if the replication is configured to be asynchronous, although it isn't Here are some of the configuration settings that I can imagine would be important: postgresql.conf (from the master) wal_level = hot_standby checkpoint_segments = 16 checkpoint_completion_target = 0.9 max_wal_senders = 5 wal_keep_segments = 50 synchronous_standby_names = 'test_slave' The following entries are unchanged from a default configuration (they are still commented out) #fsync = on #synchronous_commit = on #wal_sync_method = fsync #full_page_writes = on #wal_buffers = -1 #wal_writer_delay = 200ms #commit_delay = 0 #commit_siblings = 5 The slave has hot_standby = on hot_standby_feedback = on pgpool.conf num_init_children = 400 max_pool = 2 child_life_time = 300 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 connection_cache = on reset_query_list = 'ABORT; DISCARD ALL
[GENERAL] Oleg's talk in Japan
Hi, On September 11th Oleg Bartunov visited Tokyo to give a keynote talk for a PostgreSQL conference held by SRA OSS, Inc. Japan. There were about 80 attendees and we were very impressed by his talk (consecutive interpreting provided) and the possibility of JSONB. Oleg, thank you for the great presentation! His slide is on the SRA OSS's web site: http://www.sraoss.co.jp/event_seminar/2014/20140911_pg94_schemaless.pdf This is definitely worth to download. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Database block lifecycle
Yesterday I had an interesting discussion with my colleague about shared buffers size for our new server. This machine (is dedicated for db) has got 512GB of RAM and database size is about 80GB, so he assumes that db will never have to read from disk, so there is no point to adjust read ahead setting, because every block gonna be read from RAM. As I've red in Greg Smith book, once a block is changed it will be written to a disk and buffers page is marked as clean, which would mean than changes occur in the same page as before? What if dirty page doesn't have enough space for another row and row has to be written to another page? Is it still occurs in RAM? If that's true all updates of FSM occurs in RAM as well? What about buffers_clean and pg_clog then? Are those maintained completely in RAM as well without direct read from disk at all? As long as they are on shared buffers, they are read from the buffer, not from disk. To be precise, does the path to update and read updated row looks like a or b?: a). clean page (shared buffers) - dirty page (shared buffers) - to disk - read from disk - shared buffers - query b). clean page (shared buffers) - dirty page (shared buffers) - to disk dirty page (shared buffers) - clean page (shared buffers) - query I'm not sure what you exactly mean by a) or b) but both look incorrect for me. A reader can read a page from shared bufferes even if it's dirty. So: clean page (shared buffers) - dirty page (shared buffers) - query will be closer to the reality. Note that dirty page will be written by bgwriter process at different timing. Also note that I completely ignore lock or buffer replacement algorithm. Please read src/backend/storage/buffer/README for more precise information. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] The dangers of streaming across versions of glibc: A cautionary tale
Over time, collation order will vary: there may be fixes needed as more information becomes available about languages; there may be new government or industry standards for the language that require changes; and finally, new characters added to the Unicode Standard will interleave with the previously-defined ones. This means that collations must be carefully versioned. Another idea could be having our own collation data to isolate any changes from outside world. I vaguley recall this had been discussed before. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Pgpool starting problem
Hi all, If I turn use_watchdog = off in the pgpool.conf file, then in spite of the system being configured as master-slave, a socket file is created in /tmp, and everything starts normally with no errors. If, however, I set use_watchdog = on, pgpool will abort on startup with the following errors: 2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed 2014-05-08 14:40:22 ERROR: pid 32893: wd_main error 2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed: No such file or directory It seems pretty consistent doing this, and I can flip use_watchdog on and off and the same behavior repeating. Any ideas? You didn't give any pgpool version, configuration file info, I cannot guess what's going on. There's a detailed installation document here: http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html Despite you follow the instruction you still have problems, you should ask pgpool ML. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Monitoring Pg servers with Microsoft SCOM
I am in the process of deploying Microsoft System Centre Operations Manager and was hoping that somebody had either developed or knew of where I could get hold of a management pack for PostgreSQL. I am not sure whether there is an instrumentation interface into the DB so haven't yet looked at rolling our own. Any comments appreciated. Glen Have you looked into this? http://pg-monz.github.io/pg_monz/index-en.html Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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 recovery failed master after failover
I am evaluating the HA solution of PostgreSQL 9.3. 1. Pacemaker resource agent for PostgreSQL + PostgreSQL sync stream replication 2. PG-Pool + PostgreSQL sync stream replication According to my understanding, above two solution must be done same things to recovery failed master as new slave after fail over: copy the database from new master. Is it necessary operation to recovery failed master? Does it protect the data consistent after fail over? If copy operation is necessary, recovery time very depend on database size. For example, our database size is 10G, we have to wait for a few minutes for copying new master data by network. I'm not in position to speak about the Pacemaker resource agent. I only speak about pgpool-II. The reason why the failed master need to be recovered is, availability, not consistency. If you do not do that, you only have the new master at that point. If it goes down, it's the end of your system. About the 10G database recovery. you do *not* need to wait until all the data copied. You can issue not only read queries but write queries to the system. Any modifications to the database while copying data has been recorded to WAL (or archive log) and will be sent to new slave afterward. The only concern is the additional load to copy data. You could overcome it by affording more higher spec hardwares. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] password-less access, without using pg_hba
On Thu, Feb 6, 2014 at 6:37 PM, David Johnston pol...@yahoo.com wrote: Doubtful. Yeah, that's what I had assumed too. The question is motivated entirely by what I think would make it easier for users. In principle it's not difficult to give people a password (as I do now), but in practice it's a barrier that I'd like to eliminate. +1. I told Amazon's RDS guy in Japan that it is a major pain for PostgreSQL users to not be able to touch pg_hba.conf. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Fully-automatic streaming replication failover when master dies?
I know about PostgresXC, but I thought it is distributed database (similar to shards of mongoDB). [Though if I am correct there could be tables which are shared across different nodes, but that is not the best way of utilizing features of PostgresXC] I think it is not apt to call it synchronous (since there is no replication happening). I thought non eventual consistency type DB can be called synchronous, no? If I am correct, Posgres-XC is definitely synchronous. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Fully-automatic streaming replication failover when master dies?
I don't think pgpool adds the lost node on its own (once the node is live or available again). That's one of my TODOs. If we limit it to the standby nodes, it will be safe. I hope this is added to next major version of pgpool-II. Plus if you have a 3 node replication you need to have your own failover_command (as a shell script) which changes the master node for 2nd secondary when one of the secondary servers decides to be promoted to primary). I hope things will get easy with version 9.4 (I guess in 9.4 one won't have to rebuild a master node from backup. if the wal files are available it will just roll forward). Yes, if you have multiple candicate standbys to be promoted, you should make a discion on this. Pgpool-II cannot guess your will. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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 checksum algo?
Hi, It was good to see you in Japan. PostgreSQL Enterprise Consortium (non profit PostgreSQL related organization in Japan. http://www.pgecons.org) is about to inspect the performance impact of the checksum using High-end PC server (real 80 cores with 2TB memory). What in my mind is using pgbench with custom query (purely SELECT). Is there any recommendations/suggestions in doing that? (The result will be in public of course). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp On Wed, Nov 13, 2013 at 4:39 PM, Michael Paquier michael.paqu...@gmail.com wrote: CRC16 is used. Actually, subsequently another algorithm was introduced - see commit 43e7a668499b8a69a62cc539a0fbe6983384339c . -- Regards, Peter Geoghegan -- 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
Re: [GENERAL] what checksum algo?
Well, off the top of my head I would of course be sure to build Postgres to take advantage of this: * Vectorization of the algorithm requires 32bit x 32bit - 32bit integer * multiplication instruction. As of 2013 the corresponding instruction is * available on x86 SSE4.1 extensions (pmulld) and ARM NEON (vmul.i32). * Vectorization requires a compiler to do the vectorization for us. For recent * GCC versions the flags -msse4.1 -funroll-loops -ftree-vectorize are enough * to achieve vectorization. Unfortunately I have no idea what packagers are currently doing about this. Could you please enlighten me, Devrim? No problem. We will install PostgreSQL from source code anyway. I tried in my local environment, PostgreSQL compiles fine with the addional arguments you gave me and passed regression test (of course pg_regress.c is modified to add initdb -k flag). It also occurs to me that pgbench will be pretty unsympathetic to checksums as compared to a non-checksummed baseline here, because of course as always it uses a uniform distribution, and that's going to literally maximize the amount of verification that must occur. Maybe that's something you're interested in, because you want to characterize the worst case. If the average case is more interesting, you could try applying this patch: https://commitfest.postgresql.org/action/patch_view?id=1240 I don't know if the patch is any good, having not looked at the code, but surely as the original author of pgbench you are eminently qualified to judge this. I think that in general I prefer a uniform distribution, because most often I look to pgbench to satisfy myself that certain types of regressions have not occurred. That's quite a different thing to a representative workload, obviously. Ok, I will look into this when I have enough time. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [HACKERS] Who is pgFoundery administrator?
Hi, I want to submit new project in pgFoundery project. I submitted new project which is WAL archive copy tool with directIO method in pgFoundery homepage 2 weeks ago, but it does not have approved and responded at all:-( Who is pgFoundery administrator or board member now? I would like to send e-mail them. At least, it does not have information and support page in pgFoundery homepage. It's Marc (scra...@hub.org). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hot_standby_feedback
I have a question about hot_standby_feedback parameter. In my understanding, if this parameter is on, a long running transaction on standby will not be canceled even if the transaction conflicts. So I have primary PostgreSQL and standby PostgreSQL running 9.2.4. On primary: create table t1(i int); insert into t1 values(1),(2),(3); On standby: begin; select * from t1; i --- 1 2 3 (3 rows) On primary: delete from t1; On standby: select * from t1; i --- (0 rows) On primary: vacuum verbose t1; INFO: vacuuming public.t1 INFO: t1: removed 3 row versions in 1 pages INFO: t1: found 3 removable, 0 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: t1: truncated 1 to 0 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM As you can see vacuum on the primary removes all the rows in t1. I thought vacuum will not make the page entriely empty because of the effect of hot_standby_feedback. After while, on standby: test=# select * from t1; FATAL: terminating connection due to conflict with recovery DETAIL: User was holding a relation lock for too long. HINT: In a moment you should be able to reconnect to the database and repeat your command. Again, this is not what I expected. Am I missing something? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Replication delay
On Sun, Aug 11, 2013 at 5:51 AM, ascot.m...@gmail.com ascot.m...@gmail.com wrote: Hi, I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica. From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replication lag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-only queries, can you please suggest a way to monitor and failover the read-only query when the replication lag in a replica is more than 5 minutes? I assume that you could use pgpool for that. It has some monitoring features for replication delay and it can do read-only load balancing among several servers. You also shouldn't need to change your application. Have a look at its documentation to make an opinion: http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html The url above is obsoleted. Please visit: http://www.pgpool.net/docs/latest/pgpool-en.html -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reddwarf for PostgreSQL?
Hi, There is an opensource DaaS project called RedDwarf. It seems the project is only for MySQL. Does anybody know if the project will support PostgreSQL in the future? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres_fdw changes schema search path
I'm just wondering why postgres_fdw explicitly sets search_path to pg_catalog. If it does not do that, is there any security risk? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Normal errors codes in serializable transactions
The manual mentions that SERIALIZABLE transactions may abort with error 40001, in which case the client application is supposed to retry the transaction. I've been stress testing an application by issuing lots of concurrent requests, and sure enough, every now and then I get back those 40001 errors. However, sometimes I also get back error 40P01. It seems no ill comes to pass if I also retry those transactions, but since this error code is not explicitly mentioned in the manual, one question arises: which error codes can be considered normal (in the sense it's reasonable for the client to retry) when issuing SERIALIZABLE transactions, and which ones (within the scope of class 40, of course) are to be considered real errors? 40P01 is mentioned in the manual. See A. PostgreSQL Error Codes of Appendixes. In most cases it means that transaction is aborted because PostgreSQL detected deadlock. Grepping source indicates that part of HOT standby code uses the error code as well, I'm not sure what is the situation when the error code is supposed to be generated, however. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Install PostgreSQL 9.2.4 to IBM Power System ppc64
I have two IBM Power System servers, the architecture is ppc64, the PostgreSQL on RHEL6 for IBM Power is version 8.4 but I need to install 9.2.4 as I need to use the PostgreSQL replication features. I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64? or please advise me the steps to compile from the 9.2.4 source. I think there's nothing special with installing PostgreSQL from 9.2.4. source code on Power. Please let me know if you have any problem. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] AWS and postgres issues
While debugging this with a coworker we figured out that pg_ctl was attaching to the tty and then it clicked that we needed to be using '-t' where I was using -T or (neither). Are you sure? I checked the pg_ctl source code and could not find any place attaching to the tty. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] AWS and postgres issues
2013/4/9 Tatsuo Ishii is...@postgresql.org: While debugging this with a coworker we figured out that pg_ctl was attaching to the tty and then it clicked that we needed to be using '-t' where I was using -T or (neither). Are you sure? I checked the pg_ctl source code and could not find any place attaching to the tty. I think he means the ssh options -t and -T Yes, I know. In my understanding, he is saying because pg_ctl attaches to the tty, and ssh should be executed with -t (force ssh to allocate pseudo-tty). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] initdb of pg 9.0.13 fails on pg_authid
Robert drb...@fatalsyntax.com writes: I'm having some trouble installing `9.0.13`. creating template1 database in /opt/pg9013/data/base/1 ... ok initializing pg_authid ... FATAL: wrong number of index expressions Our buildfarm member anchovy has been showing similar failures for awhile, but no others do, which makes me think this is a compiler bug. You might try backing off the -O level as a workaround. The md5sum of my tarball checks out. This was built w/ GCC-4.8.0 on an x86_64 Linux kernel. Nominally the same compiler version on anchovy ... however, Fedora 19's version of 4.8.0 works for me, so apparently only some flavors of 4.8.0 have the issue. Does it matter 32/64 bit? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgpool-general: 1315] Re: [GENERAL] Database connections seemingly hanging
It seems that the root cause was that pgpool acquired the locks in the wrong order. If the resource is called A it seems that pgpool allows child X to acquire A on node1 and at the same time, child Y acquires A on node2. This leaves X wanting A on node2 and Y wanting A on node1. This leaves both children hanging indefinitely. It also leaves both postgres'es blissfully unaware of the deadlock, whereby it escapes postgres'es deadlock detection. That's hard to believe for me. For any query, pgpool sends it to the master node (node1 in your case) first and waits until the node returns response by using select(2) on the socket to PostgreSQL backend. After someting comes from the socket, then pgpool issues to node2. So pgpool never sends query to master node(node1) and node2 concurrently. This is a classical technique to avoid a cross node dead lock situation. If your explation is correct, pgpool easily goes into dead lock situation even by using simple pgbench query. Could you please show me self-contained test case? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pgpool2 load balancing not working
Sorry if this is the wrong list, but I've been stuck for a couple days now. I tried pgpool-general but that list appears to not like me. I'm not getting any posts and my post hasn't shown up in the archives. I am the admin of the list. Please contact me directly if you need help on this. I have a Python/Django app that will require database load balancing at some point in the near future. In the meantime I'm trying to learn to implement pgpool on a local virtual machine setup. I have 4 Ubuntu 12.04 VMs: 192.168.1.80 - pool, pgppool2 installed and accessible 192.168.1.81 - db1 master 192.168.1.82 - db2 slave 192.168.1.83 - db3 slave I have pgpool-II version 3.1.1 and my database servers are running PostgreSQL 9.1. I have my app's db connection pointed to 192.168.1.80: and it works fine. The problem is when I use Apache ab to throw some load at it, none of SELECT queries appear to be balanced. All the load goes to my db1 master. Also, very concerning is the load on the pool server itself, it is really high compared to db1, maybe an average of 8-10 times higher. Meanwhile my db2 and db3 servers have a load of nearly zero, they appear to only be replicating from db1, which isn't very load intensive for my tests with ab. ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' http://192.168.1.17/contacts/ That drives the load on my pool server up to about 2.3. Load on db1 is about 0.4 and load on db2 and db3 is nearly zero. Can someone take a look at my pgpool.conf and see if what I'm doing wrong? http://pastebin.com/raw.php?i=wzBc0aSp I'm starting to think maybe it has something to do with Django wrapping every request in a transaction by default, but when the transaction only has SELECTs, shouldn't that be load balanced just fine? Makes my stomach hurt to think I may have to turn off auto-commit and manually commit transactions all throughout my code :( Still hoping it's a pgpool setup issue, since it's my first time setting it up and all. Yes, your guess is correct. In replication mode, the SELECTs should not be in an explicit transaction if you want them load balanced (if you run pgpool-II in streaming replication mode, the restriction is not applied). Is it possible to turn off auto-start-transaction of Django? Pgpool-II already wraps every write SQL statements in a trasaction. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Streaming replication + pgpool-II tutorial
Probably this is not the right forum. You'd better to subscribe and post to pgpool-general list. http://www.pgpool.net/mailman/listinfo/pgpool-general -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Hi Tatsuo Ishii, I am very beginner at PostgreSQL. I am trying to create fail-over cluster server (with replication and online recover) using pg-pool 3.1. 1. which prerequisite are needed for preparing failover server using pgpool at linux environment (RHEL 6.1). 2. I am not able to configure pgpool.conf file and pool_hba.conf. 3. details information of my server are as follow (I am using 3 servers one for cluster and other 2 for postgresql): cluster server: IP:192.168.1.21 HOST NAME: CLUST.pgsql.local Port: node1 server: IP: 192.168.1.30 HOST NAME: node1.pgsql.local Port: 5432 username: postgres Password: 123qwe dbname: pg_new backend_data_directory = '/var/lib/pgsql/data' node2 server: IP:192.168.1.31 HOST NAME: node2.pgsql.local Port: 5432 username: postgres Password: 123qwe dbname: pg_new backend_data_directory = '/var/lib/pgsql/data' please help me. Thanks Gautam -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-pgpool-II-tutorial-tp3250984p5737033.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
Re: [GENERAL] [BUGS] main log encoding problem
I am thinking about variant of C. Problem with C is, converting from other encoding to UTF-8 is not cheap because it requires huge conversion tables. This may be a serious problem with busy server. Also it is possible some information is lossed while in this conversion. This is because there's no gualntee that there is one-to-one-mapping between UTF-8 and other encodings. Other problem with UTF-8 is, you have to choose *one* locale when using your editor. This may or may not affect handling of string in your editor. My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. -- I believe that postgres has such conversion functions anyway. And they used for data conversion when we have clients (and databases) with different encodings. So if they can be used for data, why not to use them for relatively little amount of log messages? Frontend/Backend encoding conversion only happens when they are different. While conversion for logs *always* happens. A busy database could produce tons of logs (i is not unusual that log all SQLs for auditing purpose). And regarding mule internal encoding - reading about Mule http://www.emacswiki.org/emacs/UnicodeEncoding I found: /In future (probably Emacs 22), Mule will use an internal encoding which is a UTF-8 encoding of a superset of Unicode. / So I still see UTF-8 as a common denominator for all the encodings. I am not aware of any characters absent in Unicode. Can you please provide some examples of these that can results in lossy conversion? You can google by encoding EUC_JP has no equivalent in UTF8 or some such to find such an example. In this case PostgreSQL just throw an error. For frontend/backend encoding conversion this is fine. But what should we do for logs? Apparently we cannot throw an error here. Unification is another problem. Some kanji characters of CJK are unified in Unicode. The idea of unification is, if kanji A in China, B in Japan, C in Korea looks similar unify ABC to D. This is a great space saving:-) The price of this is inablity of round-trip-conversion. You can convert A, B or C to D, but you cannot convert D to A/B/C. BTW, I'm not stick with mule-internal encoding. What we need here is a super encoding which could include any existing encodings without information loss. For this purpose, I think we can even invent a new encoding(maybe something like very first prposal of ISO/IEC 10646?). However, using UTF-8 for this purpose seems to be just a disaster to me. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [BUGS] main log encoding problem
Hello, Implementing any of these isn't trivial - especially making sure messages emitted to stderr from things like segfaults and dynamic linker messages are always correct. Ensuring that the logging collector knows when setlocale() has been called to change the encoding and translation of system messages, handling the different logging output methods, etc - it's going to be fiddly. I have some performance concerns about the transcoding required for (b) or (c), but realistically it's already the norm to convert all the data sent to and from clients. Conversion for logging should not be a significant additional burden. Conversion can be short-circuited out when source and destination encodings are the same for the common case of logging in utf-8 or to a dedicated file. The initial issue was that log file contains messages in different encodings. So transcoding is performed already, but it's not This is not true. Transcoding happens only when PostgreSQL is built with --enable-nls option (default is no nls). consistent and in my opinion this is the main problem. I suspect the eventual choice will be all of the above: - Default to (b) or (c), both have pros and cons. I favour (c) with a - UTF-8 BOM to warn editors, but (b) is nice for people whose DBs are - all in the system locale. As I understand UTF-8 is the default encoding for databases. And even when a database is in the system encoding, translated postgres messages still come in UTF-8 and will go through UTF-8 - System locale conversion within gettext. Again, this is not always true. - Allow (a) for people who have many different DBs in many different - encodings, do high volume logging, and want to avoid conversion - overhead. Let them deal with the mess, just provide an additional % - code for the encoding so they can name their per-DB log files to - indicate the encoding. I think that (a) solution can be an evolvement of the logging mechanism if there will be a need for it. The main issue is just that code needs to be prototyped, cleaned up, and submitted. So far nobody's cared enough to design it, build it, and get it through patch review. I've just foolishly volunteered myself to work on an automated crash-test system for virtual plug-pull testing, so I'm not stepping up. I see you point and I can prepare a prototype if the proposed (c) solution seems reasonable enough and can be accepted. Best regards, Alexander -- Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- 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] [BUGS] main log encoding problem
You can google by encoding EUC_JP has no equivalent in UTF8 or some such to find such an example. In this case PostgreSQL just throw an error. For frontend/backend encoding conversion this is fine. But what should we do for logs? Apparently we cannot throw an error here. Unification is another problem. Some kanji characters of CJK are unified in Unicode. The idea of unification is, if kanji A in China, B in Japan, C in Korea looks similar unify ABC to D. This is a great space saving:-) The price of this is inablity of round-trip-conversion. You can convert A, B or C to D, but you cannot convert D to A/B/C. BTW, I'm not stick with mule-internal encoding. What we need here is a super encoding which could include any existing encodings without information loss. For this purpose, I think we can even invent a new encoding(maybe something like very first prposal of ISO/IEC 10646?). However, using UTF-8 for this purpose seems to be just a disaster to me. Ok, maybe the time of real universal encoding has not yet come. Then we maybe just should add a new parameter log_encoding (UTF-8 by default) to postgresql.conf. And to use this encoding consistently within logging_collector. If this encoding is not available then fall back to 7-bit ASCII. What do you mean by not available? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [BUGS] main log encoding problem
Sorry, it was inaccurate phrase. I mean if the conversion to this encoding is not avaliable. For example, when we have database in EUC_JP and log_encoding set to Latin1. I think that we can even fall back to UTF-8 as we can convert all encodings to it (with some exceptions that you noticed). So, what you wanted to say here is: If the conversion to this encoding is not avaliable then fall back to UTF-8 Am I correct? Also is it possible to completely disable the feature? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [BUGS] main log encoding problem
C. We have one logfile with UTF-8. Pros: Log messages of all our clients can fit in it. We can use any generic editor/viewer to open it. Nothing changes for Linux (and other OSes with UTF-8 encoding). Cons: All the strings written to log file should go through some conversation function. I think that the last solution is the solution. What is your opinion? I am thinking about variant of C. Problem with C is, converting from other encoding to UTF-8 is not cheap because it requires huge conversion tables. This may be a serious problem with busy server. Also it is possible some information is lossed while in this conversion. This is because there's no gualntee that there is one-to-one-mapping between UTF-8 and other encodings. Other problem with UTF-8 is, you have to choose *one* locale when using your editor. This may or may not affect handling of string in your editor. My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [BUGS] main log encoding problem
Tatsuo Ishii is...@postgresql.org writes: My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. Um ... but ... (1) nothing whatsoever can read MULE, except emacs and xemacs. (2) there is more than one version of MULE (emacs versus xemacs, not to mention any possible cross-version discrepancies). (3) from a log volume standpoint, this could be pretty disastrous. I'm not for a write-only solution, which is pretty much what this would be. I'm not sure how long xemacs will survive (the last stable release of xemacs was released in 2009). Anyway, I'm not too worried about your points, since it's easy to convert back from mule-internal code encoded log files to original encoding mixed log file. No information will be lost. Even converting to UTF-8 should be possible. My point is, once the log file is converted to UTF-8, there's no way to convert back to original encoding log file. Probably we treat mule-internal encoded log files as an internal format, and have a utility which does conversion from mule-internal to UTF-8. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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 about source download site.
Hi, I have a small question about the site of PostgreSQL source. http://www.postgresql.org/ftp/source/ Now I can see some directories of PostgreSQL 9.2beta1/2. - v9.2.0beta1 - v9.2.0beta2 - v9.2beta1 - v9.2beta2 It seems to be pointed the same direcotory with each version. - v9.2beta1 = v9.2.0beta1 - v9.2beta2 = v9.2.0beta2 I don't know why it's duplicated. acording to my memory, it's not duplicated when 9.1 has released. anyone knows the reason ? There was a discussion about release versioning: http://archives.postgresql.org/pgsql-hackers/2012-06/msg01100.php I don't know the conclusion though. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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 about load balance
My question was how stable is pgpool, what problems I can expect, and pure curiosity what is the technique for managing large databases. CPU and memory to the second coming or are there other techniques for scattering applications on other servers. --- cut --- I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. You'd better to subscribe pgpool-gene...@pgpool.net list (http://www.pgpool.net/mediawiki/index.php/Mailing_lists) and post this kind of question. There are many people who are serious about pgpool in the list. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pgpool2 master fail problem
Hi all, i try to build a postgresql cluster using postgresql 9.1.3 and pgpool2 3.1.2. I want to setup this cluster as a master/slave with streaming copy. Everything is ok execept the case when the masters fails, and i try to recover it, because the old master recovers as master instead of slave. I have installed pgpool-walrecrunning on both servers but still does not seems to work. any ideea ? I'm not sure I fully understand your complain but I guess you expected the failed master became new standby by just restarting it? That is not allowed with streaming replication system anyway. You need to recreate the failed master as a new standby from base backup. Pgpool provides a convenient way called online recovery to do that. See pgpool manual or tutorials at pgpool web site (http://www.pgpool.net), especially this one: http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.1/index.html -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [ADMIN] PG synchronous replication and unresponsive slave
I am aware of pgpool-II and its features. Just that my requirements are little different. I have a System (PG runs on it) which already has Failover mechanism to another System and I want PG to be part of this cluster and not clustered on its own. Mean, PG has to be running in Master system and in synchronous replication mode with another slave system, but the failover is driven from the higher level and not just on PG's failure. So, whenever PG's slave node is unresponsive, we better let the replication cutoff and run the master system independently. So, we need better mechanism to detect when Master PG's synchronous replication not working as expected or when the slave PG is going unresponsive. If not, master PG is held back by the slave PG and so the whole clustered system is stuck. Hope, I am making some sense here. Let me know if there are easy ways to detect Master PG's replication not working (via libpq would be more preferable). I'm not sure I fully understand your requirement but... From pgpool-II 3.1, it has a switch not to trigger failover and you can use it for avoiding automatic failover of master node. For detecting replication not working case, you can use replication delay feature of pgpool-II. It monitors replication delay between master and standby: if the delay is greater than a threshold, it stopps to send read query to the standby. In case of standby failure (server down etc.) you can use automatic failover as usual. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp thanks, Manoj On 01/17/2012 05:04 PM, Fujii Masao wrote: On Wed, Jan 18, 2012 at 6:37 AM, Manoj Govindassamy ma...@nimblestorage.com wrote: (2) We are not comfortable moving to PGPool just for automatic failback mode on hot-standby failure. Hmm.. my reply might be misleading. What I meant was to use pgpool-II as a clusterware for PostgreSQL built-in replication, not as a replication itself. You can health-check, do failover if necessary and manage the PostgreSQL replication by using pgpool-II. AFAIK pgpool-II has such an operation mode. But you are still not comfortable in using pgpool-II in that way? Regards, -- 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
Re: [GENERAL] Streaming replication failover
I'm in the process of setting up a 9.1-based SR cluster, and I've got a question on how failover is expected to work in the case of multiple slaves. http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html says: Some people choose to use a third server to provide backup for the new primary until the new standby server is recreated, though clearly this complicates the system configuration and operational processes. I think a third server sounds like a swell idea, but I'm unclear how a slave based on the old master can act as a slave for the new master. I thought that once the master switched, you'd get a new wal timeline, and any existing slaves won't follow along with the new timeline. Which is really unfortunate, because it means the only way to have a slave, immediately after a failover, is to build one ASAP and hope you don't have insurmountable problems while that's going on. For a big database, that can take a while. Hopefully I'm missing something? I think you can specify recovery_target_timeline to 'latest' in the standby server's recovery.conf to solve part of your problem. The standby will follow the new primary without restoring whole database from the new standby as long as all logs have transferred before the old primary went down. That means this technique can only be used in case when the primary is scheduled down though. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Some services of pgfoundry down?
It seems web and ssh service on pgfoundry are not available at this moment. Anyone knows why? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] postgis and pgpool
Hi, do you know about any problems with using pgpool and postgis together? I personaly don't know any case study of pgpool and posgis but I guess: 1) You are using pgpool-II native replication mode 2) Some of postgis functions are doing updates/inserts/deletes then you may have problem because of inconsistent data among DB servers. Pgpool-II 3.0 or later can deal with the problem by using black and white function list though. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Pgpool outdated spec
I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your spec is outdated, there is some spec updated or have to make my changes manually? Devrim is in charge of updating the spec file. Devrim? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Failover architecture
liOf course, Is there any easy way to do that? If so, then what happens when pgpool tries forward an INSERT to the master while it's in read-only mode? Assuming read-only mode is a database running in read-only transaction mode(like standby), you will get errors something like this: ERROR: cannot execute INSERT in a read-only transaction (For the record, I'm pretty sure that there isn't any easy or obvious way to make a database read-only, and that we can simulate read-only mode by adding INSERT/UPDATE triggers on each of the four -- yes, only four -- tables in the database, silently ignoring data that's posted. I floated this with the project managers, and they were OK with this idea -- but I wanted to double-check whether this is a viable solution, or if there's an obvious pitfall I'm missing and/or a better way to go about this./li liIf we use master-slave replication, and communication is cut off, does the slave reconnect automatically? I believe that the answer is yes, and that the replication will continue so long Yes, as long as you turn on archive logging *and* keep enough archive log segments. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Book
On Jul 20, 2011, at 18:11, Andrej andrej.gro...@gmail.com wrote: Can anyone recommend PostgreSQL 9.0 High Performance by G. Smith? Yes. +1. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] [ANNOUNCE] PostgreSQL conference China 2011 Announced!
Galy Lee, Congratulations to the huge success of CPUG 2011! The conference was extremely well organized and attenders were very enthusiastic to learn PostgreSQL. And food was very good of course:-) I hope the Chinese PostgreSQL community is growing up taking advantage of the conference. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp PostgreSQL conferenceChina 2011 will be hold in July 15-16 in Guangzhou, China. Though PostgreSQL is enterprise-ready for a long time and the user communities around the world are growing, but there is no formal user group in China, the users is difficult to get support, books and technical material is not sufficient. This conference is an effort to help the users in China to deploy PostgreSQL with less pain, and try to cultivate a PostgreSQL user community in China. The conference covers topics related to PostgreSQL: * Ways in which PostgreSQL is used * Case studies * Performance tuning, database monitoring, disaster recovery * PostgreSQL HA and clustering solutions * PostGis * DWH systems based on PostgreSQL * hacking PostgreSQL code * Migration of production systems from another database * Data processing new technologies(Hadoop, NoSQL etc. ) * PostgreSQL 9.1 and 9.2 features in development Please join us to make this Conference as a milestone PostgreSQL conference in China! This conference is organized by Galy Lee(galylee((at)gmail(dot)com), if you have any question, please contact him . For detail please refer to the conference page: http://wiki.postgresql.org/wiki/Pgconchina2011 -- 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] LPI-Japan to start PostgreSQL certfication
Hi, LPI-Japan, a non-profit distributor of LPIC(Linux Professional Institute Certification) in Japan will start OSS-DB exam from July 1st, 2011. LPI-Japan is known as one of the largest distributor of LPIC in the world(according to LPI-Japan they have distributed 164k LPIC so far). http://www.oss-db.jp/news/press/20110608_04.shtml According to LPI-Japan, OSS-DB will be ready for several open source databases in the future. However the initial version will only support PostgreSQL(!) To develop OSS-DB, many companies, including NEC, Hitachi, Fujitsu, NTT and SRA OSS, have been working with LPI-Japan. Correction to this: Representatives from Fujitsu, Hitach, Miracle Linux, NEC, NEC Soft., NTT, and SRA OSS attended the press announcement event. I hope OSS-DB will significantly contribute to making PostgreSQL more popular in Japan. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] LPI-Japan to start PostgreSQL certfication
Hi, LPI-Japan, a non-profit distributor of LPIC(Linux Professional Institute Certification) in Japan will start OSS-DB exam from July 1st, 2011. LPI-Japan is known as one of the largest distributor of LPIC in the world(according to LPI-Japan they have distributed 164k LPIC so far). http://www.oss-db.jp/news/press/20110608_04.shtml According to LPI-Japan, OSS-DB will be ready for several open source databases in the future. However the initial version will only support PostgreSQL(!) To develop OSS-DB, many companies, including NEC, Hitachi, Fujitsu, NTT and SRA OSS, have been working with LPI-Japan. I hope OSS-DB will significantly contribute to making PostgreSQL more popular in Japan. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool-II 3.0.4 release delayed
Sorry for off topic posting but... Since pgfoundry has been down, the schedule for releasing pgpool-II 3.0.4, to be released today, will be delayed. Sorry for inconvenience. We will start to continue the releasing work as soon as pgfoundry comes back. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Memcached for Database server
How do you handle statements that rely on current_timestamp, random(), etc? What about if their reliance is via a function? Is that just an understood limitation of the cache, that it'll cache even queries that don't really make sense to cache? Probably we should cache the result of a query which containts no functions or a query which only contains immutable functions. Also we should be carefull about views, which may be a result of non immutable functions. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgfoundry news page
Hi, I noticed in the the latest news section on http://pgfoundry.org, no news has been uploaded since September 10, last year. I know at least pgpool-II project has made news after September 10 but It did not appear. I am not in the position speaking for other projects, I suspect news made by other projects were not appeared on the page. It seems this is not the first experience of the trouble. Are there any problems with the software of pgfoundry or with human process? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication + pgpool-II tutorial
Hi, It seems there are some demand to seek how to deal with automated failover while using Streaming replication and Hot standby. I wrote a small tutorial how to implement this by using pgpool-II. Please visit: http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html if you are interested. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Tutorials on high availability Postgresql setup?
Is there any tutorials or detailed instructions on how to set up HA postgresql failover? The documentation (http://www.postgresql.org/docs/9.0/interactive/warm-standby-failover.html) on this topics is pretty scarce. The scenario I'm most interested in is this: 2 servers - a master and a hot standby. All writes are sent to master, reads are split between master and hot standby. 1) If the hot standby goes down, how do I redirect reads to the master? pgpool-II 3.0 will take care of this. 2) If the master fails -how do I automatically promote the standby to master and send all reads/writes to the new master? This is covered by pgpool-II 3.0 as well. -what happens when the old master comes back up? Do I need to so anything to make it catches up to the new master? I recommend to use it a standby. Such a configuration is possible by using pgpool-II 3.0. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Tutorials on high availability Postgresql setup?
The scenario I'm most interested in is this: 2 servers - a master and a hot standby. All writes are sent to master, reads are split between master and hot standby. 1) If the hot standby goes down, how do I redirect reads to the master? pgpool-II 3.0 will take care of this. 2) If the master fails ,A (B ,A (B-how do I automatically promote the standby to master and send all reads/writes to the new master? This is covered by pgpool-II 3.0 as well. ,A (B ,A (B-what happens when the old master comes back up? Do I need to so anything to make it catches up to the new master? I recommend to use it a standby. Such a configuration is possible by using pgpool-II 3.0. -- Oh so I'd still need a proxy such as pgpool-II for HA setup? I was thinking that with the new built-in replication in 9.0 there would be no need to use pgpool-II. PostgreSQL 9.0's replication still lacks automated failover/load balance/query dispatching(send read/write query to primary, send read query to standby). So if you need these, you would want to use pgpool-II or any other proxy solutions. If pgpool is still necessary why not also use it for replication? What would be the advantages of using the 9.0's built-in replication as opposed to pgpool's replication? Each replication solution has its own merit/demerit. For example, if you need synchronous replication, pgpool-II is for you. If you are ok with async, PostgreSQL's replication is quite nice. So it depends on you. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy bmurphy1...@gmail.comwrote: I'm sorry, when I went back over to double check my steps I realized I ran the wrong command. I am *still* having the problem. It appears that the MD5 hashes now match, but it's still failing. I have postgres and pgpool installed in /opt/postgresql, but I have the same problem when I put pool_passwd in /etc and /opt/postgresql/etc. Here is a gdb backtrace from the child process when it sigsegs after I enter the correct password. I'm digging in a little deeper now and I thought this might be useful. Unfortunately the gdb backtrace does not show enough information because of optimization, I guess. Can you take a backtrace with optimization disabled binary? You can obtain this by editing Makefile around line 147. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp (gdb) bt #0 0xefcbf24f in ?? () #1 0x08052ac5 in do_md5 (backend=0xefcbf24f, frontend=0x9dfc5c0, reauth=value optimized out, protoMajor=3) at /usr/include/bits/string3.h:52 #2 0x08052e7b in pool_do_auth (frontend=0x9df97c0, cp=0x9df8fa8) at pool_auth.c:222 #3 0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143 #4 do_child (unix_fd=4, inet_fd=5) at child.c:293 #5 0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=value optimized out) at main.c:1024 #6 0x0804ddef in main (argc=3, argv=0xbfab6754) at main.c:514 -- 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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
I tried the patch file and I still cannot connect. The only other difference is that I've already upgraded our images to Postgres 9.0.0 from rc1. Here is the pgpool debug log: 2010-09-20 19:43:19 DEBUG: pid 1329: I am 1329 accept fd 6 2010-09-20 19:43:19 LOG: pid 1329: connection received: host=127.0.0.1 port=39064 2010-09-20 19:43:19 DEBUG: pid 1329: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-20 19:43:19 DEBUG: pid 1329: new_connection: connecting 0 backend 2010-09-20 19:43:19 DEBUG: pid 1329: pool_ssl: SSL requested but SSL support is not available 2010-09-20 19:43:19 DEBUG: pid 1329: pool_read_message_length: slot: 0 length: 12 2010-09-20 19:43:19 DEBUG: pid 1329: pool_do_auth: auth kind:5 2010-09-20 19:43:19 DEBUG: pid 1329: trying md5 authentication 2010-09-20 19:43:19 DEBUG: pid 1329: DB node id: 0 salt: 89d02c38 2010-09-20 19:43:19 DEBUG: pid 1329: read_password_packet_password: failed to read password packet p 2010-09-20 19:43:19 ERROR: pid 1329: do_md5: read_password_packet failed 2010-09-20 19:43:19 DEBUG: pid 1329: do_md5failed in slot 0 2010-09-20 19:43:21 DEBUG: pid 1350: I am 1350 accept fd 6 2010-09-20 19:43:21 LOG: pid 1350: connection received: host=127.0.0.1 port=39066 2010-09-20 19:43:21 DEBUG: pid 1350: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-20 19:43:21 DEBUG: pid 1350: new_connection: connecting 0 backend 2010-09-20 19:43:21 DEBUG: pid 1350: pool_ssl: SSL requested but SSL support is not available 2010-09-20 19:43:21 DEBUG: pid 1350: pool_read_message_length: slot: 0 length: 12 2010-09-20 19:43:21 DEBUG: pid 1350: pool_do_auth: auth kind:5 2010-09-20 19:43:21 DEBUG: pid 1350: trying md5 authentication 2010-09-20 19:43:21 DEBUG: pid 1350: DB node id: 0 salt: 474a91ef 2010-09-20 19:43:21 DEBUG: pid 1350: send_password_packet: backend does not return R 2010-09-20 19:43:21 DEBUG: pid 1350: do_md5failed in slot 0 I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5 auth. Your log seems to indicate that the password in pool_passwd and the one in pg_shadow are not identical. Can you verify that? The query result: select passwd from pg_shadow where usename = 'test'; must be identical the password in pool_passwd for user test. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
Sorry for delay. I had a trip outside Japan.
Thanks, I should have thought to try that location. :)
I put the pool_passwd file in the expected location (custom build,
/opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test. I'm
still having the same connection problem.
I found nasty bug with pgpool. Please try attached patches.
I think it would be good to add an additional command line parameter to
pgpool to allow overriding the location of that file (the same way the
location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden).
Thanks for the suggestion. I'm thinking about to include your idea in
the next major pgpool version up.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
Index: pool_auth.c
===
RCS file: /cvsroot/pgpool/pgpool-II/pool_auth.c,v
retrieving revision 1.25
diff -c -r1.25 pool_auth.c
*** pool_auth.c 17 Aug 2010 02:22:17 - 1.25
--- pool_auth.c 20 Sep 2010 04:27:36 -
***
*** 5,11
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
*
! * Copyright (c) 2003-2009PgPool Global Development Group
*
* Permission to use, copy, modify, and distribute this software and
* its documentation for any purpose and without fee is hereby
--- 5,11
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
*
! * Copyright (c) 2003-2010PgPool Global Development Group
*
* Permission to use, copy, modify, and distribute this software and
* its documentation for any purpose and without fee is hereby
***
*** 131,136
--- 131,138
authkind = ntohl(authkind);
+ pool_debug(pool_do_auth: auth kind:%d, authkind);
+
/* trust? */
if (authkind == 0)
{
***
*** 833,838
--- 835,848
if (!RAW_MODE NUM_BACKENDS 1)
{
+ /* Read password entry from pool_passwd */
+ pool_passwd = pool_get_passwd(frontend-username);
+ if (!pool_passwd)
+ {
+ pool_debug(do_md5: %s does not exist in pool_passwd,
frontend-username);
+ return -1;
+ }
+
/* master? */
if (IS_MASTER_NODE_ID(backend-db_node_id))
{
***
*** 852,864
}
/* Check the password using my salt + pool_passwd */
- pool_passwd = pool_get_passwd(frontend-username);
- if (!pool_passwd)
- {
- pool_debug(do_md5: %s does not exist in
pool_passwd, frontend-username);
- return -1;
- }
-
pg_md5_encrypt(pool_passwd+strlen(md5), salt,
sizeof(salt), encbuf);
if (strcmp(password, encbuf))
{
--- 862,867
--
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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0. I see references to pool_passwd in the pgpool documentation, but I see nothing indicating *where* this file should exist and how pgpool finds it. I've set my accounts up in pcp.conf, however, I do not believe this is what is expected. Sorry for not enough description about pool_passwd. It's located under the same directory as pgpool.conf. So the default is /usr/local/etc/pool_passwd. You need to create /usr/local/etc/pool_passwd if the uid to run pgpool server does not have the right to create a new file under /usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd owned by the same uid as pgpool. After that you should be able to register your password as decribed in the doc. Authentication / Access Controls -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp When I try to access the database with md5 turned on, I see the following error in my pgpool debug log: read_password_packet_password: failed to read password packet p I can get everything working with trust. postgres log: *LOG: unexpected EOF on client connection* LOG: disconnection: session time: 0:00:00.007 user=test database=test host=10.201.121.204 port=42812 psql output: psql -h 127.0.0.1 -p test test Password for user test: *psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. * pgpool debug log: 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6 2010-09-14 21:38:51 LOG: pid 6633: connection received: host=127.0.0.1 port=48336 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234 *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed to read password packet p 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6 2010-09-14 21:38:53 LOG: pid 6633: connection received: host=127.0.0.1 port=48338 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3 *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by segmentation fault 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by signal 11 *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited pg_hba.conf: local all all trust hostall all 127.0.0.1/32trust hostall all ::1/128 trust hostall all 0.0.0.0/0 md5 pool_hba.conf: local all all trust host all all 0.0.0.0/0 md5 pgpool.conf: listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/srv/pgpool/run' pcp_socket_dir = '/srv/pgpool/run' backend_socket_dir = '/srv/pgpool/run' pcp_timeout = 10 num_init_children = 64 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/srv/pgpool/log' pid_file_name = '/srv/pgpool/run/pgpool.pid' replication_mode = false load_balance_mode = false replication_stop_on_mismatch = false failover_if_affected_tuples_mismatch = false replicate_select = false reset_query_list = 'ABORT; DISCARD ALL' white_function_list = '' black_function_list = 'nextval,setval' print_timestamp = true master_slave_mode = false master_slave_sub_mode = 'slony' delay_threshold = 0 log_standby_delay = 'none' connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' failover_command = '' failback_command = '' fail_over_on_backend_error = true
[GENERAL] pgfoundry news section
Hi, It seems the news section on http://pgfoundry.org wasn't updated for a while. I thought this is automatically done if each project put a news. pgpool-II project put news several times, but it never appeas on the page. Does anybody know who is able to fix this? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wiki on pgfoundry
Hi, Does anybody know if there is Wiki system or something like that on pgfoundry? I want to discuss/record the road maps, TODOs etc. on a wiki. I know there is a Wiki on postgresql.org but I don't think it's for external projects. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool-II 3.0 beta1/pgpoolAdmin 3.0 beta1 released
Pgpool Global Development Group is pleased to announce the availability of pgpool-II 3.0 beta1. In the mean time new version of pgpoolAdmin, the GUI tool for pgpool-II, pgpoolAdmin 3.0 beta1 is released. Users are encouraged to take part in our beta test program. pgpool-II 3.0 beta1/pgpoolAdmin 3.0 beta1 can be down loaded here: http://pgfoundry.org/frs/download.php/2778/pgpool-II-3.0-beta1.tar.gz http://pgfoundry.org/frs/download.php/2777/pgpoolAdmin-3.0-beta1.tar.gz Pgpool-II 3.0 is an important version in its history. The internal structure has been dramatically enhanced to make it more robust and easier to maintenance. Also this version adopts to PostgreSQL 9.0's built-in replication. Many enhancements are added also. So users of PostgreSQL 8.4 or prior version should be able to enjoy pgpool-II 3.0's enhancements as well. Pgpool-II 3.0 beta1 has been tested with PostgreSQL 9.0 beta4 and prior version of PostgreSQL including 7.3. Users of prior version of pgpool-II should be able to upgrade to pgpool-II 3.0 without much pain. Especially pgpool-II 2.3.x users should be able to migrate to 3.0 without changing pgpool.conf(it is recommneded to use new pgpool.conf, though). If you find any imcomaptibilty, please let us know. However be warned that output format of some of pcp command are changed. Please look into the documents. Also you should use pgpoolAdmin 3.0 with pgpool-II 3.0. Prior version of pgpoolAdmin is not compatible with pgpool-II 3.0. Attached are release note of pgpool-II 3.0 and pgpoolAdmin 3.0. Please help us in testing and making pgpool-II 3.0 better! --- This is the first version of pgpool-II 3.0 series. That is, a major version up from 2.2 or 2.3 series. The biggest news is, this version adapts to PostgreSQL 9.0's new feature: Streaming Replication/Hot Standby. Streaming replication can be used as a sub mode of master slave mode. Master slave mode itself heavily enhanced: - SELECTs in explicit transactions can be load balanced - In extended protocol, PARSE/BIND/DESCRIBE messages are sent to the node which execute EXECUTE message, not all node. This will reduce lock contentions. - Auto start of transaction happens only when it needed. - Temporary tables can be used safely. - SELECT which calls functions possibly write to database executes on master(primary) Also many new features are added and major refactoring has been made to the internal structure of pgpoo-II. For example, in replication mode, SELECTs calling functions possibly write to database will not allow to load balance. * New features - Online recovery can be used with master/slave/streaming replication mode(Tatsuo) - New directive delay_threshold is added to monitor replication delay in master/slave/streaming replication mode. If replication delay is too much, SELECTs are not load balanced(Tatsuo) - show pool_status shows replication delay in master/slave/streaming replication mode(Tatsuo) - New directive log_standby_delay is added to control logging of replication delay in master/slave/streaming replication mode(Tatsuo) - Add support for more SHOW commands: pool_nodes, pool_processes, pool_nodes, and pool_version(Guillaume Lelarge) - Backend process id and whether frontend connects to this connection pool or not are added to pcp_proc_info's output(Tatsuo) - Gracefuly detach option is added to pcp_detatch_node. With this option, pcp_detatch_node waits until all frontends disconnected(Tatsuo) - New directive white_function_list and black_function_list are added to register functions those do not or do write to database(Tatsuo) - In master/slave mode, SELECTs to system catalogs executes only on master/primary(Tatsuo) - In master/slave mode, SELECTs to temporary table executes only on master/primary(Tatsuo) - In master/slave mode, write queries outside of explicit transactions no longer trigger to start internal transaction(Tatsuo) - In master/slave mode, SELECTs inside explicit transactions are load balanced(Tatsuo, Kitagawa) - In master/slave mode, commands are no longer sent to all DB nodes. This will prevent unnecessary locking(Tatsuo, Kitagawa) - New command option adds to ignore the status file when starting up(Tatsuo) - Supports PostgreSQL 9.0's new VACUUM syntax(Tatsuo) - New directive failover_if_affected_tuples_mismatch controls the behavior when number of result rows of INSERT/UPDATE/DELETE are
Re: [GENERAL] MySQL versus Postgres
With modern servers often shipping with 72GB of RAM now, that would make shared_buffers set to 18GB. This is an absolutely disastrous setting for PostgreSQL in its current state; I'm seeing servers with that much RAM that suffer enormous problems with a far lower shared_buffers than that in production. I'm working on a doc patch to address this better before 9.0 goes out but I assure you this simple rule of thumb is already nearing its end of life as a good one for big systems. What's the problem with 18GB shared_buffers exactly? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
