HOW TO CREATE A DATABASE IN POSTGRESQL

Creating a database in postgres is pretty simple in postgres

Create a basic role first for the database

[postgres@DBZX21 ~]$ psql
psql (10.21)
Type "help" for help.

postgres=# CREATE ROLE prole
postgres-# SUPERUSER
postgres-# LOGIN
postgres-# PASSWORD 'prole';
CREATE ROLE

Create a directory for datafiles to be physically stored and give permissions

[oracle@DBZX21 ~]$ mkdir -p /u01/postgre/datafile
[root@DBZX21 u01]# chmod -R 775 postgre/
[root@DBZX21 u01]# chown -R postgres:postgres postgre/

[root@DBZX21 u01]# ls -lrt postgre/
total 0
drwx------ 3 postgres postgres 29 Nov  9 15:26 datafile

Create a tablespace to be used by default for application data

postgres=# CREATE TABLESPACE ptab
postgres-# OWNER prole
postgres-# LOCATION '/u01/postgre/datafile';
CREATE TABLESPACE

Create a database with all the above specified values for parameters

postgres=# CREATE DATABASE pgre
postgres-# WITH
postgres-#     OWNER = prole
postgres-#     ENCODING = 'UTF8'
postgres-#     TABLESPACE = ptab
postgres-#     CONNECTION LIMIT = 30;
CREATE DATABASE

Leave a Reply