Developer's Guide to PostgreSQL Admin on Ubuntu

When developing an app which uses a database, I prefer to have a local database instance to develop against instead of using a shared database on the network or provisioning one in the cloud. This allows me to make changes to the database without interfering with others. But this setup does require some basic database administration knowledge. At a minimum, you should know how to install and setup the database server and how to create and remove users and databases. In this post, I will show you how to do this for PostgreSQL on Ubuntu Linux.

Install

PostgreSQL can be installed using the package manager.

sudo apt install postgresql

This command does the following:

1. Installs the PostgreSQL server (postgres)
2. Installs PostgreSQL client apps (psql, createuser, createdb, etc.)
3. Creates a PostgreSQL user named postgres (with superuser status)
4. Creates a PostgreSQL database named postgres
5. Creates a Linux user named postgres

Setup

For local connections, PostgreSQL client apps default to connect using the user and database names matching the username of the Linux user that executed the app.

To get a PostgreSQL interactive terminal for executing SQL queries as a superuser, use sudo -u to execute psql as the postgres Linux user.

sudo -u postgres psql

To do this without sudo, create a PostgreSQL user with superuser status and a database, both with the same name as your Linux user.

Using SQL:

sudo -u postgres psql

CREATE USER username SUPERUSER;
CREATE DATABASE username OWNER username;
\q

• username is your Linux username
• \q quits psql

Using client apps:

sudo -u postgres createuser -s $USER
createdb -O $USER $USER

• -s gives the user superuser status
• $USER is an environment variable which holds your Linux username
• -O specifies the owner of the database

Now you can use psql and the other client apps without sudo.

psql

Create a Database and User

It is common to create a separate database and user for each project.

Using SQL:

psql

CREATE USER username PASSWORD 'password';
CREATE DATABASE dbname OWNER username;
\q

• username is the database user
• password is the database user's password
• dbname is the database name

Using client apps:

createuser username -P
createdb dbname -O username

• username is the database user
• -P causes createuser to prompt for the database user's password
• dbname is the database name
• -O specifies the owner of the database

Remove a Database and User

Using SQL:

psql

DROP DATABASE dbname;
DROP USER username;
\q

• dbname is the database name
• username is the database user

Using client apps:

dropdb dbname
dropuser username

• dbname is the database name
• username is the database user

Access a Database

Using psql:

psql -h localhost -U username -W dbname

• -h specifies the hostname of the PostgreSQL server (localhost)
• -U username specifies the database user to connect as
• -W causes psql to prompt for the database user's password
• dbname is the name of the database to connect to

Using Node.js:

import { Client } from 'pg';

async function hello() {
  const client = new Client({
    host: 'localhost',
    database: 'dbname',
    user: 'username',
    password: 'password',
  });
  await client.connect();

  try {
    const res = await client.query(`SELECT 'Hello world!' as message`);
    console.log(res.rows[0].message);
  } catch (e) {
    console.log(e);
  } finally {
    await client.end();
  }
}

hello();

• dbname is the database name
• username is the database user
• password is the database user's password