In Linux we trust!

Ubuntu 16 PostgreSQL 10 quick start

My technical notes concerning PostgreSQL 10 install on newly created Ubuntu 16.04 instance.

After instance is ready, let's prepare it before proceeding to PostgreSQL:

# apt-get update
# apt-get upgrade
# apt-get install cron fontconfig curl ca-certificates imagemagick bc
# apt-get install dnsutils git wget iputils-ping nano unzip gcc

Then add to /etc/apt/sources.list.d/pgdg.list following line:

deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
# wget --quiet -O - 'https://www.postgresql.org/media/keys/ACCC4CF8.asc' \
>   | apt-key add -
# apt-get update
# apt-get install postgresql-10

Use commands below to manage PostgreSQL daemon:

# systemctl stop postgresql.service
# systemctl start postgresql.service
# systemctl enable postgresql.service
# systemctl status postgresql.service

PostgreSQL is running, but still is not configured. Edit /etc/postgresql/10/main/postgresql.conf:

listen_addresses = '*'

Since it's my test server, PostgreSQL server listens on all possible interfaces. In most cases it's not your option (be careful).

/etc/postgresql/10/main/pg_hba.conf:

host mydb myuser 0.0.0.0/0 md5

mydb and myuser values should be changed by your own ones. After that switch to postgres user and enter CLI mode:

# su - postgres
$ psql

Create database and corresponding user:

CREATE DATABASE mydb;
CREATE USER "myuser" WITH ENCRYPTED PASSWORD 'mypass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO "myuser";
GRANT USAGE ON SCHEMA public TO "myuser";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "myuser";

Server is ready (you can additionally restart it). Let's test connection from your PC:

$ pgcli -h myhost -U myuser -d mydb

BTW, pgcli utility is the only adequate way to operate with PostgreSQL.