This is the next post in a series of posts about PostgreSQL and is actually a continuation from post on “PostgreSQL Create User – Manage Users“.

If you followed my post on “PostgreSQL Create User – Manage Users” then you successfully created your firstPostgreSQL users and also assigned them desired rights and privileges. The next step is to create and manage PostgreSQL databases and thus a post onPostgreSQL Create Database follows.

In this post we will describe how to create, delete and change a database and all of the useful options to go along with. We will also learn what a tablespace is and how to create a new tablespace for PostgreSQL database to reside on.

PostgreSQL Create Database

As with creating a PostgreSQL user, same is with the database – there are two ways of creating a database on PostgreSQL. One way is to

• create a database via console command “creatdb”
• create a database via “psql“ SQL command.

We are assuming you are running a fresh and clean install of PostgreSQL server and did not touch “ph_dba.conf” file yet. This is important since by default authentication for local users is set to peer and enables “postgres” user to login via “psql” without any password prompt.

Console Command (PostgreSQL Create Database)

We can create a new PostgreSQL database using the console command “createdb” which we must run as “postgres” user.

Although console comand “createdb” is a wrapper around SQL “CREATE DATABASE” command, it does not support setting a “connenction limit” for the newly created database.

 By default,  if no database name is specified, a new database with the same name as the user running “createdb” command will be created. Interesting thing is also that by default new databases will be created by cloning the standard system database “template1”.

In most cases the following syntax will be more than enough when creating a new PostgreSQL database:

[root@geekpeek ~]# su - postgres
-bash-4.1$ createdb -O geek01 database01

In this example we created a new database called “database01” with owner “geek01” – of course this PostgreSQL user must exist.

Another useful option when using “createdb” is the “echo” of the SQL command sent by the “createdb” command:

-bash-4.1$ createdb -O geek01 database01 -e
CREATE DATABASE database01 OWNER geek01;

We can also define locale, encoding and the template to use when creating a new database. You can see all available options by running “createdb –help”.

Psql SQL Command (PostgreSQL Create Database)

New PostgreSQL database can also be created via “psql” command. Change to “postgres” user and run “psql” to run SQL commands.

Creating a new database via “psql” command also allows us to define the “connection limit” for the newly created database.

These are the concurrent connections that can be made to the database. The default setting for this is -1 which means unlimited number of connections can be made.

Please do not forget the semicolon at the end of each SQL command!

Create a new database with the desired owner with the following SQL command:

[root@geekpeek1 ~]# su - postgres
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# CREATE DATABASE database02 OWNER geek01;
CREATE DATABASE

We can also define default encoding locale, encoding, template and connection limit when creating a new database.

Let’s create a new database with connection limit set to 20:

postgres=# CREATE DATABASE database03 OWNER geek01 CONNECTION LIMIT 20;
CREATE DATABASE

Options we can use when creating a new database with SQL command are:

CREATE DATABASE name
 [ [ WITH ] [ OWNER [=] user_name ]
 [ TEMPLATE [=] template ]
 [ ENCODING [=] encoding ]
 [ LC_COLLATE [=] lc_collate ]
 [ LC_CTYPE [=] lc_ctype ]
 [ TABLESPACE [=] tablespace_name ]
 [ CONNECTION LIMIT [=] connlimit ] ]

PostgreSQL Delete Database

There are two ways of deleting a PostgreSQL database. One way is to drop the database via console command “dropdb” and the second one is to drop the database via “psql“.

Again, “dropdb” console command is a wrapper arounud the SQL “DROP DATABASE” command.

Console Command (PostgreSQL Delete Database)

Run “dropdb” console command as “postgres” user. If you would like to know the SQL command run by “createdb” use the “-e” echo parameter:

[root@geekpeek ~]# su - postgres
-bash-4.1$ dropdb database03 -e
DROP DATABASE database03;
-bash-4.1$

Psql SQL Command (PostgreSQL Delete Database)

Change to “postgres” user and run “psql” command. We can delete the PostgreSQL database with the following syntax:

[root@geekpeek ~]# su - postgres
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# DROP DATABASE database01;
DROP DATABASE
postgres=#

…not many other useful options when dropping a PostgreSQL (or any other) database 🙂

PostgreSQL Change Database

Halfway through. We already covered the topics on PostgreSQL Create Database and Delete Database., but sometimes we need to make changes to existing database and this is no problem to do in PostgreSQL. Changing existing database owner or anything else is a made in a snap in PostgreSQL.

Again we need to become “postgres” user and run “psql” command. When this is done we execute “ALTER DATABASE” SQL command with the desired options.

If we want to change the owner of an existing database we must run the following SQL command:

postgres=# ALTER DATABASE database02 OWNER TO newuser;
ALTER DATABASE

This command changed the owner of the database “database02” to user “newuser”.

We can also rename the database by running the following SQL command:

postgres=# ALTER DATABASE database02 RENAME TO database01;
ALTER DATABASE

This command renamed the database called “database02” to “database01.

PostgreSQL Create New Tablespace

Tablespace is a location on the filesystem where database and files related to this database reside. We can put more than one database on a specific tablespace. Tablespace name must be unique and can not begin with “pg_” since such names are reserved for system tablespaces.

Usually tablespaces are used because we want to put the database files on a different partition on our system.

We can create a new tablespace with the following SQL command:

postgres=# CREATE TABLESPACE dbspace01 OWNER geek01 LOCATION '/opt/db01';
CREATE TABLESPACE

With this command we created a new tablespace named “dbspace01” with user “geek01” as owner. The location is a directory on the filesystem.

Please note that the location directory must exist, so create the directory prior to creating tablespace and the owner of the directory must be user “postgres”!

We can migrate an existing database to the new tablespace by issuing the following SQL command:

postgres=# ALTER DATABASE database01 SET TABLESPACE dbspace01;
ALTER DATABASE

There are also a number of changes we can make to the database using the “SET” parameter, but we will not be covering this in our how to. This is the end of my post on PostgreSQL Create Database and Tablespace, stay tuned for the next one!