Re: [Pgpool-general] Pgpool-II - replication problems
nir osovsky wrote: I urgently need your help here. Please reply to my previous email and let me know if you have any idea what the problem is. Try setting pgpool-II in debug mode and see what you get in the log. That is why you have logs, don't you? ;-) -- 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] Pgpool-II - replication problems
nir osovsky wrote: My questions are: 1. Why the above insert command failed? Have you properly set up the users in pg_hba.conf in both pgsql servers? 2. What do I need to configure in order for any change to the testdb on dev1 to replicate automaticly to testdb on dev2? In your pgpool.conf file, you need to have this directive: replication_mode = true And also you need to configure these: backend_hostname backend_port backend_weight backend_data_directory Optionally, you may also want to enable this one: load_balance_mode = true But it does not have anything to do with replicating information. Other parameters may need to be adjusted from their default values depending on your specific configuration. Check these two for more information and examples: http://pgpool.projects.postgresql.org/ http://www.pgcon.org/2008/schedule/attachments/66_pgpool-II-demo.pdf -- 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] Pgpool-II - replication problems
Can you show me the error log of PostgreSQL? Also is it possible to try the latest version of of pgpool-II (2.1)? -- Tatsuo Ishii SRA OSS, Inc. Japan Hi, I'm trying to configure pgpool-II 2.to to replication between two computers and postgresql database in each. pgpool-II is defined on dev1. postgresql 8.1.11 (testdb) is defined on dev1 postgresql 8.1.11 (testdb) is defined on dev2 I can run the following commands from each computer and run queries from the databases: psql -h dev1 -U postgres testdb psql -h dev2 -U postgres testdb I'm also able to do and see the databases bench_replication created on both computers and have the same data: createdb -p bench_replication pgbench -i -p bench_replication While trying to run: psql -p -c INSERT INTO branches VALUES (3,1,' ') bench_replication I'm getting: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost And the /tmp/pgpool.log show: 2008-11-08 22:41:53 DEBUG: pid 23744: I am 23744 accept fd 0 2008-11-08 22:41:53 DEBUG: pid 23744: Protocol Major: 3 Minor: 0 database: bench_replication user: postgres 2008-11-08 22:41:53 DEBUG: pid 23744: new_connection: connecting 0 backend 2008-11-08 22:41:53 DEBUG: pid 23744: new_connection: connecting 1 backend 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 0 length: 8 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 1 length: 8 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: client_encoding value: UTF8 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: client_encoding value: UTF8 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 23 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 23 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: DateStyle value: ISO, MDY 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: DateStyle value: ISO, MDY 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 26 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 26 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: integer_datetimes value: off 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: integer_datetimes value: off 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 20 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 20 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: is_superuser value: on 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: is_superuser value: on 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: server_encoding value: UTF8 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: server_encoding value: UTF8 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 25 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: server_version value: 8.1.2 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: server_version value: 8.1.2 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 35 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 35 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: session_authorization value: postgres 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: session_authorization value: postgres 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 36 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 36 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: standard_conforming_strings value: off 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: standard_conforming_strings value: off 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 0 length: 21 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length2: master slot: 1 length: 21 2008-11-08 22:41:53 DEBUG: pid 23744: 0 th backend: name: TimeZone value: PST8PDT 2008-11-08 22:41:53 DEBUG: pid 23744: 1 th backend: name: TimeZone value: PST8PDT 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length: slot: 0 length: 12 2008-11-08 22:41:53 DEBUG: pid 23744: pool_read_message_length:
Re: [Pgpool-general] Pgpool-II - replication problems
Thank you, Jaume. Attached my pg_hba.conf (identical in 2 servers) and pgpool.conf. I appreciate if you can let me know if I need to change something. pg_hba.conf = # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 192.168.158.38/32 trust hostall all 192.168.158.39/32 trust # IPv6 local connections: hostall all ::1/128 trust 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 = 32 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/tmp' # Replication mode replication_mode = true replication_timeout = 9000 load_balance_mode = true replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' failover_command = '' failback_command = '' insert_lock = false ignore_leading_white_space = true log_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 = 'localhost' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/usr/local/pgsql/data' backend_hostname1 = '192.168.158.39' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/usr/local/pgsql/data' # - HBA - #enable_pool_hba = false enable_pool_hba = true recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' Thanks, Nir On Sun, Nov 9, 2008 at 3:34 AM, Jaume Sabater [EMAIL PROTECTED]wrote: nir osovsky wrote: My questions are: 1. Why the above insert command failed? Have you properly set up the users in pg_hba.conf in both pgsql servers? 2. What do I need to configure in order for any change to the testdb on dev1 to replicate automaticly to testdb on dev2? In your pgpool.conf file, you need to have this directive: replication_mode = true And also you need to configure these: backend_hostname backend_port backend_weight backend_data_directory Optionally, you may also want to enable this one: load_balance_mode = true But it does not have anything to do with replicating information. Other parameters may need to be adjusted from their default values depending on your specific configuration. Check these two for more information and examples: http://pgpool.projects.postgresql.org/ http://www.pgcon.org/2008/schedule/attachments/66_pgpool-II-demo.pdf -- 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