PostgreSQL Installation and DB Creation on Ubuntu 16.04

2018-03-26 611 words 3 minutes views

Contents

Installation

Note: This installation procedure is based on Ubuntu 16.04, for other versions see [https://www.postgresql.org/download/linux/ubuntu/]

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository

1

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list

Import the repository signing key, and update the package lists

1

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Ubuntu includes PostgreSQL by default. To install PostgreSQL on Ubuntu, use the apt-get (or other apt-driving) command
1 2

$ sudo apt-get update $ sudo apt-get install postgresql-9.6
The bin directory (/usr/lib/postgresql/9.6/bin) of postgresql package contains all the necessary commands. Hence let’s make the directory accessible from any where.
- Edit ~/.profile file to export postgresql bin directory.
1

$ vim ~/.profile
- Add the following two lines to export postgresql utility commands
1 2

PATH=$PATH:/usr/lib/postgresql/9.6/bin export PATH
- Activate the profile
1

$ . ~/.profile
During the installation, PostgreSQL will create a default user to operate under. We will be using this user for some administrative purposes.
- Log in with the following command
1

$ sudo su - postgres
- Do the same as #4
- Logout from this user
1

$ exit

Now we can execute the utility commands from the shell, even for the postgres user.

1
2


$ pg_ctl --help
$ pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Setup User/Role and DB

Note: PostgreSQL assumes that when we log in, we will be using a username that matches our operating system username, and that we will be connecting to a database with the same name as well.

Switch to user postgres
1

$ sudo su - postgres
Run psql command to create postgres’s password. It doesn’t have any default password.
1 2 3

$ psql postgres=# \password postgres postgres=# ********

Create a user/role for our brand new database, let’s say blog and give the user to LOGIN and CREATEDB permissions. Also make the user OWNER of the db blog

1
2
3


postgres=# CREATE ROLE admin WITH LOGIN PASSWORD '********';
postgres=# ALTER ROLE admin CREATEDB;
postgres=# CREATE DATABASE blog OWNER admin;

Quit psql commandline interface
1

postgres=# \q
Exit (logout) from this user (postgres)
1

$ exit

Now we can run psql command with that newly created user without switching to user postgres

1

$ psql --host=localhost --port=5432 --username=admin --password --dbname=blog

This will connect the command line interface to the new db blog with this user admin logged in. Output should be something like the followings:

1
2
3
4


psql (9.6.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
blog=>

Creating Cluster Data Directory

initdb will attempt to create the directory we specify if it does not already exist. Of course, this will fail if initdb does not have permissions to write in the parent directory. It’s generally recommendable that the PostgreSQL user own not just the data directory but its parent directory as well, so that this should not be a problem. If the desired parent directory doesn’t exist either, we will need to create it first, using root privileges if the grandparent directory isn’t writable. So the process might look like this:
1 2

$ sudo mkdir /usr/local/pgsql $ sudo chown postgres /usr/local/pgsql
Again before running any postgresql commands we need to switch user to postgres
1

$ sudo su - postgres

Now we can point the directory for creating cluster data directory using initd

1

$ initdb -D /usr/local/pgsql/data

Output should be ended with something like this:

1
2


Success. You can now start the database server using:
pg_ctl -D /usr/local/pgsql/data -l logfile start

So let’s start the server

1

$ pg_ctl -D /usr/local/pgsql/data -l pgserver.log start