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

[Koichi S. <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
>