Category: PostgreSQL

Install and Configure PostgreSQL

Here’s how to correctly install and configure PostgreSQL.

Install

First update the system

sudo apt-get update

Then install the packages:

sudo apt-get install postgresql postgresql-contrib

Create Database and User

Now let’s create a PostgreSQL roles and databases.

PostgreSQL uses the concept of roles to distinguish the variety of users that can connect to a database. After a fresh install, the default PostgreSQL user is actual named “postgres”.

Login into the “postgres” user using

sudo su postgres

and add a user

createuser -e -P USERNAME

set the password for the specified USERNAME.

Now create a database and assign the user to it

createdb -O USERNAME DATABASENAME

Connect to the database using

psql -d DATABASENAME -U USERNAME

And that’s it.

Correctly Uninstall PostgreSQL

Uninstalling PostgreSQL may leave some undesired files. Here’s how to purge it:

First, uninstall all packages from PostgreSQL from the system using

sudo apt-get --purge remove postgresql\*

Then remove all the configuration and library stuff

sudo rm -r /etc/postgresql/
sudo rm -r /etc/postgresql-common/
sudo rm -r /var/lib/postgresql/

And finally, remove the user and group

sudo userdel -r postgres
sudo groupdel postgres

Upgrade PostgreSQL from 9.1 to 9.3 on Kubuntu

This seven steps will perform the upgrade of PostgreSQL from version 9.1 to version 9.3.
This also works in Ubuntu and it can also be used to upgrade between any version numbers.

To upgrade between any versions, just changed the 9.1 for the legacy version number and the 9.3 for the new version number.

First install the necessary dependencies

sudo apt-get update
sudo apt-get -y install python-software-properties

Second, add the PostgreSQL repository

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

Third, setup the repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

Fourth, install PostgreSQL 9.3.

sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-contrib-9.3

Fifth, perform the upgrade process.
The upgrade process is performed by having both servers running at the same time. Note that the new 9.3 version will run on a different port, 5433 as specified in the script bellow, and it will be set to the default port latter when the legacy version is uninstalled and the default port becomes available.

sudo su -l postgres
psql -d template1 -p 5433
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
\q #logout from database
service postgresql stop
/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O " -c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"
exit # logout postgresql back to previous user

Sixth, remove the 9.1 version.

sudo apt-get remove postgresql-9.1

Seventh, set the new version server port back to the default value and restart the service.

sudo vim /etc/postgresql/9.3/main/postgresql.conf # find old port of 5433 and change it to 5432
sudo service postgresql restart