Re: [Pgpool-general] :[i] Re: "BEGIN; LOCK TABLE" sends to slave in Master/Slave mode
Any reasons why it's not possible to have pgpool split the multi-statement query by using ";", and send one by one instead of making the client worry about this? Thanks, -Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii > Sent: Friday, October 21, 2011 4:11 AM > To: ad...@bestly.ru > Cc: pgpool-general@pgfoundry.org > Subject: s::[i] Re: [Pgpool-general] "BEGIN; LOCK TABLE" sends to slave > in Master/Slave mode > > > Thank you for your reply! > > > > But I have no way to change the request because the client > proprietary. > > Is it possible in pgpool or modify the query or not to send to slave? > > Help white_function_list or black_function_list? > > No. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > > > В Птн, 21/10/2011 в 15:23 +0900, Tatsuo Ishii пишет: > >> From: Успенский Игорь > >> Subject: [Pgpool-general] "BEGIN; LOCK TABLE" sends to slave in > Master/Slave mode > >> Date: Fri, 21 Oct 2011 09:48:48 +0400 > >> Message-ID: <1319176128.17769.42.ca...@admin.bestly.ru> > >> > >> > Hello! > >> > > >> > I using pgpool2 v3.1 and two PostgreSQL 9.0.4 (Master/Slave with > stream > >> > replication) > >> > > >> > Client send query "BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE;" > and > >> > pgpool sends this query master and slave, after slave print to log > >> > "cannot execute LOCK TABLE during recovery". Why pgpool sends this > query > >> > to slave? How to change this behavior? > >> > >> It's a know limitation of pgpool-II. > >> > >> From: > >> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool- > en.html#restriction > >> > >> > pgpool-II cannot process multi-statement queries. > >> > >> BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE; is recognized as > >> "BEGIN;" statement by pgpool-II and it thinks that it needed to sent > >> to both master and slave. > >> > >> The solution is to avoid multi-statement queries like BEGIN;LOCK > TABLE_1SCONNECT IN EXCLUSIVE MODE; > >> > >> Instead: > >> > >> BEGIN; > >> LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE; > >> -- > >> Tatsuo Ishii > >> SRA OSS, Inc. Japan > >> English: http://www.sraoss.co.jp/index_en.php > >> Japanese: http://www.sraoss.co.jp > > > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
> -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Alan Hodgson > Sent: Thursday, October 06, 2011 5:52 PM > To: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] How to configure pgpool for pool > conexions.. > > On October 6, 2011 02:44:34 PM Matias Israel Malpica Escobar wrote: > > Another question for you guys... > > > > I have succesfully installed and configure pgpool, if i go to pgadmin > > and connect to port of my server i can see the databases and > > everything... but when i try to run the following i get this error: > > > > [root@etc]# psql -d test -U testuser -p > > psql: could not connect to server: Connection refused > > Is the server running locally and accepting > > connections on Unix domain socket "/tmp/.s.PGSQL."? > > [root@ etc]# > > What is socket_dir set to in pgpool.conf? If it isn't /tmp, then psql > will > need a hint how to find the unix domain socket to talk to. First, check that you can connect to the database by doing: psql -U postgres -h localhost -p 5432 (assuming proper postgresql.conf entries, like -> listen_addresses = '*') If it succeeds, then, in pgpool.conf, check that backend_hostname0 is pointing to where the database is. If it's localhost, the following should most likely be in your pgpool configuration: backend_hostname0 = 'localhost' backend_port0 = 5432 backend_weight0 = 0 backend_data_directory0 = '/var/lib/pgsql/9.0/data' Restart pgpool and try again connecting to the database through pgpool by doing: psql -U postgres -p If it doesn't work. Stop pgpool, and run it with debug output by doing the following as root: pgpool -n -d It should tell you that it cannot connect to backend0 for a reason. Then come back and show us your findings. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Replicate selected table
So in the end, Server A and B are meant to be identical, right? If so, you need multi-master replication. I think Bucardo is the one that can do that. -Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Micka Sent: Thursday, September 22, 2011 11:00 AM To: pgpool-general@pgfoundry.org Subject: [Pgpool-general] Replicate selected table Hello, I've two server : A & B. In the Server A, I've the table 1 and 3 In the Server B, I've the table 2 and 4. I would like to know if the server A can replicate the table 1 and 3 to the server B. And the server B can replicate the table 2 and 4 to the server A ? Thank you, ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Is pgpool_walrecrunning needed in Master/Slavemode?
> From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Sandeep Thakkar > Sent: Thursday, September 22, 2011 12:25 PM > To: Sandeep Thakkar; Tatsuo Ishii > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] Is pgpool_walrecrunning needed in Master/Slavemode? > > Also, > - who will add "hot_standby = on" in the new Standby and remove it from new Master? > - who will add "hostreplication..." entry in the new Standby's pg_hba.conf? > > I didn't find all these settings in basebackup. Though, we handle some of them. Please help. You can have the same pg_hba.conf and postgresql.conf in both database servers. In postgresql.conf, make sure you have: wal_level = hot_standby hot_standby = on Again, this is specific to PostgreSQL 9.x. Please, look at their documentation. Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Data not same on both nodes, streaming replication & load balancing
Hi everyone, I have configuration pgpool streaming replication & load balancing configuration with two nodes. pgpool version is 3.1.0-alpha2 (hatsuiboshi), postgres on both nodes is 9.0.4 Reason why I have use this version is that latest stable version (at that time) 3.0.1 was everything but not stable. Anyway.. I have situation now.. that my slave node is containing different data from master node. I select something from master and get one result, and same query from slave and I get different result. How to prevent this happening, and how it is possible that this happened. How to know data my other data is health? This is specific to PostgreSQL 9.x Streaming Replication. Please check: http://www.postgresql.org/docs/9.0/interactive/warm-standby.html#STREAMI NG-REPLICATION -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool thinks a backend is down even though its not
You have to use pcp_attach_node command to re-attach that node. Pgpool doesn't know if a database that went down is in good shape even if it is back online. So, after you perform the synching (it seems like you did), call the pcp_attach_node to bring it back to pgpool's pool of databases. -Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Lonni J Friedman > Sent: Monday, September 12, 2011 1:14 PM > To: pgpool-general@pgfoundry.org > Subject: [Pgpool-general] pgpool thinks a backend is down even though > its not > > Greetings, > I've got a 3 node postgresql-9.0.4 cluster (1 master, two standby, all > running on Linux-x86_64. I had a hardware problem on one of the > standby's, and had to bring it down to swap out the bad HW. I got it > synced back up with the mater successfully, and I can successfully > manually run SQL queries from the pgpool server to the standby. > However, pgpool is convinced that the standby is still down: > read_status_file: 1 th backend is set to down status > > I'm confused how its making this determination, or how to fix it, > especially since I've set: > health_check_period = 0 > > Help?! > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] pgpool configuration file question
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html states the following: " backend_weight ... However, *values cannot be updated so you must restart pgpool-II in that case*. In pgpool-II 2.2.6/2.3 *or later, you can change this value by re-loading the configuration file*. This will take effect only for new established client sessions. This is useful if you want to prevent any query sent to slaves to perform some administrative work in master/slave mode. " Which of the text enclosed with asterisks above is correct? Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Can a failed master rejoin as a slave?
According to Matt, he is using pgpool-II 3.0.4 built from source. I have not tried either. From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Anton Koldaev Sent: Friday, June 17, 2011 2:39 PM To: Matt Solnit Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Can a failed master rejoin as a slave? Hmm... it seems to me your problem was resolved in 3.0.3: 3.0.3 (umiyameboshi) 2011/02/23 * Version 3.0.3 This version fixes various bugs since 3.0.1. Please note that 3.0.2 was canceled due to a packaging problem. - Fix online recovery problem in the streaming replication mode(Tatsuo). Consider following scenario. Suppose node 0 is the initial primary server and 1 is the initial standby server. 1) Node 0 going down and node 1 promotes to new primary. 2) Recover node 0 as new standby. 3) pgpool-II assumes that node 0 is the new primary. This problem happens because pgpool-II regarded unconditionally the youngest node to be the primary. pgpool-II 3.0.3 now checks each node by using pgpool_walrecrunning() to see if it is a actually primary or not and is able to avoid the problem and regards node as standby correctly. Also you can use new variable "%P" to be used in the recovery script. If you do not install the function, the above problem is not resolved. On Fri, Jun 17, 2011 at 8:02 PM, Matt Solnit wrote: On Jun 17, 2011, at 8:17 AM, wrote: >> Hi, Matt >>> pgpool-II immediately attempts to use it as a master again. This doesn't work, obviously, because it's no longer a master. >> I dont understand why it doesnt work. >> AFAIK node with the youngest id(backendX in pgpool.conf) and status 2(psql -c 'show pool_nodes;') will always become a primary node. >> >> Check this out: >> The backend which was given the DB node ID of 0 will be called "Master DB". When multiple backends are defined, the service can be continued even if the Master DB is down (not true in some modes). In this case, the youngest DB node ID alive will be the new Master DB. >> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html > > The problem Matt points out is precisely when primary DB *is re-attached*. After re-attaching the primary DB (node ID 0), it's "back online", therefore, pgpool treats it as the master again, according to your cited explanation. So I agree with Matt: the just re-attached Node 0 should be slave from now on, since it was technically attached AFTER selecting the new master (which is Node 1 at this point). > > -Daniel Exactly. With streaming replication, only the "true" master can accept DML statements (insert/update/delete), so if pgpool-II attempts to send them to the wrong node, you get a "connect execute XYZ in a read-only transaction" error. This thread seems to cover the same question, but I couldn't really tell what the resolution was: http://lists.pgfoundry.org/pipermail/pgpool-general/2011-April/003568.ht ml -- Matt ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general -- Best regards, Koldaev Anton ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Can a failed master rejoin as a slave?
> Hi, Matt >> pgpool-II immediately attempts to use it as a master again. This doesn't >> work, obviously, because it's no longer a master. > I dont understand why it doesnt work. > AFAIK node with the youngest id(backendX in pgpool.conf) and status 2(psql -c > 'show pool_nodes;') will always become a primary node. > > Check this out: > The backend which was given the DB node ID of 0 will be called "Master DB". > When multiple backends are defined, the service can be continued even if the > Master DB is down (not true in some modes). In this case, the youngest DB > node ID alive will be the new Master DB. > http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html The problem Matt points out is precisely when primary DB *is re-attached*. After re-attaching the primary DB (node ID 0), it's "back online", therefore, pgpool treats it as the master again, according to your cited explanation. So I agree with Matt: the just re-attached Node 0 should be slave from now on, since it was technically attached AFTER selecting the new master (which is Node 1 at this point). -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] replication/failover feature in Master/Slavemode
Try running pgpool -d -n, and search for "failover_command". It should tell you if was or not executed. Remember to enable health_check in pgpool.conf. Search for "health_check" param in pgpool.conf. -Daniel From: Armin Nesiren [mailto:armin.nesi...@intera.si] Sent: Friday, January 28, 2011 4:01 PM To: Crespo, Daniel @ SSG - SDS Cc: sandee...@yahoo.com; pgpool-general@pgfoundry.org; Tatsuo Ishii Subject: Re: RE: [Pgpool-general] replication/failover feature in Master/Slavemode In my case there is no missunderstunding... Again... when I try to recover my old primary node (node 0) failover_command is not executed at all. I'm testing it just like you have described...im trying to echo string in file that is located in /tmp On 28 Jan 2011 21:55, wrote: > I think there is a misunderstanding... > > To clarify (just in case). > > 1. Pgpool detects non-healthy postgresql backend. > 2. Pgpool executes WHATEVER is in "failover_command" entry in > pgpool.conf > 3. The end. > > In "failover_command" you can put > "executeFailoverOfMyCurrentSlavePostgreSqlBackend.sh". > Which means, in yout pgpool.conf you should have: > failover_command="/path/to/executeFailoverOfMyCurrentSlavePostgreSqlBack > end.sh" > > in which you should do something like: > > + > #!/bin/bash > > touch /path/to/where/trigger/file/must/exist > > + > > Of course, you'll want to touch the trigger file of the backend that is > the standby, so you got to know its path. If you don't know it, let us > know. > > You might want to try having failover_command execute something that > writes to /tmp/somefile to prove that pgpool is actually executing > failover_command. > > Best regards, > -Daniel > >> -Original Message- >> From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] >> Sent: Friday, January 28, 2011 9:21 AM >> To: armin.nesi...@intera.si >> Cc: sandee...@yahoo.com; is...@sraoss.co.jp; Crespo, Daniel @ SSG - >> SDS; pgpool-general@pgfoundry.org >> Subject: Re: [Pgpool-general] replication/failover feature in >> Master/Slavemode >> >> > Also I have same problem. >> > When I do pcp_recovery of my old primary node (node 0), >> failover_command is >> > not executed, >> >> If you are using 3.0.x, then it's a known problem. >> Please try CVS HEAD (to be released as 3.1). >> -- >> Tatsuo Ishii >> SRA OSS, Inc. Japan >> English: http://www.sraoss.co.jp/index_en.php >> Japanese: http://www.sraoss.co.jp >> >> > I have set failback_command, and that command creates trigger file >> for me. >> > >> > This configuration works fine right now, but I would like to know if >> this is >> > right way to do it? >> > >> > On Fri, Jan 28, 2011 at 12:53 PM, Sandeep Thakkar >> wrote: >> > >> >> Well, that's what my first question was. Why pgpool is not > executing >> the >> >> failover_command in my case? Does it need any more settings in >> pgpool.conf? >> >> I did not set failback_command. >> >> >> >> >> >> >> >> -- >> >> *From:* Tatsuo Ishii >> >> *To:* sandee...@yahoo.com >> >> *Cc:* daniel.cre...@l-3com.com; guilla...@lelarge.info; >> >> pgpool-general@pgfoundry.org >> >> *Sent:* Fri, January 28, 2011 2:01:50 PM >> >> >> >> *Subject:* Re: [Pgpool-general] replication/failover feature in >> >> Master/Slavemode >> >> >> >> No. Failover_command will be automatically executed by pgpool-II if >> >> primary goes down. And you can have touch command to create the >> >> trigger file in your failover_command. This should automatically >> >> promote the standby. >> >> -- >> >> Tatsuo Ishii >> >> SRA OSS, Inc. Japan >> >> English: http://www.sraoss.co.jp/index_en.php >> >> Japanese: http://www.sraoss.co.jp >> >> >> >> > Thanks Tatsuo. So, does it mean with existing PG version (9.0), > we >> need >> >> to >> >> > manually execute failover script (create trigger file) even when >> using >> >> pgpool? >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > From: Tatsuo Ishii >> >> > To: sandee...@yahoo.com >> >> > Cc: daniel.cre...@l-3com.com; guilla...@lelarge.info; >> >> > pgpool-general@pgfoundry.org >> >> > Sent: Fri, January 28, 2011 1:08:08 PM >> >> > Subject: Re: [Pgpool-general] replication/failover feature in >> >> Master/Slavemode >> >> > >> >> >> Yes, I'm aware that I need to create a trigger file (same as > what >> is >> >> defined in >> >> >> >> >> >> recovery.conf) to start the failover and promote the Standby to >> Primary. >> >> I have >> >> >> >> >> >> tried this when I did not use pgpool and it worked fine. I >> thought, when >> >> pgpool >> >> >> >> >> >> is in picture, it will execute the failver_command, when it >> detects that >> >> >> >> >>primary >> >> >> >> >> >> is down. and since, the failover_command in my case is to "touch >> trigger >> >> file". >> >> >> >> >> >> Shouldn't pgpool execute the failover_command here and create >> that file >> >> >> automatically? >> >> > >> >> > Besides the problem that it's not easy to read and parse >>
Re: [Pgpool-general] replication/failover feature in Master/Slavemode
I think there is a misunderstanding... To clarify (just in case). 1. Pgpool detects non-healthy postgresql backend. 2. Pgpool executes WHATEVER is in "failover_command" entry in pgpool.conf 3. The end. In "failover_command" you can put "executeFailoverOfMyCurrentSlavePostgreSqlBackend.sh". Which means, in yout pgpool.conf you should have: failover_command="/path/to/executeFailoverOfMyCurrentSlavePostgreSqlBack end.sh" in which you should do something like: + #!/bin/bash touch /path/to/where/trigger/file/must/exist + Of course, you'll want to touch the trigger file of the backend that is the standby, so you got to know its path. If you don't know it, let us know. You might want to try having failover_command execute something that writes to /tmp/somefile to prove that pgpool is actually executing failover_command. Best regards, -Daniel > -Original Message- > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > Sent: Friday, January 28, 2011 9:21 AM > To: armin.nesi...@intera.si > Cc: sandee...@yahoo.com; is...@sraoss.co.jp; Crespo, Daniel @ SSG - > SDS; pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] replication/failover feature in > Master/Slavemode > > > Also I have same problem. > > When I do pcp_recovery of my old primary node (node 0), > failover_command is > > not executed, > > If you are using 3.0.x, then it's a known problem. > Please try CVS HEAD (to be released as 3.1). > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > > > I have set failback_command, and that command creates trigger file > for me. > > > > This configuration works fine right now, but I would like to know if > this is > > right way to do it? > > > > On Fri, Jan 28, 2011 at 12:53 PM, Sandeep Thakkar > wrote: > > > >> Well, that's what my first question was. Why pgpool is not executing > the > >> failover_command in my case? Does it need any more settings in > pgpool.conf? > >> I did not set failback_command. > >> > >> > >> > >> -- > >> *From:* Tatsuo Ishii > >> *To:* sandee...@yahoo.com > >> *Cc:* daniel.cre...@l-3com.com; guilla...@lelarge.info; > >> pgpool-general@pgfoundry.org > >> *Sent:* Fri, January 28, 2011 2:01:50 PM > >> > >> *Subject:* Re: [Pgpool-general] replication/failover feature in > >> Master/Slavemode > >> > >> No. Failover_command will be automatically executed by pgpool-II if > >> primary goes down. And you can have touch command to create the > >> trigger file in your failover_command. This should automatically > >> promote the standby. > >> -- > >> Tatsuo Ishii > >> SRA OSS, Inc. Japan > >> English: http://www.sraoss.co.jp/index_en.php > >> Japanese: http://www.sraoss.co.jp > >> > >> > Thanks Tatsuo. So, does it mean with existing PG version (9.0), we > need > >> to > >> > manually execute failover script (create trigger file) even when > using > >> pgpool? > >> > > >> > > >> > > >> > > >> > > >> > From: Tatsuo Ishii > >> > To: sandee...@yahoo.com > >> > Cc: daniel.cre...@l-3com.com; guilla...@lelarge.info; > >> > pgpool-general@pgfoundry.org > >> > Sent: Fri, January 28, 2011 1:08:08 PM > >> > Subject: Re: [Pgpool-general] replication/failover feature in > >> Master/Slavemode > >> > > >> >> Yes, I'm aware that I need to create a trigger file (same as what > is > >> defined in > >> >> > >> >> recovery.conf) to start the failover and promote the Standby to > Primary. > >> I have > >> >> > >> >> tried this when I did not use pgpool and it worked fine. I > thought, when > >> pgpool > >> >> > >> >> is in picture, it will execute the failver_command, when it > detects that > >> > >> >>primary > >> >> > >> >> is down. and since, the failover_command in my case is to "touch > trigger > >> file". > >> >> > >> >> Shouldn't pgpool execute the failover_command here and create > that file > >> >> automatically? > >> > > >> > Besides the problem that it's not easy to read and parse > >> > recovery.conf, creating trigger file might not be the only way to > >> > promote standby server forever. Actually currently PostgreSQL > >> > developers are discussing about "pg_ctl failover" command which > will > >> > be a new way to promote standby in the next version of > >> > PostgreSQL. Pgpool-II is designed to be flexible to work with as > many > >> > PostgreSQL version as possible. So we don't want to hard code > "touch > >> > trigger file" in pgpool itself. > >> > > >> >> I think, either pgpool is not able to detect that primary is down > >> >> or it is unable to execute the failover_command. Am I missing to > define > >> >>anything > >> >> > >> >> in the configuration file? > >> > >> > >> > >> ___ > >> Pgpool-general mailing list > >> Pgpool-general@pgfoundry.org > >> http://pgfoundry.org/mailman/listinfo/pgpool-general > >> > >> > > > > > > -- > > Armin
Re: [Pgpool-general] replication/failover feature in Master/Slavemode
"failover_command" entry in pgpool.conf allows you to do whatever you need to do when pgpool detects a database node degeneration. In your case you would need to touch the file where PostgreSQL 9.0+ expects it (specified in "trigger_file" within recovery.conf). Take a look at how postgresql 9.0's built-in replication works, and then you'll know what to touch and where. -Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Sandeep Thakkar Sent: Thursday, January 27, 2011 3:50 AM To: Sandeep Thakkar; Guillaume Lelarge Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] replication/failover feature in Master/Slavemode I was just testing the pgpool with one primary and one standby on the same box. Hot Standby, Streaming replication, load balancing, all are working fine. But, I see that failover is not happening when I shut down my primary server. When I manually touch the trigger file, failover happens. I thought, pgpool will execute the fail over command on it's own. Am I doing anything wrong? Here is my related settings in pgpool.conf: failover_command = 'touch My recovery.conf in the data directory of standby contains these variables: standby_mode primary_conninfo restore_command trigger_file Please help. From: Sandeep Thakkar To: Guillaume Lelarge Cc: pgpool-general@pgfoundry.org Sent: Fri, January 14, 2011 2:40:52 PM Subject: Re: [Pgpool-general] replication/failover feature in Master/Slave mode Thanks Guillaume. From: Guillaume Lelarge To: Sandeep Thakkar Cc: pgpool-general@pgfoundry.org Sent: Wed, January 12, 2011 8:09:53 PM Subject: Re: [Pgpool-general] replication/failover feature in Master/Slave mode Hi, Le 12/01/2011 15:10, Sandeep Thakkar a écrit : > [...] > I have a primary and a standby server on different boxes and both are running > on > different ports. I would like to use pgpool for Streaming Replication and > Failover. Hence, I selected Master/Slave mode with stream. Now, I have a > question, if I write anything on primary, how will the data be replicated on > standby? Will it happen automatically like it does in replication mode Or do > I > need to look into Hot Standby feature of PG? > You need to look at the streaming replication feature of PostgreSQL. You need Hot Standby for example if you want to use the load balacing feature of pgPool. > Also, on failover (let's say primary goes down for some reason), and the > standby > now takes over. Will the standby has write operations? > You need to write scripts for that. > Also, in my application, will I use the pgpool port, so that I do not need to > change the configuration file in case of failover?? With Slony, we had to > tell > the application to use the other port. > The pgpool port, always. -- Guillaume http://www.postgresql.fr http://dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Suggestion for Failover on Master/Slave Streaming Replication
Hi, Using Master/Slave with Streaming Replication and Hot Standby, on Failover, I think it would be a good idea to have pgpool stop handling connections until new master becomes writable. Maybe, the application should handle this, but we don't want the application check if the database is writable every time an INSERT, UPDATE, or DELETE is about to be run. Any workaround? I'm tempted to add a sleep in pgpool when a failover happens, before handling new connections to the new master, in order for the new master to become writable (by touching the corresponding file) and be able to service the clients without giving them errors. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Online recovery on streaming replication + hot standby
Hi all, When doing a failover, shoudn't pgpool-II keep the new master as the master from that point on? >From my experience, after the master database fails and the slave takes over as master, if pcp_recovery_node is used to recover the old master, pgpool-II is assumes the master is back (because pgpool seems to mainain its master at backend0). I think pgpool should attach any other database as an slave, therefore not assuming it is the master again if it's backend0. I'm tempted to stop pgpool-II, change its configuration file to swap backend values (backend0 with backend1 values and viceversa) and restart pgpool. This way, pgpool would work as I expect, but of course it's a hack. Is there a better idea around this? I'm using: pgpool-II 3.0.2 (CVS) and PostgreSQL 9.0.1, in a two-backend configuration. Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool_walrecrunning not working from pgpool connection
> >> > Issuing: > >> > psql -h 10.20.60.2 -U someuser -p 5432 -c "select > >> > pgpool_walrecrunning()" db0 > >> > Returns: > >> > pgpool_walrecrunning > >> > -- > >> > f > >> > (1 row) > > Thanks. What does this show? > > psql -h 10.20.60.3 -U someuser -p 5432 -c "select > pgpool_walrecrunning()" db0 Same error: ERROR: could not access file "$libdir/pgpool-walrecrunning": No such file or directory But good news, I found the error. I installed the functions into "someuser" database, instead of the "postgres" database. I think pgpool is using postgres as the user to run the pgpool_walrecrunning() function, and therefore defaulting the database to "postgres" to run the query. Now that I installed the pgpool-walrecrunning.sql into postgres database, pgpool is now not complaining. Thanks, Tatsuo. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool_walrecrunning not working from pgpool connection
> >> >> > I'm using Postgresql 9.0 and the latest pgpool from CVS Head > from > >> >> > Thursday October 21st, for streaming replication. > >> >> > > >> >> > > >> >> > > >> >> > When I start pgpool-II, and see the postgresql log file, its > >> output > >> >> > states that pgpool_walrecrunning() can not access > >> >> > '$libdir/pgpool_walrecrunning': No such file or directory. > >> >> > > >> >> > > >> >> > > >> >> > pgpool_walrecrunning.so is actually there. What is more > > important, > >> >> when > >> >> > I connect directly to postgresql it returns properly. But if I > >> >> connect > >> >> > using pgpool's port ( by default), the above error is > shown. > >> >> > > >> >> > > >> >> > > >> >> > I did enter the sql directory of pgpool source, and compiled > and > >> >> > installed each of the components, including > pgpool_walrecrunning. > >> >> > > >> >> > > >> >> > > >> >> > Is there something I'm missing? > >> >> > >> >> Are you sure that you installed pgpool_walrecrunning on each DB > >> node? > >> > > >> > Yes. I did. Here is what I experience: > >> > > >> > * Directly to postgres * > >> > -bash-3.2$ psql -U someuser -p 5432 db0 > >> > psql (9.0.1) > >> > Type "help" for help. > >> > > >> > db0=# select pgpool_walrecrunning(); > >> > pgpool_walrecrunning > >> > -- > >> > f > >> > (1 row) > >> > > >> > db0=# \q > >> > > >> > * Through pgpool *** > >> > -bash-3.2$ psql -U someuser -p db0 > >> > psql (9.0.1) > >> > Type "help" for help. > >> > > >> > db0=# select pgpool_walrecrunning(); > >> > ERROR: could not access file "$libdir/pgpool-walrecrunning": No > > such > >> > file or directory > >> > db0=# \q > >> > >> What are your backend_hostnameN in pgpool.conf? > >> For example, if your backend_hostname0 is "somehost", you want to > try: > >> > >> psql -h somehost -U someuser -p 5432 -c "select > > pgpool_walrecrunning()" > >> db0 > > > > backend_hostname0=10.20.60.2 > > > > Issuing: > > psql -h 10.20.60.2 -U someuser -p 5432 -c "select > > pgpool_walrecrunning()" db0 > > Returns: > > pgpool_walrecrunning > > -- > > f > > (1 row) > > > > For your information: In my case, I have pgpool on the same host. > > To make sure can you show me your pgpool.conf? listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/tmp' pcp_socket_dir = '/tmp' backend_socket_dir = '/tmp' pcp_timeout = 10 num_init_children = 100 max_pool = 1 child_life_time = 0 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/opt/pgpool/log/' pid_file_name = '/opt/pgpool/pgpool.pid' replication_mode = false load_balance_mode = true 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,addnewteam' print_timestamp = true master_slave_mode = true master_slave_sub_mode = 'stream' delay_threshold = 1000 log_standby_delay = 'if_over_threshold' connection_cache = true health_check_timeout = 30 health_check_period = 10 health_check_user = 'postgres' failover_command = '/opt/pgpool/bin/trigger_failover_command %m' failback_command = 'touch FAILBACK_OCCURRED' fail_over_on_backend_error = true insert_lock = false ignore_leading_white_space = true log_statement = false log_per_node_statement = false log_connections = false log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = '' system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' backend_hostname0 = '10.20.60.2' backend_port0 = 5432 backend_weight0 = 0 backend_data_directory0 = '/var/lib/pgsql/9.0/data' backend_hostname1 = '10.20.60.3' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/9.0/data' enable_pool_hba = false recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 client_idle_limit_in_recovery = 0 lobj_lock_table = '' ssl = false debug_level = 0 Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool_walrecrunning not working from pgpool connection
> >> > I'm using Postgresql 9.0 and the latest pgpool from CVS Head from > >> > Thursday October 21st, for streaming replication. > >> > > >> > > >> > > >> > When I start pgpool-II, and see the postgresql log file, its > output > >> > states that pgpool_walrecrunning() can not access > >> > '$libdir/pgpool_walrecrunning': No such file or directory. > >> > > >> > > >> > > >> > pgpool_walrecrunning.so is actually there. What is more important, > >> when > >> > I connect directly to postgresql it returns properly. But if I > >> connect > >> > using pgpool's port ( by default), the above error is shown. > >> > > >> > > >> > > >> > I did enter the sql directory of pgpool source, and compiled and > >> > installed each of the components, including pgpool_walrecrunning. > >> > > >> > > >> > > >> > Is there something I'm missing? > >> > >> Are you sure that you installed pgpool_walrecrunning on each DB > node? > > > > Yes. I did. Here is what I experience: > > > > * Directly to postgres * > > -bash-3.2$ psql -U someuser -p 5432 db0 > > psql (9.0.1) > > Type "help" for help. > > > > db0=# select pgpool_walrecrunning(); > > pgpool_walrecrunning > > -- > > f > > (1 row) > > > > db0=# \q > > > > * Through pgpool *** > > -bash-3.2$ psql -U someuser -p db0 > > psql (9.0.1) > > Type "help" for help. > > > > db0=# select pgpool_walrecrunning(); > > ERROR: could not access file "$libdir/pgpool-walrecrunning": No such > > file or directory > > db0=# \q > > What are your backend_hostnameN in pgpool.conf? > For example, if your backend_hostname0 is "somehost", you want to try: > > psql -h somehost -U someuser -p 5432 -c "select pgpool_walrecrunning()" > db0 backend_hostname0=10.20.60.2 Issuing: psql -h 10.20.60.2 -U someuser -p 5432 -c "select pgpool_walrecrunning()" db0 Returns: pgpool_walrecrunning -- f (1 row) For your information: In my case, I have pgpool on the same host. Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool_walrecrunning not working from pgpool connection
> > I'm using Postgresql 9.0 and the latest pgpool from CVS Head from > > Thursday October 21st, for streaming replication. > > > > > > > > When I start pgpool-II, and see the postgresql log file, its output > > states that pgpool_walrecrunning() can not access > > '$libdir/pgpool_walrecrunning': No such file or directory. > > > > > > > > pgpool_walrecrunning.so is actually there. What is more important, > when > > I connect directly to postgresql it returns properly. But if I > connect > > using pgpool's port ( by default), the above error is shown. > > > > > > > > I did enter the sql directory of pgpool source, and compiled and > > installed each of the components, including pgpool_walrecrunning. > > > > > > > > Is there something I'm missing? > > Are you sure that you installed pgpool_walrecrunning on each DB node? Yes. I did. Here is what I experience: * Directly to postgres * -bash-3.2$ psql -U someuser -p 5432 db0 psql (9.0.1) Type "help" for help. db0=# select pgpool_walrecrunning(); pgpool_walrecrunning -- f (1 row) db0=# \q * Through pgpool *** -bash-3.2$ psql -U someuser -p db0 psql (9.0.1) Type "help" for help. db0=# select pgpool_walrecrunning(); ERROR: could not access file "$libdir/pgpool-walrecrunning": No such file or directory db0=# \q Any clue? Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] pgpool_walrecrunning not working from pgpool connection
I'm using Postgresql 9.0 and the latest pgpool from CVS Head from Thursday October 21st, for streaming replication. When I start pgpool-II, and see the postgresql log file, its output states that pgpool_walrecrunning() can not access '$libdir/pgpool_walrecrunning': No such file or directory. pgpool_walrecrunning.so is actually there. What is more important, when I connect directly to postgresql it returns properly. But if I connect using pgpool's port ( by default), the above error is shown. I did enter the sql directory of pgpool source, and compiled and installed each of the components, including pgpool_walrecrunning. Is there something I'm missing? Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Two geographic instances
> > I suggest you to read this: > > > > http://www.postgresql.org/docs/9.0/interactive/different-replication- > solutions.html > > > > > > > > I would look at what Bucardo offers, which is asynchronous multi- > master > > replication. In synchronous multi-master, and especially in your case > with > > pgpool-II, if one database goes down, you might end with inconsistent > data > > in each database (some data same, but newly added data on each end > would > > cause a conflic in case you wanted to merge somehow the changes). > > Thanks, I will certainly look at Bucardo. However, I don't understand > why I should have inconsistent data if a site goes down. If a site > goes down, only the other site will receive updates, so in the end > when the down site is back up again (before letting users use it) I > will only have to do a unidirectional synchronization from the working > site to the recovered site. > Am I missing something? That's true for a "database goes down". But remember that each site might also work "locally" when there is not connection between the backends (split-brain problem), meaning that each database might end with equally important data. It would not suffice to synchronize one way, but both, opening the possibility to conflicts. Bucardo has conflict resolution. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Two geographic instances
I suggest you to read this: http://www.postgresql.org/docs/9.0/interactive/different-replication-sol utions.html I would look at what Bucardo offers, which is asynchronous multi-master replication. In synchronous multi-master, and especially in your case with pgpool-II, if one database goes down, you might end with inconsistent data in each database (some data same, but newly added data on each end would cause a conflic in case you wanted to merge somehow the changes). -Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Marco Sent: Friday, October 22, 2010 7:04 AM To: pgpool-general@pgfoundry.org Subject: [Pgpool-general] Two geographic instances Hi all, I've been tasked with setting up a website running at two different geographic locations. Both locations should look the same to users, so the problem is keeping the database (postgres) consistent. I'm expecting a minority of the accesses to be writes, and the rest reads. So I thought about doing something like this: have a postgres database at each site. On top of that, a pgpool instance at each site configured to use the local postgres and the remote pgpool as backends. Writes would go to the local postgres, and to the remote pgpool (synchronously; I can probably afford the cost as writes will be few). Would this work technically? Would this provide consistency between the two databases? I've never used pgpool before, so I may be uttering nonsense (let me know if that's the case). Thanks for any help. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Database redundancy recommended approach
> > > * 2 DB Servers (DB0 and DB1)
> > >
> > > * 1 pgpool instance (in a thrid server)
> > >
> > > * Streaming replication from DB0 (master) to DB1 (slave)
> > >
> > > * If DB0 fails, DB1 must become primary.
> >
> > Easy.
> >
> > > * As soon as DB0 is ready again, make it slave of DB1.
> >
> > With 3.0.2(or CVS HEAD) it's possible if you write a program(outside
> > pgpool world) which triggers online recovery when DB0 is ready
again.
> >
> > 3.0.1 or before does not handle the case when DB0 is standby and DB1
> > is primary.
>
> I downloaded CVS head (3.0.2), compiled and run it.
>
> As soon as it starts, it segfaults:
>
> Program received signal SIGSEGV, Segmentation fault.
> 0x00403c38 in find_primary_node () at main.c:2313
> 2313if (!strcmp(res->data[0], "t"))
> Missing separate debuginfos, use: debuginfo-install
> cyrus-sasl-lib-2.1.22-19.fc10.x86_64 e2fsprogs-libs-1.41.4-
> 6.fc10.x86_64
> glibc-2.9-3.x86_64 keyutils-libs-1.2-3.fc9.x86_64
> krb5-libs-1.6.3-18.fc10.x86_64 libselinux-2.0.78-1.fc10.x86_64
> openldap-2.4.12-1.fc10.x86_64 openssl-0.9.8g-14.fc10.x86_64
> postgresql-libs-8.3.8-2.fc10.x86_64 zlib-1.2.3-18.fc9.x86_64
>
> Daniel
Sorry, I should have put more detail:
...
2010-10-21 11:28:29 LOG: pid 22758: find_primary_node: do_query
returns no rows
2010-10-21 11:28:29 LOG: pid 22758: find_primary_node: do_query
returns no data
Program received signal SIGSEGV, Segmentation fault.
0x00403c38 in find_primary_node () at main.c:2313
2313if (!strcmp(res->data[0], "t"))
Missing separate debuginfos, use: debuginfo-install
cyrus-sasl-lib-2.1.22-19.fc10.x86_64 e2fsprogs-libs-1.41.4-6.fc10.x86_64
glibc-2.9-3.x86_64 keyutils-libs-1.2-3.fc9.x86_64
krb5-libs-1.6.3-18.fc10.x86_64 libselinux-2.0.78-1.fc10.x86_64
openldap-2.4.12-1.fc10.x86_64 openssl-0.9.8g-14.fc10.x86_64
postgresql-libs-8.3.8-2.fc10.x86_64 zlib-1.2.3-18.fc9.x86_64
In main.c:
if (res->data[0] == NULL)
{
pool_log("find_primary_node: do_query returns no data");
}
if (res->nullflags[0] == -1)
{
pool_log("find_primary_node: do_query returns NULL");
}
According to the log, res->data[0] is null, and therefore is failing in:
if (!strcmp(res->data[0], "t"))
{
is_standby = true;
}
Regards,
Daniel
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Database redundancy recommended approach
> > * 2 DB Servers (DB0 and DB1) > > > > * 1 pgpool instance (in a thrid server) > > > > * Streaming replication from DB0 (master) to DB1 (slave) > > > > * If DB0 fails, DB1 must become primary. > > Easy. > > > * As soon as DB0 is ready again, make it slave of DB1. > > With 3.0.2(or CVS HEAD) it's possible if you write a program(outside > pgpool world) which triggers online recovery when DB0 is ready again. > > 3.0.1 or before does not handle the case when DB0 is standby and DB1 > is primary. I downloaded CVS head (3.0.2), compiled and run it. As soon as it starts, it segfaults: Program received signal SIGSEGV, Segmentation fault. 0x00403c38 in find_primary_node () at main.c:2313 2313if (!strcmp(res->data[0], "t")) Missing separate debuginfos, use: debuginfo-install cyrus-sasl-lib-2.1.22-19.fc10.x86_64 e2fsprogs-libs-1.41.4-6.fc10.x86_64 glibc-2.9-3.x86_64 keyutils-libs-1.2-3.fc9.x86_64 krb5-libs-1.6.3-18.fc10.x86_64 libselinux-2.0.78-1.fc10.x86_64 openldap-2.4.12-1.fc10.x86_64 openssl-0.9.8g-14.fc10.x86_64 postgresql-libs-8.3.8-2.fc10.x86_64 zlib-1.2.3-18.fc9.x86_64 Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Online recovery with streaming replication
"Online recovery with Streaming Replication ... You cannot recover the primary node. To recover the primary node, you have to stop all DB nodes and pgpool-II then restore from the backup." Is this a feature? I think the oldest node that has become master should remain master from that point on, even if the failed old-master is recovered and re-attached. Don't you think so? Why isn't it this way? (I'm sure there is a reason as to why it is the way it is) Thanks in advance, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Database redundancy recommended approach
>From your point of view, what is the recommended approach to have the following scenario working without human intervention? * 2 DB Servers (DB0 and DB1) * 1 pgpool instance (in a thrid server) * Streaming replication from DB0 (master) to DB1 (slave) * If DB0 fails, DB1 must become primary. * As soon as DB0 is ready again, make it slave of DB1. * With DB1 as primary, if it fails, DB0 must become primary (again). * And so on. Thanks, -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] white_function_list parameter
"white_function_list ... In master slave mode, SELECTs are sent to master(primary) only. " (Taken from pgpool-II web site) Is this true? What about when working with streaming replication where I want the hot-standby to run read-only queries (SELECTs)? Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Duration of commit on more than one backend configuration
Hi, As far as I know, pgpool-II waits for [all] backends to commit the SQL statements (under replication mode) before returning to the client, right? This means that if, for example, one of the backends is very busy (100% CPU, etc.), pgpool will experience a delay and so will the application. Is this correct? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] rawmode failover
I forgot to specify that this happens after, for example, backend_hostname0 is killed. > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Crespo, Daniel @ SDS > Sent: Friday, June 18, 2010 9:49 AM > To: Joe Conway; Tatsuo Ishii > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] rawmode failover > > I have health check enabled every 5 seconds or so, but most of the time > (if not always) I experience a failed attempt to connect (or execute a > query in an already started session), then, the next attempt succeeds. > So I think it is not working for me for avoiding the connection attempt > failure. > > Daniel > > > -Original Message- > > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > > boun...@pgfoundry.org] On Behalf Of Joe Conway > > Sent: Thursday, June 17, 2010 8:40 PM > > To: Tatsuo Ishii > > Cc: pgpool-general@pgfoundry.org > > Subject: Re: [Pgpool-general] rawmode failover > > > > * PGP Signed by an unknown key > > > > On 06/17/2010 05:39 PM, Tatsuo Ishii wrote: > > >> In rawmode, set up for failover per the docs, I find that after > > >> killing backend_hostname0, my first attempt to access the database > > >> via pgpool will fail, and the second attempt will succeed against > > >> backend_hostname1. Is this normal? I.e. is there a way to > eliminate > > >> the initial failed connection after backend_hostname0 goes down? > > > > > > Enabling health checking should help you. If the health checking > find > > > backend goes down, it will trigger failover immediately, rather > than > > > "first attempt to access the database via pgpool". So next attempt > to > > > connect pgpool will success. > > > > Ah, OK -- thanks! > > > > Joe > > > > > > > > * Unknown Key > > * 0x35F7D865 > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] rawmode failover
I have health check enabled every 5 seconds or so, but most of the time (if not always) I experience a failed attempt to connect (or execute a query in an already started session), then, the next attempt succeeds. So I think it is not working for me for avoiding the connection attempt failure. Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Joe Conway > Sent: Thursday, June 17, 2010 8:40 PM > To: Tatsuo Ishii > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] rawmode failover > > * PGP Signed by an unknown key > > On 06/17/2010 05:39 PM, Tatsuo Ishii wrote: > >> In rawmode, set up for failover per the docs, I find that after > >> killing backend_hostname0, my first attempt to access the database > >> via pgpool will fail, and the second attempt will succeed against > >> backend_hostname1. Is this normal? I.e. is there a way to eliminate > >> the initial failed connection after backend_hostname0 goes down? > > > > Enabling health checking should help you. If the health checking find > > backend goes down, it will trigger failover immediately, rather than > > "first attempt to access the database via pgpool". So next attempt to > > connect pgpool will success. > > Ah, OK -- thanks! > > Joe > > > > * Unknown Key > * 0x35F7D865 ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool detected difference of the number of inserted, updated, or deleted ...etc..
Here is a complement for that suggestion: If the statement is INSERT/UPDATE/DELETE, and the number of returned affected rows differ, degenerate the backends that differ from master db, without throwing an error to the client (conserve transparency). Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Ramon de Carvalho Valle > Sent: Tuesday, June 08, 2010 7:52 PM > To: Tatsuo Ishii > Cc: tirt...@simply-y.com; pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pgpool detected difference of the number > of inserted, updated, or deleted ...etc.. > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Here is my suggestion: > > When set to true, if a SELECT statement returns a result set of records > different between the backends, the backends that differ from the > master > are degenerated. This is only valid if the SELECT statement is part of > a > transaction and replicate_select is set to true. If set to false, the > session is terminated and the backends are not degenerated. Default is > false. > > - -Ramon > > > On 06/06/2010 10:25 PM, Tatsuo Ishii wrote: > >> BTW : what is 'replication_stop_on_mismatch' and has it got anything > to do > >> with this issue ? > > > > replication_stop_on_mismatch only affects SELECT. So it does not > > anything to do with the issue. > > > > I have to admit that current document regarding > > replication_stop_on_mismatch is quite incorrect and hard to > > understand: > > > > "When set to true, pgpool-II degenerates the backends and keeps the > > service only with the Master DB if data mismatch occurs. If false, > > pgpool-II just terminates the query. Default is false." > > > > Probably enhanced description for this would be something like > > this. Please correct or raise questions. Grammatical corrections are > > welcome as well. > > > > "When set to true, if a backend returns different number of rows from > > other backends return in SELECT (or SHOW), pgpool-II degenerates > > it. This only take effects when the SELECT is running in an explicit > > transaction and replicate_select is set to true. If false, pgpool-II > > just terminates the session. Default is false." > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > English: http://www.sraoss.co.jp/index_en.php > > Japanese: http://www.sraoss.co.jp > > ___ > > Pgpool-general mailing list > > Pgpool-general@pgfoundry.org > > http://pgfoundry.org/mailman/listinfo/pgpool-general > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkwO160ACgkQGIS0iEuhp4MFVACdFrC9kvAvN20wz3PK4afu3f15 > dRoAoMaVA1bwxHlQt3oXAPL6nQ2qpGB8 > =bEVA > -END PGP SIGNATURE- > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool detected difference of the number of inserted, updated, or deleted ...etc..
I totally support this idea. > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii > Sent: Thursday, June 03, 2010 7:05 PM > To: a...@lingnu.com > Cc: tirt...@simply-y.com; pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pgpool detected difference of the number > of inserted, updated, or deleted ...etc.. > > > after a couple of months of quite operation, we are getting the error > > "pgpool detected difference of the number of inserted, updated, or > deleted > > touples.." > > > > why isn't pgpool triggers the failover event ? > > it looks most rationale to me, so that users can continue to work, > > and sysadmin can handle it in he's free time > > Looks reasonable idea to me. I would like to include it in the next > major release. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Recovery from network outage
Russ, pgpool-II can call a script every time there is a failover event (see 'failover_command' in the manual). You can create a script that calls pcp_node_attach to reattach the failed node once the access to it is recovered. For example, keep testing "SELECT 1;" against the remote database until it returns correctly, meaning there is connectivity. After this, issue the pcp_node_attach of that node. And that's it. BTW, looking at your pgpool.conf, you only have one node configured in pgpool, so I'm assuming this is correct. Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Russ Neufeld > Sent: Tuesday, May 25, 2010 2:58 PM > To: pgpool-general@pgfoundry.org > Subject: [Pgpool-general] Recovery from network outage > > Hi all, > > How do I set up pgpool to recover from the occasion network > outage? This morning we briefly lost network connectivity between our > web machine and our db machine, and this showed up in > /var/log/messages: > > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 ERROR: pid 17169: > connect_inet_domain_socket: connect() failed: No route to host > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 ERROR: pid 17169: > connection to 10.177.77.115(5432) failed > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 ERROR: pid 17169: > new_connection: create_cp() failed > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 LOG: pid 17169: > notice_backend_error: 0 fail over request from pid 17169 > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 LOG: pid 16889: > starting degeneration. shutdown host 10.177.77.115(5432) > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 ERROR: pid 16889: > failover_handler: no valid DB node found > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 LOG: pid 16889: > failover_handler: set new master node: 1 > May 25 06:35:05 web pgpool: 2010-05-25 06:35:05 LOG: pid 16889: > failover done. shutdown host 10.177.77.115(5432) > > I needed to restart pgpool manually for it to recover. Here's > what our pgpool.conf looks like: > > listen_addresses = 'localhost' > port = 5432 > enable_pool_hba = true > replication_mode = false > load_balance_mode = false > master_slave_mode = false > backend_hostname0 = '10.177.77.115' > backend_port0 = 5432 > health_check_period = 0 > fail_over_on_backend_error = false > connection_cache = true > num_init_children = 20 > max_pool = 2 > child_life_time = 300 > connection_life_time = 0 > child_max_connections = 0 > child_idle_limit = 0 > authentication_timeout = 30 > > Do I need to play with health_check_period and/or > health_check_timeout to get this right? Is there a way to make pgpool > resilient to network blips, or is this always a manual recovery? > > Thanks, > > Russ > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Need documentation about pgpool2
I think you are talking about table partitioning (not replication), which I think it's supported by pgpool. Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Alessio Grasso > Sent: Wednesday, March 31, 2010 1:29 PM > To: . > Subject: [Pgpool-general] Need documentation about pgpool2 > > Hi all, >I'm working with PostgreSql 8.3 and I need to distribute fragments > of some tables. > I followed the tutorial present in pgpool home site and I configured it > succesfully (testing with pgbench). > I would like to replicate single table instead to replicate all the > tables present in database. > For example if I have 2 tables: users and sites. Table users have a > foreign key to sites. I would to distribute all the users that are in > site 1 phisically in node1, the users that refer to site 2 on node2 and > so on. > Is it possible to do this with PgPool-2? > If it's possible where can I find further information to realize this? > > Thanks in advance, > Alessio Grasso > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Multi-statement queries
Question: What would happen if the multi-statement string is "BEGIN; SELECT 1;
INSERT INTO ...; COMMIT;"?
> -Original Message-
> From: Matthias Tief [mailto:matth...@itso-berlin.de]
> Sent: Wednesday, March 10, 2010 7:21 AM
> To: Tatsuo Ishii
> Cc: cmart...@gmail.com; glynast...@yahoo.co.uk; Crespo, Daniel @ SDS;
> pgpool-general@pgfoundry.org
> Subject: Re: [Pgpool-general] Multi-statement queries
>
> Hi,
>
> As I understand one can avoid this problem by setting replicate_select
> =
> true, right? But than one will have almost no load balancing anymore.
>
> Matthias
>
> Tatsuo Ishii schrieb:
> >> Hello there,
> >>
> >> My experience is that, the problem with multi-statement queries, is
> when you
> >> run in replication mode and the multi-statement query starts with
> "select",
> >> and next an "insert", then pgpool runs query in one backend, but
> don't
> >> replicate insert. In this case backends are not synchronized.
> >> With multi-statement query, type select;select.;select
> seems run
> >> well.
> >
> > Not really. What if:
> >
> > SELECT 1;SELECT nextval(..);...
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese: http://www.sraoss.co.jp
> >
> >> Regards.
> >>
> >>
> >> 2010/3/9 Glyn Astill
> >>
> >>> Yeah I'm with Daniel on this one, Tatsuo?
> >>>
> >>> --- On Tue, 9/3/10, daniel.cre...@l-3com.com 3com.com>
> >>> wrote:
> >>>
> From: daniel.cre...@l-3com.com
> Subject: Re: [Pgpool-general] Multi-statement queries
> To: "Matthias Tief" ,
> >>> pgpool-general@pgfoundry.org
> Date: Tuesday, 9 March, 2010, 14:50
> My experience is also that
> multi-statement queries ("statement1; statement2; ...;
> statementN;") work in pgpool-II, so now I'm confused with
> "pgpool-II cannot process multi-statement query". Also, why
> wouldn't it work if PostgreSQL itself does? I think the idea
> is pgpool-II to work transparently as connecting directly to
> PostgreSQL.
>
> Daniel
>
> > -Original Message-
> > From: pgpool-general-boun...@pgfoundry.org
> [mailto:pgpool-general-
> > boun...@pgfoundry.org]
> On Behalf Of Matthias Tief
> > Sent: Tuesday, March 09, 2010 4:11 AM
> > To: pgpool-general@pgfoundry.org
> > Subject: Re: [Pgpool-general] Multi-statement queries
> >
> > Thank you for your rapid answer.
> >
> > But for me it is still not clear. What you describe
> seems to be a
> > collection of statements transmitted as a single
> transaction. But this
> > is done very well by pgpool-II in my tests. I am wrong
> with that?
> > So than my question is: What is the difference between
> "multi-statement
> > queries" and a single transaction of multiple
> statements?
> > As I understand pgadmin implicitly wrapps a
> BEGIN/COMMIT block around
> > all statements, that it becomes a single transaction.
> >
> > Looking forward to your answer.
> >
> > Matthias
> >
> > Guillaume Lelarge schrieb:
> >> Le 05/03/2010 09:45, Matthias Tief a écrit :
> >>> Hallo,
> >>>
> >>> we are planning to use pgpool-II as
> replication solution for our
> >>> PostgeSQL application. Currently we evaluate,
> whether the
> > restriction of
> >>> pgpool-II affect our application. In the
> documentation you say:
> >>> "pgpool-II cannot process multi-statement
> query." What do you
> > exactly
> >>> mean with "multi-statement queries". As I
> found out you don't mean
> >>> transactions that contain multiple statments,
> like BEGIN - COMMIT
> >>> blocks. These blocks are "committed" or in
> case of a failure are
> > "rolled
> >>> back" correctly. I would appreciate if you
> could give me an example
> > of
> >>> multi-statement queries.
> >>>
> >> "statement1; statement2; statement3;" in a single
> trip to the
> > backend.
> >> For example:
> >>
> >> "INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES
> (2); INSERT INTO t1
> >> VALUES (3);"
> >>
> >> is a multi-statement query, whereas
> >>
> >> "INSERT INTO t1 VALUES (1);"
> >> then
> >> "INSERT INTO t1 VALUES (2);"
> >> then
> >> "INSERT INTO t1 VALUES (3);"
> >>
> >> are three one-statement queries.
> >>
> >> pgAdmin uses this quite a lot ("SET
> client_encoding TO utf8; SELECT *
> >> FROM...").
> >>
> >>
> > --
> > IT Service Omikron GmbH
> > Mohrenstr. 63-64; D-10117 Berlin
> > Tel.: +49 (030) 220791-30
> > Fax: +49 (030) 220791-55
> > Email: matthias.t...@itso-berlin.de
> > Internet: www.itso.de
> > Amtsgericht Berlin Charlottenburg HRB 21013
> >
> > ___
> > Pgpool-general mailing list
> > Pgpool-general@pgfoundry.or
Re: [Pgpool-general] Multi-statement queries
My experience is also that multi-statement queries ("statement1; statement2;
...; statementN;") work in pgpool-II, so now I'm confused with "pgpool-II
cannot process multi-statement query". Also, why wouldn't it work if PostgreSQL
itself does? I think the idea is pgpool-II to work transparently as connecting
directly to PostgreSQL.
Daniel
> -Original Message-
> From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-
> boun...@pgfoundry.org] On Behalf Of Matthias Tief
> Sent: Tuesday, March 09, 2010 4:11 AM
> To: pgpool-general@pgfoundry.org
> Subject: Re: [Pgpool-general] Multi-statement queries
>
> Thank you for your rapid answer.
>
> But for me it is still not clear. What you describe seems to be a
> collection of statements transmitted as a single transaction. But this
> is done very well by pgpool-II in my tests. I am wrong with that?
>
> So than my question is: What is the difference between "multi-statement
> queries" and a single transaction of multiple statements?
>
> As I understand pgadmin implicitly wrapps a BEGIN/COMMIT block around
> all statements, that it becomes a single transaction.
>
> Looking forward to your answer.
>
> Matthias
>
> Guillaume Lelarge schrieb:
> > Le 05/03/2010 09:45, Matthias Tief a écrit :
> >> Hallo,
> >>
> >> we are planning to use pgpool-II as replication solution for our
> >> PostgeSQL application. Currently we evaluate, whether the
> restriction of
> >> pgpool-II affect our application. In the documentation you say:
> >> "pgpool-II cannot process multi-statement query." What do you
> exactly
> >> mean with "multi-statement queries". As I found out you don't mean
> >> transactions that contain multiple statments, like BEGIN - COMMIT
> >> blocks. These blocks are "committed" or in case of a failure are
> "rolled
> >> back" correctly. I would appreciate if you could give me an example
> of
> >> multi-statement queries.
> >>
> >
> > "statement1; statement2; statement3;" in a single trip to the
> backend.
> >
> > For example:
> >
> > "INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1
> > VALUES (3);"
> >
> > is a multi-statement query, whereas
> >
> > "INSERT INTO t1 VALUES (1);"
> > then
> > "INSERT INTO t1 VALUES (2);"
> > then
> > "INSERT INTO t1 VALUES (3);"
> >
> > are three one-statement queries.
> >
> > pgAdmin uses this quite a lot ("SET client_encoding TO utf8; SELECT *
> > FROM...").
> >
> >
>
> --
> IT Service Omikron GmbH
> Mohrenstr. 63-64; D-10117 Berlin
> Tel.: +49 (030) 220791-30
> Fax: +49 (030) 220791-55
> Email: matthias.t...@itso-berlin.de
> Internet: www.itso.de
> Amtsgericht Berlin Charlottenburg HRB 21013
>
> ___
> Pgpool-general mailing list
> Pgpool-general@pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Cluster pgpool-II + PHP
> > My other question is, the maintenance tasks reindex and vacuum, > should be > > made over pgpool or over each backend? > > Vaccuum with verbose option should not be used over pgpool. It may > cause "kind mismatch" error because number of messages returned from > vacuum may vary on each backend(I have a plan to improve > this). Otherwise you could made these commands over pgpool or over > each backend. So, both "VACUUM" and "VACUUM ANALYZE" (without verbose) work properly when used over pgpool? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Cannot trigger out-of-sync
> > Question: Could it be that a failover was triggered for a different > > number of rows in a previous version? We were under the impression > that > > this was the behaviour. > > > > Thanks a lot, > > Ger. > > > > > Hi again, > > Could someone kindly give me an answer to the questions above? I do > think this is an important issue. I have the same question. I thought that different number of rows was triggering a failover, but tests with pgpool-II 2.2.2 have not shown this behavior. However, I DO have seen this behavior before. Any other answering this question, please? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] health_check user
Hi, I'm using pgpool-II 2.2.2. I password protected the database for every user, including 'postgres'. However, I made the .pgpass in the corresponding home directories in order to have access without explicitly providing password. If I password protect postgresql (by changing pg_hba.conf of postgresql), the postgresql log files show LOG: could not receive data from client: Connection reset by peer LOG: unexpected EOF within message length word every pgpool's health_check_period seconds. If I take out the password protection, the above error goes away. Notice that I'm able to interact with the database normally in both scenarios. It's just that the logs show something not making sense to me. Any clue? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Incorrect port (9999) for pgpool?
> Mary Ellen Fitzpatrick wrote: > > Hi, > > I am new at using pgpool and I am having some difficulty with getting > > pgpool/postgres to work on port . I am running CentOS5, > > Postgres-8.1.18, and have installed pgpool-II-2.3.1. Even after > having > > read the pgpool-II manual, pgpool-II-Tutorial and Pgpool-II for > > beginners, I am missing something that does not allow me connect to > > pgpool via port . > > > > visantst:~$ createdb -p bench_replication > > createdb: could not connect to database postgres: server closed the > > connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > > > I can create databases if I do not use port : > > visantst:~$ createdb junk > > Password: > > CREATE DATABASE > > > > Seems like I have incorrect ports for pgpool... and/or postgres... to > > work together. > > Postgres is configured/running on port 5432. Stop pgpool (make sure it's not running). And try running pgpool as follows: pgpool -n -d and see the output. It should help you debugging the issue. Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] pgpool locking
Hi, I'm using pgpool 2.2.2. and wanted to know if the following is expected behavior. Configuration: +-+ | (Server A) | | App ---> pgpool -> backend0 | +\+ \ +--\--+ | (Server B)\ | |`-> backend1 | +-+ Test: 1. Only Server A is running. Server B is down. 2. Start pgpool. 3. Try connecting to pgpool (e.g. pgsql -p ). At this point, client (psql) holds. I think it's at TCP level. However, I changed the tcp timeout values to be few minutes/seconds. 4. After the timeout, pgpool is then unlocked and allow connections. I can see pgpool doing this by design. I just wanted to know if it's this way and if would be possible to have pgpool just degenerate any backend that is not available within some configurable period of time. Regards, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Doubt with system_db.sql script
> Hi! > > i have a question about system_db.sql script included in pgpool. > > Really is needed execute this script for pgpool to replicate??? > > Which is the best database to install it??? template1?? postgres??? > > ok! maybe the question was a too newbie :-) , but i hope your > answers > > have a nice day! Hi, I know this is not the only set of questions you have, so I recommend you reading the pgpool-II tutorial: http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html then read this other one: http://linuxsilo.net/articles/postgresql-pgpool.html Answering your first question The turorial (first link above) states the following: "To enable parallel query in pgpool-II, you must set up another database called "System Database" (we will denote it as SystemDB from this point).". So no, system_db is used for parallel queries, and is not required for replication. Regards, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] start_recovery: remote start failed
> Hi!
>
> i have this trouble with pgpool-II 2.2.6 and start_recovery.
>
>
> when i tried to recover a node whit this command:
>
> pcp_recovery_node 5 pgsql1 9898 root rutilio 1
>
> and the log of pgpool is:
>
> 2010-01-27 09:24:12 LOG: pid 6036: CHECKPOINT in the 1st stage done
> 2010-01-27 09:24:12 LOG: pid 6036: 1st stage is done
> 2010-01-27 09:24:12 LOG: pid 6036: starting 2nd stage
> 2010-01-27 09:24:12 LOG: pid 6036: all connections from clients have
> been closed
> 2010-01-27 09:24:12 LOG: pid 6036: CHECKPOINT in the 2nd stage done
> 2010-01-27 09:24:12 ERROR: pid 6036: exec_remote_start:
> pgpool_remote_start failed: ERROR: function pgpool_remote_start(u
> nknown, unknown) does not exist
> LINE 1: SELECT pgpool_remote_start('pgsql2', '')
> HINT: No function matches the given name and argument types. You
might
> need to add explicit type casts.
>
> 2010-01-27 09:24:12 ERROR: pid 6036: start_recovery: remote start
> failed
>
>
> What can i do to solve this? maybe i have missing some settings???
Hi,
You need to install pgpool recovery stored procedures in all database
backends.
In your pgpool source dir, go to:
/usr/local/src/pgpool-II-2.2.6/sql/pgpool-recovery
There should be a file called pgpool-recovery.sql. Execute it in the
following way on all database backends:
psql -U postgres -d template1 -f pgpool-recovery.sql
If you already created a database, then execute it in that database by
replacing "template1" with the name of the database.
And try again.
Daniel
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool crashing(?) at startup
Sorry, I missed one step in the series: > > The problem is as follows: > > > > 1. pgpool is stopped > > 2. App is started > >Every 0.01 secs, outputs: > >psql: could not connect to server: Connection refused > > Is the server running locally and accepting > > connections on Unix domain socket "/tmp/.s.PGSQL."? > > > > 4. pgpool tries starting (app is trying connecting every 0.01 secs) > > 5. pgpool stop running. 1. pgpool is stopped 2. App is started 3. backend0 is up and backend1 is down (on purpose) 4. pgpool tries starting 5. pgpool stops. I still don't know if it is a crash or not, but it exits. I think it should still work with the only database available. Daniel > -Original Message- > From: Xavier Noguer [mailto:xnog...@antica.cl] > Sent: Thursday, January 21, 2010 4:24 PM > To: Crespo, Daniel @ SDS > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pgpool crashing(?) at startup > > > Hi, > > > 2010-01-21 14:31:56 ERROR: pid 10881: connection to > > 192.168.101.214(5432) failed > > 2010-01-21 14:31:56 ERROR: pid 10881: new_connection: create_cp() > > failed > > 2010-01-21 14:31:56 LOG: pid 10881: notice_backend_error: 1 fail > > over > > request from pid 10881 > > this seems to indicate the second backend is down or not configured > properly. For the 0.1 secs test do you use the exact same backends ? > > > 5. pgpool stop running. > > Does it crash ? Do you have the last lines on the log before it stops > ? > > > Regards > > On Thu, 2010-01-21 at 14:57 -0500, daniel.cre...@l-3com.com wrote: > > Hi, > > > > I'm using pgpool 2.2.2 connecting to two backends PGSQL8.2.1. Below > is a > > diagram of my configuration > > > > .---> backend0 > >/ > > App ---> pgpool > >\ > > `---> backend1 > > > > Where App, for testing purposes is: > > > > while [ 1 ]; do psql -U postgres -p -c "select 1;"; sleep 0.01; > > done > > > > The problem is as follows: > > > > 1. pgpool is stopped > > 2. App is started > >Every 0.01 secs, outputs: > >psql: could not connect to server: Connection refused > > Is the server running locally and accepting > > connections on Unix domain socket "/tmp/.s.PGSQL."? > > > > 4. pgpool tries starting (app is trying connecting every 0.01 secs) > > 5. pgpool stop running. > > > > Below is the output of log of pgpool > > > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: listen_addresses > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: '*' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: port > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_port > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 9898 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: socket_dir > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_socket_dir > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: backend_socket_dir > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_timeout > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 10 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: num_init_children > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 100 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: max_pool > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 1 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: child_life_time > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: connection_life_time > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: child_max_connections > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: client_idle_limit > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: authentication_timeout > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: 60 kind: 2 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: logdir > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: pid_file_name > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: > > '/var/run/pgpool/pgpool.pid' kind: 4 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: replication_mode > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: load_balance_mode > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: > replication_stop_on_mismatch > > 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 > > 2010-01-21 14:31:56 DEBUG: pid 10811: replication_stop_on_mismatch: 1 > > 2010-01-21 14:31:56 DEBUG: pid 10811: key: replicate_select > > 2010-01-21 14:31:56 DEBUG: pi
[Pgpool-general] pgpool crashing(?) at startup
Hi, I'm using pgpool 2.2.2 connecting to two backends PGSQL8.2.1. Below is a diagram of my configuration .---> backend0 / App ---> pgpool \ `---> backend1 Where App, for testing purposes is: while [ 1 ]; do psql -U postgres -p -c "select 1;"; sleep 0.01; done The problem is as follows: 1. pgpool is stopped 2. App is started Every 0.01 secs, outputs: psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL."? 4. pgpool tries starting (app is trying connecting every 0.01 secs) 5. pgpool stop running. Below is the output of log of pgpool 2010-01-21 14:31:56 DEBUG: pid 10811: key: listen_addresses 2010-01-21 14:31:56 DEBUG: pid 10811: value: '*' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: port 2010-01-21 14:31:56 DEBUG: pid 10811: value: kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_port 2010-01-21 14:31:56 DEBUG: pid 10811: value: 9898 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: socket_dir 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_socket_dir 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: backend_socket_dir 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: pcp_timeout 2010-01-21 14:31:56 DEBUG: pid 10811: value: 10 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: num_init_children 2010-01-21 14:31:56 DEBUG: pid 10811: value: 100 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: max_pool 2010-01-21 14:31:56 DEBUG: pid 10811: value: 1 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: child_life_time 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: connection_life_time 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: child_max_connections 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: client_idle_limit 2010-01-21 14:31:56 DEBUG: pid 10811: value: 0 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: authentication_timeout 2010-01-21 14:31:56 DEBUG: pid 10811: value: 60 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: logdir 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/tmp' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: pid_file_name 2010-01-21 14:31:56 DEBUG: pid 10811: value: '/var/run/pgpool/pgpool.pid' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: replication_mode 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: load_balance_mode 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: replication_stop_on_mismatch 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: replication_stop_on_mismatch: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: replicate_select 2010-01-21 14:31:56 DEBUG: pid 10811: value: false kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: replicate_select: 0 2010-01-21 14:31:56 DEBUG: pid 10811: key: reset_query_list 2010-01-21 14:31:56 DEBUG: pid 10811: value: 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: extract_string_tokens: token: ABORT 2010-01-21 14:31:56 DEBUG: pid 10811: extract_string_tokens: token: RESET ALL 2010-01-21 14:31:56 DEBUG: pid 10811: extract_string_tokens: token: SET SESSION AUTHORIZATION DEFAULT 2010-01-21 14:31:56 DEBUG: pid 10811: key: print_timestamp 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: master_slave_mode 2010-01-21 14:31:56 DEBUG: pid 10811: value: false kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: connection_cache 2010-01-21 14:31:56 DEBUG: pid 10811: value: false kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: health_check_timeout 2010-01-21 14:31:56 DEBUG: pid 10811: value: 20 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: health_check_period 2010-01-21 14:31:56 DEBUG: pid 10811: value: 5 kind: 2 2010-01-21 14:31:56 DEBUG: pid 10811: key: health_check_user 2010-01-21 14:31:56 DEBUG: pid 10811: value: 'postgres' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: failover_command 2010-01-21 14:31:56 DEBUG: pid 10811: value: ' . /var/lib/pgsql/replication/failover_command_trigger %h %d' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: failback_command 2010-01-21 14:31:56 DEBUG: pid 10811: value: ' . /var/lib/pgsql/replication/failback_command_trigger %h %d' kind: 4 2010-01-21 14:31:56 DEBUG: pid 10811: key: insert_lock 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: ignore_leading_white_space 2010-01-21 14:31:56 DEBUG: pid 10811: value: true kind: 1 2010-01-21 14:31:56 DEBUG: pid 10811: key: log_state
Re: [Pgpool-general] Cannot trigger out-of-sync
I'm also getting this exact behavior. Any clue? Thanks, Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Ger Apeldoorn > Sent: Thursday, January 14, 2010 2:56 AM > To: pgpool-general@pgfoundry.org > Subject: [Pgpool-general] Cannot trigger out-of-sync > > Hi, > > I want to trigger an out-of-sync for a pgpool cluster in our > test-environment, but am unable to do it. > > As I understood it, if pgpools get a different number of rows back from > a query, it should trigger an out of sync for one of the databases. > > This is what I did: > 1) Insert new record directly in the local postgres database: > psql -p5433 my_database -c "insert into users (id, name, description, > password, field) values (1,'gap','Ger','hihaho', 1);" > > 2) Update this record through PGPool: > psql -p my_database -c "update users set name = 'Harry' where id = > 1;" > > This generates the following error: > ERROR: pgpool detected difference of the number of inserted, updated > or > deleted tuples. Possible last query was: "update users set name = > 'Harry' where id = 1;" > HINT: check data consistency between master and other db node > > Unfortunately, no out-of-sync status is being set: > [r...@db3 ~]# pcp_node_info 30 node1 9898 pgpool fake_password 1 > node2 5433 2 1073741823.50 > [r...@node2 ~]# pcp_node_info 30 node1 9898 pgpool fake_password 0 > node1 5433 2 1073741823.50 > > How can this be? Is there another (better) way to trigger a failure? > > Thanks again, > Ger. > > > > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] which is advantage of master/slave mode vsreplication on pgpool-II
In addition to that, I would say that first of all, you need to know what you need: Synchronous replication or Asynchronous replication. Slony-I is Asynchronous Pgpool-II is Synchronous You should not compare Slony-I vs Pgpool-II. Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Steven Crandell Sent: Thursday, January 07, 2010 3:18 PM To: erobles Cc: pgpool Subject: Re: [Pgpool-general] which is advantage of master/slave mode vsreplication on pgpool-II The details of master/slave vs replication modes are detailed pretty well here: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html In general, master/slave mode assumes that there is some other replication software involved replicating data from the master to the slaves (slony, mammoth, etc) Replication mode means that pgpool will take care of the replication itself. I'm a big fan of the mammoth replication system so the advantage of master/slave replication mode for me is that I can use pgpool to load balance my queries while still counting on mammoth to handle my replication. I haven't use pgpool in replication mode so I can't speak to any strengths or weaknesses there. It basically comes down to apples vs. oranges. It's two completely different methods to accomplish basically the same thing. -s On Thu, Jan 7, 2010 at 9:15 AM, erobles wrote: hi! like the subjects said, i am interesting on the advantages of pgpool in master/slave mode over replication mode regards! ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Consistency issues
> Hi Fernando, > > Thanks for the info. Oh, pgpool's recovery process connects to > template1. That was the cause of the problem. Pgpool should connects > to "postgres" database instead of template1, I think. Will fix. > -- > Tatsuo Ishii Was this introduced in 2.3.x or is this same behavior occurring on all pgpool-II versions? If it is not too much to ask, what does pgpool-II do with template1 database (that seems to be wrong and therefore postgres database will be used)? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Multiple statements support
Hi, Can someone confirm that multiple statements as shown below is supported by pgpool-II? "update table1 set a=1 where a=0; update table2 set b=2 where b=0;" Unfortunately, I don't have a system available to test. Thanks in advance, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] load balancing and autocommit?
I'm not familiar with JDBC, but I think it should be setable. I googled it and below seems to be the answer. Connection connection = dataSource.getConnection(); Connection.setAutoCommit(false);// Disables auto-commit. Taken from (http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/ rzaha/transaut.htm) Regards, Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- > boun...@pgfoundry.org] On Behalf Of Kevin Kempter > Sent: Tuesday, December 01, 2009 4:26 PM > To: pgpool-general@pgfoundry.org > Subject: [Pgpool-general] load balancing and autocommit? > > > > Hi all; > > I have a client using JDBC, we want to use PG POOL II for connection > pooling, > master-slave mode (with SLONY on the db servers) and load balancing > (send all > of our reads to the SLONY slave. However we do NOT wish to use JDBC > autocommit > since that will break our ability to rollback a block of work. > > I'll have a look at the code, in the meanwhile does > anyone know of any work-arounds / have any ideas? > > > Thanks in advance > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] saving state across pgpool restarts?
> > > > If pgpool-ii is restarted, it will consider that all backends are > in > > > "good" > > > > state - even if one of the backends was not up to date (and data > was > > > not > > > > being replicated there). > > > > > > > > What is the best way to preserve backend state across pgpool-ii > > > restarts? > > > > > > IMHO, making sure the PostgreSQL daemon is stopped when a failover > is > > > done on a node. > > > > It seems that there is not a good solution for this. > > > > For example, let's say I have two backends: backend0 and backend1. > > > > 1. Network connection error happens on backend0. > > 2. backend0 is denerated. > > 3. New data incomes into pgpool->backend1. > > 4. backend0 is now out of sync. > > 5. Network connection is re-established on backend0. > > 6. pgpool is restarted for some reason. > > 7. pgpool connects to both backends. > > 8. a client executes a query that causes to retrieve data from both > > backends, but are mismatching. > > 9. pgpool degenerate the one that is NOT the master -> backend1. > > 10. DBA gets crazy because data was lost (in the backend1, when > backend0 > > connection was down) > > > > I think pgpool should keep track of which database has data with > latest > > timestamp in order to degenerate the oldest one. Is it possible? > > Or any ideas as to how to solve or mitigate this? > > I think it's fairly easy to implement persistent state file for > pgpool-II. The state file, possibly called "pgpool_status", is created > under logdir if it does not exist. The contents will be backend id, > its sttaus and timestamp per line. If pool_status exists, pgpool will > read the file before starting health checking and set it to shared > memeory status area. If fail over/fail back events happen, pgpool will > update the file. That sounds like it would work. I guess that after synchronizing databases and re-attaching the degenerated database, I would delete the pgpool_status file for starting fresh. Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] saving state across pgpool restarts?
Thanks for your reply, Jaume. Below are some comments. > On Thu, Oct 29, 2009 at 6:21 PM, wrote: > > > It seems that there is not a good solution for this. > > For example, let's say I have two backends: backend0 and backend1. > > > > 1. Network connection error happens on backend0. > > 2. backend0 is denerated. > > 3. New data incomes into pgpool->backend1. > > 4. backend0 is now out of sync. > > 5. Network connection is re-established on backend0. > > 6. pgpool is restarted for some reason. > > 7. pgpool connects to both backends. > > 8. a client executes a query that causes to retrieve data from both > > backends, but are mismatching. > > 9. pgpool degenerate the one that is NOT the master -> backend1. > > 10. DBA gets crazy because data was lost (in the backend1, when > backend0 > > connection was down) > > I think that you are going over the top by missing a few key points: > > 2. After this event, pgsql in node 0 should be shut down by failover > script configured in pgpool-II or any other mechanism. > > 6. "For some reason" seems like kids playing around to me. The reason was not relevant for the explanation. > I can only see this happening if all of the following are true: > a) No backup power available. > b) Electricity goes down. > c) No sysadmin receives an alert of what has happened There you go! > If you meant a software restart of the daemon, then you have a problem > with humans, not with software. Please, imagine a more suitable scenario. Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] saving state across pgpool restarts?
> > If pgpool-ii is restarted, it will consider that all backends are in > "good" > > state - even if one of the backends was not up to date (and data was > not > > being replicated there). > > > > What is the best way to preserve backend state across pgpool-ii > restarts? > > IMHO, making sure the PostgreSQL daemon is stopped when a failover is > done on a node. It seems that there is not a good solution for this. For example, let's say I have two backends: backend0 and backend1. 1. Network connection error happens on backend0. 2. backend0 is denerated. 3. New data incomes into pgpool->backend1. 4. backend0 is now out of sync. 5. Network connection is re-established on backend0. 6. pgpool is restarted for some reason. 7. pgpool connects to both backends. 8. a client executes a query that causes to retrieve data from both backends, but are mismatching. 9. pgpool degenerate the one that is NOT the master -> backend1. 10. DBA gets crazy because data was lost (in the backend1, when backend0 connection was down) I think pgpool should keep track of which database has data with latest timestamp in order to degenerate the oldest one. Is it possible? Or any ideas as to how to solve or mitigate this? Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] saving state across pgpool restarts?
> If pgpool-ii is restarted, it will consider that all backends are in > "good" state - even if one of the backends was not up to date (and data > was not being replicated there). > > > What is the best way to preserve backend state across pgpool-ii > restarts? I'm also interested in knowing this Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool-II replication - issue when connecting with pgadmin3
> There are two problems with the SQL created by pgAdmin: > > - multi statement (two or more SQLs per 1 line) > Running "select 1; select 2;" into pgpool will cause a problem? What is the point of not being able to execute multi-statements? Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Error: pgpool detected difference of thenumber of...
Thanks for the reponse, Xavier. I forgot to mention that when I issue pcp_node_info for backend0, it returns with a long string that contains the well known '2', meaning its connected. When I do it for backend1 is says BackendError. BackendError is shown when you are trying to get info from a backend that is not set up, or when another error (unknown to me at this point) if the backend is there, but experiences some problem. I expect pgpool to ignore backend1 since it presents errors. Also, if you issue an update and pgpool says that there's a difference between backends, it should degenerate the backend1 and above, but it doesn't. I just try to keep making the same update, and every time pgpool complains, instead of degenerate the backends and carry on with only backend0. Any other idea? Thanks, Daniel > -Original Message- > From: Xavier Noguer [mailto:xnog...@antica.cl] > Sent: Monday, September 28, 2009 1:51 PM > To: Crespo, Daniel @ SDS > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] Error: pgpool detected difference of > thenumber of... > > > That error usually appears when there's a difference between backends. > You should compare the number of affected rows on each backend. If the > query you are running is: > > UPDATE table SET field1=’something’ WHERE field2=’something_else’; > > then you should run: > > SELECT COUNT(*) FROM table WHERE field2=’something_else’; > > connecting directly to each backend. > > Once you have discovered what the difference is, you can synchronize > backends using pcp_recovery_node. But you have to decide which backends > are corrupt and which backends have valid data before you run > pcp_recovery_node. > > > Cheers > > On Mon, 2009-09-28 at 13:31 -0400, daniel.cre...@l-3com.com wrote: > > When connecting through pgpool, the query: > > > > > > > > UPDATE table SET field1=’something’ WHERE field2=’something_else’; > > > > > > > > throws the following error: > > > > > > > > ERROR: pgpool detected difference of the number of INSERTS, DELETES, > > or UPDATES …(etc. I can’t recall the rest of the error message, but I > > know it has something like “type ‘E’ ” > > > > > > > > If I connect directly to postgresql, the query is executed > > successfully. > > > > > > > > I have not been able to test updating other tables because the system > > is not available for me for a while. > > > > > > > > I’m using pgpool-II 2.2.1 > > > > > > > > Any ideas? > > > > > > > > Thanks, > > > > Daniel > > > > > > ___ > > Pgpool-general mailing list > > Pgpool-general@pgfoundry.org > > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Error: pgpool detected difference of the number of...
When connecting through pgpool, the query: UPDATE table SET field1='something' WHERE field2='something_else'; throws the following error: ERROR: pgpool detected difference of the number of INSERTS, DELETES, or UPDATES ...(etc. I can't recall the rest of the error message, but I know it has something like "type 'E' " If I connect directly to postgresql, the query is executed successfully. I have not been able to test updating other tables because the system is not available for me for a while. I'm using pgpool-II 2.2.1 Any ideas? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_recovery_node
> 2009-09-15 12:04:00 LOG: pid 3983: starting recovery command:
"SELECT
> pgpool_recovery('startbackup1', 'back2',
> '/var/lib/postgresql/8.3/main')"
> 2009-09-15 12:04:00 DEBUG: pid 3983: exec_recovery: start recovery
> 2009-09-15 12:04:00 ERROR: pid 3983: exec_recovery: startbackup1
> command
> failed at 1st stage
>
> What could be possible mistakes? How could i get some detailed debug
> message?
The user running pgpool might affect pgpool's scope to access the file
specified in failover_command, including permissions. Also, remember
that in failover_command, if you don't specify full path, it will look
for it locally to wherever pgpool executable is running.
Daniel
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp stats
> > Im using pgpool for 3 backends that have different weights. Its a > > replication/load-balancing setup. Is there any way to show the number > of > > connections/queries that have been sent to each of the backends? I > just want > > to verify that the weighing mechanism works correctly. > > Check the active connections/queries sent on each of the PostgreSQL > nodes? You can do so by doing: select * from pg_stat_activity; on each postgresql backend. Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Is pgpool compatible with protocolVersion=3 ofjdbc?
Are you able to create a table running slony-I (without using pgpool)?
AFAIK, you can not create tables by issuing SQL statements to an open
session of postgresql when it's being replicated by slony-I.
Daniel
From: pgpool-general-boun...@pgfoundry.org
[mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Nimesh Satam
Sent: Friday, August 28, 2009 12:32 AM
To: Tatsuo Ishii
Cc: pgpool-general@pgfoundry.org
Subject: Re: [Pgpool-general] Is pgpool compatible with
protocolVersion=3 ofjdbc?
Hi Tatsuo,
Can you please let us know what might be going wrong here or if you need
any further information?
Regards,
Nimesh.
On Wed, Aug 26, 2009 at 10:07 AM, Nimesh Satam
wrote:
Tatsuo,
>> What error do you have with temp table creation?
When we try to create temp table using pgpool, the application fails
with below error:
"ERROR: kind mismatch among backends. Possible last query was: "..."
kind details are: 0[1] 1[E]" error.
We are using pgpool in master-slave mode and loadbalance=on.
On execution of the below queries the above error appears:
1) CREATE TEMP TABLE tmp_ch (key integer PRIMARY KEY);
2) INSERT INTO tmp_ch (key) SELECT key FROM rpt_ch WHERE id IN (135848,
135841);
Note: in logs we see the first query getting Parsed on master and slave
database. But the execute is done only on slave. As a result the 2 query
fails.
Let me know if you need any information
Regards,
Nimesh.
On Wed, Aug 26, 2009 at 5:52 AM, Tatsuo Ishii
wrote:
> Is there any significant difference in using pg connection string
> "protocolVersion=2"?
>
> By default pg driver uses protocol V3, this causes the temp table
creation
> from application to fail.
What error do you have with temp table creation?
> If we change the connection string to
> "protocolVersion=2" everything works fine
>
> eg: Connection c =
>
DriverManager.getConnection("jdbc:postgresql://db_host:db_port/reporting
?protocolVersion=2","car",
> "");
>
> Is there any negative impact of using protocolversion 2.
Yes. Protocolversion 2 is inefficient comparing with 3. Also you will
lose some important features those are available with only 3. For
example, load balance, insert lock etc.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Vacuuming
Hi, I think vacuum works properly, but just in case I ask: is there any limitations when running "vacuum;" within a pgpool-II session? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Order of execution of queries by pgpool
> > When inserting, updating or deleting through pgpool connected to two > > backends, is the order of commits: > > > > 1. Backend 0, and then Backend 1? > > 1. Execute in backend 1 > 2. Wait for execution. > 3. If okay, execute (paralelly, I believe) in the rest of backends (in > this case only in backend 0). > 4. If okay, return okay to client. So, if there are 3 backends (0, 1, and 2), the order will be: 1. Execute in the last backend (backend 2) 2. Wait for execution. 3. If okay, execute (paralelly) in the rest of backends (in this case in backend 0 and 1) 4. If okay, return okay to client. ? or 1. Execute in the last backend (backend 1) 2. Wait for execution. 3. If okay, execute (paralelly) in the rest of backends (in this case in backend 0 and 2) 4. If okay, return okay to client. ? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Order of execution of queries by pgpool
When inserting, updating or deleting through pgpool connected to two backends, is the order of commits: 1.Backend 0, and then Backend 1? 2."at the same time" (in two independent threads) and waits for both to be successful in order to return a successful transaction to the client? If the answer is 1, why not 2? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] pgpool and PgCon 2009
Hi all, I received an e-mail from PgCon 2009 saying that pgpool-II (Tatsuo) is not going to be present at the conference. Is this confirmed? I'm attending the conference, and I was really looking forward to that talk Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Able to do online recovery for failed nodes but not to attach new node
> Notice in the second case it is using "localhost" as the parameter (and localhost is not the node I'm trying to recover). I notice that if I start pgpool with only one backend, then I add a backend to the conf file, then do pgpool reload, it does read it correctly, eg, I see I think you should never configure pgpool.conf to have backendN='localhost', unless it's the only node in the cluster. You should specify an IP for each backend. This information is used for doing recovery. Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] archive_command question
Very good! I think it's very useful. Thanks for sharing! Daniel -Original Message- From: Jaume Sabater [mailto:jsaba...@gmail.com] Sent: Wednesday, April 15, 2009 5:12 AM To: Tatsuo Ishii Cc: Crespo, Daniel @ SDS; pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] archive_command question 2009/4/15 Tatsuo Ishii : > Probably what we need is "recovery_end_command" to be executed at the > very end of recovery. I thought that was the fallback_command command. Not that I am using it right now as I'd rather do that manually, but I thought it could be used for that purpose. > recovery_1st_stage_command enables archive_command and > recovery_end_command disables archive_command and remove archive logs > etc. I use a wall_archiving script, which is attached to this email for reference. Still, would be useful :) -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] archive_command question
I know this is a question for postgresql forum, but I think is convenient to know it in this forum as well. Does the 'archive_command' run if and only if the postgresql stored procedure named 'pg_start_backup' is called? My concern is that if archiving is enabled, the backup files would fill the disk space. If instead, every time I'm going to recover a failed node, I would just need to empty the archive directory before issuing pg_start_backup. Does this make sense? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Is there a option
You are welcome. > So this is done by using pcp_recovery_node command? Yes. That command will trigger everything I explained. Also, be aware that your database needs to have the pgpool stored procedures installed in order for it to work. And for them to be installed, your database needs to have the proper pl languages installed, I don't remember which one, but got to be either plpgsql or plperlu. I think it's the former. Read the pgpool tutorial for info on all this. Daniel From: DM [mailto:dm.a...@gmail.com] Sent: Tuesday, March 17, 2009 4:57 PM To: Crespo, Daniel @ SDS Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Is there a option Thanks Daniel, Great Answer. So this is done by using pcp_recovery_node command? Thanks Deepak On Tue, Mar 17, 2009 at 1:30 PM, wrote: Let's say you have two backends: backend0 and backend1. Supposing backend1 has failed, recoverying it (by re-synchronizing data from backend0) is expected to have a pgpool client reconnection. From the point of view of pgpool, the clients must disconnect and reconnect in order to complete a data synchronization. However, this disconnection can be very quick. The interesting part is that data synchronization (a kind of copy of the postgresql data directory) will happen while backend0 is available por reads and writes. At the end of the data transfer (1st stage), a postgresql feature (WAL archiving) helps with the synchronization of newer data (2nd stage) that has been transferred at the 1st stage. At 2nd stage is when clients must disconnect and reconnect. Pgpool has the ability to normally accept connections during 1st stage. At 2nd stage, pgpool waits until clients disconnect or until client_idle_limit_in_recovery seconds happen. Then clients get disconnected. If clients try to connect while 2nd stage happen, pgpool will block them until it finishes. Clients won't get disconnected, just will sit waiting until connection happens. I hope this helps. Daniel From: Crespo, Daniel @ SDS Sent: Tuesday, March 17, 2009 4:05 PM To: 'DM'; pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] Is there a option Yes. Look for the following keywords: pcp_recovery_node (pgpool client program) client_idle_limit_in_recovery (parameter in pgpool.conf) pgpool_copy_backup (script) pgpool_switch_xlog (script) pgpool_remote_start (script) Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of DM Sent: Tuesday, March 17, 2009 2:57 PM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Is there a option Hi All, Sorry I wanted to ask the question the other way round. In Pgpool is it possible to recover / sync the 2nd DB without quiescing the first. Thanks Deepak On Tue, Mar 17, 2009 at 9:48 AM, DM wrote: Hi All, In pgpool Is it possible to do quiescing of a db while (recovering)/syncing the 2nd DB? Thanks Deepak ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Is there a option
Let's say you have two backends: backend0 and backend1. Supposing backend1 has failed, recoverying it (by re-synchronizing data from backend0) is expected to have a pgpool client reconnection. From the point of view of pgpool, the clients must disconnect and reconnect in order to complete a data synchronization. However, this disconnection can be very quick. The interesting part is that data synchronization (a kind of copy of the postgresql data directory) will happen while backend0 is available por reads and writes. At the end of the data transfer (1st stage), a postgresql feature (WAL archiving) helps with the synchronization of newer data (2nd stage) that has been transferred at the 1st stage. At 2nd stage is when clients must disconnect and reconnect. Pgpool has the ability to normally accept connections during 1st stage. At 2nd stage, pgpool waits until clients disconnect or until client_idle_limit_in_recovery seconds happen. Then clients get disconnected. If clients try to connect while 2nd stage happen, pgpool will block them until it finishes. Clients won't get disconnected, just will sit waiting until connection happens. I hope this helps. Daniel From: Crespo, Daniel @ SDS Sent: Tuesday, March 17, 2009 4:05 PM To: 'DM'; pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] Is there a option Yes. Look for the following keywords: pcp_recovery_node (pgpool client program) client_idle_limit_in_recovery (parameter in pgpool.conf) pgpool_copy_backup (script) pgpool_switch_xlog (script) pgpool_remote_start (script) Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of DM Sent: Tuesday, March 17, 2009 2:57 PM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Is there a option Hi All, Sorry I wanted to ask the question the other way round. In Pgpool is it possible to recover / sync the 2nd DB without quiescing the first. Thanks Deepak On Tue, Mar 17, 2009 at 9:48 AM, DM wrote: Hi All, In pgpool Is it possible to do quiescing of a db while (recovering)/syncing the 2nd DB? Thanks Deepak ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Is there a option
Yes. Look for the following keywords: pcp_recovery_node (pgpool client program) client_idle_limit_in_recovery (parameter in pgpool.conf) pgpool_copy_backup (script) pgpool_switch_xlog (script) pgpool_remote_start (script) Daniel From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of DM Sent: Tuesday, March 17, 2009 2:57 PM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Is there a option Hi All, Sorry I wanted to ask the question the other way round. In Pgpool is it possible to recover / sync the 2nd DB without quiescing the first. Thanks Deepak On Tue, Mar 17, 2009 at 9:48 AM, DM wrote: Hi All, In pgpool Is it possible to do quiescing of a db while (recovering)/syncing the 2nd DB? Thanks Deepak ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Transactions not working properly from ODBC
It seems that there's no PostgreSQL ODBC driver that supports version 3 protocol, since the one I have is the latest I could find. If you know where can I find it, would be great! I'll keep searching anyway. Thanks, Daniel -Original Message- From: Jaume Sabater [mailto:jsaba...@gmail.com] Sent: Thursday, March 12, 2009 4:20 AM To: Tatsuo Ishii Cc: Crespo, Daniel @ SDS; pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Transactions not working properly from ODBC On Thu, Mar 12, 2009 at 1:23 AM, Tatsuo Ishii wrote: > The difference between psql and ODBC is the protocol version > number. Namely psql speaks version 3 which is standard protocol since > PostgreSQL 7.4, while your ODBC driver speaks version 2, which is > pretty old. > > Unfortunately pgpool-II 2.2 seems to have a problem with insert_lock > handling used with version 2 protocol. Don't want to sound like a pain in the neck but, is it worth supporting version 2 these days? -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Transactions not working properly from ODBC
Hi again, Just in case, I tested connecting with isql (the psql-like odbc interface) directly to PostgreSQL and there's no problem at all. I guess there's something pgpool doesn't like that is received from odbc layer. Any clue? Thanks, Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of daniel.cre...@l-3com.com Sent: Wednesday, March 11, 2009 5:48 PM To: pgpool-general@pgfoundry.org Subject: [Pgpool-general] Transactions not working properly from ODBC Hi, I have a very particular problem here. I have two odbc configurations to access PostgreSQL through pgpool and directly to PostgreSQL. Bottomline is that INSERTs within a transaction block using ODBC to pgpool, does not work. Here's what I have: Pgpool 2.2beta1 PostgreSQL 8.2.1 unixODBC 2.2.11 The SQL statement is simple: BEGIN; INSERT INTO test VALUES (1); COMMIT; >From isql (odbc interface, it's analogous to psql): [r...@coyote ~]# isql testdb +---+ | Connected!| | | | sql-statement | | help [tablename] | | quit | | | +---+ SQL> begin; SQLRowCount returns -1 SQL> insert into test values (6); [ISQL]ERROR: Could not SQLExecute SQL> quit The related logged error in pgpool is: >From psql: [r...@coyote ~]# psql -U postgres -d testdb -p Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit testdb=# begin; BEGIN testdb=# insert into test values (5); INSERT 0 1 testdb=# commit; COMMIT testdb=# \q The problem seems to be in ODBC, but it seems to happen ONLY when pgpool is the connection interface to PostgreSQL. Any clue? Attached are the log files. Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool-II not starting after stopping it
Any news on this? I have checked it on pgpool-II 2.2beta1 and still happens. Any workaround or suggestions? Thanks, Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > Jaume Sabater > Sent: Friday, January 16, 2009 2:41 AM > To: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pgpool-II not starting after stopping it > > On Thu, Jan 15, 2009 at 5:41 PM, wrote: > > > This is happening to me a lot of times: > [..] > > 2009-01-15 11:33:12 ERROR: pid 1169: bind(0.0.0.0:distinct) > failed. reason: > > Address already in use > > I've seen this happening on my 2.1 installation, too. Several times. > The socket is still there, taken, and the new started process cannot > get hold of it, therefore it cannot "start". I have not investigated > the reason, but it always happened when pgpool-II was under (heavy) > load when I stopped it. > > -- > Jaume Sabater > http://linuxsilo.net/ > > "Ubi sapientas ibi libertas" > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] documentation for beginners
Exactly :) > -Original Message- > From: Glyn Astill [mailto:glynast...@yahoo.co.uk] > Sent: Wednesday, February 18, 2009 9:51 AM > To: Gerd König; pgpool-general@pgfoundry.org; Crespo, Daniel @ SDS > Subject: Re: [Pgpool-general] documentation for beginners > > Yeah, > > Although our pool is setup and fine, I bet there's something > it can teach me. > > > --- On Wed, 18/2/09, daniel.cre...@l-3com.com > wrote: > > > From: daniel.cre...@l-3com.com > > Subject: Re: [Pgpool-general] documentation for beginners > > To: "Gerd König" , > pgpool-general@pgfoundry.org > > Date: Wednesday, 18 February, 2009, 2:14 PM > > Hi, Gerd. > > > > I'm interested. Thanks! > > > > Daniel > > > > > -Original Message- > > > From: pgpool-general-boun...@pgfoundry.org > > > [mailto:pgpool-general-boun...@pgfoundry.org] On > > Behalf Of Gerd König > > > Sent: Wednesday, February 18, 2009 7:45 AM > > > To: pgpool-general@pgfoundry.org > > > Subject: [Pgpool-general] documentation for beginners > > > > > > Hello, > > > > > > while evaluating pgpool-II in the last days I wrote > > down (almost) > > > everything to bring pgpool up and running. > > > > > > The documentation covers installation, configuration > > > "replication mode", > > > online recovery, testing > > > > > > Thanks to Tatsuo for a first review. > > > If somebody is interested in the documentation, please > > drop me a note > > > and I'll send it to you (since I dont't want > > to spam the list > > > I thought > > > it would be better sending it on demand) > > > > > > Any hints, comments highly appreciated. > > > > > > regards.GERD. > > > ___ > > > Pgpool-general mailing list > > > Pgpool-general@pgfoundry.org > > > http://pgfoundry.org/mailman/listinfo/pgpool-general > > > > > ___ > > Pgpool-general mailing list > > Pgpool-general@pgfoundry.org > > http://pgfoundry.org/mailman/listinfo/pgpool-general > > > > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] documentation for beginners
Hi, Gerd. I'm interested. Thanks! Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Gerd König > Sent: Wednesday, February 18, 2009 7:45 AM > To: pgpool-general@pgfoundry.org > Subject: [Pgpool-general] documentation for beginners > > Hello, > > while evaluating pgpool-II in the last days I wrote down (almost) > everything to bring pgpool up and running. > > The documentation covers installation, configuration > "replication mode", > online recovery, testing > > Thanks to Tatsuo for a first review. > If somebody is interested in the documentation, please drop me a note > and I'll send it to you (since I dont't want to spam the list > I thought > it would be better sending it on demand) > > Any hints, comments highly appreciated. > > regards.GERD. > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] PgPool2.2Beta2 and pgpoolAdmin-2.2-beta1
The defaulted value of "nobody" is for health_check_user in pgpool.conf, and must exist as a postgresql user in the backends. I changed it to postgres, and works fine. I don't use system_db, so I don't need a user for it. It's a matter of having the users in the backend engines, that's all. Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Glyn Astill > Sent: Thursday, February 12, 2009 1:13 PM > To: Tatsuo Ishii > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] PgPool2.2Beta2 and pgpoolAdmin-2.2-beta1 > > --- On Thu, 12/2/09, Tatsuo Ishii wrote: > > > > The login user and password for pgpoolAdmin is defined in > > pcp.conf. > > Not that login, I can login to the pgpoolAdmin page okay. > > It appears to use the user "pgpool" to check the and "nobody" > to check "Node status" and to access the cluster settings > "System DB" (which I don't use) > > I.e.: > > 2009-02-12 18:10:59 GMT [21371]: [1-1]: [user=pgpool]: > [host=127.0.0.1]: [db=pgpool]:: FATAL: database "pgpool" > does not exist > 2009-02-12 18:11:02 GMT [21377]: [1-1]: [user=nobody]: > [host=192.168.1.111]: [db=template1]:: FATAL: role "nobody" > does not exist > > > > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] some basic questions
Hi, Jaume I'm also interested in having that tutorial. Is it possible for you to send the URL to me as well? Thanks! Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > Jaume Sabater > Sent: Wednesday, February 04, 2009 7:00 AM > To: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] some basic questions > > 2009/2/4 Gerd König : > > > It would be very nice if you could send your documentation to me > > directly, I'm pretty sure that I'll find somebody for > translating it ;-) > > I'll send you a URL as soon as I have it online. If I take too much > time, send me an email again. Give me a few days, though. > > >> You may want to try online recovery with PostgreSQL's PITR. > >> > > A, this means in addition to the replication I have to > use PITR to > > copy the wal files to node2 ? Strange, I haven't thought of > using two > > "techniques" at once ;-)) > think there are better ways ;-) > > No. It means you want to use WAL archiving to archive logs, then > transfer them, then use PITR so that, when the node being recovered is > starting, it reads those WAL files and processes them to bring itself > back to speed with the master node. > > I strongly suggest that you read the online documentation at > postgresql.org regarding WAL and PITR before getting started with > pgpool. It's not easy, but it's a must that you understand it. Still, > you don't really need to use PITR. It's an improvement IMHO, but not a > requirement. > > -- > Jaume Sabater > http://linuxsilo.net/ > > "Ubi sapientas ibi libertas" > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Location of pgpool.pid
Hello, list. I'm finally using the latest CVS version of pgpool-II. One question: is it proper to leave the creation of pgpool.pid under /tmp directory? I suppose it is defaulted to /tmp for suiting everybody's needs... Any thoughts? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
Definitely, my box is not good: I tested UNinstalling pgpool-II-2.1 from the current servers that I'm using, and INstalled the latest CVS version (I got it today). Everything worked exactly as Marcelo said. Life is good. I have no idea what is wrong with my box, but exactly the same thing happened when I transitioned from 2.0.1 to 2.1: I had to reimage. The uninstallation process would work I don't know why. I did this same uninstallation process on the servers I'm working on, and everything is working. Thanks a lot, Marcelo, for enlighten me. Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > daniel.cre...@l-3com.com > Sent: Wednesday, January 21, 2009 1:33 PM > To: Marcelo Martins > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > > Thanks for your response, I really appreciate it. > > > First, I don't really agree on just attaching a node back into the > > pool the manner your are doing with the steps shown below. If a > > postgreSQL backend node goes down, for some reason out of anyone's > > control, you should bring that node back into the pool by using > > online_recovery, that's why that mechanism is in place. > > > > Now there are times that we may need to purposely take one of the > > postgreSQL backend nodes down, (I agree on that) but when that is > > the case one should have in place some maintenance > > procedures. There > > are several scenarios though depending on your setup. You > > may need to > > keep your environment in read/write mode at all times which > > means you > > would use the pcp utilities to detach the PG node, do whatever you > > need to do and then use the pcp online recovery to bring that node > > back on the pool. (not pcp attach) > > If you happen to be able to have your environment in > read-only mode > > then you could use the pcp detach to take the backend node > > out of the > > pool and then then use pcp attach to bring that node back > > into the pool. > > I understand your point and that's what I think too. But my > example only > shows unit testing. > > My real case is as follows: > > I have 2 or 4 server configuration. > > 2-server configuration: > Application and DB run in each server > > 4-server configuration: > Application run in two of the servers > DB run in the other two servers. > > Pgpool-II would run only in the server where the application > is running > (a total of 2), but only one application would be active at a > time. The > applications would always connect to localhost port . > > In any case, when we are installing the applications and DBs, it's > always done one at a time (this is the procedure and can not currently > be changed). > > The worst case scenerio for pgpool is at installation time > with 2-server > setup: > 1. Install first server (App & pgpool and DB) > 2. Install second server (App & pgpool and DB) > > For changes to take effect, the installation reboots the server (don't > ask me... It's the way it has been and takes a lot of time/money to > replace this procedure). So imagine it: > At the end of step 1, the system reboots. When it comes up, only the > first of the two servers is up; the other one does not have even IP > address set. Pgpool starts and sees that there's no secondary > database. > With failover_command I trigger a script that would look for > availability of the secondary database. > At the end of step two, after rebooting, secondary server is up and > running. Its pgpool will successfully connect to both databases since > the first one is already up. However, the script running in > the primary > server detects that there's the secondary database running (I > check for > specific tables in the database, so I know it's up and ready > for running > application requests). If specific data in tables are not the same > between primary and secondary database for any reason, I will do > *manual* pcp recovery; otherwise (which is the most likely to > happen at > installation time since it has been just installed and both databases > should have the same data), do pcp attach. > > Why don't I do pcp recovery in all cases? Because pcp > recovery requires > no connections from the application at the second stage of > the recovery. > With the release that is working for me (2.1) I can not disconnect > clients at second stage only (using > client_idle_limit_in_recovery in the > latest copy of pgpool-II), so I need to close the application on > purpose. Therefore, I need manual recovery. In this regard, > I'm going to > re-image my development box and install a fresh latest CVS version of > pgpool-II, because something funny like this happened when I went from > 2.0.1 to 2.1, so... No clue. The thing is that I'm running > out of time. > > In conclusion, it should not behave the way it does when I > disconnect a > backend
Re: [Pgpool-general] pcp_attach_node problem?
Thanks for your response, I really appreciate it. > First, I don't really agree on just attaching a node back into the > pool the manner your are doing with the steps shown below. If a > postgreSQL backend node goes down, for some reason out of anyone's > control, you should bring that node back into the pool by using > online_recovery, that's why that mechanism is in place. > > Now there are times that we may need to purposely take one of the > postgreSQL backend nodes down, (I agree on that) but when that is > the case one should have in place some maintenance > procedures. There > are several scenarios though depending on your setup. You > may need to > keep your environment in read/write mode at all times which > means you > would use the pcp utilities to detach the PG node, do whatever you > need to do and then use the pcp online recovery to bring that node > back on the pool. (not pcp attach) > If you happen to be able to have your environment in read-only mode > then you could use the pcp detach to take the backend node > out of the > pool and then then use pcp attach to bring that node back > into the pool. I understand your point and that's what I think too. But my example only shows unit testing. My real case is as follows: I have 2 or 4 server configuration. 2-server configuration: Application and DB run in each server 4-server configuration: Application run in two of the servers DB run in the other two servers. Pgpool-II would run only in the server where the application is running (a total of 2), but only one application would be active at a time. The applications would always connect to localhost port . In any case, when we are installing the applications and DBs, it's always done one at a time (this is the procedure and can not currently be changed). The worst case scenerio for pgpool is at installation time with 2-server setup: 1. Install first server (App & pgpool and DB) 2. Install second server (App & pgpool and DB) For changes to take effect, the installation reboots the server (don't ask me... It's the way it has been and takes a lot of time/money to replace this procedure). So imagine it: At the end of step 1, the system reboots. When it comes up, only the first of the two servers is up; the other one does not have even IP address set. Pgpool starts and sees that there's no secondary database. With failover_command I trigger a script that would look for availability of the secondary database. At the end of step two, after rebooting, secondary server is up and running. Its pgpool will successfully connect to both databases since the first one is already up. However, the script running in the primary server detects that there's the secondary database running (I check for specific tables in the database, so I know it's up and ready for running application requests). If specific data in tables are not the same between primary and secondary database for any reason, I will do *manual* pcp recovery; otherwise (which is the most likely to happen at installation time since it has been just installed and both databases should have the same data), do pcp attach. Why don't I do pcp recovery in all cases? Because pcp recovery requires no connections from the application at the second stage of the recovery. With the release that is working for me (2.1) I can not disconnect clients at second stage only (using client_idle_limit_in_recovery in the latest copy of pgpool-II), so I need to close the application on purpose. Therefore, I need manual recovery. In this regard, I'm going to re-image my development box and install a fresh latest CVS version of pgpool-II, because something funny like this happened when I went from 2.0.1 to 2.1, so... No clue. The thing is that I'm running out of time. In conclusion, it should not behave the way it does when I disconnect a backend and do pcp attach after that. > I have downloaded the latest CVS version and tried the > following a few > times and did not see any issues. I'll push very hard to use it, starting with re-imaging my box. > On your last step though, you mentioned that you "re-attached the > primary" backend but I guess you meant the secondary backend since > that was the one you stopped. Yes, you are right: I meant 'sceondary'. > Marcelo > PostgreSQL DBA > Linux/Solaris System Administrator Thanks, Marcelo Daniel > > On Jan 20, 2009, at 5:46 PM, daniel.cre...@l-3com.com wrote: > > > I think the patch is for debugging purposes, but I'm not sure. > > > > The weird thing that happens to me is the following (I just > tested it > > again): > > > > 1. The two backends start > > 2. start pgpool. So both backend statuses are 2. > > 3.a stop primary backend, > >The connection is lost with the message "server closed the > > connection unexpectedly > >This probably means the server terminated abnormally > >before or while processing the request. > > The connection to the ser
[Pgpool-general] Patch for 2.1 release - insert_lock feature
Can anybody point me to or give me a patch for using insert_lock on serial fields tables in the 2.1 release? Thanks a lot, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Failover shuts down incoming connections
That is what happens to me too. I did: [~]$ patch < pgpool.patch (Stripping trailing CRs from patch.) patching file pool_process_query.c Hunk #1 FAILED at 251. 1 out of 1 hunk FAILED -- saving rejects to file pool_process_query.c.rej So no luck either...Anyone else with any ideas? Thanks, Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > Marcelo Martins > Sent: Tuesday, January 20, 2009 6:46 PM > To: Tatsuo Ishii > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] Failover shuts down incoming connections > > What patch command do you use to apply these patches ? I have tried > with the -c flag for context diffs but no luck there > > I basically added the patch code to the "pool_process_query.c" file > and recompiled but I think I missed something cause any query I > execute now hangs. > > > patch -p 0 -c < pool_process_query.c.patch > patching file pool_process_query.c > Hunk #1 FAILED at 251. > 1 out of 1 hunk FAILED -- saving rejects to file > pool_process_query.c.rej > > thanks, > Marcelo > PostgreSQL DBA > Linux/Solaris System Administrator > > On Jan 18, 2009, at 7:01 AM, Tatsuo Ishii wrote: > > > Thanks for testing. This error is very strange. It seems > only *once* I > > was able to reproduce the error. But since that was not > reproducable, > > it is very hard to track the case of the problem. > > > > If you easily reproduce the error, could you apply included patches > > and show me the log? I have just added a debug log. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > >> Hello, > >> > >> I finally have a log for what I noticed in the CVS head that I > >> downloaded one week ago. (Right now I don't have > permissions to do > >> a new > >> download). > >> > >> I started pgpool-II with -n and -d flags. In addition to this, I > >> started > >> it with the primary backend stopped. So as soon as it starts, it > >> detects > >> a failure in backend0, supposing to keep backend1 alive, > but it's not > >> the case: I can connect to pgpool but can't do anything. > It says that > >> the connection is closed, although I'm still in psql. > Please, look at > >> the below log and comments between lines. > >> > >> -bash-3.1$ pgpool -n -d > >> 2009-01-15 12:56:38 DEBUG: pid 6616: key: listen_addresses > >> 2009-01-15 12:56:38 DEBUG: pid 6616: value: '*' kind: 4 > >> [... loading up pgpool.conf info ...] > >> 2009-01-15 12:56:38 DEBUG: pid 6616: value: '/var/lib/pgsql/data' > >> kind: > >> 4 > >> 2009-01-15 12:56:38 DEBUG: pid 6616: num_backends: 2 > num_backends: 2 > >> total_weight: 2.00 > >> 2009-01-15 12:56:38 DEBUG: pid 6616: backend 0 weight: > >> 1073741823.50 > >> 2009-01-15 12:56:38 DEBUG: pid 6616: backend 1 weight: > >> 1073741823.50 > >> 2009-01-15 12:56:38 DEBUG: pid 6617: I am 6617 > >> 2009-01-15 12:56:38 DEBUG: pid 6618: I am 6618 > >> 2009-01-15 12:56:38 DEBUG: pid 6619: I am 6619 > >> 2009-01-15 12:56:38 DEBUG: pid 6620: I am 6620 > >> 2009-01-15 12:56:38 DEBUG: pid 6621: I am 6621 > >> 2009-01-15 12:56:38 DEBUG: pid 6622: I am 6622 > >> 2009-01-15 12:56:38 DEBUG: pid 6623: I am 6623 > >> 2009-01-15 12:56:38 DEBUG: pid 6624: I am 6624 > >> 2009-01-15 12:56:38 DEBUG: pid 6625: I am 6625 > >> 2009-01-15 12:56:38 DEBUG: pid 6626: I am 6626 > >> 2009-01-15 12:56:38 DEBUG: pid 6627: I am 6627 > >> 2009-01-15 12:56:38 DEBUG: pid 6628: I am 6628 > >> 2009-01-15 12:56:38 DEBUG: pid 6629: I am 6629 > >> 2009-01-15 12:56:38 DEBUG: pid 6630: I am 6630 > >> 2009-01-15 12:56:38 DEBUG: pid 6631: I am 6631 > >> 2009-01-15 12:56:38 DEBUG: pid 6632: I am 6632 > >> 2009-01-15 12:56:38 DEBUG: pid 6633: I am 6633 > >> 2009-01-15 12:56:38 DEBUG: pid 6634: I am 6634 > >> 2009-01-15 12:56:38 DEBUG: pid 6635: I am 6635 > >> 2009-01-15 12:56:38 DEBUG: pid 6636: I am 6636 > >> 2009-01-15 12:56:38 DEBUG: pid 6637: I am 6637 > >> 2009-01-15 12:56:38 DEBUG: pid 6638: I am 6638 > >> 2009-01-15 12:56:38 DEBUG: pid 6639: I am 6639 > >> 2009-01-15 12:56:38 DEBUG: pid 6640: I am 6640 > >> 2009-01-15 12:56:38 DEBUG: pid 6641: I am 6641 > >> 2009-01-15 12:56:38 DEBUG: pid 6642: I am 6642 > >> 2009-01-15 12:56:38 DEBUG: pid 6643: I am 6643 > >> 2009-01-15 12:56:38 DEBUG: pid 6644: I am 6644 > >> 2009-01-15 12:56:38 DEBUG: pid 6645: I am 6645 > >> 2009-01-15 12:56:38 DEBUG: pid 6646: I am 6646 > >> 2009-01-15 12:56:38 DEBUG: pid 6647: I am 6647 > >> 2009-01-15 12:56:38 DEBUG: pid 6648: I am 6648 > >> 2009-01-15 12:56:38 DEBUG: pid 6649: I am 6649 > >> 2009-01-15 12:56:38 DEBUG: pid 6650: I am 6650 > >> 2009-01-15 12:56:38 DEBUG: pid 6651: I am 6651 > >> 2009-01-15 12:56:38 DEBUG: pid 6652: I am 6652 > >> 2009-01-15 12:56:38 DEBUG: pid 6653: I am 6653 > >> 2009-01-15 12:56:38 DEBUG: pid 6654: I am 6654 > >> 2009-01-15 12:56:38 DEBUG: pid 6655: I am 6655 > >> 2009-01-15 12:56:38 DEBUG: pid 6656: I am 6656 > >> 2009-01-15 12:56:38 DEBUG: pid 6657: I am 6657 > >> 2009-01-15
Re: [Pgpool-general] pcp_attach_node problem?
I think the patch is for debugging purposes, but I'm not sure. The weird thing that happens to me is the following (I just tested it again): 1. The two backends start 2. start pgpool. So both backend statuses are 2. 3.a stop primary backend, The connection is lost with the message "server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.", every time I try to re-run the query. If I re-attach the primary backend, the connection works just fine again. 3.b stop secondary backend. The connection keeps going (good). If I re-attach the primary backend, the connection blocks. It's weird Daniel > -Original Message- > From: Marcelo Martins [mailto:pgli...@zeroaccess.org] > Sent: Tuesday, January 20, 2009 6:03 PM > To: Crespo, Daniel @ SDS > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > yeah just saw your new one when sent mine :) > > weird that it just keeps throwing that error. > I think I have done the PG shutdown and then re-attaching about 15 > times now and I only get the "server closed the connection > unexpectedly" once. > > I haven't tried to apply the patch that Tatsuo mentioned on 18th > though to see what difference it makes. might try that today > > > Marcelo > PostgreSQL DBA > Linux/Solaris System Administrator > > On Jan 20, 2009, at 4:52 PM, daniel.cre...@l-3com.com wrote: > > > Hi, Marcelo, > > > > I just wrote to the mail list something about exactly this. > > > > In your description, it doesn't happen to me... I don't know why... > > After doing failover, when a query is executed it throws back that > > "server closed the connection unexpectedly", and keeps > throwing that > > for > > every try I make. No idea about this. > > > > Thanks for the information! > > > > Daniel > > > >> -Original Message- > >> From: Marcelo Martins [mailto:pgli...@zeroaccess.org] > >> Sent: Tuesday, January 20, 2009 5:34 PM > >> To: Crespo, Daniel @ SDS > >> Subject: Re: [Pgpool-general] pcp_attach_node problem? > >> > >> Hi Daniel, > >> > >> I have just tested that with pgpool 2.1 and I also have the > >> same issue. > >> When I re-attach node 1 (second node) back, the psql > >> connection that I > >> had opened hangs after executing a second query. > >> > >> ERROR: pid 31003: pool_read2: EOF encountered with backend > >> > >> On the latest CVS version though the hanging issue seems > to be fixed. > >> Now when the failover/failback happens though it seems like pgpool > >> failover_handler process kills the childs that pgpool had open with > >> node 1 (second node - at least that is what I can tell from what I > >> see ) therefore when a query is executed it throws back > that "server > >> closed the connection unexpectedly" . When I execute the query a > >> second time then pgpool uses a new child that has connection > >> opened to > >> node 0 "new_connection: skipping slot 1 because backend_status = 3" > >> > >> > >> Marcelo > >> PostgreSQL DBA > >> Linux/Solaris System Administrator > >> > >> On Jan 13, 2009, at 8:18 AM, daniel.cre...@l-3com.com wrote: > >> > >>> Sorry for the delay, I haven't had enough time. > >>> > 1. Show us the logs. Full logs, but only the relevant > >> parts (got tons > of things to read every day here). :) > >>> > >>> I'll try it again with full logs to give them to you guys > >>> > 2. Check whether PostgreSQL is having some problem of some sort > before > blaming it on pgpool-II. Can you run the same queries on > both nodes > and get the same results? > >>> > >>> PostgreSQL is not having any problems. It's not a query problem. > >>> When I > >>> install the latest CVS head, what I showed to you is what happens. > >>> However, when I uninstall it and install the 2.1 released > >> version, it > >>> doesn't happen anymore. The problem with this 2.1 release > is that it > >>> doesn't keep the connection when a node is detached or > >> attached (if I > >>> have an already opened connection and do attach/detach node, it > >>> locks. I > >>> must disconnect and reconnect in order to keep doing > >> queries). Another > >>> problem is that I need the insert lock newly introduced to > >>> automatically > >>> apply on serial fields tables. > >>> > 3. Check permissions in both bg_hba.conf files. > >>> No problem with this. > >>> > 4. Have you considered using version 8.3.5 of PostgreSQL > >> and see how > it goes? Or at least, the last revision of the 8.1 branch. > >>> No. I can not update PostgreSQL. I'm using 8.2.1. > >>> > >>> When I have the logs, I'll post them for sure. Thanks! > >>> > >>> Daniel > >>> > >>> > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > Jaume Sabater > Se
[Pgpool-general] Connection issues with pgpool-II
I have some good and bad news in the my testings of 2.1 release (since
the latest CVS version does not work for me. Maybe this helps a bit):
The good news:
---
The connection blocking behavior I had when a failover happened was
because the failover_command was not returning (in pgpool.conf:
failover_command = '. failover_cmd $h %d &'). I replaced it with another
script which in turn calls the intended command (without & at the end).
That way, existent connections keep working, although there's a little
sleep time when the failover occurrs, which is not that bad.
The bad news:
--
When a failback happens, already opened clients would block forever, no
matter whether or not you have a failback_command. The ideal behavior
should be that the existent connections keep working without
interruption. I found the below code in pool_stream.c, and added
'child_exit(1)' to see if at least I can force the clients to exit, and
have them try reconnecting again.
Inside both:
'char *pool_read2(POOL_CONNECTION *cp, int len)' and 'int
pool_read(POOL_CONNECTION *cp, void *buf, int len)' functions:
[...]
else if (readlen == 0)
{
if (cp->isbackend)
{
pool_error("pool_read2: EOF encountered with backend");
child_exit(1); // *** Added this for forcing clients to exit ***
return -1;
[...]
This change worked for me. It's not ideal, but at least it makes clients
connected not block forever. Might there be a way of instead of exiting
clients, just unblock them and have them continue with their queries?
Thanks,
Daniel
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Failover shuts down incoming connections
[~]$ patch < pgpool.patch (Stripping trailing CRs from patch.) patching file pool_process_query.c Hunk #1 FAILED at 251. 1 out of 1 hunk FAILED -- saving rejects to file pool_process_query.c.rej Do I have the correct version of pool_process_query.c? Unfortunately, I don't have access to the CVS head right now for downloading it. Here is the first 6 lines of pool_process_query.c file that I have: 1 /* -*-pgsql-c-*- */ 2 /* 3 * $Header: /cvsroot/pgpool/pgpool-II/pool_process_query.c,v 1.131 2009/01/07 13:33:31 t-ishii Exp $ 4 * 5 * pgpool: a language independent connection pool server for PostgreSQL 6 * written by Tatsuo Ishii ... Thanks, Daniel > -Original Message- > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > Sent: Sunday, January 18, 2009 8:01 AM > To: Crespo, Daniel @ SDS > Cc: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] Failover shuts down incoming connections > > Thanks for testing. This error is very strange. It seems only > *once* I was able to reproduce the error. But since that was > not reproducable, it is very hard to track the case of the problem. > > If you easily reproduce the error, could you apply included > patches and show me the log? I have just added a debug log. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Hello, > > > > I finally have a log for what I noticed in the CVS head that I > > downloaded one week ago. (Right now I don't have > permissions to do a > > new download). > > > > I started pgpool-II with -n and -d flags. In addition to this, I > > started it with the primary backend stopped. So as soon as > it starts, > > it detects a failure in backend0, supposing to keep backend1 alive, > > but it's not the case: I can connect to pgpool but can't do > anything. > > It says that the connection is closed, although I'm still in psql. > > Please, look at the below log and comments between lines. > > > > -bash-3.1$ pgpool -n -d > > 2009-01-15 12:56:38 DEBUG: pid 6616: key: listen_addresses > > 2009-01-15 12:56:38 DEBUG: pid 6616: value: '*' kind: 4 > [... loading > > up pgpool.conf info ...] > > 2009-01-15 12:56:38 DEBUG: pid 6616: value: > '/var/lib/pgsql/data' kind: > > 4 > > 2009-01-15 12:56:38 DEBUG: pid 6616: num_backends: 2 num_backends: 2 > > total_weight: 2.00 > > 2009-01-15 12:56:38 DEBUG: pid 6616: backend 0 weight: > > 1073741823.50 > > 2009-01-15 12:56:38 DEBUG: pid 6616: backend 1 weight: > > 1073741823.50 > > 2009-01-15 12:56:38 DEBUG: pid 6617: I am 6617 > > 2009-01-15 12:56:38 DEBUG: pid 6618: I am 6618 > > 2009-01-15 12:56:38 DEBUG: pid 6619: I am 6619 > > 2009-01-15 12:56:38 DEBUG: pid 6620: I am 6620 > > 2009-01-15 12:56:38 DEBUG: pid 6621: I am 6621 > > 2009-01-15 12:56:38 DEBUG: pid 6622: I am 6622 > > 2009-01-15 12:56:38 DEBUG: pid 6623: I am 6623 > > 2009-01-15 12:56:38 DEBUG: pid 6624: I am 6624 > > 2009-01-15 12:56:38 DEBUG: pid 6625: I am 6625 > > 2009-01-15 12:56:38 DEBUG: pid 6626: I am 6626 > > 2009-01-15 12:56:38 DEBUG: pid 6627: I am 6627 > > 2009-01-15 12:56:38 DEBUG: pid 6628: I am 6628 > > 2009-01-15 12:56:38 DEBUG: pid 6629: I am 6629 > > 2009-01-15 12:56:38 DEBUG: pid 6630: I am 6630 > > 2009-01-15 12:56:38 DEBUG: pid 6631: I am 6631 > > 2009-01-15 12:56:38 DEBUG: pid 6632: I am 6632 > > 2009-01-15 12:56:38 DEBUG: pid 6633: I am 6633 > > 2009-01-15 12:56:38 DEBUG: pid 6634: I am 6634 > > 2009-01-15 12:56:38 DEBUG: pid 6635: I am 6635 > > 2009-01-15 12:56:38 DEBUG: pid 6636: I am 6636 > > 2009-01-15 12:56:38 DEBUG: pid 6637: I am 6637 > > 2009-01-15 12:56:38 DEBUG: pid 6638: I am 6638 > > 2009-01-15 12:56:38 DEBUG: pid 6639: I am 6639 > > 2009-01-15 12:56:38 DEBUG: pid 6640: I am 6640 > > 2009-01-15 12:56:38 DEBUG: pid 6641: I am 6641 > > 2009-01-15 12:56:38 DEBUG: pid 6642: I am 6642 > > 2009-01-15 12:56:38 DEBUG: pid 6643: I am 6643 > > 2009-01-15 12:56:38 DEBUG: pid 6644: I am 6644 > > 2009-01-15 12:56:38 DEBUG: pid 6645: I am 6645 > > 2009-01-15 12:56:38 DEBUG: pid 6646: I am 6646 > > 2009-01-15 12:56:38 DEBUG: pid 6647: I am 6647 > > 2009-01-15 12:56:38 DEBUG: pid 6648: I am 6648 > > 2009-01-15 12:56:38 DEBUG: pid 6649: I am 6649 > > 2009-01-15 12:56:38 DEBUG: pid 6650: I am 6650 > > 2009-01-15 12:56:38 DEBUG: pid 6651: I am 6651 > > 2009-01-15 12:56:38 DEBUG: pid 6652: I am 6652 > > 2009-01-15 12:56:38 DEBUG: pid 6653: I am 6653 > > 2009-01-15 12:56:38 DEBUG: pid 6654: I am 6654 > > 2009-01-15 12:56:38 DEBUG: pid 6655: I am 6655 > > 2009-01-15 12:56:38 DEBUG: pid 6656: I am 6656 > > 2009-01-15 12:56:38 DEBUG: pid 6657: I am 6657 > > 2009-01-15 12:56:38 DEBUG: pid 6658: I am 6658 > > 2009-01-15 12:56:38 DEBUG: pid 6659: I am 6659 > > 2009-01-15 12:56:38 DEBUG: pid 6660: I am 6660 > > 2009-01-15 12:56:38 DEBUG: pid 6661: I am 6661 > > 2009-01-15 12:56:38 DEBUG: pid 6662: I am 6662 > > 2009-01-15 12:56:38 DEBUG: pid 6663: I am 6663 > > 2009-01-15 12:56:38 DEBUG: pid 6664: I am 6664 > > 2009-01-15 12:56:38 DEBUG: pid 6665: I am 6665 > > 2009-01-15 12:56:38 DEBUG:
[Pgpool-general] Failover shuts down incoming connections
Hello, I finally have a log for what I noticed in the CVS head that I downloaded one week ago. (Right now I don't have permissions to do a new download). I started pgpool-II with -n and -d flags. In addition to this, I started it with the primary backend stopped. So as soon as it starts, it detects a failure in backend0, supposing to keep backend1 alive, but it's not the case: I can connect to pgpool but can't do anything. It says that the connection is closed, although I'm still in psql. Please, look at the below log and comments between lines. -bash-3.1$ pgpool -n -d 2009-01-15 12:56:38 DEBUG: pid 6616: key: listen_addresses 2009-01-15 12:56:38 DEBUG: pid 6616: value: '*' kind: 4 [... loading up pgpool.conf info ...] 2009-01-15 12:56:38 DEBUG: pid 6616: value: '/var/lib/pgsql/data' kind: 4 2009-01-15 12:56:38 DEBUG: pid 6616: num_backends: 2 num_backends: 2 total_weight: 2.00 2009-01-15 12:56:38 DEBUG: pid 6616: backend 0 weight: 1073741823.50 2009-01-15 12:56:38 DEBUG: pid 6616: backend 1 weight: 1073741823.50 2009-01-15 12:56:38 DEBUG: pid 6617: I am 6617 2009-01-15 12:56:38 DEBUG: pid 6618: I am 6618 2009-01-15 12:56:38 DEBUG: pid 6619: I am 6619 2009-01-15 12:56:38 DEBUG: pid 6620: I am 6620 2009-01-15 12:56:38 DEBUG: pid 6621: I am 6621 2009-01-15 12:56:38 DEBUG: pid 6622: I am 6622 2009-01-15 12:56:38 DEBUG: pid 6623: I am 6623 2009-01-15 12:56:38 DEBUG: pid 6624: I am 6624 2009-01-15 12:56:38 DEBUG: pid 6625: I am 6625 2009-01-15 12:56:38 DEBUG: pid 6626: I am 6626 2009-01-15 12:56:38 DEBUG: pid 6627: I am 6627 2009-01-15 12:56:38 DEBUG: pid 6628: I am 6628 2009-01-15 12:56:38 DEBUG: pid 6629: I am 6629 2009-01-15 12:56:38 DEBUG: pid 6630: I am 6630 2009-01-15 12:56:38 DEBUG: pid 6631: I am 6631 2009-01-15 12:56:38 DEBUG: pid 6632: I am 6632 2009-01-15 12:56:38 DEBUG: pid 6633: I am 6633 2009-01-15 12:56:38 DEBUG: pid 6634: I am 6634 2009-01-15 12:56:38 DEBUG: pid 6635: I am 6635 2009-01-15 12:56:38 DEBUG: pid 6636: I am 6636 2009-01-15 12:56:38 DEBUG: pid 6637: I am 6637 2009-01-15 12:56:38 DEBUG: pid 6638: I am 6638 2009-01-15 12:56:38 DEBUG: pid 6639: I am 6639 2009-01-15 12:56:38 DEBUG: pid 6640: I am 6640 2009-01-15 12:56:38 DEBUG: pid 6641: I am 6641 2009-01-15 12:56:38 DEBUG: pid 6642: I am 6642 2009-01-15 12:56:38 DEBUG: pid 6643: I am 6643 2009-01-15 12:56:38 DEBUG: pid 6644: I am 6644 2009-01-15 12:56:38 DEBUG: pid 6645: I am 6645 2009-01-15 12:56:38 DEBUG: pid 6646: I am 6646 2009-01-15 12:56:38 DEBUG: pid 6647: I am 6647 2009-01-15 12:56:38 DEBUG: pid 6648: I am 6648 2009-01-15 12:56:38 DEBUG: pid 6649: I am 6649 2009-01-15 12:56:38 DEBUG: pid 6650: I am 6650 2009-01-15 12:56:38 DEBUG: pid 6651: I am 6651 2009-01-15 12:56:38 DEBUG: pid 6652: I am 6652 2009-01-15 12:56:38 DEBUG: pid 6653: I am 6653 2009-01-15 12:56:38 DEBUG: pid 6654: I am 6654 2009-01-15 12:56:38 DEBUG: pid 6655: I am 6655 2009-01-15 12:56:38 DEBUG: pid 6656: I am 6656 2009-01-15 12:56:38 DEBUG: pid 6657: I am 6657 2009-01-15 12:56:38 DEBUG: pid 6658: I am 6658 2009-01-15 12:56:38 DEBUG: pid 6659: I am 6659 2009-01-15 12:56:38 DEBUG: pid 6660: I am 6660 2009-01-15 12:56:38 DEBUG: pid 6661: I am 6661 2009-01-15 12:56:38 DEBUG: pid 6662: I am 6662 2009-01-15 12:56:38 DEBUG: pid 6663: I am 6663 2009-01-15 12:56:38 DEBUG: pid 6664: I am 6664 2009-01-15 12:56:38 DEBUG: pid 6665: I am 6665 2009-01-15 12:56:38 DEBUG: pid : I am 2009-01-15 12:56:38 DEBUG: pid 6667: I am 6667 2009-01-15 12:56:38 DEBUG: pid 6668: I am 6668 2009-01-15 12:56:38 DEBUG: pid 6669: I am 6669 2009-01-15 12:56:38 DEBUG: pid 6670: I am 6670 2009-01-15 12:56:38 DEBUG: pid 6671: I am 6671 2009-01-15 12:56:38 DEBUG: pid 6672: I am 6672 2009-01-15 12:56:38 DEBUG: pid 6673: I am 6673 2009-01-15 12:56:38 DEBUG: pid 6674: I am 6674 2009-01-15 12:56:38 DEBUG: pid 6675: I am 6675 2009-01-15 12:56:38 DEBUG: pid 6676: I am 6676 2009-01-15 12:56:38 DEBUG: pid 6677: I am 6677 2009-01-15 12:56:38 DEBUG: pid 6678: I am 6678 2009-01-15 12:56:38 DEBUG: pid 6679: I am 6679 2009-01-15 12:56:38 DEBUG: pid 6680: I am 6680 2009-01-15 12:56:38 DEBUG: pid 6681: I am 6681 2009-01-15 12:56:38 DEBUG: pid 6682: I am 6682 2009-01-15 12:56:38 DEBUG: pid 6683: I am 6683 2009-01-15 12:56:38 DEBUG: pid 6684: I am 6684 2009-01-15 12:56:38 DEBUG: pid 6685: I am 6685 2009-01-15 12:56:38 DEBUG: pid 6686: I am 6686 2009-01-15 12:56:38 DEBUG: pid 6687: I am 6687 2009-01-15 12:56:38 DEBUG: pid 6688: I am 6688 2009-01-15 12:56:38 DEBUG: pid 6689: I am 6689 2009-01-15 12:56:38 DEBUG: pid 6690: I am 6690 2009-01-15 12:56:38 DEBUG: pid 6691: I am 6691 2009-01-15 12:56:38 DEBUG: pid 6692: I am 6692 2009-01-15 12:56:38 DEBUG: pid 6693: I am 6693 2009-01-15 12:56:38 DEBUG: pid 6694: I am 6694 2009-01-15 12:56:38 DEBUG: pid 6695: I am 6695 2009-01-15 12:56:38 DEBUG: pid 6696: I am 6696 2009-01-15 12:56:38 DEBUG: pid 6697: I am 6697 2009-01-15 12:56:38 DEBUG: pid 6698: I am 6698 2009-01-15 12:56:38 DEBUG: pid 6699: I am 6699 2009-01-15 12:56:38 DEBUG: pid 6700: I am 6700 2009-
[Pgpool-general] pgpool-II not starting after stopping it
This is happening to me a lot of times: -bash-3.1$ pgpool -n -d 2009-01-15 11:33:07 DEBUG: pid 1169: key: listen_addresses 2009-01-15 11:33:07 DEBUG: pid 1169: value: '*' kind: 4 [...] 2009-01-15 11:33:07 DEBUG: pid 1169: key: backend_data_directory1 2009-01-15 11:33:07 DEBUG: pid 1169: value: '/var/lib/pgsql/data' kind: 4 2009-01-15 11:33:07 DEBUG: pid 1169: num_backends: 2 num_backends: 2 total_weight: 2.00 2009-01-15 11:33:07 DEBUG: pid 1169: backend 0 weight: 1073741823.50 2009-01-15 11:33:07 DEBUG: pid 1169: backend 1 weight: 1073741823.50 2009-01-15 11:33:12 ERROR: pid 1169: bind(0.0.0.0:distinct) failed. reason: Address already in use 2009-01-15 11:33:12 ERROR: pid 1169: unlink() failed: No such file or directory 2009-01-15 11:33:12 DEBUG: pid 1169: shmem_exit(1) ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
Sorry for the delay, I haven't had enough time. > 1. Show us the logs. Full logs, but only the relevant parts (got tons > of things to read every day here). :) I'll try it again with full logs to give them to you guys > 2. Check whether PostgreSQL is having some problem of some sort before > blaming it on pgpool-II. Can you run the same queries on both nodes > and get the same results? PostgreSQL is not having any problems. It's not a query problem. When I install the latest CVS head, what I showed to you is what happens. However, when I uninstall it and install the 2.1 released version, it doesn't happen anymore. The problem with this 2.1 release is that it doesn't keep the connection when a node is detached or attached (if I have an already opened connection and do attach/detach node, it locks. I must disconnect and reconnect in order to keep doing queries). Another problem is that I need the insert lock newly introduced to automatically apply on serial fields tables. > 3. Check permissions in both bg_hba.conf files. No problem with this. > 4. Have you considered using version 8.3.5 of PostgreSQL and see how > it goes? Or at least, the last revision of the 8.1 branch. No. I can not update PostgreSQL. I'm using 8.2.1. When I have the logs, I'll post them for sure. Thanks! Daniel > -Original Message- > From: pgpool-general-boun...@pgfoundry.org > [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of > Jaume Sabater > Sent: Friday, January 09, 2009 2:32 AM > To: pgpool-general@pgfoundry.org > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > On Thu, Jan 8, 2009 at 10:14 PM, wrote: > > >And issue a SQL Select command on a table, like: > >postgres=# select * from pg_stat_activity ; > > > >It returns: > >postgres=# select 1; > >server closed the connection unexpectedly > >This probably means the server terminated abnormally > >before or while processing the request. > >The connection to the server was lost. Attempting reset: > Succeeded. > > > >postgres=# select 1; > > Some ideas: > > 1. Show us the logs. Full logs, but only the relevant parts (got tons > of things to read every day here). :) > 2. Check whether PostgreSQL is having some problem of some sort before > blaming it on pgpool-II. Can you run the same queries on both nodes > and get the same results? > 3. Check permissions in both bg_hba.conf files. > 4. Have you considered using version 8.3.5 of PostgreSQL and see how > it goes? Or at least, the last revision of the 8.1 branch. > > -- > Jaume Sabater > http://linuxsilo.net/ > > "Ubi sapientas ibi libertas" > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
Well, I tested installing the latest CVS version in a clean box and this is what I get: 1. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 2 1073741823.50 2. [Server B] stop second backend: service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] 3. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 3 1073741823.50 4. [Server A] Re-Attach node 1: pcp_attach_node 10 localhost 9898 postgres postgres 1 5. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 1 1073741823.50 6. Here comes the problem (slightly different than before): If I had an already opened psql console, like: [root ~]# psql -U postgres -p Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# And issue a SQL Select command on a table, like: postgres=# select * from pg_stat_activity ; It returns: postgres=# select 1; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. postgres=# select 1; Then it blocks. Meanwhile, when I try to check the statuses of both backends, pcp_* commands are also blocking. Thanks, Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii Sent: Wednesday, January 07, 2009 11:24 PM To: Crespo, Daniel @ SDS Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? Did not reproduce here? -- Tatsuo Ishii SRA OSS, Inc. Japan > Let me look into this... > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Finally, I installed the latest CVS checkout. I uninstalled my previous > > pgpool-II build by doing: > > > > [root ~]# make uninstall > > [user ~]# make clean > > > > Then, went to the just downloaded repository for pgpool-II and did: > > > > [user ~]# ./configure > > [user ~]# make > > [root ~]# make install > > > > Tried the below same thing, but now db connection through pgpool blocks > > as soon as second backend goes from status 2 to 3. > > > > Anything wrong with the way I installed the most recent CVS head? > > > > Thanks, > > > > Daniel > > > > > > -Original Message- > > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > > Sent: Wednesday, January 07, 2009 8:39 AM > > To: Crespo, Daniel @ SDS > > Cc: pgpool-general@pgfoundry.org > > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > > > Thanks for the report. I think it's a bug with CVS Head. pgpool should > > fail over in this case. Fix was committed. Could you please try it > > out? > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > Hello, > > > > > > I have pgpool configured with two backends. > > > > > > Below is the sequence of events to reproduce the > > seems-to-be-a-problem: > > > > > > 1. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 2 1073741823.50 > > > > > > 2. [Server B] stop second backend: > > > service postgresql restart > > > Stopping postgresql service: [ OK ] > > > Starting postgresql service: [ OK ] > > > > > > 3. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 3 1073741823.50 > > > > > > 4. [Server A] Re-Attach node 1: > > > pcp_attach_node 10 localhost 9898 postgres postgres 1 > > > > > > 5. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 1 1073741823.50 > > > > > > 6. Here comes the problem: > > > > > > 6.a If I had an already ope
Re: [Pgpool-general] pcp_attach_node problem?
I did what Jaume suggested, and still I get the behavior shown below. However, when I uninstall the CVS version, and go back to the 2.1 released version, everything works as before. In fact, it behaves better. Any clue? Thanks, Danielmake -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of daniel.cre...@l-3com.com Sent: Thursday, January 08, 2009 9:28 AM To: Tatsuo Ishii Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? I guess my installation is corrupted... What can I do for cleaning every file that the package is supposed to use, that is installed by doing make, make install? Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii Sent: Wednesday, January 07, 2009 11:24 PM To: Crespo, Daniel @ SDS Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? Did not reproduce here? -- Tatsuo Ishii SRA OSS, Inc. Japan > Let me look into this... > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Finally, I installed the latest CVS checkout. I uninstalled my previous > > pgpool-II build by doing: > > > > [root ~]# make uninstall > > [user ~]# make clean > > > > Then, went to the just downloaded repository for pgpool-II and did: > > > > [user ~]# ./configure > > [user ~]# make > > [root ~]# make install > > > > Tried the below same thing, but now db connection through pgpool blocks > > as soon as second backend goes from status 2 to 3. > > > > Anything wrong with the way I installed the most recent CVS head? > > > > Thanks, > > > > Daniel > > > > > > -Original Message- > > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > > Sent: Wednesday, January 07, 2009 8:39 AM > > To: Crespo, Daniel @ SDS > > Cc: pgpool-general@pgfoundry.org > > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > > > Thanks for the report. I think it's a bug with CVS Head. pgpool should > > fail over in this case. Fix was committed. Could you please try it > > out? > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > Hello, > > > > > > I have pgpool configured with two backends. > > > > > > Below is the sequence of events to reproduce the > > seems-to-be-a-problem: > > > > > > 1. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 2 1073741823.50 > > > > > > 2. [Server B] stop second backend: > > > service postgresql restart > > > Stopping postgresql service: [ OK ] > > > Starting postgresql service: [ OK ] > > > > > > 3. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 3 1073741823.50 > > > > > > 4. [Server A] Re-Attach node 1: > > > pcp_attach_node 10 localhost 9898 postgres postgres 1 > > > > > > 5. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 1 1073741823.50 > > > > > > 6. Here comes the problem: > > > > > > 6.a If I had an already opened psql console, like: > > > [root ~]# psql -U postgres -p > > > Welcome to psql 8.2.1, the PostgreSQL interactive terminal. > > > > > > Type: \copyright for distribution terms > > >\h for help with SQL commands > > >\? for help with psql commands > > >\g or terminate with semicolon to execute query > > >\q to quit > > > > > > postgres=# > > > > > > And issue a SQL Select command on a table, like: > > > postgres=# select * from pg_stat_activity ; > > > > > > It sits there forever. Meanwhile, the status of both backends > > > are still 2 and 1, respectively. > > > > > > If, instead, I disconnect the psql console, and connect it > > > again, both statuses become 2, and then I can issue the same SQL > > Select, > > > and works just fine. > > > > > > Any clue? > > > > > > Thanks, > > > Daniel > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.o
Re: [Pgpool-general] pcp_attach_node problem?
Sorry, I was expecting a list of files. Since I already tried what you said, I was wondering if there were more. I'll try again. Thanks! Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Jaume Sabater Sent: Thursday, January 08, 2009 10:01 AM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? On Thu, Jan 8, 2009 at 3:55 PM, wrote: > How can I know the list of all the files that are to be removed without > having to go through the make files? I thought I had already answered that question :D Here it goes again: - Download sources. Configure with custom prefix. Compile and install. - There you are all the files of the installation, so that you can compare what you should have somewhere else, spread through your filesystem in the worst case. -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
Thanks, Jaume... How can I know the list of all the files that are to be removed without having to go through the make files? -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Jaume Sabater Sent: Thursday, January 08, 2009 9:36 AM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? On Thu, Jan 8, 2009 at 3:27 PM, wrote: > I guess my installation is corrupted... What can I do for cleaning every > file that the package is supposed to use, that is installed by doing > make, make install? By default, pgpool-II puts everything under /usr/local/, although I used the --prefix parameter when doing the ./configure to change it to /opt/pgpool2. Also try updatedb and locate -i with grep. It helps cleaning up. -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
I guess my installation is corrupted... What can I do for cleaning every file that the package is supposed to use, that is installed by doing make, make install? Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii Sent: Wednesday, January 07, 2009 11:24 PM To: Crespo, Daniel @ SDS Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? Did not reproduce here? -- Tatsuo Ishii SRA OSS, Inc. Japan > Let me look into this... > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Finally, I installed the latest CVS checkout. I uninstalled my previous > > pgpool-II build by doing: > > > > [root ~]# make uninstall > > [user ~]# make clean > > > > Then, went to the just downloaded repository for pgpool-II and did: > > > > [user ~]# ./configure > > [user ~]# make > > [root ~]# make install > > > > Tried the below same thing, but now db connection through pgpool blocks > > as soon as second backend goes from status 2 to 3. > > > > Anything wrong with the way I installed the most recent CVS head? > > > > Thanks, > > > > Daniel > > > > > > -Original Message- > > From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] > > Sent: Wednesday, January 07, 2009 8:39 AM > > To: Crespo, Daniel @ SDS > > Cc: pgpool-general@pgfoundry.org > > Subject: Re: [Pgpool-general] pcp_attach_node problem? > > > > Thanks for the report. I think it's a bug with CVS Head. pgpool should > > fail over in this case. Fix was committed. Could you please try it > > out? > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > Hello, > > > > > > I have pgpool configured with two backends. > > > > > > Below is the sequence of events to reproduce the > > seems-to-be-a-problem: > > > > > > 1. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 2 1073741823.50 > > > > > > 2. [Server B] stop second backend: > > > service postgresql restart > > > Stopping postgresql service: [ OK ] > > > Starting postgresql service: [ OK ] > > > > > > 3. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 3 1073741823.50 > > > > > > 4. [Server A] Re-Attach node 1: > > > pcp_attach_node 10 localhost 9898 postgres postgres 1 > > > > > > 5. [Server A] Check the status of the nodes: > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > > > 172.20.79.163 5432 2 1073741823.50 > > > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > > > 172.20.79.25 5432 1 1073741823.50 > > > > > > 6. Here comes the problem: > > > > > > 6.a If I had an already opened psql console, like: > > > [root ~]# psql -U postgres -p > > > Welcome to psql 8.2.1, the PostgreSQL interactive terminal. > > > > > > Type: \copyright for distribution terms > > >\h for help with SQL commands > > >\? for help with psql commands > > >\g or terminate with semicolon to execute query > > >\q to quit > > > > > > postgres=# > > > > > > And issue a SQL Select command on a table, like: > > > postgres=# select * from pg_stat_activity ; > > > > > > It sits there forever. Meanwhile, the status of both backends > > > are still 2 and 1, respectively. > > > > > > If, instead, I disconnect the psql console, and connect it > > > again, both statuses become 2, and then I can issue the same SQL > > Select, > > > and works just fine. > > > > > > Any clue? > > > > > > Thanks, > > > Daniel > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pcp_attach_node problem?
Finally, I installed the latest CVS checkout. I uninstalled my previous pgpool-II build by doing: [root ~]# make uninstall [user ~]# make clean Then, went to the just downloaded repository for pgpool-II and did: [user ~]# ./configure [user ~]# make [root ~]# make install Tried the below same thing, but now db connection through pgpool blocks as soon as second backend goes from status 2 to 3. Anything wrong with the way I installed the most recent CVS head? Thanks, Daniel -Original Message- From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] Sent: Wednesday, January 07, 2009 8:39 AM To: Crespo, Daniel @ SDS Cc: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] pcp_attach_node problem? Thanks for the report. I think it's a bug with CVS Head. pgpool should fail over in this case. Fix was committed. Could you please try it out? -- Tatsuo Ishii SRA OSS, Inc. Japan > Hello, > > I have pgpool configured with two backends. > > Below is the sequence of events to reproduce the seems-to-be-a-problem: > > 1. [Server A] Check the status of the nodes: > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > 172.20.79.163 5432 2 1073741823.50 > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > 172.20.79.25 5432 2 1073741823.50 > > 2. [Server B] stop second backend: > service postgresql restart > Stopping postgresql service: [ OK ] > Starting postgresql service: [ OK ] > > 3. [Server A] Check the status of the nodes: > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > 172.20.79.163 5432 2 1073741823.50 > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > 172.20.79.25 5432 3 1073741823.50 > > 4. [Server A] Re-Attach node 1: > pcp_attach_node 10 localhost 9898 postgres postgres 1 > > 5. [Server A] Check the status of the nodes: > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 > 172.20.79.163 5432 2 1073741823.50 > [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 > 172.20.79.25 5432 1 1073741823.50 > > 6. Here comes the problem: > > 6.a If I had an already opened psql console, like: > [root ~]# psql -U postgres -p > Welcome to psql 8.2.1, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help with psql commands >\g or terminate with semicolon to execute query >\q to quit > > postgres=# > > And issue a SQL Select command on a table, like: > postgres=# select * from pg_stat_activity ; > > It sits there forever. Meanwhile, the status of both backends > are still 2 and 1, respectively. > > If, instead, I disconnect the psql console, and connect it > again, both statuses become 2, and then I can issue the same SQL Select, > and works just fine. > > Any clue? > > Thanks, > Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] pcp_attach_node problem?
Hello, I have pgpool configured with two backends. Below is the sequence of events to reproduce the seems-to-be-a-problem: 1. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 2 1073741823.50 2. [Server B] stop second backend: service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] 3. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 3 1073741823.50 4. [Server A] Re-Attach node 1: pcp_attach_node 10 localhost 9898 postgres postgres 1 5. [Server A] Check the status of the nodes: [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 0 172.20.79.163 5432 2 1073741823.50 [root ~]# pcp_node_info 10 localhost 9898 postgres postgres 1 172.20.79.25 5432 1 1073741823.50 6. Here comes the problem: 6.a If I had an already opened psql console, like: [root ~]# psql -U postgres -p Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# And issue a SQL Select command on a table, like: postgres=# select * from pg_stat_activity ; It sits there forever. Meanwhile, the status of both backends are still 2 and 1, respectively. If, instead, I disconnect the psql console, and connect it again, both statuses become 2, and then I can issue the same SQL Select, and works just fine. Any clue? Thanks, Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Detecting type of failure
Thanks for your suggestions. We are not using Heartbeat, plus we need AP0 and AP1 connected to their own pgpool-II instance (refer to the original message). However, only one of those APx is going to be active at a certain time, so there should be only one source of data incoming to the database at any time. Since pgpool-II is a new implementation, we are trying to accommodate it to our framework, and Heartbeat is not an option for us right now. In regard of the sequence of the installation, jumping back and forth for configuring the servers is definitely not what we need. I have an schema of what can be done. As soon as I have it planned, I'll post it for anyone who is interested on it. Thanks Jaume. Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Jaume Sabater Sent: Monday, December 22, 2008 3:25 AM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] Detecting type of failure On Fri, Dec 19, 2008 at 9:32 PM, wrote: > Some suggestions? Install PostgreSQL on the first node. Install pgpool-II on first node and configure only one backend server. Install PostgreSQL on the second node. Configure the second backend on the first pgpool-II. Configure the second pgpool-II, configure both backends. Do not start it. Configure Heartbeat. Start heartbeat. If you don't have Heartbeat in mind, then you don't want two pgpool-II simultaneously active against two backends. -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] Detecting type of failure
Hi,
I think most people is going to be interested on this, so I hope I can
find someone in this list who is interested on this as well, so we can
find out a solution together.
I'm actively pursuing a successful installation of two servers with an
instance of a:
- Custom Server Application (AP)
- pgpool
- postgresql
on each server.
The diagram below explains the connections
Server A Server B
+--+ +--+
| AP0 | | AP1 |
| | | | | |
| V | | V |
| pgpool0|---. .---|---pgpool1|
| | |\ /| | |
| V | X | V |
| DB0 <|' '|---> DB1 |
+--+ +--+
172.10.10.2172.10.10.3
In each pgpool.conf I have:
backend_hostname0 = '172.10.10.2'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/data'
backend_hostname1 = '172.10.10.3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data'
PROBLEM:
The problem comeS when installing the servers, since the installation
must be done (at least in my case) one server at a time.
Server A:
-
When I install AP0, pgpool0 and DB0 in Server A, Server B has no
installations at all, therefore, pgpool0 in Server A will start with
access to DB0 only.
#pcp_node_info 10 localhost 9898 postgres postgres 0
172.10.10.2 5432 2 1073741823.50
#pcp_node_info 10 localhost 9898 postgres postgres 1
172.10.10.3 5432 3 1073741823.50
Server B:
-
When I install AP1, pgpool1 and DB1 in Server B, Server A will be ready,
specially DB0. In this case, when we start services in Server B, pgpool
will successfully connect to DB0 and DB1.
#pcp_node_info 10 localhost 9898 postgres postgres 0
172.10.10.2 5432 2 1073741823.50
#pcp_node_info 10 localhost 9898 postgres postgres 1
172.10.10.3 5432 2 1073741823.50
The goal, of course, is to have both pgpool0 and pgpool1 connected to
both DB0 and DB1. With "connected" I mean "connection status" of a
backend is 0, 1, or 2. A connection status of 3 means "disconnected".
This can be seen by running:
Possible solutionS:
--
1. Install DB0 and DB1 first, then pgpool0 and pgpool1. The problem I
have is that I have to do a whole server first, then the other one.
2. After installing Server A and Server B, restart pgpool in Server A,
and therefore restart AP0 (if it does not handle reconnection).
Non of these are good enough to me. What I want is to install Server A,
then Server B, and have everything connected perfectly. For this, a
re-attach of DB1 must be done in pgpool0, but how to know when and
automatically?
An idea:
Upon failover detection, failover_command is triggered by pgpool. This
can be used for:
check periodically the failed backend (e.g. keep pinging, and
when there's positive response, try connecting to database).
If failed backend is back (let's say you can do "select 1;" from
*this* server)
{
check if data is ok (e.g. count(*) on some indicative
tables on local pgpool connection against same quey on failed backend).
If *this* pgpool is the one where the active Server
Application is running (e.g. AP0), then
{
If data [in step 2] is ok
{
call pcp_attach_command to attach the
failed node.
}
Otherwise
{
do pcp_recovery_command for the failed
node.
[Somehow] restart pgpool on the other
server.
}
}
}
Otherwise, do nothing.
Some suggestions?
Thanks,
Daniel
___
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] termination of maintenance for pgpool
No objections here. Actually, I don't see advantages of pgpool over pgpool-II, so I think it's better to put effort on pgpool-II. Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Tatsuo Ishii Sent: Thursday, December 18, 2008 8:40 PM To: pgpool-general@pgfoundry.org Subject: [Pgpool-general] termination of maintenance for pgpool Hi pgpool users, I would like to propose to terminate of maintenance for pgpool(not pgpool-II). It seems there's no reason to continute to use pgpool, and actually there are very few people who are still using pgpool. And, of course, resource for developing is precious so I think it would be more productive to focus on pgpool-II developement. Comments? suggestions? -- Tatsuo Ishii SRA OSS, Inc. Japan ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] The forgotten question
What I understood about this is that you have to know what backend might or will be part of the pool. So, let's say you have only two backends, but you wish to add a 3rd. Initially, you have: backend_hostname0 = '172.16.10.10' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/postgresql/8.3/main' backend_hostname1 = '172.16.10.11' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/8.3/main' But you would have to add: backend_hostname2 = '172.16.10.12' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/var/lib/postgresql/8.3/main' Even if it is not still there. When restarting pgpool, it will try to add the 3 nodes, but the last one (let's say) is not ready yet. After you start, you are going to be able to call the attach command specifying to add the node 2 (zero-based node count) Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Marcelo Martins Sent: Thursday, December 18, 2008 1:54 PM To: Tatsuo Ishii Cc: pgpool-general@pgfoundry.org Subject: [Pgpool-general] The forgotten question In the pgpool page there is section under online recovery that says "A recovery target node must have detached before doing online recovery. If you wish to add PostgreSQL server dynamically, add backend_* parameters and reload pgpool.conf. pgpool-II registers a new node as a detached node. ". How should that "backend_*" be configured in the pgpool.conf file so that I could add nodes dynamically when needed. Say I already have 3 backend_nodes that are already configured and I would like to have the option to attache new nodes dynamically without having to edit the pgpool.conf file and reload it. I assume that such is what that comment above refers to be possible, am I interpreting that wrong ? So should I have something like the below for that to work ? backend_hostname0 = '172.16.10.10' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/postgresql/8.3/main' backend_hostname1 = '172.16.10.11' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/8.3/main' backend_* Marcelo PostgreSQL DBA Linux/Solaris System Administrator http://www.zeroaccess.org On Dec 18, 2008, at 12:21 PM, Marcelo Martins wrote: > Hi Tatsuo, > > I understand that pgpool does pooling by saving the connections to PG > and reusing them when the same user/database is used and indeed I see > some pcp procs being reused 40+ times. What I'm trying to figure out > here is, does pgpool just passes the new query it receives through > that same connection that has already been opened previously and such > will be reused by this new request coming from the same user to the > same database ? > > How does pgpool queues its incoming connections when it starts to > receive more connections than num_init_children is available ? I'm > pretty sure here that the "child_life_time" setting would be the one > responsible for freeing up the pgpool a child so that the new > connection queued can obtain access to PG through pgpool and execute > its query, correct ? > > In regards to the load balancing, that can indeed be very helpful > specially since the master node is usually the one with a higher load. > I'm pretty sure this may not be possible right now but it would be > pretty cool if pgpool only opened a connection to the backend that it > chooses to run the SELECT query against. I'm pretty sure this may be > complicated to implement, if it all possible which may not be, since > this would affect how pgpool handles connections. > > > Also you were right about the online recovery scripts. If I skip the > second base backup it seems 30-50% faster in most cases. What takes > the longest time is just the checkpoint that pg_start_backup has to do > while there is a lot of writes are being done to the DB. But the new > online recovery setting makes things perfect since the client just > keeps on trying to send the data over and eventually when the 2nd > stage is over the rest of all data resumes to be sent. > > can't remember the other questions right now, sorry :) > > > > > Marcelo > PostgreSQL DBA > Linux/Solaris System Administrator > http://www.zeroaccess.org > > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool Online Recovery scripts example
Thanks for sharing! I have similar scripts to copy-base-backup and pgpool-start-remote. I think I'm not needing pgpool-recovery-pitr. Am I wrong or the pitr one is in case I don't use a two stages recovery? Thanks, Daniel -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Marcelo Martins Sent: Monday, December 15, 2008 12:58 PM To: Tatsuo Ishii Cc: pgpool-general@pgfoundry.org Subject: [Pgpool-general] pgpool Online Recovery scripts example These have been tested under PostgreSQL 8.3.X versions I have also tested these under stable version of pgpool and also the latest CVS version I'm sure there are things that can be improved/changed so feel free to share that. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] inconsistency when using two pgpool instances inwarm standby?
Hello, I have the exact same doubt. However, in my system, appservers "A" and "B" don't run at the same time. While "A" is active, "B" is inactive and viceversa. In the case "A" becomes unavailable, "B" takes over. So, the scenario in this case would be: Pgpool in both appservers are running and connected to Master DB. Each pgpool instance can be accessed at any time, but the application running on each server will do stuff with its local pgpool only if it becomes active. So, for example: 1. Network connection between "A" and Master DB goes down. 2. "A" application still runs and connects to pgpool, which detects the failure and issues failover. 3. "A" app is interacting with Backup DB through its local Pgpool. 4. "A" app goes down. 5. "B" takes over. 6. "B" app is still connected to its local pgpool which has no problems connecting to Master DB. 7. While "A" already saved data into Backup DB, "B" is saving into Master DB. Result: inconsistency. I think it's kind of the same problem. Any workaround? Thanks, Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Duco Fijma Sent: Thursday, December 11, 2008 9:34 AM To: pgpool-general@pgfoundry.org Subject: [Pgpool-general] inconsistency when using two pgpool instances inwarm standby? Hello, I'm designing a warm-standby Postgres/pgpool system accepting connections from a number of application servers. Of course, running a single instance of pgpool introduces a single point of failure. However, I'm having difficulties seeing how multiple instances of pgpool, in general, can have a consistent picture of the (failure) state of the "master" database. For example, consider two appservers "A" and "B", both running an instance of pgpool. Additionally, we have a master postgres database server and a standby postgres database server. In that situation, the network connection between "B" and the master database server is going down. The pgpool instance on B detect a failure and triggers a failover to the standby server, which therefore exits recovery mode and starts accepting database connections. Especially in a design without single points of failure, the network connection between appserver "A" and the master database is likely not to be failing at the same time. Therefore, from the perspective of appserver "A" the master database is not failing at all. Its pgpool instance therefore happily continues to proxy database connections to the master database. The net effect is that our system is broken into two independent halves. It just takes one database transaction to make these two halves into two inconsistent systems :) I find it difficult to think of a solutions to this. Whatever schema I use, the very same failure that caused the database failover in the first place, can or will also hinder (direct or indirect) communication between the two pgpool instances to prevent inconsistent failover states. -- Duco ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
