[postgres-xl-bugs] duplicate key value violates unique constraint "pg_database_datname_index"

Chandra N chandra.blogging at gmail.com
Sun Jan 14 05:19:32 PST 2018


Hey folks,
                     Novice here. Setting up a cluster on my localhost and
got as far as initiating the cluster. Now when I try to create a database,
pgxc_ctl gives me the following error:

PGXC Createdb abcd;
Selected coord1.
createdb: database creation failed: ERROR:  duplicate key value violates
unique constraint "pg_database_datname_index"
DETAIL:  Key (datname)=(abcd) already exists.
CONTEXT:  Error message received from nodes: datanode#1 datanode#4

Appreciate your help, am sure I am making a silly mistake.

Below is my pgxc_ctl.conf:

# pgxcInstallDir variable is needed if you invoke "deploy" command from
pgxc_ctl utility.
# If don't you don't need this variable.
pgxcInstallDir=/usr/local/pgsql
#---- OVERALL
-----------------------------------------------------------------------------
#
pgxcOwner=postgres # owner of the Postgres-XC databaseo cluster.  Here, we
use this
# both as linus user and database user.  This must be
# the super user of each coordinator and datanode.
pgxcUser=$pgxcOwner # OS user of Postgres-XC owner

tmpDir=/tmp # temporary dir used in XC servers
localTmpDir=$tmpDir # temporary dir used here locally

configBackup=y # If you want config file backup, specify y to this value.
configBackupHost=pgxc-linker # host to backup config file
configBackupDir=$HOME/pgxc # Backup directory
configBackupFile=pgxc_ctl.bak # Backup file name --> Need to synchronize
when original changed.

dataDirRoot=/usr/local/pgsql

#---- GTM
------------------------------------------------------------------------------------

# GTM is mandatory.  You must have at least (and only) one GTM master in
your Postgres-XC cluster.
# If GTM crashes and you need to reconfigure it, you can do it by
pgxc_update_gtm command to update
# GTM master with others.   Of course, we provide pgxc_remove_gtm command
to remove it.  This command
# will not stop the current GTM.  It is up to the operator.

#---- Overall -------
gtmName=gtm

#---- GTM Master -----------------------------------------------

#---- Overall ----
gtmMasterServer=localhost
gtmMasterPort=6666
gtmMasterDir=/usr/local/pgsql/data_gtm

#---- Configuration ---
gtmExtraConfig=() # Will be added gtm.conf for both Master and Slave (done
at initilization only)
gtmMasterSpecificExtraConfig=() # Will be added to Master's gtm.conf (done
at initialization only)

#---- GTM Slave -----------------------------------------------

# Because GTM is a key component to maintain database consistency, you may
want to configure GTM slave
# for backup.

#---- Overall ------
gtmSlave=n # Specify y if you configure GTM Slave.   Otherwise, GTM slave
will not be configured and
# all the following variables will be reset.
gtmSlaveName=()
gtmSlaveServer=() # value none means GTM slave is not available.  Give none
if you don't configure GTM Slave.
gtmSlavePort=() # Not used if you don't configure GTM slave.
gtmSlaveDir=() # Not used if you don't configure GTM slave.
# Please note that when you have GTM failover, then there will be no slave
available until you configure the slave
# again. (pgxc_add_gtm_slave function will handle it)

#---- Configuration ----
gtmSlaveSpecificExtraConfig=() # Will be added to Slave's gtm.conf (done at
initialization only)

#---- GTM Proxy
-------------------------------------------------------------------------------------------------------
# GTM proxy will be selected based upon which server each component runs on.
# When fails over to the slave, the slave inherits its master's gtm proxy.
It should be
# reconfigured based upon the new location.
#
# To do so, slave should be restarted.   So pg_ctl promote -> (edit
postgresql.conf and recovery.conf) -> pg_ctl restart
#
# You don't have to configure GTM Proxy if you dont' configure GTM slave or
you are happy if every component connects
# to GTM Master directly.  If you configure GTL slave, you must configure
GTM proxy too.

