PostgreSQL Installation and Cheatsheat

I always seem to forget some small details when installing PostgreSQL so a nice cheatsheet is very good to have. Following applies to Ubuntu Xenial 16.04 and PostgreSQL 9.5

Installation:

Installation is the easy part. Lets install postgis at the same time. You don't need postgis if you wnn't work with spatial data.

# apt-get install postgresql postgis

PostgreSQL by default works with UID authentication. To become administrator, all we have to do is to switch to postgres user and then run psql

# su - postgres
$ psql

Lets create a user named masauser. You have to be logged in as postgres user.

$ createuser --pwprompt masauser
$ createdb -O masauser masadb

Remember dropuser and dropdb commands do the reverse and delete a user and database.

If you want to change the password of the user you can use \password if you are changing the current user's password. Otherwise use this:

ALTER USER masauser WITH PASSWORD 'masapassword';

Note that this will be stored in executing user's history!

Enabling Extensions:

To be able to use postgis, you need to connect to the database you want to use postgis. Then execute CREATE EXTENSION postgis;

To enable UUID support execute the following CREATE EXTENSION "uuid-ossp";

To be able to use trigram support, you need to connect to the database you want to use trigram. Then execute CREATE EXTENSION pg_trgm;

Logging in:

Normally you can login simply by writing psql masadb masauser but you may need to add the -W option if psql does not ask password. psql masadb masauser -W

If you want to login to correct database which uses password authentication, you can export the necessary information in login. The important part is PGHOST otherwise PostgreSQL will not use the password.

export PGHOST=localhost
export PGDATABASE=masadb
export PGUSER=masauser
export PGPASSWORD=masapassword

Useful Hints:

It is quite useful to enable timing when running queries. You can easily see efficiency of your queries when timing is enabled. This is enabled by timing

Create table and fill it with random data:

CREATE TABLE users (
    first_name text,
    last_name varchar(255)
)

INSERT INTO users (
    first_name,last_name
)
SELECT md5(random()::text), md5(random()::text) FROM
          (SELECT * FROM generate_series(1,1000000) AS id) AS x;

Backups:

Dump database with compression you should login as postgres user first:

$ pg_dump masadb | bzip2 > masa.sql.bz2
$ bzip2 -c -d masa.sql.bz2 | psql masadb