The first action you should do is entering into psql mode as

sudo -u postgres psql


Below is a common script can create a database in psql

update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UTF8';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';

create database mydb with encoding='utf8' template=template0;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;


If everything goes fine, you can check your created database by \list command as below:

\list


The result will be like:

 mydb       | postgres | UTF8      | en_US.UTF-8 | C     | =Tc/postgres           +
            |          |           |             |       | postgres=CTc/postgres  +
            |          |           |             |       | myuser=CTc/postgres
 postgres   | postgres | SQL_ASCII | en_US.UTF-8 | C     |
 template0  | postgres | SQL_ASCII | en_US.UTF-8 | C     | =c/postgres            +
            |          |           |             |       | postgres=CTc/postgres
 template1  | postgres | UTF8      | en_US.UTF-8 | C     |


You can also make psql commands one by one by adding -c parameter:

sudo -u postgres psql -c "CREATE USER your_user_id with password 'your_password' superuser;"
sudo -u postgres psql -c 'create database confluence with owner your_user_id;'


If you want to encode in UTF8, you need to change teamplate0 in UTF8 first

sudo -u postgres psql
update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UTF8';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';
sudo -u postgres psql -c "create database newid with encoding='utf8' template=template0;"
CREATE DATABASE db_name
 OWNER =  role_name
 TEMPLATE = template
 ENCODING = encoding
 LC_COLLATE = collate
 LC_CTYPE = ctype
 TABLESPACE = tablespace_name
 CONNECTION LIMIT = max_concurrent_connection


WITH ENCODING='UTF8'
  • No labels