how to add database in a new cluster in postgreSQL?

Question

I am using

sudo pg_createcluster -u=$USER --datadir=candisDatabase -l=candisDatabase/candis.psql.log 9.6 candisCluster

to create a new database cluster with the specified directory as candisDatabase under the account $USER (which is 'rupav').

pg_lsclusters
Ver Cluster        Port Status Owner    Data directory               Log file
9.6 candisCluster2 5433 online   rupav    candisDatabase               candisDatabase/candis.psql.log
9.6 main           5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

cluster is created successfully, and am able to run/start the new cluster and server candisCluster2 with pg_ctlcluster 9.6 candisCluster2 start successfully

following is the status of the new cluster (using pg_ctlcluster 9.6 candisCluster2 status) :

/usr/lib/postgresql/9.6/bin/postgres "-D" "candisDatabase" "-c" "config_file=/etc/postgresql/9.6/candisCluster2/postgresql.conf"

Now that server is listening on 5433 port, how can I add create role, database and password in the given cluster. By default (after postgresql installation) I can do sudo -u postgres psql to enter into the psql shell.

In short, I would like to have all my tables in the data directory specified, as candisDatabase which is owned by user different from postgres.

System details: I am on 'Ubuntu 17.10 (Oracle VM, host being windows)' and postgresql version is 9.6

Answer 1

Given that you specified user you don't want to su to postgres, su to the user you specified instead.

if it's you just do

psql "port=5433 dbname=postgres"