Database - Postgres

Updated: 2020-04-02

Install Postgres

If you do not care about the latest features (most likely you would not need them for these tutorials), use the default version installed from brew or apt

# macOS
$ brew install postgresql

# Ubuntu
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Or follow the latest official doc to install: https://www.postgresql.org/docs/current/

Start Postgres

Check that postgresql is correctly installed

$ service postgresql
Usage: /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status} [version ..]

Start the service

$ sudo service postgresql start

Check status

$ service postgresql status
12/main (port 5432): online

Connect to postgres

psql is a regular PostgreSQL client application.

psql is just another client.

$ psql
psql: error: could not connect to server: FATAL: role "ubuntu" does not exist

The error is because user ubuntu is not a valid role; to create a user, you need to login as postgres first:

[email protected]:~$ sudo -u postgres -i

Note that the prompt is changed to postgres instead of ubuntu

[email protected]:~$ createuser -l -d -P ubuntu
Enter password for new role:
Enter it again:

where

  • -d: The new user will be allowed to create databases.
  • -l: The new user will be allowed to log in
  • -P: createuser will issue a prompt for the password of the new user.

This will create a new user and grant proper permissions.

Exit to your normal account:

Try again:

$ psql
psql: error: could not connect to server: FATAL:  database "ubuntu" does not exist

Now it complains about the missing database instead of role. The default user name AND the default database name are both your OS user name, in this case ubuntu.

  • -d: dbname
  • -h: hostname
  • -p: port
  • -U: username

$ psql postgres is equivalent to $ psql -d postgres as argument does not belong to any option would be interpreted as database name.

$ psql postgres

# now connected to postgres server; prompt changed
postgres=> \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |

Create a new db:

postgres=> CREATE DATABASE timeline;
CREATE DATABASE

postgres=> \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 timeline  | ubuntu   | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |

\l is equivalent to postgres=> SELECT * FROM pg_database;

Create a db/create.sql file:

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  event_name text,
  event_time TIMESTAMP
);

Execute the file:

$ psql timeline -f db/create.sql