We're ready to create our first PostgreSQL databases and learn how to work with them.
As indicated in the previous article, I'll assume you have two normal database roles — named "demorole1" and "demorole2" — and an additional superuser role named "mysuperuser". If those roles don't exist in your postgres server, go ahead and create them now.
Login as Linux user "postgres"
To create a database, we'll need to connect as the postgres superuser role. This will first require a login as the Linux user named "postgres". This was explained in the article PostgreSQL - local connections as postgres.
Login to your slice as a normal Linux user, then:
sudo su - postgres
Connect with psql
Now connect to the database server using the psql client, as the postgres role:
postgres@demo:~$ psql -U postgres ... Welcome to psql 8.3.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=#
Listing all databases
From within a psql session, you can list all databases using the '\l' command. That's a lowercase "L".
Let's try it now:
postgres=# \l List of databases Name | Owner | Encoding -----------+----------+----------- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows)
The three databases listed were created during the PostgreSQL installation process — we won't be working with them directly, though you can learn their purpose in Section 20.3 of the official documentation.
Creating a database
We'll create our first database with this SQL statement:
postgres=#CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';
Note the required trailing semicolon ( ; ) at the end of the SQL statement. Single-quotes ( ' ' ) must enclose the specified character encoding.
We've specified 'demorole1' as the owner of this database. If we don't specify an owner, ownership defaults to the creating role.
Specifying the database's character encoding as UTF-8 (a.k.a. "Unicode") is not strictly necessary, but is a reasonable thing to do if our data will be coming from and viewed in a web application. If unspecified, a database's character set will default to 'SQL_ASCII'. Learn more about character encoding in Section 22.2 of the official documentation.
Did it work? We can check using the '\l' command:
postgres=# \l List of databases Name | Owner | Encoding -----------+-----------+----------- demodb1 | demorole1 | UTF8 postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (4 rows)
Dropping a database
What if we want to drop (delete, destroy) a database? Easy:
postgres=#DROP DATABASE demodb1;
Note that dropping a database cannot be undone; once dropped, a database and its contents are permanently deleted.
If we check with the '\l' command we'll see that 'demodb1' is no longer listed.
An alternative: createdb and dropdb
Alternatively, we can create and drop databases using the createdb and dropdb shell commands, which are basically "wrappers" for the CREATE and DROP SQL statements. They are included in a standard postgres installation.
With our present setup, we can only run these commands (successfully) as the postgres Linux user. We're still connected with the psql client, so let's exit with Ctrl-D or the '\q' command:
postgres=# \q ... postgres@demo:~$
We're back to our shell prompt, as the postgres Linux user.
With createdb and the '-O' flag we'll create a database owned by the 'demorole2' role:
postgres@demo:~$ createdb -O demorole2 -E UTF8 demodb2
With the '-E' flag we've specified the database's character encoding as UTF-8.
Now, If we reconnect with psql and run the '\l' command, we'll get this:
postgres=# \l List of databases Name | Owner | Encoding -----------+-----------+----------- demodb2 | demorole2 | UTF8 postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (4 rows)
We can drop (delete, destroy) a database with the dropdb shell command:
postgres@demo:~$ dropdb -i demodb2 Database "demodb2" will be permanently removed. Are you sure? (y/n) y ... postgres@demo:~$
The '-i' flag provides a confirmation prompt, which is a good safety measure when running an irreversibly destructive command.
We've looked at creating and dropping databases using the psql client, and alternatively using the createdb and dropdb shell commands.
Going forward, I'll assume you have two databases: 'demodb1' owned by 'demorole1', and 'demodb2' owned by 'demorole2'. To follow the remaining articles closely, go ahead and re-create those databases using either method.
In the next article, we'll configure our postgres server to allow less restricted local and remote connections.