#---- Shortcuts ------
gtmProxyDir=()

#---- Overall -------
gtmProxy=n # Specify y if you conifugre at least one GTM proxy.   You may
not configure gtm proxies
# only when you dont' configure GTM slaves.
# If you specify this value not to y, the following parameters will be set
to default empty values.
# If we find there're no valid Proxy server names (means, every servers are
specified
# as none), then gtmProxy value will be set to "n" and all the entries will
be set to
# empty values.
gtmProxyNames=() # No used if it is not configured
gtmProxyServers=() # Specify none if you dont' configure it.
gtmProxyPorts=() # Not used if it is not configured.
gtmProxyDirs=() # Not used if it is not configured.

#---- Configuration ----
gtmPxyExtraConfig=n # Extra configuration parameter for gtm_proxy.
Coordinator section has an example.

#---- Coordinators
----------------------------------------------------------------------------------------------------

#---- shortcuts ----------
coordMasterDir=$dataDirRoot
coordSlaveDir=$dataDirRoot
coordArchLogDir=$HOME/coord_archlog

#---- Overall ------------
coordNames=(coord1) # Master and slave use the same name
coordPorts=(5432) # Master server listening ports
poolerPorts=(6668) # Master pooler ports
#coordPgHbaEntries=(::1/128) # Assumes that all the coordinator
(master/slave) accepts
# the same connection
# This entry allows only $pgxcOwner to connect.
# If you'd like to setup another connection, you should
# supply these entries through files specified below.
coordPgHbaEntries=(127.0.0.1/32) # Same as above but for IPv4 connections

#---- Master -------------
coordMasterServers=(localhost) # none means this master is not available
coordMasterDirs=($coordMasterDir/data_coord1)
coordMaxWALsender=5 # max_wal_senders: needed to configure slave. If zero
value is specified,
# it is expected to supply this parameter explicitly by external files
# specified in the following. If you don't configure slaves, leave this
value to zero.
coordMaxWALSenders=($coordMaxWALsender)
# max_wal_senders configuration for each coordinator.

#---- Slave -------------
coordSlave=n # Specify y if you configure at least one coordiantor slave.
Otherwise, the following
# configuration parameters will be set to empty values.
# If no effective server names are found (that is, every servers are
specified as none),
# then coordSlave value will be set to n and all the following values will
be set to
# empty values.
coordSlaveSync=n # Specify to connect with synchronized mode.
coordSlaveServers=() # none means this slave is not available
coordSlavePorts=() # coordinator slave listening ports
coordSlavePoolerPorts=() # coordinator slave pooler ports
coordSlaveDirs=()
coordArchLogDirs=()

#---- Configuration files---
# Need these when you'd like setup specific non-default configuration
# These files will go to corresponding files for the master.
# You may supply your bash script to setup extra config lines and extra
pg_hba.conf entries
# Or you may supply these files manually.
coordExtraConfig=coordExtraConfig # Extra configuration file for
coordinators.
# This file will be added to all the coordinators'
# postgresql.conf
# Pleae note that the following sets up minimum parameters which you may
want to change.
# You can put your postgresql.conf lines here.
cat > $coordExtraConfig <<EOF
#================================================
# Added to all the coordinator postgresql.conf
# Original: $coordExtraConfig
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_connections = 100
hot_standby = off
EOF

# Additional Configuration file for specific coordinator master.
# You can define each setting by similar means as above.
coordSpecificExtraConfig=()
coordSpecificExtraPgHba=()

#---- Datanodes
-------------------------------------------------------------------------------------------------------

#---- Shortcuts --------------
datanodeMasterDir=$dataDirRoot
datanodeSlaveDir=$dataDirRoot
datanodeArchLogDir=$dataDirRoot/datanode_archlog

