Your Definitive Guide to Installing PostgreSQL for Ubuntu 16.04
No install guides described the complete process I had to go through
I am working on a project that requires PostgreSQL on the back end. It’s a fairly popular database solution so I was surprised that I had such a hard time getting it working under Ubuntu 16.04.5 LTS (Xenial Xerus) at least the version I use under the Windows Subsystem for Linux. I read several install guides and none of them came close to describing the complete process I had to go through in order to get PostgreSQL functional so I wanted to document my process in the hopes it saves someone some time and frustration in the future.
Step One: Download
I look to use npm when I can, but getting no joy there or with Linuxbrew so I went with apt-get but first:
Create the file /etc/apt/sources.list.d/pgdg.list and add a line for the repository:
$ deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
Import the repository signing key, and update the package lists:
$ wget — quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
All the install docs had I looked at had me downloading postgresql, in addition some had me getting postgresql-contrib, others postgresql-common, I found I needed all three:
$ sudo apt-get install postgresql postgresql-contrib postgresql-common
Step Two: Fix PATH
So after the scripts finished I went to confirm installation:
$ postgres — version
And in response got:
No command ‘postgres’ found, did you mean: Command ‘postgrey’ from package ‘postgrey’
postgres: command not found
This is because PostgreSQL is not in our environment path. Yet :)
I tried putting the following statements in .bashrc but they were not being read on system boot, possibly because I have a .bash_profile so I ended up putting them in .bash_profile. All four directories is probably overkill but between postgres, psql, pg_ctl, and pg_ctlcluster there are executable files all over the place so just to be sure:
export PATH=$PATH:/usr/lib/postgresql/9.5/bin
export PATH=$PATH:/usr/lib/postgresql/9.5/main
export PATH=$PATH:/var/lib/postgresql/9.5/main
export PATH=$PATH:/var/run/postgresql
Source your config file, in this case .bash_profile
$ source .bash_profile
Now running $ postgres — version
should finally give you the expected response:
postgres (PostgreSQL) 9.5.13
Step 3: Start the server
I was feeling pretty good about solving the PATH problem, and went to create my database:
$ createdb -U postgres -w address-bloc-dev
But was confronted with another error:
could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432”?
A quick check showed
$ sudo service postgresql status
9.5/main (port 5432): down
so
$ sudo service postgresql start
* Starting PostgreSQL 9.5 database server
$ sudo service postgresql status
9.5/main (port 5432): online
Yay! Server started :)
Step 4: Fix postgreSQL permissions
I was, again, feeling pretty good and, again, went to create my database and, again, got an error grrrr:
createdb: could not connect to database template1: FATAL: Peer authentication failed for user “postgres”
The solution here is to edit the file /etc/postgresql/9.5/main/pg_hba.conf but you will have to have su or root access to read/write on pg_hba.conf:
$ sudo nano /etc/postgresql/9.5/main/pg_hba.conf
Change the Postgres Authorization Method on the line
# “local” is for Unix domain socket connections only
from peer to trust:
local all all peer
to:
local all all trust
Restart your server:
$ sudo service postgresql restart
and you should be good to go!
Conclusion
Try creating your database and if it works you should get no system response, just another command prompt back. I would like this document to be of value where it can be so if you notice any errors or have any suggestions or feedback or just don’t think its definitive enough let me know so I can update accordingly. I hope this helped!
