The first action you should do is entering into psql mode as
| Code Block |
|---|
sudo -u postgres psql |
Below is a common script can create a database in psql
| Code Block |
|---|
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:
The result will be like:
| Code Block |
|---|
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:
| Code Block |
|---|
sudo -u postgres psql -c "CREATE USER your_user_id with password 'your_password' superuser;" |
| Code Block |
|---|
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
| Code Block |
|---|
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'; |
| Code Block |
|---|
sudo -u postgres psql -c "create database newid with encoding='utf8' template=template0;" |
| Code Block |
|---|
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' |