PostgreSQL: How To Setup Our Installed PostgreSQL

Intro

So we installed PostgreSQL on our machine.

Now we want to learn how to setup our installed PostgreSQL.

Create a new PostgreSQL database cluster

initdb must be run as the user that will own the server process, therefore it is recommended to create a new user and login with it.

# login as user
sudo -iu [username]

initdb creates a new PostgreSQL database cluster (a collection of databases that are managed by a single server instance).

initdb -D /var/lib/postgres/data

If you get permission denied, you can find a solutin in the initdb docs

Start PostgreSQL server

If you don't know how to start a system service, read this.

Example for Arch:

systemctl start postgresql

If you get some problems, read this.

Create a Role (= Database User)

Now we need a role, that can do some operations.

Don't forget to be logged in as the user you created before:

# login as user
sudo -iu [username]

Create user:

createuser --interactive

createuser docs

Create a Database

No we need a database, that is owned by our new user

createdb -O [username] [dbname]

Connect to the Database Shell

We've just created a user and a database.

Now we can connect to the database shell:

psql -d [dbname]

psql is a terminal-based front-end to PostgreSQL, you can type in queries, issue them to PostgreSQL, and see the query results.

Next Part

We will write our first commands to get some data.

Further Reading

PostgreSQL Homepage
PostgreSQL Docs
SQL Syntax

Questions

• What's your favorite SQL database?