How time flies, last March I explained how to install and configure PostgreSQL on Windows. It was my intent to start posting more content on PostgreSQL but I was distracted by another writing commitment on Oracle Database 12c PL/SQL Advanced Programming Techniques, which should be available in November. It tempted me away from PostgreSQL because I got to write about how to use Java inside Oracle Database 12c, which was fun. Having completed that, I’m back on task. Here’s the second entry on PostgreSQL. It shows you howto create your own database, database administrator role, user, and how to connect with
psql CLI (Command Line Interface) as the new user.
- Create a user-defined
video_db tablespace for your database. This requires that you know where the physical files where created when you installed PostgreSQL. You can discover the directory with the following query:
SELECT setting AS "Data Location"
WHERE name = 'data_directory';
You create the
video_db tablespace with the following syntax:
CREATE TABLESPACE video_db
LOCATION 'C:\Program Files\PostgreSQL\9.3\data';
You can check the presence of the
video_db tablespace after creating it with the following query:
SELECT * FROM pg_tablespace;
It should print:
spcname | spcowner | spcacl | spcoptions
pg_default | 10 | |
pg_global | 10 | |
video_db | 10 | |
It’s important to note for those new to PostgreSQL that the
pg_default tablespaces are creating when initializing the database. The
pg_global holds shared tables and the
pg_default holds everything else.
- Create a database that uses your user-defined
video_db tablespace with the following two commands:
CREATE DATABASE videodb
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = video_db
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
CONNECTION LIMIT = -1;
COMMENT ON DATABASE videodb
- Create a database role, grant the super user privileges to the role, and create a user with the role. You can do that with the following three commands:
CREATE ROLE dba
GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
CREATE USER video
WITH ROLE dba
- Connect to the new
videodb database with the
psql CLI as the
video user. You can do that with the following OS command:
- Once connected as the new
video user, you can use a system information function to determine the current database:
It should display:
There are many privilege options, and you should choose wisely which ones you use. As always, I hope this answers questions for other users.