why not take a look at postgresql ? (2): database creation

if you followed the first postgresql post the initial postgresql instance should be up and running and now it is time to create the first database. remember that in postgresql terms a database is defined as: A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database. (However there are a few system catalogs, for example pg_database, that belong to a whole cluster and are accessible from each database within the cluster.) More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

first of all let’s create a .psqlrc file which will control some settings for the psql sessions created:

echo "\set AUTOCOMMIT off
\set PROMPT1 '%n@%m:%>/%/%x%# '
\pset null '<NULL>'
\pset format aligned" > ~/.psqlrc

this will turn off autocommit, set a nice psql prompt for us, display NULL values as ‘<NULL>’ and sets the output to aligned mode.

addtionally I will create a new alias for my database connection as I am too lazy to type the whole command every time:

echo "alias pg='psql -d postgres -U sysdba'" >> ~/.bashrc
. ~/.bashrc

lest quickly check which databases are already present in the current postgresql instance:

pg
Null display is "<>".
Output format is aligned.
psql (9.1.4)
Type "help" for help.
sysdba@[local]:5432/postgres# select datname from pg_database;
datname
-----------
template1
template0
postgres
(3 rows)
sysdba@[local]:5432/postgres*#

the template1 database will essentially be cloned for every database created. this means if you create objects or users in this database they will be present ( cloned ) in the created database, too. If i remember correctly microsoft sql server uses a similar concept.
the template0 database should not be used to create users or objects so that you always have a blank template at the original state available if you need to create databases without custom objects.

to create a database issue a command similar to this:

mkdir /opt/postgres/mydb/tbs
pg
CREATE TABLESPACE db_tbs1 LOCATION '/opt/postgres/mydb/tbs/';
CREATE DATABASE db1
OWNER default
TEMPLATE template1
ENCODING 'UTF8'
TABLESPACE db_tbs1
;

the character sets supported by postgres are listed in the documentation.

to quickly check the encodings of your databases may either query pg_database:

 sysdba@[local]:5432/postgres*# select datname,datcollate,datctype from pg_database; 
datname    | datcollate  | datctype 
-----------+-------------+------------- 
template1 | en_US.UTF-8 | en_US.UTF-8 
template0 | en_US.UTF-8 | en_US.UTF-8 
postgres | en_US.UTF-8 | en_US.UTF-8 
db1 | en_US.UTF-8 | en_US.UTF-8 

or use the psql shortcut:

psql -U sysdba -l 
Null display is "<>". 
Output format is aligned. 
List of databases 
Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+-------------+-------------+------------------- 
db1       | sysdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | sysdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
template0 | sysdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/sysdba + | | | | | sysdba=CTc/sysdba 
template1 | sysdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/sysdba + | | | | | sysdba=CTc/sysdba 

destroying the database is almost exactly the same as in oracle:

DROP DATABASE db1;

what about temporary tablespaces ? let’s see what tablespaces currently exist in the instance:

SELECT * FROM pg_tablespace; 
spcname    | spcowner | spclocation | spcacl | spcoptions 
------------+----------+------------------------+----------+------------ 
pg_default | 10       | | | pg_global | 10 | | | 
db_tbs1    | 10       | /opt/postgres/mydb/tbs | | 

if you read the documentation postgresql seems to use a similar concept than the table space groups within oracle. let’s create two new tablespaces. for this prepare the directories ( is not possible to create more than one tablespace in the same directory ):

mkdir /opt/postgres/mydb/tbs_temp1/
mkdir /opt/postgres/mydb/tbs_temp2/

… and create the tablespaces:

CREATE TABLESPACE temp1 LOCATION '/opt/postgres/mydb/tbs_temp1/'; 
CREATE TABLESPACE temp2 LOCATION '/opt/postgres/mydb/tbs_temp2/'; 
SELECT * FROM pg_tablespace; 
spcname | spcowner | spclocation | spcacl | spcoptions 
------------+----------+------------------------------+----------+------------ 
pg_default | 10 | | <> | <> pg_global | 10 | | <> | <> 
db_tbs1 | 10 | /opt/postgres/mydb/tbs | <> | <> 
temp1 | 10 | /opt/postgres/mydb/tbs_temp1 | <> | <> 
temp2 | 10 | /opt/postgres/mydb/tbs_temp2 | <> | <> 

adjusting the configuration file:

echo "temp_tablespaces='temp1,temp2'" >> /opt/postgres/mydb/postgresql.conf

… reload the configuration

pgreload

… and check the actual value:

show temp_tablespaces;
temp_tablespaces
------------------
temp1, temp2
(1 row)

postgresql will now select one of the tablespaces ( round robin ) for the operations which will require temporary space.

next time I will take a look at the maintenance tasks which can/should be performed with postgres.