#---- Overall ---------------
#primaryDatanode= datanode1
datanodeNames=(datanode_1 datanode_2)
datanodePorts=(15432 15433) # Master and slave use the same port!
datanodePoolerPorts=(6669 6670) # Master and slave use the same port!
#datanodePgHbaEntries=(::1/128) # Assumes that all the coordinator
(master/slave) accepts
# the same connection
# This list sets up pg_hba.conf for $pgxcOwner user.
# If you'd like to setup other entries, supply them
# through extra configuration files specified below.
datanodePgHbaEntries=(127.0.0.1/32) # Same as above but for IPv4 connections

#---- Master ----------------
datanodeMasterServers=(localhost localhost) # none means this master is not
available.
# This means that there should be the master but is down.
# The cluster is not operational until the master is
# recovered and ready to run.
datanodeMasterDirs=($dataDirRoot/data_datanode_1
$dataDirRoot/data_datanode_2)
datanodeMaxWalSender=5 # max_wal_senders: needed to configure slave. If
zero value is
# specified, it is expected this parameter is explicitly supplied
# by external configuration files.
# If you don't configure slaves, leave this value zero.
datanodeMaxWALSenders=(5 5)
# max_wal_senders configuration for each datanode

#---- Slave -----------------
datanodeSlave=n # Specify y if you configure at least one coordiantor
slave.  Otherwise, the following
# configuration parameters will be set to empty values.
# If no effective server names are found (that is, every servers are
specified as none),
# then datanodeSlave value will be set to n and all the following values
will be set to
# empty values.
datanodeSlaveServers=() # value none means this slave is not available
datanodeSlavePorts=() # Master and slave use the same port!
datanodeSlavePoolerPorts=() # Master and slave use the same port!
#datanodeSlaveSync=y # If datanode slave is connected in synchronized mode
datanodeSlaveDirs=()
datanodeArchLogDirs=()

# ---- Configuration files ---
# You may supply your bash script to setup extra config lines and extra
pg_hba.conf entries here.
# These files will go to corresponding files for the master.
# Or you may supply these files manually.
datanodeExtraConfig=datanodeExtraConfig
cat > $datanodeExtraConfig <<EOF
#================================================
# Added to all the datanode postgresql.conf
# Original: $datanodeExtraConfig
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_connections = 100
hot_standby = off
EOF
# Additional Configuration file for specific datanode master.
# You can define each setting by similar means as above.
datanodeSpecificExtraConfig=()
datanodeSpecificExtraPgHba=()
#===================================================
# pgxc configuration file updated due to GTM master addition
#        20170416_20:22:58
gtmName=gtm
gtmMasterServer=localhost
gtmMasterPort=20001
gtmMasterDir=/usr/local/pgsql/data_gtm
#----End of reconfiguration -------------------------
#===================================================
# pgxc configuration file updated due to GTM master removal
#        20170416_20:29:32
gtmName=none
gtmMasterServer=none
gtmMasterPort=-1
gtmMasterDir=none
#----End of reconfiguration -------------------------
#===================================================
# pgxc configuration file updated due to GTM master addition
#        20170416_20:29:48
gtmName=gtm
gtmMasterServer=localhost
gtmMasterPort=20001
gtmMasterDir=/usr/local/pgsql/data_gtm
#----End of reconfiguration -------------------------
#===================================================
# pgxc configuration file updated due to GTM master removal
#        20170416_20:29:53
gtmName=none
gtmMasterServer=none
gtmMasterPort=-1
gtmMasterDir=none
#----End of reconfiguration -------------------------
#===================================================
# pgxc configuration file updated due to GTM master addition
#        20170416_20:36:03
gtmName=gtm
gtmMasterServer=localhost
gtmMasterPort=20001
gtmMasterDir=/usr/local/pgsql/data_gtm
#----End of reconfiguration -------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-bugs-postgres-xl.org/attachments/20180114/3f002c6c/attachment-0001.html>


More information about the postgres-xl-bugs mailing list