Following the last post about which tools you can use to create your PostgreSQL instance in this post we’ll look at how you can do the “create database” part that you would do in Oracle when not using the database configuration assistant. Of course can can create a database in PostgreSQL but it is not the same as it is in Oracle. To actually create your Oracle database you would do something like this once you have the instance started in nomount mode:
startup nomount pfile="/u01/app/oracle/admin/DB4/create/init.ora";
CREATE DATABASE "DB4"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u02/oradata/DB4/system01DB4.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/oradata/DB4/sysaux01DB4.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 2048M
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/DB4/temp01DB4.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/DB4/undotbs01DB4.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 2048M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/DB4/redog1m1DB4.dbf','/u04/oradata/DB4/redog1m2DB4.dbf') SIZE 50M,
GROUP 2 ('/u03/oradata/DB4/redog2m1DB4.dbf','/u04/oradata/DB4/redog2m2DB4.dbf') SIZE 50M,
GROUP 3 ('/u03/oradata/DB4/redog3m1DB4.dbf','/u04/oradata/DB4/redog3m2DB4.dbf') SIZE 50M,
GROUP 4 ('/u03/oradata/DB4/redog4m1DB4.dbf','/u04/oradata/DB4/redog4m2DB4.dbf') SIZE 50M,
GROUP 5 ('/u03/oradata/DB4/redog5m1DB4.dbf','/u04/oradata/DB4/redog5m2DB4.dbf') SIZE 50M,
GROUP 6 ('/u03/oradata/DB4/redog6m1DB4.dbf','/u04/oradata/DB4/redog6m2DB4.dbf') SIZE 50M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('/u02/oradata/DB4/system01DB4.dbf','/u02/oradata/DB4/pdbseed/system01DB4.dbf','/u02/oradata/DB4/sysaux01DB4.dbf','/u02/oradata/DB4/pdbseed/sysaux01DB4.dbf','/u02/oradata/DB4/temp01DB4.dbf','/u02/oradata/DB4/pdbseed/temp01DB4.dbf','/u02/oradata/DB4/undotbs01DB4.dbf','/u02/oradata/DB4/pdbseed/undotbs01DB4.dbf') LOCAL UNDO ON;
Once this completed you start creating the catalog and install additional stuff that you need for your application (e.g. Oracle Text or Oracle Spatial). How does that work in PostgreSQL?
In the last post we had a very quick look at initdb. To create the PostgreSQL database cluster you have to use initdb, there is no other possibility. When you take a look at the options you can provide to initdb there are not too much. The only mandatory parameter is “-D” or “–pgdata”. This tells initdb where you want to have the files created on disk:
postgres@pgbox:/home/postgres/ [pg962final] initdb -D /home/postgres/test/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: en_US.UTF-8
CTYPE: en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: de_CH.UTF-8
NUMERIC: de_CH.UTF-8
TIME: en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/test ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /home/postgres/test/ -l logfile start
There are some important messages which got printed to the screen. The first two lines tell us that all the files will be owned by the operating system user which invoked initdb, postgres, and that the same user must be used to start the instance. Then it gets more interesting as it is about the default encoding/characterset for the template database. When you do not specify what you want you get the default of you operating system session:
postgres@pgbox:/home/postgres/ [pg962final] locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC=de_CH.UTF-8 LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY=de_CH.UTF-8 LC_MESSAGES="en_US.UTF-8" LC_PAPER=de_CH.UTF-8 LC_NAME=de_CH.UTF-8 LC_ADDRESS=de_CH.UTF-8 LC_TELEPHONE=de_CH.UTF-8 LC_MEASUREMENT=de_CH.UTF-8 LC_IDENTIFICATION=de_CH.UTF-8 LC_ALL=
Of course you can override that by passing any of the supported character sets to initdb, e.g.:
postgres@pgbox:/home/postgres/ [pg962final] rm -rf test postgres@pgbox:/home/postgres/ [pg962final] mkdir test postgres@pgbox:/home/postgres/ [pg962final] initdb -D test --encoding=LATIN1 --locale=de_DE
You can control how sorting and the display for numbers, money and so on shall happen by specifying the various “–lc” parameters, e.g.:
postgres@pgbox:/home/postgres/ [pg962final] rm -rf test postgres@pgbox:/home/postgres/ [pg962final] mkdir test postgres@pgbox:/home/postgres/ [pg962final] initdb -D test --encoding=LATIN1 --locale=de_DE --lc-messages=en_US --lc-monetary=de_DE
PostgreSQL comes with build-in full text search and the line below the encoding stuff tells you that the default will be English. Can overwrite this as well with the “–text-search-config” parameter of initdb.
Maybe the most important message is this:”Data page checksums are disabled.”. This means that PostgreSQL will not use checksums to detect silent data corruptions. Of course this introduces overhead when enabled but your data usually is important, isn’t it? You can enable this by using the “–data-checksums” switch of initdb and this cannot be changed afterwards.
The last message we will look at for now is this one: “fixing permissions on existing directory /home/postgres/test … ok”. What does that mean? When you look at the permissions of the files and directories that got created by initdb you’ll notice that only the owner of the files and directories has permission (no permission for group and world):
drwx------. 19 postgres postgres 4096 Mar 31 11:07 test postgres@pgbox:/home/postgres/ [pg962final] ls -al test/ total 56 drwx------. 19 postgres postgres 4096 Mar 31 11:07 . drwx------. 10 postgres postgres 4096 Mar 31 10:51 .. drwx------. 5 postgres postgres 38 Mar 31 11:07 base drwx------. 2 postgres postgres 4096 Mar 31 11:07 global drwx------. 2 postgres postgres 17 Mar 31 11:07 pg_clog drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_commit_ts drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_dynshmem -rw-------. 1 postgres postgres 4468 Mar 31 11:07 pg_hba.conf -rw-------. 1 postgres postgres 1636 Mar 31 11:07 pg_ident.conf drwx------. 4 postgres postgres 37 Mar 31 11:07 pg_logical drwx------. 4 postgres postgres 34 Mar 31 11:07 pg_multixact drwx------. 2 postgres postgres 17 Mar 31 11:07 pg_notify drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_replslot drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_serial drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_snapshots drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_stat drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_stat_tmp drwx------. 2 postgres postgres 17 Mar 31 11:07 pg_subtrans drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_tblspc drwx------. 2 postgres postgres 6 Mar 31 11:07 pg_twophase -rw-------. 1 postgres postgres 4 Mar 31 11:07 PG_VERSION drwx------. 3 postgres postgres 58 Mar 31 11:07 pg_xlog -rw-------. 1 postgres postgres 88 Mar 31 11:07 postgresql.auto.conf -rw-------. 1 postgres postgres 22258 Mar 31 11:07 postgresql.conf
When you change that PostgreSQL will refuse to start:
postgres@pgbox:/home/postgres/ [pg962final] chmod 770 test/ postgres@pgbox:/home/postgres/ [pg962final] pg_ctl -D test/ start server starting postgres@pgbox:/home/postgres/ [pg962final] FATAL: data directory "/home/postgres/test" has group or world access DETAIL: Permissions should be u=rwx (0700).
Now that we have everything initialized on disk we are ready to start the instance:
postgres@pgbox:/home/postgres/ [pg962final] pg_ctl -D /home/postgres/test/ start postgres@pgbox:/home/postgres/ [pg962final] LOG: database system was shut down at 2017-03-31 11:07:05 CEST LOG: MultiXact member wraparound protections are now enabled LOG: autovacuum launcher started LOG: database system is ready to accept connections postgres@pgbox:/home/postgres/ [pg962final] psql postgres psql (9.6.2 dbi services build) Type "help" for help. postgres=
From now on you can create a database:
postgres=# create database mydb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
Check here if you want to know what the other databases are for. When you compare that to the Oracle “create database” statement it creates something like a pluggable database and you can create as many as you want. Inside the database you have the usual objects (schemata,tables,views,…). You can even change the encoding for new databases:
postgres=# create database mydb2 encoding='LATIN1' LC_COLLATE='de_CH.iso88591' LC_CTYPE='de_CH.iso88591' template=template0;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+----------------+----------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mydb2 | postgres | LATIN1 | de_CH.iso88591 | de_CH.iso88591 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
Important to know is that users are global so you can not create users inside databases but of course you can grant access to databases to different users and users can be owners of databases:
postgres=# alter database mydb owner to myusr;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+----------------+----------------+-----------------------
mydb | myusr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mydb2 | postgres | LATIN1 | de_CH.iso88591 | de_CH.iso88591 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
How can you then install options into the databases? There are none, all is included. What maybe comes closest to what Oracle calls options are extensions or modules. Some of them are provided by default and you can find them usually in the “share/extension” directory where you installed the PostgreSQL binaries:
postgres@pgbox:/u01/app/postgres/product/96/db_2/ [pg962final] ls bin include lib share postgres@pgbox:/u01/app/postgres/product/96/db_2/ [pg962final] ls share/extension/ adminpack--1.0.sql hstore--1.3--1.4.sql pageinspect.control plperlu--unpackaged--1.0.sql adminpack.control hstore--1.4.sql pageinspect--unpackaged--1.0.sql plpgsql--1.0.sql ...
All of those can be installed per database, e.g.:
postgres@pgbox:/home/postgres/ [pg962final] psql postgres
psql (9.6.2 dbi services build)
Type "help" for help.
postgres=# \connect mydb
You are now connected to database "mydb" as user "postgres".
mydb=# create extension hstore;
CREATE EXTENSION
mydb=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.4 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
mydb=#
Others are not available by default and you usually need to download them from github, e.g.
For an overview can you check the PostgreSQL Extension Network.
Conclusion: You need to use initdb to initialize all the files for your PostgreSQL instance on disk. Use pg_ctl to start your instance and then you are ready to create databases. Hope this helps for starting with PostgreSQL.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)