Re: [Postgres-xc-general] drop user get wrong, it shows "Failed to get pooled connections", involvi

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Thank you for pointing out.   The document looks to need some
improvement on the node management.  You're correct that you need to
issue pgxc_pool_reload().   Before this, you need to create new node
ready to this step.
---
Koichi Suzuki


2014-03-04 1:17 GMT+09:00 Lin Wang <lin...@gm...>:
>
> Thank you very much,Koichi Suzuki. From postgres-xc 1.1 Documentation
> "Chapter 30. Adding a New Node", I learn it how to add a new node to the
> cluster. I find it: when I create node 'node100' in the session of A, the
> node100 is not efficient for the cluster untile execute 'select
> pgxc_pool_reload()' or I open a new session B, in the session  B the node100
> is efficient.  as the reason of this, when I create node node100, then
> use'\c postgres' ,the session shows"psql (9.3beta2, server 9.2.4)" , so I
> know it open a new session, the node100 is efficient, but I don't initdb the
> node of node100, so it gets wrong. just like as below:
>
> [wln@localhost pgxc]$ psql -d postgres -p 5300
> psql (9.3beta2, server 9.2.4)
> Type "help" for help.
>
> postgres=# select * from pgxc_node;
>   node_name   | node_type | node_port | node_host | nodeis_primary |
> nodeis_preferred |  node_id
> --------------+-----------+-----------+-----------+----------------+------------------+------------
>  datanode1    | D         |      5302 | localhost | f              | f
> |  888802358
>  datanode2    | D         |      5304 | localhost | f              | f
> | -905831925
>  coordinator1 | C         |      5300 | localhost | f              | f
> | 1938253334
> (3 rows)
>
> postgres=# create node node100
> with(type='datanode',host=localhost,port=7809);
> CREATE NODE
> postgres=# create table t1(id int);
> CREATE TABLE
> postgres=# insert into t1 values(1);
> INSERT 0 1
> postgres=# explain select * from t1;
>                                  QUERY PLAN
> ----------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Node/s: datanode1, datanode2
> (2 rows)
>
> postgres=# create user user1 with SUPERUSER;
> CREATE ROLE
> postgres=# set role user1;
> SET
> postgres=# \c postgres
> psql (9.3beta2, server 9.2.4)
> You are now connected to database "postgres" as user "wln".
> postgres=# create table t2 (id int);
> ERROR:  Failed to get pooled connections
>
>
>    > 2014-03-03 12:27 GMT+08:00 Koichi Suzuki <koi...@gm...>:
>
>> > Did you run initdb for node100 before you run CREATE NODE node100?
>> > Because you already have created database, you need to run initdb and
>> > copy catalog from other datanode to node100.
>>
>> > If you're adding new datanode to the cluster, you should do the
>> > following:
>> > 1. Run initdb to initialize new datanode resources,
>> > 2. Prepare at least postgresql.conf and pg_hba.conf for the new
>> > datanode.
>> > 3. Fun pg_dumpall on an existing datanode to dump all the catalog
>> > info.   You should specify --include-nodes and --dump-nodes option to
>> > dump everything in the catalog.
>> > 4. Start the new datanode using pg_ctl with -Z restoremode option.
>> > 5. Restore the backed up catalog you build at the step 3 by running
>> > psql against the new datanode.
>> > 6. Stop the new datanode and restart it with -Z datanode option
>> > (without -Z restoremode option).
>> > 7. Issue CREATE NODE statement at all the coordinators.
>>
>> > You seem to do only the step 7.
>>
>> > If you're not sure what to do, plase take a look at the function
>> > add_datanodeMaster() in datanode_cmd.c at contrib/pgxc_ctl directory.
>> >  Of course, the simplest way is to configure your cluster with
>> > pgxc_ctl.   Documentation will be available at
>> > http://postgres-xc.sourceforge.net/docs/1_1/pgxc-ctl.html
>>
>> > Pgxc_ctl also locks the entire cluster while you're adding new node to
>> > maintain cluster consistent.
>>
>> > Good Luck;
>> > ---
>> > Koichi Suzuki
>>
>>
>> > 2014-03-02 14:19 GMT+09:00 Lin Wang <lin...@gm...>:
>> > Hi,
>> >       first I create user1 then use user1 create a node;   seceond, I
>> > use
>> > 'set role wln' to return the user of the superuser of  cluster; last , I
>> > want to use 'drop user user1;' to drop the user1, but it shows "Failed
>> > to
>> > get pooled connections". (use postgres-xc1.1).
>> > the detailed content as below:
>> > [wln@localhost pgxc]$ psql -d postgres -p 5300
>> > psql (9.3beta2, server 9.2.4)
>> > Type "help" for help.
>> >
>> > postgres=# create user user1 with SUPERUSER;
>> > CREATE ROLE
>> > postgres=# set role user1;
>> > SET
>> > postgres=# create node node100
>> > with(type='datanode',host=localhost,port=7809);
>> > CREATE NODE
>> > postgres=# set role wln;
>> > SET
>> > postgres=# drop user user1;
>> > DROP ROLE
>> > postgres=# create user user1 with SUPERUSER;
>> > CREATE ROLE
>> > postgres=# set role user1;
>> > SET
>> > postgres=# \c postgres
>> > psql (9.3beta2, server 9.2.4)
>> > You are now connected to database "postgres" as user "wln".
>> > postgres=#  drop user user1;
>> > ERROR:  Failed to get pooled connections
>> > postgres=# select oid, * from pgxc_node;
>> >   oid  |  node_name   | node_type | node_port | node_host |
>> > nodeis_primary |
>> > nodeis_preferred |  node_id
>> >
>> > -------+--------------+-----------+-----------+-----------+----------------+------------------+------------
>> >  16384 | datanode1    | D         |      5302 | localhost | f
>> > |
>> > f                |  888802358
>> >  16385 | datanode2    | D         |      5304 | localhost | f
>> > |
>> > f                | -905831925
>> >  16386 | coordinator1 | C         |      5300 | localhost | f
>> > |
>> > f                | 1938253334
>> >  16388 | node100      | D         |      7809 | localhost | f
>> > |
>> > f                | 1668042070
>> > (4 rows)
>> >
>> > log:
>> > 2014-03-01 06:39:18.543 CST 53111016.6fe0 postgres 28640 psql 0
>> > 00000DEBUG:
>> > [re]setting xid = 0, old_value = 0
>> > 2014-03-01 06:39:18.545 CST 53110fe1.6fd1 postgres 28625 psql 0
>> > 00000DEBUG:
>> > Postmaster child: connection to GTM closed
>> > 2014-03-01 06:39:25.370 CST 53111016.6fe0 postgres 28640 psql 0
>> > 00000DEBUG:
>> > Postmaster child: connection established to GTM with string
>> > host=localhost
>> > port=5307 node_name=coordinator1
>> > 2014-03-01 06:39:25.371 CST 53111016.6fe0 postgres 28640 psql 0
>> > 00000DEBUG:
>> > Assigned new transaction ID from GTM = 10014
>> > 2014-03-01 06:39:25.371 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 00000DEBUG:  Getting snapshot. Current XID = 10014
>> > 2014-03-01 06:39:25.372 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 00000DEBUG:  from GTM: xmin = 10014, xmax = 10014, xcnt = 0, RecGlobXmin
>> > =
>> > 10014
>> > 2014-03-01 06:39:25.394 CST 53110fd8.6fac  28588  0 08006LOG:  failed to
>> > connect to Datanode
>> > 2014-03-01 06:39:25.394 CST 53110fd8.6fac  28588  0 01000WARNING:  can
>> > not
>> > connect to node 16388
>> > 2014-03-01 06:39:25.394 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 53000LOG:  failed to acquire connections
>> > 2014-03-01 06:39:25.394 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 53000STATEMENT:  drop user user1;
>> > 2014-03-01 06:39:25.394 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 53000ERROR:  Failed to get pooled connections
>> > 2014-03-01 06:39:25.394 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 53000STATEMENT:  drop user user1;
>> > 2014-03-01 06:39:25.394 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 00000DEBUG:  Record transaction abort 10014
>> > 2014-03-01 06:39:25.395 CST 53111016.6fe0 postgres 28640 psql 10014
>> > 00000DEBUG:  [re]setting xid = 0, old_value = 0
>> > 2014-03-01 06:40:16.315 CST 53110fd8.6fba  28602  0 00000DEBUG:
>> > Autovacuum
>> > launcher: connection established to GTM with string host=localhost
>> > port=5307
>> > node_name=coordinator1
>> >
>> >
>> > Thanks
>> > waln
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Flow-based real-time traffic analytics software. Cisco certified tool.
>> > Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer
>> > Customize your own dashboards, set traffic alerts and generate reports.
>> > Network behavioral analysis & security monitoring. All-in-one tool.
>> >
>> > http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Postgres-xc-general mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >

Re: [Postgres-xc-general] drop user get wrong, it shows "Failed to get pooled connections", involvi

Re: [Postgres-xc-general] drop user get wrong, it shows "Failed to get pooled connections", involving create node