Menu
▾
▴
Re: [Postgres-xc-general] in db import, XC node replicted with all but the actual data.
|
From: Mason S. <ma...@st...> - 2013-06-05 12:30:14
|
On Wed, Jun 5, 2013 at 7:37 AM, Andrei Martsinchyk < and...@gm...> wrote: > Hi Iván, > > First of all, you should not replicate all your tables. No such case when > it might be reasonable, except maybe some ultimate test case. Single > Postgres server would perform better then your four-node cluster in any > application. So think again about the distribution planning. > It might be ok if the data set is relatively small (fits in memory on all nodes) and there is very high read-only concurrency with multiple coordinators. Anyway, I agree, in general distributing tables is the thing to do. > > Regarding your problem, I guess node definitions was different at the > moment when you created your tables. > To verify, please run following query on all your coordinators: > > select nodeoids from pgxc_class; > > The result should look like this: > > nodeoids > --------------------------- > 16386 16387 16388 16389 > 16386 16387 16388 16389 > ... > (N rows) > > If you see less then four node OIDs in some or all rows that is the cause. > > > > > > 2013/6/5 seikath <se...@gm...> > >> Hello guys, >> >> I am facing one problem with XC I would like to know if its common or not. >> >> 4 AWS based XC nodes installed with datanode and coordinator on each one, >> then I have separated one gtm-proxy and one gtm node. >> version used: >> psql (Postgres-XC) 1.0.3 >> (based on PostgreSQL) 9.1.9 >> >> once installed, it operates OK as db/roles creation , database import etc. >> >> The issue is, on db import, I get only three of the nodes replicated , as >> all the tables definitions are with *distribute by replication* : >> >> xzcat prod-db01-new.2013-06-04.12.31.34.sql.xz | sed 'h;/^CREATE >> TABLE/,/^);/s/;/ DISTRIBUTE BY REPLICATION;/' | psql -U postgres dbname >> I see no errors at the time of the dbimport , >> the tables , indexes , pkeys are replicated, just the data is only at >> three of the 4 active nodes. >> >> Details : >> >> # coordinatods config: >> >> boxes@vpc-xc-coord-01:5432::boxes_production=[Wed Jun 5 08:41:26 UTC 2013]> select * from pgxc_node; >> node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id >> ------------+-----------+-----------+-----------+----------------+------------------+------------- >> coord01 | C | 5432 | localhost | f | f | -951114102 >> datanode01 | D | 6543 | localhost | t | t | -561864558 >> coord02 | C | 5432 | 10.0.1.12 | f | f | -1523582700 >> datanode02 | D | 6543 | 10.0.1.12 | f | f | 670480207 >> coord03 | C | 5432 | 10.0.1.13 | f | f | 1641506819 >> datanode03 | D | 6543 | 10.0.1.13 | f | f | -1804036519 >> coord04 | C | 5432 | 10.0.1.14 | f | f | -1385444041 >> datanode04 | D | 6543 | 10.0.1.14 | f | f | 1005050720 >> (8 rows) >> >> postgres@vpc-xc-coord-02:5432::boxes_production=[Wed Jun 5 08:42:24 UTC 2013]# select * from pgxc_node; >> node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id >> ------------+-----------+-----------+-----------+----------------+------------------+------------- >> coord02 | C | 5432 | localhost | f | f | -1523582700 >> datanode02 | D | 6543 | localhost | f | t | 670480207 >> coord01 | C | 5432 | 10.0.1.11 | f | f | -951114102 >> datanode01 | D | 6543 | 10.0.1.11 | t | f | -561864558 >> coord03 | C | 5432 | 10.0.1.13 | f | f | 1641506819 >> datanode03 | D | 6543 | 10.0.1.13 | f | f | -1804036519 >> coord04 | C | 5432 | 10.0.1.14 | f | f | -1385444041 >> datanode04 | D | 6543 | 10.0.1.14 | f | f | 1005050720 >> (8 rows) >> postgres@vpc-xc-coord-03:5432::boxes_production=[Wed Jun 5 08:42:57 UTC 2013]# select * from pgxc_node; >> node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id >> ------------+-----------+-----------+-----------+----------------+------------------+------------- >> coord03 | C | 5432 | localhost | f | f | 1641506819 >> datanode03 | D | 6543 | localhost | f | t | -1804036519 >> coord01 | C | 5432 | 10.0.1.11 | f | f | -951114102 >> datanode01 | D | 6543 | 10.0.1.11 | t | f | -561864558 >> coord02 | C | 5432 | 10.0.1.12 | f | f | -1523582700 >> datanode02 | D | 6543 | 10.0.1.12 | f | f | 670480207 >> coord04 | C | 5432 | 10.0.1.14 | f | f | -1385444041 >> datanode04 | D | 6543 | 10.0.1.14 | f | f | 1005050720 >> >> postgres@vpc-xc-coord-04:5432::boxes_production=[Wed Jun 5 08:20:35 UTC 2013]# select * from pgxc_node; >> node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id >> ------------+-----------+-----------+-----------+----------------+------------------+------------- >> coord04 | C | 5432 | localhost | f | f | -1385444041 >> datanode04 | D | 6543 | localhost | f | t | 1005050720 >> coord01 | C | 5432 | 10.0.1.11 | f | f | -951114102 >> datanode01 | D | 6543 | 10.0.1.11 | t | f | -561864558 >> coord02 | C | 5432 | 10.0.1.12 | f | f | -1523582700 >> datanode02 | D | 6543 | 10.0.1.12 | f | f | 670480207 >> coord03 | C | 5432 | 10.0.1.13 | f | f | 1641506819 >> datanode03 | D | 6543 | 10.0.1.13 | f | f | -1804036519 >> >> >> fist node coordinator and datanodes config: >> ==================================================================== >> >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:40:00][/usr/local/pgsql]$ cat datanode.postgresql.conf >> listen_addresses = '*' # what IP address(es) to listen on; >> port = 6543 # (change requires restart) >> max_connections = 100 # (change requires restart) >> shared_buffers = 320MB # min 128kB >> max_prepared_transactions = 100 # zero disables the feature >> datestyle = 'iso, mdy' >> lc_messages = 'en_US.UTF-8' # locale for system error message >> lc_monetary = 'en_US.UTF-8' # locale for monetary formatting >> lc_numeric = 'en_US.UTF-8' # locale for number formatting >> lc_time = 'en_US.UTF-8' # locale for time formatting >> default_text_search_config = 'pg_catalog.english' >> include '/usr/local/pgsql/gtm.include.conf' >> include '/usr/local/pgsql/datanode_node_name.conf' >> #pgxc_node_name = 'datanode04' # Coordinator or Datanode name >> enforce_two_phase_commit = on # Enforce the usage of two-phase commit on transactions >> enable_fast_query_shipping = on >> enable_remotejoin = on >> enable_remotegroup = on >> >> >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:43:29][/usr/local/pgsql]$ cat coordinator.postgresql.conf >> listen_addresses = '*' # what IP address(es) to listen on; >> port = 5432 # (change requires restart) >> max_connections = 100 # (change requires restart) >> shared_buffers = 120MB # min 128kB >> max_prepared_transactions = 100 # zero disables the feature >> datestyle = 'iso, mdy' >> lc_messages = 'en_US.UTF-8' # locale for system error message >> lc_monetary = 'en_US.UTF-8' # locale for monetary formatting >> lc_numeric = 'en_US.UTF-8' # locale for number formatting >> lc_time = 'en_US.UTF-8' # locale for time formatting >> default_text_search_config = 'pg_catalog.english' >> pooler_port = 6667 # Pool Manager TCP port >> min_pool_size = 1 # Initial pool size >> max_pool_size = 100 # Maximum pool size >> max_coordinators = 16 # Maximum number of Coordinators >> max_datanodes = 16 # Maximum number of Datanodes >> include '/usr/local/pgsql/gtm.include.conf' >> include '/usr/local/pgsql/coordinator_node_name.conf' >> enforce_two_phase_commit = on # Enforce the usage of two-phase commit on transactions >> enable_fast_query_shipping = on >> enable_remotejoin = on >> enable_remotegroup = on >> >> >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:43:38][/usr/local/pgsql]$ cat /usr/local/pgsql/gtm.include.conf >> gtm_host = '10.0.1.16' # Host name or address of GTM Proxy, if not - direct link to GTM >> gtm_port = 6543 >> >> #gtm_host = '127.0.0.1' >> #gtm_port = 5434 >> >> boxes@vpc-xc-coord-01:5432::boxes_production=[Wed Jun 5 08:42:04 UTC 2013]> select count(*) from friends; >> count >> ------- >> 1 >> (1 row) >> >> Time: 4.698 ms >> >> postgres@vpc-xc-coord-02:5432::boxes_production=[Wed Jun 5 08:42:25 UTC 2013]# select count(*) from friends; >> count >> ------- >> 41416 >> (1 row) >> >> postgres@vpc-xc-coord-03:5432::boxes_production=[Wed Jun 5 08:43:01 UTC 2013]# select count(*) from friends; >> count >> ------- >> 41416 >> (1 row) >> >> postgres@vpc-xc-coord-04:5432::boxes_production=[Wed Jun 5 08:42:53 UTC 2013]# select count(*) from friends; >> count >> ------- >> 41416 >> (1 row) >> >> # identical configs : >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:53:58][/usr/local/pgsql]$ >> md5sum coordinator.postgresql.conf >> b7f61b5d8baeec83cd82d9f1ee744728 coordinator.postgresql.conf >> postgres@vpc-xc-coord-02:[Wed Jun 05 08:53:53][~]$ md5sum >> coordinator.postgresql.conf >> b7f61b5d8baeec83cd82d9f1ee744728 coordinator.postgresql.conf >> >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:54:58][/usr/local/pgsql]$ md5sum >> datanode.postgresql.conf >> 00d6a5736b6401dc6cc3d820fb412082 datanode.postgresql.conf >> postgres@vpc-xc-coord-02:[Wed Jun 05 08:54:37][~]$ md5sum >> datanode.postgresql.conf >> 00d6a5736b6401dc6cc3d820fb412082 datanode.postgresql.conf >> >> postgres@vpc-xc-coord-01:[Wed Jun 05 08:55:24][/usr/local/pgsql]$ md5sum >> /usr/local/pgsql/gtm.include.conf >> a6e7c3a21958a23bfb5054dc645d9576 /usr/local/pgsql/gtm.include.conf >> postgres@vpc-xc-coord-02:[Wed Jun 05 08:55:01][~]$ md5sum >> /usr/local/pgsql/gtm.include.conf >> a6e7c3a21958a23bfb5054dc645d9576 /usr/local/pgsql/gtm.include.conf >> >> >> ==================================================================== >> I >> In general I suspect wrong configuration, but atm can not find any. >> >> I did a test importing the same db at the second XC node, and its the >> same issue: the fixt XC gets eveyrthing replicated but the actual data. >> >> My plan is to launch new clone instance of the vpc-xc-coord-02 as a >> replacement of the vpc-xc-coord-01, but this is the desperate plan C >> I want to know what happens .. :) >> >> >> Kind regards, >> >> Iván >> >> >> >> >> >> ------------------------------------------------------------------------------ >> How ServiceNow helps IT people transform IT departments: >> 1. A cloud service to automate IT design, transition and operations >> 2. Dashboards that offer high-level views of enterprise services >> 3. A single system of record for all IT processes >> http://p.sf.net/sfu/servicenow-d2d-j >> _______________________________________________ >> Postgres-xc-general mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general >> >> > > > -- > Andrei Martsinchyk > > StormDB - http://www.stormdb.com > The Database Cloud > > > > ------------------------------------------------------------------------------ > How ServiceNow helps IT people transform IT departments: > 1. A cloud service to automate IT design, transition and operations > 2. Dashboards that offer high-level views of enterprise services > 3. A single system of record for all IT processes > http://p.sf.net/sfu/servicenow-d2d-j > _______________________________________________ > Postgres-xc-general mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general > > -- Mason Sharp StormDB - http://www.stormdb.com The Database Cloud Postgres-XC Support and Services |
