Creating a Database in PostgreSQL

The creation of a database in PostgreSQL is almost too simple.
This article will give you a initial view of how to create a database and
verify its existence.

This article assumes that you have read what is beginning to
become a series in getting a PostgreSQL environment setup and an actual
instance up and running. If you need assistance in those categories please
visit these past articles: PostgreSQL
Software Installation and Between the
Software and theDatabase.

Since we have gotten an instance running in PostgreSQL, it
is time to create a real database that we can use. Every PostgreSQL instance
can manage more than one database and as you go through this article just
consider how you might segregate your application or use across the multiple
databases that a single instance can handle. Most database vendors that choose
this hierarchy of one instance to many databases expect you to segregate
databases by usage and thus group application objects into a single database.

How to Create a Database

In order to create a database you must have a running PostgreSQL
instance. From previous articles you learned to start an instance by executing
the postmaster as show in Figure 1. The output of
starting an instance should be similar to the Listing 1.

Figure 1
Starting an instance through postmaster script

Success. You can now start the database server using:
    /usr/local/pgsql/bin/postmaster -D /u04/pgsql/data

Listing 1

Output of starting an instance

 [postgres@koopgate postgres]$     /usr/local/pgsql/bin/postmaster -D /u04/pgsql/data
LOG:  database system was shut down at 2003-11-16 07:29:58 MST
LOG:  checkpoint record is at 0/8019BC
LOG:  redo record is at 0/8019BC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 509; next oid: 16976
LOG:  database system is ready

After the instance is up and running, or if it already is,
you need to connect to a database under the control of the instance you wish to
create your new database under. If you recall, when creating the instance in a
previous article, you also create a template database by default. You can
connect to the template database as shown in Figure 2.

Figure 2

Connect to the template database

[postgres@koopgate postgres]$ psql template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit
template1=#

The SQL statement to create a database has a very simple
form as show in Figure 3. After you enter the
statement, which comes back very fast, you should get the “CREATE DATABASE”
acknowledgement. Believe it or not, I did have a problem with this simple
statement. I had forgotten to put the “;” after the CREATE statement and this
cause a bit of havoc until I entered a “;” a few times. All this happened
without an error message. If you find yourself in this situation, just look for
the “CREATE DATABASE” acknowledgement.

Figure 3

CREATE DATABASE statement

template1=# CREATE DATABASE db1;
CREATE DATABASE

Verifying a Database was Created

You can now verify that you have created a database
successfully by connecting to it. Figure 4 shows that I did in fact create the
database and am able to connect to it.

Figure 4

Connecting to my newly created database

[postgres@koopgate data]$ psql db1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit
db1=#

If you wish to query a system catalog table or two, to
verify you created a database, use the SQL in Listing 2
or Listing 3. Listing 2 shows the database id along with the database
name. Listing 3 shows the database name with the database owner id in the datdba
column for each of the databases. To determine who this owner is you can query
the pg_shadow system table as shown in Listing 4.
Each of these three tables is global and viewable across the entire database in
instance. Therefore, if I connected to the db1 database and issued the last
three SQL statements, I would get the same result.

Listing 2

Query pg_stat_database system catalog table to see newly
created database

db1=# select datid, datname from pg_stat_database;
 datid |  datname  |
——-+———–+
 16976 | db1       |
     1 | template1 |
 16975 | template0 |
(3 rows)

Listing 3

Get database name and database owner

db1=# select datname, datdba from pg_database;
  datname  |datdba |
———–+——-+
 db1       |     1 |
 template1 |     1 |
 template0 |     1 |

Listing 4

Get users that are defined across the databases

db1=# select usename, usesysid from pg_shadow;
 usename  | usesysid |
———-+———-+
 postgres |        1 |